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