[Home] [Help]
PACKAGE BODY: APPS.ALR_RESPONSE_ACTIONS_PKG
Source
1 package body ALR_RESPONSE_ACTIONS_PKG as
2 /* $Header: ALRRACTB.pls 120.3.12010000.1 2008/07/27 06:58:58 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ALERT_ID in NUMBER,
7 X_RESPONSE_SET_ID in NUMBER,
8 X_RESPONSE_ID in NUMBER,
9 X_SEQUENCE in NUMBER,
10 X_ACTION_ID in NUMBER,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_END_DATE_ACTIVE in DATE,
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 ) is
19 cursor C is select ROWID from ALR_RESPONSE_ACTIONS
20 where APPLICATION_ID = X_APPLICATION_ID
21 and ALERT_ID = X_ALERT_ID
22 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
23 and RESPONSE_ID = X_RESPONSE_ID
24 and SEQUENCE = X_SEQUENCE
25 ;
26 begin
27 insert into ALR_RESPONSE_ACTIONS (
28 APPLICATION_ID,
29 ALERT_ID,
30 RESPONSE_SET_ID,
31 RESPONSE_ID,
32 ACTION_ID,
33 SEQUENCE,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_LOGIN,
39 ENABLED_FLAG,
40 END_DATE_ACTIVE
41 ) values (
42 X_APPLICATION_ID,
43 X_ALERT_ID,
44 X_RESPONSE_SET_ID,
45 X_RESPONSE_ID,
46 X_ACTION_ID,
47 X_SEQUENCE,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_LOGIN,
53 X_ENABLED_FLAG,
54 X_END_DATE_ACTIVE
55 );
56
57 open c;
58 fetch c into X_ROWID;
59 if (c%notfound) then
60 close c;
61 raise no_data_found;
62 end if;
63 close c;
64
65 end INSERT_ROW;
66
67 procedure LOAD_ROW (
68 X_APPLICATION_SHORT_NAME in VARCHAR2,
69 X_ALERT_NAME in VARCHAR2,
70 X_RESP_SET_NAME in VARCHAR2,
71 X_RESPONSE_NAME in VARCHAR2,
72 X_OWNER in VARCHAR2,
73 X_SEQUENCE in VARCHAR2,
74 X_ACTION_NAME in VARCHAR2,
75 X_ENABLED_FLAG in VARCHAR2,
76 X_END_DATE_ACTIVE in VARCHAR2,
77 X_LAST_UPDATE_DATE in VARCHAR2,
78 X_CUSTOM_MODE in VARCHAR2
79 ) is
80
81 l_user_id number := 0;
82 l_app_id number := 0;
83 l_alert_id number := 0;
84 l_response_set_id number := 0;
85 l_response_id number := 0;
86 l_action_id number := 0;
87 l_sequence number := 0;
88 l_row_id varchar2(64);
89
90 f_luby number; -- entity owner in file
91 f_ludate date; -- entity update date in file
92 db_luby number; -- entity owner in db
93 db_ludate date; -- entity update date in db
94
95
96 begin
97
98 -- Translate owner to file_last_updated_by
99 f_luby := fnd_load_util.owner_id(X_OWNER);
100
101 -- Translate char last_update_date to date
102 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
103
104 select application_id into l_app_id
105 from fnd_application
106 where application_short_name = X_APPLICATION_SHORT_NAME;
107
108 select alert_id into l_alert_id
109 from alr_alerts
110 where application_id = l_app_id
111 and alert_name = X_ALERT_NAME;
112
113 select response_set_id into l_response_set_id
114 from alr_response_sets
115 where application_id = l_app_id
116 and alert_id = l_alert_id
117 and name = X_RESP_SET_NAME ;
118
119 /* There is no unique key. The where clause is missing response_text
120 because it is a long column. It is also missing type because type is
121 not in the load_row parameter list. */
122 select response_id into l_response_id
123 from alr_valid_responses
124 where application_id = l_app_id
125 and alert_id = l_alert_id
126 and response_set_id = l_response_set_id
127 and ((response_name is null)
128 or ((response_name is not null)
129 and (response_name = X_RESPONSE_NAME)));
130
131 /* end_date_active can be a null column */
132 select action_id into l_action_id
133 from alr_actions
134 where application_id = l_app_id
135 and alert_id = l_alert_id
136 and name = X_ACTION_NAME
137 and ((end_date_active is null)
138 or ((end_date_active is not null)
139 and (end_date_active =
140 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
141
142 select last_updated_by, last_update_date
143 into db_luby, db_ludate
144 from ALR_RESPONSE_ACTIONS
145 where application_id = l_app_id
146 and alert_id = l_alert_id
147 and response_id = l_response_id
148 and response_set_id = l_response_set_id
149 and sequence = to_number(X_SEQUENCE);
150
151 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
152 x_custom_mode)) then
153
154 ALR_RESPONSE_ACTIONS_PKG.UPDATE_ROW(
155 X_APPLICATION_ID => l_app_id,
156 X_ALERT_ID => l_alert_id,
157 X_RESPONSE_SET_ID => l_response_set_id,
158 X_RESPONSE_ID => l_response_id,
159 X_SEQUENCE => to_number(X_SEQUENCE),
160 X_ACTION_ID => l_action_id,
161 X_ENABLED_FLAG => X_ENABLED_FLAG,
162 X_END_DATE_ACTIVE =>
163 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
164 X_LAST_UPDATE_DATE => f_ludate,
165 X_LAST_UPDATED_BY => f_luby,
166 X_LAST_UPDATE_LOGIN => 0 );
167
168 end if;
169 exception
170
171 when NO_DATA_FOUND then
172
173 select nvl(max(to_number(SEQUENCE)),0)+1 into l_sequence
174 from ALR_RESPONSE_ACTIONS
175 where APPLICATION_ID = l_app_id
176 and RESPONSE_ID = l_response_id;
177 -- insert into applsys.nancy values ('l_sequence', l_sequence);
178 -- commit;
179
180 ALR_RESPONSE_ACTIONS_PKG.INSERT_ROW(
181 X_ROWID => l_row_id,
182 X_APPLICATION_ID => l_app_id,
183 X_ALERT_ID => l_alert_id,
184 X_RESPONSE_SET_ID => l_response_set_id,
185 X_RESPONSE_ID => l_response_id,
186 X_SEQUENCE => to_number(X_SEQUENCE),
187 X_ACTION_ID => l_action_id,
188 X_ENABLED_FLAG => X_ENABLED_FLAG,
189 X_END_DATE_ACTIVE =>
190 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
191 X_CREATION_DATE => f_ludate,
192 X_CREATED_BY => f_luby,
193 X_LAST_UPDATE_DATE => f_ludate,
194 X_LAST_UPDATED_BY => f_luby,
195 X_LAST_UPDATE_LOGIN => 0 );
196
197
198 end LOAD_ROW;
199
200 procedure LOCK_ROW (
201 X_APPLICATION_ID in NUMBER,
202 X_ALERT_ID in NUMBER,
203 X_RESPONSE_SET_ID in NUMBER,
204 X_RESPONSE_ID in NUMBER,
205 X_SEQUENCE in NUMBER,
206 X_ACTION_ID in NUMBER,
207 X_ENABLED_FLAG in VARCHAR2,
208 X_END_DATE_ACTIVE in DATE
209 ) is
210 cursor c1 is select
211 ACTION_ID,
212 ENABLED_FLAG,
213 END_DATE_ACTIVE,
214 APPLICATION_ID,
215 ALERT_ID,
216 RESPONSE_SET_ID,
217 RESPONSE_ID,
218 SEQUENCE
219 from ALR_RESPONSE_ACTIONS
220 where APPLICATION_ID = X_APPLICATION_ID
221 and ALERT_ID = X_ALERT_ID
222 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
223 and RESPONSE_ID = X_RESPONSE_ID
224 and SEQUENCE = X_SEQUENCE
225 for update of APPLICATION_ID nowait;
226 begin
227 for recinfo in c1 loop
228 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
229 AND (recinfo.ALERT_ID = X_ALERT_ID)
230 AND (recinfo.RESPONSE_SET_ID = X_RESPONSE_SET_ID)
231 AND (recinfo.RESPONSE_ID = X_RESPONSE_ID)
232 AND (recinfo.SEQUENCE = X_SEQUENCE)
233 AND (recinfo.ACTION_ID = X_ACTION_ID)
234 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
235 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
236 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
237 ) then
238 null;
239 else
240 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
241 app_exception.raise_exception;
242 end if;
243 end loop;
244 return;
245 end LOCK_ROW;
246
247 procedure UPDATE_ROW (
248 X_APPLICATION_ID in NUMBER,
249 X_ALERT_ID in NUMBER,
250 X_RESPONSE_SET_ID in NUMBER,
251 X_RESPONSE_ID in NUMBER,
252 X_SEQUENCE in NUMBER,
253 X_ACTION_ID in NUMBER,
254 X_ENABLED_FLAG in VARCHAR2,
255 X_END_DATE_ACTIVE in DATE,
256 X_LAST_UPDATE_DATE in DATE,
257 X_LAST_UPDATED_BY in NUMBER,
258 X_LAST_UPDATE_LOGIN in NUMBER
259 ) is
260 begin
261 update ALR_RESPONSE_ACTIONS set
262 ACTION_ID = X_ACTION_ID,
263 ENABLED_FLAG = X_ENABLED_FLAG,
264 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
265 APPLICATION_ID = X_APPLICATION_ID,
266 ALERT_ID = X_ALERT_ID,
267 RESPONSE_SET_ID = X_RESPONSE_SET_ID,
268 RESPONSE_ID = X_RESPONSE_ID,
269 SEQUENCE = X_SEQUENCE,
270 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
271 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
272 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
273 where APPLICATION_ID = X_APPLICATION_ID
274 and ALERT_ID = X_ALERT_ID
275 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
276 and RESPONSE_ID = X_RESPONSE_ID
277 and SEQUENCE = X_SEQUENCE;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282 end UPDATE_ROW;
283
284 procedure DELETE_ROW (
285 X_APPLICATION_ID in NUMBER,
286 X_ALERT_ID in NUMBER,
287 X_RESPONSE_SET_ID in NUMBER,
288 X_RESPONSE_ID in NUMBER,
289 X_SEQUENCE in NUMBER
290 ) is
291 begin
292 delete from ALR_RESPONSE_ACTIONS
293 where APPLICATION_ID = X_APPLICATION_ID
294 and ALERT_ID = X_ALERT_ID
295 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
296 and RESPONSE_ID = X_RESPONSE_ID
297 and SEQUENCE = X_SEQUENCE;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303 end DELETE_ROW;
304
305 end ALR_RESPONSE_ACTIONS_PKG;