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