1 package body ECX_STANDARDS_PKG as
2 /* $Header: ECXSTDSB.pls 120.2 2005/06/30 11:18:01 appldev ship $ */
3
4 procedure TRANSLATE_ROW
5 (
6 X_STANDARD_TYPE IN VARCHAR2,
7 X_STANDARD_CODE IN VARCHAR2,
8 X_STANDARD_DESC IN VARCHAR2,
9 X_OWNER IN VARCHAR2,
10 X_CUSTOM_MODE IN VARCHAR2
11 )
12 is
13 l_luby number; -- entity owner in file
14 l_ludate date; -- entity update date in file
15 l_db_luby number; -- entity owner in db
16 l_db_ludate date; -- entity update date in db
17 l_standard_id number;
18 begin
19 -- Translate owner to file_last_updated_by
20 if (x_owner = 'SEED') then
21 l_luby := 1;
22 else
23 l_luby := 0;
24 end if;
25 -- Translate char last_update_date to date
26 l_ludate := sysdate;
27
28 begin
29 select standard_id
30 into l_standard_id
31 from ecx_standards
32 where STANDARD_CODE = X_STANDARD_CODE
33 and STANDARD_TYPE = X_STANDARD_TYPE;
34
35 select LAST_UPDATED_BY,
36 LAST_UPDATE_DATE
37 into l_db_luby,
38 l_db_ludate
39 from ECX_STANDARDS_TL
40 where STANDARD_ID = l_standard_id
41 and LANGUAGE = userenv('LANG');
42
43 -- Update record, honoring customization mode.
44 -- Record should be updated only if:
45 -- a. CUSTOM_MODE = FORCE, or
46 -- b. file owner is CUSTOM, db owner is SEED
47 -- c. owners are the same, and file_date > db_date
48 if ((x_custom_mode = 'FORCE') or
49 ((l_luby = 0) and (l_db_luby = 1)) or
50 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
51 then
52 update ECX_STANDARDS_TL set
53 STANDARD_DESC = nvl(X_STANDARD_DESC, STANDARD_DESC),
54 SOURCE_LANG = userenv('LANG'),
55 LAST_UPDATE_DATE = l_ludate,
56 LAST_UPDATED_BY = l_luby,
57 LAST_UPDATE_LOGIN = 0
58 where STANDARD_ID = l_standard_id
59 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
60 end if;
61 exception
62 when no_data_found then
63 null;
64 end;
65 end TRANSLATE_ROW;
66
67
68 procedure LOAD_ROW (
69 X_STANDARD_TYPE IN VARCHAR2,
70 X_STANDARD_CODE IN VARCHAR2,
71 X_STANDARD_DESC IN VARCHAR2,
72 X_DATA_SEEDED IN VARCHAR2,
73 X_OWNER IN VARCHAR2,
74 X_CUSTOM_MODE IN VARCHAR2
75 )
76 is
77
78 l_row_id varchar2(64);
79 l_standard_id number;
80 l_ret_code pls_integer;
81 l_errmsg varchar2(2000);
82
83 l_luby number; -- entity owner in file
84 l_ludate date; -- entity update date in file
85 l_db_luby number; -- entity owner in db
86 l_db_ludate date; -- entity update date in db
87 l_data_seeded varchar2(1);
88 begin
89 -- Translate owner to file_last_updated_by
90 if (x_owner = 'SEED') then
91 l_luby := 1;
92 l_data_seeded := nvl(X_DATA_SEEDED,'Y');
93 else
94 l_luby := 0;
95 l_data_seeded := nvl(X_DATA_SEEDED,'N');
96 end if;
97 -- Translate char last_update_date to date
98 l_ludate := sysdate;
99
100 begin
101 select STANDARD_ID,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_DATE
104 into l_standard_id,
105 l_db_luby,
106 l_db_ludate
107 from ECX_STANDARDS_B
108 where STANDARD_CODE = X_STANDARD_CODE
109 and STANDARD_TYPE = X_STANDARD_TYPE;
110
111 -- Update record, honoring customization mode.
112 -- Record should be updated only if:
113 -- a. CUSTOM_MODE = FORCE, or
114 -- b. file owner is CUSTOM, db owner is SEED
115 -- c. owners are the same, and file_date > db_date
116 if ((x_custom_mode = 'FORCE') or
117 ((l_luby = 0) and (l_db_luby = 1)) or
118 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
119 then
120 ecx_document_standards_api.update_standard
121 (
122 x_return_status => l_ret_code,
123 x_msg => l_errmsg,
124 p_standard_id => l_standard_id,
125 p_standard_desc => X_STANDARD_DESC,
126 p_data_seeded => l_data_seeded,
127 p_owner => X_OWNER
128 );
129 if (l_ret_code <> ECX_UTIL_API.G_NO_ERROR) then
130 raise_application_error(-20000, l_errmsg);
131 end if;
132 end if;
133 exception
134 when no_data_found then
135 ecx_document_standards_api.create_standard
136 (
137 x_return_status => l_ret_code,
138 x_msg => l_errmsg,
139 x_standard_id => l_standard_id,
140 p_standard_code => X_STANDARD_CODE,
141 p_standard_type => X_STANDARD_TYPE,
142 p_standard_desc => X_STANDARD_DESC,
143 p_data_seeded => l_data_seeded,
144 p_owner => X_OWNER
145 );
146
147 if (l_ret_code <> ECX_UTIL_API.G_NO_ERROR) then
148 raise_application_error(-20000, l_errmsg);
149 end if;
150 end;
151
152 end LOAD_ROW;
153
154 procedure INSERT_ROW (
155 X_ROWID in out nocopy VARCHAR2,
156 X_STANDARD_ID in NUMBER,
157 X_STANDARD_CODE in VARCHAR2,
158 X_STANDARD_TYPE in VARCHAR2,
159 X_DATA_SEEDED in VARCHAR2,
160 X_STANDARD_DESC in VARCHAR2,
161 X_CREATION_DATE in DATE,
162 X_CREATED_BY in NUMBER,
163 X_LAST_UPDATE_DATE in DATE,
164 X_LAST_UPDATED_BY in NUMBER,
165 X_LAST_UPDATE_LOGIN in NUMBER
166 ) is
167 cursor C is select ROWID from ECX_STANDARDS_B
168 where STANDARD_ID = X_STANDARD_ID
169 ;
170 begin
171 insert into ECX_STANDARDS_B (
172 STANDARD_ID,
173 STANDARD_CODE,
174 STANDARD_TYPE,
175 DATA_SEEDED,
176 CREATION_DATE,
177 CREATED_BY,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 LAST_UPDATE_LOGIN
181 ) values (
182 X_STANDARD_ID,
183 X_STANDARD_CODE,
184 X_STANDARD_TYPE,
185 X_DATA_SEEDED,
186 X_CREATION_DATE,
187 X_CREATED_BY,
188 X_LAST_UPDATE_DATE,
189 X_LAST_UPDATED_BY,
190 X_LAST_UPDATE_LOGIN
191 );
192
193 insert into ECX_STANDARDS_TL (
194 STANDARD_DESC,
195 CREATED_BY,
196 LAST_UPDATED_BY,
197 CREATION_DATE,
198 LAST_UPDATE_DATE,
199 LAST_UPDATE_LOGIN,
200 STANDARD_ID,
201 LANGUAGE,
202 SOURCE_LANG
203 ) select
204 X_STANDARD_DESC,
205 X_CREATED_BY,
206 X_LAST_UPDATED_BY,
207 X_CREATION_DATE,
208 X_LAST_UPDATE_DATE,
209 X_LAST_UPDATE_LOGIN,
210 X_STANDARD_ID,
211 L.CODE,
212 userenv('LANG')
213 from WF_LANGUAGES L
214 where L.INSTALLED_FLAG = 'Y'
215 and not exists
216 (select NULL
217 from ECX_STANDARDS_TL T
218 where T.STANDARD_ID = X_STANDARD_ID
219 and T.LANGUAGE = L.CODE);
220
221 open c;
222 fetch c into X_ROWID;
223 if (c%notfound) then
224 close c;
225 raise no_data_found;
226 end if;
227 close c;
228
229 end INSERT_ROW;
230
231 procedure LOCK_ROW (
232 X_STANDARD_ID in NUMBER,
233 X_STANDARD_CODE in VARCHAR2,
234 X_STANDARD_TYPE in VARCHAR2,
235 X_DATA_SEEDED in VARCHAR2,
236 X_STANDARD_DESC in VARCHAR2
237 ) is
238 cursor c is select
239 STANDARD_CODE,
240 STANDARD_TYPE,
241 DATA_SEEDED
242 from ECX_STANDARDS_B
243 where STANDARD_ID = X_STANDARD_ID
244 for update of STANDARD_ID nowait;
245 recinfo c%rowtype;
246
247 cursor c1 is select
248 STANDARD_DESC,
249 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
250 from ECX_STANDARDS_TL
251 where STANDARD_ID = X_STANDARD_ID
252 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
253 for update of STANDARD_ID nowait;
254 begin
255 open c;
256 fetch c into recinfo;
257 if (c%notfound) then
258 close c;
259 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
260 app_exception.raise_exception;
261 end if;
262 close c;
263 if ( (recinfo.STANDARD_CODE = X_STANDARD_CODE)
264 AND ((recinfo.STANDARD_TYPE = X_STANDARD_TYPE)
265 OR ((recinfo.STANDARD_TYPE is null) AND (X_STANDARD_TYPE is null)))
266 AND ((recinfo.DATA_SEEDED = X_DATA_SEEDED)
267 OR ((recinfo.DATA_SEEDED is null) AND (X_DATA_SEEDED is null)))
268 ) then
269 null;
270 else
271 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272 app_exception.raise_exception;
273 end if;
274
275 for tlinfo in c1 loop
276 if (tlinfo.BASELANG = 'Y') then
277 if ( ((tlinfo.STANDARD_DESC = X_STANDARD_DESC)
278 OR ((tlinfo.STANDARD_DESC is null) AND (X_STANDARD_DESC is null)))
279 ) then
280 null;
281 else
282 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
283 app_exception.raise_exception;
284 end if;
285 end if;
286 end loop;
287 return;
288 end LOCK_ROW;
289
290 procedure UPDATE_ROW (
291 X_STANDARD_ID in NUMBER,
292 X_STANDARD_CODE in VARCHAR2,
293 X_STANDARD_TYPE in VARCHAR2,
294 X_DATA_SEEDED in VARCHAR2,
295 X_STANDARD_DESC in VARCHAR2,
296 X_LAST_UPDATE_DATE in DATE,
297 X_LAST_UPDATED_BY in NUMBER,
298 X_LAST_UPDATE_LOGIN in NUMBER
299 ) is
300 begin
301 update ECX_STANDARDS_B set
302 STANDARD_CODE = X_STANDARD_CODE,
303 STANDARD_TYPE = X_STANDARD_TYPE,
304 DATA_SEEDED = X_DATA_SEEDED,
305 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
306 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
307 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
308 where STANDARD_ID = X_STANDARD_ID;
309
310 if (sql%notfound) then
311 raise no_data_found;
312 end if;
313
314 update ECX_STANDARDS_TL set
315 STANDARD_DESC = X_STANDARD_DESC,
316 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
317 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
318 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
319 SOURCE_LANG = userenv('LANG')
320 where STANDARD_ID = X_STANDARD_ID
321 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
322
323 if (sql%notfound) then
324 raise no_data_found;
325 end if;
326 end UPDATE_ROW;
327
328 procedure DELETE_ROW (
329 X_STANDARD_ID in NUMBER
330 ) is
331 begin
332 delete from ECX_STANDARDS_TL
333 where STANDARD_ID = X_STANDARD_ID;
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 delete from ECX_STANDARDS_B
340 where STANDARD_ID = X_STANDARD_ID;
341
342 if (sql%notfound) then
343 raise no_data_found;
344 end if;
345 end DELETE_ROW;
346
347 procedure ADD_LANGUAGE
348 is
349 begin
350 delete from ECX_STANDARDS_TL T
351 where not exists
352 (select NULL
353 from ECX_STANDARDS_B B
354 where B.STANDARD_ID = T.STANDARD_ID
355 );
356
357 update ECX_STANDARDS_TL T set (
358 STANDARD_DESC
359 ) = (select
360 B.STANDARD_DESC
361 from ECX_STANDARDS_TL B
362 where B.STANDARD_ID = T.STANDARD_ID
363 and B.LANGUAGE = T.SOURCE_LANG)
364 where (
365 T.STANDARD_ID,
366 T.LANGUAGE
367 ) in (select
368 SUBT.STANDARD_ID,
369 SUBT.LANGUAGE
370 from ECX_STANDARDS_TL SUBB, ECX_STANDARDS_TL SUBT
371 where SUBB.STANDARD_ID = SUBT.STANDARD_ID
372 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
373 and (SUBB.STANDARD_DESC <> SUBT.STANDARD_DESC
374 or (SUBB.STANDARD_DESC is null and SUBT.STANDARD_DESC is not null)
375 or (SUBB.STANDARD_DESC is not null and SUBT.STANDARD_DESC is null)
376 ));
377
378 insert into ECX_STANDARDS_TL (
379 STANDARD_DESC,
380 CREATED_BY,
381 LAST_UPDATED_BY,
382 CREATION_DATE,
383 LAST_UPDATE_DATE,
384 LAST_UPDATE_LOGIN,
385 STANDARD_ID,
386 LANGUAGE,
387 SOURCE_LANG
388 ) select
389 B.STANDARD_DESC,
390 B.CREATED_BY,
391 B.LAST_UPDATED_BY,
392 B.CREATION_DATE,
393 B.LAST_UPDATE_DATE,
394 B.LAST_UPDATE_LOGIN,
395 B.STANDARD_ID,
396 L.CODE,
397 B.SOURCE_LANG
398 from ECX_STANDARDS_TL B, WF_LANGUAGES L
399 where L.INSTALLED_FLAG = 'Y'
400 and B.LANGUAGE = userenv('LANG')
401 and not exists
402 (select NULL
403 from ECX_STANDARDS_TL T
404 where T.STANDARD_ID = B.STANDARD_ID
405 and T.LANGUAGE = L.CODE);
406 end ADD_LANGUAGE;
407
408 end ECX_STANDARDS_PKG;
[Home] [Help]