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