[Home] [Help]
PACKAGE BODY: APPS.AMW_OBJECT_RULES_PKG
Source
1 package body AMW_OBJECT_RULES_PKG as
2 /* $Header: amwlobrb.pls 120.0 2005/05/31 20:57:32 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_OBJECT_RULE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SECURITY_GROUP_ID in NUMBER,
8 X_APPROVAL_TYPE in VARCHAR2,
9 X_RULE_USED_BY in VARCHAR2,
10 X_OBJECT_TYPE in VARCHAR2,
11 X_RULE_TYPE in VARCHAR2,
12 X_API_TYPE in VARCHAR2,
13 X_PACKAGE_NAME in VARCHAR2,
14 X_PROCEDURE_NAME in VARCHAR2,
15 X_SEEDED_FLAG in VARCHAR2,
16 X_APPLICATION_ID in NUMBER,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23
24 cursor C is select ROWID from AMW_OBJECT_RULES
25 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
26 ;
27 begin
28 insert into AMW_OBJECT_RULES (
29 OBJECT_RULE_ID,
30 OBJECT_VERSION_NUMBER,
31 APPROVAL_TYPE,
32 RULE_USED_BY,
33 OBJECT_TYPE,
34 RULE_TYPE,
35 API_TYPE,
36 PACKAGE_NAME,
37 PROCEDURE_NAME,
38 SEEDED_FLAG,
39 APPLICATION_ID,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_LOGIN
45 ) values (
46 X_OBJECT_RULE_ID,
47 X_OBJECT_VERSION_NUMBER,
48 X_APPROVAL_TYPE,
49 X_RULE_USED_BY,
50 X_OBJECT_TYPE,
51 X_RULE_TYPE,
52 X_API_TYPE,
53 X_PACKAGE_NAME,
54 X_PROCEDURE_NAME,
55 X_SEEDED_FLAG,
56 X_APPLICATION_ID,
57 X_CREATION_DATE,
58 X_CREATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN
62 );
63
64 open c;
65 fetch c into X_ROWID;
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70 close c;
71
72 end INSERT_ROW;
73
74 procedure LOCK_ROW (
75 X_OBJECT_RULE_ID in NUMBER,
76 X_OBJECT_VERSION_NUMBER in NUMBER,
77 X_SECURITY_GROUP_ID in NUMBER,
78 X_APPROVAL_TYPE in VARCHAR2,
79 X_RULE_USED_BY in VARCHAR2,
80 X_OBJECT_TYPE in VARCHAR2,
81 X_RULE_TYPE in VARCHAR2,
82 X_API_TYPE in VARCHAR2,
83 X_PACKAGE_NAME in VARCHAR2,
84 X_PROCEDURE_NAME in VARCHAR2,
85 X_SEEDED_FLAG in VARCHAR2,
86 X_APPLICATION_ID in NUMBER
87 ) is
88 cursor c is select
89 OBJECT_VERSION_NUMBER,
90 SECURITY_GROUP_ID,
91 APPROVAL_TYPE,
92 RULE_USED_BY,
93 OBJECT_TYPE,
94 RULE_TYPE,
95 API_TYPE,
96 PACKAGE_NAME,
97 PROCEDURE_NAME,
98 SEEDED_FLAG,
99 APPLICATION_ID
100 from AMW_OBJECT_RULES
101 where OBJECT_RULE_ID = X_OBJECT_RULE_ID
102 for update of OBJECT_RULE_ID nowait;
103 recinfo c%rowtype;
104
105 begin
106 open c;
107 fetch c into recinfo;
108 if (c%notfound) then
109 close c;
110 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
111 app_exception.raise_exception;
112 end if;
113 close c;
114 if(((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
115 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
116 AND ((recinfo.APPROVAL_TYPE = X_APPROVAL_TYPE)
117 OR ((recinfo.APPROVAL_TYPE is null) AND (X_APPROVAL_TYPE is null)))
118 AND ((recinfo.RULE_USED_BY = X_RULE_USED_BY)
119 OR ((recinfo.RULE_USED_BY is null) AND (X_RULE_USED_BY is null)))
120 AND ((recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
121 OR ((recinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
122 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
123 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
124 AND ((recinfo.RULE_TYPE = X_RULE_TYPE)
125 OR ((recinfo.RULE_TYPE is null) AND (X_RULE_TYPE is null)))
126 AND ((recinfo.API_TYPE = X_API_TYPE)
127 OR ((recinfo.API_TYPE is null) AND (X_API_TYPE is null)))
128 AND ((recinfo.PACKAGE_NAME = X_PACKAGE_NAME)
129 OR ((recinfo.PACKAGE_NAME is null) AND (X_PACKAGE_NAME is null)))
130 AND ((recinfo.PROCEDURE_NAME = X_PROCEDURE_NAME)
131 OR ((recinfo.PROCEDURE_NAME is null) AND (X_PROCEDURE_NAME is null)))
132 AND ((recinfo.APPLICATION_ID = X_APPLICATION_ID)
133 OR ((recinfo.APPLICATION_ID is null) AND (X_APPLICATION_ID is null)))
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140
141 return;
142 end LOCK_ROW;
143
144 procedure UPDATE_ROW (
145 X_OBJECT_RULE_ID in NUMBER,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_SECURITY_GROUP_ID in NUMBER,
148 X_APPROVAL_TYPE in VARCHAR2,
149 X_RULE_USED_BY in VARCHAR2,
150 X_OBJECT_TYPE in VARCHAR2,
151 X_RULE_TYPE in VARCHAR2,
152 X_API_TYPE in VARCHAR2,
153 X_PACKAGE_NAME in VARCHAR2,
154 X_PROCEDURE_NAME in VARCHAR2,
155 X_SEEDED_FLAG in VARCHAR2,
156 X_APPLICATION_ID in NUMBER,
157 X_LAST_UPDATE_DATE in DATE,
158 X_LAST_UPDATED_BY in NUMBER,
159 X_LAST_UPDATE_LOGIN in NUMBER
160 ) is
161 begin
162 update AMW_OBJECT_RULES set
163 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
164 APPROVAL_TYPE = X_APPROVAL_TYPE,
165 RULE_USED_BY = X_RULE_USED_BY,
166 OBJECT_TYPE = X_OBJECT_TYPE,
167 RULE_TYPE = X_RULE_TYPE,
168 API_TYPE = X_API_TYPE,
169 PACKAGE_NAME = X_PACKAGE_NAME,
170 PROCEDURE_NAME = X_PROCEDURE_NAME,
171 SEEDED_FLAG = X_SEEDED_FLAG,
172 APPLICATION_ID = X_APPLICATION_ID,
173 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
174 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
175 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
176 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
177
178 if (sql%notfound) then
179 raise no_data_found;
180 end if;
181
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186 end UPDATE_ROW;
187
188 procedure DELETE_ROW (
189 X_OBJECT_RULE_ID in NUMBER
190 ) is
191 begin
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 delete from AMW_OBJECT_RULES
198 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end DELETE_ROW;
204
205
206 procedure LOAD_ROW (
207 X_OBJECT_RULE_ID in NUMBER,
208 X_OBJECT_VERSION_NUMBER in NUMBER,
209 X_SECURITY_GROUP_ID in NUMBER,
210 X_APPROVAL_TYPE in VARCHAR2,
211 X_RULE_USED_BY in VARCHAR2,
212 X_OBJECT_TYPE in VARCHAR2,
213 X_RULE_TYPE in VARCHAR2,
214 X_API_TYPE in VARCHAR2,
215 X_PACKAGE_NAME in VARCHAR2,
216 X_PROCEDURE_NAME in VARCHAR2,
217 X_SEEDED_FLAG in VARCHAR2,
218 X_APPLICATION_ID in NUMBER,
219 X_OWNER IN VARCHAR2
220 )is
221 l_user_id number := 0;
222 l_objrule_id number;
223 l_obj_verno number;
224 l_dummy_char varchar2(1);
225 l_row_id varchar2(100);
226
227 cursor c_obj_verno is
228 select object_version_number
229 from AMW_OBJECT_RULES
230 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
231
232 cursor c_chk_objrule_exists is
233 select 'x'
234 from AMW_OBJECT_RULES
235 where OBJECT_RULE_ID = X_OBJECT_RULE_ID;
236
237 cursor c_get_objrule_id is
238 select AMW_OBJECT_RULES_S.nextval
239 from dual;
240
241 BEGIN
242 if X_OWNER = 'SEED' then
243 l_user_id := 1;
244 end if;
245
246 open c_chk_objrule_exists;
247 fetch c_chk_objrule_exists into l_dummy_char;
248 if c_chk_objrule_exists%notfound
249 then
250 close c_chk_objrule_exists;
251 if X_OBJECT_RULE_ID is null
252 then
253 open c_get_objrule_id;
254 fetch c_get_objrule_id into l_objrule_id;
255 close c_get_objrule_id;
256 else
257 l_objrule_id := X_OBJECT_RULE_ID;
258 end if;
259
260 AMW_OBJECT_RULES_PKG.INSERT_ROW (
261 X_ROWID => l_row_id,
262 X_OBJECT_RULE_ID => l_objrule_id,
263 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
264 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
265 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
266 X_RULE_USED_BY => X_RULE_USED_BY,
267 X_OBJECT_TYPE => X_OBJECT_TYPE,
268 X_RULE_TYPE => X_RULE_TYPE,
269 X_API_TYPE => X_API_TYPE,
270 X_PACKAGE_NAME => X_PACKAGE_NAME,
271 X_PROCEDURE_NAME => X_PROCEDURE_NAME,
272 X_SEEDED_FLAG => X_SEEDED_FLAG,
273 X_APPLICATION_ID => X_APPLICATION_ID ,
274 X_CREATION_DATE => sysdate,
275 X_CREATED_BY => l_user_id,
276 X_LAST_UPDATE_DATE => sysdate,
277 X_LAST_UPDATED_BY => l_user_id,
278 X_LAST_UPDATE_LOGIN => 0
279 );
280 else
281 close c_chk_objrule_exists;
282 open c_obj_verno;
283 fetch c_obj_verno into l_obj_verno;
284 close c_obj_verno;
285
286 -- assigning value for l_user_status_id
287 l_objrule_id := X_OBJECT_RULE_ID;
288 AMW_OBJECT_RULES_PKG.UPDATE_ROW(
289 X_OBJECT_RULE_ID => X_OBJECT_RULE_ID,
290 X_OBJECT_VERSION_NUMBER => l_obj_verno,
291 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
292 X_APPROVAL_TYPE => X_APPROVAL_TYPE,
293 X_RULE_USED_BY => X_RULE_USED_BY,
294 X_OBJECT_TYPE => X_OBJECT_TYPE,
295 X_RULE_TYPE => X_RULE_TYPE,
296 X_API_TYPE => X_API_TYPE,
297 X_PACKAGE_NAME => X_PACKAGE_NAME,
298 X_PROCEDURE_NAME => X_PROCEDURE_NAME,
299 X_SEEDED_FLAG => X_SEEDED_FLAG,
300 X_APPLICATION_ID => X_APPLICATION_ID,
301 X_LAST_UPDATE_DATE => SYSDATE,
302 X_LAST_UPDATED_BY => l_user_id,
303 X_LAST_UPDATE_LOGIN => 0
304 );
305 END IF;
306
307 end LOAD_ROW;
308
309
310 end AMW_OBJECT_RULES_PKG;