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