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