1 PACKAGE BODY AHL_DI_DOC_REVISION_PVT AS
2 /* $Header: AHLVDORB.pls 120.7.12020000.2 2012/12/14 08:00:06 shnatu ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_DOC_REVISION_PVT';
5 --
6 /*---------------------------------------------------------*/
7 /* procedure name: validate_revision(private procedure) */
8 /* description : Validation checks for before inserting */
9 /* new record as well before modification */
10 /* takes place */
11 /*---------------------------------------------------------*/
12
13 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
14 PROCEDURE VALIDATE_REVISION
15 (
16 P_DOC_REVISION_ID IN NUMBER ,
17 P_DOCUMENT_ID IN NUMBER ,
18 P_REVISION_NO IN VARCHAR2 ,
19 P_REVISION_TYPE_CODE IN VARCHAR2 ,
20 P_REVISION_STATUS_CODE IN VARCHAR2 ,
21 P_REVISION_DATE IN DATE ,
22 P_APPROVED_BY_PARTY_ID IN NUMBER ,
23 P_APPROVED_DATE IN DATE ,
24 P_EFFECTIVE_DATE IN DATE ,
25 P_OBSOLETE_DATE IN DATE ,
26 P_ISSUE_DATE IN DATE ,
27 P_RECEIVED_DATE IN DATE ,
28 P_MEDIA_TYPE_CODE IN VARCHAR2 ,
29 --pekambar Enigma Phase II changes -- start
30 P_ENIGMA_DOC_ID IN VARCHAR2 ,
31 P_X_ENIGMA_MODEL_CODE IN OUT NOCOPY VARCHAR2 ,
32 P_X_ENIGMA_MODEL_MEANING IN OUT NOCOPY VARCHAR2 ,
33 --pekambar Enigma Phase II changes -- end
34 P_ISSUE_NUMBER IN NUMBER ,
35 P_DELETE_FLAG IN VARCHAR2 := 'N' )
36 IS
37
38 --Cursor to retrieve the revision type code
39 CURSOR get_revision_type_code(c_revision_type_code VARCHAR2)
40 IS
41 SELECT lookup_code
42 FROM FND_LOOKUP_VALUES_VL
43 WHERE lookup_code = c_revision_type_code
44 AND lookup_type = 'AHL_REVISION_TYPE'
45 AND sysdate between start_date_active
46 AND nvl(end_date_active,sysdate);
47
48 --Cursor to retrieve revision status code
49 CURSOR get_revision_status_code(c_revision_status_code VARCHAR2)
50 IS
51 SELECT lookup_code
52 FROM FND_LOOKUP_VALUES_VL
53 WHERE lookup_code = c_revision_status_code
54 AND lookup_type = 'AHL_REVISION_STATUS_TYPE'
55 AND sysdate between start_date_active
56 AND nvl(end_date_active,sysdate);
57
58 --Cursor to retrieve media type code
59 CURSOR get_media_type_code(c_media_type_code VARCHAR2)
60 IS
61 SELECT lookup_code
62 FROM FND_LOOKUP_VALUES_VL
63 WHERE lookup_code = c_media_type_code
64 AND lookup_type = 'AHL_MEDIA_TYPE'
65 AND sysdate between start_date_active
66 AND nvl(end_date_active,sysdate);
67
68 -- Used to validate the document id
69 CURSOR check_doc_info(c_document_id NUMBER)
70 IS
71 SELECT 'X'
72 FROM AHL_DOCUMENTS_B
73 WHERE document_id = c_document_id;
74
75 -- Retrieves doc revision record
76 CURSOR get_doc_revision_rec_info (c_doc_revision_id NUMBER)
77 IS
78 SELECT document_id,
79 revision_no,
80 revision_type_code,
81 revision_status_code,
82 revision_date,
83 approved_by_party_id,
84 approved_date,
85 effective_date,
86 obsolete_date,
87 issue_date,
88 received_date,
89 media_type_code,
90 --pekambar Enigma Phase II changes -- start
91 enigma_doc_id,
92 enigma_model_code,
93 --pekambar Enigma Phase II changes -- end
94 issue_number
95 FROM AHL_DOC_REVISIONS_B
96 WHERE doc_revision_id = c_doc_revision_id;
97
98 -- Check for Duplicate Record
99 CURSOR dup_rec(c_document_id NUMBER,
100 c_revision_no VARCHAR2)
101 IS
102 SELECT 'X'
103 FROM AHL_DOC_REVISIONS_B
104 WHERE document_id = c_document_id
105 AND revision_no = c_revision_no;
106
107 --Modified pjha 25-Jun-2002 for restricting Media Type to Electronic File
108 --if there is a file uploaded for the document : Begin
109 --Cursor to check if record exist in IBC_CITEM_LIVE_V
110 CURSOR get_record_from_AHL(c_doc_revision_id VARCHAR2)
111 IS
112 SELECT '1'
113 FROM AHL_DOC_FILE_ASSOC_V
114 WHERE revision_id = c_doc_revision_id;
115 --Modified pjha 25-Jun-2002 for restricting Media Type to Electronic File
116 --if there is a file uploaded for the document : End
117 --
118 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_REVISION';
119 l_api_version CONSTANT NUMBER := 1.0;
120 l_dummy VARCHAR2(2000);
121 l_doc_revision_id NUMBER;
122 l_revision_no VARCHAR2(30);
123 l_document_id NUMBER;
124 l_revision_type_code VARCHAR2(30);
125 l_revision_status_code VARCHAR2(30);
126 l_revision_date DATE;
127 l_approved_by_party_id NUMBER;
128 l_approved_date DATE;
129 l_effective_date DATE;
130 l_obsolete_date DATE;
131 l_issue_date DATE;
132 l_received_date DATE;
133 l_media_type_code VARCHAR2(30);
134 l_media_type_meaning VARCHAR2(80);
135 --pekambar Enigma Phase II changes -- start
136 l_enigma_doc_id VARCHAR2(80);
137 l_enigma_model_code VARCHAR2(30);
138 l_enigma_model_meaning VARCHAR2(80);
139 l_return_status VARCHAR2(1);
140 l_msg_data VARCHAR2(2000);
141 --pekambar Enigma Phase II changes -- end
142 l_issue_number NUMBER;
143
144 BEGIN
145 --When the delete flag is 'YES' means either insert or update
146 IF NVL(p_delete_flag,'N') <> 'Y'
147 THEN
148 IF p_doc_revision_id IS NOT NULL
149 THEN
150 OPEN get_doc_revision_rec_info(p_doc_revision_id);
151 FETCH get_doc_revision_rec_info INTO l_document_id,
152 l_revision_no,
153 l_revision_type_code,
154 l_revision_status_code,
155 l_revision_date,
156 l_approved_by_party_id,
157 l_approved_date,
158 l_effective_date,
159 l_obsolete_date,
160 l_issue_date,
161 l_received_date,
162 l_media_type_code,
163 --pekambar Enigma Phase II changes -- start
164 l_enigma_doc_id,
165 l_enigma_model_code,
166 --pekambar Enigma Phase II changes -- end
167 l_issue_number;
168 CLOSE get_doc_revision_rec_info;
169 END IF;
170 --
171 IF p_document_id IS NOT NULL
172 THEN
173 l_document_id := p_document_id;
174 END IF;
175 --
176 IF p_revision_no IS NOT NULL
177 THEN
178 l_revision_no := p_revision_no;
179 END IF;
180 --
181 IF p_revision_type_code IS NOT NULL
182 THEN
183 l_revision_type_code := p_revision_type_code;
184 END IF;
185 --
186 IF p_revision_status_code IS NOT NULL
187 THEN
188 l_revision_status_code := p_revision_status_code;
189 END IF;
190 --
191 IF p_revision_date IS NOT NULL
192 THEN
193 l_revision_date := p_revision_date;
194 END IF;
195 --
196 IF p_approved_by_party_id IS NOT NULL
197 THEN
198 l_approved_by_party_id := p_approved_by_party_id;
199 END IF;
200 --
201 IF p_approved_date IS NOT NULL
202 THEN
203 l_approved_date := p_approved_date;
204 END IF;
205 --
206 IF p_effective_date IS NOT NULL
207 THEN
208 l_effective_date := p_effective_date;
209 END IF;
210 --
211 IF p_obsolete_date IS NOT NULL
212 THEN
213 l_obsolete_date := p_obsolete_date;
214 END IF;
215 --
216 IF p_media_type_code IS NOT NULL
217 THEN
218 l_media_type_code := p_media_type_code;
219 END IF;
220 --
221 --pekambar Enigma Phase II changes -- start
222 IF p_enigma_doc_id IS NOT NULL
223 THEN
224 l_enigma_doc_id := p_enigma_doc_id;
225 END IF;
226 --
227 IF p_x_enigma_model_code IS NOT NULL
228 THEN
229 l_enigma_model_code := p_x_enigma_model_code;
230 END IF;
231 --
232 IF p_x_enigma_model_meaning IS NOT NULL
233 THEN
234 l_enigma_model_meaning := p_x_enigma_model_meaning;
235 END IF;
236
237 --pekambar Enigma Phase II changes -- end
238 --
239 IF p_issue_number IS NOT NULL
240 THEN
241 l_issue_number := p_issue_number;
242 END IF;
243 l_doc_revision_id := p_doc_revision_id;
244 -- This condition checks Document Id, when the action is insert or update
245 IF ((p_doc_revision_id IS NULL AND
246 p_document_id IS NULL)
247 OR
248
249 (p_doc_revision_id IS NOT NULL
250 AND l_document_id IS NULL))
251
252 THEN
253 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
254 FND_MSG_PUB.ADD;
255 END IF;
256 --This condition checks fro Revision Number, when the action is insert or update
257 IF ((p_doc_revision_id IS NULL AND
258 p_revision_no IS NULL)
259 OR
260
261 (p_doc_revision_id IS NOT NULL
262 AND l_revision_no IS NULL))
263 THEN
264 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REVISION_NO_NULL');
265 FND_MSG_PUB.ADD;
266 END IF;
267 --This condition checks for Revision Type Code
268 IF ((p_doc_revision_id IS NULL AND
269 p_revision_type_code IS NULL)
270 OR
271
272 (p_doc_revision_id IS NOT NULL
273 AND l_revision_type_code IS NULL))
274 THEN
275 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_TYPE_CODE_NULL');
276 FND_MSG_PUB.ADD;
277 END IF;
278 --This condition checks for Revision Status Code
279 IF ((p_doc_revision_id IS NULL AND
280 p_revision_status_code IS NULL)
281 OR
282
283 (p_doc_revision_id IS NOT NULL
284 AND l_revision_status_code IS NULL))
285
286 THEN
287 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_STATUS_CODE_NULL');
288 FND_MSG_PUB.ADD;
289 END IF;
290 -- Checks for existence of Revision type code in fnd lookups
291 IF p_revision_type_code IS NOT NULL
292 THEN
293 OPEN get_revision_type_code(p_revision_type_code);
294 FETCH get_revision_type_code INTO l_dummy;
295 IF get_revision_type_code%NOTFOUND
296 THEN
297 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_TYPE_CODE_NOT_EXIST');
298 FND_MSG_PUB.ADD;
299 END IF;
300 CLOSE get_revision_type_code;
301 END IF;
302 --Checks for existence of Revision Status Code in fnd lookups
303 IF p_revision_status_code IS NOT NULL
304 THEN
305 OPEN get_revision_status_code(p_revision_status_code);
306 FETCH get_revision_status_code INTO l_dummy;
307 IF get_revision_status_code%NOTFOUND
308 THEN
309 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REV_STAT_CODE_NOT_EXIST');
310 FND_MSG_PUB.ADD;
311 END IF;
312 CLOSE get_revision_status_code;
313 END IF;
314 -- Checks for existence of Media Type Code in fnd lookups
315 IF p_media_type_code IS NOT NULL
316 THEN
317 OPEN get_media_type_code(p_media_type_code);
318 FETCH get_media_type_code INTO l_dummy;
319 IF get_media_type_code%NOTFOUND
320 THEN
321 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_CODE_NOT_EXISTS');
322 FND_MSG_PUB.ADD;
323 END IF;
324 CLOSE get_media_type_code;
325 END IF;
326
327 --Modified pjha 25-Jun-2002 for restricting Media Type to Electronic File
328 --if there is a file uploaded for the document : Begin
329 IF (p_media_type_code IS NULL OR p_media_type_code <> 'E-FILE')
330 THEN
331 OPEN get_record_from_AHL(l_doc_revision_id);
332 FETCH get_record_from_AHL into l_dummy;
333 IF get_record_from_AHL%FOUND THEN
334 --{{adharia to add a token to the message
335 SELECT MEANING into l_media_type_meaning
336 FROM FND_LOOKUP_VALUES_VL
337 WHERE LOOKUP_TYPE='AHL_MEDIA_TYPE' AND LOOKUP_CODE='E-FILE';
338
339 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDTYP_NOT_EFILE');
340 FND_MESSAGE.SET_TOKEN('EFILE',l_media_type_meaning);
341 FND_MSG_PUB.ADD;
342 END IF;
343 CLOSE get_record_from_AHL;
344 END IF;
345 --Modified pjha 25-Jun-2002 for restricting Media Type to Electronic File
346 --if there is a file uploaded for the document : End
347
348 --pekambar Enigma Phase II changes -- start
349
350 -- Validating if media type is enigma enigma_doc_id should not be null
351 -- if null throw error message
352 IF G_DEBUG='Y' THEN
353 AHL_DEBUG_PUB.debug( 'in Validate :p_media_type_code ' ||p_enigma_doc_id||'p_media_type_code:'||p_media_type_code);
354 AHL_DEBUG_PUB.debug( 'in Validate :p_enigma_doc_id ' ||p_enigma_doc_id);
355 AHL_DEBUG_PUB.debug( 'in Validate :p_x_enigma_model_code ' ||p_x_enigma_model_code);
356 AHL_DEBUG_PUB.debug( 'in Validate :p_x_enigma_model_meaning ' ||p_x_enigma_model_meaning);
357 END IF;
358
359 /* IF (p_media_type_code = 'ENIGMA' AND p_enigma_doc_id IS NULL)
360 THEN
361 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ENIGMA_DOC_ID_NOT_NULL');
362 FND_MSG_PUB.ADD;
363 END IF;
364 IF (p_media_type_code <> 'ENIGMA' AND p_enigma_doc_id IS NOT NULL)
365 THEN
366 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDIATYPE_IS_NOT_ENIGMA');
367 FND_MSG_PUB.ADD;
368 END IF;*/
369
370
371 IF G_DEBUG='Y' THEN
372 AHL_DEBUG_PUB.debug( 'in AFter lookup Validate :p_media_type_code ' ||p_enigma_doc_id||'p_media_type_code:'||p_media_type_code);
373 AHL_DEBUG_PUB.debug( 'in AFter lookup Validate :p_enigma_doc_id ' ||p_enigma_doc_id);
374 AHL_DEBUG_PUB.debug( 'in AFter lookup Validate :p_x_enigma_model_code ' ||p_x_enigma_model_code);
375 AHL_DEBUG_PUB.debug( 'in AFter lookup Validate :p_x_enigma_model_meaning ' ||p_x_enigma_model_meaning);
376 END IF;
377 -- Validating if media type is enigma enigma_model_code should not be null
378 -- if null throw error message
379 IF ( p_media_type_code = 'ENIGMA' )
380 THEN
381
382 IF (p_enigma_doc_id IS NULL)
383 THEN
384 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ENIGMA_DOC_ID_NOT_NULL');
385 FND_MSG_PUB.ADD;
386 END IF;
387
388 IF( p_x_enigma_model_code IS NULL AND p_x_enigma_model_meaning IS NULL)
389 THEN
390 FND_MESSAGE.set_name( 'AHL','AHL_RM_MODEL_CODE_NULL' );
391 FND_MSG_PUB.add;
392 END IF;
393
394 -- There is MODULE_TYPE passed to this package. assuming only call from JSP.
395 -- So Validating Lookup againest Meaning.
396
397 IF( p_x_enigma_model_meaning IS NOT NULL )
398 THEN
399 AHL_RM_ROUTE_UTIL.validate_lookup
400 (
401 x_return_status => l_return_status,
402 x_msg_data => l_msg_data,
403 p_lookup_type => 'AHL_ENIGMA_MODEL_CODE',
404 p_lookup_meaning => p_x_enigma_model_meaning,
405 p_x_lookup_code => p_x_enigma_model_code
406 );
407
408 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
409
410 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
411 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_INVALID_MODEL' );
412 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
413 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_TOO_MANY_MODELS' );
414 ELSE
415 FND_MESSAGE.set_name( 'AHL', l_msg_data );
416 END IF;
417
418 IF ( p_x_enigma_model_meaning IS NULL OR
419 p_x_enigma_model_meaning = FND_API.G_MISS_CHAR ) THEN
420 FND_MESSAGE.set_token( 'FIELD', p_x_enigma_model_code );
421 ELSE
422 FND_MESSAGE.set_token( 'FIELD', p_x_enigma_model_meaning );
423 END IF;
424
425 FND_MSG_PUB.add;
426
427 END IF;
428 END IF;
429
430 ELSIF (p_media_type_code <> 'ENIGMA' OR p_media_type_code IS NULL)
431 THEN
432 IF( p_x_enigma_model_meaning IS NOT NULL OR p_enigma_doc_id IS NOT NULL )
433 THEN
434 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MEDIATYPE_IS_NOT_ENIGMA');
435 FND_MSG_PUB.ADD;
436 ELSIF(p_x_enigma_model_meaning IS NULL)
437 THEN
438 p_x_enigma_model_code := NULL ;
439 END IF;
440
441 END IF;
442
443
444 --pekambar Enigma Phase II changes -- end
445
446
447 -- Validates for existence of document id in ahl documents table
448 IF p_document_id IS NOT NULL
449 THEN
450 OPEN Check_doc_info(p_document_id);
451 FETCH Check_doc_info INTO l_dummy;
452 IF Check_doc_info%NOTFOUND
453 THEN
454 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NOT_EXISTS');
455 FND_MSG_PUB.ADD;
456 END IF;
457 CLOSE Check_doc_info;
458 END IF;
459
460 -- Validates the Issue Number
461 IF p_issue_number IS NOT NULL
462 OR
463 l_issue_number IS NOT NULL
464 THEN
465 IF(p_issue_number <= 0 or l_issue_number <= 0)
466 THEN
467 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ISSUE_NUM_INVALID');
468 FND_MSG_PUB.ADD;
469 END IF;
470 END IF;
471 --Validations for Duplicate Record
472 IF p_doc_revision_id IS NULL
473 THEN
474 OPEN dup_rec(l_document_id, l_revision_no);
475 FETCH dup_rec INTO l_dummy;
476 IF dup_rec%FOUND THEN
477 FND_MESSAGE.SET_NAME('AHL','AHL_DI_REVISION_DUP_RECORD');
478 FND_MSG_PUB.ADD;
479 END IF;
480 CLOSE dup_rec;
481 END IF;
482
483 END IF;
484 END VALIDATE_REVISION;
485
486 -- FP for Bug #8410484
487 PROCEDURE UPDATE_ASSOCIATIONS
488 (
489 p_doc_revision_id IN NUMBER :=NULL,
490 p_document_id IN NUMBER :=NULL
491 )
492 IS
493 --Cursor to get Association Records
494 CURSOR get_association_records(c_document_id NUMBER)
495 IS
496 SELECT doc_title_asso_id,object_version_number,last_update_date,last_updated_by,last_update_login,
497 aso_object_type_code,aso_object_id
498 FROM AHL_DOC_TITLE_ASSOS_B
499 WHERE document_id = c_document_id
500 AND use_latest_rev_flag = 'Y';
501
502 l_asso_record get_association_records%rowtype;
503
504 --Cursor To get revision status code
505 CURSOR get_revision_status_code(c_doc_revision_id NUMBER)
506 IS
507 SELECT revision_status_code
508 FROM AHL_DOC_REVISIONS_B
509 WHERE doc_revision_id = c_doc_revision_id;
510
511 -- Cursor to get MC status
512 CURSOR get_mc_status(c_relationship_id NUMBER)
513 IS
514 SELECT CONFIG_STATUS_CODE
515 FROM ahl_mc_headers_b header,ahl_mc_relationships relation
516 WHERE relation.relationship_id = c_relationship_id
517 AND header.mc_header_id = relation.mc_header_id;
518
519 --Cursor to get PC status
520 CURSOR get_pc_status(c_node_id NUMBER)
521 IS
522 SELECT STATUS
523 FROM ahl_pc_headers_b header,ahl_pc_nodes_b node
524 WHERE node.pc_node_id = c_node_id
525 AND header.pc_header_id = node.pc_header_id;
526
527 --Cursor to get MR status
528 CURSOR get_mr_status(c_mr_header_id Number)
529 IS
530 SELECT mr_status_code
531 FROM ahl_mr_headers_b
532 where mr_header_id = c_mr_header_id;
533
534 --Cursor to get Operation Status
535 CURSOR get_operation_status(c_operation_id NUMBER)
536 IS
537 SELECT revision_status_code
538 FROM ahl_operations_b
539 WHERE operation_id = c_operation_id;
540
541 --Cursor to get Route Status
542 CURSOR get_route_status(c_route_id NUMBER)
543 IS
544 SELECT revision_status_code
545 FROM ahl_routes_b
546 WHERE route_id = c_route_id;
547
548 l_revision_status_code VARCHAR2(30):= NULL;
549 l_object_status_code VARCHAR2(30):= NULL;
550 l_latest_doc_revision_id NUMBER:= NULL;
551
552 BEGIN
553 --Query to select the latest revision id
554 SELECT MAX(doc_revision_id) INTO l_latest_doc_revision_id
555 FROM ahl_doc_revisions_b
556 WHERE NVL(effective_date,revision_date) = (SELECT MAX(NVL(effective_date,revision_date))
557 FROM ahl_doc_revisions_b
558 WHERE document_id = p_document_id
559 AND NVL(effective_date,revision_date) <= SYSDATE
560 AND revision_status_code = 'CURRENT')
561 AND document_id = p_document_id
562 AND revision_status_code = 'CURRENT';
563 -- If concurrent program is the caller then p_doc_revision_id is passed as null.So in this case
564 -- l_latest_doc_revision_id will be used for updating the association table.
565 -- If this procdure is not called by concurrent program then if the new/modified revision(of which
566 -- revision id is passed) is not latest then the program will return at this point.
567 IF p_doc_revision_id IS NOT NULL AND l_latest_doc_revision_id <> p_doc_revision_id THEN
568 RETURN ;
569 END IF;
570 -- If no 'Current' revision of the document is available(i.e. all the revisions are 'Obsolete'
571 -- and the document is associated with Use Latest Flag ='Yes' then keep the association as it is.
572
573 IF l_latest_doc_revision_id IS NULL THEN
574 RETURN ;
575 END IF;
576
577 IF G_DEBUG='Y' THEN
578 AHL_DEBUG_PUB.debug( 'Got Max Revision Id' || l_latest_doc_revision_id);
579 END IF;
580
581
582 -- For each association record which has use_latest_flag = 'Y' and this document is attached,
583 -- the following piece of code will update the document association table with the latest revision.
584 OPEN get_association_records(p_document_id);
585 LOOP
586 FETCH get_association_records INTO l_asso_record;
587 EXIT WHEN get_association_records%NOTFOUND;
588 IF l_asso_record.aso_object_type_code = 'MR' THEN
589 OPEN get_mr_status(l_asso_record.aso_object_id);
590 FETCH get_mr_status INTO l_object_status_code;
591 CLOSE get_mr_status;
592 ELSIF l_asso_record.aso_object_type_code = 'ROUTE' THEN
593 OPEN get_route_status(l_asso_record.aso_object_id);
594 FETCH get_route_status INTO l_object_status_code;
595 CLOSE get_route_status;
596 ELSIF l_asso_record.aso_object_type_code = 'OPERATION' THEN
597 OPEN get_operation_status(l_asso_record.aso_object_id);
598 FETCH get_operation_status INTO l_object_status_code;
599 CLOSE get_operation_status;
600 ELSIF l_asso_record.aso_object_type_code = 'MC' THEN
601 OPEN get_mc_status(l_asso_record.aso_object_id);
602 FETCH get_mc_status INTO l_object_status_code;
603 CLOSE get_mc_status;
604 ELSIF l_asso_record.aso_object_type_code = 'PC' THEN
605 OPEN get_pc_status(l_asso_record.aso_object_id);
606 FETCH get_pc_status INTO l_object_status_code;
607 CLOSE get_pc_status;
608 END IF;
609 IF G_DEBUG='Y' THEN
610 AHL_DEBUG_PUB.debug( 'Object Status Code ' || l_object_status_code);
611 END IF;
612 -- Updation is allowed only if the associated object status is complete or approval rejected or
613 -- draft.
614 IF l_object_status_code = 'COMPLETE' OR l_object_status_code = 'APPROVAL_REJECTED'
615 OR l_object_status_code = 'DRAFT' THEN
616 IF G_DEBUG='Y' THEN
617 AHL_DEBUG_PUB.debug( 'Before Update in New Procedure' );
618 END IF;
619 UPDATE AHL_DOC_TITLE_ASSOS_B
620 SET doc_revision_id = l_latest_doc_revision_id,
621 object_version_number = l_asso_record.object_version_number + 1,
622 last_update_date = l_asso_record.last_update_date,
623 last_updated_by =l_asso_record.last_updated_by,
624 last_update_login = l_asso_record.last_update_login
625 WHERE doc_title_asso_id =l_asso_record.doc_title_asso_id;
626 END IF;
627 END LOOP;
628 CLOSE get_association_records;
629 END UPDATE_ASSOCIATIONS;
630
631 -- FP end
632
633 /*------------------------------------------------------*/
634 /* procedure name: create_revision */
635 /* description : Creates new revision record */
636 /* for an associated document */
637 /* */
638 /*------------------------------------------------------*/
639 PROCEDURE CREATE_REVISION
640 (
641 p_api_version IN NUMBER := 1.0 ,
642 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
643 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
644 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
645 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
646 p_x_revision_tbl IN OUT NOCOPY revision_tbl ,
647 x_return_status OUT NOCOPY VARCHAR2 ,
648 x_msg_count OUT NOCOPY NUMBER ,
649 x_msg_data OUT NOCOPY VARCHAR2
650 )
651 IS
652 --
653 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REVISION';
654 l_api_version CONSTANT NUMBER := 1.0;
655 l_msg_count NUMBER;
656 l_rowid ROWID;
657 l_doc_revision_id NUMBER;
658 l_revision_info revision_rec;
659 --Added for storing local variables to pass to Table Handler- Prakash 26-Dec-2001
660 l_row_id VARCHAR2(30);
661 --End of addition: Prakash 26-dec-2001
662 BEGIN
663 -- Standard Start of API savepoint
664 SAVEPOINT create_revision;
665 -- Check if API is called in debug mode. If yes, enable debug.
666 IF G_DEBUG='Y' THEN
667 AHL_DEBUG_PUB.enable_debug;
668
669 END IF;
670 -- Debug info.
671 IF G_DEBUG='Y' THEN
672 IF G_DEBUG='Y' THEN
673 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_revision_pvt.Create Revision','+REV+');
674
675 END IF;
676 END IF;
677 -- Standard call to check for call compatibility.
678 IF FND_API.to_boolean(p_init_msg_list)
679 THEN
680 FND_MSG_PUB.initialize;
681 END IF;
682 -- Initialize API return status to success
683 x_return_status := 'S';
684 -- Initialize message list if p_init_msg_list is set to TRUE.
685 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
686 p_api_version,
687 l_api_name,G_PKG_NAME)
688 THEN
689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
690 END IF;
691 --Start of API Body
692 IF p_x_revision_tbl.COUNT > 0
693 THEN
694 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
695 LOOP
696 -- Calling for Validation
697 VALIDATE_REVISION
698 (
699 p_doc_revision_id => p_x_revision_tbl(i).doc_revision_id,
700 p_document_id => p_x_revision_tbl(i).document_id,
701 p_revision_no => p_x_revision_tbl(i).revision_no,
702 p_revision_type_code => p_x_revision_tbl(i).revision_type_code,
703 p_revision_status_code => p_x_revision_tbl(i).revision_status_code,
704 p_revision_date => p_x_revision_tbl(i).revision_date,
705 p_approved_by_party_id => p_x_revision_tbl(i).approved_by_party_id,
706 p_approved_date => p_x_revision_tbl(i).approved_date,
707 p_effective_date => p_x_revision_tbl(i).effective_date,
708 p_obsolete_date => p_x_revision_tbl(i).obsolete_date,
709 p_issue_date => p_x_revision_tbl(i).issue_date,
710 p_received_date => p_x_revision_tbl(i).received_date,
711 p_media_type_code => p_x_revision_tbl(i).media_type_code,
712 --pekambar Enigma Phase II changes -- start
713 p_enigma_doc_id => p_x_revision_tbl(i).enigma_doc_id,
714 p_x_enigma_model_code => p_x_revision_tbl(i).enigma_model_code,
715 p_x_enigma_model_meaning => p_x_revision_tbl(i).enigma_model_meaning,
716 --pekambar Enigma Phase II changes -- end
717 p_issue_number => p_x_revision_tbl(i).issue_number,
718 p_delete_flag => p_x_revision_tbl(i).delete_flag
719 );
720 END LOOP;
721 -- Standard call to get message count and if count is get message info.
722 l_msg_count := FND_MSG_PUB.count_msg;
723
724
725 IF l_msg_count > 0 THEN
726 X_msg_count := l_msg_count;
727 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 RAISE FND_API.G_EXC_ERROR;
729 END IF;
730 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
731 LOOP
732 IF p_x_revision_tbl(i).doc_revision_id IS NULL
733 THEN
734 -- The following conditions should be checked for Optional Fields
735 l_revision_info.approved_by_party_id := p_x_revision_tbl(i).approved_by_party_id;
736 l_revision_info.revision_date := p_x_revision_tbl(i).revision_date;
737 l_revision_info.approved_date := p_x_revision_tbl(i).approved_date;
738 l_revision_info.effective_date := p_x_revision_tbl(i).effective_date;
739 l_revision_info.obsolete_date := p_x_revision_tbl(i).obsolete_date;
740 l_revision_info.issue_date := p_x_revision_tbl(i).issue_date;
741 l_revision_info.received_date := p_x_revision_tbl(i).received_date;
742 l_revision_info.url := p_x_revision_tbl(i).url;
743 l_revision_info.media_type_code := p_x_revision_tbl(i).media_type_code;
744 --pekambar Enigma Phase II changes -- start
745 l_revision_info.enigma_doc_id := p_x_revision_tbl(i).enigma_doc_id;
746 l_revision_info.enigma_model_code := p_x_revision_tbl(i).enigma_model_code;
747 l_revision_info.enigma_model_meaning := p_x_revision_tbl(i).enigma_model_meaning;
748 --pekambar Enigma Phase II changes -- end
749 l_revision_info.volume := p_x_revision_tbl(i).volume;
750 l_revision_info.issue := p_x_revision_tbl(i).issue;
751 l_revision_info.issue_number := p_x_revision_tbl(i).issue_number;
752 l_revision_info.comments := p_x_revision_tbl(i).comments;
753 --snarkhed::Added this as part of bug fix for Bug #9532118
754
755 IF(p_x_revision_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
756 l_revision_info.attribute_category := NULL;
757 ELSE
758 l_revision_info.attribute_category := p_x_revision_tbl(i).attribute_category;
759 END IF;
760
761
762 IF(p_x_revision_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
763 l_revision_info.attribute1 := NULL;
764 ELSE
765 l_revision_info.attribute1 := p_x_revision_tbl(i).attribute1;
766 END IF;
767
768 IF(p_x_revision_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
769 l_revision_info.attribute2 := NULL;
770 ELSE
771 l_revision_info.attribute2 := p_x_revision_tbl(i).attribute2;
772 END IF;
773
774 IF(p_x_revision_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
775 l_revision_info.attribute3 := NULL;
776 ELSE
777 l_revision_info.attribute3 := p_x_revision_tbl(i).attribute3;
778 END IF;
779
780 IF(p_x_revision_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
781 l_revision_info.attribute4 := NULL;
782 ELSE
783 l_revision_info.attribute4 := p_x_revision_tbl(i).attribute4;
784 END IF;
785
786 IF(p_x_revision_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
787 l_revision_info.attribute5 := NULL;
788 ELSE
789 l_revision_info.attribute5 := p_x_revision_tbl(i).attribute5;
790 END IF;
791
792 IF(p_x_revision_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
793 l_revision_info.attribute6 := NULL;
794 ELSE
795 l_revision_info.attribute6 := p_x_revision_tbl(i).attribute6;
796 END IF;
797
798 IF(p_x_revision_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
799 l_revision_info.attribute7 := NULL;
800 ELSE
801 l_revision_info.attribute7 := p_x_revision_tbl(i).attribute7;
802 END IF;
803
804 IF(p_x_revision_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
805 l_revision_info.attribute8 := NULL;
806 ELSE
807 l_revision_info.attribute8 := p_x_revision_tbl(i).attribute8;
808 END IF;
809
810 IF(p_x_revision_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
811 l_revision_info.attribute9 := NULL;
812 ELSE
813 l_revision_info.attribute9 := p_x_revision_tbl(i).attribute9;
814 END IF;
815
816 IF(p_x_revision_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
817 l_revision_info.attribute10 := NULL;
818 ELSE
819 l_revision_info.attribute10 := p_x_revision_tbl(i).attribute10;
820 END IF;
821
822 IF(p_x_revision_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
823 l_revision_info.attribute11:= NULL;
824 ELSE
825 l_revision_info.attribute11 := p_x_revision_tbl(i).attribute11;
826 END IF;
827
828 IF(p_x_revision_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
829 l_revision_info.attribute12 := NULL;
830 ELSE
831 l_revision_info.attribute12 := p_x_revision_tbl(i).attribute12;
832 END IF;
833
834 IF(p_x_revision_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
835 l_revision_info.attribute13 := NULL;
836 ELSE
837 l_revision_info.attribute13 := p_x_revision_tbl(i).attribute13;
838 END IF;
839
840 IF(p_x_revision_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
841 l_revision_info.attribute14 := NULL;
842 ELSE
843 l_revision_info.attribute14 := p_x_revision_tbl(i).attribute14;
844 END IF;
845
846 IF(p_x_revision_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
847 l_revision_info.attribute15 := NULL;
848 ELSE
849 l_revision_info.attribute15 := p_x_revision_tbl(i).attribute15;
850 END IF;
851
852 /*l_revision_info.attribute_category := p_x_revision_tbl(i).attribute_category;
853 l_revision_info.attribute1 := p_x_revision_tbl(i).attribute1;
854 l_revision_info.attribute2 := p_x_revision_tbl(i).attribute2;
855 l_revision_info.attribute3 := p_x_revision_tbl(i).attribute3;
856 l_revision_info.attribute4 := p_x_revision_tbl(i).attribute4;
857 l_revision_info.attribute5 := p_x_revision_tbl(i).attribute5;
858 l_revision_info.attribute6 := p_x_revision_tbl(i).attribute6;
859 l_revision_info.attribute7 := p_x_revision_tbl(i).attribute7;
860 l_revision_info.attribute8 := p_x_revision_tbl(i).attribute8;
861 l_revision_info.attribute9 := p_x_revision_tbl(i).attribute9;
862 l_revision_info.attribute10 := p_x_revision_tbl(i).attribute10;
863 l_revision_info.attribute11 := p_x_revision_tbl(i).attribute11;
864 l_revision_info.attribute12 := p_x_revision_tbl(i).attribute12;
865 l_revision_info.attribute13 := p_x_revision_tbl(i).attribute13;
866 l_revision_info.attribute14 := p_x_revision_tbl(i).attribute14;
867 l_revision_info.attribute15 := p_x_revision_tbl(i).attribute15;*/
868 --End of changes for Bug 9532118
869 -- Gets the value from sequence
870 Select AHL_DOC_REVISIONS_B_S.Nextval Into l_doc_revision_id from dual;
871 --Insert the record into doc revisions table
872 AHL_DOC_REVISIONS_PKG.INSERT_ROW(X_ROWID => l_row_id,
873 X_DOC_REVISION_ID => l_doc_revision_id,
874 X_APPROVED_DATE => l_revision_info.approved_date,
875 X_EFFECTIVE_DATE => l_revision_info.effective_date,
876 X_OBSOLETE_DATE => l_revision_info.obsolete_date,
877 X_ISSUE_DATE => l_revision_info.issue_date,
878 X_RECEIVED_DATE => l_revision_info.received_date,
879 X_URL => l_revision_info.url,
880 X_MEDIA_TYPE_CODE => l_revision_info.media_type_code,
881 --pekambar Enigma Phase II changes -- start
882 X_ENIGMA_DOC_ID => l_revision_info.enigma_doc_id,
883 X_ENIGMA_MODEL_CODE => l_revision_info.enigma_model_code,
884 --pekambar Enigma Phase II changes -- end
885 X_VOLUME => l_revision_info.volume,
886 X_ISSUE => l_revision_info.issue,
887 X_ISSUE_NUMBER => l_revision_info.issue_number,
888 X_ATTRIBUTE_CATEGORY => l_revision_info.attribute_category,
889 X_ATTRIBUTE1 => l_revision_info.attribute1,
890 X_ATTRIBUTE2 => l_revision_info.attribute2,
891 X_REVISION_DATE => l_revision_info.revision_date,
892 X_ATTRIBUTE15 => l_revision_info.attribute15,
893 X_ATTRIBUTE9 => l_revision_info.attribute9,
894 X_ATTRIBUTE10 => l_revision_info.attribute10,
895 X_ATTRIBUTE11 => l_revision_info.attribute11,
896 X_ATTRIBUTE12 => l_revision_info.attribute12,
897 X_ATTRIBUTE13 => l_revision_info.attribute13,
898 X_DOCUMENT_ID => p_x_revision_tbl(i).document_id,
899 X_REVISION_NO => p_x_revision_tbl(i).revision_no,
900 X_APPROVED_BY_PARTY_ID => l_revision_info.approved_by_party_id,
901 X_REVISION_TYPE_CODE => p_x_revision_tbl(i).revision_type_code,
902 X_REVISION_STATUS_CODE => p_x_revision_tbl(i).revision_status_code,
903 X_OBJECT_VERSION_NUMBER => 1,
904 X_ATTRIBUTE3 => l_revision_info.attribute3,
905 X_ATTRIBUTE4 => l_revision_info.attribute4,
906 X_ATTRIBUTE5 => l_revision_info.attribute5,
907 X_ATTRIBUTE6 => l_revision_info.attribute6,
908 X_ATTRIBUTE7 => l_revision_info.attribute7,
909 X_ATTRIBUTE8 => l_revision_info.attribute8,
910 X_ATTRIBUTE14 => l_revision_info.attribute14,
911 X_COMMENTS => l_revision_info.comments,
912 X_CREATION_DATE => sysdate,
913 X_CREATED_BY => fnd_global.user_id,
914 X_LAST_UPDATE_DATE => sysdate,
915 X_LAST_UPDATED_BY => fnd_global.user_id,
916 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
917
918 -- FP for Bug #8410484
919 UPDATE_ASSOCIATIONS(p_doc_revision_id => l_doc_revision_id,
920 p_document_id => p_x_revision_tbl(i).document_id);
921 -- FP end
922
923 /*Following line have been moved, since table handler does not take care of
924 assignments: Prakash : 24-Dec-2001*/
925 --Assign doc revision id
926 p_x_revision_tbl(i).doc_revision_id := l_doc_revision_id;
927 p_x_revision_tbl(i).object_version_number := 1;
928 l_msg_count := FND_MSG_PUB.count_msg;
929
930 IF l_msg_count > 0 THEN
931 X_msg_count := l_msg_count;
932 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933 RAISE FND_API.G_EXC_ERROR;
934 END IF;
935 END IF;
936 END LOOP;
937 END IF;
938 -- Standard check of p_commit.
939 IF FND_API.TO_BOOLEAN(p_commit) THEN
940 COMMIT;
941 END IF;
942 -- Debug info
943 IF G_DEBUG='Y' THEN
944 AHL_DEBUG_PUB.debug( 'End of private api Create Revision','+REV+');
945
946 END IF;
947 -- Check if API is called in debug mode. If yes, disable debug.
948 IF G_DEBUG='Y' THEN
949 AHL_DEBUG_PUB.disable_debug;
950
951 END IF;
952
953 EXCEPTION
954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
955 ROLLBACK TO create_revision;
956 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
958 p_count => x_msg_count,
959 p_data => x_msg_data);
960 --Debug Info
961 IF G_DEBUG='Y' THEN
962 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
963 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Create Revision','+REV+');
964
965
966 -- Check if API is called in debug mode. If yes, disable debug.
967 AHL_DEBUG_PUB.disable_debug;
968
969 END IF;
970
971 WHEN FND_API.G_EXC_ERROR THEN
972 ROLLBACK TO create_revision;
973 X_return_status := FND_API.G_RET_STS_ERROR;
974 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
975 p_count => x_msg_count,
976 p_data => X_msg_data);
977 -- Debug info.
978 IF G_DEBUG='Y' THEN
979 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
980 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Create Revision','+REV+');
981
982
983 -- Check if API is called in debug mode. If yes, disable debug.
984 AHL_DEBUG_PUB.disable_debug;
985
986 END IF;
987
988 WHEN OTHERS THEN
989 ROLLBACK TO create_revision;
990 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
991 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
992 THEN
993 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_REVISION_PVT',
994 p_procedure_name => 'CREATE_REVISION',
995 p_error_text => SUBSTR(SQLERRM,1,240));
996 END IF;
997 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
998 p_count => x_msg_count,
999 p_data => X_msg_data);
1000 -- Debug info.
1001 IF G_DEBUG='Y' THEN
1002 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1003 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Create Revision','+REV+');
1004
1005 -- Check if API is called in debug mode. If yes, disable debug.
1006 AHL_DEBUG_PUB.disable_debug;
1007
1008 END IF;
1009
1010 END CREATE_REVISION;
1011 /*------------------------------------------------------*/
1012 /* procedure name: modify_revision */
1013 /* description : Update the existing revision record */
1014 /* and removes the revision record */
1015 /* for an associated document */
1016 /* */
1017 /*------------------------------------------------------*/
1018 PROCEDURE MODIFY_REVISION
1019 (
1020 p_api_version IN NUMBER := 1.0 ,
1021 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1022 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1023 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1024 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1025 --pekambar changed in enigma phase -2
1026 --p_x_revision_tbl IN revision_tbl ,
1027 p_x_revision_tbl IN OUT NOCOPY revision_tbl ,
1028 x_return_status OUT NOCOPY VARCHAR2 ,
1029 x_msg_count OUT NOCOPY NUMBER ,
1030 x_msg_data OUT NOCOPY VARCHAR2
1031 )
1032 IS
1033 -- Used to retrieve the existing record info
1034 CURSOR get_doc_revisions_b_rec_info(c_doc_revision_id NUMBER)
1035 IS
1036 SELECT ROWID,
1037 document_id,
1038 revision_no,
1039 revision_type_code,
1040 revision_status_code,
1041 revision_date,
1042 approved_by_party_id,
1043 approved_date,
1044 effective_date,
1045 obsolete_date,
1046 issue_date,
1047 received_date,
1048 url,
1049 media_type_code,
1050 --pekambar Enigma Phase II changes -- start
1051 enigma_doc_id,
1052 enigma_model_code,
1053 --pekambar Enigma Phase II changes -- end
1054 volume,
1055 issue,
1056 issue_number,
1057 object_version_number,
1058 attribute_category,
1059 attribute1,
1060 attribute2,
1061 attribute3,
1062 attribute4,
1063 attribute5,
1064 attribute6,
1065 attribute7,
1066 attribute8,
1067 attribute9,
1068 attribute10,
1069 attribute11,
1070 attribute12,
1071 attribute13,
1072 attribute14,
1073 attribute15
1074 FROM AHL_DOC_REVISIONS_B
1075 WHERE doc_revision_id = c_doc_revision_id
1076 FOR UPDATE OF object_version_number NOWAIT;
1077 --Used to retrieve the record from trans table
1078 CURSOR get_doc_revisions_tl_rec_info(c_doc_revision_id NUMBER)
1079 IS
1080 SELECT comments
1081 FROM AHL_DOC_REVISIONS_TL
1082 WHERE doc_revision_id = c_doc_revision_id
1083 FOR UPDATE OF doc_revision_id NOWAIT;
1084 --
1085 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_REVISION';
1086 l_api_version CONSTANT NUMBER := 1.0;
1087 l_msg_count NUMBER;
1088 l_rowid ROWID;
1089 l_doc_revision_id NUMBER;
1090 l_language VARCHAR2(4);
1091 l_source_lang VARCHAR2(4);
1092 l_comments VARCHAR2(2000);
1093 /*--pekambar Enigma Phase II changes -- start
1094 l_enigma_doc_id VARCHAR2(80);
1095 l_enigma_model_code VARCHAR2(30);
1096 l_enigma_model_meaning VARCHAR2(80);
1097 --pekambar Enigma Phase II changes -- end*/
1098
1099 l_revision_info get_doc_revisions_b_rec_info%ROWTYPE;
1100 --
1101 BEGIN
1102 -- Standard Start of API savepoint
1103 SAVEPOINT modify_revision;
1104 -- Check if API is called in debug mode. If yes, enable debug.
1105 IF G_DEBUG='Y' THEN
1106 AHL_DEBUG_PUB.enable_debug;
1107
1108 END IF;
1109 -- Debug info.
1110 IF G_DEBUG='Y' THEN
1111 IF G_DEBUG='Y' THEN
1112 AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_revision_pvt.Modify Revision','+REV+');
1113
1114 END IF;
1115 END IF;
1116 -- Standard call to check for call compatibility.
1117 IF FND_API.to_boolean(p_init_msg_list)
1118 THEN
1119 FND_MSG_PUB.initialize;
1120 END IF;
1121 -- Initialize API return status to success
1122 x_return_status := 'S';
1123
1124 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1125 p_api_version,
1126 l_api_name,G_PKG_NAME)
1127 THEN
1128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1129 END IF;
1130 --Start of API Body
1131 IF p_x_revision_tbl.COUNT > 0
1132 THEN
1133 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
1134 LOOP
1135 --Calling for Validation
1136 VALIDATE_REVISION
1137 (
1138 p_doc_revision_id => p_x_revision_tbl(i).doc_revision_id,
1139 p_document_id => p_x_revision_tbl(i).document_id,
1140 p_revision_no => p_x_revision_tbl(i).revision_no,
1141 p_revision_type_code => p_x_revision_tbl(i).revision_type_code,
1142 p_revision_status_code => p_x_revision_tbl(i).revision_status_code,
1143 p_revision_date => p_x_revision_tbl(i).revision_date,
1144 p_approved_by_party_id => p_x_revision_tbl(i).approved_by_party_id,
1145 p_approved_date => p_x_revision_tbl(i).approved_date,
1146 p_effective_date => p_x_revision_tbl(i).effective_date,
1147 p_obsolete_date => p_x_revision_tbl(i).obsolete_date,
1148 p_issue_date => p_x_revision_tbl(i).obsolete_date,
1149 p_received_date => p_x_revision_tbl(i).obsolete_date,
1150 p_media_type_code => p_x_revision_tbl(i).media_type_code,
1151 --pekambar Enigma Phase II changes -- start
1152 p_enigma_doc_id => p_x_revision_tbl(i).enigma_doc_id,
1153 p_x_enigma_model_code => p_x_revision_tbl(i).enigma_model_code,
1154 p_x_enigma_model_meaning => p_x_revision_tbl(i).enigma_model_meaning,
1155 --pekambar Enigma Phase II changes -- end
1156 p_issue_number => p_x_revision_tbl(i).issue_number,
1157 p_delete_flag => p_x_revision_tbl(i).delete_flag
1158 );
1159 END LOOP;
1160 --End of Validations
1161 -- Standard call to get message count
1162 l_msg_count := FND_MSG_PUB.count_msg;
1163 IF l_msg_count > 0 THEN
1164 X_msg_count := l_msg_count;
1165 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 RAISE FND_API.G_EXC_ERROR;
1167 END IF;
1168 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
1169 LOOP
1170 OPEN get_doc_revisions_b_rec_info(p_x_revision_tbl(i).doc_revision_id);
1171 FETCH get_doc_revisions_b_rec_info INTO l_revision_info;
1172 CLOSE get_doc_revisions_b_rec_info;
1173 --
1174 OPEN get_doc_revisions_tl_rec_info(p_x_revision_tbl(i).doc_revision_id);
1175 FETCH get_doc_revisions_tl_rec_info INTO l_comments;
1176 CLOSE get_doc_revisions_tl_rec_info;
1177
1178 -- This bug fix when concurrent users update
1179 -- updating same record...02/05/02
1180 if (l_revision_info.object_version_number <>p_x_revision_tbl(i).object_version_number)
1181 then
1182 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1183 FND_MSG_PUB.ADD;
1184 RAISE FND_API.G_EXC_ERROR;
1185 end if;
1186 -- The following conditions compare the new record value with old record
1187 -- value, if its different then assign the new value else continue
1188 IF p_x_revision_tbl(i).doc_revision_id IS NOT NULL
1189 THEN
1190 l_revision_info.document_id := p_x_revision_tbl(i).document_id;
1191 l_revision_info.revision_no := p_x_revision_tbl(i).revision_no;
1192 l_revision_info.revision_type_code := p_x_revision_tbl(i).revision_type_code;
1193 l_revision_info.revision_date := p_x_revision_tbl(i).revision_date;
1194 l_revision_info.approved_by_party_id := p_x_revision_tbl(i).approved_by_party_id;
1195 l_revision_info.approved_date := p_x_revision_tbl(i).approved_date;
1196 l_revision_info.effective_date := p_x_revision_tbl(i).effective_date;
1197 l_revision_info.obsolete_date := p_x_revision_tbl(i).obsolete_date;
1198 --FP for Bug #8410484
1199 IF (trunc(l_revision_info.obsolete_date) <= trunc(sysdate)) THEN
1200 l_revision_info.revision_status_code := 'OBSOLETE';
1201 ELSE
1202 l_revision_info.revision_status_code := p_x_revision_tbl(i).revision_status_code;
1203 END IF;
1204
1205 IF G_DEBUG='Y' THEN
1206 AHL_DEBUG_PUB.debug( 'obs date ' || l_revision_info.obsolete_date);
1207 AHL_DEBUG_PUB.debug( 'rev_status_code ' || l_revision_info.revision_status_code);
1208 END IF;
1209 -- End of FP
1210 l_revision_info.issue_date := p_x_revision_tbl(i).issue_date;
1211 l_revision_info.received_date := p_x_revision_tbl(i).received_date;
1212 l_revision_info.url := p_x_revision_tbl(i).url;
1213 l_revision_info.media_type_code := p_x_revision_tbl(i).media_type_code;
1214 --pekambar Enigma Phase II changes -- start
1215 l_revision_info.enigma_doc_id := p_x_revision_tbl(i).enigma_doc_id;
1216 l_revision_info.enigma_model_code := p_x_revision_tbl(i).enigma_model_code;
1217 --l_revision_info.enigma_model_meaning := p_x_revision_tbl(i).enigma_model_meaning;
1218 --pekambar Enigma Phase II changes -- end
1219 l_revision_info.volume := p_x_revision_tbl(i).volume;
1220 l_revision_info.issue := p_x_revision_tbl(i).issue;
1221 l_revision_info.issue_number := p_x_revision_tbl(i).issue_number;
1222 l_comments := p_x_revision_tbl(i).comments;
1223 --snarkhed::Added this as part of bug fix for Bug #9532118
1224
1225 IF(p_x_revision_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
1226 l_revision_info.attribute_category := NULL;
1227 ELSIF(p_x_revision_tbl(i).attribute_category IS NOT NULL) THEN
1228 l_revision_info.attribute_category := p_x_revision_tbl(i).attribute_category;
1229 END IF;
1230
1231
1232 IF(p_x_revision_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
1233 l_revision_info.attribute1 := NULL;
1234 ELSIF(p_x_revision_tbl(i).attribute1 IS NOT NULL) THEN
1235 l_revision_info.attribute1 := p_x_revision_tbl(i).attribute1;
1236 END IF;
1237
1238 IF(p_x_revision_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
1239 l_revision_info.attribute2 := NULL;
1240 ELSIF(p_x_revision_tbl(i).attribute2 IS NOT NULL) THEN
1241 l_revision_info.attribute2 := p_x_revision_tbl(i).attribute2;
1242 END IF;
1243
1244 IF(p_x_revision_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
1245 l_revision_info.attribute3 := NULL;
1246 ELSIF(p_x_revision_tbl(i).attribute3 IS NOT NULL) THEN
1247 l_revision_info.attribute3 := p_x_revision_tbl(i).attribute3;
1248 END IF;
1249
1250 IF(p_x_revision_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
1251 l_revision_info.attribute4 := NULL;
1252 ELSIF(p_x_revision_tbl(i).attribute4 IS NOT NULL) THEN
1253 l_revision_info.attribute4 := p_x_revision_tbl(i).attribute4;
1254 END IF;
1255
1256 IF(p_x_revision_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
1257 l_revision_info.attribute5 := NULL;
1258 ELSIF(p_x_revision_tbl(i).attribute5 IS NOT NULL) THEN
1259 l_revision_info.attribute5 := p_x_revision_tbl(i).attribute5;
1260 END IF;
1261
1262 IF(p_x_revision_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
1263 l_revision_info.attribute6 := NULL;
1264 ELSIF(p_x_revision_tbl(i).attribute6 IS NOT NULL) THEN
1265 l_revision_info.attribute6 := p_x_revision_tbl(i).attribute6;
1266 END IF;
1267
1268 IF(p_x_revision_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
1269 l_revision_info.attribute7 := NULL;
1270 ELSIF(p_x_revision_tbl(i).attribute7 IS NOT NULL) THEN
1271 l_revision_info.attribute7 := p_x_revision_tbl(i).attribute7;
1272 END IF;
1273
1274 IF(p_x_revision_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
1275 l_revision_info.attribute8 := NULL;
1276 ELSIF(p_x_revision_tbl(i).attribute8 IS NOT NULL) THEN
1277 l_revision_info.attribute8 := p_x_revision_tbl(i).attribute8;
1278 END IF;
1279
1280 IF(p_x_revision_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
1281 l_revision_info.attribute9 := NULL;
1282 ELSIF(p_x_revision_tbl(i).attribute9 IS NOT NULL) THEN
1283 l_revision_info.attribute9 := p_x_revision_tbl(i).attribute9;
1284 END IF;
1285
1286 IF(p_x_revision_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
1287 l_revision_info.attribute10 := NULL;
1288 ELSIF(p_x_revision_tbl(i).attribute10 IS NOT NULL) THEN
1289 l_revision_info.attribute10 := p_x_revision_tbl(i).attribute10;
1290 END IF;
1291
1292 IF(p_x_revision_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
1293 l_revision_info.attribute11:= NULL;
1294 ELSIF(p_x_revision_tbl(i).attribute11 IS NOT NULL) THEN
1295 l_revision_info.attribute11 := p_x_revision_tbl(i).attribute11;
1296 END IF;
1297
1298 IF(p_x_revision_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
1299 l_revision_info.attribute12 := NULL;
1300 ELSIF(p_x_revision_tbl(i).attribute12 IS NOT NULL) THEN
1301 l_revision_info.attribute12 := p_x_revision_tbl(i).attribute12;
1302 END IF;
1303
1304 IF(p_x_revision_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
1305 l_revision_info.attribute13 := NULL;
1306 ELSIF(p_x_revision_tbl(i).attribute13 IS NOT NULL) THEN
1307 l_revision_info.attribute13 := p_x_revision_tbl(i).attribute13;
1308 END IF;
1309
1310 IF(p_x_revision_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
1311 l_revision_info.attribute14 := NULL;
1312 ELSIF(p_x_revision_tbl(i).attribute14 IS NOT NULL) THEN
1313 l_revision_info.attribute14 := p_x_revision_tbl(i).attribute14;
1314 END IF;
1315
1316 IF(p_x_revision_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
1317 l_revision_info.attribute15 := NULL;
1318 ELSIF(p_x_revision_tbl(i).attribute15 IS NOT NULL) THEN
1319 l_revision_info.attribute15 := p_x_revision_tbl(i).attribute15;
1320 END IF;
1321
1322
1323 /*
1324 l_revision_info.attribute_category := p_x_revision_tbl(i).attribute_category;
1325 l_revision_info.attribute1 := p_x_revision_tbl(i).attribute1;
1326 l_revision_info.attribute2 := p_x_revision_tbl(i).attribute2;
1327 l_revision_info.attribute3 := p_x_revision_tbl(i).attribute3;
1328 l_revision_info.attribute4 := p_x_revision_tbl(i).attribute4;
1329 l_revision_info.attribute5 := p_x_revision_tbl(i).attribute5;
1330 l_revision_info.attribute6 := p_x_revision_tbl(i).attribute6;
1331 l_revision_info.attribute7 := p_x_revision_tbl(i).attribute7;
1332 l_revision_info.attribute8 := p_x_revision_tbl(i).attribute8;
1333 l_revision_info.attribute9 := p_x_revision_tbl(i).attribute9;
1334 l_revision_info.attribute10 := p_x_revision_tbl(i).attribute10;
1335 l_revision_info.attribute11 := p_x_revision_tbl(i).attribute11;
1336 l_revision_info.attribute12 := p_x_revision_tbl(i).attribute12;
1337 l_revision_info.attribute13 := p_x_revision_tbl(i).attribute13;
1338 l_revision_info.attribute14 := p_x_revision_tbl(i).attribute14;
1339 l_revision_info.attribute15 := p_x_revision_tbl(i).attribute15;*/
1340 --Changes for Bug 9532118 End
1341
1342 /*Calling Table Handler: Prakash : 26-Dec-2001 */
1343 AHL_DOC_REVISIONS_PKG.UPDATE_ROW(X_DOC_REVISION_ID => p_x_revision_tbl(i).doc_revision_id,
1344 X_APPROVED_DATE => l_revision_info.approved_date,
1345 X_EFFECTIVE_DATE => l_revision_info.effective_date,
1346 X_OBSOLETE_DATE => l_revision_info.obsolete_date,
1347 X_ISSUE_DATE => l_revision_info.issue_date,
1348 X_RECEIVED_DATE => l_revision_info.received_date,
1349 X_URL => l_revision_info.url,
1350 X_MEDIA_TYPE_CODE => l_revision_info.media_type_code,
1351 --pekambar Enigma Phase II changes -- start
1352 X_ENIGMA_DOC_ID => l_revision_info.enigma_doc_id,
1353 X_ENIGMA_MODEL_CODE => l_revision_info.enigma_model_code,
1354 --pekambar Enigma Phase II changes -- end
1355 X_VOLUME => l_revision_info.volume,
1356 X_ISSUE => l_revision_info.issue,
1357 X_ISSUE_NUMBER => l_revision_info.issue_number,
1358 X_ATTRIBUTE_CATEGORY => l_revision_info.attribute_category,
1359 X_ATTRIBUTE1 => l_revision_info.attribute1,
1360 X_ATTRIBUTE2 => l_revision_info.attribute2,
1361 X_REVISION_DATE => l_revision_info.revision_date,
1362 X_ATTRIBUTE15 => l_revision_info.attribute15,
1363 X_ATTRIBUTE9 => l_revision_info.attribute9,
1364 X_ATTRIBUTE10 => l_revision_info.attribute10,
1365 X_ATTRIBUTE11 => l_revision_info.attribute11,
1366 X_ATTRIBUTE12 => l_revision_info.attribute12,
1367 X_ATTRIBUTE13 => l_revision_info.attribute13,
1368 X_DOCUMENT_ID => l_revision_info.document_id,
1369 X_REVISION_NO => l_revision_info.revision_no,
1370 X_APPROVED_BY_PARTY_ID => l_revision_info.approved_by_party_id,
1371 X_REVISION_TYPE_CODE => l_revision_info.revision_type_code,
1372 X_REVISION_STATUS_CODE => l_revision_info.revision_status_code,
1373 X_OBJECT_VERSION_NUMBER => l_revision_info.object_version_number+1,
1374 X_ATTRIBUTE3 => l_revision_info.attribute3,
1375 X_ATTRIBUTE4 => l_revision_info.attribute4,
1376 X_ATTRIBUTE5 => l_revision_info.attribute5,
1377 X_ATTRIBUTE6 => l_revision_info.attribute6,
1378 X_ATTRIBUTE7 => l_revision_info.attribute7,
1379 X_ATTRIBUTE8 => l_revision_info.attribute8,
1380 X_ATTRIBUTE14 => l_revision_info.attribute14,
1381 X_COMMENTS => l_comments,
1382 X_LAST_UPDATE_DATE => sysdate,
1383 X_LAST_UPDATED_BY => fnd_global.user_id,
1384 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1385 -- FP for Bug 8410484
1386 UPDATE_ASSOCIATIONS(NULL,
1387 p_document_id => p_x_revision_tbl(i).document_id);
1388 --FP end
1389 -- This will be called to delete revision record, not supported in this phase
1390 ELSIF ((p_x_revision_tbl(i).doc_revision_id IS NOT NULL) AND
1391 NVL(p_x_revision_tbl(i).delete_flag, 'N') = 'Y' )
1392
1393 THEN
1394 DELETE_REVISION
1395 (p_api_version => 1.0 ,
1396 p_init_msg_list => FND_API.G_TRUE ,
1397 p_commit => FND_API.G_FALSE ,
1398 p_validate_only => FND_API.G_TRUE ,
1399 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1400 p_x_revision_tbl => p_x_revision_tbl ,
1401 x_return_status => x_return_status ,
1402 x_msg_count => x_msg_count ,
1403 x_msg_data => x_msg_data
1404 );
1405 END IF;
1406 END LOOP;
1407 END IF;
1408 -- Standard check of p_commit.
1409 IF FND_API.TO_BOOLEAN(p_commit) THEN
1410 COMMIT;
1411 END IF;
1412 -- Debug info
1413 IF G_DEBUG='Y' THEN
1414 AHL_DEBUG_PUB.debug( 'End of private api Modify Revision','+REV+');
1415
1416 END IF;
1417 -- Check if API is called in debug mode. If yes, disable debug.
1418 IF G_DEBUG='Y' THEN
1419 AHL_DEBUG_PUB.disable_debug;
1420
1421 END IF;
1422 EXCEPTION
1423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1424 ROLLBACK TO modify_revision;
1425 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1427 p_count => x_msg_count,
1428 p_data => x_msg_data);
1429
1430 IF G_DEBUG='Y' THEN
1431 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1432 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Modify Revision','+REV+');
1433
1434
1435 -- Check if API is called in debug mode. If yes, disable debug.
1436 AHL_DEBUG_PUB.disable_debug;
1437
1438 END IF;
1439
1440 WHEN FND_API.G_EXC_ERROR THEN
1441 ROLLBACK TO modify_revision;
1442 X_return_status := FND_API.G_RET_STS_ERROR;
1443 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1444 p_count => x_msg_count,
1445 p_data => X_msg_data);
1446 -- Debug info.
1447 IF G_DEBUG='Y' THEN
1448 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1449 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Modify Revision','+REV+');
1450
1451
1452 -- Check if API is called in debug mode. If yes, disable debug.
1453 AHL_DEBUG_PUB.disable_debug;
1454
1455 END IF;
1456
1457 WHEN OTHERS THEN
1458 ROLLBACK TO modify_revision;
1459 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1461 THEN
1462 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_DOC_REVISION_PVT',
1463 p_procedure_name => 'MODIFY_REVISION',
1464 p_error_text => SUBSTR(SQLERRM,1,240));
1465 END IF;
1466 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1467 p_count => x_msg_count,
1468 p_data => X_msg_data);
1469
1470 -- Debug info.
1471 IF G_DEBUG='Y' THEN
1472 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1473 AHL_DEBUG_PUB.debug( 'ahl_di_doc_revision_pvt.Modify Revision','+REV+');
1474
1475
1476 -- Check if API is called in debug mode. If yes, disable debug.
1477 AHL_DEBUG_PUB.disable_debug;
1478
1479 END IF;
1480
1481 END MODIFY_REVISION;
1482 --
1483 PROCEDURE DELETE_REVISION
1484 (
1485 p_api_version IN NUMBER := 1.0 ,
1486 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1487 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1488 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1489 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1490 p_x_revision_tbl IN revision_tbl ,
1491 x_return_status OUT NOCOPY VARCHAR2 ,
1492 x_msg_count OUT NOCOPY NUMBER ,
1493 x_msg_data OUT NOCOPY VARCHAR2
1494 )
1495 IS
1496 -- to get the revision rec
1497 CURSOR get_revision_rec_b_info(c_doc_revision_id NUMBER)
1498 IS
1499 SELECT ROWID,
1500 effective_date,
1501 obsolete_date,
1502 object_version_number
1503 FROM AHL_DOC_REVISIONS_B
1504 WHERE doc_revision_id = c_doc_revision_id
1505 FOR UPDATE OF object_version_number NOWAIT;
1506 --
1507 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_REVISION';
1508 l_api_version CONSTANT NUMBER := 1.0;
1509 l_rowid ROWID;
1510 l_object_version_number NUMBER;
1511 l_effective_date DATE;
1512 l_obsolete_date DATE;
1513 BEGIN
1514 -- Standard Start of API savepoint
1515 SAVEPOINT delete_revision;
1516 -- Standard call to check for call compatibility.
1517 IF FND_API.to_boolean(p_init_msg_list)
1518 THEN
1519 FND_MSG_PUB.initialize;
1520 END IF;
1521 -- Initialize API return status to success
1522 x_return_status := 'S';
1523 -- Initialize message list if p_init_msg_list is set to TRUE.
1524 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1525 p_api_version,
1526 l_api_name,G_PKG_NAME)
1527 THEN
1528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1529 END IF;
1530 --Start of API Body
1531 IF p_x_revision_tbl.COUNT > 0
1532 THEN
1533 FOR i IN p_x_revision_tbl.FIRST..p_x_revision_tbl.LAST
1534 LOOP
1535 OPEN get_revision_rec_b_info(p_x_revision_tbl(i).doc_revision_id);
1536 FETCH get_revision_rec_b_info INTO l_rowid,
1537 l_effective_date,
1538 l_obsolete_date,
1539 l_object_version_number;
1540 IF (get_revision_rec_b_info%NOTFOUND)
1541 THEN
1542 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_REVI_RECORD_INVALID');
1543 FND_MSG_PUB.ADD;
1544 END IF;
1545 CLOSE get_revision_rec_b_info;
1546 -- Check for version number
1547 IF (l_object_version_number <> p_x_revision_tbl(i).object_version_number)
1548 THEN
1549 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_REVI_RECORD_CHANGED');
1550 FND_MSG_PUB.ADD;
1551 END IF;
1552 -- Validate with end date
1553 IF (l_obsolete_date IS NOT NULL AND l_obsolete_date <= SYSDATE)
1554 THEN
1555 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_REVI_RECORD_CLOSED');
1556 FND_MSG_PUB.ADD;
1557 END IF;
1558 IF (TRUNC(NVL(l_obsolete_date, SYSDATE)) >
1559 TRUNC(NVL(p_x_revision_tbl(i).obsolete_date,SYSDATE)))
1560 THEN
1561 FND_MESSAGE.SET_NAME('AHL','AHL_DI_OBSOLETE_DATE_INVALID');
1562 FND_MSG_PUB.ADD;
1563 END IF;
1564 -- Update the end date in subscriptions table
1565 UPDATE AHL_DOC_REVISIONS_B
1566 SET OBSOLETE_DATE = p_x_revision_tbl(i).obsolete_date
1567 WHERE ROWID = l_rowid;
1568 END LOOP;
1569 END IF;
1570 -- Standard check of p_commit.
1571 IF FND_API.TO_BOOLEAN(p_commit) THEN
1572 COMMIT;
1573 END IF;
1574
1575 EXCEPTION
1576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1577 ROLLBACK TO delete_revision;
1578 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1579 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data);
1582
1583 WHEN FND_API.G_EXC_ERROR THEN
1584 ROLLBACK TO delete_revision;
1585 X_return_status := FND_API.G_RET_STS_ERROR;
1586 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1587 p_count => x_msg_count,
1588 p_data => X_msg_data);
1589 WHEN OTHERS THEN
1590 ROLLBACK TO delete_revision;
1591 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1592 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1593 THEN
1594 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DOC_REVISIONS_PVT',
1595 p_procedure_name => 'DELETE_REVISION',
1596 p_error_text => SUBSTR(SQLERRM,1,240));
1597 END IF;
1598 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1599 p_count => x_msg_count,
1600 p_data => X_msg_data);
1601
1602 END DELETE_REVISION;
1603
1604 --FP #8410484
1605 PROCEDURE UPDATE_ASSOCIATIONS_CONCURRENT(
1606 errbuf OUT NOCOPY VARCHAR2,
1607 retcode OUT NOCOPY NUMBER,
1608 p_api_version IN NUMBER := 1.0
1609 )
1610 IS
1611 --Cursor to get ids of all the documents which are associated with use latest flag ='Yes'.
1612 CURSOR get_all_document_ids
1613 IS
1614 select distinct(asso.document_id),doc.document_no
1615 from ahl_doc_title_assos_b asso,ahl_documents_b doc
1616 where asso.use_latest_rev_flag='Y'
1617 and asso.document_id=doc.document_id;
1618
1619 l_api_version NUMBER :=1.0;
1620 l_api_name VARCHAR2(40) := 'UPDATE_ASSOCIATIONS_CONCURRENT';
1621 l_document_id NUMBER;
1622 l_document_no VARCHAR2(100);
1623
1624 BEGIN
1625 FND_MSG_PUB.Initialize;
1626 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1627 THEN
1628 retcode := 2;
1629 errbuf := FND_MSG_PUB.Get;
1630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1631 END IF;
1632 -- To change the status of the documents obsoleted using future obsolete date to 'obsolete' status.
1633 update ahl_doc_revisions_b
1634 set revision_status_code ='OBSOLETE'
1635 where trunc(obsolete_date) <= trunc(sysdate)
1636 and revision_status_code <> 'OBSOLETE';
1637
1638 fnd_file.put_line(fnd_file.log,'Associations for following documents are checked and updated appropriately');
1639 OPEN get_all_document_ids;
1640 LOOP
1641 FETCH get_all_document_ids INTO l_document_id,l_document_no;
1642 EXIT WHEN get_all_document_ids%NOTFOUND;
1643 UPDATE_ASSOCIATIONS(NULL,l_document_id);
1644 fnd_file.put_line(fnd_file.log,' document_no -> ' || l_document_no);
1645 END LOOP;
1646 CLOSE get_all_document_ids;
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 retcode := 2;
1650 FND_MESSAGE.SET_NAME('AHL','AHL_DI_CONC_UPDT_FAILED');
1651 FND_MSG_PUB.ADD;
1652 errbuf := FND_MSG_PUB.GET;
1653 fnd_file.put_line(fnd_file.log, errbuf);
1654
1655 END UPDATE_ASSOCIATIONS_CONCURRENT;
1656 END AHL_DI_DOC_REVISION_PVT;
[Home] [Help]