[Home] [Help]
PACKAGE BODY: APPS.BEN_STARTUP_ACTN_TYP_PKG
Source
1 package body BEN_STARTUP_ACTN_TYP_PKG as
2 /* $Header: besat01t.pkb 120.0 2005/05/28 11:48:04 appldev noship $ */
3 procedure OWNER_TO_WHO (
4 P_OWNER in VARCHAR2,
5 P_CREATION_DATE out nocopy DATE,
6 P_CREATED_BY out nocopy NUMBER,
7 P_LAST_UPDATE_DATE out nocopy DATE,
8 P_LAST_UPDATED_BY out nocopy NUMBER,
9 P_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if P_OWNER = 'SEED' then
13 P_CREATED_BY := 1;
14 P_LAST_UPDATED_BY := 1;
15 else
16 P_CREATED_BY := 0;
17 P_LAST_UPDATED_BY := 0;
18 end if;
19 P_CREATION_DATE := sysdate;
20 P_LAST_UPDATE_DATE := sysdate;
21 P_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23
24 procedure INSERT_ROW (
25 P_ROWID in out nocopy VARCHAR2,
26 P_TYPE_CD in VARCHAR2,
27 P_DESCRIPTION in VARCHAR2,
28 P_NAME in VARCHAR2,
29 P_CREATION_DATE in DATE,
30 P_CREATED_BY in NUMBER,
31 P_LAST_UPDATE_DATE in DATE,
32 P_LAST_UPDATED_BY in NUMBER,
33 P_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from BEN_STARTUP_ACTN_TYP
36 where TYPE_CD = P_TYPE_CD
37 ;
38 begin
39 insert into BEN_STARTUP_ACTN_TYP (
40 TYPE_CD,
41 DESCRIPTION,
42 NAME,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN
48 ) values (
49 P_TYPE_CD,
50 P_DESCRIPTION,
51 P_NAME,
52 P_CREATION_DATE,
53 P_CREATED_BY,
54 P_LAST_UPDATE_DATE,
55 P_LAST_UPDATED_BY,
56 P_LAST_UPDATE_LOGIN
57 );
58
59 insert into BEN_STARTUP_ACTN_TYP_TL (
60 LAST_UPDATE_LOGIN,
61 CREATED_BY,
62 CREATION_DATE,
63 DESCRIPTION,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 NAME,
67 TYPE_CD,
68 LANGUAGE,
69 SOURCE_LANG
70 ) select
71 P_LAST_UPDATE_LOGIN,
72 P_CREATED_BY,
73 P_CREATION_DATE,
74 P_DESCRIPTION,
75 P_LAST_UPDATE_DATE,
76 P_LAST_UPDATED_BY,
77 P_NAME,
78 P_TYPE_CD,
79 L.LANGUAGE_CODE,
80 userenv('LANG')
81 from FND_LANGUAGES L
82 where L.INSTALLED_FLAG in ('I', 'B')
83 and not exists
84 (select NULL
85 from BEN_STARTUP_ACTN_TYP_TL T
86 where T.TYPE_CD = P_TYPE_CD
87 and T.LANGUAGE = L.LANGUAGE_CODE);
88
89 open c;
90 fetch c into P_ROWID;
91 if (c%notfound) then
92 close c;
93 raise no_data_found;
94 end if;
95 close c;
96
97 end INSERT_ROW;
98
99 procedure LOCK_ROW (
100 P_TYPE_CD in VARCHAR2,
101 P_DESCRIPTION in VARCHAR2,
102 P_NAME in VARCHAR2
103 ) is
104 cursor c is select
105 DESCRIPTION
106 from BEN_STARTUP_ACTN_TYP
107 where TYPE_CD = P_TYPE_CD
108 for update of TYPE_CD nowait;
109 recinfo c%rowtype;
110
111 cursor c1 is select
112 NAME,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from BEN_STARTUP_ACTN_TYP_TL
115 where TYPE_CD = P_TYPE_CD
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of TYPE_CD nowait;
118 begin
119 open c;
120 fetch c into recinfo;
121 if (c%notfound) then
122 close c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 end if;
126 close c;
127 if ( (recinfo.DESCRIPTION = P_DESCRIPTION)
128 ) then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134
135 for tlinfo in c1 loop
136 if (tlinfo.BASELANG = 'Y') then
137 if ( (tlinfo.NAME = P_NAME)
138 ) then
139 null;
140 else
141 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
142 app_exception.raise_exception;
143 end if;
144 end if;
145 end loop;
146 return;
147 end LOCK_ROW;
148
149 procedure UPDATE_ROW (
150 P_TYPE_CD in VARCHAR2,
151 P_DESCRIPTION in VARCHAR2,
152 P_NAME in VARCHAR2,
153 P_LAST_UPDATE_DATE in DATE,
154 P_LAST_UPDATED_BY in NUMBER,
155 P_LAST_UPDATE_LOGIN in NUMBER
156 ) is
157 begin
158 update BEN_STARTUP_ACTN_TYP set
159 NAME = decode(userenv('LANG'),'US',P_NAME,NAME),
160 DESCRIPTION = P_DESCRIPTION,
161 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
162 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
163 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
164 where TYPE_CD = P_TYPE_CD;
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169
170 update BEN_STARTUP_ACTN_TYP_TL set
171 NAME = P_NAME,
172 DESCRIPTION = P_DESCRIPTION,
173 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
174 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
176 SOURCE_LANG = userenv('LANG')
177 where TYPE_CD = P_TYPE_CD
178 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183 end UPDATE_ROW;
184
185 procedure DELETE_ROW (
186 P_TYPE_CD in VARCHAR2
187 ) is
188 begin
189 delete from BEN_STARTUP_ACTN_TYP_TL
190 where TYPE_CD = P_TYPE_CD;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195
196 delete from BEN_STARTUP_ACTN_TYP
197 where TYPE_CD = P_TYPE_CD;
198
199 if (sql%notfound) then
200 raise no_data_found;
201 end if;
202 end DELETE_ROW;
203
204 PROCEDURE LOAD_ROW (
205 P_TYPE_CD in VARCHAR2,
206 P_DESCRIPTION in VARCHAR2,
207 P_NAME in VARCHAR2,
208 P_OWNER in VARCHAR2
209 )is
210 --
211 L_ROWID ROWID;
212 L_CREATION_DATE DATE;
213 L_CREATED_BY NUMBER;
214 L_LAST_UPDATE_DATE DATE;
215 L_LAST_UPDATED_BY NUMBER;
216 L_LAST_UPDATE_LOGIN NUMBER;
217 begin
218 OWNER_TO_WHO (
219 P_OWNER,
220 L_CREATION_DATE,
221 L_CREATED_BY,
222 L_LAST_UPDATE_DATE,
223 L_LAST_UPDATED_BY,
224 L_LAST_UPDATE_LOGIN
225 );
226 UPDATE_ROW (
227 P_TYPE_CD ,
228 P_DESCRIPTION ,
229 P_NAME ,
230 L_LAST_UPDATE_DATE ,
231 L_LAST_UPDATED_BY ,
232 L_LAST_UPDATE_LOGIN
233 );
234 exception
235 when no_data_found then
236 INSERT_ROW (
237 L_ROWID,
238 P_TYPE_CD ,
239 P_DESCRIPTION ,
240 P_NAME,
241 L_CREATION_DATE,
242 L_CREATED_BY,
243 L_LAST_UPDATE_DATE,
244 L_LAST_UPDATED_BY,
245 L_LAST_UPDATE_LOGIN);
246 end;
247 procedure translate_row(
248 P_TYPE_CD in VARCHAR2,
249 P_DESCRIPTION in VARCHAR2,
250 P_NAME in VARCHAR2,
251 P_OWNER IN VARCHAR2) is
252 --
253 L_CREATION_DATE DATE;
254 L_CREATED_BY NUMBER;
255 L_LAST_UPDATE_DATE DATE;
256 L_LAST_UPDATED_BY NUMBER;
257 L_LAST_UPDATE_LOGIN NUMBER;
258 begin
259 OWNER_TO_WHO (
260 P_OWNER,
261 L_CREATION_DATE,
262 L_CREATED_BY,
263 L_LAST_UPDATE_DATE,
264 L_LAST_UPDATED_BY,
265 L_LAST_UPDATE_LOGIN
266 );
267 --
268 update BEN_STARTUP_ACTN_TYP_TL set
269 NAME = P_NAME,
270 DESCRIPTION = P_DESCRIPTION ,
271 LAST_UPDATE_DATE = L_LAST_UPDATE_DATE,
272 LAST_UPDATED_BY = L_LAST_UPDATED_BY,
273 LAST_UPDATE_LOGIN = L_LAST_UPDATE_LOGIN,
274 SOURCE_LANG = userenv('LANG')
275 where TYPE_CD = P_TYPE_CD
276 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
277 end;
278
279
280 procedure ADD_LANGUAGE
281 is
282 begin
283 delete from BEN_STARTUP_ACTN_TYP_TL T
284 where not exists
285 (select NULL
286 from BEN_STARTUP_ACTN_TYP B
287 where B.TYPE_CD = T.TYPE_CD
288 );
289
290 update BEN_STARTUP_ACTN_TYP_TL T set (
291 NAME , DESCRIPTION
292 ) = (select
293 B.NAME , B.DESCRIPTION
294 from BEN_STARTUP_ACTN_TYP_TL B
295 where B.TYPE_CD = T.TYPE_CD
296 and B.LANGUAGE = T.SOURCE_LANG)
297 where (
298 T.TYPE_CD,
299 T.LANGUAGE
300 ) in (select
301 SUBT.TYPE_CD,
302 SUBT.LANGUAGE
303 from BEN_STARTUP_ACTN_TYP_TL SUBB, BEN_STARTUP_ACTN_TYP_TL SUBT
304 where SUBB.TYPE_CD = SUBT.TYPE_CD
305 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
306 and (SUBB.NAME <> SUBT.NAME
307 ));
308
309 insert into BEN_STARTUP_ACTN_TYP_TL (
310 LAST_UPDATE_LOGIN,
311 CREATED_BY,
312 CREATION_DATE,
313 DESCRIPTION,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY,
316 NAME,
317 TYPE_CD,
318 LANGUAGE,
319 SOURCE_LANG
320 ) select
321 B.LAST_UPDATE_LOGIN,
322 B.CREATED_BY,
323 B.CREATION_DATE,
324 B.DESCRIPTION,
325 B.LAST_UPDATE_DATE,
326 B.LAST_UPDATED_BY,
327 B.NAME,
328 B.TYPE_CD,
329 L.LANGUAGE_CODE,
330 B.SOURCE_LANG
331 from BEN_STARTUP_ACTN_TYP_TL B, FND_LANGUAGES L
332 where L.INSTALLED_FLAG in ('I', 'B')
333 and B.LANGUAGE = userenv('LANG')
334 and not exists
335 (select NULL
336 from BEN_STARTUP_ACTN_TYP_TL T
337 where T.TYPE_CD = B.TYPE_CD
338 and T.LANGUAGE = L.LANGUAGE_CODE);
339 end ADD_LANGUAGE;
340
341 end BEN_STARTUP_ACTN_TYP_PKG;