[Home] [Help]
PACKAGE BODY: APPS.AMS_CTDS_PKG
Source
1 PACKAGE BODY AMS_CTDS_PKG as
2 /* $Header: amstctdb.pls 120.1 2005/06/03 12:41:22 appldev $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_CTDS_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_CTDS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstctdb.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 PROCEDURE Insert_Row(
38 px_ctd_id IN OUT NOCOPY NUMBER,
39 p_action_id NUMBER,
40 p_forward_url VARCHAR2,
41 p_track_url VARCHAR2,
42 p_activity_product_id NUMBER,
43 p_activity_offer_id NUMBER,
44 px_object_version_number IN OUT NOCOPY NUMBER,
45 p_last_update_date DATE,
46 p_last_updated_by NUMBER,
47 p_creation_date DATE,
48 p_created_by NUMBER,
49 p_last_update_login NUMBER,
50 p_security_group_id NUMBER)
51
52 IS
53 x_rowid VARCHAR2(30);
54
55
56 BEGIN
57
58
59 px_object_version_number := 1;
60
61
62 INSERT INTO AMS_CTDS(
63 ctd_id,
64 action_id,
65 forward_url,
66 track_url,
67 activity_product_id,
68 activity_offer_id,
69 object_version_number,
70 last_update_date,
71 last_updated_by,
72 creation_date,
73 created_by,
74 last_update_login,
75 security_group_id
76 ) VALUES (
77 DECODE( px_ctd_id, FND_API.g_miss_num, NULL, px_ctd_id),
78 DECODE( p_action_id, FND_API.g_miss_num, NULL, p_action_id),
79 DECODE( p_forward_url, FND_API.g_miss_char, NULL, p_forward_url),
80 DECODE( p_track_url, FND_API.g_miss_char, NULL, p_track_url),
81 DECODE( p_activity_product_id, FND_API.g_miss_num, NULL, p_activity_product_id),
82 DECODE( p_activity_offer_id, FND_API.g_miss_num, NULL, p_activity_offer_id),
83 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
84 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
85 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
86 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
87 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
88 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
89 DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id));
90 END Insert_Row;
91
92
93 ----------------------------------------------------------
94 ---- MEDIA ----
95 ----------------------------------------------------------
96
97 -- ========================================================
98 --
99 -- NAME
100 -- createUpdateBody
101 --
102 -- PURPOSE
103 --
104 -- NOTES
105 --
106 -- HISTORY
107 --
108 -- ========================================================
109 PROCEDURE Update_Row(
110 p_ctd_id NUMBER,
111 p_action_id NUMBER,
112 p_forward_url VARCHAR2,
113 p_track_url VARCHAR2,
114 p_activity_product_id NUMBER,
115 p_activity_offer_id NUMBER,
116 p_object_version_number NUMBER,
117 p_last_update_date DATE,
118 p_last_updated_by NUMBER,
119 p_creation_date DATE,
120 p_created_by NUMBER,
121 p_last_update_login NUMBER,
122 p_security_group_id NUMBER)
123
124 IS
125 BEGIN
126 Update AMS_CTDS
127 SET
128 ctd_id = DECODE( p_ctd_id, FND_API.g_miss_num, ctd_id, p_ctd_id),
129 action_id = DECODE( p_action_id, FND_API.g_miss_num, action_id, p_action_id),
130 forward_url = DECODE( p_forward_url, FND_API.g_miss_char, forward_url, p_forward_url),
131 track_url = DECODE( p_track_url, FND_API.g_miss_char, track_url, p_track_url),
132 activity_product_id = DECODE( p_activity_product_id, FND_API.g_miss_num, activity_product_id, p_activity_product_id),
133 activity_offer_id = DECODE( p_activity_offer_id, FND_API.g_miss_num, activity_offer_id, p_activity_offer_id),
134 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1),
135 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
136 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
137 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
138 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
139 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
140 security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id)
141 WHERE CTD_ID = p_CTD_ID
142 AND object_version_number = p_object_version_number;
143
144 IF (SQL%NOTFOUND) THEN
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146 END IF;
147 END Update_Row;
148
149
150 ----------------------------------------------------------
151 ---- MEDIA ----
152 ----------------------------------------------------------
153
154 -- ========================================================
155 --
156 -- NAME
157 -- createDeleteBody
158 --
159 -- PURPOSE
160 --
161 -- NOTES
162 --
163 -- HISTORY
164 --
165 -- ========================================================
166 PROCEDURE Delete_Row(
167 p_CTD_ID NUMBER)
168 IS
169 BEGIN
170 DELETE FROM AMS_CTDS
171 WHERE CTD_ID = p_CTD_ID;
172 If (SQL%NOTFOUND) then
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174 End If;
175 END Delete_Row ;
176
177
178
179 ----------------------------------------------------------
180 ---- MEDIA ----
181 ----------------------------------------------------------
182
183 -- ========================================================
184 --
185 -- NAME
186 -- createLockBody
187 --
188 -- PURPOSE
189 --
190 -- NOTES
191 --
192 -- HISTORY
193 --
194 -- ========================================================
195 PROCEDURE Lock_Row(
196 p_ctd_id NUMBER,
197 p_action_id NUMBER,
198 p_forward_url VARCHAR2,
199 p_track_url VARCHAR2,
200 p_activity_product_id NUMBER,
201 p_activity_offer_id NUMBER,
202 p_object_version_number NUMBER,
203 p_last_update_date DATE,
204 p_last_updated_by NUMBER,
205 p_creation_date DATE,
206 p_created_by NUMBER,
207 p_last_update_login NUMBER,
208 p_security_group_id NUMBER)
209
210 IS
211 CURSOR C IS
212 SELECT *
213 FROM AMS_CTDS
214 WHERE CTD_ID = p_CTD_ID
215 FOR UPDATE of CTD_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.ctd_id = p_ctd_id)
228 AND ( ( Recinfo.action_id = p_action_id)
229 OR ( ( Recinfo.action_id IS NULL )
230 AND ( p_action_id IS NULL )))
231 AND ( ( Recinfo.forward_url = p_forward_url)
232 OR ( ( Recinfo.forward_url IS NULL )
233 AND ( p_forward_url IS NULL )))
234 AND ( ( Recinfo.track_url = p_track_url)
235 OR ( ( Recinfo.track_url IS NULL )
236 AND ( p_track_url IS NULL )))
237 AND ( ( Recinfo.activity_product_id = p_activity_product_id)
238 OR ( ( Recinfo.activity_product_id IS NULL )
239 AND ( p_activity_product_id IS NULL )))
240 AND ( ( Recinfo.activity_offer_id = p_activity_offer_id)
241 OR ( ( Recinfo.activity_offer_id IS NULL )
242 AND ( p_activity_offer_id IS NULL )))
243 AND ( ( Recinfo.object_version_number = p_object_version_number)
244 OR ( ( Recinfo.object_version_number IS NULL )
245 AND ( p_object_version_number IS NULL )))
246 AND ( ( Recinfo.last_update_date = p_last_update_date)
247 OR ( ( Recinfo.last_update_date IS NULL )
248 AND ( p_last_update_date IS NULL )))
249 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
250 OR ( ( Recinfo.last_updated_by IS NULL )
251 AND ( p_last_updated_by IS NULL )))
252 AND ( ( Recinfo.creation_date = p_creation_date)
253 OR ( ( Recinfo.creation_date IS NULL )
254 AND ( p_creation_date IS NULL )))
255 AND ( ( Recinfo.created_by = p_created_by)
256 OR ( ( Recinfo.created_by IS NULL )
257 AND ( p_created_by IS NULL )))
258 AND ( ( Recinfo.last_update_login = p_last_update_login)
259 OR ( ( Recinfo.last_update_login IS NULL )
260 AND ( p_last_update_login IS NULL )))
261 AND ( ( Recinfo.security_group_id = p_security_group_id)
262 OR ( ( Recinfo.security_group_id IS NULL )
263 AND ( p_security_group_id IS NULL )))
264 ) THEN
265 RETURN;
266 ELSE
267 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
268 APP_EXCEPTION.RAISE_EXCEPTION;
269 END IF;
270 END Lock_Row;
271
272 -- ========================================================
273 --
274 -- NAME
275 -- LOAD_ROW
276 --
277 -- PURPOSE
278 --
279 -- NOTES
280 --
281 -- HISTORY
282 --
283 -- ========================================================
284 procedure LOAD_ROW(
285 X_CTD_ID IN NUMBER,
286 X_ACTION_ID IN NUMBER,
287 X_FORWARD_URL IN VARCHAR2,
288 X_TRACK_URL IN VARCHAR2,
289 X_ACTIVITY_PRODUCT_ID IN NUMBER,
290 X_ACTIVITY_OFFER_ID IN NUMBER,
291 X_OWNER in VARCHAR2,
292 X_CUSTOM_MODE in VARCHAR2
293 ) is
294
295 l_user_id number := 0;
296 l_last_updated_by number;
297 l_obj_verno number;
298 l_ctd_id number := 1;
299 l_row_id varchar2(100);
300 l_dummy_char varchar2(1);
301
302 cursor c_obj_verno is
303 select OBJECT_VERSION_NUMBER,
304 last_updated_by
305 from AMS_CTDS
306 where CTD_ID = X_CTD_ID;
307
308 cursor c_chk_ctd_exists is
309 select 'X'
310 from AMS_CTDS
311 where CTD_ID = X_CTD_ID;
312
313 cursor c_get_ctd_id is
314 select ams_ctds_s.nextval
315 from dual;
316
317 BEGIN
318
319 if X_OWNER = 'SEED' then
320 l_user_id := 1;
321 elsif X_OWNER = 'ORACLE' then
322 l_user_id := 2;
323 elsif X_OWNER = 'SYSADMIN' THEN
324 l_user_id := 0;
325 end if;
326
327 open c_chk_ctd_exists;
328 fetch c_chk_ctd_exists into l_dummy_char;
329 if c_chk_ctd_exists%notfound
330 then
331 if x_ctd_id is null then
332 open c_get_ctd_id;
333 fetch c_get_ctd_id into l_ctd_id;
334 close c_get_ctd_id;
335 else
336 l_ctd_id := x_ctd_id;
337 end if;
338
339 close c_chk_ctd_exists;
340 l_obj_verno := 1;
341 AMS_CTDS_PKG.INSERT_ROW (
342 px_ctd_id => l_ctd_id,
343 p_action_id => X_ACTION_ID,
344 p_forward_url => x_forward_url,
345 p_track_url => x_track_url,
346 p_activity_product_id => X_ACTIVITY_PRODUCT_ID,
347 p_activity_offer_id => X_ACTIVITY_offer_ID,
348 px_object_version_number => l_obj_verno,
349 p_last_update_date => sysdate,
350 p_last_updated_by => l_user_id,
351 p_creation_date => sysdate,
352 p_created_by =>l_user_id,
353 p_last_update_login => 0,
354 p_security_group_id => 0);
355
356 else
357 close c_chk_ctd_exists;
358
359 open c_obj_verno;
360 fetch c_obj_verno into l_obj_verno,l_last_updated_by;
361 /*if (c_obj_verno%notfound) then
362 --dbms_output.put_line('Excpetion :: rec not found with the obj version num');
363 end if;*/
364 close c_obj_verno;
365
366 if (l_last_updated_by in (1,2,0) OR
367 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
368
369 AMS_CTDS_PKG.UPDATE_ROW(
370 p_ctd_id => X_CTD_ID,
371 p_action_id => X_ACTION_ID,
372 p_forward_url => X_FORWARD_URL,
373 p_track_url => X_TRACK_URL,
374 p_activity_product_id => X_ACTIVITY_PRODUCT_ID,
375 p_activity_offer_id => X_ACTIVITY_OFFER_ID,
376 p_object_version_number => l_obj_verno,
377 p_last_update_date => SYSDATE,
378 p_last_updated_by => l_last_updated_by,
379 p_creation_date => SYSDATE,
380 p_created_by => l_user_id,
381 p_last_update_login => 0,
382 p_security_group_id =>0
383 );
384 end if;
385 end if;
386
387 EXCEPTION -- exception handlers begin
388 WHEN OTHERS THEN -- handles all other errors
389 ROLLBACK;
390
391 END LOAD_ROW;
392
393 -- ========================================================
394 --
395 -- NAME
396 -- TRANSLATE_ROW
397 --
398 -- PURPOSE
399 --
400 -- NOTES
401 -- This table doen't have any translatable entry.
402 -- HISTORY
403 --
404 -- ========================================================
405
406 PROCEDURE TRANSLATE_ROW (
407 X_CTD_ID IN NUMBER,
408 X_OWNER IN VARCHAR2,
409 X_CUSTOM_MODE IN VARCHAR2
410 )
411 is
412 l_date DATE;
413 BEGIN
414 select sysdate into l_date from dual;
415 END TRANSLATE_ROW;
416
417
418 END AMS_CTDS_PKG;