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