[Home] [Help]
PACKAGE BODY: APPS.IEU_WP_PARAM_DEFS_SEED_PKG
Source
1 PACKAGE BODY IEU_WP_PARAM_DEFS_SEED_PKG AS
2 /* $Header: IEUWACPB.pls 120.1 2005/07/07 02:25:50 appldev ship $ */
3
4 PROCEDURE Update_Row (p_WP_PARAM_DEFS_rec IN WP_PARAM_DEFS_rec_type) IS
5
6 BEGIN
7 -- API body
8 UPDATE ieu_WP_PARAM_DEFS_b SET
9 last_updated_by = p_WP_PARAM_DEFS_rec.last_updated_by,
10 last_update_date = p_WP_PARAM_DEFS_rec.last_update_date,
11 last_update_login = p_WP_PARAM_DEFS_rec.last_update_login,
12 param_name = p_WP_PARAM_DEFS_rec.param_name,
13 data_type = p_WP_PARAM_DEFS_rec.data_type
14 WHERE PARAM_ID = p_WP_PARAM_DEFS_rec.PARAM_ID;
15
16 IF (SQL%NOTFOUND) THEN
17 RAISE no_data_found;
18 END IF;
19
20 UPDATE ieu_WP_PARAM_DEFS_tl SET
21 param_user_label = p_WP_PARAM_DEFS_rec.PARAM_USER_LABEL,
22 param_description = p_WP_PARAM_DEFS_rec.PARAM_DESCRIPTION,
23 source_lang = USERENV('LANG'),
24 last_updated_by = p_WP_PARAM_DEFS_rec.last_updated_by,
25 last_update_date = p_WP_PARAM_DEFS_rec.last_update_date,
26 last_update_login = p_WP_PARAM_DEFS_rec.last_update_login
27 WHERE PARAM_ID = p_WP_PARAM_DEFS_rec.PARAM_ID
28 AND USERENV('LANG') IN (language, source_lang);
29
30 IF (SQL%NOTFOUND) THEN
31 RAISE no_data_found;
32 END IF;
33 -- End of API body
34
35 END Update_Row;
36
37 PROCEDURE Load_Row (
38 p_PARAM_ID IN NUMBER,
39 p_PARAM_NAME IN VARCHAR2,
40 p_DATA_TYPE IN VARCHAR2,
41 p_param_user_label IN VARCHAR2,
42 p_param_description IN VARCHAR2,
43 p_last_update_date IN VARCHAR2,
44 p_owner IN VARCHAR2) IS
45 BEGIN
46
47 DECLARE
48 user_id number := 0;
49 l_WP_PARAM_DEFS_rec WP_PARAM_DEFS_rec_type;
50 l_last_update_date date;
51 p_application_id number(15);
52
53 BEGIN
54
55 --IF (p_owner = 'SEED') then
56 -- user_id := -1;
57 --END IF;
58
59 user_id := fnd_load_util.owner_id(P_OWNER);
60
61 if (p_last_update_date is null) then
62 l_last_update_date := sysdate;
63 else
64 l_last_update_date := to_date(p_last_update_date, 'YYYY/MM/DD');
65 end if;
66
67 l_WP_PARAM_DEFS_rec.param_id:= p_PARAM_ID;
68 l_WP_PARAM_DEFS_rec.param_name := p_PARAM_NAME;
69 l_WP_PARAM_DEFS_rec.data_type:= p_DATA_TYPE;
70 l_WP_PARAM_DEFS_rec.param_user_label := p_param_user_label;
71 l_WP_PARAM_DEFS_rec.param_description := p_param_description;
72 l_WP_PARAM_DEFS_rec.last_update_date := l_last_update_date;
73 l_WP_PARAM_DEFS_rec.last_updated_by := user_id;
74 l_WP_PARAM_DEFS_rec.last_update_login := 0;
75
76 Update_Row (p_WP_PARAM_DEFS_rec => l_WP_PARAM_DEFS_rec);
77 EXCEPTION
78 when no_data_found then
79
80 l_WP_PARAM_DEFS_rec.param_id:= p_PARAM_ID;
81 l_WP_PARAM_DEFS_rec.param_name := p_PARAM_NAME;
82 l_WP_PARAM_DEFS_rec.data_type := p_DATA_TYPE;
83 l_WP_PARAM_DEFS_rec.param_user_label := p_param_user_label;
84 l_WP_PARAM_DEFS_rec.param_description := p_param_description;
85 l_WP_PARAM_DEFS_rec.last_update_date := l_last_update_date;
86 l_WP_PARAM_DEFS_rec.last_updated_by := user_id;
87 l_WP_PARAM_DEFS_rec.last_update_login := 0;
88 l_WP_PARAM_DEFS_rec.creation_date := sysdate;
89 l_WP_PARAM_DEFS_rec.created_by := user_id;
90
91 Insert_Row (p_WP_PARAM_DEFS_rec => l_WP_PARAM_DEFS_rec);
92
93 END;
94 END Load_Row;
95
96
97
98 PROCEDURE translate_row (
99 p_PARAM_ID IN NUMBER,
100 p_param_user_label IN VARCHAR2,
101 p_param_description IN VARCHAR2,
102 p_last_update_date IN VARCHAR2,
103 p_owner IN VARCHAR2) IS
104
105 user_id number := 0;
106
107 BEGIN
108
109 -- only UPDATE rows that have not been altered by user
110
111 user_id := fnd_load_util.owner_id(P_OWNER);
112
113 UPDATE ieu_WP_PARAM_DEFS_tl SET
114 param_user_label = p_param_user_label,
115 param_description = p_param_description,
116 source_lang = userenv('LANG'),
117 last_update_date = decode(p_last_update_date, null,sysdate,to_date(p_last_update_date,'YYYY/MM/DD')),
118 --last_updated_by = decode(p_owner, 'SEED', -1, 0),
119 last_updated_by = user_id,
120 last_update_login = 0
121 WHERE PARAM_ID = p_PARAM_ID
122 AND userenv('LANG') IN (language, source_lang);
123 end translate_row;
124
125
126
127 procedure DELETE_ROW (
128 r_PARAM_ID in NUMBER
129 ) is
130 begin
131 delete from IEU_WP_PARAM_DEFS_TL
132 where PARAM_ID = r_PARAM_ID;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137
138 delete from IEU_WP_PARAM_DEFS_B
139 where PARAM_ID = r_PARAM_ID;
140
141 if (sql%notfound) then
142 raise no_data_found;
143 end if;
144 end DELETE_ROW;
145
146 PROCEDURE Insert_Row (p_WP_PARAM_DEFS_rec IN WP_PARAM_DEFS_rec_type) IS
147
148 CURSOR c IS SELECT 'X' FROM ieu_WP_PARAM_DEFS_b
149 WHERE PARAM_ID = p_WP_PARAM_DEFS_rec.PARAM_ID;
150
151 l_dummy CHAR(1);
152
153 BEGIN
154
155 -- API body
156 insert into IEU_WP_PARAM_DEFS_B
157 ( PARAM_ID,
158 CREATED_BY,
159 CREATION_DATE,
160 LAST_UPDATED_BY,
161 LAST_UPDATE_DATE,
162 LAST_UPDATE_LOGIN,
163 PARAM_NAME,
164 DATA_TYPE,
165 OBJECT_VERSION_NUMBER)
166 values( p_WP_PARAM_DEFS_rec.PARAM_ID,
167 FND_GLOBAL.USER_ID,
168 SYSDATE,
169 FND_GLOBAL.USER_ID,
170 SYSDATE,
171 FND_GLOBAL.LOGIN_ID,
172 p_WP_PARAM_DEFS_rec.param_name,
173 p_WP_PARAM_DEFS_rec.data_type,
174 1);
175 INSERT INTO ieu_WP_PARAM_DEFS_tl (
176 PARAM_ID,
177 language,
178 created_by,
179 creation_date,
180 last_updated_by,
181 last_update_date,
182 last_update_login,
183 param_user_label,
184 param_description,
185 object_version_number,
186 source_lang
187 ) SELECT
188 p_WP_PARAM_DEFS_rec.PARAM_ID,
189 l.language_code,
190 p_WP_PARAM_DEFS_rec.created_by,
191 p_WP_PARAM_DEFS_rec.creation_date,
192 p_WP_PARAM_DEFS_rec.last_updated_by,
193 p_WP_PARAM_DEFS_rec.last_update_date,
194 p_WP_PARAM_DEFS_rec.last_update_login,
195 p_WP_PARAM_DEFS_rec.param_user_label,
196 p_WP_PARAM_DEFS_rec.param_description,
197 1,
198 USERENV('LANG')
199 FROM fnd_languages l
200 WHERE l.installed_flag IN ('I', 'B')
201 AND NOT EXISTS
202 (SELECT NULL
203 FROM ieu_WP_PARAM_DEFS_tl t
204 WHERE t.PARAM_ID = p_WP_PARAM_DEFS_rec.PARAM_ID
205 AND t.language = l.language_code);
206
207 OPEN c;
208 FETCH c INTO l_dummy;
209 IF (c%NOTFOUND) THEN
210 CLOSE c;
211 RAISE NO_DATA_FOUND;
212 END IF;
213 CLOSE c;
214 -- End of API body
215 END Insert_Row;
216
217 procedure ADD_LANGUAGE
218 is
219 begin
220 delete from IEU_WP_PARAM_DEFS_TL T
221 where not exists
222 (select NULL
223 from IEU_WP_PARAM_DEFS_B B
224 where B.PARAM_ID = T.PARAM_ID
225 );
226
227 update IEU_WP_PARAM_DEFS_TL T set (
228 PARAM_USER_LABEL,
229 PARAM_DESCRIPTION
230 ) = (select
231 B.PARAM_USER_LABEL,
232 B.PARAM_DESCRIPTION
233 from IEU_WP_PARAM_DEFS_TL B
234 where B.PARAM_ID = T.PARAM_ID
235 and B.LANGUAGE = T.SOURCE_LANG)
236 where (
237 T.PARAM_ID,
238 T.LANGUAGE
239 ) in (select
240 SUBT.PARAM_ID,
241 SUBT.LANGUAGE
242 from IEU_WP_PARAM_DEFS_TL SUBB, IEU_WP_PARAM_DEFS_TL SUBT
243 where SUBB.PARAM_ID = SUBT.PARAM_ID
244 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
245 and (SUBB.PARAM_USER_LABEL <> SUBT.PARAM_USER_LABEL
246 or SUBB.PARAM_DESCRIPTION <> SUBT.PARAM_DESCRIPTION
247 or (SUBB.PARAM_DESCRIPTION is null and SUBT.PARAM_DESCRIPTION is not null)
248 or (SUBB.PARAM_DESCRIPTION is not null and SUBT.PARAM_DESCRIPTION is null)
249 ));
250
251 insert into IEU_WP_PARAM_DEFS_TL (
252 PARAM_ID,
253 OBJECT_VERSION_NUMBER,
254 CREATED_BY,
255 CREATION_DATE,
256 LAST_UPDATED_BY,
257 LAST_UPDATE_DATE,
258 LAST_UPDATE_LOGIN,
259 SECURITY_GROUP_ID,
260 PARAM_USER_LABEL,
261 PARAM_DESCRIPTION,
262 LANGUAGE,
263 SOURCE_LANG
264 ) select
265 B.PARAM_ID,
266 B.OBJECT_VERSION_NUMBER,
267 B.CREATED_BY,
268 B.CREATION_DATE,
269 B.LAST_UPDATED_BY,
270 B.LAST_UPDATE_DATE,
271 B.LAST_UPDATE_LOGIN,
272 B.SECURITY_GROUP_ID,
273 B.PARAM_USER_LABEL,
274 B.PARAM_DESCRIPTION,
275 L.LANGUAGE_CODE,
276 B.SOURCE_LANG
277 from IEU_WP_PARAM_DEFS_TL B, FND_LANGUAGES L
278 where L.INSTALLED_FLAG in ('I', 'B')
279 and B.LANGUAGE = userenv('LANG')
280 and not exists
281 (select NULL
282 from IEU_WP_PARAM_DEFS_TL T
283 where T.PARAM_ID = B.PARAM_ID
284 and T.LANGUAGE = L.LANGUAGE_CODE);
285 end ADD_LANGUAGE;
286
287 PROCEDURE Load_Seed_Row (
288 p_upload_mode IN VARCHAR2,
289 p_PARAM_ID IN NUMBER,
290 p_PARAM_NAME IN VARCHAR2,
291 p_DATA_TYPE IN VARCHAR2,
292 p_param_user_label IN VARCHAR2,
293 p_param_description IN VARCHAR2,
294 p_last_update_date IN VARCHAR2,
295 p_owner IN VARCHAR2
296 )is
297 user_id number := FND_GLOBAL.USER_ID;
298 begin
299
300 if (p_upload_mode = 'NLS') then
301 TRANSLATE_ROW (
302 P_PARAM_ID,
303 P_PARAM_USER_LABEL,
304 P_PARAM_DESCRIPTION,
305 P_LAST_UPDATE_DATE,
306 P_OWNER);
307 else
308 LOAD_ROW (
309 P_PARAM_ID,
310 P_PARAM_NAME,
311 P_DATA_TYPE,
312 P_PARAM_USER_LABEL,
313 P_PARAM_DESCRIPTION,
314 P_LAST_UPDATE_DATE,
315 P_OWNER);
316
317 end if;
318
319 end Load_Seed_Row;
320
321
322 END IEU_WP_PARAM_DEFS_SEED_PKG;