1 package body FEM_DB_LINKS_PKG as
2 /* $Header: fem_db_links_pkb.plb 120.0 2005/06/06 20:34:48 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_DATABASE_LINK in VARCHAR2,
6 X_MIG_WF_OUT_AGENT_IN_LOCAL_DB in VARCHAR2,
7 X_MIG_WF_IN_AGENT_IN_LINKED_DB in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_DB_LINK_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from FEM_DB_LINKS_B
18 where DATABASE_LINK = X_DATABASE_LINK
19 ;
20 begin
21 insert into FEM_DB_LINKS_B (
22 MIG_WF_OUT_AGENT_IN_LOCAL_DB,
23 DATABASE_LINK,
24 MIG_WF_IN_AGENT_IN_LINKED_DB,
25 OBJECT_VERSION_NUMBER,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_MIG_WF_OUT_AGENT_IN_LOCAL_DB,
33 X_DATABASE_LINK,
34 X_MIG_WF_IN_AGENT_IN_LINKED_DB,
35 X_OBJECT_VERSION_NUMBER,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN
41 );
42
43 insert into FEM_DB_LINKS_TL (
44 DATABASE_LINK,
45 DB_LINK_NAME,
46 DESCRIPTION,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_DATABASE_LINK,
56 X_DB_LINK_NAME,
57 X_DESCRIPTION,
58 X_CREATION_DATE,
59 X_CREATED_BY,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_DATE,
62 X_LAST_UPDATE_LOGIN,
63 L.LANGUAGE_CODE,
64 userenv('LANG')
65 from FND_LANGUAGES L
66 where L.INSTALLED_FLAG in ('I', 'B')
67 and not exists
68 (select NULL
69 from FEM_DB_LINKS_TL T
70 where T.DATABASE_LINK = X_DATABASE_LINK
71 and T.LANGUAGE = L.LANGUAGE_CODE);
72
73 open c;
74 fetch c into X_ROWID;
75 if (c%notfound) then
76 close c;
77 raise no_data_found;
78 end if;
79 close c;
80
81 end INSERT_ROW;
82
83 procedure LOCK_ROW (
84 X_DATABASE_LINK in VARCHAR2,
85 X_MIG_WF_OUT_AGENT_IN_LOCAL_DB in VARCHAR2,
86 X_MIG_WF_IN_AGENT_IN_LINKED_DB in VARCHAR2,
87 X_OBJECT_VERSION_NUMBER in NUMBER,
88 X_DB_LINK_NAME in VARCHAR2,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 MIG_WF_OUT_AGENT_IN_LOCAL_DB,
93 MIG_WF_IN_AGENT_IN_LINKED_DB,
94 OBJECT_VERSION_NUMBER
95 from FEM_DB_LINKS_B
96 where DATABASE_LINK = X_DATABASE_LINK
97 for update of DATABASE_LINK nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 DB_LINK_NAME,
102 DESCRIPTION,
103 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
104 from FEM_DB_LINKS_TL
105 where DATABASE_LINK = X_DATABASE_LINK
106 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
107 for update of DATABASE_LINK nowait;
108 begin
109 open c;
110 fetch c into recinfo;
111 if (c%notfound) then
112 close c;
113 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
114 app_exception.raise_exception;
115 end if;
116 close c;
117 if ( ((recinfo.MIG_WF_OUT_AGENT_IN_LOCAL_DB = X_MIG_WF_OUT_AGENT_IN_LOCAL_DB)
118 OR ((recinfo.MIG_WF_OUT_AGENT_IN_LOCAL_DB is null) AND (X_MIG_WF_OUT_AGENT_IN_LOCAL_DB is null)))
119 AND ((recinfo.MIG_WF_IN_AGENT_IN_LINKED_DB = X_MIG_WF_IN_AGENT_IN_LINKED_DB)
120 OR ((recinfo.MIG_WF_IN_AGENT_IN_LINKED_DB is null) AND (X_MIG_WF_IN_AGENT_IN_LINKED_DB is null)))
121 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 for tlinfo in c1 loop
130 if (tlinfo.BASELANG = 'Y') then
131 if ( ((tlinfo.DB_LINK_NAME = X_DB_LINK_NAME)
132 OR ((tlinfo.DB_LINK_NAME is null) AND (X_DB_LINK_NAME is null)))
133 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
134 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
135 ) then
136 null;
137 else
138 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
139 app_exception.raise_exception;
140 end if;
141 end if;
142 end loop;
143 return;
144 end LOCK_ROW;
145
146 procedure UPDATE_ROW (
147 X_DATABASE_LINK in VARCHAR2,
148 X_MIG_WF_OUT_AGENT_IN_LOCAL_DB in VARCHAR2,
149 X_MIG_WF_IN_AGENT_IN_LINKED_DB in VARCHAR2,
150 X_OBJECT_VERSION_NUMBER in NUMBER,
151 X_DB_LINK_NAME in VARCHAR2,
152 X_DESCRIPTION in VARCHAR2,
153 X_LAST_UPDATE_DATE in DATE,
154 X_LAST_UPDATED_BY in NUMBER,
155 X_LAST_UPDATE_LOGIN in NUMBER
156 ) is
157 begin
158 update FEM_DB_LINKS_B set
159 MIG_WF_OUT_AGENT_IN_LOCAL_DB = X_MIG_WF_OUT_AGENT_IN_LOCAL_DB,
160 MIG_WF_IN_AGENT_IN_LINKED_DB = X_MIG_WF_IN_AGENT_IN_LINKED_DB,
161 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
162 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
163 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
165 where DATABASE_LINK = X_DATABASE_LINK;
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170
171 update FEM_DB_LINKS_TL set
172 DB_LINK_NAME = X_DB_LINK_NAME,
173 DESCRIPTION = X_DESCRIPTION,
174 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
175 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
177 SOURCE_LANG = userenv('LANG')
178 where DATABASE_LINK = X_DATABASE_LINK
179 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
180
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184 end UPDATE_ROW;
185
186 procedure DELETE_ROW (
187 X_DATABASE_LINK in VARCHAR2
188 ) is
189 begin
190 delete from FEM_DB_LINKS_TL
191 where DATABASE_LINK = X_DATABASE_LINK;
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 delete from FEM_DB_LINKS_B
198 where DATABASE_LINK = X_DATABASE_LINK;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end DELETE_ROW;
204
205 procedure ADD_LANGUAGE
206 is
207 begin
208 delete from FEM_DB_LINKS_TL T
209 where not exists
210 (select NULL
211 from FEM_DB_LINKS_B B
212 where B.DATABASE_LINK = T.DATABASE_LINK
213 );
214
215 update FEM_DB_LINKS_TL T set (
216 DB_LINK_NAME,
217 DESCRIPTION
218 ) = (select
219 B.DB_LINK_NAME,
220 B.DESCRIPTION
221 from FEM_DB_LINKS_TL B
222 where B.DATABASE_LINK = T.DATABASE_LINK
223 and B.LANGUAGE = T.SOURCE_LANG)
224 where (
225 T.DATABASE_LINK,
226 T.LANGUAGE
227 ) in (select
228 SUBT.DATABASE_LINK,
229 SUBT.LANGUAGE
230 from FEM_DB_LINKS_TL SUBB, FEM_DB_LINKS_TL SUBT
231 where SUBB.DATABASE_LINK = SUBT.DATABASE_LINK
232 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
233 and (SUBB.DB_LINK_NAME <> SUBT.DB_LINK_NAME
234 or (SUBB.DB_LINK_NAME is null and SUBT.DB_LINK_NAME is not null)
235 or (SUBB.DB_LINK_NAME is not null and SUBT.DB_LINK_NAME is null)
236 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
237 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
238 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
239 ));
240
241 insert into FEM_DB_LINKS_TL (
242 DATABASE_LINK,
243 DB_LINK_NAME,
244 DESCRIPTION,
245 CREATION_DATE,
246 CREATED_BY,
247 LAST_UPDATED_BY,
248 LAST_UPDATE_DATE,
249 LAST_UPDATE_LOGIN,
250 LANGUAGE,
251 SOURCE_LANG
252 ) select /*+ ORDERED */
253 B.DATABASE_LINK,
254 B.DB_LINK_NAME,
255 B.DESCRIPTION,
256 B.CREATION_DATE,
257 B.CREATED_BY,
258 B.LAST_UPDATED_BY,
259 B.LAST_UPDATE_DATE,
260 B.LAST_UPDATE_LOGIN,
261 L.LANGUAGE_CODE,
262 B.SOURCE_LANG
263 from FEM_DB_LINKS_TL B, FND_LANGUAGES L
264 where L.INSTALLED_FLAG in ('I', 'B')
265 and B.LANGUAGE = userenv('LANG')
266 and not exists
267 (select NULL
268 from FEM_DB_LINKS_TL T
269 where T.DATABASE_LINK = B.DATABASE_LINK
270 and T.LANGUAGE = L.LANGUAGE_CODE);
271 end ADD_LANGUAGE;
272 PROCEDURE TRANSLATE_ROW(
273 x_DATABASE_LINK in varchar2,
274 x_owner in varchar2,
275 x_last_update_date in varchar2,
276 x_DB_LINK_NAME in varchar2,
277 x_description in varchar2,
278 x_custom_mode in varchar2) is
279
280 owner_id number;
281 ludate date;
282 row_id varchar2(64);
283 f_luby number; -- entity owner in file
284 f_ludate date; -- entity update date in file
285 db_luby number; -- entity owner in db
286 db_ludate date; -- entity update date in db
287 begin
288
289
290 -- Translate owner to file_last_updated_by
291 f_luby := fnd_load_util.owner_id(x_owner);
292
293 -- Translate char last_update_date to date
294 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
295 begin
296 select LAST_UPDATED_BY, LAST_UPDATE_DATE
297 into db_luby, db_ludate
298 from FEM_DB_LINKS_TL
299 where DATABASE_LINK = x_DATABASE_LINK
300 and LANGUAGE = userenv('LANG');
301
302 -- Test for customization and version
303 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
304 db_ludate, x_custom_mode)) then
305 -- Update translations for this language
306 update FEM_DB_LINKS_TL set
307 DB_LINK_NAME = decode(x_DB_LINK_NAME,
308 fnd_load_util.null_value, null, -- Real null
309 null, x_DB_LINK_NAME, -- No change
310 x_DB_LINK_NAME),
311 DESCRIPTION = nvl(x_description, DESCRIPTION),
312 LAST_UPDATE_DATE = f_ludate,
313 LAST_UPDATED_BY = f_luby,
314 LAST_UPDATE_LOGIN = 0,
315 SOURCE_LANG = userenv('LANG')
316 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
317 and DATABASE_LINK = x_DATABASE_LINK;
318 end if;
319 exception
320 when no_data_found then
321 -- Do not insert missing translations, skip this row
322 null;
323 end;
324 end TRANSLATE_ROW;
325
326
327 end FEM_DB_LINKS_PKG;
[Home] [Help]