[Home] [Help]
PACKAGE BODY: APPS.BNE_PARAM_OVERRIDES_PKG
Source
1 package body BNE_PARAM_OVERRIDES_PKG as
2 /* $Header: bneparamovb.pls 120.2 2005/06/29 03:40:30 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_PARAM_DEFN_CODE in VARCHAR2,
8 X_LVL in NUMBER,
9 X_OVERRIDE_ID in NUMBER,
10 X_OBJECT_VERSION_NUMBER in NUMBER,
11 X_REQUIRED_FLAG in VARCHAR2,
12 X_VISIBLE_FLAG in VARCHAR2,
13 X_USER_MODIFYABLE_FLAG in VARCHAR2,
14 X_DEFAULT_DATE in DATE,
15 X_DEFAULT_NUMBER in NUMBER,
16 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
17 X_DEFAULT_FORMULA in VARCHAR2,
18 X_DESC_VALUE in VARCHAR2,
19 X_DEFAULT_STRING in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26 cursor C is select ROWID from BNE_PARAM_OVERRIDES
27 where APPLICATION_ID = X_APPLICATION_ID
28 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
29 and LVL = X_LVL
30 and OVERRIDE_ID = X_OVERRIDE_ID
31 ;
32 begin
33 insert into BNE_PARAM_OVERRIDES (
34 APPLICATION_ID,
35 PARAM_DEFN_CODE,
36 LVL,
37 OVERRIDE_ID,
38 OBJECT_VERSION_NUMBER,
39 REQUIRED_FLAG,
40 VISIBLE_FLAG,
41 USER_MODIFYABLE_FLAG,
42 DEFAULT_STRING,
43 DEFAULT_DATE,
44 DEFAULT_NUMBER,
45 DEFAULT_BOOLEAN_FLAG,
46 DEFAULT_FORMULA,
47 DESC_VALUE,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN
53 ) values (
54 X_APPLICATION_ID,
55 X_PARAM_DEFN_CODE,
56 X_LVL,
57 X_OVERRIDE_ID,
58 X_OBJECT_VERSION_NUMBER,
59 X_REQUIRED_FLAG,
60 X_VISIBLE_FLAG,
61 X_USER_MODIFYABLE_FLAG,
62 X_DEFAULT_STRING,
63 X_DEFAULT_DATE,
64 X_DEFAULT_NUMBER,
65 X_DEFAULT_BOOLEAN_FLAG,
66 X_DEFAULT_FORMULA,
67 X_DESC_VALUE,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 X_LAST_UPDATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATE_LOGIN
73 );
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure LOCK_ROW (
86 X_APPLICATION_ID in NUMBER,
87 X_PARAM_DEFN_CODE in VARCHAR2,
88 X_LVL in NUMBER,
89 X_OVERRIDE_ID in NUMBER,
90 X_OBJECT_VERSION_NUMBER in NUMBER,
91 X_REQUIRED_FLAG in VARCHAR2,
92 X_VISIBLE_FLAG in VARCHAR2,
93 X_USER_MODIFYABLE_FLAG in VARCHAR2,
94 X_DEFAULT_DATE in DATE,
95 X_DEFAULT_NUMBER in NUMBER,
96 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
97 X_DEFAULT_FORMULA in VARCHAR2,
98 X_DESC_VALUE in VARCHAR2,
99 X_DEFAULT_STRING in VARCHAR2
100 ) is
101 cursor c1 is select
102 OBJECT_VERSION_NUMBER,
103 REQUIRED_FLAG,
104 VISIBLE_FLAG,
105 USER_MODIFYABLE_FLAG,
106 DEFAULT_DATE,
107 DEFAULT_NUMBER,
108 DEFAULT_BOOLEAN_FLAG,
109 DEFAULT_FORMULA,
110 DESC_VALUE,
111 DEFAULT_STRING
112 from BNE_PARAM_OVERRIDES
113 where APPLICATION_ID = X_APPLICATION_ID
114 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
115 and LVL = X_LVL
116 and OVERRIDE_ID = X_OVERRIDE_ID
117 for update of APPLICATION_ID nowait;
118 begin
119 for tlinfo in c1 loop
120 if ( ((tlinfo.DEFAULT_STRING = X_DEFAULT_STRING)
121 OR ((tlinfo.DEFAULT_STRING is null) AND (X_DEFAULT_STRING is null)))
122 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
123 AND ((tlinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
124 OR ((tlinfo.REQUIRED_FLAG is null) AND (X_REQUIRED_FLAG is null)))
125 AND ((tlinfo.VISIBLE_FLAG = X_VISIBLE_FLAG)
126 OR ((tlinfo.VISIBLE_FLAG is null) AND (X_VISIBLE_FLAG is null)))
127 AND ((tlinfo.USER_MODIFYABLE_FLAG = X_USER_MODIFYABLE_FLAG)
128 OR ((tlinfo.USER_MODIFYABLE_FLAG is null) AND (X_USER_MODIFYABLE_FLAG is null)))
129 AND ((tlinfo.DEFAULT_DATE = X_DEFAULT_DATE)
130 OR ((tlinfo.DEFAULT_DATE is null) AND (X_DEFAULT_DATE is null)))
131 AND ((tlinfo.DEFAULT_NUMBER = X_DEFAULT_NUMBER)
132 OR ((tlinfo.DEFAULT_NUMBER is null) AND (X_DEFAULT_NUMBER is null)))
133 AND ((tlinfo.DEFAULT_BOOLEAN_FLAG = X_DEFAULT_BOOLEAN_FLAG)
134 OR ((tlinfo.DEFAULT_BOOLEAN_FLAG is null) AND (X_DEFAULT_BOOLEAN_FLAG is null)))
135 AND ((tlinfo.DEFAULT_FORMULA = X_DEFAULT_FORMULA)
136 OR ((tlinfo.DEFAULT_FORMULA is null) AND (X_DEFAULT_FORMULA is null)))
137 AND ((tlinfo.DESC_VALUE = X_DESC_VALUE)
138 OR ((tlinfo.DESC_VALUE is null) AND (X_DESC_VALUE is null)))
139 ) then
140 null;
141 else
142 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
143 app_exception.raise_exception;
144 end if;
145 end loop;
146 return;
147 end LOCK_ROW;
148
149 procedure UPDATE_ROW (
150 X_APPLICATION_ID in NUMBER,
151 X_PARAM_DEFN_CODE in VARCHAR2,
152 X_LVL in NUMBER,
153 X_OVERRIDE_ID in NUMBER,
154 X_OBJECT_VERSION_NUMBER in NUMBER,
155 X_REQUIRED_FLAG in VARCHAR2,
156 X_VISIBLE_FLAG in VARCHAR2,
157 X_USER_MODIFYABLE_FLAG in VARCHAR2,
158 X_DEFAULT_DATE in DATE,
159 X_DEFAULT_NUMBER in NUMBER,
160 X_DEFAULT_BOOLEAN_FLAG in VARCHAR2,
161 X_DEFAULT_FORMULA in VARCHAR2,
162 X_DESC_VALUE in VARCHAR2,
163 X_DEFAULT_STRING in VARCHAR2,
164 X_LAST_UPDATE_DATE in DATE,
165 X_LAST_UPDATED_BY in NUMBER,
166 X_LAST_UPDATE_LOGIN in NUMBER
167 ) is
168 begin
169 update BNE_PARAM_OVERRIDES set
170 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
171 REQUIRED_FLAG = X_REQUIRED_FLAG,
172 VISIBLE_FLAG = X_VISIBLE_FLAG,
173 USER_MODIFYABLE_FLAG = X_USER_MODIFYABLE_FLAG,
174 DEFAULT_DATE = X_DEFAULT_DATE,
175 DEFAULT_NUMBER = X_DEFAULT_NUMBER,
176 DEFAULT_BOOLEAN_FLAG = X_DEFAULT_BOOLEAN_FLAG,
177 DEFAULT_FORMULA = X_DEFAULT_FORMULA,
178 DESC_VALUE = X_DESC_VALUE,
179 DEFAULT_STRING = X_DEFAULT_STRING,
180 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
181 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
183 where APPLICATION_ID = X_APPLICATION_ID
184 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
185 and LVL = X_LVL
186 and OVERRIDE_ID = X_OVERRIDE_ID;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191 end UPDATE_ROW;
192
193 procedure DELETE_ROW (
194 X_APPLICATION_ID in NUMBER,
195 X_PARAM_DEFN_CODE in VARCHAR2,
196 X_LVL in NUMBER,
197 X_OVERRIDE_ID in NUMBER
198 ) is
199 begin
200 delete from BNE_PARAM_OVERRIDES
201 where APPLICATION_ID = X_APPLICATION_ID
202 and PARAM_DEFN_CODE = X_PARAM_DEFN_CODE
203 and LVL = X_LVL
204 and OVERRIDE_ID = X_OVERRIDE_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 end DELETE_ROW;
211
212 procedure ADD_LANGUAGE
213 is
214 begin
215 null;
216 end ADD_LANGUAGE;
217
218 --------------------------------------------------------------------------------
219 -- PROCEDURE: LOAD_ROW --
220 -- --
221 -- DESCRIPTION: Load a row into the BNE_PARAM_OBERRIDES entity. --
222 -- This proc is called from the apps loader. --
223 -- --
224 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
225 -- --
226 -- --
227 -- MODIFICATION HISTORY --
228 -- Date Username Description --
229 -- 1-Oct-02 DGROVES CREATED --
230 --------------------------------------------------------------------------------
231 procedure LOAD_ROW(
232 x_param_defn_asn in VARCHAR2,
233 x_param_defn_code in VARCHAR2,
234 x_lvl in VARCHAR2,
235 x_override_id in VARCHAR2,
236 x_object_version_number in VARCHAR2,
237 x_required_flag in VARCHAR2,
238 x_visible_flag in VARCHAR2,
239 x_user_modifyable_flag in VARCHAR2,
240 x_default_string in VARCHAR2,
241 x_default_date in VARCHAR2,
242 x_default_number in VARCHAR2,
243 x_default_boolean_flag in VARCHAR2,
244 x_default_formula in VARCHAR2,
245 x_desc_value in VARCHAR2,
246 x_owner in VARCHAR2,
247 x_last_update_date in VARCHAR2,
248 x_custom_mode in VARCHAR2
249 )
250 is
251 l_app_id number;
252 l_row_id varchar2(64);
253 l_default_number number;
254 l_default_date date;
255 f_luby number; -- entity owner in file
256 f_ludate date; -- entity update date in file
257 db_luby number; -- entity owner in db
258 db_ludate date; -- entity update date in db
259 begin
260 -- translate values to IDs
261 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_param_defn_asn);
262
263 -- Translate owner to file_last_updated_by
264 f_luby := fnd_load_util.owner_id(x_owner);
265
266 l_default_number := null;
267 l_default_date := null;
268 if x_default_number is not null
269 then
270 l_default_number := to_number(x_default_number);
271 end if;
272 if x_default_date is not null
273 then
274 l_default_date := to_date(x_default_date, 'YYYY/MM/DD-HH24:MI:SS');
275 end if;
276
277 -- Translate char last_update_date to date
278 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
279 begin
280 select LAST_UPDATED_BY, LAST_UPDATE_DATE
281 into db_luby, db_ludate
282 from BNE_PARAM_OVERRIDES
283 where APPLICATION_ID = l_app_id
284 and PARAM_DEFN_CODE = x_param_defn_code
285 and LVL = x_lvl
286 and OVERRIDE_ID = x_override_id;
287
288 -- Test for customization and version
289 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
290 db_ludate, x_custom_mode)) then
291 -- Update existing row
292 BNE_PARAM_OVERRIDES_PKG.Update_Row(
293 X_APPLICATION_ID => l_app_id,
294 X_PARAM_DEFN_CODE => x_param_defn_code,
295 X_LVL => x_lvl,
296 X_OVERRIDE_ID => x_override_id,
297 X_OBJECT_VERSION_NUMBER => x_object_version_number,
298 X_REQUIRED_FLAG => x_required_flag,
299 X_VISIBLE_FLAG => x_visible_flag,
300 X_USER_MODIFYABLE_FLAG => x_user_modifyable_flag,
301 X_DEFAULT_DATE => l_default_date,
302 X_DEFAULT_NUMBER => l_default_number,
303 X_DEFAULT_BOOLEAN_FLAG => x_default_boolean_flag,
304 X_DEFAULT_FORMULA => x_default_formula,
305 X_DESC_VALUE => x_desc_value,
306 X_DEFAULT_STRING => x_default_string,
307 X_LAST_UPDATE_DATE => f_ludate,
308 X_LAST_UPDATED_BY => f_luby,
309 X_LAST_UPDATE_LOGIN => 0
310 );
311 end if;
312 exception
313 when no_data_found then
314 -- Record doesn't exist - insert in all cases
315 BNE_PARAM_OVERRIDES_PKG.Insert_Row(
316 X_ROWID => l_row_id,
317 X_APPLICATION_ID => l_app_id,
318 X_PARAM_DEFN_CODE => x_param_defn_code,
319 X_LVL => x_lvl,
320 X_OVERRIDE_ID => x_override_id,
321 X_OBJECT_VERSION_NUMBER => x_object_version_number,
322 X_REQUIRED_FLAG => x_required_flag,
323 X_VISIBLE_FLAG => x_visible_flag,
324 X_USER_MODIFYABLE_FLAG => x_user_modifyable_flag,
325 X_DEFAULT_DATE => l_default_date,
326 X_DEFAULT_NUMBER => l_default_number,
327 X_DEFAULT_BOOLEAN_FLAG => x_default_boolean_flag,
328 X_DEFAULT_FORMULA => x_default_formula,
332 X_CREATED_BY => f_luby,
329 X_DESC_VALUE => x_desc_value,
330 X_DEFAULT_STRING => x_default_string,
331 X_CREATION_DATE => f_ludate,
333 X_LAST_UPDATE_DATE => f_ludate,
334 X_LAST_UPDATED_BY => f_luby,
335 X_LAST_UPDATE_LOGIN => 0
336 );
337 end;
338 end LOAD_ROW;
339
340
341
342 end BNE_PARAM_OVERRIDES_PKG;