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