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