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