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