1 package body DOM_DOCUMENT_CATALOGS_PKG as
2 /* $Header: DOMCATB.pls 120.0 2006/02/23 02:12:07 ysireesh noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_CATALOG_ID in NUMBER,
7 X_INTERNAL_NAME in VARCHAR2,
8 X_NAME in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from DOM_DOCUMENT_CATALOGS
17 where CATALOG_ID = X_CATALOG_ID
18 ;
19 begin
20 insert into DOM_DOCUMENT_CATALOGS (
21 CATALOG_ID,
22 INTERNAL_NAME,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_LOGIN
28 ) values (
29 X_CATALOG_ID,
30 X_INTERNAL_NAME,
31 X_CREATION_DATE,
32 X_CREATED_BY,
33 X_LAST_UPDATE_DATE,
34 X_LAST_UPDATED_BY,
35 X_LAST_UPDATE_LOGIN
36 );
37
38 insert into DOM_DOCUMENT_CATALOGS_TL (
39 CATALOG_ID,
40 INTERNAL_NAME,
41 NAME,
42 DESCRIPTION,
43 CREATED_BY,
44 CREATION_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATE_LOGIN,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_CATALOG_ID,
52 X_INTERNAL_NAME,
53 X_NAME,
54 X_DESCRIPTION,
55 X_CREATED_BY,
56 X_CREATION_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_DATE,
59 X_LAST_UPDATE_LOGIN,
60 L.LANGUAGE_CODE,
61 userenv('LANG')
62 from FND_LANGUAGES L
63 where L.INSTALLED_FLAG in ('I', 'B')
64 and not exists
65 (select NULL
66 from DOM_DOCUMENT_CATALOGS_TL T
67 where T.CATALOG_ID = X_CATALOG_ID
68 and T.LANGUAGE = L.LANGUAGE_CODE);
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_CATALOG_ID in NUMBER,
82 X_INTERNAL_NAME in VARCHAR2,
83 X_NAME in VARCHAR2,
84 X_DESCRIPTION in VARCHAR2
85 ) is
86 cursor c is select
87 INTERNAL_NAME
88 from DOM_DOCUMENT_CATALOGS
89 where CATALOG_ID = X_CATALOG_ID
90 for update of CATALOG_ID nowait;
91 recinfo c%rowtype;
92
93 cursor c1 is select
94 NAME,
95 DESCRIPTION,
96 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
97 from DOM_DOCUMENT_CATALOGS_TL
98 where CATALOG_ID = X_CATALOG_ID
99 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
100 for update of CATALOG_ID nowait;
101 begin
102 open c;
103 fetch c into recinfo;
104 if (c%notfound) then
105 close c;
106 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
107 app_exception.raise_exception;
108 end if;
109 close c;
110 if ( ((recinfo.INTERNAL_NAME = X_INTERNAL_NAME)
111 OR ((recinfo.INTERNAL_NAME is null) AND (X_INTERNAL_NAME is null)))
112 ) then
113 null;
114 else
115 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
116 app_exception.raise_exception;
117 end if;
118
119 for tlinfo in c1 loop
120 if (tlinfo.BASELANG = 'Y') then
121 if ( ((tlinfo.NAME = X_NAME)
122 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
123 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
124 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 end if;
132 end loop;
133 return;
134 end LOCK_ROW;
135
136 procedure UPDATE_ROW (
137 X_CATALOG_ID in NUMBER,
138 X_INTERNAL_NAME in VARCHAR2,
139 X_NAME in VARCHAR2,
140 X_DESCRIPTION in VARCHAR2,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update DOM_DOCUMENT_CATALOGS set
147 INTERNAL_NAME = X_INTERNAL_NAME,
148 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
149 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
150 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
151 where CATALOG_ID = X_CATALOG_ID;
152
153 if (sql%notfound) then
154 raise no_data_found;
155 end if;
156
157 update DOM_DOCUMENT_CATALOGS_TL set
158 NAME = X_NAME,
159 DESCRIPTION = X_DESCRIPTION,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
163 SOURCE_LANG = userenv('LANG')
164 where CATALOG_ID = X_CATALOG_ID
165 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170 end UPDATE_ROW;
171
172 procedure DELETE_ROW (
173 X_CATALOG_ID in NUMBER
174 ) is
175 begin
176 delete from DOM_DOCUMENT_CATALOGS_TL
177 where CATALOG_ID = X_CATALOG_ID;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 delete from DOM_DOCUMENT_CATALOGS
184 where CATALOG_ID = X_CATALOG_ID;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end DELETE_ROW;
190
191 procedure ADD_LANGUAGE
192 is
193 begin
194 delete from DOM_DOCUMENT_CATALOGS_TL T
195 where not exists
196 (select NULL
197 from DOM_DOCUMENT_CATALOGS B
198 where B.CATALOG_ID = T.CATALOG_ID
199 );
200
201 update DOM_DOCUMENT_CATALOGS_TL T set (
202 NAME,
203 DESCRIPTION
204 ) = (select
205 B.NAME,
206 B.DESCRIPTION
207 from DOM_DOCUMENT_CATALOGS_TL B
208 where B.CATALOG_ID = T.CATALOG_ID
209 and B.LANGUAGE = T.SOURCE_LANG)
210 where (
211 T.CATALOG_ID,
212 T.LANGUAGE
213 ) in (select
214 SUBT.CATALOG_ID,
215 SUBT.LANGUAGE
216 from DOM_DOCUMENT_CATALOGS_TL SUBB, DOM_DOCUMENT_CATALOGS_TL SUBT
217 where SUBB.CATALOG_ID = SUBT.CATALOG_ID
218 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
219 and (SUBB.NAME <> SUBT.NAME
220 or (SUBB.NAME is null and SUBT.NAME is not null)
221 or (SUBB.NAME is not null and SUBT.NAME is null)
222 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
223 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
224 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
225 ));
226
227 insert into DOM_DOCUMENT_CATALOGS_TL (
228 CATALOG_ID,
229 INTERNAL_NAME,
230 NAME,
231 DESCRIPTION,
232 CREATED_BY,
233 CREATION_DATE,
234 LAST_UPDATED_BY,
235 LAST_UPDATE_DATE,
236 LAST_UPDATE_LOGIN,
237 LANGUAGE,
238 SOURCE_LANG
239 ) select /*+ ORDERED */
240 B.CATALOG_ID,
241 B.INTERNAL_NAME,
242 B.NAME,
243 B.DESCRIPTION,
244 B.CREATED_BY,
245 B.CREATION_DATE,
246 B.LAST_UPDATED_BY,
247 B.LAST_UPDATE_DATE,
248 B.LAST_UPDATE_LOGIN,
249 L.LANGUAGE_CODE,
250 B.SOURCE_LANG
251 from DOM_DOCUMENT_CATALOGS_TL B, FND_LANGUAGES L
252 where L.INSTALLED_FLAG in ('I', 'B')
253 and B.LANGUAGE = userenv('LANG')
254 and not exists
255 (select NULL
256 from DOM_DOCUMENT_CATALOGS_TL T
257 where T.CATALOG_ID = B.CATALOG_ID
258 and T.LANGUAGE = L.LANGUAGE_CODE);
259 end ADD_LANGUAGE;
260
261 end DOM_DOCUMENT_CATALOGS_PKG;
[Home] [Help]