1 PACKAGE BODY AHL_DI_ASSO_DOCASO_PVT AS
2 /* $Header: AHLVDOAB.pls 115.29 2003/09/15 06:39:31 rroy noship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_ASSO_DOCASO_PVT';
4 G_PM_INSTALL VARCHAR2(30):=ahl_util_pkg.is_pm_installed;
5 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
6
7 PROCEDURE RECORD_IDENTIFIER
8 (
9 p_association_rec IN AHL_DI_ASSO_DOCASO_PVT.association_rec,
10 x_record OUT NOCOPY VARCHAR2
11 )
12 as
13 Begin
14 IF p_association_rec.aso_object_type_code = 'OPERATION'
15 THEN
16 If p_association_rec.document_no is not null and p_association_rec.document_no<>fnd_api.g_miss_char
17 Then
18 x_record:=x_record||nvl(p_association_rec.document_no,'')||' - ';
19 End if;
20
21 If p_association_rec.revision_no is not null and p_association_rec.revision_no<>fnd_api.g_miss_char
22 Then
23 x_record:=x_record||nvl(p_association_rec.revision_no,'')||' - ';
24 End if;
25
26 If p_association_rec.chapter is not null and p_association_rec.chapter<>fnd_api.g_miss_char
27 Then
28 x_record:=x_record||nvl(p_association_rec.chapter,'')||' - ';
29 End if;
30
31 If p_association_rec.section is not null and p_association_rec.section<>fnd_api.g_miss_char
32 Then
33 x_record:=x_record||nvl(p_association_rec.section,'')||' - ';
34 End if;
35
36 If p_association_rec.subject is not null and p_association_rec.subject<>fnd_api.g_miss_char
37 Then
38 x_record:=x_record||nvl(p_association_rec.subject,'')||' - ';
39 End if;
40
41 If p_association_rec.figure is not null and p_association_rec.figure<>fnd_api.g_miss_char
42 Then
43 x_record:=x_record||nvl(p_association_rec.figure,'')||' - ';
44 End if;
45
46 If p_association_rec.page is not null and p_association_rec.page<>fnd_api.g_miss_char
47 Then
48 x_record:=x_record||nvl(p_association_rec.page,'');
49 End if;
50 ELSIF p_association_rec.aso_object_type_code = 'MR'
51 THEN
52
53 If p_association_rec.document_no is not null and p_association_rec.document_no<>fnd_api.g_miss_char
54 Then
55 x_record:=x_record||nvl(p_association_rec.document_no,'')||' - ';
56 End if;
57
58 If p_association_rec.revision_no is not null and p_association_rec.revision_no<>fnd_api.g_miss_char
59 Then
60 x_record:=x_record||nvl(p_association_rec.revision_no,'')||' - ';
61 End if;
62
63 If p_association_rec.chapter is not null and p_association_rec.chapter<>fnd_api.g_miss_char
64 Then
65 x_record:=x_record||nvl(p_association_rec.chapter,'')||' - ';
66 End if;
67
68 If p_association_rec.section is not null and p_association_rec.section<>fnd_api.g_miss_char
69 Then
70 x_record:=x_record||nvl(p_association_rec.section,'')||' - ';
71 End if;
72
73 If p_association_rec.subject is not null and p_association_rec.subject<>fnd_api.g_miss_char
74 Then
75 x_record:=x_record||nvl(p_association_rec.subject,'')||' - ';
76 End if;
77 If p_association_rec.page is not null and p_association_rec.page<>fnd_api.g_miss_char
78 Then
79 x_record:=x_record||nvl(p_association_rec.page,'');
80 End if;
81
82 If p_association_rec.figure is not null and p_association_rec.figure<>fnd_api.g_miss_char
83 Then
84 x_record:=x_record||nvl(p_association_rec.figure,'')||' - ';
85 End if;
86 END IF;
87 End;
88
89
90 PROCEDURE DEFAULT_MISSING_ATTRIBS
91 (
92 p_x_association_tbl IN OUT NOCOPY AHL_DI_ASSO_DOCASO_PVT.association_TBL
93 )
94 AS
95 Cursor GetDocDet(C_DOC_TITLE_ASSO_ID NUMBER)
96 IS
97 SELECT A.*,B.CHAPTER,B.SECTION,B.SUBJECT,B.PAGE,B.FIGURE,B.NOTE
98 FROM AHL_DOC_TITLE_ASSOS_B a, AHL_DOC_TITLE_ASSOS_TL B
99 WHERE A.DOC_TITLE_ASSO_ID=B.DOC_TITLE_ASSO_ID
100 AND A.DOC_TITLE_ASSO_ID=C_DOC_TITLE_ASSO_ID
101 AND B.LANGUAGE=USERENV('LANG');
102
103 l_doc_assos_rec GetDocDet%rowtype;
104 BEGIN
105 IF p_x_association_tbl.count>0
106 THEN
107
108 FOR i in p_x_association_tbl.FIRST .. p_x_association_tbl.LAST
109 LOOP
110 -- bug 2979987 : pbarman : 29.5.2003
111 IF p_x_association_tbl(I).DML_OPERATION<>'D' and p_x_association_tbl(I).DML_OPERATION<>'C'
112 THEN
113 OPEN GetDocDet(p_x_association_tbl(i).DOC_TITLE_ASSO_ID);
114 FETCH GetDocDet into l_doc_assos_rec;
115 CLOSE GetDocDet;
116
117 IF p_x_association_tbl(I).DOC_TITLE_ASSO_ID= FND_API.G_MISS_NUM
118 THEN
119 p_x_association_tbl(I).DOC_TITLE_ASSO_ID:=NULL;
120 ELSIF p_x_association_tbl(I).DOC_TITLE_ASSO_ID IS NULL
121 THEN
122 p_x_association_tbl(I).DOC_TITLE_ASSO_ID:=l_doc_assos_rec.DOC_TITLE_ASSO_ID;
123 END IF;
124
125 IF p_x_association_tbl(I).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
126 THEN
127 p_x_association_tbl(I).OBJECT_VERSION_NUMBER:=NULL;
128 ELSIF p_x_association_tbl(I).OBJECT_VERSION_NUMBER IS NULL
129 THEN
130 p_x_association_tbl(I).OBJECT_VERSION_NUMBER:=l_doc_assos_rec.OBJECT_VERSION_NUMBER;
131 END IF;
132
133 IF p_x_association_tbl(I).LAST_UPDATE_DATE=FND_API.G_MISS_DATE
134 THEN
135 p_x_association_tbl(I).LAST_UPDATE_DATE:=NULL;
136 ELSIF p_x_association_tbl(I).LAST_UPDATE_DATE IS NULL
137 THEN
138 p_x_association_tbl(I).LAST_UPDATE_DATE:=l_doc_assos_rec.LAST_UPDATE_DATE;
139 END IF;
140
141 IF p_x_association_tbl(I).LAST_UPDATED_BY= FND_API.G_MISS_NUM
142 THEN
143 p_x_association_tbl(I).LAST_UPDATED_BY:=NULL;
144 ELSIF p_x_association_tbl(I).LAST_UPDATED_BY IS NULL
145 THEN
146 p_x_association_tbl(I).LAST_UPDATED_BY:=l_doc_assos_rec.LAST_UPDATED_BY;
147 END IF;
148
149 IF p_x_association_tbl(I).CREATION_DATE=FND_API.G_MISS_DATE
150 THEN
151 p_x_association_tbl(I).CREATION_DATE:=NULL;
152 ELSIF p_x_association_tbl(I).CREATION_DATE IS NULL
153 THEN
154 p_x_association_tbl(I).CREATION_DATE:=l_doc_assos_rec.CREATION_DATE;
155 END IF;
156
157 IF p_x_association_tbl(I).CREATED_BY= FND_API.G_MISS_NUM
158 THEN
159 p_x_association_tbl(I).CREATED_BY:=NULL;
160 ELSIF p_x_association_tbl(I).CREATED_BY IS NULL
161 THEN
162 p_x_association_tbl(I).CREATED_BY:=l_doc_assos_rec.CREATED_BY;
163 END IF;
164 IF p_x_association_tbl(I).LAST_UPDATE_LOGIN= FND_API.G_MISS_NUM
165 THEN
166 p_x_association_tbl(I).LAST_UPDATE_LOGIN:=NULL;
167 ELSIF p_x_association_tbl(I).LAST_UPDATE_LOGIN IS NULL
168 THEN
169 p_x_association_tbl(I).LAST_UPDATE_LOGIN:=l_doc_assos_rec.LAST_UPDATE_LOGIN;
170 END IF;
171
172 IF p_x_association_tbl(I).DOC_REVISION_ID= FND_API.G_MISS_NUM
173 THEN
174 p_x_association_tbl(I).DOC_REVISION_ID:=NULL;
175 ELSIF p_x_association_tbl(I).DOC_REVISION_ID IS NULL
176 THEN
177 p_x_association_tbl(I).DOC_REVISION_ID:=l_doc_assos_rec.DOC_REVISION_ID;
178 END IF;
179
180 IF p_x_association_tbl(I).ASO_OBJECT_TYPE_CODE= FND_API.G_MISS_CHAR
181 THEN
182 p_x_association_tbl(I).ASO_OBJECT_TYPE_CODE:=NULL;
183 ELSIF p_x_association_tbl(I).ASO_OBJECT_TYPE_CODE IS NULL
184 THEN
185 p_x_association_tbl(I).ASO_OBJECT_TYPE_CODE:=l_doc_assos_rec.ASO_OBJECT_TYPE_CODE;
186 END IF;
187
188 IF p_x_association_tbl(I).ASO_OBJECT_ID= FND_API.G_MISS_NUM
189 THEN
190 p_x_association_tbl(I).ASO_OBJECT_ID:=NULL;
191 ELSIF p_x_association_tbl(I).ASO_OBJECT_ID IS NULL
192 THEN
193 p_x_association_tbl(I).ASO_OBJECT_ID:=l_doc_assos_rec.ASO_OBJECT_ID;
194 END IF;
195
196 IF p_x_association_tbl(I).DOCUMENT_ID= FND_API.G_MISS_NUM
197 THEN
198 p_x_association_tbl(I).DOCUMENT_ID:=NULL;
199 ELSIF p_x_association_tbl(I).DOCUMENT_ID IS NULL
200 THEN
201 p_x_association_tbl(I).DOCUMENT_ID:=l_doc_assos_rec.DOCUMENT_ID;
202 END IF;
203
204 IF p_x_association_tbl(I).USE_LATEST_REV_FLAG= FND_API.G_MISS_CHAR
205 THEN
206 p_x_association_tbl(I).USE_LATEST_REV_FLAG:=NULL;
207 ELSIF p_x_association_tbl(I).USE_LATEST_REV_FLAG IS NULL
208 THEN
209 p_x_association_tbl(I).USE_LATEST_REV_FLAG:=l_doc_assos_rec.USE_LATEST_REV_FLAG;
210 END IF;
211
212 IF p_x_association_tbl(I).SERIAL_NO= FND_API.G_MISS_CHAR
213 THEN
214 p_x_association_tbl(I).SERIAL_NO:=NULL;
215 ELSIF p_x_association_tbl(I).SERIAL_NO IS NULL
216 THEN
217 p_x_association_tbl(I).SERIAL_NO:=l_doc_assos_rec.SERIAL_NO;
218 END IF;
219
220 IF p_x_association_tbl(I).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
221 THEN
222 p_x_association_tbl(I).ATTRIBUTE_CATEGORY:=NULL;
223 ELSIF p_x_association_tbl(I).ATTRIBUTE_CATEGORY IS NULL
224 THEN
225 p_x_association_tbl(I).ATTRIBUTE_CATEGORY:=l_doc_assos_rec.ATTRIBUTE_CATEGORY;
226 END IF;
227
228 IF p_x_association_tbl(I).ATTRIBUTE1= FND_API.G_MISS_CHAR
229 THEN
230 p_x_association_tbl(I).ATTRIBUTE1:=NULL;
231 ELSIF p_x_association_tbl(I).ATTRIBUTE1 IS NULL
232 THEN
233 p_x_association_tbl(I).ATTRIBUTE1:=l_doc_assos_rec.ATTRIBUTE1;
234 END IF;
235
236 IF p_x_association_tbl(I).ATTRIBUTE2= FND_API.G_MISS_CHAR
237 THEN
238 p_x_association_tbl(I).ATTRIBUTE2:=NULL;
239 ELSIF p_x_association_tbl(I).ATTRIBUTE2 IS NULL
240 THEN
241 p_x_association_tbl(I).ATTRIBUTE2:=l_doc_assos_rec.ATTRIBUTE2;
242 END IF;
243
244 IF p_x_association_tbl(I).ATTRIBUTE3= FND_API.G_MISS_CHAR
245 THEN
246 p_x_association_tbl(I).ATTRIBUTE3:=NULL;
247 ELSIF p_x_association_tbl(I).ATTRIBUTE3 IS NULL
248 THEN
249 p_x_association_tbl(I).ATTRIBUTE3:=l_doc_assos_rec.ATTRIBUTE3;
250 END IF;
251
252 IF p_x_association_tbl(I).ATTRIBUTE4= FND_API.G_MISS_CHAR
253 THEN
254 p_x_association_tbl(I).ATTRIBUTE4:=NULL;
255 ELSIF p_x_association_tbl(I).ATTRIBUTE4 IS NULL
256 THEN
257 p_x_association_tbl(I).ATTRIBUTE4:=l_doc_assos_rec.ATTRIBUTE4;
258 END IF;
259
260 IF p_x_association_tbl(I).ATTRIBUTE5= FND_API.G_MISS_CHAR
261 THEN
262 p_x_association_tbl(I).ATTRIBUTE5:=NULL;
263 ELSIF p_x_association_tbl(I).ATTRIBUTE5 IS NULL
264 THEN
265 p_x_association_tbl(I).ATTRIBUTE5:=l_doc_assos_rec.ATTRIBUTE5;
266 END IF;
267
268 IF p_x_association_tbl(I).ATTRIBUTE6= FND_API.G_MISS_CHAR
269 THEN
270 p_x_association_tbl(I).ATTRIBUTE6:=NULL;
271 ELSIF p_x_association_tbl(I).ATTRIBUTE6 IS NULL
272 THEN
273 p_x_association_tbl(I).ATTRIBUTE6:=l_doc_assos_rec.ATTRIBUTE6;
274 END IF;
275
276 IF p_x_association_tbl(I).ATTRIBUTE7= FND_API.G_MISS_CHAR
277 THEN
278 p_x_association_tbl(I).ATTRIBUTE7:=NULL;
279 ELSIF p_x_association_tbl(I).ATTRIBUTE7 IS NULL
280 THEN
281 p_x_association_tbl(I).ATTRIBUTE7:=l_doc_assos_rec.ATTRIBUTE7;
282 END IF;
283
284 IF p_x_association_tbl(I).ATTRIBUTE8= FND_API.G_MISS_CHAR
285 THEN
286 p_x_association_tbl(I).ATTRIBUTE8:=NULL;
287 ELSIF p_x_association_tbl(I).ATTRIBUTE8 IS NULL
288 THEN
289 p_x_association_tbl(I).ATTRIBUTE8:=l_doc_assos_rec.ATTRIBUTE8;
290 END IF;
291
292 IF p_x_association_tbl(I).ATTRIBUTE9= FND_API.G_MISS_CHAR
293 THEN
294 p_x_association_tbl(I).ATTRIBUTE9:=NULL;
295 ELSIF p_x_association_tbl(I).ATTRIBUTE9 IS NULL
296 THEN
297 p_x_association_tbl(I).ATTRIBUTE9:=l_doc_assos_rec.ATTRIBUTE9;
298 END IF;
299
300 IF p_x_association_tbl(I).ATTRIBUTE10= FND_API.G_MISS_CHAR
301 THEN
302 p_x_association_tbl(I).ATTRIBUTE10:=NULL;
303 ELSIF p_x_association_tbl(I).ATTRIBUTE10 IS NULL
304 THEN
305 p_x_association_tbl(I).ATTRIBUTE10:=l_doc_assos_rec.ATTRIBUTE10;
306 END IF;
307 IF p_x_association_tbl(I).ATTRIBUTE11= FND_API.G_MISS_CHAR
308 THEN
309 p_x_association_tbl(I).ATTRIBUTE11:=NULL;
310 ELSIF p_x_association_tbl(I).ATTRIBUTE11 IS NULL
311 THEN
312 p_x_association_tbl(I).ATTRIBUTE11:=l_doc_assos_rec.ATTRIBUTE11;
313 END IF;
314 IF p_x_association_tbl(I).ATTRIBUTE12= FND_API.G_MISS_CHAR
315 THEN
316 p_x_association_tbl(I).ATTRIBUTE12:=NULL;
317 ELSIF p_x_association_tbl(I).ATTRIBUTE12 IS NULL
318 THEN
319 p_x_association_tbl(I).ATTRIBUTE12:=l_doc_assos_rec.ATTRIBUTE12;
320 END IF;
321 IF p_x_association_tbl(I).ATTRIBUTE13= FND_API.G_MISS_CHAR
322 THEN
323 p_x_association_tbl(I).ATTRIBUTE13:=NULL;
324 ELSIF p_x_association_tbl(I).ATTRIBUTE13 IS NULL
325 THEN
326 p_x_association_tbl(I).ATTRIBUTE13:=l_doc_assos_rec.ATTRIBUTE13;
327 END IF;
328 IF p_x_association_tbl(I).ATTRIBUTE14= FND_API.G_MISS_CHAR
329 THEN
330 p_x_association_tbl(I).ATTRIBUTE14:=NULL;
331 ELSIF p_x_association_tbl(I).ATTRIBUTE14 IS NULL
332 THEN
333 p_x_association_tbl(I).ATTRIBUTE14:=l_doc_assos_rec.ATTRIBUTE14;
334 END IF;
335 IF p_x_association_tbl(I).ATTRIBUTE15= FND_API.G_MISS_CHAR
336 THEN
337 p_x_association_tbl(I).ATTRIBUTE15:=NULL;
338 ELSIF p_x_association_tbl(I).ATTRIBUTE15 IS NULL
339 THEN
340 p_x_association_tbl(I).ATTRIBUTE15:=l_doc_assos_rec.ATTRIBUTE15;
341 END IF;
342
343 IF p_x_association_tbl(I).SOURCE_REF_CODE= FND_API.G_MISS_CHAR
344 THEN
345 p_x_association_tbl(I).SOURCE_REF_CODE:=NULL;
346 ELSIF p_x_association_tbl(I).SOURCE_REF_CODE IS NULL
347 THEN
348 p_x_association_tbl(I).SOURCE_REF_CODE:=l_doc_assos_rec.SOURCE_REF_CODE;
349 END IF;
350 IF p_x_association_tbl(I).DOC_TITLE_ASSO_ID= FND_API.G_MISS_NUM
351 THEN
352 p_x_association_tbl(I).DOC_TITLE_ASSO_ID:=NULL;
353 ELSIF p_x_association_tbl(I).DOC_TITLE_ASSO_ID IS NULL
354 THEN
355 p_x_association_tbl(I).DOC_TITLE_ASSO_ID:=l_doc_assos_rec.DOC_TITLE_ASSO_ID;
356 END IF;
357 IF p_x_association_tbl(I).CHAPTER= FND_API.G_MISS_CHAR
358 THEN
359 p_x_association_tbl(I).CHAPTER:=NULL;
360 ELSIF p_x_association_tbl(I).CHAPTER IS NULL
361 THEN
362 p_x_association_tbl(I).CHAPTER:=l_doc_assos_rec.CHAPTER;
363 END IF;
364 IF p_x_association_tbl(I).SECTION= FND_API.G_MISS_CHAR
365 THEN
366 p_x_association_tbl(I).SECTION:=NULL;
367 ELSIF p_x_association_tbl(I).SECTION IS NULL
368 THEN
369 p_x_association_tbl(I).SECTION:=l_doc_assos_rec.SECTION;
370 END IF;
371 IF p_x_association_tbl(I).SUBJECT= FND_API.G_MISS_CHAR
372 THEN
373 p_x_association_tbl(I).SUBJECT:=NULL;
374 ELSIF p_x_association_tbl(I).SUBJECT IS NULL
375 THEN
376 p_x_association_tbl(I).SUBJECT:=l_doc_assos_rec.SUBJECT;
377 END IF;
378
379 IF p_x_association_tbl(I).PAGE= FND_API.G_MISS_CHAR
380 THEN
381 p_x_association_tbl(I).PAGE:=NULL;
382 ELSIF p_x_association_tbl(I).PAGE IS NULL
383 THEN
384 p_x_association_tbl(I).PAGE:=l_doc_assos_rec.PAGE;
385 END IF;
386
387 IF p_x_association_tbl(I).FIGURE= FND_API.G_MISS_CHAR
388 THEN
389 p_x_association_tbl(I).FIGURE:=NULL;
390 ELSIF p_x_association_tbl(I).FIGURE IS NULL
391 THEN
392 p_x_association_tbl(I).FIGURE:=l_doc_assos_rec.FIGURE;
393 END IF;
394
395 IF p_x_association_tbl(I).NOTE= FND_API.G_MISS_CHAR
396 THEN
397 p_x_association_tbl(I).NOTE:=NULL;
398 ELSIF p_x_association_tbl(I).NOTE IS NULL
399 THEN
400 p_x_association_tbl(I).NOTE:=l_doc_assos_rec.NOTE;
401 END IF;
402 END IF;
403 END LOOP;
404 END IF;
405 END;
406
407 PROCEDURE TRANS_VALUE_ID
408 (
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_x_association_tbl IN OUT NOCOPY AHL_DI_ASSO_DOCASO_PVT.association_tbl
411 )
412 as
413 CURSOR get_lookup_meaning_to_code(c_meaning VARCHAR2,c_lookup_type VARCHAR2)
414 IS
415 SELECT lookup_code
416 FROM FND_LOOKUP_VALUES_VL
417 WHERE lookup_type= c_lookup_type
418 AND upper(meaning)=upper(c_meaning)
419 AND sysdate between NVL(start_date_active,sysdate)
420 AND nvl(end_date_active,sysdate);
421
422
423 CURSOR GetDocDet(c_document_no VARCHAR2)
424 IS
425 SELECT a.document_id,
426 a.document_no,
427 a.doc_type_code,
428 a.doc_sub_type_code,
429 a.document_title,
430 b.doc_revision_id,
431 b.revision_no,
432 b.revision_status_code
433 FROM ahl_documents_vl a ,AHL_DOC_REVISIONS_B b
434 WHERE A.DOCUMENT_ID=B.DOCUMENT_ID
435 and upper(a.document_no)=upper(c_document_no);
436
437 l_doc_rec GetDocDet%rowtype;
438
439 CURSOR GetDocCheck(c_document_no VARCHAR2)
440 IS
441 SELECT count(a.document_no)
442 FROM ahl_documents_vl a ,AHL_DOC_REVISIONS_B b
443 WHERE A.DOCUMENT_ID=B.DOCUMENT_ID(+)
444 and upper(a.document_no)=upper(c_document_no);
445
446 CURSOR GetRevCheck(c_revision_no VARCHAR2)
447 IS
448 SELECT count(a.revision_no)
449 FROM AHL_DOC_REVISIONS_B a
450 WHERE upper(a.revision_no)=upper(c_revision_no);
451
452 CURSOR GetdocCount(c_document_no VARCHAR2)
453 IS
454 SELECT count(a.document_no)
455 FROM AHL_DOCUMENTS_B a,AHL_DOC_REVISIONS_B b
456 WHERE upper(a.document_no)=upper(c_document_no)
457 and a.document_id=b.document_id;
458
459
460 CURSOR GetDocDetail(c_document_no VARCHAR2,c_revision_no VARCHAR2)
461 IS
462 SELECT a.document_id,
463 a.document_no,
464 a.doc_type_code,
465 a.doc_sub_type_code,
466 a.document_title,
467 b.doc_revision_id,
468 b.revision_no,
469 b.revision_status_code
470 FROM ahl_documents_vl a ,AHL_DOC_REVISIONS_B b
471 WHERE A.DOCUMENT_ID=B.DOCUMENT_ID
472 and A.DOCUMENT_no=c_document_no
473 and b.revision_no=c_revision_no;
474
475 l_docdet_rec GetDocDet%rowtype;
476 l_object_version_number number;
477 l_num_rec NUMBER;
478 l_return_status VARCHAR2(1);
479 l_lookup_code VARCHAR2(30):='';
480 l_document_id NUMBER:=0;
481 l_counter NUMBER:=0;
482 l_counter2 NUMBER:=0;
483 l_counter3 NUMBER:=0;
484 l_check_flag VARCHAR2(1):='Y';
485 l_record VARCHAR2(4000):='';
486 BEGIN
487 x_return_status:=FND_API.G_RET_STS_SUCCESS;
488
489 IF G_DEBUG='Y' THEN
490 AHL_DEBUG_PUB.enable_debug;
491 END IF;
492 IF G_DEBUG='Y' THEN
493 AHL_DEBUG_PUB.debug( 'enter ahl_di_asso_doc_aso_pub.TRANS_VALUE_ID','+DOBJASS+');
494 END IF;
495
496 IF p_x_association_tbl.count >0
497 THEN
498
499 FOR i IN p_x_association_tbl.FIRST.. p_x_association_tbl.LAST
500 LOOP
501
502 IF p_x_association_tbl(i).DML_OPERATION<>'D'
503 THEN
504 IF p_x_association_tbl(i).aso_object_type_code IS NULL or
505 p_x_association_tbl(i).aso_object_type_code = FND_API.G_MISS_CHAR
506 Then
507 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJ_TYP_NOT_EXISTS');
508 FND_MSG_PUB.ADD;
509 END IF;
510 END IF;
511
512 RECORD_IDENTIFIER
513 (
514 p_association_rec =>p_x_association_tbl(i),
515 x_record =>l_record
516 );
517
518 IF p_x_association_tbl(i).DML_OPERATION<>'D'
519 THEN
520 IF p_x_association_tbl(i).aso_object_type_code<>'MR'
521 THEN
522 IF (p_x_association_tbl(i).document_no IS NULL or
523 p_x_association_tbl(i).document_no=FND_API.G_MISS_CHAR)
524 THEN
525 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCUMENT_NO_NULL');
526 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
527 FND_MSG_PUB.ADD;
528 IF G_DEBUG='Y' THEN
529 AHL_DEBUG_PUB.debug( 'Stage 002');
530 END IF;
531 l_check_flag:='N';
532 END IF;
533 ELSE
534 IF (p_x_association_tbl(i).document_no IS NULL or
535 p_x_association_tbl(i).document_no=FND_API.G_MISS_CHAR)
536 THEN
537 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCUMENT_NO_NULL');
538 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
539 FND_MSG_PUB.ADD;
540 IF G_DEBUG='Y' THEN
541 AHL_DEBUG_PUB.debug( 'Document number Null');
542 END IF;
543 l_check_flag:='N';
544 ELSE
545 OPEN GetDocCheck(p_x_association_tbl(i).document_no);
546 FETCH GetDocCheck INTO l_counter2;
547 IF l_counter2=0
548 THEN
549 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCUMENT_NO_INVALID');
550 FND_MESSAGE.SET_TOKEN('FIELD',p_x_association_tbl(i).document_no);
551 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
552 FND_MSG_PUB.ADD;
553 IF G_DEBUG='Y' THEN
554 AHL_DEBUG_PUB.debug( 'Document number Does not exist');
555 END IF;
556 l_check_flag:='N';
557 END IF;
558 CLOSE GetDocCheck;
559 END IF;
560 END IF;
561
562 IF l_check_flag='Y'
563 Then
564
565 SELECT count(a.document_no) into l_counter3
566 FROM ahl_documents_b a ,AHL_DOC_REVISIONS_B b
567 WHERE A.DOCUMENT_ID=B.DOCUMENT_ID
568 AND upper(a.document_no)=upper(p_x_association_tbl(i).document_no);
569
570 IF l_counter3=0
571 THEN
572 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCNO_WITHNO_REV');
573 FND_MESSAGE.SET_TOKEN('FIELD',p_x_association_tbl(i).document_no);
574 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
575 FND_MSG_PUB.ADD;
576 IF G_DEBUG='Y' THEN
577 AHL_DEBUG_PUB.debug( 'Revision for Document number does not exist');
578 END IF;
579
580 l_check_flag:='N';
581 END IF;
582 End if;
583
584 IF l_check_flag='Y'
585 THEN
586 IF (p_x_association_tbl(i).revision_no is null or
587 p_x_association_tbl(i).revision_no=fnd_api.g_miss_char)
588 THEN
589 IF l_counter2<>1
590 THEN
591 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_REVISION_NO_NULL');
592 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
593 FND_MSG_PUB.ADD;
594 IF G_DEBUG='Y' THEN
595 AHL_DEBUG_PUB.debug( 'Revision for Document is null');
596 END IF;
597
598 l_check_flag:='N';
599 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SELECT_FRM_LOV');
600 FND_MSG_PUB.ADD;
601 IF G_DEBUG='Y' THEN
602 AHL_DEBUG_PUB.debug( 'Select document from lov');
603 END IF;
604 END IF;
605 ELSE
606
607 OPEN GetRevCheck(p_x_association_tbl(i).revision_no);
608 FETCH GetRevCheck INTO l_counter2;
609 IF l_counter2=0
610 THEN
611 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_REVISION_NO_INVALID');
612 FND_MESSAGE.SET_TOKEN('FIELD',p_x_association_tbl(i).revision_no);
613 FND_MESSAGE.SET_TOKEN('RECORD',l_record,false);
614 FND_MSG_PUB.ADD;
615 IF G_DEBUG='Y' THEN
616 AHL_DEBUG_PUB.debug( 'Revision for Document number is invalid');
617 END IF;
618
619 l_check_flag:='N';
620 END IF;
621 CLOSE GetRevCheck;
622 END IF;
623 END IF;
624
625 IF l_check_flag='Y'
626 THEN
627 OPEN GetdocCount(p_x_association_tbl(i).document_no);
628 FETCH GetdocCount INTO l_counter;
629 IF l_counter=0
630 THEN
631 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOC_REV_COMB_INVLD');
632 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_association_tbl(i).revision_no);
633 FND_MESSAGE.SET_TOKEN('FIELD2',p_x_association_tbl(i).document_no);
634 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
635 FND_MSG_PUB.ADD;
636 IF G_DEBUG='Y' THEN
637 AHL_DEBUG_PUB.debug( 'Revision and Document number combination does not exist');
638 END IF;
639
640 END IF;
641 CLOSE GetdocCount;
642 IF l_counter=1
643 THEN
644 OPEN GetDocDet(p_x_association_tbl(i).document_no);
645 FETCH GetDocDet INTO l_doc_rec;
646
647 IF GetDocDet%FOUND
648 THEN
649 p_x_association_tbl(i).document_id :=l_doc_rec.document_id;
650 p_x_association_tbl(i).revision_no:=l_doc_rec.Revision_no;
651 p_x_association_tbl(i).doc_revision_id:=l_doc_rec.doc_Revision_id;
652 ELSE
653 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOC_REV_COMB_INVLD');
654 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_association_tbl(i).revision_no);
655 FND_MESSAGE.SET_TOKEN('FIELD2',p_x_association_tbl(i).document_no);
656 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
657 FND_MSG_PUB.ADD;
658
659 IF G_DEBUG='Y' THEN
660 AHL_DEBUG_PUB.debug( 'Revision and Document number combination does not exist');
661 END IF;
662 END IF;
663 CLOSE GetDocDet;
664
665 ELSIF l_counter>1
666 THEN
667 OPEN GetDocDetail(p_x_association_tbl(i).document_NO,p_x_association_tbl(i).revision_no);
668 FETCH GetDocDetail INTO l_docdet_rec;
669 IF GetDocDetail%FOUND
670 THEN
671 IF l_docdet_rec.document_no=p_x_association_tbl(i).document_no
672 and l_docdet_rec.revision_no=p_x_association_tbl(i).revision_no
673 THEN
674 p_x_association_tbl(i).document_id :=l_docdet_rec.document_id;
675 p_x_association_tbl(i).revision_no:=l_docdet_rec.Revision_no;
676 p_x_association_tbl(i).doc_revision_id:=l_docdet_rec.doc_Revision_id;
677 ELSE
678 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SELECT_FRM_LOV');
679 FND_MSG_PUB.ADD;
680 END IF;
681 ELSE
682 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOC_REV_COMB_INVLD');
683 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_association_tbl(i).revision_no);
684 FND_MESSAGE.SET_TOKEN('FIELD2',p_x_association_tbl(i).document_no);
685 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
686 FND_MSG_PUB.ADD;
687 END IF;
688 CLOSE GetDocDetail;
689 END IF;
690 END IF;
691
692 END IF;
693 END LOOP;
694 END IF;
695 END;
696
697 PROCEDURE VALIDATE_DOC_ASSOCIATION
698 (
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2,
702 p_association_rec IN association_rec
703 )
704 as
705 CURSOR GetRevDet(c_doc_revision_id NUMBER)
706 IS
707 SELECT REVISION_STATUS_CODE,
708 OBSOLETE_DATE,
709 REVISION_NO
710 FROM AHL_DOC_REVISIONS_B
711 WHERE DOC_REVISION_ID = c_doc_revision_id;
712
713 l_Rev_rec1 GetRevDet%rowtype;
714
715
716 --Cursor to retrieve Aso Object Type Code
717
718 CURSOR get_aso_obj_type_code(c_aso_object_type_code VARCHAR2)
719 IS
720 SELECT lookup_code
721 FROM FND_LOOKUP_VALUES_VL
722 WHERE lookup_code = c_aso_object_type_code
723 AND lookup_type = 'AHL_OBJECT_TYPE'
724 AND sysdate between start_date_active
725 AND nvl(end_date_active,sysdate);
726
727 --Cursor to retrive the doc title record
728 CURSOR get_doc_assos_rec_b_info (c_doc_title_asso_id NUMBER)
729 IS
730 SELECT document_id,
731 doc_revision_id,
732 use_latest_rev_flag,
733 aso_object_type_code,
734 aso_object_id
735 FROM AHL_DOC_TITLE_ASSOS_B
736 WHERE doc_title_asso_id = c_doc_title_asso_id;
737
738 -- Used to validate the document id
739 CURSOR check_doc_info(c_document_id NUMBER)
740 IS
741 SELECT 'X'
742 FROM AHL_DOCUMENTS_B
743 WHERE document_id = c_document_id;
744 --
745 CURSOR get_doc_det(c_document_id NUMBER)
746 IS
747 SELECT document_no
748 FROM AHL_DOCUMENTS_B
749 WHERE document_id = c_document_id;
750
751 CURSOR get_operation_status(c_operation_id NUMBER)
752 IS
753 SELECT revision_status_code
754 FROM ahl_operations_b
755 WHERE operation_id = c_operation_id;
756
757 CURSOR get_route_status(c_route_id NUMBER)
758 IS
759 SELECT revision_status_code
760 FROM ahl_routes_b
761 WHERE route_id = c_route_id;
762
763 CURSOR dup_rec(c_aso_object_type_code VARCHAR2,
764 c_aso_object_id NUMBER,
765 c_document_id NUMBER,
766 c_doc_revision_id NUMBER,
767 c_source_ref_code VARCHAR2,
768 c_serial_no VARCHAR2,
769 c_chapter VARCHAR2,
770 c_section VARCHAR2,
771 c_subject VARCHAR2,
772 c_page VARCHAR2,
773 c_figure VARCHAR2)
774 IS
775 SELECT DOC_TITLE_ASSO_ID,aso_object_id,aso_object_type_code,document_id,doc_revision_id
776 FROM AHL_DOC_TITLE_ASSOS_vl
777 WHERE aso_object_id = nvl(c_aso_object_id,0)
778 AND NVL(aso_object_type_code,'X') = NVL(c_aso_object_type_code,'X')
779 AND document_id = nvl(c_document_id,0)
780 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0)
781 AND NVL(SOURCE_REF_CODE,'X')=NVL(C_SOURCE_REF_CODE,'X')
782 AND NVL(SERIAL_NO,'X')=NVL(C_SERIAL_NO,'X')
783 AND NVL(chapter,'X') =NVL(c_chapter,'X')
784 AND NVL(section,'X') =NVL(c_section,'X')
785 AND NVL(subject,'X') =NVL(c_subject,'X')
786 AND NVL(page,'X') =NVL(c_page,'X')
787 AND NVL(figure,'X') =NVL(c_figure,'X');
788
789 l_dup_rec dup_rec%rowtype;
790
791 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)
792 IS
793 SELECT *
794 FROM AHL_DOC_TITLE_ASSOS_B
795 WHERE aso_object_id=c_aso_object_id
796 AND aso_object_type_code=c_aso_object_type_code
797 AND document_id=c_document_id
798 AND use_latest_rev_flag<>nvl(c_use_latest_rev_flag,'X')
799 AND DOC_TITLE_ASSO_ID <> NVL(C_DOC_TITLE_ASSO_ID,0);
800 l_lat_rec CheckLatestRevFlag%rowtype;
801
802 l_dummy VARCHAR2(2000);
803 l_doc_title_asso_id NUMBER;
804 l_document_id NUMBER;
805 l_doc_revision_id NUMBER;
806 l_document_no VARCHAR2(80);
807 l_use_latest_rev_flag VARCHAR2(1);
808 l_aso_object_type_code VARCHAR2(30);
809 l_aso_object_id NUMBER;
810 l_status VARCHAR2(30);
811 l_obsolete_date DATE;
812 l_object_version_number NUMBER;
813 l_api_name CONSTANT VARCHAR2(30):= 'VALIDATE_DOC_ASSOCIATION';
814 l_api_version CONSTANT NUMBER:=1.0;
815 l_num_rec NUMBER;
816 l_msg_count NUMBER;
817 l_msg_data VARCHAR2(2000);
818 l_return_status VARCHAR2(1);
819 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
820 l_counter NUMBER:=0;
821 l_counter1 NUMBER:=0;
822 l_lookup_code VARCHAR2(30):='';
823 l_record VARCHAR2(4000):='';
824 l_type_code VARCHAR2(30);
825
826 BEGIN
827 x_return_status:=FND_API.G_RET_STS_SUCCESS;
828 IF G_DEBUG='Y' THEN
829 AHL_DEBUG_PUB.enable_debug;
830 END IF;
831
832 -- Debug info.
833
834 IF G_DEBUG='Y' THEN
835 AHL_DEBUG_PUB.debug( 'enter ahl_di_asso_doc_aso_pub.VALIDATE_DOC_ASSOCIATION','+DOBJASS+');
836 END IF;
837
838
839
840 IF p_association_rec.dml_operation <> 'D'
841 THEN
842 RECORD_IDENTIFIER
843 (
844 p_association_rec =>p_association_rec,
845 x_record =>l_record
846 );
847
848 IF p_association_rec.aso_object_type_code = 'MR'
849 THEN
850 IF g_pm_install<>'Y'
851 THEN
852
853 IF p_association_rec.ASO_OBJECT_ID IS NOT NULL OR p_association_rec.ASO_OBJECT_ID<>FND_API.G_MISS_NUM
854 THEN
855 SELECT TYPE_CODE INTO l_type_code
856 FROM AHL_MR_HEADERS_B
857 WHERE MR_HEADER_ID=p_association_rec.ASO_OBJECT_ID;
858
859 IF L_TYPE_CODE='PROGRAM'
860 THEN
861 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MR_NOTEDITABLE');
862 FND_MSG_PUB.ADD;
863 END IF;
864
865 END IF;
866
867
868 END IF;
869
870 END IF;
871
872
873 IF p_association_rec.doc_title_asso_id IS NOT NULL AND p_association_rec.doc_title_asso_id <> FND_API.G_MISS_NUM
874 THEN
875 OPEN get_doc_assos_rec_b_info(p_association_rec.doc_title_asso_id);
876 FETCH get_doc_assos_rec_b_info INTO l_document_id,
877 l_doc_revision_id,
878 l_use_latest_rev_flag,
879 l_aso_object_type_code,
880 l_aso_object_id;
881 CLOSE get_doc_assos_rec_b_info;
882 END IF;
883
884 OPEN get_doc_det(p_association_rec.document_id);
885 FETCH get_doc_det INTO l_document_no;
886 CLOSE get_doc_det;
887
888 IF p_association_rec.aso_object_type_code = 'OPERATION' THEN
889 OPEN get_operation_status(p_association_rec.aso_object_id);
890 FETCH get_operation_status INTO l_status;
891 CLOSE get_operation_status;
892 IF l_status <> 'DRAFT' AND l_status <> 'APPROVAL_REJECTED'
893 THEN
894 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_DRFT_ASO');
895 FND_MSG_PUB.ADD;
896 RETURN;
897 END IF;
898 END IF;
899
900 IF p_association_rec.aso_object_type_code = 'ROUTE' THEN
901 OPEN get_route_status(p_association_rec.aso_object_id);
902 FETCH get_route_status INTO l_status;
903 CLOSE get_route_status;
904
905 IF l_status <> 'DRAFT' AND l_status <> 'APPROVAL_REJECTED'
906 THEN
907 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROU_STAT_DRFT_ASO');
908 FND_MSG_PUB.ADD;
909 RETURN;
910 END IF;
911 END IF;
912
913 IF p_association_rec.doc_revision_id IS NOT NULL and p_association_rec.doc_revision_id <> FND_API.G_MISS_NUM
914 THEN
915 OPEN GetRevDet(p_association_rec.doc_revision_id);
916 FETCH GetRevDet INTO l_rev_rec1;
917 IF GetRevDet%notfound
918 then
919 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_INVALID');
920 FND_MESSAGE.SET_TOKEN('field',l_record);
921 FND_MSG_PUB.ADD;
922 RETURN;
923 else
924 IF TRUNC(NVL(l_rev_rec1.obsolete_date,SYSDATE+1)) < TRUNC(sysdate)
925 THEN
926 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_OBSOLETE');
927 FND_MESSAGE.SET_TOKEN('FIELD1',p_association_rec.document_no);
928 FND_MESSAGE.SET_TOKEN('FIELD2',l_rev_Rec1.REVISION_NO);
929 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
930 FND_MSG_PUB.ADD;
931 END IF;
932 END IF;
933
934
935 CLOSE GetRevDet;
936 END IF;
937
938
939 IF ((p_association_rec.doc_title_asso_id IS NULL OR
940 p_association_rec.doc_title_asso_id = FND_API.G_MISS_NUM) AND
941 (p_association_rec.document_id IS NULL OR p_association_rec.document_id = FND_API.G_MISS_NUM))
942 OR
943 ((p_association_rec.doc_title_asso_id IS NOT NULL OR
944 p_association_rec.doc_title_asso_id <> FND_API.G_MISS_NUM) AND l_document_id IS NULL)
945 THEN
946 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
947 FND_MSG_PUB.ADD;
948 END IF;
949
950 -- This condition checks for Aso Object Type Code is Null
951
952 IF ((p_association_rec.doc_title_asso_id IS NULL OR
953 p_association_rec.doc_title_asso_id = FND_API.G_MISS_NUM) AND
954 (p_association_rec.aso_object_type_code IS NULL OR
955 p_association_rec.aso_object_type_code = FND_API.G_MISS_CHAR))
956 OR
957 ((p_association_rec.doc_title_asso_id IS NOT NULL OR
958 p_association_rec.doc_title_asso_id <> FND_API.G_MISS_NUM)
959 AND l_aso_object_type_code IS NULL)
960 THEN
961 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJECT_TYPE_NULL');
962 FND_MSG_PUB.ADD;
963 END IF;
964
965 -- This condiiton checks for Aso Object Id Value Is Null
966 IF ((p_association_rec.doc_title_asso_id IS NULL OR
967 p_association_rec.doc_title_asso_id = FND_API.G_MISS_NUM) AND
968 (p_association_rec.aso_object_id IS NULL OR
969 p_association_rec.aso_object_id = FND_API.G_MISS_NUM))
970 OR
971 ((p_association_rec.doc_title_asso_id IS NOT NULL OR
972 p_association_rec.doc_title_asso_id <> FND_API.G_MISS_NUM) AND l_aso_object_id IS NULL)
973 THEN
974 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJECT_ID_NULL');
975 FND_MSG_PUB.ADD;
976 END IF;
977
978 --Check for Aso Object Type Code in fnd lookups
979
980 IF p_association_rec.aso_object_type_code IS NOT NULL AND
981 p_association_rec.aso_object_type_code <> FND_API.G_MISS_CHAR
982 THEN
983 OPEN get_aso_obj_type_code(p_association_rec.aso_object_type_code);
984 FETCH get_aso_obj_type_code INTO l_dummy;
985 IF get_aso_obj_type_code%NOTFOUND
986 THEN
987 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJ_TYPE_NOT_EXISTS');
988 FND_MSG_PUB.ADD;
989 END IF;
990 CLOSE get_aso_obj_type_code;
991 END IF;
992
993 -- Validates for existence of document id in ahl documents table
994
995 IF p_association_rec.DML_OPERATION<>'D'
996 THEN
997
998 OPEN dup_rec(p_association_rec.aso_object_type_code,
999 p_association_rec.aso_object_id,
1000 p_association_rec.document_id,
1001 p_association_rec.doc_revision_id,
1002 p_association_Rec.source_Ref_code,
1003 p_association_Rec.serial_no,
1004 p_association_Rec.chapter,
1005 p_association_Rec.section,
1006 p_association_Rec.subject,
1007 p_association_Rec.page,
1008 p_association_Rec.figure
1009 );
1010 FETCH dup_rec INTO l_dup_rec;
1011 IF dup_Rec%found
1012 then
1013 IF p_association_rec.DML_OPERATION='C'
1014 THEN
1015 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TABDOC_ASSOS_DUP_RECORD');
1016 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1017 FND_MSG_PUB.ADD;
1018 END IF;
1019 --bug fix : pbarman : May 23 rd 2003
1020 --IF p_association_rec.DML_OPERATION='U'
1021 --THEN
1022
1023 -- IF nvl(p_association_rec.doc_title_asso_id,0)<>nvl(l_dup_rec.doc_title_asso_id,0)
1024 -- THEN
1025
1026
1027 -- FND_MESSAGE.SET_NAME('AHL','AHL_DI_TABDOC_ASSOS_DUP_RECORD');
1028 -- FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1029 -- FND_MSG_PUB.ADD;
1030 -- IF G_DEBUG='Y' THEN
1031 -- AHL_DEBUG_PUB.debug( 'Dup_record Not Found' ,'+DOBJASS+');
1032 -- END IF;
1033 -- END IF;
1034
1035
1036 --END IF;
1037 END IF;
1038 CLOSE dup_rec;
1039
1040
1041 -- Latest Rev Flag Check.
1042 SELECT count(*) into l_counter1
1043 FROM AHL_DOC_TITLE_ASSOS_B
1044 WHERE aso_object_id=nvl(p_association_rec.aso_object_id,0)
1045 AND aso_object_type_code=nvl(p_association_rec.aso_object_type_code,'x')
1046 AND document_id=nvl(p_association_rec.document_id,0)
1047 AND nvl(use_latest_rev_flag,'N')='Y'
1048 AND NVL(p_association_rec.DOC_TITLE_ASSO_ID,0)=0;
1049
1050
1051 SELECT count(*) into l_counter
1052 FROM AHL_DOC_TITLE_ASSOS_B
1053 WHERE aso_object_id=nvl(p_association_rec.aso_object_id,0)
1054 AND aso_object_type_code=nvl(p_association_rec.aso_object_type_code,'x')
1055 AND document_id=nvl(p_association_rec.document_id,0)
1056 AND use_latest_rev_flag<>NVL(p_association_rec.use_latest_rev_flag,'N');
1057
1058 if l_counter1>0
1059 then
1060 FND_MESSAGE.SET_NAME('AHL','AHL_DI_USE_LATEST_DUP_YES');
1061 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1062 FND_MSG_PUB.ADD;
1063
1064 elsif (l_counter>0 and p_association_rec.DML_OPERATION='C') OR (l_counter>1 and p_association_rec.DML_OPERATION='U')
1065 then
1066 open CheckLatestRevFlag(NVL(p_association_rec.DOC_TITLE_ASSO_ID,0),p_association_rec.aso_object_id,p_association_rec.aso_object_type_code,p_association_rec.document_id,NVL(p_association_rec.use_latest_rev_flag,'X'));
1067 fetch CheckLatestRevFlag intO l_lat_rec;
1068 IF CheckLatestRevFlag%FOUND
1069 THEN
1070 FND_MESSAGE.SET_NAME('AHL','AHL_DI_USE_LATEST_FLAG');
1071 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1072 FND_MSG_PUB.ADD;
1073 END IF;
1074 close CheckLatestRevFlag;
1075 end if;
1076
1077
1078 END IF;
1079
1080 END IF;
1081 IF G_DEBUG='Y' THEN
1082 AHL_DEBUG_PUB.debug( 'exit ahl_di_asso_doc_aso_pub.VALIDATE_DOC_ASSOCIATION','+DOBJASS+');
1083 END IF;
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1086 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1088 p_count => x_msg_count,
1089 p_data => x_msg_data);
1090 WHEN FND_API.G_EXC_ERROR THEN
1091 X_return_status := FND_API.G_RET_STS_ERROR;
1092 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1093 p_count => x_msg_count,
1094 p_data => X_msg_data);
1095 WHEN OTHERS THEN
1096 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1097 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1098 THEN
1099 fnd_msg_pub.add_exc_msg(p_pkg_name =>g_pkg_name,
1100 p_procedure_name =>'VALIDATE_DOC_ASSOCIATION',
1101 p_error_text =>SUBSTR(SQLERRM,1,240));
1102 END IF;
1103 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1104 p_count => x_msg_count,
1105 p_data => X_msg_data);
1106 END;
1107
1108
1109 PROCEDURE PROCESS_ASSOCIATION
1110 (
1111 p_api_version IN NUMBER := 1.0,
1112 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1113 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1114 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1115 p_default IN VARCHAR2 := FND_API.G_FALSE,
1116 p_module_type IN VARCHAR2 ,
1117 x_return_status OUT NOCOPY VARCHAR2,
1118 x_msg_count OUT NOCOPY NUMBER,
1119 x_msg_data OUT NOCOPY VARCHAR2,
1120 p_x_association_tbl IN OUT NOCOPY association_tbl)
1121 IS
1122
1123 --cursor to check for duplicate records : pbarman 23rd May 2003
1124 CURSOR dup_rec(c_aso_object_type_code VARCHAR2,
1125 c_aso_object_id NUMBER,
1126 c_document_id NUMBER,
1127 c_doc_revision_id NUMBER,
1128 c_source_ref_code VARCHAR2,
1129 c_serial_no VARCHAR2,
1130 c_chapter VARCHAR2,
1131 c_section VARCHAR2,
1132 c_subject VARCHAR2,
1133 c_page VARCHAR2,
1134 c_figure VARCHAR2)
1135 IS
1136 SELECT DOC_TITLE_ASSO_ID
1137 FROM AHL_DOC_TITLE_ASSOS_vl
1138 WHERE aso_object_id = nvl(c_aso_object_id,0)
1139 AND NVL(aso_object_type_code,'X') = NVL(c_aso_object_type_code,'X')
1140 AND document_id = nvl(c_document_id,0)
1141 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0)
1142 AND NVL(SOURCE_REF_CODE,'X')=NVL(C_SOURCE_REF_CODE,'X')
1143 AND NVL(SERIAL_NO,'X')=NVL(C_SERIAL_NO,'X')
1144 AND NVL(chapter,'X') =NVL(c_chapter,'X')
1145 AND NVL(section,'X') =NVL(c_section,'X')
1146 AND NVL(subject,'X') =NVL(c_subject,'X')
1147 AND NVL(page,'X') =NVL(c_page,'X')
1148 AND NVL(figure,'X') =NVL(c_figure,'X');
1149
1150 --To retrieve document id
1151 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ASSOCIATION';
1152 l_api_version CONSTANT NUMBER := 1.0;
1153 l_msg_count NUMBER;
1154 l_msg_data VARCHAR2(2000);
1155 l_return_status VARCHAR2(1);
1156 l_document_id NUMBER;
1157 l_doc_revision_id NUMBER;
1158 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
1159 l_rowid VARCHAR2(30);
1160 l_found_flag VARCHAR2(5) := 'N';
1161 l_doc_title_asso_id NUMBER;
1162 l_record VARCHAR2(4000):='';
1163
1164 BEGIN
1165 -- Standard Start of API savepoint
1166 SAVEPOINT process_association;
1167 -- Check if API is called in debug mode. If yes, enable debug.
1168
1169 IF G_DEBUG='Y' THEN
1170 AHL_DEBUG_PUB.enable_debug;
1171 END IF;
1172
1173 -- Debug info.
1174
1175 IF G_DEBUG='Y' THEN
1176 AHL_DEBUG_PUB.debug( 'enter ahl_di_asso_doc_aso_pub.Process Association','+DOBJASS+');
1177 END IF;
1178
1179 -- Initialize message list if p_init_msg_list is set to TRUE.
1180 IF FND_API.to_boolean(p_init_msg_list)
1181 THEN
1182 FND_MSG_PUB.initialize;
1183 END IF;
1184
1185 -- Initialize API return status to success
1186
1187 x_return_status := 'S';
1188
1189 -- Standard call to check for call compatibility.
1190 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1191 p_api_version,
1192 l_api_name,G_PKG_NAME)
1193 THEN
1194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1195 END IF;
1196
1197
1198 --Module type is 'JSP' then make it null for the following fields
1199
1200 IF p_x_association_tbl.count >0
1201 THEN
1202 FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
1203 LOOP
1204 IF (p_module_type = 'JSP') THEN
1205 IF p_x_association_tbl(i).DML_OPERATION<>'D'
1206 THEN
1207 p_x_association_tbl(i).document_id:=null;
1208 END IF;
1209 END IF;
1210 END LOOP;
1211
1212 IF FND_API.to_boolean(p_default)
1213 THEN
1214 DEFAULT_MISSING_ATTRIBS
1215 (
1216 p_x_association_tbl =>p_x_association_tbl
1217 );
1218 END IF;
1219
1220 TRANS_VALUE_ID
1221 (
1222 x_return_status =>x_return_Status,
1223 p_x_association_tbl =>p_x_association_tbl
1224 );
1225
1226 l_msg_count := FND_MSG_PUB.count_msg;
1227
1228 IF l_msg_count > 0 THEN
1229 X_msg_count := l_msg_count;
1230 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 RAISE FND_API.G_EXC_ERROR;
1232 END IF;
1233
1234 IF G_DEBUG='Y' THEN
1235 AHL_DEBUG_PUB.debug( 'before modify');
1236 END IF;
1237
1238 FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
1239 LOOP
1240 l_return_status := FND_API.G_RET_STS_SUCCESS;
1241 l_msg_count:=0;
1242 IF l_msg_count > 0 THEN
1243 l_msg_count := FND_MSG_PUB.count_msg;
1244 l_msg_count := l_msg_count;
1245 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 END IF;
1247
1248 VALIDATE_DOC_ASSOCIATION
1249 (
1250 x_return_status =>l_return_Status,
1251 x_msg_count =>l_msg_count,
1252 x_msg_data =>l_msg_data,
1253 p_association_rec =>p_x_association_tbl(i));
1254
1255 -- check whether the record is there in the database if no then no error.
1256 -- if yes then check whether the record is there in tbl_rec.
1257 -- check only for the rec with doc_title_asso_id = doc_title_asso_id of rec that has aduplicate in database
1258 -- if such a record not found in tbl_rec then throw error.
1259 -- bug no 2918260 : pbarman : 23 rd April 2003
1260
1261 IF p_x_association_tbl(i).DML_OPERATION='U'
1262 THEN
1263 OPEN dup_rec(p_x_association_tbl(i).aso_object_type_code,
1264 p_x_association_tbl(i).aso_object_id,
1265 p_x_association_tbl(i).document_id,
1266 p_x_association_tbl(i).doc_revision_id,
1267 p_x_association_tbl(i).source_Ref_code,
1268 p_x_association_tbl(i).serial_no,
1269 p_x_association_tbl(i).chapter,
1270 p_x_association_tbl(i).section,
1271 p_x_association_tbl(i).subject,
1272 p_x_association_tbl(i).page,
1273 p_x_association_tbl(i).figure
1274 );
1275 FETCH dup_rec INTO l_doc_title_asso_id;
1276 IF dup_Rec%found
1277 THEN
1278 FOR j IN (i+1)..p_x_association_tbl.LAST
1279 LOOP
1280 IF( p_x_association_tbl(j).doc_title_asso_id = l_doc_title_asso_id)
1281 THEN
1282 l_found_flag := 'Y';
1283 IF(p_x_association_tbl(j).aso_object_type_code = p_x_association_tbl(i).aso_object_type_code AND
1284 p_x_association_tbl(j).aso_object_id = p_x_association_tbl(i).aso_object_id AND
1285 p_x_association_tbl(j).document_id = p_x_association_tbl(i).document_id AND
1286 p_x_association_tbl(j).doc_revision_id = p_x_association_tbl(i).doc_revision_id AND
1287 p_x_association_tbl(j).source_Ref_code = p_x_association_tbl(i).source_Ref_code AND
1288 p_x_association_tbl(j).serial_no = p_x_association_tbl(i).serial_no AND
1289 p_x_association_tbl(j).chapter = p_x_association_tbl(i).chapter AND
1290 p_x_association_tbl(j).section = p_x_association_tbl(i).section AND
1291 p_x_association_tbl(j).subject = p_x_association_tbl(i).subject AND
1292 p_x_association_tbl(j).page = p_x_association_tbl(i).page AND
1293 p_x_association_tbl(j).figure = p_x_association_tbl(i).figure
1294 )
1295 THEN
1296
1297
1298 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TABDOC_ASSOS_DUP_RECORD');
1299 RECORD_IDENTIFIER
1300 (
1301 p_association_rec =>p_x_association_tbl(i),
1302 x_record =>l_record
1303 );
1304
1305 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1306 FND_MSG_PUB.ADD;
1307 RAISE FND_API.G_EXC_ERROR;
1308
1309 END IF;
1310 END IF;
1311
1312 END LOOP;
1313
1314 IF l_found_flag = 'N'
1315 THEN
1316
1317 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TABDOC_ASSOS_DUP_RECORD');
1318 RECORD_IDENTIFIER
1319 (
1320 p_association_rec =>p_x_association_tbl(i),
1321 x_record =>l_record
1322 );
1323
1324 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
1325 FND_MSG_PUB.ADD;
1326 RAISE FND_API.G_EXC_ERROR;
1327 END IF;
1328
1329 END IF;
1330
1331 CLOSE dup_Rec;
1332 END IF;
1333
1334 IF l_return_status= FND_API.G_RET_STS_SUCCESS
1335 THEN
1336
1337 IF p_x_association_tbl(i).DML_OPERATION ='D'
1338 THEN
1339 delete from AHL_DOC_TITLE_ASSOS_TL
1340 where DOC_TITLE_ASSO_ID = p_x_association_tbl(I).DOC_TITLE_ASSO_ID;
1341 IF (sql%notfound)
1342 THEN
1343 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1344 FND_MSG_PUB.ADD;
1345 ELSE
1346 delete from AHL_DOC_TITLE_ASSOS_B
1347 where DOC_TITLE_ASSO_ID = p_x_association_tbl(I).DOC_TITLE_ASSO_ID
1348 and OBJECT_VERSION_NUMBER=p_x_association_tbl(I).OBJECT_VERSION_NUMBER;
1349
1350 IF (sql%notfound)
1351 THEN
1352 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1353 FND_MSG_PUB.ADD;
1354 END IF;
1355 END IF;
1356 ELSIF p_x_association_tbl(i).DML_OPERATION ='U'
1357 THEN
1358 update AHL_DOC_TITLE_ASSOS_B set
1359 SERIAL_NO = p_x_association_tbl(i).SERIAL_NO,
1360 ATTRIBUTE_CATEGORY = p_x_association_tbl(i).ATTRIBUTE_CATEGORY,
1361 ATTRIBUTE1 = p_x_association_tbl(i).ATTRIBUTE1,
1362 ATTRIBUTE2 = p_x_association_tbl(i).ATTRIBUTE2,
1363 ATTRIBUTE3 = p_x_association_tbl(i).ATTRIBUTE3,
1364 ATTRIBUTE4 = p_x_association_tbl(i).ATTRIBUTE4,
1365 ATTRIBUTE5 = p_x_association_tbl(i).ATTRIBUTE5,
1366 ATTRIBUTE6 = p_x_association_tbl(i).ATTRIBUTE6,
1367 ATTRIBUTE7 = p_x_association_tbl(i).ATTRIBUTE7,
1368 ATTRIBUTE8 = p_x_association_tbl(i).ATTRIBUTE8,
1369 ATTRIBUTE9 = p_x_association_tbl(i).ATTRIBUTE9,
1370 ATTRIBUTE10 = p_x_association_tbl(i).ATTRIBUTE10,
1371 ATTRIBUTE11 = p_x_association_tbl(i).ATTRIBUTE11,
1372 ATTRIBUTE12 = p_x_association_tbl(i).ATTRIBUTE12,
1373 ATTRIBUTE13 = p_x_association_tbl(i).ATTRIBUTE13,
1374 ATTRIBUTE14 = p_x_association_tbl(i).ATTRIBUTE14,
1375 ATTRIBUTE15 = p_x_association_tbl(i).ATTRIBUTE15,
1376 ASO_OBJECT_TYPE_CODE = p_x_association_tbl(i).ASO_OBJECT_TYPE_CODE,
1377 SOURCE_REF_CODE = p_x_association_tbl(i).SOURCE_REF_CODE,
1378 ASO_OBJECT_ID = p_x_association_tbl(i).ASO_OBJECT_ID,
1379 DOCUMENT_ID = p_x_association_tbl(i).DOCUMENT_ID,
1380 USE_LATEST_REV_FLAG= p_x_association_tbl(i).USE_LATEST_REV_FLAG,
1381 DOC_REVISION_ID = p_x_association_tbl(i).DOC_REVISION_ID,
1382 OBJECT_VERSION_NUMBER = p_x_association_tbl(i).OBJECT_VERSION_NUMBER+1,
1383 LAST_UPDATE_DATE =SYSDATE,
1384 LAST_UPDATED_BY =fnd_global.user_id,
1385 LAST_UPDATE_LOGIN =fnd_global.user_id
1386 where DOC_TITLE_ASSO_ID = p_x_association_tbl(i).DOC_TITLE_ASSO_ID
1387 and OBJECT_VERSION_NUMBER=p_x_association_tbl(I).OBJECT_VERSION_NUMBER;
1388
1389
1390 IF (sql%notfound)
1391 THEN
1392 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1393 FND_MSG_PUB.ADD;
1394 ELSE
1395 update AHL_DOC_TITLE_ASSOS_TL set
1396 CHAPTER = p_x_association_tbl(i).CHAPTER,
1397 SECTION = p_x_association_tbl(i).SECTION,
1398 SUBJECT = p_x_association_tbl(i).SUBJECT,
1399 FIGURE = p_x_association_tbl(i).FIGURE,
1400 PAGE = p_x_association_tbl(i).PAGE,
1401 NOTE = p_x_association_tbl(i).NOTE,
1402 LAST_UPDATE_DATE = p_x_association_tbl(i).LAST_UPDATE_DATE,
1403 LAST_UPDATED_BY = p_x_association_tbl(i).LAST_UPDATED_BY,
1404 LAST_UPDATE_LOGIN = p_x_association_tbl(i).LAST_UPDATE_LOGIN,
1405 SOURCE_LANG = userenv('LANG')
1406 where DOC_TITLE_ASSO_ID = p_x_association_tbl(i).DOC_TITLE_ASSO_ID
1407 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1408 IF (sql%notfound)
1409 THEN
1410 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1411 FND_MSG_PUB.ADD;
1412 END IF;
1413 END IF;
1414 ELSIF p_x_association_tbl(i).DML_OPERATION ='C'
1415 THEN
1416 SELECT AHL_DOC_TITLE_ASSOS_B_S.Nextval INTO
1417 p_x_association_tbl(i).doc_title_asso_id from DUAL;
1418
1419 AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
1420 X_ROWID =>l_rowid,
1421 X_DOC_TITLE_ASSO_ID =>p_x_association_tbl(i).doc_title_asso_id,
1422 X_SERIAL_NO =>p_x_association_tbl(i).serial_no,
1423 X_ATTRIBUTE_CATEGORY =>p_x_association_tbl(i).attribute_category,
1424 X_ATTRIBUTE1 =>p_x_association_tbl(i).attribute1,
1425 X_ATTRIBUTE2 =>p_x_association_tbl(i).attribute2,
1426 X_ATTRIBUTE3 =>p_x_association_tbl(i).attribute3,
1427 X_ATTRIBUTE4 =>p_x_association_tbl(i).attribute4,
1428 X_ATTRIBUTE5 =>p_x_association_tbl(i).attribute5,
1429 X_ATTRIBUTE6 =>p_x_association_tbl(i).attribute6,
1430 X_ATTRIBUTE7 =>p_x_association_tbl(i).attribute7,
1431 X_ATTRIBUTE8 => p_x_association_tbl(i).attribute8,
1432 X_ATTRIBUTE9 =>p_x_association_tbl(i).attribute9,
1433 X_ATTRIBUTE10 =>p_x_association_tbl(i).attribute10,
1434 X_ATTRIBUTE11 =>p_x_association_tbl(i).attribute11,
1435 X_ATTRIBUTE12 =>p_x_association_tbl(i).attribute12,
1436 X_ATTRIBUTE13 =>p_x_association_tbl(i).attribute13,
1437 X_ATTRIBUTE14 =>p_x_association_tbl(i).attribute14,
1438 X_ATTRIBUTE15 => p_x_association_tbl(i).attribute15,
1439 X_ASO_OBJECT_TYPE_CODE => p_x_association_tbl(i).aso_object_type_code,
1440 X_SOURCE_REF_CODE => p_x_association_tbl(i).SOURCE_REF_CODE,
1441 X_ASO_OBJECT_ID => p_x_association_tbl(i).aso_object_id,
1442 X_DOCUMENT_ID => p_x_association_tbl(i).document_id,
1443 X_USE_LATEST_REV_FLAG =>nvl(p_x_association_tbl(i).use_latest_rev_flag,'N'),
1444 X_DOC_REVISION_ID => p_x_association_tbl(i).doc_revision_id,
1445 X_OBJECT_VERSION_NUMBER => 1,
1446 X_CHAPTER => p_x_association_tbl(i).chapter,
1447 X_SECTION => p_x_association_tbl(i).section,
1448 X_SUBJECT => p_x_association_tbl(i).subject,
1449 X_FIGURE => p_x_association_tbl(i).figure,
1450 X_PAGE => p_x_association_tbl(i).page,
1451 X_NOTE => p_x_association_tbl(i).note,
1452 X_CREATION_DATE => sysdate,
1453 X_CREATED_BY => fnd_global.user_id,
1454 X_LAST_UPDATE_DATE => sysdate,
1455 X_LAST_UPDATED_BY => fnd_global.user_id,
1456 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1457 END IF;
1458 END IF;
1459 END LOOP;
1460
1461 END IF;
1462
1463 l_msg_count := FND_MSG_PUB.count_msg;
1464
1465 IF l_msg_count > 0 THEN
1466 X_msg_count := l_msg_count;
1467 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1468 RAISE FND_API.G_EXC_ERROR;
1469 END IF;
1470
1471 IF FND_API.TO_BOOLEAN(p_commit) THEN
1472 COMMIT;
1473 END IF;
1474
1475 -- Check if API is called in debug mode. If yes, disable debug.
1476 IF G_DEBUG='Y' THEN
1477 AHL_DEBUG_PUB.debug( 'exit ahl_di_asso_doc_aso_pub.Process Association','+DOBJASS+');
1478 END IF;
1479
1480 IF G_DEBUG='Y' THEN
1481 AHL_DEBUG_PUB.disable_debug;
1482 END IF;
1483
1484 EXCEPTION
1485 WHEN FND_API.G_EXC_ERROR THEN
1486 ROLLBACK TO PROCESS_association;
1487 X_return_status := FND_API.G_RET_STS_ERROR;
1488 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1489 p_count => x_msg_count,
1490 p_data => X_msg_data);
1491 -- Debug info.
1492 IF G_DEBUG='Y' THEN
1493 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1494 AHL_DEBUG_PUB.debug( 'ahl_di_asso_doc_aso_pub.Process Association','+DOCJASS+');
1495 AHL_DEBUG_PUB.disable_debug;
1496 END IF;
1497
1498 WHEN OTHERS THEN
1499 ROLLBACK TO PROCESS_association;
1500 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1501 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1502 THEN
1503 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME ,
1504 p_procedure_name => 'PROCESS_ASSOCIATION',
1505 p_error_text => SUBSTR(SQLERRM,1,240));
1506 END IF;
1507 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1508 p_count => x_msg_count,
1509 p_data => X_msg_data);
1510
1511 END PROCESS_ASSOCIATION;
1512
1513 END AHL_DI_ASSO_DOCASO_PVT;