[Home] [Help]
PACKAGE BODY: APPS.BNE_SIMPLE_QUERY_PKG
Source
1 package body BNE_SIMPLE_QUERY_PKG as
2 /* $Header: bnesimplequeryb.pls 120.2 2005/06/29 03:41:01 dvayro noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_QUERY_CODE in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_ID_COL in VARCHAR2,
10 X_ID_COL_ALIAS in VARCHAR2,
11 X_MEANING_COL in VARCHAR2,
12 X_MEANING_COL_ALIAS in VARCHAR2,
13 X_DESCRIPTION_COL in VARCHAR2,
14 X_DESCRIPTION_COL_ALIAS in VARCHAR2,
15 X_ADDITIONAL_COLS in VARCHAR2,
16 X_OBJECT_NAME in VARCHAR2,
17 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
18 X_ORDER_BY_CLAUSE in VARCHAR2,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25 cursor C is select ROWID from BNE_SIMPLE_QUERY
26 where APPLICATION_ID = X_APPLICATION_ID
27 and QUERY_CODE = X_QUERY_CODE
28 ;
29 begin
30 insert into BNE_SIMPLE_QUERY (
31 APPLICATION_ID,
32 QUERY_CODE,
33 OBJECT_VERSION_NUMBER,
34 ID_COL,
35 ID_COL_ALIAS,
36 MEANING_COL,
37 MEANING_COL_ALIAS,
38 DESCRIPTION_COL,
39 DESCRIPTION_COL_ALIAS,
40 ADDITIONAL_COLS,
41 OBJECT_NAME,
42 ADDITIONAL_WHERE_CLAUSE,
43 ORDER_BY_CLAUSE,
44 CREATED_BY,
45 CREATION_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 LAST_UPDATE_DATE
49 ) values (
50 X_APPLICATION_ID,
51 X_QUERY_CODE,
52 X_OBJECT_VERSION_NUMBER,
53 X_ID_COL,
54 X_ID_COL_ALIAS,
55 X_MEANING_COL,
56 X_MEANING_COL_ALIAS,
57 X_DESCRIPTION_COL,
58 X_DESCRIPTION_COL_ALIAS,
59 X_ADDITIONAL_COLS,
60 X_OBJECT_NAME,
61 X_ADDITIONAL_WHERE_CLAUSE,
62 X_ORDER_BY_CLAUSE,
63 X_CREATED_BY,
64 X_CREATION_DATE,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_LOGIN,
67 X_LAST_UPDATE_DATE
68 );
69
70 open c;
71 fetch c into X_ROWID;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 end INSERT_ROW;
79
80 procedure LOCK_ROW (
81 X_APPLICATION_ID in NUMBER,
82 X_QUERY_CODE in VARCHAR2,
83 X_OBJECT_VERSION_NUMBER in NUMBER,
84 X_ID_COL in VARCHAR2,
85 X_ID_COL_ALIAS in VARCHAR2,
86 X_MEANING_COL in VARCHAR2,
87 X_MEANING_COL_ALIAS in VARCHAR2,
88 X_DESCRIPTION_COL in VARCHAR2,
89 X_DESCRIPTION_COL_ALIAS in VARCHAR2,
90 X_ADDITIONAL_COLS in VARCHAR2,
91 X_OBJECT_NAME in VARCHAR2,
92 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
93 X_ORDER_BY_CLAUSE in VARCHAR2
94 ) is
95 cursor c1 is select
96 OBJECT_VERSION_NUMBER,
97 ID_COL,
98 ID_COL_ALIAS,
99 MEANING_COL,
100 MEANING_COL_ALIAS,
101 DESCRIPTION_COL,
102 DESCRIPTION_COL_ALIAS,
103 ADDITIONAL_COLS,
104 OBJECT_NAME,
105 ADDITIONAL_WHERE_CLAUSE,
106 ORDER_BY_CLAUSE
107 from BNE_SIMPLE_QUERY
108 where APPLICATION_ID = X_APPLICATION_ID
109 and QUERY_CODE = X_QUERY_CODE
110 for update of APPLICATION_ID nowait;
111 begin
112 for tlinfo in c1 loop
113 if ( (tlinfo.ID_COL_ALIAS = X_ID_COL_ALIAS)
114 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
115 AND (tlinfo.ID_COL = X_ID_COL)
116 AND (tlinfo.MEANING_COL = X_MEANING_COL)
117 AND (tlinfo.MEANING_COL_ALIAS = X_MEANING_COL_ALIAS)
118 AND ((tlinfo.DESCRIPTION_COL = X_DESCRIPTION_COL)
119 OR ((tlinfo.DESCRIPTION_COL is null) AND (X_DESCRIPTION_COL is null)))
120 AND ((tlinfo.DESCRIPTION_COL_ALIAS = X_DESCRIPTION_COL_ALIAS)
121 OR ((tlinfo.DESCRIPTION_COL_ALIAS is null) AND (X_DESCRIPTION_COL_ALIAS is null)))
122 AND ((tlinfo.ADDITIONAL_COLS = X_ADDITIONAL_COLS)
123 OR ((tlinfo.ADDITIONAL_COLS is null) AND (X_ADDITIONAL_COLS is null)))
124 AND (tlinfo.OBJECT_NAME = X_OBJECT_NAME)
125 AND ((tlinfo.ADDITIONAL_WHERE_CLAUSE = X_ADDITIONAL_WHERE_CLAUSE)
126 OR ((tlinfo.ADDITIONAL_WHERE_CLAUSE is null) AND (X_ADDITIONAL_WHERE_CLAUSE is null)))
127 AND ((tlinfo.ORDER_BY_CLAUSE = X_ORDER_BY_CLAUSE)
128 OR ((tlinfo.ORDER_BY_CLAUSE is null) AND (X_ORDER_BY_CLAUSE is null)))
129 ) then
130 null;
131 else
132 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
133 app_exception.raise_exception;
134 end if;
135 end loop;
136 return;
137 end LOCK_ROW;
138
139 procedure UPDATE_ROW (
140 X_APPLICATION_ID in NUMBER,
141 X_QUERY_CODE in VARCHAR2,
142 X_OBJECT_VERSION_NUMBER in NUMBER,
143 X_ID_COL in VARCHAR2,
144 X_ID_COL_ALIAS in VARCHAR2,
145 X_MEANING_COL in VARCHAR2,
146 X_MEANING_COL_ALIAS in VARCHAR2,
147 X_DESCRIPTION_COL in VARCHAR2,
148 X_DESCRIPTION_COL_ALIAS in VARCHAR2,
149 X_ADDITIONAL_COLS in VARCHAR2,
150 X_OBJECT_NAME in VARCHAR2,
151 X_ADDITIONAL_WHERE_CLAUSE in VARCHAR2,
152 X_ORDER_BY_CLAUSE in VARCHAR2,
153 X_LAST_UPDATE_DATE in DATE,
154 X_LAST_UPDATED_BY in NUMBER,
155 X_LAST_UPDATE_LOGIN in NUMBER
156 ) is
157 begin
158 update BNE_SIMPLE_QUERY set
159 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
160 ID_COL = X_ID_COL,
161 ID_COL_ALIAS = X_ID_COL_ALIAS,
162 MEANING_COL = X_MEANING_COL,
163 MEANING_COL_ALIAS = X_MEANING_COL_ALIAS,
164 DESCRIPTION_COL = X_DESCRIPTION_COL,
165 DESCRIPTION_COL_ALIAS = X_DESCRIPTION_COL_ALIAS,
166 ADDITIONAL_COLS = X_ADDITIONAL_COLS,
167 OBJECT_NAME = X_OBJECT_NAME,
168 ADDITIONAL_WHERE_CLAUSE = X_ADDITIONAL_WHERE_CLAUSE,
169 ORDER_BY_CLAUSE = X_ORDER_BY_CLAUSE,
170 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
171 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
172 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
173 where APPLICATION_ID = X_APPLICATION_ID
174 and QUERY_CODE = X_QUERY_CODE;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179 end UPDATE_ROW;
180
181 procedure DELETE_ROW (
182 X_APPLICATION_ID in NUMBER,
183 X_QUERY_CODE in VARCHAR2
184 ) is
185 begin
186 delete from BNE_SIMPLE_QUERY
187 where APPLICATION_ID = X_APPLICATION_ID
188 and QUERY_CODE = X_QUERY_CODE;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193
194 end DELETE_ROW;
195
196 procedure ADD_LANGUAGE
197 is
198 begin
199 null;
200 end ADD_LANGUAGE;
201
202 --------------------------------------------------------------------------------
203 -- PROCEDURE: LOAD_ROW --
204 -- --
205 -- DESCRIPTION: Load a row into the BNE_SIMPLE_QUERY entity. --
206 -- This proc is called from the apps loader. --
207 -- --
208 -- SEE: http://www-apps.us.oracle.com/atg/plans/r115/fndloadqr.txt --
209 -- --
210 -- --
211 -- MODIFICATION HISTORY --
212 -- Date Username Description --
213 -- 21-Apr-04 DGROVES CREATED --
214 --------------------------------------------------------------------------------
215 procedure LOAD_ROW (
216 x_query_asn IN VARCHAR2,
217 x_query_code IN VARCHAR2,
218 x_object_version_number IN VARCHAR2,
219 x_id_col IN VARCHAR2,
220 x_id_col_alias IN VARCHAR2,
221 x_meaning_col IN VARCHAR2,
222 x_meaning_col_alias IN VARCHAR2,
223 x_description_col IN VARCHAR2,
224 x_description_col_alias IN VARCHAR2,
225 x_additional_cols IN VARCHAR2,
226 x_object_name IN VARCHAR2,
227 x_additional_where_clause IN VARCHAR2,
228 x_order_by_clause IN VARCHAR2,
229 x_owner IN VARCHAR2,
230 x_last_update_date IN VARCHAR2,
231 x_custom_mode IN VARCHAR2
232 )
233 is
234 l_app_id number;
235 l_row_id varchar2(64);
236 f_luby number; -- entity owner in file
237 f_ludate date; -- entity update date in file
238 db_luby number; -- entity owner in db
239 db_ludate date; -- entity update date in db
240 begin
241 -- translate values to IDs
242 l_app_id := BNE_LCT_TOOLS_PKG.ASN_TO_APP_ID(x_query_asn);
243
244 -- Translate owner to file_last_updated_by
245 f_luby := fnd_load_util.owner_id(x_owner);
246
247 -- Translate char last_update_date to date
248 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
249 begin
250 select LAST_UPDATED_BY, LAST_UPDATE_DATE
251 into db_luby, db_ludate
252 from BNE_SIMPLE_QUERY
253 where APPLICATION_ID = l_app_id
254 and QUERY_CODE = x_query_code;
255
256 -- Test for customization and version
257 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
258 db_ludate, x_custom_mode)) then
259 -- Update existing row
260 BNE_SIMPLE_QUERY_PKG.Update_Row(
261 X_APPLICATION_ID => l_app_id,
262 X_QUERY_CODE => x_query_code,
263 X_OBJECT_VERSION_NUMBER => x_object_version_number,
264 X_ID_COL => x_id_col,
265 X_ID_COL_ALIAS => x_id_col_alias,
266 X_MEANING_COL => x_meaning_col,
267 X_MEANING_COL_ALIAS => x_meaning_col_alias,
268 X_DESCRIPTION_COL => x_description_col,
269 X_DESCRIPTION_COL_ALIAS => x_description_col_alias,
270 X_ADDITIONAL_COLS => x_additional_cols,
271 X_OBJECT_NAME => x_object_name,
272 X_ADDITIONAL_WHERE_CLAUSE => x_additional_where_clause,
273 X_ORDER_BY_CLAUSE => x_order_by_clause,
274 X_LAST_UPDATE_DATE => f_ludate,
275 X_LAST_UPDATED_BY => f_luby,
276 X_LAST_UPDATE_LOGIN => 0
277 );
278
279 end if;
280 exception
281 when no_data_found then
282 -- Record doesn't exist - insert in all cases
283 BNE_SIMPLE_QUERY_PKG.Insert_Row(
284 X_ROWID => l_row_id,
285 X_APPLICATION_ID => l_app_id,
286 X_QUERY_CODE => x_query_code,
287 X_OBJECT_VERSION_NUMBER => x_object_version_number,
288 X_ID_COL => x_id_col,
289 X_ID_COL_ALIAS => x_id_col_alias,
290 X_MEANING_COL => x_meaning_col,
291 X_MEANING_COL_ALIAS => x_meaning_col_alias,
292 X_DESCRIPTION_COL => x_description_col,
293 X_DESCRIPTION_COL_ALIAS => x_description_col_alias,
294 X_ADDITIONAL_COLS => x_additional_cols,
295 X_OBJECT_NAME => x_object_name,
296 X_ADDITIONAL_WHERE_CLAUSE => x_additional_where_clause,
297 X_ORDER_BY_CLAUSE => x_order_by_clause,
298 X_CREATION_DATE => f_ludate,
299 X_CREATED_BY => f_luby,
300 X_LAST_UPDATE_DATE => f_ludate,
301 X_LAST_UPDATED_BY => f_luby,
302 X_LAST_UPDATE_LOGIN => 0
303 );
304 end;
305 end LOAD_ROW;
306
307
308 end BNE_SIMPLE_QUERY_PKG;