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