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