[Home] [Help]
PACKAGE BODY: APPS.AMS_COLUMN_RULES_PKG
Source
1 PACKAGE BODY AMS_COLUMN_RULES_PKG as
2 /* $Header: amslclrb.pls 120.1 2005/06/27 05:38:12 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_COLUMN_RULE_ID in NUMBER,
6 X_COLUMNS_METADATA_ID in NUMBER,
7 X_OBJECT_TYPE in VARCHAR2,
8 X_OBJECT_ATTRIBUTE in VARCHAR2,
9 X_AK_REGION_CODE in VARCHAR2,
10 X_AK_ATTRIBUTE_CODE in VARCHAR2,
11 X_ACTIVITY_TYPE_CODE in VARCHAR2,
12 X_HTML_FORM_PARAM in VARCHAR2,
13 X_DB_TABLE_NAME in VARCHAR2,
14 X_DB_COLUMN_NAME in VARCHAR2,
15 X_SYSTEM_STATUS_CODE in VARCHAR2,
16 X_RULE_TYPE in VARCHAR2,
17 X_SEEDED_FLAG in VARCHAR2,
18 X_SECURITY_GROUP_ID in NUMBER,
19 X_OBJECT_VERSION_NUMBER in NUMBER,
20 X_APPLICATION_ID in NUMBER,
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 begin
28 insert into AMS_COLUMN_RULES (
29 COLUMN_RULE_ID,
30 COLUMNS_METADATA_ID,
31 OBJECT_TYPE,
32 OBJECT_ATTRIBUTE,
33 AK_REGION_CODE,
34 AK_ATTRIBUTE_CODE,
35 ACTIVITY_TYPE_CODE,
36 HTML_FORM_PARAM,
37 DB_TABLE_NAME,
38 DB_COLUMN_NAME,
39 SYSTEM_STATUS_CODE,
40 RULE_TYPE,
41 SEEDED_FLAG,
42 --SECURITY_GROUP_ID,
43 OBJECT_VERSION_NUMBER,
44 APPLICATION_ID ,
45 LAST_UPDATED_BY,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_LOGIN,
49 LAST_UPDATE_DATE
50 ) values (
51 X_COLUMN_RULE_ID,
52 X_COLUMNS_METADATA_ID,
53 X_OBJECT_TYPE,
54 X_OBJECT_ATTRIBUTE,
55 X_AK_REGION_CODE,
56 X_AK_ATTRIBUTE_CODE,
57 X_ACTIVITY_TYPE_CODE,
58 X_HTML_FORM_PARAM,
59 X_DB_TABLE_NAME,
60 X_DB_COLUMN_NAME,
61 X_SYSTEM_STATUS_CODE,
62 X_RULE_TYPE,
63 X_SEEDED_FLAG,
64 --X_SECURITY_GROUP_ID,
65 X_OBJECT_VERSION_NUMBER,
66 X_APPLICATION_ID,
67 X_LAST_UPDATED_BY,
68 X_CREATION_DATE,
69 X_CREATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_LAST_UPDATE_DATE
72 );
73
74 end INSERT_ROW;
75
76 procedure LOCK_ROW (
77 X_COLUMN_RULE_ID in NUMBER,
78 X_COLUMNS_METADATA_ID in NUMBER,
79 X_OBJECT_TYPE in VARCHAR2,
80 X_OBJECT_ATTRIBUTE in VARCHAR2,
81 X_AK_REGION_CODE in VARCHAR2,
82 X_AK_ATTRIBUTE_CODE in VARCHAR2,
83 X_ACTIVITY_TYPE_CODE in VARCHAR2,
84 X_HTML_FORM_PARAM in VARCHAR2,
85 X_DB_TABLE_NAME in VARCHAR2,
86 X_DB_COLUMN_NAME in VARCHAR2,
87 X_SYSTEM_STATUS_CODE in VARCHAR2,
88 X_RULE_TYPE in VARCHAR2,
89 X_SEEDED_FLAG in VARCHAR2,
90 X_SECURITY_GROUP_ID in NUMBER,
91 X_OBJECT_VERSION_NUMBER in NUMBER,
92 X_APPLICATION_ID in NUMBER
93 ) is
94 cursor c1 is select
95 COLUMNS_METADATA_ID,
96 OBJECT_TYPE,
97 OBJECT_ATTRIBUTE,
98 AK_REGION_CODE,
99 AK_ATTRIBUTE_CODE,
100 ACTIVITY_TYPE_CODE,
101 HTML_FORM_PARAM,
102 DB_TABLE_NAME,
103 DB_COLUMN_NAME,
104 SYSTEM_STATUS_CODE,
105 RULE_TYPE,
106 SEEDED_FLAG,
107 SECURITY_GROUP_ID,
108 OBJECT_VERSION_NUMBER,
109 APPLICATION_ID,
110 COLUMN_RULE_ID
111 from AMS_COLUMN_RULES
112 where COLUMN_RULE_ID = X_COLUMN_RULE_ID
113 for update of COLUMN_RULE_ID nowait;
114 begin
115 for tlinfo in c1 loop
116 if ( (tlinfo.COLUMN_RULE_ID = X_COLUMN_RULE_ID)
117 AND ((tlinfo.COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID)
118 OR ((tlinfo.COLUMNS_METADATA_ID is null) AND (X_COLUMNS_METADATA_ID is null)))
119 AND ((tlinfo.OBJECT_TYPE = X_OBJECT_TYPE)
120 OR ((tlinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
121 AND ((tlinfo.OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE)
122 OR ((tlinfo.OBJECT_ATTRIBUTE is null) AND (X_OBJECT_ATTRIBUTE is null)))
123 AND ((tlinfo.AK_REGION_CODE = X_AK_REGION_CODE)
124 OR ((tlinfo.AK_REGION_CODE is null) AND (X_AK_REGION_CODE is null)))
125 AND ((tlinfo.AK_ATTRIBUTE_CODE = X_AK_ATTRIBUTE_CODE)
126 OR ((tlinfo.AK_ATTRIBUTE_CODE is null) AND (X_AK_ATTRIBUTE_CODE is null)))
127 AND ((tlinfo.ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE)
128 OR ((tlinfo.ACTIVITY_TYPE_CODE is null) AND (X_ACTIVITY_TYPE_CODE is null)))
129 AND ((tlinfo.HTML_FORM_PARAM = X_HTML_FORM_PARAM)
130 OR ((tlinfo.HTML_FORM_PARAM is null) AND (X_HTML_FORM_PARAM is null)))
131 AND ((tlinfo.DB_TABLE_NAME = X_DB_TABLE_NAME)
132 OR ((tlinfo.DB_TABLE_NAME is null) AND (X_DB_TABLE_NAME is null)))
133 AND ((tlinfo.DB_COLUMN_NAME = X_DB_COLUMN_NAME)
134 OR ((tlinfo.DB_COLUMN_NAME is null) AND (X_DB_COLUMN_NAME is null)))
135 AND ((tlinfo.SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE)
136 OR ((tlinfo.SYSTEM_STATUS_CODE is null) AND (X_SYSTEM_STATUS_CODE is null)))
137 AND ((tlinfo.RULE_TYPE = X_RULE_TYPE)
138 OR ((tlinfo.RULE_TYPE is null) AND (X_RULE_TYPE is null)))
139 AND ((tlinfo.SEEDED_FLAG = X_SEEDED_FLAG)
140 OR ((tlinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
141 --AND ((tlinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
142 --OR ((tlinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
143 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
144 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
145 AND ((tlinfo.APPLICATION_ID = X_APPLICATION_ID)
146 OR ((tlinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID 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_COLUMN_RULE_ID in NUMBER,
159 X_COLUMNS_METADATA_ID in NUMBER,
160 X_OBJECT_TYPE in VARCHAR2,
161 X_OBJECT_ATTRIBUTE in VARCHAR2,
162 X_AK_REGION_CODE in VARCHAR2,
163 X_AK_ATTRIBUTE_CODE in VARCHAR2,
164 X_ACTIVITY_TYPE_CODE in VARCHAR2,
165 X_HTML_FORM_PARAM in VARCHAR2,
166 X_DB_TABLE_NAME in VARCHAR2,
167 X_DB_COLUMN_NAME in VARCHAR2,
168 X_SYSTEM_STATUS_CODE in VARCHAR2,
169 X_RULE_TYPE in VARCHAR2,
170 X_SEEDED_FLAG in VARCHAR2,
171 X_SECURITY_GROUP_ID in NUMBER,
172 X_OBJECT_VERSION_NUMBER in NUMBER,
173 X_APPLICATION_ID in NUMBER,
174 X_LAST_UPDATE_DATE in DATE,
175 X_LAST_UPDATED_BY in NUMBER,
176 X_LAST_UPDATE_LOGIN in NUMBER
177 ) is
178 begin
179 update AMS_COLUMN_RULES set
180 COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID,
181 OBJECT_TYPE = X_OBJECT_TYPE,
182 OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE,
183 AK_REGION_CODE = X_AK_REGION_CODE,
184 AK_ATTRIBUTE_CODE = X_AK_ATTRIBUTE_CODE,
185 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
186 HTML_FORM_PARAM = X_HTML_FORM_PARAM,
187 DB_TABLE_NAME = X_DB_TABLE_NAME,
188 DB_COLUMN_NAME = X_DB_COLUMN_NAME,
189 SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE,
190 RULE_TYPE = X_RULE_TYPE,
191 SEEDED_FLAG = X_SEEDED_FLAG,
192 --SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
193 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
194 APPLICATION_ID = X_APPLICATION_ID ,
195 COLUMN_RULE_ID = X_COLUMN_RULE_ID,
196 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
197 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
198 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
199 where COLUMN_RULE_ID = X_COLUMN_RULE_ID;
200
201 if (sql%notfound) then
202 raise no_data_found;
203 end if;
204 end UPDATE_ROW;
205
206 procedure DELETE_ROW (
207 X_COLUMN_RULE_ID in NUMBER
208 ) is
209 begin
210 delete from AMS_COLUMN_RULES
211 where COLUMN_RULE_ID = X_COLUMN_RULE_ID;
212
213 if (sql%notfound) then
214 raise no_data_found;
215 end if;
216
217 end DELETE_ROW;
218
219 procedure LOAD_ROW (
220 X_COLUMN_RULE_ID in NUMBER,
221 X_COLUMNS_METADATA_ID in NUMBER,
222 X_OBJECT_TYPE in VARCHAR2,
223 X_OBJECT_ATTRIBUTE in VARCHAR2,
224 X_AK_REGION_CODE in VARCHAR2,
225 X_AK_ATTRIBUTE_CODE in VARCHAR2,
226 X_ACTIVITY_TYPE_CODE in VARCHAR2,
227 X_HTML_FORM_PARAM in VARCHAR2,
228 X_DB_TABLE_NAME in VARCHAR2,
229 X_DB_COLUMN_NAME in VARCHAR2,
230 X_SYSTEM_STATUS_CODE in VARCHAR2,
231 X_RULE_TYPE in VARCHAR2,
232 X_SEEDED_FLAG in VARCHAR2,
233 X_SECURITY_GROUP_ID in NUMBER,
234 X_OBJECT_VERSION_NUMBER in NUMBER,
235 X_APPLICATION_ID in NUMBER,
236 X_OWNER in VARCHAR2
237 )is
238 l_user_id number := 0;
239 l_colrule_id number;
240 l_obj_verno number;
241 l_dummy_char varchar2(1);
242 l_row_id varchar2(100);
243 l_id_exists varchar2(1);
244
245
246 cursor c_obj_verno is
247 select object_version_number, column_rule_id
248 from AMS_COLUMN_RULES
249 where OBJECT_TYPE = X_OBJECT_TYPE
250 and OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
251 and nvl(ACTIVITY_TYPE_CODE,0) = nvl(X_ACTIVITY_TYPE_CODE, 0)
252 and HTML_FORM_PARAM = X_HTML_FORM_PARAM
253 and APPLICATION_ID = X_APPLICATION_ID
254 and nvl(SYSTEM_STATUS_CODE, 0)= nvl(X_SYSTEM_STATUS_CODE, 0)
255 and RULE_TYPE =X_RULE_TYPE
256 and COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
257
258 cursor c_chk_colrule_exists is
259 select 'x'
260 from AMS_COLUMN_RULES
261 where OBJECT_TYPE = X_OBJECT_TYPE
262 and OBJECT_ATTRIBUTE = X_OBJECT_ATTRIBUTE
263 and nvl(ACTIVITY_TYPE_CODE, 0) = nvl(X_ACTIVITY_TYPE_CODE, 0)
264 and HTML_FORM_PARAM = X_HTML_FORM_PARAM
265 and APPLICATION_ID = X_APPLICATION_ID
266 and nvl(SYSTEM_STATUS_CODE, 0)= nvl(X_SYSTEM_STATUS_CODE, 0)
267 and RULE_TYPE =X_RULE_TYPE
268 and COLUMNS_METADATA_ID = X_COLUMNS_METADATA_ID;
269
270 --where COLUMN_RULE_ID = X_COLUMN_RULE_ID;
271
272 cursor c_get_colrule_id is
273 select AMS_COLUMN_RULES_S.nextval
274 from dual;
275
276 cursor c_id_exists(id_in IN NUMBER) is
277 select 'x'
278 from AMS_COLUMN_RULES
279 where COLUMN_RULE_ID = id_in;
280
281 BEGIN
282 if X_OWNER = 'SEED' then
283 l_user_id := 1;
284 end if;
285
286 open c_chk_colrule_exists;
287 fetch c_chk_colrule_exists into l_dummy_char;
288 if c_chk_colrule_exists%notfound
289 then
290 close c_chk_colrule_exists;
291 /* if X_COLUMN_RULE_ID is null
292 then
293 open c_get_colrule_id;
294 fetch c_get_colrule_id into l_colrule_id;
295 close c_get_colrule_id;
296 else
297 l_colrule_id := X_COLUMN_RULE_ID;
298 end if;
299 */
300 l_id_exists := 'n';
301 LOOP
302 open c_get_colrule_id;
303 fetch c_get_colrule_id into l_colrule_id;
304 close c_get_colrule_id;
305
306 open c_id_exists(l_colrule_id);
307 fetch c_id_exists into l_id_exists;
308 close c_id_exists;
309 if l_id_exists <> 'x' then
310 exit;
311 end if;
312 END LOOP;
313
314 AMS_COLUMN_RULES_PKG.INSERT_ROW (
315 X_ROWID => l_row_id,
316 X_COLUMN_RULE_ID => l_colrule_id,
317 X_COLUMNS_METADATA_ID => X_COLUMNS_METADATA_ID,
318 X_OBJECT_TYPE => X_OBJECT_TYPE,
319 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
320 X_AK_REGION_CODE => X_AK_REGION_CODE,
321 X_AK_ATTRIBUTE_CODE => X_AK_ATTRIBUTE_CODE,
322 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
323 X_HTML_FORM_PARAM => X_HTML_FORM_PARAM,
324 X_DB_TABLE_NAME => X_DB_TABLE_NAME,
325 X_DB_COLUMN_NAME => X_DB_COLUMN_NAME,
326 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
327 X_RULE_TYPE => X_RULE_TYPE,
328 X_SEEDED_FLAG => X_SEEDED_FLAG,
329 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
330 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
331 X_APPLICATION_ID => X_APPLICATION_ID,
332 X_CREATION_DATE => sysdate,
333 X_CREATED_BY => l_user_id,
334 X_LAST_UPDATE_DATE => sysdate,
335 X_LAST_UPDATED_BY => l_user_id,
336 X_LAST_UPDATE_LOGIN => 0
337 );
338 else
339 close c_chk_colrule_exists;
340 open c_obj_verno;
341 fetch c_obj_verno into l_obj_verno,l_colrule_id;
342 close c_obj_verno;
343 -- assigning value for l_user_status_id
344 --l_colrule_id := X_COLUMN_RULE_ID := l_colrule_id;
345 AMS_COLUMN_RULES_PKG.UPDATE_ROW(
346 X_COLUMN_RULE_ID => l_colrule_id,
347 X_COLUMNS_METADATA_ID => X_COLUMNS_METADATA_ID,
348 X_OBJECT_TYPE => X_OBJECT_TYPE,
349 X_OBJECT_ATTRIBUTE => X_OBJECT_ATTRIBUTE,
350 X_AK_REGION_CODE => X_AK_REGION_CODE,
351 X_AK_ATTRIBUTE_CODE => X_AK_ATTRIBUTE_CODE,
352 X_ACTIVITY_TYPE_CODE => X_ACTIVITY_TYPE_CODE,
353 X_HTML_FORM_PARAM => X_HTML_FORM_PARAM,
354 X_DB_TABLE_NAME => X_DB_TABLE_NAME,
355 X_DB_COLUMN_NAME => X_DB_COLUMN_NAME,
356 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
357 X_RULE_TYPE => X_RULE_TYPE,
358 X_SEEDED_FLAG => X_SEEDED_FLAG,
359 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
360 X_OBJECT_VERSION_NUMBER => l_obj_verno,
361 X_APPLICATION_ID => X_APPLICATION_ID,
362 X_LAST_UPDATE_DATE => sysdate,
363 X_LAST_UPDATED_BY => l_user_id,
364 X_LAST_UPDATE_LOGIN => 0
365 );
366 END IF;
367 end LOAD_ROW;
368
369 end AMS_COLUMN_RULES_PKG;