eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ALR_ACTION_GROUP_MEMBERS_PKG

Source


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