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