[Home] [Help]
PACKAGE BODY: APPS.PV_STATUS_NOTIFICATIONS_PKG
Source
1 package body PV_STATUS_NOTIFICATIONS_PKG as
2 /* $Header: pvxtsnfb.pls 120.0 2005/07/11 23:13:09 appldev noship $ */
3
4 procedure INSERT_ROW (
5 px_status_notification_id in out nocopy NUMBER,
6 px_object_version_number in out nocopy NUMBER,
7 p_status_type in VARCHAR2,
8 p_status_code in VARCHAR2,
9 p_enabled_flag in VARCHAR2,
10 p_notify_pt_flag in VARCHAR2,
11 p_notify_cm_flag in VARCHAR2,
12 p_notify_am_flag in VARCHAR2,
13 p_notify_others_flag in VARCHAR2,
14 p_creation_date in DATE,
15 p_created_by in NUMBER,
16 p_last_update_date in DATE,
17 p_last_updated_by in NUMBER,
18 p_last_update_login in NUMBER) IS
19
20 begin
21 px_object_version_number := 1;
22
23 insert into PV_STATUS_NOTIFICATIONS (
24 status_notification_id,
25 object_version_number,
26 status_type,
27 status_code,
28 enabled_flag,
29 notify_pt_flag,
30 notify_cm_flag,
31 notify_am_flag,
32 notify_others_flag,
33 creation_date,
34 created_by,
35 last_update_date,
36 last_updated_by,
37 last_update_login)
38 VALUES
39 (
40 DECODE ( px_status_notification_id,FND_API.g_miss_num,NULL,px_status_notification_id ),
41 DECODE ( px_object_version_number,FND_API.g_miss_num,NULL,px_object_version_number ),
42 DECODE ( p_status_type,FND_API.g_miss_char, NULL,p_status_type ),
43 DECODE ( p_status_code,FND_API.g_miss_char, NULL,p_status_code ),
44 DECODE ( p_enabled_flag,FND_API.g_miss_char, NULL,p_enabled_flag ),
45 DECODE ( p_notify_pt_flag,FND_API.g_miss_char, NULL,p_notify_pt_flag ),
46 DECODE ( p_notify_cm_flag,FND_API.g_miss_char, NULL,p_notify_cm_flag ),
47 DECODE ( p_notify_am_flag,FND_API.g_miss_char, NULL,p_notify_am_flag ),
48 DECODE ( p_notify_others_flag,FND_API.g_miss_char, NULL,p_notify_others_flag ),
49 DECODE ( p_creation_date,FND_API.g_miss_char, NULL,p_creation_date ),
50 DECODE ( p_created_by,FND_API.g_miss_num,NULL,p_created_by ),
51 DECODE ( p_last_update_date,FND_API.g_miss_char, NULL,p_last_update_date ),
52 DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by ),
53 DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login ));
54
55
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 p_status_notification_id in NUMBER,
61 p_object_version_number in NUMBER,
62 p_status_type in VARCHAR2,
63 p_status_code in VARCHAR2,
64 p_enabled_flag in VARCHAR2,
65 p_notify_pt_flag in VARCHAR2,
66 p_notify_cm_flag in VARCHAR2,
67 p_notify_am_flag in VARCHAR2,
68 p_notify_others_flag in VARCHAR2
69 ) IS
70 CURSOR c IS SELECT
71 object_version_number,
72 status_type,
73 status_code,
74 enabled_flag,
75 notify_pt_flag,
76 notify_cm_flag,
77 notify_am_flag,
78 notify_others_flag
79 FROM pv_status_notifications
80 WHERE status_notification_id = p_status_notification_id
81 FOR UPDATE OF status_notification_id NOWAIT;
82
83 recinfo c%ROWTYPE;
84
85 begin
86 open c;
87 fetch c into recinfo;
88 if (c%notfound) then
89 close c;
90 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
91 app_exception.raise_exception;
92 end if;
93 close c;
94 if ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
95 AND (recinfo.STATUS_TYPE = p_STATUS_TYPE)
96 AND (recinfo.STATUS_CODE = p_STATUS_CODE)
97 AND (recinfo.ENABLED_FLAG = p_ENABLED_FLAG)
98 AND ((recinfo.NOTIFY_PT_FLAG = p_NOTIFY_PT_FLAG)
99 OR ((recinfo.NOTIFY_PT_FLAG is null) AND (p_NOTIFY_PT_FLAG is null)))
100 AND ((recinfo.NOTIFY_CM_FLAG = p_NOTIFY_CM_FLAG)
101 OR ((recinfo.NOTIFY_CM_FLAG is null) AND (p_NOTIFY_CM_FLAG is null)))
102 AND ((recinfo.NOTIFY_AM_FLAG = p_NOTIFY_AM_FLAG)
103 OR ((recinfo.NOTIFY_AM_FLAG is null) AND (p_NOTIFY_AM_FLAG is null)))
104 AND ((recinfo.NOTIFY_OTHERS_FLAG = p_NOTIFY_OTHERS_FLAG)
105 OR ((recinfo.NOTIFY_OTHERS_FLAG is null) AND (p_NOTIFY_OTHERS_FLAG is null)))
106 ) then
107 null;
108 else
109 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
110 app_exception.raise_exception;
111 end if;
112 return;
113 end LOCK_ROW;
114
115 procedure UPDATE_ROW (
116 p_status_notification_id in NUMBER,
117 p_object_version_number in NUMBER,
118 p_status_type in VARCHAR2,
119 p_status_code in VARCHAR2,
120 p_enabled_flag in VARCHAR2,
121 p_notify_pt_flag in VARCHAR2,
122 p_notify_cm_flag in VARCHAR2,
123 p_notify_am_flag in VARCHAR2,
124 p_notify_others_flag in VARCHAR2,
125 p_last_update_date in DATE,
126 p_last_updated_by in NUMBER,
127 p_last_update_login in NUMBER
128 ) IS
129 BEGIN
130 UPDATE pv_status_notifications
131 SET
132 object_version_number = DECODE ( p_object_version_number,FND_API.g_miss_num,NULL,p_object_version_number+1 ) ,
133 status_type = DECODE ( p_status_type,FND_API.g_miss_char, NULL,p_status_type ),
134 status_code = DECODE ( p_status_code,FND_API.g_miss_char, NULL,p_status_code ),
135 enabled_flag = DECODE ( p_enabled_flag,FND_API.g_miss_char, NULL,p_enabled_flag ),
136 notify_pt_flag = DECODE ( p_notify_pt_flag,FND_API.g_miss_char, NULL,p_notify_pt_flag ),
137 notify_cm_flag = DECODE ( p_notify_cm_flag,FND_API.g_miss_char, NULL,p_notify_cm_flag ),
138 notify_am_flag = DECODE ( p_notify_am_flag,FND_API.g_miss_char, NULL,p_notify_am_flag ),
139 notify_others_flag = DECODE ( p_notify_others_flag,FND_API.g_miss_char, NULL,p_notify_others_flag ),
140 last_update_date = DECODE ( p_last_update_date,FND_API.g_miss_char, NULL,p_last_update_date ),
141 last_updated_by = DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by ),
142 last_update_login = DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login )
143 WHERE status_notification_id = p_status_notification_id;
144
145 IF (SQL%NOTFOUND) THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148
149 end UPDATE_ROW;
150
151 procedure SEED_UPDATE_ROW (
152 p_status_notification_id in NUMBER,
153 p_object_version_number in NUMBER,
154 p_status_type in VARCHAR2,
155 p_status_code in VARCHAR2,
156 p_last_update_date in DATE,
157 p_last_updated_by in NUMBER,
158 p_last_update_login in NUMBER
159 ) IS
160 BEGIN
161 UPDATE pv_status_notifications
162 SET
163 object_version_number = DECODE ( p_object_version_number,FND_API.g_miss_num,NULL,p_object_version_number+1 ),
164 status_type = DECODE ( p_status_type,FND_API.g_miss_char, NULL,p_status_type ),
165 status_code = DECODE ( p_status_code,FND_API.g_miss_char, NULL,p_status_code ),
166 last_update_date = DECODE ( p_last_update_date,FND_API.g_miss_char, NULL,p_last_update_date ),
167 last_updated_by = DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by ),
168 last_update_login = DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login )
169 WHERE status_notification_id = p_status_notification_id;
170
171 IF (SQL%NOTFOUND) THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 end SEED_UPDATE_ROW;
176
177 procedure UPDATE_ROW_SEED (
178 p_status_notification_id in NUMBER,
179 p_object_version_number in NUMBER,
180 p_status_type in VARCHAR2,
181 p_status_code in VARCHAR2,
182 p_enabled_flag in VARCHAR2,
183 p_notify_pt_flag in VARCHAR2,
184 p_notify_cm_flag in VARCHAR2,
185 p_notify_am_flag in VARCHAR2,
186 p_notify_others_flag in VARCHAR2,
187 p_last_update_date in DATE,
188 p_last_updated_by in NUMBER,
189 p_last_update_login in NUMBER
190 ) IS
191
192 CURSOR c_updated_by
193 IS
194 SELECT last_updated_by
195 FROM pv_status_notifications
196 WHERE status_notification_id = p_status_notification_id;
197
198 l_last_updated_by NUMBER;
199
200
201 BEGIN
202
203 FOR x IN c_updated_by
204 LOOP
205 l_last_updated_by := x.last_updated_by;
206 END LOOP;
207
208 -- Checking if some body updated seeded attribute codes other than SEED,
209 -- If other users updated it, We will not updated enabled_flag .
210 -- Else we will update enabled_flag
211
212 IF ( l_last_updated_by = 1) THEN
213
214 PV_STATUS_NOTIFICATIONS_PKG.UPDATE_ROW(
215 p_status_notification_id => p_status_notification_id,
216 p_object_version_number => p_object_version_number,
217 p_status_type => p_status_type,
218 p_status_code => p_status_code,
219 p_enabled_flag => p_enabled_flag,
220 p_notify_pt_flag => p_notify_pt_flag,
221 p_notify_cm_flag => p_notify_cm_flag,
222 p_notify_am_flag => p_notify_am_flag,
223 p_notify_others_flag => p_notify_others_flag,
224 p_last_update_date => p_last_update_date,
225 p_last_updated_by => p_last_updated_by,
226 p_last_update_login => p_last_update_login);
227
228
229 ELSE
230
231 PV_STATUS_NOTIFICATIONS_PKG.SEED_UPDATE_ROW(
232 p_status_notification_id => p_status_notification_id,
233 p_object_version_number => p_object_version_number,
234 p_status_type => p_status_type,
235 p_status_code => p_status_code,
236 p_last_update_date => p_last_update_date,
237 p_last_updated_by => p_last_updated_by,
238 p_last_update_login => p_last_update_login);
239
240 END IF;
241
242
243 END Update_Row_Seed;
244
245 procedure LOAD_ROW (
246 p_upload_mode in VARCHAR2,
247 p_status_notification_id in NUMBER,
248 p_object_version_number in NUMBER,
249 p_status_type in VARCHAR2,
250 p_status_code in VARCHAR2,
251 p_enabled_flag in VARCHAR2,
252 p_notify_pt_flag in VARCHAR2,
253 p_notify_cm_flag in VARCHAR2,
254 p_notify_am_flag in VARCHAR2,
255 p_notify_others_flag in VARCHAR2,
256 p_owner in VARCHAR2
257 )
258 IS
259
260 l_user_id number := 0;
261 l_obj_verno number;
262 l_dummy_char varchar2(1);
263 l_row_id varchar2(100);
264 l_status_notification_id number := p_status_notification_id;
265
266 cursor c_obj_verno is
267 SELECT object_version_number
268 FROM pv_status_notifications
269 WHERE status_notification_id = p_status_notification_id;
270
271 cursor c_chk_status_exists is
272 SELECT 'x'
273 FROM pv_status_notifications
274 WHERE status_notification_id = p_status_notification_id;
275
276
277 BEGIN
278
279 IF p_OWNER = 'SEED' then
280 l_user_id := 1;
281 ELSE
282 l_user_id := 0;
283 END IF;
284 IF p_upload_mode = 'NLS' THEN
285 null;
286 ELSE
287 OPEN c_chk_status_exists;
288 FETCH c_chk_status_exists INTO l_dummy_char;
289 IF c_chk_status_exists%NOTFOUND THEN
290 CLOSE c_chk_status_exists;
291 l_obj_verno := 1;
292 PV_STATUS_NOTIFICATIONS_PKG.INSERT_ROW(
293 px_status_notification_id => l_status_notification_id,
294 px_object_version_number => l_obj_verno,
295 p_status_type => p_status_type,
296 p_status_code => p_status_code,
297 p_enabled_flag => p_enabled_flag,
298 p_notify_pt_flag => p_notify_pt_flag,
299 p_notify_cm_flag => p_notify_cm_flag,
300 p_notify_am_flag => p_notify_am_flag,
301 p_notify_others_flag => p_notify_others_flag,
302 p_creation_date => SYSDATE,
303 p_created_by => l_user_id,
304 p_last_update_date => SYSDATE,
305 p_last_updated_by => l_user_id,
306 p_last_update_login => 0);
307 ELSE
308 close c_chk_status_exists;
309 open c_obj_verno;
310 fetch c_obj_verno into l_obj_verno;
311 close c_obj_verno;
312
313 PV_STATUS_NOTIFICATIONS_PKG.UPDATE_ROW_SEED(
314 p_status_notification_id => p_status_notification_id,
315 p_object_version_number => l_obj_verno,
316 p_status_type => p_status_type,
317 p_status_code => p_status_code,
318 p_enabled_flag => p_enabled_flag,
319 p_notify_pt_flag => p_notify_pt_flag,
320 p_notify_cm_flag => p_notify_cm_flag,
321 p_notify_am_flag => p_notify_am_flag,
322 p_notify_others_flag => p_notify_others_flag,
323 p_last_update_date => SYSDATE,
324 p_last_updated_by => l_user_id,
325 p_last_update_login => 0);
326
327 END IF;
328 END IF;
329 END LOAD_ROW;
330
331 PROCEDURE DELETE_ROW (
332 p_status_notification_id in NUMBER
333 )
334 IS
335 BEGIN
336
337 DELETE FROM pv_status_notifications
338 WHERE status_notification_id = p_status_notification_id;
339
340 IF (SQL%NOTFOUND) THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343
344 END DELETE_ROW;
345
346
347 end PV_STATUS_NOTIFICATIONS_PKG;