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