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