[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_CONT_RESTRICTIONS_PKG
Source
1 PACKAGE BODY AMS_LIST_CONT_RESTRICTIONS_PKG as
2 /* $Header: amstascb.pls 120.0 2005/05/31 21:00:56 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_LIST_CONT_RESTRICTIONS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_LIST_CONT_RESTRICTIONS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstascb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE Insert_Row(
42 px_list_cont_restrictions_id IN OUT NOCOPY NUMBER,
43 p_list_header_id NUMBER,
44 p_last_update_date DATE,
45 p_last_updated_by NUMBER,
46 p_creation_date DATE,
47 p_created_by NUMBER,
48 p_last_update_login NUMBER,
49 px_object_version_number IN OUT NOCOPY NUMBER,
50 p_do_not_contact_flag VARCHAR2,
51 p_media_id NUMBER,
52 p_list_used_by VARCHAR2,
53 p_list_used_by_id NUMBER
54 )
55
56 IS
57 x_rowid VARCHAR2(30);
58
59
60 BEGIN
61
62
63 px_object_version_number := 1;
64
65
66 INSERT INTO AMS_LIST_CONT_RESTRICTIONS(
67 list_contact_restrictions_id,
68 list_header_id,
69 last_update_date,
70 last_updated_by,
71 creation_date,
72 created_by,
73 last_update_login,
74 object_version_number,
75 do_not_contact_flag,
76 media_id,
77 list_used_by,
78 list_used_by_id
79 ) VALUES (
80 DECODE( px_list_cont_restrictions_id, FND_API.g_miss_num, NULL, px_list_cont_restrictions_id),
81 DECODE( p_list_header_id, FND_API.g_miss_num, NULL, p_list_header_id),
82 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
83 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
84 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
85 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
86 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
87 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
88 DECODE( p_do_not_contact_flag, FND_API.g_miss_char, NULL, p_do_not_contact_flag),
89 DECODE( p_media_id, FND_API.g_miss_num, NULL, p_media_id),
90 DECODE( p_list_used_by, FND_API.g_miss_char, NULL, p_list_used_by),
91 DECODE( p_list_used_by_id, FND_API.g_miss_num, NULL, p_list_used_by_id));
92 END Insert_Row;
93
94
95 ----------------------------------------------------------
96 ---- MEDIA ----
97 ----------------------------------------------------------
98
99 -- ========================================================
100 --
101 -- NAME
102 -- createUpdateBody
103 --
104 -- PURPOSE
105 --
106 -- NOTES
107 --
108 -- HISTORY
109 --
110 -- ========================================================
111 PROCEDURE Update_Row(
112 p_list_cont_restrictions_id NUMBER,
113 p_list_header_id NUMBER,
114 p_last_update_date DATE,
115 p_last_updated_by NUMBER,
116 p_creation_date DATE,
117 p_created_by NUMBER,
118 p_last_update_login NUMBER,
119 p_object_version_number NUMBER,
120 p_do_not_contact_flag VARCHAR2,
121 p_media_id NUMBER,
122 p_list_used_by VARCHAR2,
123 p_list_used_by_id NUMBER)
124
125 IS
126 BEGIN
127
128 Update AMS_LIST_CONT_RESTRICTIONS
129 SET
130 list_contact_restrictions_id = DECODE( p_list_cont_restrictions_id, FND_API.g_miss_num, list_contact_restrictions_id, p_list_cont_restrictions_id),
131 list_header_id = DECODE( p_list_header_id, FND_API.g_miss_num, list_header_id, p_list_header_id),
132 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
133 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
134 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
135 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
136 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
137 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
138 do_not_contact_flag = DECODE( p_do_not_contact_flag, FND_API.g_miss_char, do_not_contact_flag, p_do_not_contact_flag),
139 media_id = DECODE( p_media_id, FND_API.g_miss_num, media_id, p_media_id),
140 list_used_by = DECODE( p_list_used_by, FND_API.g_miss_char, list_used_by, p_list_used_by),
141 list_used_by_id = DECODE( p_list_used_by_id, FND_API.g_miss_num, list_used_by_id, p_list_used_by_id)
142 WHERE LIST_CONTACT_RESTRICTIONs_ID = p_LIST_CONT_RESTRICTIONs_ID
143 AND object_version_number = p_object_version_number;
144
145 IF (SQL%NOTFOUND) THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148 END Update_Row;
149
150
151 ----------------------------------------------------------
152 ---- MEDIA ----
153 ----------------------------------------------------------
154
155 -- ========================================================
156 --
157 -- NAME
158 -- createDeleteBody
159 --
160 -- PURPOSE
161 --
162 -- NOTES
163 --
164 -- HISTORY
165 --
166 -- ========================================================
167 PROCEDURE Delete_Row(
168 p_LIST_CONT_RESTRICTIONs_ID NUMBER)
169 IS
170 BEGIN
171 DELETE FROM AMS_LIST_CONT_RESTRICTIONS
172 WHERE LIST_CONTACT_RESTRICTIONs_ID = p_LIST_CONT_RESTRICTIONs_ID;
173 If (SQL%NOTFOUND) then
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 End If;
176 END Delete_Row ;
177
178
179
180 ----------------------------------------------------------
181 ---- MEDIA ----
182 ----------------------------------------------------------
183
184 -- ========================================================
185 --
186 -- NAME
187 -- createLockBody
188 --
189 -- PURPOSE
190 --
191 -- NOTES
192 --
193 -- HISTORY
194 --
195 -- ========================================================
196 PROCEDURE Lock_Row(
197 p_list_cont_restrictions_id NUMBER,
198 p_list_header_id NUMBER,
199 p_last_update_date DATE,
200 p_last_updated_by NUMBER,
201 p_creation_date DATE,
202 p_created_by NUMBER,
203 p_last_update_login NUMBER,
204 p_object_version_number NUMBER,
205 p_do_not_contact_flag VARCHAR2,
206 p_media_id NUMBER,
207 p_list_used_by VARCHAR2,
208 p_list_used_by_id NUMBER)
209
210 IS
211 CURSOR C IS
212 SELECT *
213 FROM AMS_LIST_CONT_RESTRICTIONS
214 WHERE LIST_CONTACT_RESTRICTIONs_ID = p_LIST_CONT_RESTRICTIONs_ID
215 FOR UPDATE of LIST_CONTACT_RESTRICTIONs_ID NOWAIT;
216 Recinfo C%ROWTYPE;
217 BEGIN
218 OPEN c;
219 FETCH c INTO Recinfo;
220 If (c%NOTFOUND) then
221 CLOSE c;
222 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
223 APP_EXCEPTION.RAISE_EXCEPTION;
224 END IF;
225 CLOSE C;
226 IF (
227 ( Recinfo.list_contact_restrictions_id = p_list_cont_restrictions_id)
228 AND ( ( Recinfo.list_header_id = p_list_header_id)
229 OR ( ( Recinfo.list_header_id IS NULL )
230 AND ( p_list_header_id IS NULL )))
231 AND ( ( Recinfo.last_update_date = p_last_update_date)
232 OR ( ( Recinfo.last_update_date IS NULL )
233 AND ( p_last_update_date IS NULL )))
234 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
235 OR ( ( Recinfo.last_updated_by IS NULL )
236 AND ( p_last_updated_by IS NULL )))
237 AND ( ( Recinfo.creation_date = p_creation_date)
238 OR ( ( Recinfo.creation_date IS NULL )
239 AND ( p_creation_date IS NULL )))
240 AND ( ( Recinfo.created_by = p_created_by)
241 OR ( ( Recinfo.created_by IS NULL )
242 AND ( p_created_by IS NULL )))
243 AND ( ( Recinfo.last_update_login = p_last_update_login)
244 OR ( ( Recinfo.last_update_login IS NULL )
245 AND ( p_last_update_login IS NULL )))
246 AND ( ( Recinfo.object_version_number = p_object_version_number)
247 OR ( ( Recinfo.object_version_number IS NULL )
248 AND ( p_object_version_number IS NULL )))
249 AND ( ( Recinfo.do_not_contact_flag = p_do_not_contact_flag)
250 OR ( ( Recinfo.do_not_contact_flag IS NULL )
251 AND ( p_do_not_contact_flag IS NULL )))
252 AND ( ( Recinfo.media_id = p_media_id)
253 OR ( ( Recinfo.media_id IS NULL )
254 AND ( p_media_id IS NULL )))
255 AND ( ( Recinfo.list_used_by = p_list_used_by)
256 OR ( ( Recinfo.list_used_by IS NULL )
257 AND ( p_list_used_by IS NULL )))
258 AND ( ( Recinfo.list_used_by_id = p_list_used_by_id)
259 OR ( ( Recinfo.list_used_by_id IS NULL )
260 AND ( p_list_used_by_id IS NULL )))
261 ) THEN
262 RETURN;
263 ELSE
264 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 END Lock_Row;
268
269 PROCEDURE LOAD_ROW(
270 p_owner VARCHAR2,
271 p_list_cont_restrictions_id NUMBER,
272 p_list_header_id NUMBER,
273 p_last_update_date DATE,
274 p_last_updated_by NUMBER,
275 p_creation_date DATE,
276 p_created_by NUMBER,
277 p_last_update_login NUMBER,
278 p_object_version_number NUMBER,
279 p_do_not_contact_flag VARCHAR2,
280 p_media_id NUMBER,
281 p_list_used_by VARCHAR2,
282 p_list_used_by_id NUMBER,
283 p_custom_mode VARCHAR2
284 ) is
285 l_dummy_char varchar2(1);
286 x_return_status varchar2(1);
287 l_row_id varchar2(100);
288 l_user_id number;
289 l_last_updated_by number;
290 l_obj_verno NUMBER;
291
292
293 l_object_version_number NUMBER := p_object_version_number ;
294 l_list_cont_restrictions_id NUMBER := p_list_cont_restrictions_id ;
295 cursor c_chk_col_exists is
296 select 'x'
297 from ams_list_cont_restrictions
298 where list_contact_restrictions_id = p_list_cont_restrictions_id;
299
300 CURSOR c_obj_verno IS
301 SELECT object_version_number, last_updated_by
302 FROM ams_list_cont_restrictions
303 where list_contact_restrictions_id = p_list_cont_restrictions_id;
304
305
306 begin
307 if p_OWNER = 'SEED' then
308 l_user_id := 1;
309 elsif p_OWNER = 'ORACLE' then
310 l_user_id := 2;
311 elsif p_OWNER = 'SYSADMIN' THEN
312 l_user_id := 0;
313 end if;
314
315 open c_chk_col_exists;
316 fetch c_chk_col_exists into l_dummy_char;
317 if c_chk_col_exists%notfound then
318 close c_chk_col_exists;
319 Insert_Row(
320 px_list_cont_restrictions_id => l_list_cont_restrictions_id ,
321 p_list_header_id => p_list_header_id ,
322 p_last_update_date => p_last_update_date ,
323 p_last_updated_by => p_last_updated_by ,
324 p_creation_date => p_creation_date ,
325 p_created_by => p_created_by ,
326 p_last_update_login => p_last_update_login ,
327 px_object_version_number => l_object_version_number ,
328 p_do_not_contact_flag => p_do_not_contact_flag ,
329 p_media_id => p_media_id ,
330 p_list_used_by => p_list_used_by ,
331 p_list_used_by_id => p_list_used_by_id );
332
333
334 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337 else
338 close c_chk_col_exists;
339
340 OPEN c_obj_verno;
341 FETCH c_obj_verno INTO l_obj_verno ,l_last_updated_by;
342 CLOSE c_obj_verno;
343
344 if (l_last_updated_by in (1,2,0) OR
345 NVL(p_custom_mode,'PRESERVE')='FORCE') THEN
346
347
348
349 Update_Row(
350 p_list_cont_restrictions_id => p_list_cont_restrictions_id ,
351 p_list_header_id => p_list_header_id ,
355 p_created_by => p_created_by ,
352 p_last_update_date => p_last_update_date ,
353 p_last_updated_by => p_last_updated_by ,
354 p_creation_date => p_creation_date ,
356 p_last_update_login => p_last_update_login ,
357 p_object_version_number => l_obj_verno ,
358 p_do_not_contact_flag => p_do_not_contact_flag ,
359 p_media_id => p_media_id ,
360 p_list_used_by => p_list_used_by ,
361 p_list_used_by_id => p_list_used_by_id );
362 --
363
364 end if;
365 end if;
366 end ;
367
368
369 END AMS_LIST_CONT_RESTRICTIONS_PKG;