[Home] [Help]
PACKAGE BODY: APPS.AMS_CONTENT_RULES_B_PKG
Source
1 PACKAGE BODY AMS_CONTENT_RULES_B_PKG as
2 /* $Header: amstctrb.pls 120.2 2006/05/30 11:10:24 prageorg noship $ */
3
4 -- ===============================================================
5 -- Start of Comments
6 -- Package name
7 -- AMS_CONTENT_RULES_B_PKG
8 --
9 -- Purpose
10 -- Private api created to Update/insert/Delete general
11 -- and object-specific content rules
12 --
13 -- History
14 -- 21-mar-2002 jieli Created.
15 -- 29-apr-2002 soagrawa Modified last_updated_Date to last_update_date
16 -- 28-mar-2003 soagrawa Added add_language. Bug# 2876033
17 -- 29-May-2006 prageorg Added delivery_mode bug 4920064
18 --
19 --
20 -- NOTE
21 --
22 -- End of Comments
23 -- ===============================================================
24
25
26 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_CONTENT_RULES_B_PKG';
27 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstctrb.pls';
28
29
30
31 -- ========================================================
32 --
33 -- NAME
34 -- Insert_Row
35 --
36 -- HISTORY
37 -- 21-mar-2002 jieli Created
38 -- 11-apr-2002 soagrawa Removed hardcoding of table of content flag and enabled flag
39 -- 29-May-2006 prageorg Added delivery_mode bug 4896511
40 -- ========================================================
41 PROCEDURE Insert_Row(
42 px_content_rule_id IN OUT NOCOPY NUMBER,
43 p_created_by NUMBER,
44 p_creation_date DATE,
45 p_last_updated_by NUMBER,
46 p_last_updated_date DATE,
47 p_last_update_login NUMBER,
48 px_object_version_number IN OUT NOCOPY NUMBER,
49 p_object_type VARCHAR2,
50 p_object_id NUMBER,
51 p_sender VARCHAR2,
52 p_reply_to VARCHAR2,
53 p_cover_letter_id NUMBER,
54 p_table_of_content_flag VARCHAR2,
55 p_trigger_code VARCHAR2,
56 p_enabled_flag VARCHAR2,
57 p_subject VARCHAR2,
58 p_sender_display_name VARCHAR2,
59 -- ADDED BY PRAGEORG ON 5/29/2006
60 p_delivery_mode VARCHAR2)
61
62 IS
63 x_rowid VARCHAR2(30);
64 l_rowid VARCHAR2(20);
65 l_last_update_date DATE;
66
67 cursor C is select ROWID from AMS_content_rules_b
68 where content_rule_ID = px_content_rule_id;
69
70
71 BEGIN
72
73
74 px_object_version_number := 1;
75 AMS_UTILITY_PVT.debug_message('SONALI table handler '||p_last_updated_date);
76
77 l_last_update_date := p_last_updated_date;
78 IF p_last_updated_date IS NULL
79 THEN l_last_update_date := sysdate;
80 END IF;
81
82 AMS_UTILITY_PVT.debug_message('SONALI table handler '||l_last_update_date);
83
84 INSERT INTO AMS_CONTENT_RULES_B(
85 content_rule_id,
86 created_by,
87 creation_date,
88 last_updated_by,
89 last_update_date,
90 last_update_login,
91 object_version_number,
92 object_type,
93 object_id,
94 sender,
95 reply_to,
96 cover_letter_id,
97 table_of_content_flag,
98 trigger_code,
99 enabled_flag,
100 default_flag,
101 sender_display_name,
102 -- ADDED BY PRAGEORG ON 5/29/2006
103 delivery_mode
104 ) VALUES (
105 DECODE( px_content_rule_id, FND_API.g_miss_num, NULL, px_content_rule_id)
106 , DECODE( p_created_by, FND_API.g_miss_num, 1, p_created_by)
107 , DECODE( p_creation_date, FND_API.g_miss_date, sysdate, p_creation_date)
108 , DECODE( p_last_updated_by, FND_API.g_miss_num, 1, p_last_updated_by)
109 , DECODE( p_last_updated_date, FND_API.g_miss_date, sysdate, l_last_update_date)
110 , DECODE( p_last_update_login, FND_API.g_miss_num, 1, p_last_update_login)
111 , DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number)
112 , DECODE( p_object_type, FND_API.g_miss_char, NULL, p_object_type)
113 , DECODE( p_object_id, FND_API.g_miss_num, NULL, p_object_id)
114 , DECODE( p_sender, FND_API.g_miss_char, NULL, p_sender)
115 , DECODE( p_reply_to, FND_API.g_miss_char, NULL, p_reply_to)
116 , DECODE( p_cover_letter_id, FND_API.g_miss_num, NULL, p_cover_letter_id)
117 , DECODE( p_table_of_content_flag, FND_API.g_miss_char, 'N', NVL(p_table_of_content_flag, 'N')) --'N',
118 , DECODE( p_trigger_code, FND_API.g_miss_char, NULL, p_trigger_code)
119 , DECODE( p_enabled_flag, FND_API.g_miss_char, 'Y', NVL(p_enabled_flag, 'Y')) --'Y',
120 , 'N' -- DECODE( p_default_flag, FND_API.g_miss_char, 'Y', p_default_flag) --'Y'
121 , DECODE( p_sender_display_name, FND_API.g_miss_char, NULL, p_sender_display_name)--anchaudh
122 -- ADDED BY PRAGEORG ON 5/29/2006
123 , DECODE( p_delivery_mode, FND_API.g_miss_char, NULL, p_delivery_mode)
124 );
125
126 INSERT INTO AMS_CONTENT_RULES_TL(
127 content_rule_id,
128 last_update_date,
129 last_updated_by,
130 creation_date,
131 created_by,
132 last_update_login,
133 language,
134 source_lang,
135 email_subject
136 ) select
137 px_content_rule_id,
138 sysdate,
139 p_last_updated_by,
140 sysdate,
141 p_created_by,
142 p_last_update_login,
143 L.LANGUAGE_CODE,
144 userenv('LANG'),
145 p_subject
146 from FND_LANGUAGES L
147 where L.INSTALLED_FLAG in ('I', 'B')
148 and not exists
149 (select NULL
150 from ams_content_rules_tl T
151 where T.content_rule_ID = px_content_rule_id
152 and T.LANGUAGE = L.LANGUAGE_CODE);
153
154 open c;
155 fetch c into l_ROWID;
156 if (c%notfound) then
157 close c;
158 raise no_data_found;
159 end if;
160 close c;
161
162 END Insert_Row;
163
164
165 -- ========================================================
166 --
167 -- NAME
168 -- Update_Row
169 --
170 -- HISTORY
171 -- 21-mar-2002 jieli Created
172 -- 29-May-2006 prageorg Added delivery_mode
173 -- ========================================================
174
175
176 PROCEDURE Update_Row(
177 p_content_rule_id NUMBER,
178 p_created_by NUMBER,
179 p_creation_date DATE,
180 p_last_updated_by NUMBER,
181 p_last_updated_date DATE,
182 p_last_update_login NUMBER,
183 p_object_version_number NUMBER,
184 p_object_type VARCHAR2,
185 p_object_id NUMBER,
186 p_sender VARCHAR2,
187 p_reply_to VARCHAR2,
188 p_cover_letter_id NUMBER,
189 p_table_of_content_flag VARCHAR2,
190 p_trigger_code VARCHAR2,
191 p_enabled_flag VARCHAR2,
192 p_subject VARCHAR2,
193 p_sender_display_name VARCHAR2,
194 -- ADDED BY PRAGEORG ON 5/29/2006
195 p_delivery_mode VARCHAR2)
196
197 IS
198 BEGIN
199 Update AMS_CONTENT_RULES_B
200 SET
201 content_rule_id = DECODE( p_content_rule_id, FND_API.g_miss_num, content_rule_id, p_content_rule_id),
202 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
203 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
204 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
205 last_update_date = DECODE( p_last_updated_date, FND_API.g_miss_date, last_update_date, p_last_updated_date),
206 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
207 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
208 object_type = DECODE( p_object_type, FND_API.g_miss_char, object_type, p_object_type),
209 object_id = DECODE( p_object_id, FND_API.g_miss_num, object_id, p_object_id),
210 sender = DECODE( p_sender, FND_API.g_miss_char, sender, p_sender),
211 reply_to = DECODE( p_reply_to, FND_API.g_miss_char, reply_to, p_reply_to),
212 cover_letter_id = DECODE( p_cover_letter_id, FND_API.g_miss_num, cover_letter_id, p_cover_letter_id),
213 table_of_content_flag = DECODE( p_table_of_content_flag, FND_API.g_miss_char, table_of_content_flag, p_table_of_content_flag),
214 trigger_code = DECODE( p_trigger_code, FND_API.g_miss_char, trigger_code, p_trigger_code),
215 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
216 sender_display_name = DECODE( p_sender_display_name, FND_API.g_miss_char, sender_display_name, p_sender_display_name),--anchaudh
217 -- added by prageorg on 5/29/2006
218 delivery_mode = DECODE( p_delivery_mode, FND_API.g_miss_char, delivery_mode, p_delivery_mode)
219
220 WHERE CONTENT_RULE_ID = p_CONTENT_RULE_ID
221 AND object_version_number = p_object_version_number;
222
223 update AMS_CONTENT_RULES_TL
224 set
225 last_update_date=DECODE( p_last_updated_date, FND_API.g_miss_date, last_update_date, p_last_updated_date),
226 last_updated_by=DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
227 creation_date=DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
228 created_by=DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
229 last_update_login=DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
230 source_lang=userenv('LANG'),
231 email_subject=DECODE( p_subject, FND_API.g_miss_char, email_subject, p_subject)
232 where CONTENT_RULE_ID = p_CONTENT_RULE_ID
233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
234
235 END Update_Row;
236
237
238 -- ========================================================
239 --
240 -- NAME
241 -- Delete_Row
242 --
243 -- HISTORY
244 -- 21-mar-2002 jieli Created
245 -- ========================================================
246
247
248 PROCEDURE Delete_Row(
249 p_CONTENT_RULE_ID NUMBER)
250 IS
251 BEGIN
252 DELETE FROM AMS_CONTENT_RULES_B
253 WHERE CONTENT_RULE_ID = p_CONTENT_RULE_ID;
254 If (SQL%NOTFOUND) then
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257 DELETE FROM AMS_CONTENT_RULES_TL
258 WHERE CONTENT_RULE_ID = p_CONTENT_RULE_ID;
259 If (SQL%NOTFOUND) then
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 END IF;
262 END Delete_Row ;
263
264
265
266 -- ========================================================
267 --
268 -- NAME
269 -- Lock_Row
270 --
271 -- HISTORY
272 -- 21-mar-2002 jieli Created
273 -- 29-May-2006 prageorg Added delivery_mode
274 -- ========================================================
275
276
277 PROCEDURE Lock_Row(
278 p_content_rule_id NUMBER,
279 p_created_by NUMBER,
280 p_creation_date DATE,
281 p_last_updated_by NUMBER,
282 p_last_updated_date DATE,
283 p_last_update_login NUMBER,
284 p_object_version_number NUMBER,
285 p_object_type VARCHAR2,
286 p_object_id NUMBER,
287 p_sender VARCHAR2,
288 p_reply_to VARCHAR2,
289 p_cover_letter_id NUMBER,
290 p_table_of_content_flag VARCHAR2,
291 p_trigger_code VARCHAR2,
292 p_enabled_flag VARCHAR2,
293 p_subject VARCHAR2,
294 p_sender_display_name VARCHAR2,
295 -- ADDED BY PRAGEORG ON 5/29/2006
296 p_delivery_mode VARCHAR2)
297
298 IS
299 CURSOR C IS
300 SELECT *
301 FROM AMS_CONTENT_RULES_B
302 WHERE CONTENT_RULE_ID = p_CONTENT_RULE_ID
303 FOR UPDATE of CONTENT_RULE_ID NOWAIT;
304 Recinfo C%ROWTYPE;
305 BEGIN
306 OPEN c;
307 FETCH c INTO Recinfo;
308 If (c%NOTFOUND) then
309 CLOSE c;
310 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
311 APP_EXCEPTION.RAISE_EXCEPTION;
312 END IF;
313 CLOSE C;
314 IF (
315 ( Recinfo.content_rule_id = p_content_rule_id)
316 AND ( ( Recinfo.created_by = p_created_by)
317 OR ( ( Recinfo.created_by IS NULL )
318 AND ( p_created_by IS NULL )))
319 AND ( ( Recinfo.creation_date = p_creation_date)
320 OR ( ( Recinfo.creation_date IS NULL )
321 AND ( p_creation_date IS NULL )))
322 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
323 OR ( ( Recinfo.last_updated_by IS NULL )
324 AND ( p_last_updated_by IS NULL )))
325 AND ( ( Recinfo.last_update_date = p_last_updated_date)
326 OR ( ( Recinfo.last_update_date IS NULL )
327 AND ( p_last_updated_date IS NULL )))
328 AND ( ( Recinfo.last_update_login = p_last_update_login)
329 OR ( ( Recinfo.last_update_login IS NULL )
330 AND ( p_last_update_login IS NULL )))
331 AND ( ( Recinfo.object_version_number = p_object_version_number)
332 OR ( ( Recinfo.object_version_number IS NULL )
333 AND ( p_object_version_number IS NULL )))
334 AND ( ( Recinfo.object_type = p_object_type)
335 OR ( ( Recinfo.object_type IS NULL )
336 AND ( p_object_type IS NULL )))
337 AND ( ( Recinfo.object_id = p_object_id)
338 OR ( ( Recinfo.object_id IS NULL )
339 AND ( p_object_id IS NULL )))
340 AND ( ( Recinfo.sender = p_sender)
341 OR ( ( Recinfo.sender IS NULL )
342 AND ( p_sender IS NULL )))
343 AND ( ( Recinfo.reply_to = p_reply_to)
344 OR ( ( Recinfo.reply_to IS NULL )
345 AND ( p_reply_to IS NULL )))
346 AND ( ( Recinfo.cover_letter_id = p_cover_letter_id)
347 OR ( ( Recinfo.cover_letter_id IS NULL )
348 AND ( p_cover_letter_id IS NULL )))
349 AND ( ( Recinfo.table_of_content_flag = p_table_of_content_flag)
350 OR ( ( Recinfo.table_of_content_flag IS NULL )
351 AND ( p_table_of_content_flag IS NULL )))
352 AND ( ( Recinfo.trigger_code = p_trigger_code)
353 OR ( ( Recinfo.trigger_code IS NULL )
354 AND ( p_trigger_code IS NULL )))
355 AND ( ( Recinfo.enabled_flag = p_enabled_flag)
356 OR ( ( Recinfo.enabled_flag IS NULL )
357 AND ( p_enabled_flag IS NULL )))
358 AND ( ( Recinfo.sender_display_name = p_sender_display_name)
359 OR ( ( Recinfo.sender_display_name IS NULL )
360 AND ( p_sender_display_name IS NULL )))--anchaudh
361 AND ( ( Recinfo.delivery_mode = p_delivery_mode)
362 OR ( ( Recinfo.delivery_mode IS NULL )
363 AND ( p_delivery_mode IS NULL )))--prageorg
364 ) THEN
365 RETURN;
366 ELSE
367 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
368 APP_EXCEPTION.RAISE_EXCEPTION;
369 END IF;
370 END Lock_Row;
371
372
373 procedure ADD_LANGUAGE
374 is
375 begin
376 delete from AMS_CONTENT_RULES_TL T
377 where not exists
378 (select NULL
379 from AMS_CONTENT_RULES_B B
380 where B.content_rule_id = T.content_rule_id
381 );
382
386 B.email_subject
383 update AMS_CONTENT_RULES_TL T set (
384 email_subject
385 ) = (select
387 from AMS_CONTENT_RULES_tl B
388 where B.content_rule_id = T.content_rule_id
389 and B.LANGUAGE = T.SOURCE_LANG)
390 where (
391 T.content_rule_id,
392 T.LANGUAGE
393 ) in (select
394 SUBT.content_rule_id,
395 SUBT.LANGUAGE
396 from AMS_CONTENT_RULES_TL SUBB, AMS_CONTENT_RULES_TL SUBT
397 where SUBB.content_rule_id = SUBT.content_rule_id
398 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
399 and (SUBB.email_subject <> SUBT.email_subject
400 ));
401
402 insert into AMS_CONTENT_RULES_TL (
403 CONTENT_RULE_ID,
404 CREATED_BY,
405 CREATION_DATE,
406 EMAIL_SUBJECT,
407 LANGUAGE,
408 LAST_UPDATE_DATE,
409 LAST_UPDATE_LOGIN,
410 LAST_UPDATED_BY,
411 SECURITY_GROUP_ID,
412 SOURCE_LANG
413 ) select
414 B.CONTENT_RULE_ID,
415 B.CREATED_BY,
419 B.LAST_UPDATE_DATE,
416 B.CREATION_DATE,
417 B.EMAIL_SUBJECT,
418 L.LANGUAGE_CODE,
420 B.LAST_UPDATE_LOGIN,
421 B.LAST_UPDATED_BY,
422 B.SECURITY_GROUP_ID,
423 B.SOURCE_LANG
424 from AMS_CONTENT_RULES_TL B, FND_LANGUAGES L
425 where L.INSTALLED_FLAG in ('I', 'B')
426 and B.LANGUAGE = userenv('LANG')
427 and not exists
428 (select NULL
429 from AMS_CONTENT_RULES_TL T
430 where T.content_rule_id = B.content_rule_id
431 and T.LANGUAGE = L.LANGUAGE_CODE);
432 end ADD_LANGUAGE;
433
434
435 END AMS_CONTENT_RULES_B_PKG;