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