[Home] [Help]
PACKAGE BODY: APPS.AHL_DI_ASSO_DOC_GEN_PUB
Source
1 PACKAGE BODY AHL_DI_ASSO_DOC_GEN_PUB AS
2 /* $Header: AHLPDAGB.pls 115.1 2003/10/20 19:36:11 sikumar noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_ASSO_DOC_ASO_PUB';
5 --
6 /*-----------------------------------------------------------*/
7 /* procedure name: Check_lookup_name_Or_Id(private procedure)*/
8 /* description : used to retrieve lookup code */
9 /* */
10 /*-----------------------------------------------------------*/
11
12 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
13 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
14
15
16 PROCEDURE RECORD_IDENTIFIER
17 (
18 p_association_rec IN AHL_DI_ASSO_DOC_GEN_PUB.association_rec,
19 x_record OUT NOCOPY VARCHAR2
20 )
21 as
22 Begin
23 If p_association_rec.document_no is not null and p_association_rec.document_no<>fnd_api.g_miss_char
24 Then
25 x_record:=x_record||nvl(p_association_rec.document_no,'')||' - ';
26 End if;
27
28 If p_association_rec.revision_no is not null and p_association_rec.revision_no<>fnd_api.g_miss_char
29 Then
30 x_record:=x_record||nvl(p_association_rec.revision_no,'')||' - ';
31 End if;
32
33 If p_association_rec.chapter is not null and p_association_rec.chapter<>fnd_api.g_miss_char
34 Then
35 x_record:=x_record||nvl(p_association_rec.chapter,'')||' - ';
36 End if;
37
38 If p_association_rec.section is not null and p_association_rec.section<>fnd_api.g_miss_char
39 Then
40 x_record:=x_record||nvl(p_association_rec.section,'')||' - ';
41 End if;
42
43 If p_association_rec.subject is not null and p_association_rec.subject<>fnd_api.g_miss_char
44 Then
45 x_record:=x_record||nvl(p_association_rec.subject,'')||' - ';
46 End if;
47 If p_association_rec.page is not null and p_association_rec.page<>fnd_api.g_miss_char
48 Then
49 x_record:=x_record||nvl(p_association_rec.page,'');
50 End if;
51
52 If p_association_rec.figure is not null and p_association_rec.figure<>fnd_api.g_miss_char
53 Then
54 x_record:=x_record||nvl(p_association_rec.figure,'')||' - ';
55 End if;
56 End;
57
58 PROCEDURE DEFAULT_MISSING_ATTRIBS
59 (
60 p_association_rec IN AHL_DI_ASSO_DOC_GEN_PUB.association_rec,
61 x_association_rec OUT NOCOPY AHL_DI_ASSO_DOC_GEN_PVT.association_rec
62 )
63 AS
64 Cursor GetDocDet(C_DOC_TITLE_ASSO_ID NUMBER)
65 IS
66 SELECT A.*,B.CHAPTER,B.SECTION,B.SUBJECT,B.PAGE,B.FIGURE,B.NOTE
67 FROM AHL_DOC_TITLE_ASSOS_B a, AHL_DOC_TITLE_ASSOS_TL B
68 WHERE A.DOC_TITLE_ASSO_ID=B.DOC_TITLE_ASSO_ID
69 AND A.DOC_TITLE_ASSO_ID=C_DOC_TITLE_ASSO_ID
70 AND B.LANGUAGE=USERENV('LANG');
71
72 l_doc_assos_rec GetDocDet%rowtype;
73 BEGIN
74
75 OPEN GetDocDet(p_association_rec.DOC_TITLE_ASSO_ID);
76 FETCH GetDocDet into l_doc_assos_rec;
77 CLOSE GetDocDet;
78
79 IF p_association_rec.DOC_TITLE_ASSO_ID= FND_API.G_MISS_NUM
80 THEN
81 x_association_rec.DOC_TITLE_ASSO_ID:=NULL;
82 ELSIF p_association_rec.DOC_TITLE_ASSO_ID IS NULL
83 THEN
84 x_association_rec.DOC_TITLE_ASSO_ID:=l_doc_assos_rec.DOC_TITLE_ASSO_ID;
85 ELSE
86 x_association_rec.DOC_TITLE_ASSO_ID:=p_association_rec.DOC_TITLE_ASSO_ID;
87 END IF;
88
89 IF p_association_rec.OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
90 THEN
91 x_association_rec.OBJECT_VERSION_NUMBER:=NULL;
92 ELSIF p_association_rec.OBJECT_VERSION_NUMBER IS NULL
93 THEN
94 x_association_rec.OBJECT_VERSION_NUMBER:=l_doc_assos_rec.OBJECT_VERSION_NUMBER;
95 ELSE
96 x_association_rec.OBJECT_VERSION_NUMBER:=p_association_rec.OBJECT_VERSION_NUMBER;
97 END IF;
98
99 IF p_association_rec.DOC_REVISION_ID= FND_API.G_MISS_NUM
100 THEN
101 x_association_rec.DOC_REVISION_ID:=NULL;
102 ELSIF p_association_rec.DOC_REVISION_ID IS NULL
103 THEN
104 x_association_rec.DOC_REVISION_ID:=l_doc_assos_rec.DOC_REVISION_ID;
105 ELSE
106 x_association_rec.DOC_REVISION_ID:=p_association_rec.DOC_REVISION_ID;
107 END IF;
108
109 IF p_association_rec.ASO_OBJECT_TYPE_CODE= FND_API.G_MISS_CHAR
110 THEN
111 x_association_rec.ASO_OBJECT_TYPE_CODE:=NULL;
112 ELSIF p_association_rec.ASO_OBJECT_TYPE_CODE IS NULL
113 THEN
114 x_association_rec.ASO_OBJECT_TYPE_CODE:=l_doc_assos_rec.ASO_OBJECT_TYPE_CODE;
115 ELSE
116 x_association_rec.ASO_OBJECT_TYPE_CODE:=p_association_rec.ASO_OBJECT_TYPE_CODE;
117 END IF;
118
119 IF p_association_rec.ASO_OBJECT_ID= FND_API.G_MISS_NUM
120 THEN
121 x_association_rec.ASO_OBJECT_ID:=NULL;
122 ELSIF p_association_rec.ASO_OBJECT_ID IS NULL
123 THEN
124 x_association_rec.ASO_OBJECT_ID:=l_doc_assos_rec.ASO_OBJECT_ID;
125 ELSE
126 x_association_rec.ASO_OBJECT_ID:=p_association_rec.ASO_OBJECT_ID;
127 END IF;
128
129 IF p_association_rec.DOCUMENT_ID= FND_API.G_MISS_NUM
130 THEN
131 x_association_rec.DOCUMENT_ID:=NULL;
132 ELSIF p_association_rec.DOCUMENT_ID IS NULL
133 THEN
134 x_association_rec.DOCUMENT_ID:=l_doc_assos_rec.DOCUMENT_ID;
135 ELSE
136 x_association_rec.DOCUMENT_ID:=p_association_rec.DOCUMENT_ID;
137 END IF;
138
139 IF p_association_rec.USE_LATEST_REV_FLAG= FND_API.G_MISS_CHAR
140 THEN
141 x_association_rec.USE_LATEST_REV_FLAG:=NULL;
142 ELSIF p_association_rec.USE_LATEST_REV_FLAG IS NULL
143 THEN
144 x_association_rec.USE_LATEST_REV_FLAG:=l_doc_assos_rec.USE_LATEST_REV_FLAG;
145 ELSE
146 x_association_rec.USE_LATEST_REV_FLAG:=p_association_rec.USE_LATEST_REV_FLAG;
147 END IF;
148
149 IF p_association_rec.SERIAL_NO= FND_API.G_MISS_CHAR
150 THEN
151 x_association_rec.SERIAL_NO:=NULL;
152 ELSIF p_association_rec.SERIAL_NO IS NULL
153 THEN
154 x_association_rec.SERIAL_NO:=l_doc_assos_rec.SERIAL_NO;
155 ELSE
156 x_association_rec.SERIAL_NO:=p_association_rec.SERIAL_NO;
157 END IF;
158
159 IF p_association_rec.ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
160 THEN
161 x_association_rec.ATTRIBUTE_CATEGORY:= NULL;
162 ELSIF p_association_rec.ATTRIBUTE_CATEGORY IS NULL
163 THEN
164 x_association_rec.ATTRIBUTE_CATEGORY:=l_doc_assos_rec.ATTRIBUTE_CATEGORY;
165 ELSE
166 x_association_rec.ATTRIBUTE_CATEGORY:=p_association_rec.ATTRIBUTE_CATEGORY;
167 END IF;
168
169 IF p_association_rec.ATTRIBUTE1= FND_API.G_MISS_CHAR
170 THEN
171 x_association_rec.ATTRIBUTE1:=NULL;
172 ELSIF p_association_rec.ATTRIBUTE1 IS NULL
173 THEN
174 x_association_rec.ATTRIBUTE1:=l_doc_assos_rec.ATTRIBUTE1;
175 ELSE
176 x_association_rec.ATTRIBUTE1:=p_association_rec.ATTRIBUTE1;
177 END IF;
178
179 IF p_association_rec.ATTRIBUTE2= FND_API.G_MISS_CHAR
180 THEN
181 x_association_rec.ATTRIBUTE2:=NULL;
182 ELSIF p_association_rec.ATTRIBUTE2 IS NULL
183 THEN
184 x_association_rec.ATTRIBUTE2:=l_doc_assos_rec.ATTRIBUTE2;
185 ELSE
186 x_association_rec.ATTRIBUTE2:=p_association_rec.ATTRIBUTE2;
187 END IF;
188
189 IF p_association_rec.ATTRIBUTE3= FND_API.G_MISS_CHAR
190 THEN
191 x_association_rec.ATTRIBUTE3:=NULL;
192 ELSIF p_association_rec.ATTRIBUTE3 IS NULL
193 THEN
194 x_association_rec.ATTRIBUTE3:=l_doc_assos_rec.ATTRIBUTE3;
195 ELSE
196 x_association_rec.ATTRIBUTE3:=p_association_rec.ATTRIBUTE3;
197 END IF;
198
199 IF p_association_rec.ATTRIBUTE4= FND_API.G_MISS_CHAR
200 THEN
201 x_association_rec.ATTRIBUTE4:=NULL;
202 ELSIF p_association_rec.ATTRIBUTE4 IS NULL
203 THEN
204 x_association_rec.ATTRIBUTE4:=l_doc_assos_rec.ATTRIBUTE4;
205 ELSE
206 x_association_rec.ATTRIBUTE4:=p_association_rec.ATTRIBUTE4;
207 END IF;
208
209 IF p_association_rec.ATTRIBUTE5= FND_API.G_MISS_CHAR
210 THEN
211 x_association_rec.ATTRIBUTE5:=NULL;
212 ELSIF p_association_rec.ATTRIBUTE5 IS NULL
213 THEN
214 x_association_rec.ATTRIBUTE5:=l_doc_assos_rec.ATTRIBUTE5;
215 ELSE
216 x_association_rec.ATTRIBUTE5:=p_association_rec.ATTRIBUTE5;
217 END IF;
218
219 IF p_association_rec.ATTRIBUTE6= FND_API.G_MISS_CHAR
220 THEN
221 x_association_rec.ATTRIBUTE6:=NULL;
222 ELSIF p_association_rec.ATTRIBUTE6 IS NULL
223 THEN
224 x_association_rec.ATTRIBUTE6:=l_doc_assos_rec.ATTRIBUTE6;
225 ELSE
226 x_association_rec.ATTRIBUTE6:=p_association_rec.ATTRIBUTE6;
227 END IF;
228
229 IF p_association_rec.ATTRIBUTE7= FND_API.G_MISS_CHAR
230 THEN
231 x_association_rec.ATTRIBUTE7:=NULL;
232 ELSIF p_association_rec.ATTRIBUTE7 IS NULL
233 THEN
234 x_association_rec.ATTRIBUTE7:=l_doc_assos_rec.ATTRIBUTE7;
235 ELSE
236 x_association_rec.ATTRIBUTE7:=p_association_rec.ATTRIBUTE7;
237 END IF;
238
239 IF p_association_rec.ATTRIBUTE8= FND_API.G_MISS_CHAR
240 THEN
241 x_association_rec.ATTRIBUTE8:=NULL;
242 ELSIF p_association_rec.ATTRIBUTE8 IS NULL
243 THEN
244 x_association_rec.ATTRIBUTE8:=l_doc_assos_rec.ATTRIBUTE8;
245 ELSE
246 x_association_rec.ATTRIBUTE8:=p_association_rec.ATTRIBUTE8;
247 END IF;
248
249 IF p_association_rec.ATTRIBUTE9= FND_API.G_MISS_CHAR
250 THEN
251 x_association_rec.ATTRIBUTE9:=NULL;
252 ELSIF p_association_rec.ATTRIBUTE9 IS NULL
253 THEN
254 x_association_rec.ATTRIBUTE9:=l_doc_assos_rec.ATTRIBUTE9;
255 ELSE
256 x_association_rec.ATTRIBUTE9:=p_association_rec.ATTRIBUTE9;
257 END IF;
258
259 IF p_association_rec.ATTRIBUTE10= FND_API.G_MISS_CHAR
260 THEN
261 x_association_rec.ATTRIBUTE10:=NULL;
262 ELSIF p_association_rec.ATTRIBUTE10 IS NULL
263 THEN
264 x_association_rec.ATTRIBUTE10:=l_doc_assos_rec.ATTRIBUTE10;
265 ELSE
266 x_association_rec.ATTRIBUTE10:=p_association_rec.ATTRIBUTE10;
267 END IF;
268 IF p_association_rec.ATTRIBUTE11= FND_API.G_MISS_CHAR
269 THEN
270 x_association_rec.ATTRIBUTE11:=NULL;
271 ELSIF p_association_rec.ATTRIBUTE11 IS NULL
272 THEN
273 x_association_rec.ATTRIBUTE11:=l_doc_assos_rec.ATTRIBUTE11;
274 ELSE
275 x_association_rec.ATTRIBUTE11:=p_association_rec.ATTRIBUTE11;
276 END IF;
277 IF p_association_rec.ATTRIBUTE12= FND_API.G_MISS_CHAR
278 THEN
279 x_association_rec.ATTRIBUTE12:=NULL;
280 ELSIF p_association_rec.ATTRIBUTE12 IS NULL
281 THEN
282 x_association_rec.ATTRIBUTE12:=l_doc_assos_rec.ATTRIBUTE12;
283 ELSE
284 x_association_rec.ATTRIBUTE12:=p_association_rec.ATTRIBUTE12;
285 END IF;
286 IF p_association_rec.ATTRIBUTE13= FND_API.G_MISS_CHAR
287 THEN
288 x_association_rec.ATTRIBUTE13:=NULL;
289 ELSIF p_association_rec.ATTRIBUTE13 IS NULL
290 THEN
291 x_association_rec.ATTRIBUTE13:=l_doc_assos_rec.ATTRIBUTE13;
292 ELSE
293 x_association_rec.ATTRIBUTE13:=p_association_rec.ATTRIBUTE13;
294 END IF;
295 IF p_association_rec.ATTRIBUTE14= FND_API.G_MISS_CHAR
296 THEN
297 x_association_rec.ATTRIBUTE14:=NULL;
298 ELSIF p_association_rec.ATTRIBUTE14 IS NULL
299 THEN
300 x_association_rec.ATTRIBUTE14:=l_doc_assos_rec.ATTRIBUTE14;
301 ELSE
302 x_association_rec.ATTRIBUTE14:=p_association_rec.ATTRIBUTE14;
303 END IF;
304 IF p_association_rec.ATTRIBUTE15= FND_API.G_MISS_CHAR
305 THEN
306 x_association_rec.ATTRIBUTE15:=NULL;
307 ELSIF p_association_rec.ATTRIBUTE15 IS NULL
308 THEN
309 x_association_rec.ATTRIBUTE15:=l_doc_assos_rec.ATTRIBUTE15;
310 ELSE
311 x_association_rec.ATTRIBUTE15:=p_association_rec.ATTRIBUTE15;
312 END IF;
313
314 IF p_association_rec.SOURCE_REF_CODE= FND_API.G_MISS_CHAR
315 THEN
316 x_association_rec.SOURCE_REF_CODE:=NULL;
317 ELSIF p_association_rec.SOURCE_REF_CODE IS NULL
318 THEN
319 x_association_rec.SOURCE_REF_CODE:=l_doc_assos_rec.SOURCE_REF_CODE;
320 ELSE
321 x_association_rec.SOURCE_REF_CODE:=p_association_rec.SOURCE_REF_CODE;
322 END IF;
323
324 IF p_association_rec.DOC_TITLE_ASSO_ID= FND_API.G_MISS_NUM
325 THEN
326 x_association_rec.DOC_TITLE_ASSO_ID:=NULL;
327 ELSIF p_association_rec.DOC_TITLE_ASSO_ID IS NULL
328 THEN
329 x_association_rec.DOC_TITLE_ASSO_ID:=l_doc_assos_rec.DOC_TITLE_ASSO_ID;
330 ELSE
331 x_association_rec.DOC_TITLE_ASSO_ID:=p_association_rec.DOC_TITLE_ASSO_ID;
332 END IF;
333
334
335 IF p_association_rec.CHAPTER= FND_API.G_MISS_CHAR
336 THEN
337 x_association_rec.CHAPTER:=NULL;
338
339 ELSIF p_association_rec.CHAPTER IS NULL
340 THEN
341 x_association_rec.CHAPTER:=l_doc_assos_rec.CHAPTER;
342 ELSE
343 x_association_rec.CHAPTER:=p_association_rec.CHAPTER;
344 END IF;
345
346
347 IF p_association_rec.SECTION= FND_API.G_MISS_CHAR
348 THEN
349
350 x_association_rec.SECTION:=NULL;
351 ELSIF p_association_rec.SECTION IS NULL
352 THEN
353
354 x_association_rec.SECTION:=l_doc_assos_rec.SECTION;
355 ELSE
356 x_association_rec.SECTION:=p_association_rec.SECTION;
357 END IF;
358
359
360 IF p_association_rec.SUBJECT= FND_API.G_MISS_CHAR
361 THEN
362 x_association_rec.SUBJECT:=NULL;
363 ELSIF p_association_rec.SUBJECT IS NULL
364 THEN
365 x_association_rec.SUBJECT:=l_doc_assos_rec.SUBJECT;
366 ELSE
367 x_association_rec.SUBJECT:=p_association_rec.SUBJECT;
368 END IF;
369
370 IF p_association_rec.PAGE= FND_API.G_MISS_CHAR
371 THEN
372 x_association_rec.PAGE:=NULL;
373 ELSIF p_association_rec.PAGE IS NULL
374 THEN
375 x_association_rec.PAGE:=l_doc_assos_rec.PAGE;
376 ELSE
377 x_association_rec.PAGE:=p_association_rec.PAGE;
378 END IF;
379
380 IF p_association_rec.FIGURE= FND_API.G_MISS_CHAR
381 THEN
382 x_association_rec.FIGURE:=NULL;
383 ELSIF p_association_rec.FIGURE IS NULL
384 THEN
385 x_association_rec.FIGURE:=l_doc_assos_rec.FIGURE;
386 ELSE
387 x_association_rec.FIGURE:=p_association_rec.FIGURE;
388 END IF;
389
390 IF p_association_rec.NOTE= FND_API.G_MISS_CHAR
391 THEN
392 x_association_rec.NOTE:=NULL;
393 ELSIF p_association_rec.NOTE IS NULL
394 THEN
395 x_association_rec.NOTE:=l_doc_assos_rec.NOTE;
396 ELSE
397 x_association_rec.NOTE:=p_association_rec.NOTE;
398 END IF;
399
400 IF p_association_rec.dml_operation= FND_API.G_MISS_CHAR
401 THEN
402 x_association_rec.dml_operation:=NULL;
403 ELSE
404 x_association_rec.dml_operation:=p_association_rec.dml_operation;
405 END IF;
406
407 END;
408
409
410 PROCEDURE Check_lookup_name_Or_Id
411 ( p_lookup_type IN FND_LOOKUPS.lookup_type%TYPE,
412 p_lookup_code IN FND_LOOKUPS.lookup_code%TYPE,
413 p_meaning IN FND_LOOKUPS.meaning%TYPE,
414 p_check_id_flag IN VARCHAR2,
415 x_lookup_code OUT NOCOPY FND_LOOKUPS.lookup_code%TYPE,
416 x_return_status OUT NOCOPY VARCHAR2)
417 IS
418 BEGIN
419 IF (p_lookup_code IS NOT NULL) THEN
420 IF (p_check_id_flag = 'Y') THEN
421 SELECT lookup_code INTO x_lookup_code
422 FROM FND_LOOKUP_VALUES_VL
423 WHERE lookup_type = p_lookup_type
424 AND lookup_code = p_lookup_code
425 AND sysdate between start_date_active
426 AND nvl(end_date_active,sysdate);
427 ELSE
428 x_lookup_code := p_lookup_code;
429 END IF;
430 ELSE
431 SELECT lookup_code INTO x_lookup_code
432 FROM FND_LOOKUP_VALUES_VL
433 WHERE lookup_type = p_lookup_type
434 AND meaning = p_meaning
435 AND sysdate between start_date_active
436 AND nvl(end_date_active,sysdate);
437 END IF;
438 x_return_status := FND_API.G_RET_STS_SUCCESS;
439 EXCEPTION
440 WHEN no_data_found THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 WHEN too_many_rows THEN
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 WHEN OTHERS THEN
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446 RAISE;
447 END;
448
449
450 PROCEDURE VALUEATE_ASSOCIATION
451 (
452 x_return_status OUT NOCOPY VARCHAR2,
453 x_msg_count OUT NOCOPY NUMBER,
454 x_msg_data OUT NOCOPY VARCHAR2,
455 p_association_rec IN AHL_DI_ASSO_DOC_GEN_PUB.association_rec,
456 p_x_association_rec IN OUT NOCOPY AHL_DI_ASSO_DOC_GEN_PVT.association_rec
457 )
458 AS
459
460 CURSOR GetDocId (c_document_no VARCHAR2)
461 IS
462 SELECT document_id
463 FROM ahl_documents_b
464 WHERE document_no = c_document_no;
465
466 CURSOR GetDocRevId (c_document_id VARCHAR2)
467 IS
468 SELECT doc_revision_id
469 FROM ahl_doc_revisions_b
470 WHERE document_id = c_document_id;
471
472 CURSOR GetDocRevCount (c_document_id VARCHAR2)
473 IS
474 SELECT count(*)
475 FROM ahl_doc_revisions_b
476 WHERE document_id = c_document_id;
477
478 CURSOR GetDocRevNoId (c_document_id NUMBER, c_doc_rev_no VARCHAR2)
479 IS
480 SELECT doc_revision_id
481 FROM ahl_doc_revisions_b
482 WHERE document_id = c_document_id
483 and revision_no= c_doc_rev_no;
484
485
486 l_document_id NUMBER;
487 l_revision_id NUMBER;
488 l_counter NUMBER:=0;
489 l_rev_counter NUMBER:=0;
490 l_record VARCHAR2(4000):='';
491 l_check_flag VARCHAR2(1):='Y';
492
493 BEGIN
494 x_return_status:=FND_API.G_RET_STS_SUCCESS;
495
496 IF G_DEBUG='Y' THEN
497 AHL_DEBUG_PUB.enable_debug;
498 END IF;
499
500
501 RECORD_IDENTIFIER
502 (
503 p_association_rec =>p_association_rec,
504 x_record =>l_record
505 );
506
507 l_check_flag := 'Y';
508
509
510 IF (p_association_rec.document_no IS NULL or
511 p_association_rec.document_no=FND_API.G_MISS_CHAR)
512 THEN
513 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCUMENT_NO_NULL');
514 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
515 FND_MSG_PUB.ADD;
516 IF G_DEBUG='Y' THEN
517 AHL_DEBUG_PUB.debug( 'Stage 002');
518 END IF;
519 l_check_flag:='N';
520 ELSE
521
522 OPEN GetDocId (p_association_rec.document_no);
523 FETCH GetDocId INTO l_document_id;
524
525 IF(GetDocId%NOTFOUND)
526 THEN
527 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOCUMENT_NO_INVALID');
528 FND_MESSAGE.SET_TOKEN('FIELD',p_association_rec.document_no);
529 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
530 FND_MSG_PUB.ADD;
531 IF G_DEBUG='Y' THEN
532 AHL_DEBUG_PUB.debug( 'Document number Does not exist');
533 END IF;
534 l_check_flag:='N';
535 ELSE
536 p_x_association_rec.document_id := l_document_id;
537 END IF;
538 CLOSE GetDocId;
539 END IF;-- DOC NULL
540
541 IF(l_check_flag = 'Y')
542 THEN
543 IF (p_association_rec.revision_no is null or
544 p_association_rec.revision_no=fnd_api.g_miss_char)
545 THEN
546 IF(p_association_rec.aso_object_type_code NOT IN ('ROUTE', 'OPERATION'))
547 THEN
548
549 OPEN GetDocRevCount (l_document_id);
550 FETCH GetDocRevCount INTO l_rev_counter;
551 CLOSE GetDocRevCount;
552
553 IF(l_rev_counter = 1)
554 THEN
555
556 OPEN GetDocRevId (l_document_id);
557 FETCH GetDocRevId INTO l_revision_id;
558 CLOSE GetDocRevId;
559 p_x_association_rec.doc_revision_id := l_revision_id;
560 ELSE
561 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_REVISION_NO_NULL');
562 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
563 FND_MSG_PUB.ADD;
564 IF G_DEBUG='Y' THEN
565 AHL_DEBUG_PUB.debug( 'Revision for Document is null');
566 END IF;
567
568 l_check_flag:='N';
569 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SELECT_FRM_LOV');
570 FND_MSG_PUB.ADD;
571 IF G_DEBUG='Y' THEN
572 AHL_DEBUG_PUB.debug( 'Select document from lov');
573 END IF;
574 END IF;
575 ELSE -- ROUTE AND OPER
576 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_REVISION_NO_NULL');
577 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
578 FND_MSG_PUB.ADD;
579 IF G_DEBUG='Y' THEN
580 AHL_DEBUG_PUB.debug( 'Revision for Document is null');
581 END IF;
582
583 l_check_flag:='N';
584 FND_MESSAGE.SET_NAME('AHL','AHL_DI_SELECT_FRM_LOV');
585 FND_MSG_PUB.ADD;
586 IF G_DEBUG='Y' THEN
587 AHL_DEBUG_PUB.debug( 'Select document from lov');
588 END IF;
589 END IF;-- NOT R O
590 ELSE -- REV NOT NULL
591 OPEN GetDocRevNoId (l_document_id,p_association_rec.revision_no );
592 FETCH GetDocRevNoId INTO l_revision_id;
593
594 IF(GetDocRevNoId%FOUND)
595 THEN
596 p_x_association_rec.doc_revision_id := l_revision_id;
597 ELSE
598 FND_MESSAGE.SET_NAME('AHL','AHL_DI_TAB_DOC_REV_COMB_INVLD');
599 FND_MESSAGE.SET_TOKEN('FIELD1',p_association_rec.revision_no);
600 FND_MESSAGE.SET_TOKEN('FIELD2',p_association_rec.document_no);
601 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
602 FND_MSG_PUB.ADD;
603 END IF;
604 CLOSE GetDocRevNoId;
605 END IF;--REV NULL
606 END IF;
607 END;
608
609
610 PROCEDURE PROCESS_ASSOCIATION
611 (
612 p_api_version IN NUMBER := 1.0,
613 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
614 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
615 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
616 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
617 p_module_type IN VARCHAR2 ,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621 p_x_association_tbl IN OUT NOCOPY association_tbl)
622 IS
623 --To retrieve document id
624 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ASSOCIATION';
625 l_api_version CONSTANT NUMBER := 1.0;
626 l_msg_count NUMBER;
627 l_msg_data VARCHAR2(2000);
628 l_return_status VARCHAR2(1);
629 l_document_id NUMBER;
630 l_doc_revision_id NUMBER;
631 l_init_msg_list VARCHAR2(10) := FND_API.G_TRUE;
632 l_rowid VARCHAR2(30);
633 l_association_tbl AHL_DI_ASSO_DOC_GEN_PVT.ASSOCIATION_TBL;
634 l_record varchar2(4000);
635
636 BEGIN
637 -- Standard Start of API savepoint
638 SAVEPOINT process_association;
639 -- Check if API is called in debug mode. If yes, enable debug.
640
641 IF G_DEBUG='Y' THEN
642 AHL_DEBUG_PUB.enable_debug;
643 END IF;
644
645 -- Debug info.
646
647 IF G_DEBUG='Y' THEN
648 AHL_DEBUG_PUB.debug( 'entering..... ahl_di_asso_doc_aso_pub.Process Association','+DOBJASS+');
649 END IF;
650
651
652
653 -- Initialize message list if p_init_msg_list is set to TRUE.
654 IF FND_API.to_boolean(p_init_msg_list)
655 THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658
659 -- Initialize API return status to success
660
661 x_return_status := 'S';
662
663 -- Standard call to check for call compatibility.
664 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
665 p_api_version,
666 l_api_name,G_PKG_NAME)
667 THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671
672 --Module type is 'JSP' then make it null for the following fields
673
674 IF p_x_association_tbl.count >0
675 THEN
676 FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
677 LOOP
678 IF (p_module_type = 'JSP') THEN
679 IF p_x_association_tbl(i).DML_OPERATION<>'D'
680 THEN
681 p_x_association_tbl(i).document_id:=null;
682 p_x_association_tbl(i).doc_revision_id := null;
683 END IF;
684 END IF;
685 END LOOP;
686 END IF;
687
688
689 IF p_x_association_tbl.count >0
690 THEN
691 FOR i in p_x_association_tbl.FIRST .. p_x_association_tbl.LAST
692 LOOP
693 null;
694
695 IF p_x_association_tbl(i).DML_OPERATION = 'D'
696 THEN
697 l_association_tbl(i).doc_title_asso_id := p_x_association_tbl(i).doc_title_asso_id;
698 l_association_tbl(i).object_version_number:= p_x_association_tbl(i).object_version_number;
699 l_association_tbl(i).dml_operation:= p_x_association_tbl(i).dml_operation;
700 ELSE
701 IF FND_API.to_boolean(p_validate_only)
702 THEN
703 RECORD_IDENTIFIER
704 (
705 p_association_rec =>p_x_association_tbl(i),
706 x_record =>l_record
707 );
708 IF G_DEBUG='Y' THEN
709 AHL_DEBUG_PUB.debug( 'before the record .... '||l_record,'+adhariamr+');
710 END IF;
711
712 DEFAULT_MISSING_ATTRIBS
713 (
714 p_association_rec => p_x_association_tbl(i),
715 x_association_rec => l_association_tbl(i)
716 );
717
718 ELSE
719
720 l_association_tbl(i).doc_title_asso_id := p_x_association_tbl(i).doc_title_asso_id;
721
722 l_association_tbl(i).aso_object_id := p_x_association_tbl(i).aso_object_id;
723 l_association_tbl(i).use_latest_rev_flag := p_x_association_tbl(i).use_latest_rev_flag;
724 l_association_tbl(i).serial_no := p_x_association_tbl(i).serial_no;
725 l_association_tbl(i).source_lang := p_x_association_tbl(i).source_lang;
726 l_association_tbl(i).chapter := p_x_association_tbl(i).chapter;
727 l_association_tbl(i).section := p_x_association_tbl(i).section;
728 l_association_tbl(i).subject := p_x_association_tbl(i).subject;
729 l_association_tbl(i).page := p_x_association_tbl(i).page;
730 l_association_tbl(i).figure := p_x_association_tbl(i).figure;
731 l_association_tbl(i).note := p_x_association_tbl(i).note;
732 l_association_tbl(i).source_ref_code := p_x_association_tbl(i).source_ref_code;
733 l_association_tbl(i).attribute_category := p_x_association_tbl(i).attribute_category;
734 l_association_tbl(i).attribute1 := p_x_association_tbl(i).attribute1;
735 l_association_tbl(i).attribute2 := p_x_association_tbl(i).attribute2;
736 l_association_tbl(i).attribute3 := p_x_association_tbl(i).attribute3;
737 l_association_tbl(i).attribute4 := p_x_association_tbl(i).attribute4;
738 l_association_tbl(i).attribute5 := p_x_association_tbl(i).attribute5;
739 l_association_tbl(i).attribute6 := p_x_association_tbl(i).attribute6;
740 l_association_tbl(i).attribute7 := p_x_association_tbl(i).attribute7;
741 l_association_tbl(i).attribute8 := p_x_association_tbl(i).attribute8;
742 l_association_tbl(i).attribute9 := p_x_association_tbl(i).attribute9;
743 l_association_tbl(i).attribute10 := p_x_association_tbl(i).attribute10;
744 l_association_tbl(i).attribute11 := p_x_association_tbl(i).attribute11;
745 l_association_tbl(i).attribute12 := p_x_association_tbl(i).attribute12;
746 l_association_tbl(i).attribute13 := p_x_association_tbl(i).attribute13;
747 l_association_tbl(i).attribute14 := p_x_association_tbl(i).attribute14;
748 l_association_tbl(i).attribute15 := p_x_association_tbl(i).attribute15;
749 l_association_tbl(i).object_version_number:= p_x_association_tbl(i).object_version_number;
750 l_association_tbl(i).dml_operation := p_x_association_tbl(i).dml_operation;
751
752
753 END IF;
754
755
756
757 VALUEATE_ASSOCIATION
758 (
759 x_return_status =>x_return_Status,
760 x_msg_count =>x_msg_count,
761 x_msg_data =>x_msg_data,
762 p_association_rec =>p_x_association_tbl(i),
763 p_x_association_rec =>l_association_tbl(i)
764 );
765
766
767
768
769 IF p_x_association_tbl(i).aso_object_desc IS NOT NULL AND
770 p_x_association_tbl(i).aso_object_desc <> FND_API.G_MISS_CHAR
771 THEN
772
773
774 Check_lookup_name_Or_Id (
775 p_lookup_type => 'AHL_OBJECT_TYPE',
776 p_lookup_code => null,
777 p_meaning => p_x_association_tbl(i).aso_object_desc,
778 p_check_id_flag => 'Y',
779 x_lookup_code => l_association_tbl(i).aso_object_type_code,
780 x_return_status => l_return_status);
781 IF nvl(l_return_status, 'X') <> 'S'
782 THEN
783 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJ_TYP_NOT_EXISTS');
784 FND_MSG_PUB.ADD;
785 END IF;
786 END IF;
787 -- If Type Code presents
788 IF p_x_association_tbl(i).aso_object_type_code IS NOT NULL AND
789 p_x_association_tbl(i).aso_object_type_code <> FND_API.G_MISS_CHAR
790 THEN
791 l_association_tbl(i).aso_object_type_code := p_x_association_tbl(i).aso_object_type_code;
792 --If both missing
793 ELSE
794 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJECT_TYPE_NULL');
795 FND_MSG_PUB.ADD;
796 END IF;
797
798
799
800 l_msg_count := FND_MSG_PUB.count_msg;
801
802 IF l_msg_count > 0 THEN
803 x_msg_count := l_msg_count;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 -- RAISE FND_API.G_EXC_ERROR;
806 END IF;
807
808 IF G_DEBUG='Y' THEN
809 AHL_DEBUG_PUB.debug( 'before modify');
810 END IF;
811
812
813 END IF;
814 END LOOP;
815
816 l_msg_count := FND_MSG_PUB.count_msg;
817
818 IF l_msg_count > 0 THEN
819 X_msg_count := l_msg_count;
820 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824
825
826 AHL_DI_ASSO_DOC_GEN_PVT.PROCESS_ASSOCIATION
827 (
828 p_api_version => 1.0,
829 p_init_msg_list => l_init_msg_list,
830 p_commit => p_commit,
831 p_validate_only => p_validate_only,
832 p_validation_level => p_validation_level,
833 p_x_association_tbl => l_association_tbl,
834 x_return_status => l_return_status,
835 x_msg_count => l_msg_count,
836 x_msg_data => l_msg_data
837 );
838
839 END IF;--
840 --Standard check to count messages
841 l_msg_count := FND_MSG_PUB.count_msg;
842
843 IF l_msg_count > 0 THEN
844 X_msg_count := l_msg_count;
845 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848
849 --Assign values
850 IF l_association_tbl.COUNT > 0
851 THEN
852 FOR i IN l_association_tbl.FIRST..l_association_tbl.LAST
853 LOOP
854 p_x_association_tbl(i).doc_title_asso_id := l_association_tbl(i).doc_title_asso_id;
855 END LOOP;
856 END IF;
857 --Standard check for commit
858
859 IF FND_API.TO_BOOLEAN(p_commit) THEN
860 COMMIT;
861 END IF;
862 -- Debug info
863 IF G_DEBUG='Y' THEN
864 AHL_DEBUG_PUB.debug( 'End of public api PROCESS Association','+DOBJASS+');
865
866 END IF;
867 -- Check if API is called in debug mode. If yes, disable debug.
868 IF G_DEBUG='Y' THEN
869 AHL_DEBUG_PUB.disable_debug;
870
871 END IF;
872
873 EXCEPTION
874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
875 ROLLBACK TO PROCESS_association;
876 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
877 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
878 p_count => x_msg_count,
879 p_data => x_msg_data);
880 --Debug Info
881 IF G_DEBUG='Y' THEN
882
883 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
884 AHL_DEBUG_PUB.debug( 'ahl_di_asso_doc_aso_pub.PROCESS Association','+DOCJASS+');
885 -- Check if API is called in debug mode. If yes, disable debug.
886 AHL_DEBUG_PUB.disable_debug;
887
888 END IF;
889
890 WHEN FND_API.G_EXC_ERROR THEN
891 ROLLBACK TO PROCESS_association;
892 X_return_status := FND_API.G_RET_STS_ERROR;
893 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
894 p_count => l_msg_count,
895 p_data => X_msg_data);
896 x_msg_count := l_msg_count;
897
898 -- Debug info.
899 IF G_DEBUG='Y' THEN
900 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
901 AHL_DEBUG_PUB.debug( 'ahl_di_asso_doc_aso_pub.PROCESS Association','+DOCJASS+');
902
903 -- Check if API is called in debug mode. If yes, disable debug.
904 AHL_DEBUG_PUB.disable_debug;
905
906 END IF;
907
908 WHEN OTHERS THEN
909 ROLLBACK TO PROCESS_association;
910 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
912 THEN
913 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PUB',
914 p_procedure_name => 'PROCESS_ASSOCIATION',
915 p_error_text => SUBSTR(SQLERRM,1,240));
916 END IF;
917 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
918 p_count => l_msg_count,
919 p_data => X_msg_data);
920 x_msg_count := l_msg_count;
921 -- Debug info.
922 IF G_DEBUG='Y' THEN
923 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
924 AHL_DEBUG_PUB.debug( 'ahl_di_asso_doc_aso_pub.PROCESS Association','+DOCJASS+');
925 -- Check if API is called in debug mode. If yes, disable debug.
926 AHL_DEBUG_PUB.disable_debug;
927
928 END IF;
929
930 END PROCESS_ASSOCIATION;
931
932 END AHL_DI_ASSO_DOC_GEN_PUB;