eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AHL_DOC_REVISIONS_PKG

Source


1 package body AHL_DOC_REVISIONS_PKG as
2 /* $Header: AHLLDORB.pls 120.1.12020000.2 2012/12/06 22:29:04 sareepar ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_DOC_REVISION_ID in NUMBER,
6   X_APPROVED_DATE in DATE,
7   X_EFFECTIVE_DATE in DATE,
8   X_OBSOLETE_DATE in DATE,
9   X_ISSUE_DATE in DATE,
10   X_RECEIVED_DATE in DATE,
11   X_URL in VARCHAR2,
12   X_MEDIA_TYPE_CODE in VARCHAR2,
13   --pekambar Enigma Phase II changes -- start
14   X_ENIGMA_DOC_ID in VARCHAR2,
15   X_ENIGMA_MODEL_CODE in VARCHAR2,
16   --pekambar Enigma Phase II changes -- end
17   X_VOLUME in VARCHAR2,
18   X_ISSUE in VARCHAR2,
19   X_ISSUE_NUMBER in NUMBER,
20   X_ATTRIBUTE_CATEGORY in VARCHAR2,
21   X_ATTRIBUTE1 in VARCHAR2,
22   X_ATTRIBUTE2 in VARCHAR2,
23   X_REVISION_DATE in DATE,
24   X_ATTRIBUTE15 in VARCHAR2,
25   X_ATTRIBUTE9 in VARCHAR2,
26   X_ATTRIBUTE10 in VARCHAR2,
27   X_ATTRIBUTE11 in VARCHAR2,
28   X_ATTRIBUTE12 in VARCHAR2,
29   X_ATTRIBUTE13 in VARCHAR2,
30   X_DOCUMENT_ID in NUMBER,
31   X_REVISION_NO in VARCHAR2,
32   X_APPROVED_BY_PARTY_ID in NUMBER,
33   X_REVISION_TYPE_CODE in VARCHAR2,
34   X_REVISION_STATUS_CODE in VARCHAR2,
35   X_OBJECT_VERSION_NUMBER in NUMBER,
36   X_ATTRIBUTE3 in VARCHAR2,
37   X_ATTRIBUTE4 in VARCHAR2,
38   X_ATTRIBUTE5 in VARCHAR2,
39   X_ATTRIBUTE6 in VARCHAR2,
40   X_ATTRIBUTE7 in VARCHAR2,
41   X_ATTRIBUTE8 in VARCHAR2,
42   X_ATTRIBUTE14 in VARCHAR2,
43   X_COMMENTS in VARCHAR2,
44   X_CREATION_DATE in DATE,
45   X_CREATED_BY in NUMBER,
46   X_LAST_UPDATE_DATE in DATE,
47   X_LAST_UPDATED_BY in NUMBER,
48   X_LAST_UPDATE_LOGIN in NUMBER
49 ) is
50   cursor C is select ROWID from AHL_DOC_REVISIONS_B
51     where DOC_REVISION_ID = X_DOC_REVISION_ID
52     ;
53 begin
54   insert into AHL_DOC_REVISIONS_B (
55     APPROVED_DATE,
56     EFFECTIVE_DATE,
57     OBSOLETE_DATE,
58     ISSUE_DATE,
59     RECEIVED_DATE,
60     URL,
61     MEDIA_TYPE_CODE,
62     --pekambar Enigma Phase II changes -- start
63     ENIGMA_DOC_ID,
64     ENIGMA_MODEL_CODE,
65     --pekambar Enigma Phase II changes -- end
66     VOLUME,
67     ISSUE,
68     ISSUE_NUMBER,
69     ATTRIBUTE_CATEGORY,
70     ATTRIBUTE1,
71     ATTRIBUTE2,
72     REVISION_DATE,
73     ATTRIBUTE15,
74     ATTRIBUTE9,
75     ATTRIBUTE10,
76     ATTRIBUTE11,
77     ATTRIBUTE12,
78     ATTRIBUTE13,
79     DOCUMENT_ID,
80     REVISION_NO,
81     APPROVED_BY_PARTY_ID,
82     REVISION_TYPE_CODE,
83     REVISION_STATUS_CODE,
84     OBJECT_VERSION_NUMBER,
85     ATTRIBUTE3,
86     ATTRIBUTE4,
87     ATTRIBUTE5,
88     ATTRIBUTE6,
89     ATTRIBUTE7,
90     ATTRIBUTE8,
91     ATTRIBUTE14,
92     DOC_REVISION_ID,
93     CREATION_DATE,
94     CREATED_BY,
95     LAST_UPDATE_DATE,
96     LAST_UPDATED_BY,
97     LAST_UPDATE_LOGIN
98   ) values (
99     X_APPROVED_DATE,
100     X_EFFECTIVE_DATE,
101     X_OBSOLETE_DATE,
102     X_ISSUE_DATE,
103     X_RECEIVED_DATE,
104     X_URL,
105     X_MEDIA_TYPE_CODE,
106     --pekambar Enigma Phase II changes -- start
107     X_ENIGMA_DOC_ID,
108     X_ENIGMA_MODEL_CODE,
109     --pekambar Enigma Phase II changes -- end
110    X_VOLUME,
111     X_ISSUE,
112     X_ISSUE_NUMBER,
113     X_ATTRIBUTE_CATEGORY,
114     X_ATTRIBUTE1,
115     X_ATTRIBUTE2,
116     X_REVISION_DATE,
117     X_ATTRIBUTE15,
118     X_ATTRIBUTE9,
119     X_ATTRIBUTE10,
120     X_ATTRIBUTE11,
121     X_ATTRIBUTE12,
122     X_ATTRIBUTE13,
123     X_DOCUMENT_ID,
124     X_REVISION_NO,
125     X_APPROVED_BY_PARTY_ID,
126     X_REVISION_TYPE_CODE,
127     X_REVISION_STATUS_CODE,
128     X_OBJECT_VERSION_NUMBER,
129     X_ATTRIBUTE3,
130     X_ATTRIBUTE4,
131     X_ATTRIBUTE5,
132     X_ATTRIBUTE6,
133     X_ATTRIBUTE7,
134     X_ATTRIBUTE8,
135     X_ATTRIBUTE14,
136     X_DOC_REVISION_ID,
137     X_CREATION_DATE,
138     X_CREATED_BY,
139     X_LAST_UPDATE_DATE,
140     X_LAST_UPDATED_BY,
141     X_LAST_UPDATE_LOGIN
142   );
143 
144   insert into AHL_DOC_REVISIONS_TL (
145     LAST_UPDATE_LOGIN,
146     COMMENTS,
147     CREATION_DATE,
148     CREATED_BY,
149     DOC_REVISION_ID,
150     LAST_UPDATE_DATE,
151     LAST_UPDATED_BY,
152     LANGUAGE,
153     SOURCE_LANG
154   ) select
155     X_LAST_UPDATE_LOGIN,
156     X_COMMENTS,
157     X_CREATION_DATE,
158     X_CREATED_BY,
159     X_DOC_REVISION_ID,
160     X_LAST_UPDATE_DATE,
161     X_LAST_UPDATED_BY,
162     L.LANGUAGE_CODE,
163     userenv('LANG')
164   from FND_LANGUAGES L
165   where L.INSTALLED_FLAG in ('I', 'B')
166   and not exists
167     (select NULL
168     from AHL_DOC_REVISIONS_TL T
169     where T.DOC_REVISION_ID = X_DOC_REVISION_ID
170     and T.LANGUAGE = L.LANGUAGE_CODE);
171 
172   open c;
173   fetch c into X_ROWID;
174   if (c%notfound) then
175     close c;
176     raise no_data_found;
177   end if;
178   close c;
179 
180 end INSERT_ROW;
181 
182 procedure LOCK_ROW (
183   X_DOC_REVISION_ID in NUMBER,
184   X_APPROVED_DATE in DATE,
185   X_EFFECTIVE_DATE in DATE,
186   X_OBSOLETE_DATE in DATE,
187   X_ISSUE_DATE in DATE,
188   X_RECEIVED_DATE in DATE,
189   X_URL in VARCHAR2,
190   X_MEDIA_TYPE_CODE in VARCHAR2,
191   --pekambar Enigma Phase II changes -- start
192   X_ENIGMA_DOC_ID in VARCHAR2,
193   X_ENIGMA_MODEL_CODE in VARCHAR2,
194   --pekambar Enigma Phase II changes -- end
195   X_VOLUME in VARCHAR2,
196   X_ISSUE in VARCHAR2,
197   X_ISSUE_NUMBER in NUMBER,
198   X_ATTRIBUTE_CATEGORY in VARCHAR2,
199   X_ATTRIBUTE1 in VARCHAR2,
200   X_ATTRIBUTE2 in VARCHAR2,
201   X_REVISION_DATE in DATE,
202   X_ATTRIBUTE15 in VARCHAR2,
203   X_ATTRIBUTE9 in VARCHAR2,
204   X_ATTRIBUTE10 in VARCHAR2,
205   X_ATTRIBUTE11 in VARCHAR2,
206   X_ATTRIBUTE12 in VARCHAR2,
207   X_ATTRIBUTE13 in VARCHAR2,
208   X_DOCUMENT_ID in NUMBER,
209   X_REVISION_NO in VARCHAR2,
210   X_APPROVED_BY_PARTY_ID in NUMBER,
211   X_REVISION_TYPE_CODE in VARCHAR2,
212   X_REVISION_STATUS_CODE in VARCHAR2,
213   X_OBJECT_VERSION_NUMBER in NUMBER,
214   X_ATTRIBUTE3 in VARCHAR2,
215   X_ATTRIBUTE4 in VARCHAR2,
216   X_ATTRIBUTE5 in VARCHAR2,
217   X_ATTRIBUTE6 in VARCHAR2,
218   X_ATTRIBUTE7 in VARCHAR2,
219   X_ATTRIBUTE8 in VARCHAR2,
220   X_ATTRIBUTE14 in VARCHAR2,
221   X_COMMENTS in VARCHAR2
222 ) is
223   cursor c is select
224       APPROVED_DATE,
225       EFFECTIVE_DATE,
226       OBSOLETE_DATE,
227       ISSUE_DATE,
228       RECEIVED_DATE,
229       URL,
230       MEDIA_TYPE_CODE,
231       --pekambar Enigma Phase II changes -- start
232       ENIGMA_DOC_ID,
233       ENIGMA_MODEL_CODE,
234       --pekambar Enigma Phase II changes -- end
235       VOLUME,
236       ISSUE,
237       ISSUE_NUMBER,
238       ATTRIBUTE_CATEGORY,
239       ATTRIBUTE1,
240       ATTRIBUTE2,
241       REVISION_DATE,
242       ATTRIBUTE15,
243       ATTRIBUTE9,
244       ATTRIBUTE10,
245       ATTRIBUTE11,
246       ATTRIBUTE12,
247       ATTRIBUTE13,
248       DOCUMENT_ID,
249       REVISION_NO,
250       APPROVED_BY_PARTY_ID,
251       REVISION_TYPE_CODE,
252       REVISION_STATUS_CODE,
253       OBJECT_VERSION_NUMBER,
254       ATTRIBUTE3,
255       ATTRIBUTE4,
256       ATTRIBUTE5,
257       ATTRIBUTE6,
258       ATTRIBUTE7,
259       ATTRIBUTE8,
260       ATTRIBUTE14
261     from AHL_DOC_REVISIONS_B
262     where DOC_REVISION_ID = X_DOC_REVISION_ID
263     for update of DOC_REVISION_ID nowait;
264   recinfo c%rowtype;
265 
266   cursor c1 is select
267       COMMENTS,
268       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
269     from AHL_DOC_REVISIONS_TL
270     where DOC_REVISION_ID = X_DOC_REVISION_ID
271     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
272     for update of DOC_REVISION_ID nowait;
273 begin
274   open c;
275   fetch c into recinfo;
276   if (c%notfound) then
277     close c;
278     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
279     app_exception.raise_exception;
280   end if;
281   close c;
282   if (    ((recinfo.APPROVED_DATE = X_APPROVED_DATE)
283            OR ((recinfo.APPROVED_DATE is null) AND (X_APPROVED_DATE is null)))
284       AND ((recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
285            OR ((recinfo.EFFECTIVE_DATE is null) AND (X_EFFECTIVE_DATE is null)))
286       AND ((recinfo.OBSOLETE_DATE = X_OBSOLETE_DATE)
287            OR ((recinfo.OBSOLETE_DATE is null) AND (X_OBSOLETE_DATE is null)))
288       AND ((recinfo.ISSUE_DATE = X_ISSUE_DATE)
289            OR ((recinfo.ISSUE_DATE is null) AND (X_ISSUE_DATE is null)))
290       AND ((recinfo.RECEIVED_DATE = X_RECEIVED_DATE)
291            OR ((recinfo.RECEIVED_DATE is null) AND (X_RECEIVED_DATE is null)))
292       AND ((recinfo.URL = X_URL)
293            OR ((recinfo.URL is null) AND (X_URL is null)))
294       AND ((recinfo.MEDIA_TYPE_CODE = X_MEDIA_TYPE_CODE)
295            OR ((recinfo.MEDIA_TYPE_CODE is null) AND (X_MEDIA_TYPE_CODE is null)))
296       --pekambar Enigma Phase II changes -- start
297       AND ((recinfo.ENIGMA_DOC_ID = X_ENIGMA_DOC_ID)
298            OR ((recinfo.ENIGMA_DOC_ID is null) AND (X_ENIGMA_DOC_ID is null)))
299       AND ((recinfo.ENIGMA_MODEL_CODE = X_ENIGMA_MODEL_CODE)
300            OR ((recinfo.ENIGMA_MODEL_CODE is null) AND (X_ENIGMA_MODEL_CODE is null)))
301       --pekambar Enigma Phase II changes -- end
302       AND ((recinfo.VOLUME = X_VOLUME)
303            OR ((recinfo.VOLUME is null) AND (X_VOLUME is null)))
304       AND ((recinfo.ISSUE = X_ISSUE)
305            OR ((recinfo.ISSUE is null) AND (X_ISSUE is null)))
306       AND ((recinfo.ISSUE_NUMBER = X_ISSUE_NUMBER)
307            OR ((recinfo.ISSUE_NUMBER is null) AND (X_ISSUE_NUMBER is null)))
308       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
309            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
310       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
311            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
312       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
313            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
314       AND ((recinfo.REVISION_DATE = X_REVISION_DATE)
315            OR ((recinfo.REVISION_DATE is null) AND (X_REVISION_DATE is null)))
316       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
317            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
318       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
319            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
320       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
321            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
322       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
323            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
324       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
325            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
326       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
327            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
328       AND (recinfo.DOCUMENT_ID = X_DOCUMENT_ID)
329       AND (recinfo.REVISION_NO = X_REVISION_NO)
330       AND ((recinfo.APPROVED_BY_PARTY_ID = X_APPROVED_BY_PARTY_ID)
331            OR ((recinfo.APPROVED_BY_PARTY_ID is null) AND (X_APPROVED_BY_PARTY_ID is null)))
332       AND (recinfo.REVISION_TYPE_CODE = X_REVISION_TYPE_CODE)
333       AND (recinfo.REVISION_STATUS_CODE = X_REVISION_STATUS_CODE)
337       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
334       AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
335       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
336            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
338            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
339       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
340            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
341       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
342            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
343       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
344            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
345       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
346            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
347       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
348            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
349   ) then
350     null;
351   else
352     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
353     app_exception.raise_exception;
354   end if;
355 
356   for tlinfo in c1 loop
357     if (tlinfo.BASELANG = 'Y') then
358       if (    ((tlinfo.COMMENTS = X_COMMENTS)
359                OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
360       ) then
361         null;
362       else
363         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
364         app_exception.raise_exception;
365       end if;
366     end if;
367   end loop;
368   return;
369 end LOCK_ROW;
370 
371 procedure UPDATE_ROW (
372   X_DOC_REVISION_ID in NUMBER,
373   X_APPROVED_DATE in DATE,
374   X_EFFECTIVE_DATE in DATE,
375   X_OBSOLETE_DATE in DATE,
376   X_ISSUE_DATE in DATE,
377   X_RECEIVED_DATE in DATE,
378   X_URL in VARCHAR2,
379   X_MEDIA_TYPE_CODE in VARCHAR2,
380   --pekambar Enigma Phase II changes -- start
381   X_ENIGMA_DOC_ID in VARCHAR2,
382   X_ENIGMA_MODEL_CODE in VARCHAR2,
383   --pekambar Enigma Phase II changes -- end
384   X_VOLUME in VARCHAR2,
385   X_ISSUE in VARCHAR2,
386   X_ISSUE_NUMBER in NUMBER,
387   X_ATTRIBUTE_CATEGORY in VARCHAR2,
388   X_ATTRIBUTE1 in VARCHAR2,
389   X_ATTRIBUTE2 in VARCHAR2,
390   X_REVISION_DATE in DATE,
391   X_ATTRIBUTE15 in VARCHAR2,
392   X_ATTRIBUTE9 in VARCHAR2,
393   X_ATTRIBUTE10 in VARCHAR2,
394   X_ATTRIBUTE11 in VARCHAR2,
395   X_ATTRIBUTE12 in VARCHAR2,
396   X_ATTRIBUTE13 in VARCHAR2,
397   X_DOCUMENT_ID in NUMBER,
398   X_REVISION_NO in VARCHAR2,
399   X_APPROVED_BY_PARTY_ID in NUMBER,
400   X_REVISION_TYPE_CODE in VARCHAR2,
401   X_REVISION_STATUS_CODE in VARCHAR2,
402   X_OBJECT_VERSION_NUMBER in NUMBER,
403   X_ATTRIBUTE3 in VARCHAR2,
404   X_ATTRIBUTE4 in VARCHAR2,
405   X_ATTRIBUTE5 in VARCHAR2,
406   X_ATTRIBUTE6 in VARCHAR2,
407   X_ATTRIBUTE7 in VARCHAR2,
408   X_ATTRIBUTE8 in VARCHAR2,
409   X_ATTRIBUTE14 in VARCHAR2,
410   X_COMMENTS in VARCHAR2,
411   X_LAST_UPDATE_DATE in DATE,
412   X_LAST_UPDATED_BY in NUMBER,
413   X_LAST_UPDATE_LOGIN in NUMBER
414 ) is
415 begin
416   update AHL_DOC_REVISIONS_B set
417     APPROVED_DATE = X_APPROVED_DATE,
418     EFFECTIVE_DATE = X_EFFECTIVE_DATE,
419     OBSOLETE_DATE = X_OBSOLETE_DATE,
420     ISSUE_DATE = X_ISSUE_DATE,
421     RECEIVED_DATE = X_RECEIVED_DATE,
422     URL = X_URL,
423     MEDIA_TYPE_CODE = X_MEDIA_TYPE_CODE,
424     --pekambar Enigma Phase II changes -- start
425     ENIGMA_DOC_ID = X_ENIGMA_DOC_ID,
426     ENIGMA_MODEL_CODE = X_ENIGMA_MODEL_CODE,
427     --pekambar Enigma Phase II changes -- end
428     VOLUME = X_VOLUME,
429     ISSUE = X_ISSUE,
430     ISSUE_NUMBER = X_ISSUE_NUMBER,
431     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
432     ATTRIBUTE1 = X_ATTRIBUTE1,
433     ATTRIBUTE2 = X_ATTRIBUTE2,
434     REVISION_DATE = X_REVISION_DATE,
435     ATTRIBUTE15 = X_ATTRIBUTE15,
436     ATTRIBUTE9 = X_ATTRIBUTE9,
437     ATTRIBUTE10 = X_ATTRIBUTE10,
438     ATTRIBUTE11 = X_ATTRIBUTE11,
439     ATTRIBUTE12 = X_ATTRIBUTE12,
440     ATTRIBUTE13 = X_ATTRIBUTE13,
441     DOCUMENT_ID = X_DOCUMENT_ID,
442     REVISION_NO = X_REVISION_NO,
443     APPROVED_BY_PARTY_ID = X_APPROVED_BY_PARTY_ID,
444     REVISION_TYPE_CODE = X_REVISION_TYPE_CODE,
445     REVISION_STATUS_CODE = X_REVISION_STATUS_CODE,
446     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
447     ATTRIBUTE3 = X_ATTRIBUTE3,
448     ATTRIBUTE4 = X_ATTRIBUTE4,
449     ATTRIBUTE5 = X_ATTRIBUTE5,
450     ATTRIBUTE6 = X_ATTRIBUTE6,
451     ATTRIBUTE7 = X_ATTRIBUTE7,
452     ATTRIBUTE8 = X_ATTRIBUTE8,
453     ATTRIBUTE14 = X_ATTRIBUTE14,
454     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
455     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
456     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
457   where DOC_REVISION_ID = X_DOC_REVISION_ID;
458 
459   if (sql%notfound) then
460     raise no_data_found;
461   end if;
462 
463   update AHL_DOC_REVISIONS_TL set
464     COMMENTS = X_COMMENTS,
465     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
466     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
467     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
468     SOURCE_LANG = userenv('LANG')
469   where DOC_REVISION_ID = X_DOC_REVISION_ID
470   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
471 
472   if (sql%notfound) then
473     raise no_data_found;
474   end if;
475 end UPDATE_ROW;
476 
477 procedure DELETE_ROW (
478   X_DOC_REVISION_ID in NUMBER
479 ) is
480 begin
481   delete from AHL_DOC_REVISIONS_TL
482   where DOC_REVISION_ID = X_DOC_REVISION_ID;
483 
484   if (sql%notfound) then
485     raise no_data_found;
486   end if;
487 
488   delete from AHL_DOC_REVISIONS_B
492     raise no_data_found;
489   where DOC_REVISION_ID = X_DOC_REVISION_ID;
490 
491   if (sql%notfound) then
493   end if;
494 end DELETE_ROW;
495 
496 procedure ADD_LANGUAGE
497 is
498 begin
499   delete from AHL_DOC_REVISIONS_TL T
500   where not exists
501     (select NULL
502     from AHL_DOC_REVISIONS_B B
503     where B.DOC_REVISION_ID = T.DOC_REVISION_ID
504     );
505 
506   update AHL_DOC_REVISIONS_TL T set (
507       COMMENTS
508     ) = (select
509       B.COMMENTS
510     from AHL_DOC_REVISIONS_TL B
511     where B.DOC_REVISION_ID = T.DOC_REVISION_ID
512     and B.LANGUAGE = T.SOURCE_LANG)
513   where (
514       T.DOC_REVISION_ID,
515       T.LANGUAGE
516   ) in (select
517       SUBT.DOC_REVISION_ID,
518       SUBT.LANGUAGE
519     from AHL_DOC_REVISIONS_TL SUBB, AHL_DOC_REVISIONS_TL SUBT
520     where SUBB.DOC_REVISION_ID = SUBT.DOC_REVISION_ID
521     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
522     and (SUBB.COMMENTS <> SUBT.COMMENTS
523       or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
524       or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
525   ));
526 
527   insert into AHL_DOC_REVISIONS_TL (
528     LAST_UPDATE_LOGIN,
529     COMMENTS,
530     CREATION_DATE,
531     CREATED_BY,
532     DOC_REVISION_ID,
533     LAST_UPDATE_DATE,
534     LAST_UPDATED_BY,
535     LANGUAGE,
536     SOURCE_LANG
537   ) select /*+ ORDERED */
538     B.LAST_UPDATE_LOGIN,
539     B.COMMENTS,
540     B.CREATION_DATE,
541     B.CREATED_BY,
542     B.DOC_REVISION_ID,
543     B.LAST_UPDATE_DATE,
544     B.LAST_UPDATED_BY,
545     L.LANGUAGE_CODE,
546     B.SOURCE_LANG
547   from AHL_DOC_REVISIONS_TL B, FND_LANGUAGES L
548   where L.INSTALLED_FLAG in ('I', 'B')
549   and B.LANGUAGE = userenv('LANG')
550   and not exists
551     (select NULL
552     from AHL_DOC_REVISIONS_TL T
553     where T.DOC_REVISION_ID = B.DOC_REVISION_ID
554     and T.LANGUAGE = L.LANGUAGE_CODE);
555 end ADD_LANGUAGE;
556 
557 procedure LOAD_ROW (
558   X_DOC_REVISION_ID        in NUMBER,
559   X_APPROVED_DATE          in DATE,
560   X_EFFECTIVE_DATE         in DATE,
561   X_OBSOLETE_DATE          in DATE,
562   X_ISSUE_DATE             in DATE,
563   X_RECEIVED_DATE          in DATE,
564   X_URL                    in VARCHAR2,
565   X_MEDIA_TYPE_CODE        in VARCHAR2,
566   --pekambar Enigma Phase II changes -- start
567   X_ENIGMA_DOC_ID in VARCHAR2,
568   X_ENIGMA_MODEL_CODE in VARCHAR2,
569   --pekambar Enigma Phase II changes -- end
570   X_VOLUME                 in VARCHAR2,
571   X_ISSUE                  in VARCHAR2,
572   X_ISSUE_NUMBER           in NUMBER,
573   X_REVISION_DATE          in DATE,
574   X_DOCUMENT_ID            in NUMBER,
575   X_REVISION_NO            in VARCHAR2,
576   X_APPROVED_BY_PARTY_ID   in NUMBER,
577   X_REVISION_TYPE_CODE     in VARCHAR2,
578   X_REVISION_STATUS_CODE   in VARCHAR2,
579   X_OBJECT_VERSION_NUMBER  in NUMBER,
580   X_ATTRIBUTE_CATEGORY     in VARCHAR2,
581   X_ATTRIBUTE1             in VARCHAR2,
582   X_ATTRIBUTE2             in VARCHAR2,
583   X_ATTRIBUTE3             in VARCHAR2,
584   X_ATTRIBUTE4             in VARCHAR2,
585   X_ATTRIBUTE5             in VARCHAR2,
586   X_ATTRIBUTE6             in VARCHAR2,
587   X_ATTRIBUTE7             in VARCHAR2,
588   X_ATTRIBUTE8             in VARCHAR2,
589   X_ATTRIBUTE9             in VARCHAR2,
590   X_ATTRIBUTE10            in VARCHAR2,
591   X_ATTRIBUTE11            in VARCHAR2,
592   X_ATTRIBUTE12            in VARCHAR2,
593   X_ATTRIBUTE13            in VARCHAR2,
594   X_ATTRIBUTE14            in VARCHAR2,
595   X_ATTRIBUTE15            in VARCHAR2,
596   X_COMMENTS               in VARCHAR2,
597   X_OWNER                  in VARCHAR2
598 ) is
599  user_id number := 0;
600  doc_revision_id  number;
601  row_id  varchar2(64);
602 begin
603   if (X_OWNER = 'SEED') then
604     user_id := 1;
605   end if;
606 
607   select doc_revision_id into doc_revision_id
608   from   ahl_doc_revisions_b
609   where  doc_revision_id = X_DOC_REVISION_ID;
610 
611 AHL_DOC_REVISIONS_PKG.UPDATE_ROW (
612 
613   X_DOC_REVISION_ID       => doc_revision_id,
614   X_APPROVED_DATE         => X_APPROVED_DATE,
615   X_EFFECTIVE_DATE        => X_EFFECTIVE_DATE,
616   X_OBSOLETE_DATE         => X_OBSOLETE_DATE,
617   X_ISSUE_DATE            => X_ISSUE_DATE,
618   X_RECEIVED_DATE         => X_RECEIVED_DATE,
619   X_URL                   => X_URL,
620   X_MEDIA_TYPE_CODE       => X_MEDIA_TYPE_CODE,
621   --pekambar Enigma Phase II changes -- start
622   X_ENIGMA_DOC_ID => X_ENIGMA_DOC_ID,
623   X_ENIGMA_MODEL_CODE => X_ENIGMA_MODEL_CODE,
624   --pekambar Enigma Phase II changes -- end
625   X_VOLUME                => X_VOLUME,
626   X_ISSUE                 => X_ISSUE,
627   X_ISSUE_NUMBER          => X_ISSUE_NUMBER,
628   X_ATTRIBUTE_CATEGORY    =>X_ATTRIBUTE_CATEGORY,
629   X_ATTRIBUTE1            =>X_ATTRIBUTE1,
630   X_ATTRIBUTE2            =>X_ATTRIBUTE2,
631   X_REVISION_DATE         =>X_REVISION_DATE,
632   X_ATTRIBUTE15           =>X_ATTRIBUTE15,
633   X_ATTRIBUTE9            =>X_ATTRIBUTE9,
634   X_ATTRIBUTE10           => X_ATTRIBUTE10,
635   X_ATTRIBUTE11           => X_ATTRIBUTE11,
636   X_ATTRIBUTE12           => X_ATTRIBUTE12,
637   X_ATTRIBUTE13           => X_ATTRIBUTE13,
638   X_DOCUMENT_ID           => X_DOCUMENT_ID,
639   X_REVISION_NO           => X_REVISION_NO,
640   X_APPROVED_BY_PARTY_ID  => X_APPROVED_BY_PARTY_ID,
641   X_REVISION_TYPE_CODE    => X_REVISION_TYPE_CODE,
642   X_REVISION_STATUS_CODE  => X_REVISION_STATUS_CODE,
643   X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
647   X_ATTRIBUTE6            => X_ATTRIBUTE6,
644   X_ATTRIBUTE3            => X_ATTRIBUTE3,
645   X_ATTRIBUTE4            => X_ATTRIBUTE4,
646   X_ATTRIBUTE5            => X_ATTRIBUTE5,
648   X_ATTRIBUTE7            => X_ATTRIBUTE7,
649   X_ATTRIBUTE8            => X_ATTRIBUTE8,
650   X_ATTRIBUTE14           => X_ATTRIBUTE14,
651   X_COMMENTS              => X_COMMENTS,
652   X_LAST_UPDATE_DATE      => sysdate,
653   X_LAST_UPDATED_BY       => user_id,
654   X_LAST_UPDATE_LOGIN 	  => 0
655 );
656 
657 exception
658   when NO_DATA_FOUND then
659 
660  SELECT  AHL_DOC_REVISIONS_B_S.Nextval INTO
661            doc_revision_id from DUAL;
662 
663 AHL_DOC_REVISIONS_PKG.INSERT_ROW (
664   X_ROWID                 => row_id,
665   X_DOC_REVISION_ID       => doc_revision_id,
666   X_APPROVED_DATE         => X_APPROVED_DATE,
667   X_EFFECTIVE_DATE        => X_EFFECTIVE_DATE,
668   X_OBSOLETE_DATE         => X_OBSOLETE_DATE,
669   X_ISSUE_DATE            => X_ISSUE_DATE,
670   X_RECEIVED_DATE         => X_RECEIVED_DATE,
671   X_URL                   => X_URL,
672   X_MEDIA_TYPE_CODE       => X_MEDIA_TYPE_CODE,
673   --pekambar Enigma Phase II changes -- start
674   X_ENIGMA_DOC_ID => X_ENIGMA_DOC_ID,
675   X_ENIGMA_MODEL_CODE => X_ENIGMA_MODEL_CODE,
676   --pekambar Enigma Phase II changes -- end
677   X_VOLUME                => X_VOLUME,
678   X_ISSUE                 => X_ISSUE,
679   X_ISSUE_NUMBER          => X_ISSUE_NUMBER,
680   X_ATTRIBUTE_CATEGORY    =>X_ATTRIBUTE_CATEGORY,
681   X_ATTRIBUTE1            =>X_ATTRIBUTE1,
682   X_ATTRIBUTE2            =>X_ATTRIBUTE2,
683   X_REVISION_DATE         =>X_REVISION_DATE,
684   X_ATTRIBUTE15           =>X_ATTRIBUTE15,
685   X_ATTRIBUTE9            =>X_ATTRIBUTE9,
686   X_ATTRIBUTE10           => X_ATTRIBUTE10,
687   X_ATTRIBUTE11           => X_ATTRIBUTE11,
688   X_ATTRIBUTE12           => X_ATTRIBUTE12,
689   X_ATTRIBUTE13           => X_ATTRIBUTE13,
690   X_DOCUMENT_ID           => X_DOCUMENT_ID,
691   X_REVISION_NO           => X_REVISION_NO,
692   X_APPROVED_BY_PARTY_ID  => X_APPROVED_BY_PARTY_ID,
693   X_REVISION_TYPE_CODE    => X_REVISION_TYPE_CODE,
694   X_REVISION_STATUS_CODE  => X_REVISION_STATUS_CODE,
695   X_OBJECT_VERSION_NUMBER => 1,
696   X_ATTRIBUTE3            => X_ATTRIBUTE3,
697   X_ATTRIBUTE4            => X_ATTRIBUTE4,
698   X_ATTRIBUTE5            => X_ATTRIBUTE5,
699   X_ATTRIBUTE6            => X_ATTRIBUTE6,
700   X_ATTRIBUTE7            => X_ATTRIBUTE7,
701   X_ATTRIBUTE8            => X_ATTRIBUTE8,
702   X_ATTRIBUTE14           => X_ATTRIBUTE14,
703   X_COMMENTS              => X_COMMENTS,
704   X_CREATION_DATE         => sysdate,
705   X_CREATED_BY            => user_id,
706   X_LAST_UPDATE_DATE      => sysdate,
707   X_LAST_UPDATED_BY       => user_id,
708   X_LAST_UPDATE_LOGIN     => 0
709 );
710 end LOAD_ROW;
711 
712 procedure TRANSLATE_ROW (
713   X_DOC_REVISION_ID        in NUMBER,
714   X_COMMENTS               in VARCHAR2,
715   X_OWNER                  in VARCHAR2
716 ) is
717 begin
718 update AHl_DOC_REVISIONS_TL set
719  comments          = nvl(X_COMMENTS, comments),
720  source_lang       = userenv('LANG'),
721  last_update_date  = sysdate,
722  last_updated_by   = decode(X_OWNER, 'SEED', 1, 0),
723  last_update_login = 0
724 where doc_revision_id =
725 	(select doc_revision_id
726          from ahl_doc_revisions_b
727          where doc_revision_id = X_DOC_REVISION_ID)
728 and userenv('LANG') in (language, source_lang);
729 
730 end TRANSLATE_ROW;
731 
732 end AHL_DOC_REVISIONS_PKG;