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