1 package body ALR_ACTION_SET_MEMBERS_PKG as
2 /* $Header: ALRASTMB.pls 120.3.12010000.1 2008/07/27 06:58:33 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ACTION_SET_MEMBER_ID in NUMBER,
7 X_ACTION_SET_ID in NUMBER,
8 X_ACTION_ID in NUMBER,
9 X_ACTION_GROUP_ID in NUMBER,
10 X_ALERT_ID in NUMBER,
11 X_SEQUENCE in NUMBER,
12 X_END_DATE_ACTIVE in DATE,
13 X_ENABLED_FLAG in VARCHAR2,
14 X_SUMMARY_THRESHOLD in NUMBER,
15 X_ABORT_FLAG in VARCHAR2,
16 X_ERROR_ACTION_SEQUENCE 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 cursor C is select ROWID from ALR_ACTION_SET_MEMBERS
24 where APPLICATION_ID = X_APPLICATION_ID
25 and ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID
26 ;
27 begin
28 insert into ALR_ACTION_SET_MEMBERS (
29 APPLICATION_ID,
30 ACTION_SET_MEMBER_ID,
31 ACTION_SET_ID,
32 ACTION_ID,
33 ACTION_GROUP_ID,
34 ALERT_ID,
35 SEQUENCE,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_LOGIN,
41 END_DATE_ACTIVE,
42 ENABLED_FLAG,
43 SUMMARY_THRESHOLD,
44 ABORT_FLAG,
45 ERROR_ACTION_SEQUENCE
46 ) values (
47 X_APPLICATION_ID,
48 X_ACTION_SET_MEMBER_ID,
49 X_ACTION_SET_ID,
50 X_ACTION_ID,
51 X_ACTION_GROUP_ID,
52 X_ALERT_ID,
53 X_SEQUENCE,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_CREATION_DATE,
57 X_CREATED_BY,
58 X_LAST_UPDATE_LOGIN,
59 X_END_DATE_ACTIVE,
60 X_ENABLED_FLAG,
61 X_SUMMARY_THRESHOLD,
62 X_ABORT_FLAG,
63 X_ERROR_ACTION_SEQUENCE);
64
65 open c;
66 fetch c into X_ROWID;
67 if (c%notfound) then
68 close c;
69 raise no_data_found;
70 end if;
71 close c;
72
73 end INSERT_ROW;
74
75 procedure LOAD_ROW (
76 X_APPLICATION_SHORT_NAME in VARCHAR2,
77 X_ALERT_NAME in VARCHAR2,
78 X_NAME in VARCHAR2,
79 X_OWNER in VARCHAR2,
80 X_ACTION_NAME in VARCHAR2,
81 X_GROUP_NAME in VARCHAR2,
82 X_GROUP_TYPE in VARCHAR2,
83 X_SEQUENCE in VARCHAR2,
84 X_END_DATE_ACTIVE in VARCHAR2,
85 X_ENABLED_FLAG in VARCHAR2,
86 X_SUMMARY_THRESHOLD in VARCHAR2,
87 X_ABORT_FLAG in VARCHAR2,
88 X_ERROR_ACTION_SEQUENCE in VARCHAR2,
89 X_LAST_UPDATE_DATE in VARCHAR2,
90 X_CUSTOM_MODE in VARCHAR2
91 ) is
92 l_user_id number := 0;
93 l_app_id number := 0;
94 l_alert_id number := 0;
95 l_action_id number := null; /* Can be a null value */
96 l_action_set_id number := 0;
97 l_action_group_id number := null; /* Can be a null value */
98 l_action_set_member_id number := 0;
99 l_row_id varchar2(64);
100
101 f_luby number; -- entity owner in file
102 f_ludate date; -- entity update date in file
103 db_luby number; -- entity owner in db
104 db_ludate date; -- entity update date in db
105
106 begin
107
108 -- EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever ,level 4''';
109
110 DBMS_SESSION.SET_SQL_TRACE(FALSE);
111 DBMS_SESSION.SET_SQL_TRACE(TRUE);
112
113 -- Translate owner to file_last_updated_by
114 f_luby := fnd_load_util.owner_id(X_OWNER);
115
116 -- Translate char last_update_date to date
117 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
118
119 select application_id into l_app_id
120 from fnd_application
121 where application_short_name = X_APPLICATION_SHORT_NAME;
122
123 select alert_id into l_alert_id
124 from alr_alerts
125 where application_id = l_app_id
126 and alert_name = X_ALERT_NAME;
127
128 select action_set_id into l_action_set_id
129 from alr_action_sets
130 where application_id = l_app_id
131 and alert_id = l_alert_id
132 and name = X_NAME;
133
134 if ((X_GROUP_NAME is not null) and (X_GROUP_TYPE is not null)) then
135 select action_group_id into l_action_group_id
136 from alr_action_groups
137 where application_id = l_app_id
138 and alert_id = l_alert_id
139 and name = X_GROUP_NAME
140 and group_type = X_GROUP_TYPE;
141 end if;
142
143 /* The X_ACTION_NAME can be null */
144 if (X_ACTION_NAME is not null) then
145 /* 3779021 since the action_id is hardcoded */
146 if (X_ACTION_NAME = 'Exit Action Set Successfully') then
147 l_action_id := -5;
148 /* 3779021 */
149 else
150 select action_id into l_action_id
151 from alr_actions
152 where application_id = l_app_id
153 and alert_id = l_alert_id
154 and name = X_ACTION_NAME
155 and ((end_date_active is null) or
156 ((end_date_active is not null) and (end_date_active =
157 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
158 end if;
159 end if;
160
161 /* Place the columns which can be null on the bottom */
162 select distinct action_set_member_id into l_action_set_member_id
163 from alr_action_set_members
164 where application_id = l_app_id
165 and action_set_id = l_action_set_id
166 and alert_id = l_alert_id
167 and sequence = to_number(X_SEQUENCE)
168 and enabled_flag = X_ENABLED_FLAG
169 and abort_flag = X_ABORT_FLAG
170 and (((action_id is null) and (l_action_id is null))
171 or ((action_id is not null)
172 and (action_id = l_action_id)))
173 and (((action_group_id is null) and (l_action_group_id is null))
174 or ((action_group_id is not null)
175 and (action_group_id = l_action_group_id)))
176 and (((end_date_active is null) and (X_END_DATE_ACTIVE is null))
177 or ((end_date_active is not null)
178 and (end_date_active =
179 to_date (X_END_DATE_ACTIVE, 'YYYY/MM/DD HH24:MI:SS'))))
180 and (((summary_threshold is null) and (X_SUMMARY_THRESHOLD is null))
181 or ((summary_threshold is not null)
182 and (summary_threshold = to_number(X_SUMMARY_THRESHOLD))))
183 and (((error_action_sequence is null)
184 and (X_ERROR_ACTION_SEQUENCE is null))
185 or ((error_action_sequence is not null)
186 and (error_action_sequence = to_number(X_ERROR_ACTION_SEQUENCE))));
187
188 select last_updated_by, last_update_date
189 into db_luby, db_ludate
190 from ALR_ACTION_SET_MEMBERS
191 where application_id = l_app_id
192 and action_set_member_id = l_action_set_member_id;
193
194 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
195 x_custom_mode)) then
196
197 ALR_ACTION_SET_MEMBERS_PKG.UPDATE_ROW(
198 X_APPLICATION_ID => l_app_id,
199 X_ACTION_SET_MEMBER_ID => l_action_set_member_id,
200 X_ACTION_SET_ID => l_action_set_id,
201 X_ACTION_ID => l_action_id,
202 X_ACTION_GROUP_ID => l_action_group_id,
203 X_ALERT_ID => l_alert_id,
204 X_SEQUENCE => to_number(X_SEQUENCE),
205 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
206 X_ENABLED_FLAG => X_ENABLED_FLAG,
207 X_SUMMARY_THRESHOLD => to_number(X_SUMMARY_THRESHOLD),
208 X_ABORT_FLAG => X_ABORT_FLAG,
209 X_ERROR_ACTION_SEQUENCE => to_number(X_ERROR_ACTION_SEQUENCE),
210 X_LAST_UPDATE_DATE => f_ludate,
211 X_LAST_UPDATED_BY => f_luby,
212 X_LAST_UPDATE_LOGIN => 0 );
213
214 end if;
215
216 exception
217
218 when NO_DATA_FOUND then
219
220 select ALR_ACTION_SET_MEMBERS_S.nextval
221 into l_action_set_member_id
222 from dual;
223
224 ALR_ACTION_SET_MEMBERS_PKG.INSERT_ROW(
225 X_ROWID => l_row_id,
226 X_APPLICATION_ID => l_app_id,
227 X_ACTION_SET_MEMBER_ID => l_action_set_member_id,
228 X_ACTION_SET_ID => l_action_set_id,
229 X_ACTION_ID => l_action_id,
230 X_ACTION_GROUP_ID => l_action_group_id,
231 X_ALERT_ID => l_alert_id,
232 X_SEQUENCE => to_number(X_SEQUENCE),
233 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
234 X_ENABLED_FLAG => X_ENABLED_FLAG,
235 X_SUMMARY_THRESHOLD => to_number(X_SUMMARY_THRESHOLD),
236 X_ABORT_FLAG => X_ABORT_FLAG,
237 X_ERROR_ACTION_SEQUENCE => to_number(X_ERROR_ACTION_SEQUENCE),
238 X_CREATION_DATE => f_ludate,
239 X_CREATED_BY => f_luby,
240 X_LAST_UPDATE_DATE => f_ludate,
241 X_LAST_UPDATED_BY => f_luby,
242 X_LAST_UPDATE_LOGIN => 0 );
243
244 end LOAD_ROW;
245
246 procedure LOCK_ROW (
247 X_APPLICATION_ID in NUMBER,
248 X_ACTION_SET_MEMBER_ID in NUMBER,
249 X_ACTION_SET_ID in NUMBER,
250 X_ACTION_ID in NUMBER,
251 X_ACTION_GROUP_ID in NUMBER,
252 X_ALERT_ID in NUMBER,
253 X_SEQUENCE in NUMBER,
254 X_END_DATE_ACTIVE in DATE,
255 X_ENABLED_FLAG in VARCHAR2,
256 X_SUMMARY_THRESHOLD in NUMBER,
257 X_ABORT_FLAG in VARCHAR2,
258 X_ERROR_ACTION_SEQUENCE in NUMBER
259 ) is
260 cursor c1 is select
261 ACTION_SET_ID,
262 ACTION_ID,
263 ACTION_GROUP_ID,
264 ALERT_ID,
265 SEQUENCE,
266 END_DATE_ACTIVE,
267 ENABLED_FLAG,
268 SUMMARY_THRESHOLD,
269 ABORT_FLAG,
270 ERROR_ACTION_SEQUENCE,
271 APPLICATION_ID,
272 ACTION_SET_MEMBER_ID
273 from ALR_ACTION_SET_MEMBERS
274 where APPLICATION_ID = X_APPLICATION_ID
275 and ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID
276 for update of APPLICATION_ID nowait;
277 begin
278 for recinfo in c1 loop
279 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
280 AND (recinfo.ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID)
281 AND (recinfo.ACTION_SET_ID = X_ACTION_SET_ID)
282 AND ((recinfo.ACTION_ID = X_ACTION_ID)
283 OR ((recinfo.ACTION_ID is null) AND (X_ACTION_ID is null)))
284 AND ((recinfo.ACTION_GROUP_ID = X_ACTION_GROUP_ID)
285 OR ((recinfo.ACTION_GROUP_ID is null)
286 AND (X_ACTION_GROUP_ID is null)))
287 AND (recinfo.ALERT_ID = X_ALERT_ID)
288 AND (recinfo.SEQUENCE = X_SEQUENCE)
289 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
290 OR ((recinfo.END_DATE_ACTIVE is null)
291 AND (X_END_DATE_ACTIVE is null)))
292 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
293 AND ((recinfo.SUMMARY_THRESHOLD = X_SUMMARY_THRESHOLD)
294 OR ((recinfo.SUMMARY_THRESHOLD is null)
295 AND (X_SUMMARY_THRESHOLD is null)))
296 AND (recinfo.ABORT_FLAG = X_ABORT_FLAG)
297 AND ((recinfo.ERROR_ACTION_SEQUENCE = X_ERROR_ACTION_SEQUENCE)
298 OR ((recinfo.ERROR_ACTION_SEQUENCE is null)
299 AND (X_ERROR_ACTION_SEQUENCE is null)))
300 ) then
301 null;
302 else
303 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
304 app_exception.raise_exception;
305 end if;
306 end loop;
307 return;
308 end LOCK_ROW;
309
310 procedure UPDATE_ROW (
311 X_APPLICATION_ID in NUMBER,
312 X_ACTION_SET_MEMBER_ID in NUMBER,
313 X_ACTION_SET_ID in NUMBER,
314 X_ACTION_ID in NUMBER,
315 X_ACTION_GROUP_ID in NUMBER,
316 X_ALERT_ID in NUMBER,
317 X_SEQUENCE in NUMBER,
318 X_END_DATE_ACTIVE in DATE,
319 X_ENABLED_FLAG in VARCHAR2,
320 X_SUMMARY_THRESHOLD in NUMBER,
321 X_ABORT_FLAG in VARCHAR2,
322 X_ERROR_ACTION_SEQUENCE in NUMBER,
323 X_LAST_UPDATE_DATE in DATE,
324 X_LAST_UPDATED_BY in NUMBER,
328 update ALR_ACTION_SET_MEMBERS set
325 X_LAST_UPDATE_LOGIN in NUMBER
326 ) is
327 begin
329 ACTION_SET_ID = X_ACTION_SET_ID,
330 ACTION_ID = X_ACTION_ID,
331 ACTION_GROUP_ID = X_ACTION_GROUP_ID,
332 ALERT_ID = X_ALERT_ID,
333 SEQUENCE = X_SEQUENCE,
334 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
335 ENABLED_FLAG = X_ENABLED_FLAG,
336 SUMMARY_THRESHOLD = X_SUMMARY_THRESHOLD,
337 ABORT_FLAG = X_ABORT_FLAG,
338 ERROR_ACTION_SEQUENCE = X_ERROR_ACTION_SEQUENCE,
339 APPLICATION_ID = X_APPLICATION_ID,
340 ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID,
341 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
342 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
343 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
344 where APPLICATION_ID = X_APPLICATION_ID
345 and ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID;
346
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350 end UPDATE_ROW;
351
352 procedure DELETE_ROW (
353 X_APPLICATION_ID in NUMBER,
354 X_ACTION_SET_MEMBER_ID in NUMBER
355 ) is
356 begin
357 delete from ALR_ACTION_SET_MEMBERS
358 where APPLICATION_ID = X_APPLICATION_ID
359 and ACTION_SET_MEMBER_ID = X_ACTION_SET_MEMBER_ID;
360
361 if (sql%notfound) then
362 raise no_data_found;
363 end if;
364
365 end DELETE_ROW;
366
367
368 end ALR_ACTION_SET_MEMBERS_PKG;
[Home] [Help]