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