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