[Home] [Help]
PACKAGE BODY: APPS.PV_GE_NOTIF_RULES_PKG
Source
1 PACKAGE BODY PV_Ge_Notif_Rules_PKG as
2 /* $Header: pvxtgnrb.pls 115.5 2004/03/12 01:29:45 pukken ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Ge_Notif_Rules_PKG
7 -- Purpose
8 --
9 -- History
10 -- 15 Nov 2002 anubhavk created
11 -- 19 Nov 2002 anubhavk Updated - For NOCOPY by running nocopy.sh
12 --
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Ge_Notif_Rules_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtgnrb.pls';
24
25
26
27
28 -- ========================================================
29 --
30 -- NAME
31 -- Insert_Row
32 --
33 -- PURPOSE
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 -- ========================================================
40 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
41 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
42 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
43
44 PROCEDURE Insert_Row(
45 px_notif_rule_id IN OUT NOCOPY NUMBER,
46 px_object_version_number IN OUT NOCOPY NUMBER,
47 p_arc_notif_for_entity_code VARCHAR2,
48 p_notif_for_entity_id NUMBER,
49 p_wf_item_type_code VARCHAR2,
50 p_notif_type_code VARCHAR2,
51 p_active_flag VARCHAR2,
52 p_repeat_freq_unit VARCHAR2,
53 p_repeat_freq_value NUMBER,
54 p_send_notif_before_unit VARCHAR2,
55 p_send_notif_before_value NUMBER,
56 p_send_notif_after_unit VARCHAR2,
57 p_send_notif_after_value NUMBER,
58 p_repeat_until_unit VARCHAR2,
59 p_repeat_until_value NUMBER,
60 p_created_by NUMBER,
61 p_creation_date DATE,
62 p_last_updated_by NUMBER,
63 p_last_update_date DATE,
64 p_last_update_login NUMBER,
65 p_notif_name VARCHAR2,
66 p_notif_content VARCHAR2,
67 p_notif_desc VARCHAR2
68 )
69
70 IS
71 x_rowid VARCHAR2(30);
72
73
74 BEGIN
75
76
77 px_object_version_number := nvl(px_object_version_number, 1);
78
79
80 INSERT INTO pv_ge_notif_rules_b(
81 notif_rule_id,
82 object_version_number,
83 arc_notif_for_entity_code,
84 notif_for_entity_id,
85 wf_item_type_code,
86 notif_type_code,
87 active_flag,
88 repeat_freq_unit,
89 repeat_freq_value,
90 send_notif_before_unit,
91 send_notif_before_value,
92 send_notif_after_unit,
93 send_notif_after_value,
94 repeat_until_unit,
95 repeat_until_value,
96 created_by,
97 creation_date,
98 last_updated_by,
99 last_update_date,
100 last_update_login
101 ) VALUES (
102 DECODE( px_notif_rule_id, FND_API.G_MISS_NUM, NULL, px_notif_rule_id),
103 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
104 DECODE( p_arc_notif_for_entity_code, FND_API.g_miss_char, NULL, p_arc_notif_for_entity_code),
105 DECODE( p_notif_for_entity_id, FND_API.G_MISS_NUM, NULL, p_notif_for_entity_id),
106 DECODE( p_wf_item_type_code, FND_API.g_miss_char, NULL, p_wf_item_type_code),
107 DECODE( p_notif_type_code, FND_API.g_miss_char, NULL, p_notif_type_code),
108 DECODE( p_active_flag, FND_API.g_miss_char, NULL, p_active_flag),
109 DECODE( p_repeat_freq_unit, FND_API.g_miss_char, NULL, p_repeat_freq_unit),
110 DECODE( p_repeat_freq_value, FND_API.G_MISS_NUM, NULL, p_repeat_freq_value),
111 DECODE( p_send_notif_before_unit, FND_API.g_miss_char, NULL, p_send_notif_before_unit),
112 DECODE( p_send_notif_before_value, FND_API.G_MISS_NUM, NULL, p_send_notif_before_value),
113 DECODE( p_send_notif_after_unit, FND_API.g_miss_char, NULL, p_send_notif_after_unit),
114 DECODE( p_send_notif_after_value, FND_API.G_MISS_NUM, NULL, p_send_notif_after_value),
115 DECODE( p_repeat_until_unit, FND_API.g_miss_char, NULL, p_repeat_until_unit),
116 DECODE( p_repeat_until_value, FND_API.G_MISS_NUM, NULL, p_repeat_until_value),
117 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
118 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
119 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
120 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
121 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login));
122
123 INSERT INTO pv_ge_notif_rules_tl(
124 notif_rule_id ,
125 language ,
126 last_update_date ,
127 last_updated_by ,
128 creation_date ,
129 created_by ,
130 last_update_login ,
131 source_lang ,
132 notif_name,
133 notif_content,
134 notif_desc
135 )
136 SELECT
137 DECODE( px_notif_rule_id, FND_API.G_MISS_NUM, NULL, px_notif_rule_id),
138 l.language_code,
139 DECODE( p_last_update_date, NULL, SYSDATE, p_last_update_date),
140 DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
141 DECODE( p_creation_date, NULL, SYSDATE, p_creation_date),
142 DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
143 DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
144 USERENV('LANG'),
145 DECODE( p_notif_name , FND_API.G_MISS_CHAR, NULL, p_notif_name),
146 DECODE( p_notif_content , FND_API.G_MISS_CHAR, NULL, p_notif_content),
147 DECODE( p_notif_desc , FND_API.G_MISS_CHAR, NULL, p_notif_desc)
148 FROM fnd_languages l
149 WHERE l.installed_flag IN ('I','B')
150 AND NOT EXISTS(SELECT NULL FROM pv_ge_notif_rules_tl t
151 WHERE t.notif_rule_id = DECODE( px_notif_rule_id, FND_API.G_MISS_NUM, NULL, px_notif_rule_id)
152 AND t.language = l.language_code);
153 END Insert_Row;
154
155
156
157
158 -- ========================================================
159 --
160 -- NAME
161 -- Update_Row
162 --
163 -- PURPOSE
164 --
165 -- NOTES
166 --
167 -- HISTORY
168 --
169 -- ========================================================
170 PROCEDURE Update_Row(
171 p_notif_rule_id NUMBER,
172 p_object_version_number IN NUMBER,
173 p_arc_notif_for_entity_code VARCHAR2,
174 p_notif_for_entity_id NUMBER,
175 p_wf_item_type_code VARCHAR2,
176 p_notif_type_code VARCHAR2,
177 p_active_flag VARCHAR2,
178 p_repeat_freq_unit VARCHAR2,
179 p_repeat_freq_value NUMBER,
180 p_send_notif_before_unit VARCHAR2,
181 p_send_notif_before_value NUMBER,
182 p_send_notif_after_unit VARCHAR2,
183 p_send_notif_after_value NUMBER,
184 p_repeat_until_unit VARCHAR2,
185 p_repeat_until_value NUMBER,
186 p_last_updated_by NUMBER,
187 p_last_update_date DATE,
188 p_last_update_login NUMBER,
189 p_notif_name VARCHAR2,
190 p_notif_content VARCHAR2,
191 p_notif_desc VARCHAR2
192 )
193
194 IS
195 BEGIN
196 Update pv_ge_notif_rules_b
197 SET
198 notif_rule_id = DECODE( p_notif_rule_id, null, notif_rule_id, FND_API.G_MISS_NUM, null, p_notif_rule_id),
199 object_version_number = nvl(p_object_version_number,0) + 1 ,
200 arc_notif_for_entity_code = DECODE( p_arc_notif_for_entity_code, null, arc_notif_for_entity_code, FND_API.g_miss_char, null, p_arc_notif_for_entity_code),
201 notif_for_entity_id = DECODE( p_notif_for_entity_id, null, notif_for_entity_id, FND_API.G_MISS_NUM, null, p_notif_for_entity_id),
202 wf_item_type_code = DECODE( p_wf_item_type_code, null, wf_item_type_code, FND_API.g_miss_char, null, p_wf_item_type_code),
203 notif_type_code = DECODE( p_notif_type_code, null, notif_type_code, FND_API.g_miss_char, null, p_notif_type_code),
204 active_flag = DECODE( p_active_flag, null, active_flag, FND_API.g_miss_char, null, p_active_flag),
205 repeat_freq_unit = DECODE( p_repeat_freq_unit, null, repeat_freq_unit, FND_API.g_miss_char, null, p_repeat_freq_unit),
206 repeat_freq_value = DECODE( p_repeat_freq_value, null, repeat_freq_value, FND_API.G_MISS_NUM, null, p_repeat_freq_value),
207 send_notif_before_unit = DECODE( p_send_notif_before_unit, null, send_notif_before_unit, FND_API.g_miss_char, null, p_send_notif_before_unit),
208 send_notif_before_value = DECODE( p_send_notif_before_value, null, send_notif_before_value, FND_API.G_MISS_NUM, null, p_send_notif_before_value),
209 send_notif_after_unit = DECODE( p_send_notif_after_unit, null, send_notif_after_unit, FND_API.g_miss_char, null, p_send_notif_after_unit),
210 send_notif_after_value = DECODE( p_send_notif_after_value, null, send_notif_after_value, FND_API.G_MISS_NUM, null, p_send_notif_after_value),
211 repeat_until_unit = DECODE( p_repeat_until_unit, null, repeat_until_unit, FND_API.g_miss_char, null, p_repeat_until_unit),
212 repeat_until_value = DECODE( p_repeat_until_value, null, repeat_until_value, FND_API.G_MISS_NUM, null, p_repeat_until_value),
213 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
214 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
215 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
216 WHERE notif_rule_id = p_notif_rule_id;
217 --AND object_version_number = p_object_version_number;
218
219 UPDATE pv_ge_notif_rules_tl
220 set notif_name = DECODE( p_notif_name, null, notif_name, FND_API.g_miss_char, null, p_notif_name),
221 notif_content = DECODE( p_notif_content, null, notif_content, FND_API.g_miss_char, null, p_notif_content),
222 notif_desc = DECODE( p_notif_desc, null, notif_desc, FND_API.g_miss_char, null, p_notif_desc),
223 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
224 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
225 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
226 source_lang = USERENV('LANG')
227 WHERE notif_rule_id = p_notif_rule_id
228 AND USERENV('LANG') IN (language, source_lang);
229
230 IF (SQL%NOTFOUND) THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233
234
235 END Update_Row;
236
237
238
239
240 -- ========================================================
241 --
242 -- NAME
243 -- Delete_Row
244 --
245 -- PURPOSE
246 --
247 -- NOTES
248 --
249 -- HISTORY
250 --
251 -- ========================================================
252 PROCEDURE Delete_Row(
253 p_notif_rule_id NUMBER,
254 p_object_version_number NUMBER)
255 IS
256 BEGIN
257 DELETE FROM pv_ge_notif_rules_b
258 WHERE notif_rule_id = p_notif_rule_id
259 AND object_version_number = p_object_version_number;
260 If (SQL%NOTFOUND) then
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 End If;
263 END Delete_Row ;
264
265
266
267
268
269 -- ========================================================
270 --
271 -- NAME
272 -- Lock_Row
273 --
274 -- PURPOSE
275 --
276 -- NOTES
277 --
278 -- HISTORY
279 --
280 -- ========================================================
281 PROCEDURE Lock_Row(
282 p_notif_rule_id NUMBER,
283 p_object_version_number NUMBER)
284 IS
285 CURSOR C IS
286 SELECT *
287 FROM pv_ge_notif_rules_b
288 WHERE notif_rule_id = p_notif_rule_id
289 AND object_version_number = p_object_version_number
290 FOR UPDATE OF notif_rule_id NOWAIT;
291 Recinfo C%ROWTYPE;
292 BEGIN
293
294 OPEN c;
295 FETCH c INTO Recinfo;
296 IF (c%NOTFOUND) THEN
297 CLOSE c;
298 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
299 RAISE FND_API.g_exc_error;
300 END IF;
301 CLOSE c;
302 END Lock_Row;
303
304 -- ==============================================================================
305 -- Start of Comments
306 -- ==============================================================================
307 -- API Name
308 -- add_language
309 -- Type
310 -- Private
311 -- History
312 --
313 -- NOTE
314 --
315 -- End of Comments
316 -- ===============================================================
317
318
319
320 PROCEDURE Add_Language
321 IS
322 BEGIN
323 delete from pv_ge_notif_rules_tl T
324 where not exists
325 (select NULL
326 from pv_ge_notif_rules_b b
327 where b.notif_rule_id = t.notif_rule_id
328 );
329
330 update pv_ge_notif_rules_tl t set (
331 --notif_rule_name
332 --, description
333 notif_name,
334 notif_content,
335 notif_desc
336 ) = (select
337 --b.notif_rule_name
338 --, b.description
339 b.notif_name,
340 b.notif_content,
341 b.notif_desc
342 from pv_ge_notif_rules_tl b
343 where b.notif_rule_id = t.notif_rule_id
344 and b.language = t.source_lang)
345 where (
346 t.notif_rule_id,
347 t.language
348 ) in (select
349 subt.notif_rule_id,
350 subt.language
351 from pv_ge_notif_rules_tl subb, pv_ge_notif_rules_tl subt
352 where subb.notif_rule_id = subt.notif_rule_id
353 and subb.language = subt.source_lang
354 and (subb.notif_name <> subt.notif_name
355 or subb.notif_content <> subt.notif_content
356 or subb.notif_desc <> subt.notif_desc
357 or (subb.notif_desc is null and subt.notif_desc is not null)
358 or (subb.notif_desc is not null and subt.notif_desc is null)
359 --and (subb.notif_rule_name <> subt.notif_rule_name
360 --or subb.description <> subt.description
361 --or (subb.description is null and subt.description is not null)
362 --or (subb.description is not null and subt.description is null)
363 ));
364
365 insert into pv_ge_notif_rules_tl (
366 notif_rule_id,
367 creation_date,
368 created_by,
369 last_update_date,
370 last_updated_by,
371 last_update_login,
372 notif_name,
373 notif_content,
374 notif_desc,
375 --notif_rule_name,
376 --description,
377 language,
378 source_lang
379 ) select
380 b.notif_rule_id,
381 b.creation_date,
382 b.created_by,
383 b.last_update_date,
384 b.last_updated_by,
385 b.last_update_login,
386 b.notif_name,
387 b.notif_content,
388 b.notif_desc,
389 --b.notif_rule_name,
390 --b.description,
391 l.language_code,
392 b.source_lang
393 from pv_ge_notif_rules_tl b, fnd_languages l
394 where l.installed_flag in ('I', 'B')
395 and b.language = userenv('lang')
396 and not exists
397 (select null
398 from pv_ge_notif_rules_tl t
399 where t.notif_rule_id = b.notif_rule_id
400 and t.language = l.language_code);
401 END ADD_LANGUAGE;
402
403
404
405
406 END PV_Ge_Notif_Rules_PKG;