[Home] [Help]
PACKAGE BODY: APPS.OZF_DENORM_QUERIES_PKG
Source
1 package body OZF_DENORM_QUERIES_PKG as
2 /* $Header: ozflofdb.pls 120.0 2005/06/01 01:09:29 appldev noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_DENORM_QUERY_ID IN NUMBER,
7 X_QUERY_FOR IN VARCHAR2,
8 X_CONTEXT IN VARCHAR2,
9 X_ATTRIBUTE IN VARCHAR2,
10 X_CONDITION_ID_COLUMN IN VARCHAR2,
11 X_CONDITION_NAME_COLUMN IN VARCHAR2,
12 X_ACTIVE_FLAG IN VARCHAR2,
13 X_CREATION_DATE IN DATE,
14 X_CREATED_BY IN NUMBER,
15 X_LAST_UPDATE_DATE IN DATE,
16 X_LAST_UPDATED_BY IN NUMBER,
17 X_LAST_UPDATE_LOGIN IN NUMBER,
18 X_SEEDED_FLAG IN VARCHAR2,
19 X_SQL_VALIDATION_1 IN VARCHAR2,
20 X_SQL_VALIDATION_2 IN VARCHAR2,
21 X_SQL_VALIDATION_3 IN VARCHAR2,
22 X_SQL_VALIDATION_4 IN VARCHAR2,
23 X_SQL_VALIDATION_5 IN VARCHAR2,
24 X_SQL_VALIDATION_6 IN VARCHAR2,
25 X_SQL_VALIDATION_7 IN VARCHAR2,
26 X_SQL_VALIDATION_8 IN VARCHAR2,
27 X_OBJECT_VERSION_NUMBER IN NUMBER,
28 X_SECURITY_GROUP_ID IN NUMBER)
29 is
30 cursor C is select ROWID from OZF_DENORM_QUERIES
31 where DENORM_QUERY_ID = X_DENORM_QUERY_ID;
32 begin
33 insert into OZF_DENORM_QUERIES(
34 DENORM_QUERY_ID,
35 QUERY_FOR,
36 CONTEXT,
37 ATTRIBUTE,
38 CONDITION_ID_COLUMN,
39 CONDITION_NAME_COLUMN,
40 ACTIVE_FLAG,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN,
46 SEEDED_FLAG,
47 SQL_VALIDATION_1,
48 SQL_VALIDATION_2,
49 SQL_VALIDATION_3,
50 SQL_VALIDATION_4,
51 SQL_VALIDATION_5,
52 SQL_VALIDATION_6,
53 SQL_VALIDATION_7,
54 SQL_VALIDATION_8,
55 OBJECT_VERSION_NUMBER,
56 SECURITY_GROUP_ID
57 )
58 values (
59 X_DENORM_QUERY_ID,
60 X_QUERY_FOR,
61 X_CONTEXT,
62 X_ATTRIBUTE,
63 X_CONDITION_ID_COLUMN,
64 X_CONDITION_NAME_COLUMN,
65 X_ACTIVE_FLAG,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_SEEDED_FLAG,
72 X_SQL_VALIDATION_1,
73 X_SQL_VALIDATION_2,
74 X_SQL_VALIDATION_3,
75 X_SQL_VALIDATION_4,
76 X_SQL_VALIDATION_5,
77 X_SQL_VALIDATION_6,
78 X_SQL_VALIDATION_7,
79 X_SQL_VALIDATION_8,
80 X_OBJECT_VERSION_NUMBER,
81 X_SECURITY_GROUP_ID
82
83 );
84
85
86 open c;
87 fetch c into X_ROWID;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 end INSERT_ROW;
95
96 procedure LOCK_ROW (
97 X_DENORM_QUERY_ID in NUMBER,
98 X_QUERY_FOR IN VARCHAR2,
99 X_CONTEXT IN VARCHAR2,
100 X_ATTRIBUTE IN VARCHAR2,
101 X_CONDITION_ID_COLUMN IN VARCHAR2,
102 X_CONDITION_NAME_COLUMN IN VARCHAR2,
103 X_ACTIVE_FLAG IN VARCHAR2,
104 X_SEEDED_FLAG IN VARCHAR2,
105 X_SQL_VALIDATION_1 IN VARCHAR2,
106 X_SQL_VALIDATION_2 IN VARCHAR2,
107 X_SQL_VALIDATION_3 IN VARCHAR2,
108 X_SQL_VALIDATION_4 IN VARCHAR2,
109 X_SQL_VALIDATION_5 IN VARCHAR2,
110 X_SQL_VALIDATION_6 IN VARCHAR2,
111 X_SQL_VALIDATION_7 IN VARCHAR2,
112 X_SQL_VALIDATION_8 IN VARCHAR2,
113 X_OBJECT_VERSION_NUMBER IN NUMBER,
114 X_SECURITY_GROUP_ID IN NUMBER)is
115 begin
116 null;
117 end lock_row;
118 procedure UPDATE_ROW (
119 X_DENORM_QUERY_ID IN NUMBER,
120 X_QUERY_FOR IN VARCHAR2,
121 X_CONTEXT IN VARCHAR2,
122 X_ATTRIBUTE IN VARCHAR2,
123 X_CONDITION_ID_COLUMN IN VARCHAR2,
124 X_CONDITION_NAME_COLUMN IN VARCHAR2,
125 X_ACTIVE_FLAG IN VARCHAR2,
126 X_LAST_UPDATE_DATE IN DATE,
127 X_LAST_UPDATED_BY IN NUMBER,
128 X_LAST_UPDATE_LOGIN IN NUMBER,
129 X_SEEDED_FLAG IN VARCHAR2,
130 X_SQL_VALIDATION_1 IN VARCHAR2,
131 X_SQL_VALIDATION_2 IN VARCHAR2,
132 X_SQL_VALIDATION_3 IN VARCHAR2,
133 X_SQL_VALIDATION_4 IN VARCHAR2,
134 X_SQL_VALIDATION_5 IN VARCHAR2,
135 X_SQL_VALIDATION_6 IN VARCHAR2,
136 X_SQL_VALIDATION_7 IN VARCHAR2,
137 X_SQL_VALIDATION_8 IN VARCHAR2,
138 X_OBJECT_VERSION_NUMBER IN NUMBER,
139 X_SECURITY_GROUP_ID IN NUMBER
140 ) is
141 begin
142 update OZF_DENORM_QUERIES set
143 QUERY_FOR = X_QUERY_FOR ,
144 CONTEXT = X_CONTEXT ,
145 ATTRIBUTE = X_ATTRIBUTE,
146 CONDITION_ID_COLUMN = X_CONDITION_ID_COLUMN,
147 CONDITION_NAME_COLUMN = X_CONDITION_NAME_COLUMN,
148 ACTIVE_FLAG = X_ACTIVE_FLAG,
149 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
152 SEEDED_FLAG = X_SEEDED_FLAG,
153 SQL_VALIDATION_1= X_SQL_VALIDATION_1,
154 SQL_VALIDATION_2= X_SQL_VALIDATION_2,
155 SQL_VALIDATION_3= X_SQL_VALIDATION_3,
156 SQL_VALIDATION_4= X_SQL_VALIDATION_4,
157 SQL_VALIDATION_5= X_SQL_VALIDATION_5,
158 SQL_VALIDATION_6= X_SQL_VALIDATION_6,
159 SQL_VALIDATION_7= X_SQL_VALIDATION_7,
160 SQL_VALIDATION_8= X_SQL_VALIDATION_8,
161 OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER,
162 SECURITY_GROUP_ID=X_SECURITY_GROUP_ID
163 where DENORM_QUERY_ID = X_DENORM_QUERY_ID;
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168
169
170
171 end UPDATE_ROW;
172
173 procedure DELETE_ROW (
174 X_DENORM_QUERY_ID in NUMBER,
175 X_OBJECT_VERSION_NUMBER IN NUMBER
176 ) is
177 begin
178 delete from OZF_DENORM_QUERIES
179 where DENORM_QUERY_ID = X_DENORM_QUERY_ID
180 and object_version_number=X_OBJECT_VERSION_NUMBER;
181 if (sql%notfound) then
182 raise no_data_found;
183 end if;
184
185
186 end DELETE_ROW;
187
188
189
190 procedure LOAD_ROW (
191 X_DENORM_QUERY_ID IN NUMBER,
192 X_QUERY_FOR IN VARCHAR2,
193 X_CONTEXT IN VARCHAR2,
194 X_ATTRIBUTE IN VARCHAR2,
195 X_CONDITION_ID_COLUMN IN VARCHAR2,
196 X_CONDITION_NAME_COLUMN IN VARCHAR2,
197 X_ACTIVE_FLAG IN VARCHAR2,
198 X_SEEDED_FLAG IN VARCHAR2,
199 X_SQL_VALIDATION_1 IN VARCHAR2,
200 X_SQL_VALIDATION_2 IN VARCHAR2,
201 X_SQL_VALIDATION_3 IN VARCHAR2,
202 X_SQL_VALIDATION_4 IN VARCHAR2,
203 X_SQL_VALIDATION_5 IN VARCHAR2,
204 X_SQL_VALIDATION_6 IN VARCHAR2,
205 X_SQL_VALIDATION_7 IN VARCHAR2,
206 X_SQL_VALIDATION_8 IN VARCHAR2,
207 X_OBJECT_VERSION_NUMBER IN NUMBER,
208 X_SECURITY_GROUP_ID IN NUMBER,
209 X_CUSTOM_MODE IN VARCHAR2,
210 X_OWNER_NAME IN VARCHAR2
211 ) IS
212
213
214
215 l_user_id number;
216 l_dummy_char varchar2(1);
217 l_row_id varchar2(100);
218 l_DENORM_QUERY_ID number;
219 l_db_luby_id NUMBER;
220
221 CURSOR c_db_luby_id IS
222 SELECT last_updated_by
223 FROM ozf_denorm_queries
224 WHERE denorm_query_id = X_DENORM_QUERY_ID;
225
226
227
228 cursor c_chk_denorm_exists is
229 select 'x'
230 from OZF_DENORM_QUERIES
231 where context= X_CONTEXT
232 AND attribute=X_ATTRIBUTE;
233
234 cursor c_get_denorm_id is
235 select OZF_DENORM_QUERIES_S.nextval
236 from dual;
237
238 BEGIN
239
240 l_user_id := FND_LOAD_UTIL.OWNER_ID(X_OWNER_NAME);
241
242 open c_chk_denorm_exists;
243 fetch c_chk_denorm_exists into l_dummy_char;
244 IF c_chk_denorm_exists%notfound
245 THEN
246 close c_chk_denorm_exists;
247 IF X_DENORM_QUERY_ID is null
248 THEN
249 open c_get_denorm_id;
250 fetch c_get_denorm_id into l_DENORM_QUERY_id;
251 close c_get_denorm_id;
252 ELSE
253 l_DENORM_QUERY_id := X_DENORM_QUERY_ID;
254 END IF;
255 --insert into jl_temp values(sysdate,l_user_id,FND_GLOBAL.CONC_LOGIN_ID,'I',l_DENORM_QUERY_id);
256 OZF_DENORM_QUERIES_PKG.INSERT_ROW(
257 X_ROWID => l_row_id,
258 X_DENORM_QUERY_ID => l_DENORM_QUERY_id,
259 X_QUERY_FOR => X_QUERY_FOR,
260 X_CONTEXT => X_CONTEXT,
261 X_ATTRIBUTE => X_ATTRIBUTE,
262 X_CONDITION_ID_COLUMN => X_CONDITION_ID_COLUMN ,
263 X_CONDITION_NAME_COLUMN => X_CONDITION_NAME_COLUMN,
264 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
265 X_CREATION_DATE => SYSDATE,
266 X_CREATED_BY => l_user_id,
267 X_LAST_UPDATE_DATE => SYSDATE,
268 X_LAST_UPDATED_BY => l_user_id,
269 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
270 X_SEEDED_FLAG => X_SEEDED_FLAG,
271 X_SQL_VALIDATION_1 => X_SQL_VALIDATION_1,
272 X_SQL_VALIDATION_2 => X_SQL_VALIDATION_2,
273 X_SQL_VALIDATION_3 => X_SQL_VALIDATION_3,
274 X_SQL_VALIDATION_4 => X_SQL_VALIDATION_4,
275 X_SQL_VALIDATION_5 => X_SQL_VALIDATION_5,
276 X_SQL_VALIDATION_6 => X_SQL_VALIDATION_6,
277 X_SQL_VALIDATION_7 => X_SQL_VALIDATION_7,
278 X_SQL_VALIDATION_8 => X_SQL_VALIDATION_8 ,
279 X_OBJECT_VERSION_NUMBER =>X_OBJECT_VERSION_NUMBER,
280 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID
281 );
282
283
284 ELSE
285 close c_chk_denorm_exists;
286
287 -- assigning value for l_DENORM_QUERIES_id
288 l_DENORM_QUERY_id := X_DENORM_QUERY_ID;
289
290 --OPEN c_db_luby_id;
291 --FETCH c_db_luby_id INTO l_db_luby_id;
292 --CLOSE c_db_luby_id;
293 --insert into jl_temp values(sysdate,l_user_id,FND_GLOBAL.CONC_LOGIN_ID,'U',l_DENORM_QUERY_id);
294 --IF (l_db_luby_id IN (1,2,0) OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
295 IF x_owner_name like 'ORACLE%' OR NVL(x_custom_mode,'PRESERVE') = 'FORCE' THEN
296 OZF_DENORM_QUERIES_PKG.UPDATE_ROW(
297 X_DENORM_QUERY_ID => l_denorm_query_id,
298 X_QUERY_FOR => X_QUERY_FOR,
299 X_CONTEXT => X_CONTEXT,
300 X_ATTRIBUTE => X_ATTRIBUTE,
301 X_CONDITION_ID_COLUMN => X_CONDITION_ID_COLUMN,
302 X_CONDITION_NAME_COLUMN => X_CONDITION_NAME_COLUMN,
303 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
304 X_LAST_UPDATE_DATE => SYSDATE,
305 X_LAST_UPDATED_BY => l_user_id,
306 X_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
307 X_SEEDED_FLAG => X_SEEDED_FLAG,
308 X_SQL_VALIDATION_1 => X_SQL_VALIDATION_1,
309 X_SQL_VALIDATION_2 => X_SQL_VALIDATION_2,
310 X_SQL_VALIDATION_3 => X_SQL_VALIDATION_3,
311 X_SQL_VALIDATION_4 => X_SQL_VALIDATION_4,
312 X_SQL_VALIDATION_5 => X_SQL_VALIDATION_5,
313 X_SQL_VALIDATION_6 => X_SQL_VALIDATION_6,
314 X_SQL_VALIDATION_7 => X_SQL_VALIDATION_7,
315 X_SQL_VALIDATION_8 => X_SQL_VALIDATION_8,
316 X_OBJECT_VERSION_NUMBER =>X_OBJECT_VERSION_NUMBER,
317 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID
318 );
319 END IF;
320 END IF;
321 END LOAD_ROW;
322
323
324 end OZF_DENORM_QUERIES_PKG;