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_TITLE_ASSOS_PKG

Source


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