1 PACKAGE BODY AME_CONDITION_USAGES_API2 AS
2 /* $Header: amecgapi.pkb 120.1 2005/10/14 04:11:53 ubhat noship $ */
3
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15
16 procedure KEY_TO_IDS (
17 X_RULE_KEY in VARCHAR2,
18 X_CONDITION_KEY in VARCHAR2,
19 X_CONDITION_USAGE out nocopy VARCHAR2,
20 X_RULE_ID out nocopy NUMBER,
21 X_CONDITION_ID out nocopy NUMBER
22 ) is
23 cursor CSR_GET_CONDITION_ID
24 (
25 X_CONDITION_KEY in VARCHAR2
26 ) is
27 select CONDITION_ID
28 from AME_CONDITIONS
29 where CONDITION_KEY = X_CONDITION_KEY;
30 cursor CSR_GET_RULE_ID
31 (
32 X_RULE_KEY in VARCHAR2
33 ) is
34 select RULE_ID
35 from AME_RULES
36 where RULE_KEY = X_RULE_KEY;
37 cursor CSR_GET_CONDITION_USAGE
38 (
39 X_CONDITION_ID in NUMBER,
40 X_RULE_ID in NUMBER
41 ) is
42 select 'FOUND'
43 from AME_CONDITION_USAGES
44 where CONDITION_ID = X_CONDITION_ID
45 and RULE_ID = X_RULE_ID;
46 begin
47 open CSR_GET_CONDITION_ID (
48 X_CONDITION_KEY
49 );
50 fetch CSR_GET_CONDITION_ID into X_CONDITION_ID;
51 if (CSR_GET_CONDITION_ID%notfound) then
52 X_CONDITION_ID := null;
53 end if;
54 close CSR_GET_CONDITION_ID;
55
56 open CSR_GET_RULE_ID (
57 X_RULE_KEY
58 );
59 fetch CSR_GET_RULE_ID into X_RULE_ID;
60 if (CSR_GET_RULE_ID%notfound) then
61 X_RULE_ID := null;
62 end if;
63 close CSR_GET_RULE_ID;
64
65 if (X_CONDITION_ID is not null) and
66 (X_RULE_ID is not null) then
67 open CSR_GET_CONDITION_USAGE(
68 X_CONDITION_ID,
69 X_RULE_ID
70 );
71 fetch CSR_GET_CONDITION_USAGE into X_CONDITION_USAGE;
72 if (CSR_GET_CONDITION_USAGE%notfound) then
73 X_CONDITION_USAGE := 'NOTFOUND';
74 end if;
75 close CSR_GET_CONDITION_USAGE;
76 end if;
77
78 end KEY_TO_IDS;
79
80 procedure KEY_TO_IDS_2 (
81 X_RULE_ID in NUMBER,
82 X_CONDITION_ID in NUMBER,
83 X_CONDITION_USAGE_COUNT out nocopy NUMBER
84 ) is
85 cursor CSR_GET_COND_USAGE_COUNT
86 (
87 X_CONDITION_ID in NUMBER,
88 X_RULE_ID in NUMBER
89 ) is
90 select COUNT(*)
91 from AME_CONDITION_USAGES
92 where CONDITION_ID = X_CONDITION_ID
93 and RULE_ID = X_RULE_ID;
94
95 X_ATTRIBUTE_ID NUMBER;
96
97 begin
98
99 if (X_CONDITION_ID is not null) and
100 (X_RULE_ID is not null) then
101 open CSR_GET_COND_USAGE_COUNT (
102 X_CONDITION_ID,
103 X_RULE_ID
104 );
105 fetch CSR_GET_COND_USAGE_COUNT into X_CONDITION_USAGE_COUNT;
106 close CSR_GET_COND_USAGE_COUNT;
107 end if;
108
109 end KEY_TO_IDS_2;
110
111 procedure KEY_TO_IDS_3 (
112 X_RULE_KEY in NUMBER,
113 X_CONDITION_ID in NUMBER,
114 X_RULE_ID out nocopy NUMBER,
115 X_CONDITION_USAGE_COUNT out nocopy NUMBER
116 ) is
117 cursor CSR_GET_COND_USAGE_COUNT
118 (
119 X_CONDITION_ID in NUMBER,
120 X_RULE_ID in NUMBER
121 ) is
122 select COUNT(*)
123 from AME_CONDITION_USAGES
124 where CONDITION_ID = X_CONDITION_ID
125 and RULE_ID = X_RULE_ID;
126 cursor CSR_GET_RULE_ID
127 (
128 X_RULE_KEY in VARCHAR2
129 ) is
130 select RULE_ID
131 from AME_RULES
132 where RULE_KEY = X_RULE_KEY;
133 X_ATTRIBUTE_ID NUMBER;
134 begin
135 open CSR_GET_RULE_ID (
136 X_RULE_KEY
137 );
138 fetch CSR_GET_RULE_ID into X_RULE_ID;
139 if (CSR_GET_RULE_ID%notfound) then
140 X_RULE_ID := null;
141 end if;
142 close CSR_GET_RULE_ID;
143 if (X_CONDITION_ID is not null) and
144 (X_RULE_ID is not null) then
145 open CSR_GET_COND_USAGE_COUNT (
146 X_CONDITION_ID,
147 X_RULE_ID
148 );
149 fetch CSR_GET_COND_USAGE_COUNT into X_CONDITION_USAGE_COUNT;
150 close CSR_GET_COND_USAGE_COUNT;
151 end if;
152 end KEY_TO_IDS_3;
153
154 procedure INSERT_ROW (
155 X_RULE_ID in NUMBER,
156 X_CONDITION_ID in NUMBER,
157 X_CREATED_BY in NUMBER,
158 X_CREATION_DATE in DATE,
159 X_LAST_UPDATED_BY in NUMBER,
160 X_LAST_UPDATE_DATE in DATE,
161 X_LAST_UPDATE_LOGIN in NUMBER,
162 X_START_DATE in DATE,
163 X_OBJECT_VERSION_NUMBER in NUMBER)
164 is
165
166 begin
167
168 insert into AME_CONDITION_USAGES
169 (
170 RULE_ID,
171 CONDITION_ID,
172 CREATED_BY,
173 CREATION_DATE,
174 LAST_UPDATED_BY,
175 LAST_UPDATE_DATE,
176 LAST_UPDATE_LOGIN,
177 START_DATE,
178 END_DATE,
179 OBJECT_VERSION_NUMBER
180 ) values (
181 X_RULE_ID,
182 X_CONDITION_ID,
183 X_CREATED_BY,
184 X_CREATION_DATE,
185 X_LAST_UPDATED_BY,
186 X_LAST_UPDATE_DATE,
187 X_LAST_UPDATE_LOGIN,
188 X_START_DATE,
189 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
190 X_OBJECT_VERSION_NUMBER);
191
192 end INSERT_ROW;
193
194 procedure DELETE_ROW (
195 X_RULE_ID in NUMBER,
196 X_CONDITION_ID in NUMBER)
197 is
198 begin
199 delete from AME_CONDITION_USAGES
200 where RULE_ID = X_RULE_ID
201 and CONDITION_ID = X_CONDITION_ID;
202 if (sql%notfound) then
203 raise no_data_found;
204 end if;
205 end DELETE_ROW;
206
207 procedure LOAD_ROW (
208 X_RULE_KEY in VARCHAR2,
209 X_RULE_ID in VARCHAR2,
210 X_CONDITION_KEY in VARCHAR2,
211 X_CONDITION_ID in VARCHAR2,
212 X_OWNER in VARCHAR2,
213 X_LAST_UPDATE_DATE in VARCHAR2,
214 X_CUSTOM_MODE in VARCHAR2
215 )
216 is
217 X_CONDITION_ID_LOAD NUMBER;
218 X_CONDITION_USAGE VARCHAR2(20);
219 X_CONDITION_USAGE_COUNT NUMBER := 0;
220 X_CREATED_BY NUMBER;
221 X_LAST_UPDATED_BY NUMBER;
222 X_LAST_UPDATE_LOGIN NUMBER;
223 X_RULE_ID_LOAD NUMBER;
224 begin
225 OWNER_TO_WHO (
226 X_OWNER,
227 X_CREATED_BY,
228 X_LAST_UPDATED_BY,
229 X_LAST_UPDATE_LOGIN
230 );
231 if X_CONDITION_ID > 0 then
232 -- CONDITION_ID is positive
233 -- Drive off RULE_KEY and CONDITION_KEY
234 X_CONDITION_ID_LOAD := null;
235 X_RULE_ID_LOAD := null;
236 KEY_TO_IDS (
237 X_RULE_KEY,
238 X_CONDITION_KEY,
239 X_CONDITION_USAGE,
240 X_RULE_ID_LOAD,
241 X_CONDITION_ID_LOAD
242 );
243 -- the current row was not found insert a new row
244 if (X_CONDITION_USAGE = 'NOTFOUND') then
245 INSERT_ROW (
246 X_RULE_ID_LOAD,
247 X_CONDITION_ID_LOAD,
248 X_CREATED_BY,
249 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
250 X_LAST_UPDATED_BY,
251 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
252 X_LAST_UPDATE_LOGIN,
253 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
254 1);
255 end if;
256 else
257 -- CONDITION_ID is negative
258 -- Drive off CONDITION_ID
259 if X_RULE_ID < 0 then
260 -- Drive off CONDITION_ID and RULE_ID
261 KEY_TO_IDS_2 (
262 X_RULE_ID,
263 X_CONDITION_ID,
264 X_CONDITION_USAGE_COUNT
265 );
266 -- the current row was not found insert a new row
267 if (X_CONDITION_USAGE_COUNT = 0) then
268 INSERT_ROW (
269 X_RULE_ID,
270 X_CONDITION_ID,
271 X_CREATED_BY,
272 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
273 X_LAST_UPDATED_BY,
274 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
275 X_LAST_UPDATE_LOGIN,
276 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
277 1);
278 end if;
279 else
280 -- Drive off CONDITION_ID and RULE_KEY
281 X_RULE_ID_LOAD:=null;
282 KEY_TO_IDS_3 (
283 X_RULE_KEY,
284 X_CONDITION_ID,
285 X_RULE_ID_LOAD,
286 X_CONDITION_USAGE_COUNT
287 );
288 -- the current row was not found insert a new row
289 if (X_CONDITION_USAGE_COUNT = 0) then
290 INSERT_ROW (
291 X_RULE_ID_LOAD,
292 X_CONDITION_ID,
293 X_CREATED_BY,
294 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
295 X_LAST_UPDATED_BY,
296 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
297 X_LAST_UPDATE_LOGIN,
298 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
299 1);
300 end if;
301 end if;
302 end if;
303 exception
304 when others then
305 ame_util.runtimeException('ame_condition_usages_api',
306 'load_row',
307 sqlcode,
308 sqlerrm);
309 raise;
310 end LOAD_ROW;
311 --
312 END AME_CONDITION_USAGES_API2;
[Home] [Help]