[Home] [Help]
PACKAGE BODY: APPS.ALR_RESPONSE_SETS_PKG
Source
1 package body ALR_RESPONSE_SETS_PKG as
2 /* $Header: ALRRSTSB.pls 120.4.12010000.1 2008/07/27 06:59:00 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_NAME in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_ENABLED_FLAG in VARCHAR2,
11 X_END_DATE_ACTIVE in DATE,
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_RESPONSE_SETS
19 where APPLICATION_ID = X_APPLICATION_ID
20 and ALERT_ID = X_ALERT_ID
21 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
22 ;
23 begin
24 insert into ALR_RESPONSE_SETS (
25 APPLICATION_ID,
26 RESPONSE_SET_ID,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 CREATION_DATE,
30 CREATED_BY,
31 NAME,
32 ALERT_ID,
33 DESCRIPTION,
34 LAST_UPDATE_LOGIN,
35 ENABLED_FLAG,
36 END_DATE_ACTIVE
37 ) values (
38 X_APPLICATION_ID,
39 X_RESPONSE_SET_ID,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_NAME,
45 X_ALERT_ID,
46 X_DESCRIPTION,
47 X_LAST_UPDATE_LOGIN,
48 X_ENABLED_FLAG,
49 X_END_DATE_ACTIVE);
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOAD_ROW (
62 X_APPLICATION_SHORT_NAME in VARCHAR2,
63 X_ALERT_NAME in VARCHAR2,
64 X_RESP_SET_NAME in VARCHAR2,
65 X_OWNER in VARCHAR2,
66 X_DESCRIPTION in VARCHAR2,
67 X_ENABLED_FLAG in VARCHAR2,
68 X_END_DATE_ACTIVE in VARCHAR2,
69 X_LAST_UPDATE_DATE in VARCHAR2,
70 X_CUSTOM_MODE in VARCHAR2
71 ) is
72
73 l_user_id number := 0;
74 l_app_id number := 0;
75 l_alert_id number := 0;
76 l_resp_set_id number := 0;
77 l_row_id varchar2(64);
78
79 f_luby number; -- entity owner in file
80 f_ludate date; -- entity update date in file
81 db_luby number; -- entity owner in db
82 db_ludate date; -- entity update date in db
83
84 begin
85
86 -- Translate owner to file_last_updated_by
87 f_luby := fnd_load_util.owner_id(X_OWNER);
88
89 -- Translate char last_update_date to date
90 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
91
92 select application_id into l_app_id
93 from fnd_application
94 where application_short_name = X_APPLICATION_SHORT_NAME;
95
96 select alert_id into l_alert_id
97 from alr_alerts
98 where application_id = l_app_id
99 and alert_name = X_ALERT_NAME;
100
101 select response_set_id into l_resp_set_id
102 from alr_response_sets
103 where application_id = l_app_id
104 and alert_id = l_alert_id
105 and name = X_RESP_SET_NAME;
106
107 /* TSHORT 5251609 - Deleting children as
108 primary key has sequence and can't be used
109 to upload */
110
111 delete ALR_RESPONSE_ACTIONS
112 where application_id = l_app_id
113 and alert_id = l_alert_id
114 and response_set_id = l_resp_set_id;
115
116 select last_updated_by, last_update_date
117 into db_luby, db_ludate
118 from ALR_RESPONSE_SETS
119 where application_id = l_app_id
120 and alert_id = l_alert_id
121 and response_set_id = l_resp_set_id;
122
123 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
124 x_custom_mode)) then
125
126 ALR_RESPONSE_SETS_PKG.UPDATE_ROW(
127 X_APPLICATION_ID => l_app_id,
128 X_ALERT_ID => l_alert_id,
129 X_RESPONSE_SET_ID => l_resp_set_id,
130 X_NAME => X_RESP_SET_NAME,
131 X_DESCRIPTION => X_DESCRIPTION,
132 X_ENABLED_FLAG => X_ENABLED_FLAG,
133 X_END_DATE_ACTIVE =>
134 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
135 X_LAST_UPDATE_DATE => f_ludate,
136 X_LAST_UPDATED_BY => f_luby,
137 X_LAST_UPDATE_LOGIN => 0 );
138
139 end if;
140
141 exception
142
143 when NO_DATA_FOUND then
144
145 select ALR_RESPONSE_SETS_S.nextval into l_resp_set_id from DUAL;
146
147 ALR_RESPONSE_SETS_PKG.INSERT_ROW(
148 X_ROWID => l_row_id,
149 X_APPLICATION_ID => l_app_id,
150 X_ALERT_ID => l_alert_id,
151 X_RESPONSE_SET_ID => l_resp_set_id,
152 X_NAME => X_RESP_SET_NAME,
153 X_DESCRIPTION => X_DESCRIPTION,
154 X_ENABLED_FLAG => X_ENABLED_FLAG,
155 X_END_DATE_ACTIVE =>
156 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
157 X_CREATION_DATE => f_ludate,
158 X_CREATED_BY => f_luby,
159 X_LAST_UPDATE_DATE => f_ludate,
160 X_LAST_UPDATED_BY => f_luby,
161 X_LAST_UPDATE_LOGIN => 0 );
162
163 end LOAD_ROW;
164
165 procedure LOCK_ROW (
166 X_APPLICATION_ID in NUMBER,
167 X_ALERT_ID in NUMBER,
168 X_RESPONSE_SET_ID in NUMBER,
169 X_NAME in VARCHAR2,
170 X_DESCRIPTION in VARCHAR2,
171 X_ENABLED_FLAG in VARCHAR2,
172 X_END_DATE_ACTIVE in DATE
173 ) is
174 cursor c1 is select
175 NAME,
176 DESCRIPTION,
177 ENABLED_FLAG,
178 END_DATE_ACTIVE,
179 APPLICATION_ID,
180 ALERT_ID,
181 RESPONSE_SET_ID
182 from ALR_RESPONSE_SETS
183 where APPLICATION_ID = X_APPLICATION_ID
184 and ALERT_ID = X_ALERT_ID
185 and RESPONSE_SET_ID = X_RESPONSE_SET_ID
186 for update of APPLICATION_ID nowait;
187 begin
188 for recinfo in c1 loop
189 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
190 AND (recinfo.ALERT_ID = X_ALERT_ID)
191 AND (recinfo.RESPONSE_SET_ID = X_RESPONSE_SET_ID)
192 AND (recinfo.NAME = X_NAME)
193 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
194 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
195 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
196 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
197 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
198 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE 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_RESPONSE_SET_ID in NUMBER,
213 X_NAME in VARCHAR2,
214 X_DESCRIPTION in VARCHAR2,
215 X_ENABLED_FLAG in VARCHAR2,
216 X_END_DATE_ACTIVE in DATE,
217 X_LAST_UPDATE_DATE in DATE,
218 X_LAST_UPDATED_BY in NUMBER,
219 X_LAST_UPDATE_LOGIN in NUMBER
220 ) is
221 begin
222 update ALR_RESPONSE_SETS set
226 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
223 NAME = X_NAME,
224 DESCRIPTION = X_DESCRIPTION,
225 ENABLED_FLAG = X_ENABLED_FLAG,
227 APPLICATION_ID = X_APPLICATION_ID,
228 ALERT_ID = X_ALERT_ID,
229 RESPONSE_SET_ID = X_RESPONSE_SET_ID,
230 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
231 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
233 where APPLICATION_ID = X_APPLICATION_ID
234 and ALERT_ID = X_ALERT_ID
235 and RESPONSE_SET_ID = X_RESPONSE_SET_ID;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240 end UPDATE_ROW;
241
242 procedure DELETE_ROW (
243 X_APPLICATION_ID in NUMBER,
244 X_ALERT_ID in NUMBER,
245 X_RESPONSE_SET_ID in NUMBER
246 ) is
247 begin
248 delete from ALR_RESPONSE_SETS
249 where APPLICATION_ID = X_APPLICATION_ID
250 and ALERT_ID = X_ALERT_ID
251 and RESPONSE_SET_ID = X_RESPONSE_SET_ID;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256
257 end DELETE_ROW;
258
259 end ALR_RESPONSE_SETS_PKG;