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_ALERT_OUTPUTS_PKG

Source


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