[Home] [Help]
PACKAGE BODY: APPS.AMS_WEBMARKETING_PVT
Source
1 PACKAGE BODY AMS_WEBMARKETING_PVT AS
2 /* $Header: amsvwppb.pls 120.6 2006/08/18 17:52:02 anskumar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_WEBMARKETING_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvwppb.pls';
6
7 -- Hint: Primary key needs to be returned.
8 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
9 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
10 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
11
12
13
14 -- ========================================================================
15 -- PROCEDURE
16 -- handles successful API call for a row during Web ADI ->
17 -- WebMarketing integration call
18 -- Purpose
19 -- COMMIT successful row in database
20 -- HISTORY
21 --
22 -- ========================================================================
23
24 PROCEDURE WEBMARKETING_PLCE_CONTENT_TYPE (
25 p_api_version_number IN NUMBER := 1.0,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
27 p_commit IN VARCHAR2 := FND_API.G_FALSE,
28 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
29 p_placement_mp_id IN NUMBER,
30 placement_id IN NUMBER,
31 x_content_type OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2
35 ) IS
36
37
38 L_API_NAME CONSTANT VARCHAR2(30) := 'WEBMARKETING_PLCE_CONTENT_TYPE';
39 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
40
41 BEGIN
42
43 -- Standard call to check for call compatibility.
44 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
45 p_api_version_number,
46 l_api_name,
47 G_PKG_NAME)
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51
52 -- Standard Start of API savepoint
53
54 SAVEPOINT WEBMARKETING_PLCE_CONTENT_TYPE;
55
56 EXCEPTION
57
58 WHEN AMS_Utility_PVT.resource_locked THEN
59 x_return_status := FND_API.g_ret_sts_error;
60 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
61
62
63 WHEN FND_API.G_EXC_ERROR THEN
64 ROLLBACK TO WEBMARKETING_PLCE_CONTENT_TYPE;
65 x_return_status := FND_API.G_RET_STS_ERROR;
66 -- Standard call to get message count and if count=1, get the message
67
68 FND_MSG_PUB.Count_And_Get (
69 p_encoded => FND_API.G_FALSE,
70 p_count => x_msg_count,
71 p_data => x_msg_data
72 );
73
74 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
75 ROLLBACK TO WEBMARKETING_PLCE_CONTENT_TYPE;
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77
78 -- Standard call to get message count and if count=1, get the message
79 FND_MSG_PUB.Count_And_Get (
80 p_encoded => FND_API.G_FALSE,
81 p_count => x_msg_count,
82 p_data => x_msg_data
83 );
84
85 WHEN OTHERS THEN
86 ROLLBACK TO WEBMARKETING_PLCE_CONTENT_TYPE;
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
89 THEN
90 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
91 END IF;
92 -- Standard call to get message count and if count=1, get the message
93 FND_MSG_PUB.Count_And_Get (
94 p_encoded => FND_API.G_FALSE,
95 p_count => x_msg_count,
96 p_data => x_msg_data
97 );
98
99
100 END WEBMARKETING_PLCE_CONTENT_TYPE;
101
102 -- ========================================================================
103 -- PROCEDURE
104 -- handles successful API call for a row during Web ADI ->
105 -- WebMarketing integration call
106 -- Purpose
107 -- COMMIT successful row in database
108 -- HISTORY
109 --
110 -- ========================================================================
111
112 PROCEDURE WEBMARKETING_PLCE_CITEMS (
113 p_api_version_number IN NUMBER := 1.0,
114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
115 p_commit IN VARCHAR2 := FND_API.G_FALSE,
116 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
117 p_placement_mp_id IN NUMBER,
118 p_web_mp_rec IN web_mp_track_rec_type := g_miss_web_mp_track_rec,
119 x_placement_citem_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
120 p_content_item_id IN NUMBER,
121 p_citem_version_id IN NUMBER,
122 p_association_type IN VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 x_return_status OUT NOCOPY VARCHAR2
126 ) IS
127
128 L_API_NAME CONSTANT VARCHAR2(30) := 'WEBMARKETING_PLCE_CITEMS';
129 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
130 l_object_version_number NUMBER := 1;
131 l_placement_citem_id_tbl JTF_NUMBER_TABLE;
132 l_placement_citem_count NUMBER;
133 l_citem_ver_id NUMBER;
134 l_placement_citem_id NUMBER;
135 l_assoc_object1 VARCHAR2(30);
136 l_assoc_object2 VARCHAR2(30);
137 l_assoc_object3 VARCHAR2(30);
138 l_assoc_object4 VARCHAR2(30);
139 l_assoc_object5 VARCHAR2(30);
140 l_content_type_code VARCHAR2(30);
141 l_dummy NUMBER;
142 x_placement_citem_id NUMBER;
143 i NUMBER := 0;
144 l_simple VARCHAR2(1) := 'Y';
145
146
147 CURSOR c_citem_assoc_id IS
148 SELECT AMS_WEB_PLCE_CITEM_ASSOC_S.NEXTVAL
149 FROM dual;
150
151 CURSOR c_citem_assoc_id_exists (l_id IN NUMBER) IS
152 SELECT 1
153 FROM AMS_WEB_PLCE_CITEM_ASSOC
154 WHERE PLACEMENT_CITEM_ID = l_id;
155
156
157 CURSOR c_plce_citems(l_citem_version_id IN NUMBER) IS
158 SELECT civ.CONTENT_ITEM_ID c_item_id ,civ.CITEM_VERSION_ID c_version_id
159 FROM IBC_COMPOUND_RELATIONS cr, IBC_ATTRIBUTE_TYPES_VL attr, IBC_CONTENT_TYPES_VL ct,IBC_CITEM_VERSIONS_VL civ,
160 IBC_CONTENT_ITEMS ci WHERE
161 cr.ATTRIBUTE_TYPE_CODE = attr.ATTRIBUTE_TYPE_CODE AND
162 cr.CONTENT_TYPE_CODE = attr.CONTENT_TYPE_CODE AND
163 ct.CONTENT_TYPE_CODE = ci.CONTENT_TYPE_CODE AND
164 ci.CONTENT_ITEM_ID = civ.CONTENT_ITEM_ID AND
165 cr.CONTENT_ITEM_ID = civ.CONTENT_ITEM_ID AND
166 ci.LIVE_CITEM_VERSION_ID = civ.CITEM_VERSION_ID AND
167 civ.CITEM_VERSION_STATUS = 'APPROVED' AND
168 cr.CITEM_VERSION_ID = l_citem_version_id;
169
170 CURSOR c_citem_ver_id(l_content_item_id IN NUMBER) IS
171 SELECT LIVE_CITEM_VERSION_ID
172 FROM IBC_CONTENT_ITEMS WHERE CONTENT_ITEM_ID = l_content_item_id;
173
174 citem_rec c_plce_citems%rowtype;
175
176 BEGIN
177
178 -- Standard call to check for call compatibility.
179 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
180 p_api_version_number,
181 l_api_name,
182 G_PKG_NAME)
183 THEN
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185 END IF;
186
187 -- Standard Start of API savepoint
188
189 SAVEPOINT webmarketing_plce_citems_pvt;
190
191 -- Initialize message list if p_init_msg_list is set to TRUE.
192 IF FND_API.to_Boolean( p_init_msg_list )
193 THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196
197 -- Debug Message
198 --Bug Fix 4652859
199 IF (AMS_DEBUG_HIGH_ON) THEN
200 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
201 END IF;
202
203
204 -- Initialize API return status to SUCCESS
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 -- Retrieve the Content Items may be compound or simple .
208
209
210 OPEN c_citem_ver_id(p_content_item_id);
211 FETCH c_citem_ver_id INTO l_citem_ver_id;
212 CLOSE c_citem_ver_id;
213
214
215 l_placement_citem_id_tbl := JTF_NUMBER_TABLE();
216 x_placement_citem_id_tbl := JTF_NUMBER_TABLE();
217 FOR citem_rec IN c_plce_citems ( l_citem_ver_id)
218 LOOP
219 -- Populate the Tables Now
220 -- Call Table Handlers (AMS_WEB_PLCE_CITEM_ASSOC )
221 -- Invoke table handler(AMS_WEB_PLCE_CITEM_ASSOC_PKG.INSERT_ROW)
222
223 l_simple := 'N';
224 IF (p_web_mp_rec.placement_citem_id IS NULL OR p_web_mp_rec.placement_citem_id = FND_API.g_miss_num) THEN
225
226 LOOP
227
228 l_dummy := NULL;
229 OPEN c_citem_assoc_id;
230 FETCH c_citem_assoc_id INTO l_placement_citem_id;
231 CLOSE c_citem_assoc_id;
232
233 OPEN c_citem_assoc_id_exists(l_placement_citem_id);
234 FETCH c_citem_assoc_id_exists INTO l_dummy;
235 CLOSE c_citem_assoc_id_exists;
236 EXIT WHEN l_dummy IS NULL;
237
238 END LOOP;
239 x_placement_citem_id := l_placement_citem_id;
240
241 END IF;
242
243
244 AMS_WEB_CITEM_ASSOC_PKG.Insert_Row(
245 px_placement_citem_id => l_placement_citem_id,
246 p_placement_mp_id => p_placement_mp_id,
247 p_content_item_id => citem_rec.c_item_id,
248 p_citem_version_id => citem_rec.c_version_id,
249 p_created_by => FND_GLOBAL.USER_ID,
250 p_creation_date => SYSDATE,
251 p_last_updated_by => FND_GLOBAL.USER_ID,
252 p_last_update_date => SYSDATE,
253 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
254 px_object_version_number => l_object_version_number,
255 p_return_status => x_return_status,
256 p_msg_count => x_msg_data,
257 p_msg_data => x_msg_data);
258
259
260 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263 i := i+1;
264 l_placement_citem_id_tbl.EXTEND;
265 l_placement_citem_id_tbl(i) := l_placement_citem_id;
266
267 END LOOP;
268
269 -- May be a Simple Item
270
271 IF ( i = 0 AND l_simple = 'Y' ) THEN
272
273 IF (p_web_mp_rec.placement_citem_id IS NULL OR p_web_mp_rec.placement_citem_id = FND_API.g_miss_num) THEN
274
275 LOOP
276
277 l_dummy := NULL;
278 OPEN c_citem_assoc_id;
279 FETCH c_citem_assoc_id INTO l_placement_citem_id;
280 CLOSE c_citem_assoc_id;
281
282 OPEN c_citem_assoc_id_exists(l_placement_citem_id);
283 FETCH c_citem_assoc_id_exists INTO l_dummy;
284 CLOSE c_citem_assoc_id_exists;
285 EXIT WHEN l_dummy IS NULL;
286
287 END LOOP;
288 x_placement_citem_id := l_placement_citem_id;
289 END IF;
290
291 AMS_WEB_CITEM_ASSOC_PKG.Insert_Row(
292 px_placement_citem_id => l_placement_citem_id,
293 p_placement_mp_id => p_placement_mp_id,
294 p_content_item_id => p_content_item_id,
295 p_citem_version_id => l_citem_ver_id,
296 p_created_by => FND_GLOBAL.USER_ID,
297 p_creation_date => SYSDATE,
298 p_last_updated_by => FND_GLOBAL.USER_ID,
299 p_last_update_date => SYSDATE,
300 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
301 px_object_version_number => l_object_version_number,
302 p_return_status => x_return_status,
303 p_msg_count => x_msg_data,
304 p_msg_data => x_msg_data);
305
306
307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
308 RAISE FND_API.G_EXC_ERROR;
309 END IF;
310 i := i+1;
311 l_placement_citem_id_tbl.EXTEND;
312 l_placement_citem_id_tbl(i) := l_placement_citem_id;
313
314 END IF;
315
316
317 --- This API would be calling OCM API's for Associations ( IBC_ASSOCIATIONS)
318 -- p_assoc_type_code => 'AMS_PLCE',
319 -- p_assoc_object1 => object_used_by_id
320 -- p_assoc_object2 => placementid
321 --p_content_item_id => p_content_item_id,
322 --p_citem_version_id => p_citem_version_id,
323
324 l_assoc_object1 := p_web_mp_rec.object_used_by_id;
325 l_assoc_object2 := p_web_mp_rec.placement_id;
326
327 Ibc_Associations_Grp.Create_Association( p_api_version => p_api_version_number,
328 p_init_msg_list => p_init_msg_list,
329 p_commit => p_commit,
330 p_assoc_type_code => 'AMS_PLCE',
331 p_assoc_object1 => l_assoc_object1,
332 p_assoc_object2 => l_assoc_object2,
333 p_assoc_object3 => l_assoc_object3 ,
334 p_assoc_object4 => l_assoc_object4,
335 p_assoc_object5 => l_assoc_object5,
336 p_content_item_id => p_content_item_id,
337 p_citem_version_id => p_citem_version_id,
338 x_return_status => x_return_status ,
339 x_msg_count => x_msg_count ,
340 x_msg_data => x_msg_data );
341
342
343 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347
348
349 -- Standard check for p_commit
350 IF FND_API.to_Boolean( p_commit )
351 THEN
352 COMMIT WORK;
353 END IF;
354
355 -- Populate the JTF Number Table
356 /*
357
358 l_placement_citem_id_tbl := JTF_NUMBER_TABLE();
359 for i in 1..l_placement_citem_id_tbl.COUNT
360 loop
361 x_placement_citem_id_tbl.EXTEND;
362 x_placement_citem_id_tbl(i) := l_placement_citem_id_tbl(i);
363 end loop;
364 */
365 x_placement_citem_id_tbl := l_placement_citem_id_tbl;
366
367 -- Debug Message
368
369 IF (AMS_DEBUG_HIGH_ON) THEN
370 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
371 END IF;
372
373 -- Standard call to get message count and if count is 1, get message info.
374 FND_MSG_PUB.Count_And_Get
375 (p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378
379 -- End of API body
380
381 EXCEPTION
382
383 WHEN FND_API.G_EXC_ERROR THEN
384 ROLLBACK TO webmarketing_plce_citems_pvt;
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 -- Standard call to get message count and if count=1, get the message
387 FND_MSG_PUB.Count_And_Get (
388 p_encoded => FND_API.G_FALSE,
389 p_count => x_msg_count,
390 p_data => x_msg_data
391 );
392
393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
394 ROLLBACK TO webmarketing_plce_citems_pvt;
395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
396 -- Standard call to get message count and if count=1, get the message
397 FND_MSG_PUB.Count_And_Get (
398 p_encoded => FND_API.G_FALSE,
399 p_count => x_msg_count,
400 p_data => x_msg_data
401 );
402
403 WHEN OTHERS THEN
404 ROLLBACK TO webmarketing_plce_citems_pvt;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
407 THEN
408 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,L_API_NAME);
409 END IF;
410 -- Standard call to get message count and if count=1, get the message
411 FND_MSG_PUB.Count_And_Get (
412 p_encoded => FND_API.G_FALSE,
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416
417 End WEBMARKETING_PLCE_CITEMS;
418
419
420 PROCEDURE VALIDATE_WEB_PLCE_ASSOC(
421 p_api_version_number IN NUMBER,
422 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
423 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
424 p_web_mp_rec IN web_mp_track_rec_type,
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2,
428 p_validation_mode IN VARCHAR2
429 )
430 IS
431 L_API_NAME CONSTANT VARCHAR2(30) := 'VALIDATE_WEB_PLCE_ASSOC';
432 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
433 l_object_version_number NUMBER;
434 l_web_mp_rec AMS_WEBMARKETING_PVT.web_mp_track_rec_type;
435 l_PLACEMENT_MP_ID NUMBER;
436 l_object_used_by_id NUMBER;
437 l_PLACEMENT_ID NUMBER;
438 l_application_id NUMBER;
439
440
441 CURSOR c_p_id_exists (l_id IN NUMBER) IS
442 SELECT 1
443 FROM AMS_WEB_PLACEMENTS_B
444 WHERE PLACEMENT_ID = l_id;
445
446
447 CURSOR c_site_ref_id (l_site_ref_code IN VARCHAR2) IS
448 SELECT site_id
449 FROM ams_iba_pl_sites_b
450 WHERE site_ref_code = l_site_ref_code;
451
452 CURSOR c_site_id_exists (l_site_id IN NUMBER) IS
453 SELECT site_id
454 FROM ams_iba_pl_sites_b
455 WHERE site_id = l_site_id;
456
457
458 CURSOR c_pctype_id_exists (l_id IN NUMBER) IS
459 SELECT 1
460 FROM AMS_WEB_PLCE_CTYPE_ASSOC
461 WHERE PLACEMENT_ID = l_id;
462
463 CURSOR c_campaign_id_exists (l_id IN NUMBER) IS
464 SELECT 1
465 FROM AMS_CAMPAIGNS_ALL_B
466 WHERE CAMPAIGN_ID = l_id;
467
468 CURSOR c_campaign_schedule_id_exists (l_id IN NUMBER) IS
469 SELECT 1
470 FROM AMS_CAMPAIGN_SCHEDULES_B
471 WHERE SCHEDULE_ID = l_id;
472
473 BEGIN
474 -- Standard Start of API savepoint
475 SAVEPOINT VALIDATE_WEB_PLCE_ASSOC_PVT;
476
477 -- Standard call to check for call compatibility.
478 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
479 p_api_version_number,
480 l_api_name,
481 G_PKG_NAME)
482 THEN
483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
484 END IF;
485
486 -- Initialize message list if p_init_msg_list is set to TRUE.
487 IF FND_API.to_Boolean( p_init_msg_list )
488 THEN
489 FND_MSG_PUB.initialize;
490 END IF;
491
492 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
493
494 -- Verify Campaign Id ( is it required )
495 -- Verify Campaign Schedule Id
496 -- Verify Placement Id
497 -- Verify Application Id
498 -- Verify Content Item
499 -- Verify Content Item Version Id
500 -- Verify Content Type Code
501 -- Validate all the cursor values
502
503 OPEN c_campaign_schedule_id_exists(l_web_mp_rec.object_used_by_id);
504 FETCH c_campaign_schedule_id_exists INTO l_object_used_by_id;
505 CLOSE c_campaign_schedule_id_exists;
506
507 OPEN c_p_id_exists(l_web_mp_rec.placement_id);
508 FETCH c_p_id_exists INTO l_PLACEMENT_ID;
509 CLOSE c_p_id_exists;
510
511 OPEN c_site_id_exists(l_web_mp_rec.application_id);
512 FETCH c_site_id_exists INTO l_application_id;
513 CLOSE c_site_id_exists;
514
515 OPEN c_pctype_id_exists(l_web_mp_rec.object_used_by_id);
516 FETCH c_pctype_id_exists INTO l_object_used_by_id;
517 CLOSE c_pctype_id_exists;
518
519 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
520 RAISE FND_API.G_EXC_ERROR;
521 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523 END IF;
524 END IF;
525
526 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
527 IF (AMS_DEBUG_HIGH_ON) THEN
528 AMS_UTILITY_PVT.debug_message('In Validate: before VALIDATE_WEB_PLCE_ASSOC call ' );
529 END IF;
530
531 END IF;
532
533
534 -- Debug Message
535 IF (AMS_DEBUG_HIGH_ON) THEN
536 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
537 END IF;
538
539
540 -- Initialize API return status to SUCCESS
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543
544 -- Debug Message
545 IF (AMS_DEBUG_HIGH_ON) THEN
546 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
547 END IF;
548
549 -- Standard call to get message count and if count is 1, get message info.
550 FND_MSG_PUB.Count_And_Get
551 (p_count => x_msg_count,
552 p_data => x_msg_data
553 );
554
555 EXCEPTION
556
557 WHEN AMS_Utility_PVT.resource_locked THEN
558 x_return_status := FND_API.g_ret_sts_error;
559 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
560 WHEN FND_API.G_EXC_ERROR THEN
561 ROLLBACK TO VALIDATE_WEB_PLCE_ASSOC_PVT;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 -- Standard call to get message count and if count=1, get the message
564 FND_MSG_PUB.Count_And_Get (
565 p_encoded => FND_API.G_FALSE,
566 p_count => x_msg_count,
567 p_data => x_msg_data
568 );
569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
570 ROLLBACK TO VALIDATE_WEB_PLCE_ASSOC_PVT;
571 IF (AMS_DEBUG_HIGH_ON) THEN
572 AMS_UTILITY_PVT.debug_message('In Validate - unexpected err: validation_mode= ' || p_validation_mode);
573 END IF;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 -- Standard call to get message count and if count=1, get the message
576 FND_MSG_PUB.Count_And_Get (
577 p_encoded => FND_API.G_FALSE,
578 p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581 WHEN OTHERS THEN
582 ROLLBACK TO VALIDATE_WEB_PLCE_ASSOC_PVT;
583 IF (AMS_DEBUG_HIGH_ON) THEN
584
585 AMS_UTILITY_PVT.debug_message('In Validate - others err: validation_mode= ' || p_validation_mode);
586 END IF;
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
589 THEN
590 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
591 END IF;
592
593 -- Standard call to get message count and if count=1, get the message
594 FND_MSG_PUB.Count_And_Get (
595 p_encoded => FND_API.G_FALSE,
596 p_count => x_msg_count,
597 p_data => x_msg_data
598 );
599
600 End VALIDATE_WEB_PLCE_ASSOC;
601
602
603 ---------------------------------------------------------------------
604 -- FUNCTION
605 -- check_citem_version_id
606 -- HISTORY
607 ---------------------------------------------------------------------
608
609
610 FUNCTION check_citem_version_id(
611 p_content_item_id IN NUMBER
612 )
613 RETURN NUMBER
614 IS
615 CURSOR c_citem_version_id IS
616 SELECT CITEM_VER_ID
617 FROM IBC_CITEMS_V
618 WHERE CITEM_ID = p_content_item_id;
619 l_citem_ver_id NUMBER;
620
621 BEGIN
622
623 OPEN c_citem_version_id;
624 FETCH c_citem_version_id INTO l_citem_ver_id;
625 CLOSE c_citem_version_id;
626
627 return l_citem_ver_id;
628
629 END check_citem_version_id;
630
631 ---------------------------------------------------------------------
632 -- FUNCTION
633 -- check_placement_publish : Verify the Publish status of the Placement
634 -- HISTORY
635 ---------------------------------------------------------------------
636
637 FUNCTION check_placement_publish(
638 p_placement_id IN NUMBER
639 )
640 RETURN VARCHAR
641 IS
642 CURSOR c_check_placement_publish IS
643 select auto_publish_flag from ams_web_placements_b where placement_id = p_placement_id;
644 l_publish VARCHAR2(1);
645
646 BEGIN
647
648 OPEN c_check_placement_publish;
649 FETCH c_check_placement_publish INTO l_publish;
650 CLOSE c_check_placement_publish;
651
652 return l_publish;
653
654 END check_placement_publish;
655
656 -- ========================================================================
657 -- PROCEDURE
658 -- handles successful API call for a row during Web ADI ->
659 -- WebMarketing integration call
660 -- Purpose
661 -- COMMIT successful row in database
662 -- HISTORY
663 --
664 -- ========================================================================
665
666 PROCEDURE CREATE_WEB_PLCE_ASSOC (
667 p_api_version_number IN NUMBER := 1.0,
668 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
669 p_commit IN VARCHAR2 := FND_API.G_FALSE,
670 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
671 p_web_mp_rec IN web_mp_track_rec_type := g_miss_web_mp_track_rec,
672 x_placement_mp_id OUT NOCOPY NUMBER,
673 x_placement_citem_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676 x_return_status OUT NOCOPY VARCHAR2
677 ) IS
678
679 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_WEB_PLCE_ASSOC';
680 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
681 l_object_version_number NUMBER := 1;
682 l_dummy NUMBER;
683 l_PLACEMENT_MP_ID NUMBER;
684 l_validation_mode VARCHAR2(30);
685 l_rowid VARCHAR2(100);
686 l_content_type_code VARCHAR2(100);
687 L_CITEM_VERSION_ID NUMBER;
688 l_publish_flag Varchar2(1);
689 l_placement_citem_id_tbl JTF_NUMBER_TABLE;
690
691 CURSOR c_mp_id IS
692 SELECT AMS_WEB_PLCE_MP_B_S.NEXTVAL
693 FROM dual;
694
695 CURSOR c_citem_assoc_id IS
696 SELECT AMS_WEB_PLCE_CITEM_ASSOC_S.NEXTVAL
697 FROM dual;
698
699 CURSOR c_mp_id_exists (l_id IN NUMBER) IS
700 SELECT 1
701 FROM AMS_WEB_PLCE_MP_B
702 WHERE PLACEMENT_MP_ID = l_id;
703
704 CURSOR c_citem_assoc_id_exists (l_id IN NUMBER) IS
705 SELECT 1
706 FROM AMS_WEB_PLCE_CITEM_ASSOC
707 WHERE PLACEMENT_CITEM_ID = l_id;
708
709 CURSOR c_media_id (l_id IN NUMBER) IS
710 SELECT 1
711 FROM AMS_MEDIA_B
712 WHERE MEDIA_ID = l_id;
713
714
715
716 BEGIN
717
718 -- Standard Start of API savepoint
719 SAVEPOINT CREATE_WEB_PLCE_ASSOC;
720
721 -- Standard call to check for call compatibility.
722 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
723 p_api_version_number,
724 l_api_name,
725 G_PKG_NAME)
726 THEN
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728 END IF;
729
730 -- Initialize message list if p_init_msg_list is set to TRUE.
731 IF FND_API.to_Boolean(p_init_msg_list)
732 THEN
733 FND_MSG_PUB.initialize;
734 END IF;
735
736 -- Debug Message
737 IF (AMS_DEBUG_HIGH_ON) THEN
738 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
739 END IF;
740
741 -- Initialize API return status to SUCCESS
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744 -- Validate the In Rec
745
746 -- =========================================================================
747 -- Validate Environment
748 -- =========================================================================
749
750 IF FND_GLOBAL.User_Id IS NULL
751 THEN
752 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
753 RAISE FND_API.G_EXC_ERROR;
754 END IF;
755
756
757 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
758
759 THEN
760
761 -- Debug message
762 IF (AMS_DEBUG_HIGH_ON) THEN
763 AMS_UTILITY_PVT.debug_message('Private API: VALIDATE_WEB_PLCE_ASSOC');
764 END IF;
765
766 -- Invoke validation procedures
767
768
769 END IF;
770
771 IF (AMS_DEBUG_HIGH_ON) THEN
772 AMS_UTILITY_PVT.debug_message('In CREATE_WEB_PLCE_ASSOC: before VALIDATE_WEB_PLCE_ASSOC call ' );
773 END IF;
774
775 IF (AMS_DEBUG_HIGH_ON) THEN
776 AMS_UTILITY_PVT.debug_message('In CREATE_WEB_PLCE_ASSOC: before VALIDATE_WEB_PLCE_ASSOC call ' );
777 END IF;
778
779 -- Local variable initialization
780
781
782 IF (p_web_mp_rec.PLACEMENT_MP_ID IS NULL OR p_web_mp_rec.PLACEMENT_MP_ID = FND_API.g_miss_num) THEN
783 LOOP
784 l_dummy := NULL;
785 OPEN c_mp_id;
786 FETCH c_mp_id INTO l_PLACEMENT_MP_ID;
787 CLOSE c_mp_id;
788
789 OPEN c_mp_id_exists(l_PLACEMENT_MP_ID);
790 FETCH c_mp_id_exists INTO l_dummy;
791 CLOSE c_mp_id_exists;
792 EXIT WHEN l_dummy IS NULL;
793 END LOOP;
794 x_PLACEMENT_MP_ID := l_PLACEMENT_MP_ID;
795 END IF;
796
797
798 -- Debug Message
799 IF (AMS_DEBUG_HIGH_ON) THEN
800 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
801 END IF;
802
803 -- Invoke table handler(AMS_WEB_PLCE_MP_PKG.INSERT_ROW)
804
805 -- Verify the Activity_ID
806
807 -- IF ( p_web_mp_rec.activity_id = 510 ) THEN
808 -- l_content_type_code := 'AMS_WEB_PROD_RECOM';
809
810 IF (p_web_mp_rec.activity_id = 30 OR p_web_mp_rec.activity_id = 40 ) THEN
811 l_content_type_code := 'AMS_WEB_AD';
812 END IF;
813
814 -- call to get the citem-version-id
815
816 l_citem_version_id := check_citem_version_id(p_web_mp_rec.content_item_id);
817 l_publish_flag := check_placement_publish(p_web_mp_rec.placement_id);
818
819 AMS_WEB_PLCE_MP_PKG.INSERT_ROW(
820 x_rowid => l_rowid,
821 x_placement_mp_id => l_placement_mp_id,
822 x_placement_id => p_web_mp_rec.placement_id,
823 x_content_item_id => p_web_mp_rec.content_item_id,
824 x_citem_version_id => l_citem_version_id,
825 x_display_priority => p_web_mp_rec.display_priority,
826 x_publish_flag => l_publish_flag,
827 x_max_recommendations => p_web_mp_rec.max_recommendations,
828 x_object_used_by_id => p_web_mp_rec.object_used_by_id,
829 x_object_used_by => p_web_mp_rec.object_used_by,
830 x_security_group_id => null,
831 x_object_version_number => l_object_version_number,
832 x_attribute_category => null,
833 x_attribute1 => null,
834 x_attribute2 => null,
835 x_attribute3 => null,
836 x_attribute4 => null,
837 x_attribute5 => null,
838 x_attribute6 => null,
839 x_attribute7 => null,
840 x_attribute8 => null,
841 x_attribute9 => null,
842 x_attribute10 => null,
843 x_attribute11 => null,
844 x_attribute12 => null,
845 x_attribute13 => null,
846 x_attribute14 => null,
847 x_attribute15 => null,
848 x_content_type_code => l_content_type_code ,
849 x_placement_mp_title => p_web_mp_rec.placement_mp_title,
850 x_creation_date => sysdate ,
851 x_created_by => fnd_global.user_id,
852 x_last_update_date => sysdate,
853 x_last_updated_by => fnd_global.user_id,
854 x_last_update_login => fnd_global.conc_login_id );
855
856
857 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 -- Only for WebAdv / WebOffer
862 -- Verify the media-id for the Dynamic recommendations
863
864 IF (p_web_mp_rec.activity_id <> 510 ) THEN
865
866 WEBMARKETING_PLCE_CITEMS (
867 p_api_version_number => p_api_version_number,
868 p_init_msg_list => p_init_msg_list,
869 p_commit => p_commit ,
870 p_validation_level => p_validation_level,
871 p_placement_mp_id => l_placement_mp_id,
872 p_web_mp_rec => p_web_mp_rec,
873 x_placement_citem_id_tbl => l_placement_citem_id_tbl ,
874 p_content_item_id => p_web_mp_rec.content_item_id,
875 p_citem_version_id => p_web_mp_rec.citem_version_id,
876 p_association_type => 'AMS_PLCE',
877 x_msg_count => x_msg_count,
878 x_msg_data => x_msg_data ,
879 x_return_status => x_return_status
880 );
881
882
883 END IF;
884
885 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889 x_return_status := FND_API.G_RET_STS_SUCCESS;
890
891 -- Standard check for p_commit ( This shud be romved after testing - Actual Commit is done by the calling program )
892 IF FND_API.to_Boolean( p_commit) THEN
893 COMMIT WORK;
894 END IF;
895
896 -- Debug Message
897 -- IF (AMS_DEBUG_HIGH_ON) THEN
898 -- AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
899 -- END IF;
900
901 -- Standard call to get message count and if count is 1, get message info.
902 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
903 p_data => x_msg_data
904 );
905
906 x_placement_mp_id := l_placement_mp_id;
907 x_placement_citem_id_tbl := l_placement_citem_id_tbl;
908 /*
909 for i in 1..l_placement_citem_id_tbl.COUNT
910 loop
911 x_placement_citem_id_tbl.EXTEND;
912 x_placement_citem_id_tbl(i) := l_placement_citem_id_tbl(i);
913 end loop;
914 */
915 -- End of API body
916
917 EXCEPTION
918
919 WHEN AMS_Utility_PVT.resource_locked THEN
920 x_return_status := FND_API.g_ret_sts_error;
921 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
922
923
924 WHEN FND_API.G_EXC_ERROR THEN
925 ROLLBACK TO CREATE_WEB_PLCE_ASSOC;
926 x_return_status := FND_API.G_RET_STS_ERROR;
927 -- Standard call to get message count and if count=1, get the message
928
929 FND_MSG_PUB.Count_And_Get (
930 p_encoded => FND_API.G_FALSE,
931 p_count => x_msg_count,
932 p_data => x_msg_data
933 );
934
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936 ROLLBACK TO CREATE_WEB_PLCE_ASSOC;
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938
939 -- Standard call to get message count and if count=1, get the message
940 FND_MSG_PUB.Count_And_Get (
941 p_encoded => FND_API.G_FALSE,
942 p_count => x_msg_count,
943 p_data => x_msg_data
944 );
945
946 WHEN OTHERS THEN
947 ROLLBACK TO CREATE_WEB_PLCE_ASSOC;
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
950 THEN
951 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
952 END IF;
953 -- Standard call to get message count and if count=1, get the message
954
955 FND_MSG_PUB.Count_And_Get (
956 p_encoded => FND_API.G_FALSE,
957 p_count => x_msg_count,
958 p_data => x_msg_data
959 );
960
961 End CREATE_WEB_PLCE_ASSOC;
962
963
964
965
966
967 -- ========================================================================
968 -- PROCEDURE
969 -- handles successful API call for a row during Web ADI ->
970 -- WebMarketing integration call
971 -- Purpose
972 --
973 -- HISTORY
974 --
975 -- ========================================================================
976
977 PROCEDURE WEBMARKETING_PLCE_CONTENT_TYPE (
978 p_api_version_number IN NUMBER := 1.0,
979 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
980 p_commit IN VARCHAR2 := FND_API.G_FALSE,
981 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
982 p_placement_mp_id IN NUMBER,
983 p_content_item_id IN NUMBER,
984 x_content_type OUT NOCOPY VARCHAR2,
985 x_msg_count OUT NOCOPY NUMBER,
986 x_msg_data OUT NOCOPY VARCHAR2,
987 x_return_status OUT NOCOPY VARCHAR2
988 ) IS
989
990 L_API_NAME CONSTANT VARCHAR2(30) := 'WEBMARKETING_PLCE_CONTENT_TYPE';
991 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
992 l_placement_mp_id NUMBER;
993
994 CURSOR c_ctype_code (l_placement_mp_id IN NUMBER) IS
995 SELECT content_type_code
996 FROM ams_web_plce_mp_b
997 WHERE placement_mp_id = l_placement_mp_id;
998
999 CURSOR c_mp_id_exists (l_placement_mp_id IN NUMBER) IS
1000 SELECT 1
1001 FROM AMS_WEB_PLCE_MP_B
1002 WHERE PLACEMENT_MP_ID = l_placement_mp_id;
1003
1004 -- Need to verify the Content Type Code Presence in IBC Schema Also
1005
1006 CURSOR c_content_id_exists (l_content_item_id IN NUMBER) IS
1007 SELECT CONTENT_TYPE_CODE from
1008 IBC_CONTENT_ITEMS WHERE CONTENT_ITEM_ID = l_content_item_id AND CONTENT_ITEM_STATUS = 'APPROVED';
1009
1010
1011 BEGIN
1012
1013 -- Standard Start of API savepoint
1014 SAVEPOINT webmarketing_plce_content_type ;
1015
1016 -- Standard call to check for call compatibility.
1017 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1018 p_api_version_number,
1019 l_api_name,
1020 G_PKG_NAME)
1021 THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024
1025 -- Initialize message list if p_init_msg_list is set to TRUE.
1026 IF FND_API.to_Boolean( p_init_msg_list )
1027 THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030
1031 -- Debug Message
1032 --Bug Fix 4652859
1033 IF (AMS_DEBUG_HIGH_ON) THEN
1034 AMS_UTILITY_PVT.debug_message('Private API: ' || L_API_NAME || 'start');
1035 END IF;
1036
1037
1038 -- Initialize API return status to SUCCESS
1039 x_return_status := FND_API.G_RET_STS_SUCCESS;
1040
1041 OPEN c_mp_id_exists(p_placement_mp_id);
1042 FETCH c_mp_id_exists INTO l_placement_mp_id;
1043 CLOSE c_mp_id_exists;
1044
1045 IF (l_placement_mp_id = p_placement_mp_id ) THEN
1046 OPEN c_ctype_code(l_placement_mp_id);
1047 FETCH c_ctype_code INTO x_content_type;
1048 CLOSE c_ctype_code;
1049 END IF;
1050
1051 OPEN c_content_id_exists(p_placement_mp_id);
1052 FETCH c_mp_id_exists INTO l_placement_mp_id;
1053 CLOSE c_mp_id_exists;
1054
1055
1056 -- x_content_type := l_content_type;
1057
1058 EXCEPTION
1059
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 ROLLBACK TO webmarketing_plce_content_type;
1062 x_return_status := FND_API.G_RET_STS_ERROR;
1063 -- Standard call to get message count and if count=1, get the message
1064 FND_MSG_PUB.Count_And_Get (
1065 p_encoded => FND_API.G_FALSE,
1066 p_count => x_msg_count,
1067 p_data => x_msg_data
1068 );
1069
1070 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1071 ROLLBACK TO webmarketing_plce_content_type;
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 -- Standard call to get message count and if count=1, get the message
1074 FND_MSG_PUB.Count_And_Get (
1075 p_encoded => FND_API.G_FALSE,
1076 p_count => x_msg_count,
1077 p_data => x_msg_data
1078 );
1079
1080 WHEN OTHERS THEN
1081 ROLLBACK TO webmarketing_plce_content_type;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1084 THEN
1085 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,L_API_NAME);
1086 END IF;
1087 -- Standard call to get message count and if count=1, get the message
1088 FND_MSG_PUB.Count_And_Get (
1089 p_encoded => FND_API.G_FALSE,
1090 p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093
1094 End WEBMARKETING_PLCE_CONTENT_TYPE;
1095
1096 -- ========================================================================
1097 -- PROCEDURE
1098 -- Integration API Call for WebPlacement Content Status for Approval Process- ->
1099 -- WebMarketing integration call
1100 -- Purpose
1101 -- WebMarketing integration call for Campaign Activity Approval Process
1102 -- HISTORY
1103 --
1104 -- ========================================================================
1105
1106
1107 PROCEDURE WEBMARKETING_CONTENT_STATUS (
1108 p_api_version_number IN NUMBER := 1.0,
1109 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1110 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1111 p_campaign_activity_id IN NUMBER,
1112 x_msg_count OUT NOCOPY NUMBER,
1113 x_msg_data OUT NOCOPY VARCHAR2,
1114 x_return_status OUT NOCOPY VARCHAR2
1115 ) IS
1116
1117 L_API_NAME CONSTANT VARCHAR2(30) := 'WEBMARKETING_CONTENT_STATUS';
1118 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1119 l_campaign_activity_id NUMBER;
1120 l_content_id NUMBER;
1121 l_media_id NUMBER;
1122
1123 CURSOR c_campaign_activity_id_exists (l_campaign_activity_id IN NUMBER) IS
1124 SELECT sched.schedule_id
1125 FROM AMS_CAMPAIGN_SCHEDULES_B SCHED, AMS_WEB_PLCE_MP_B MP
1126 WHERE SCHED.SCHEDULE_ID = MP.OBJECT_USED_BY_ID AND SCHED.SCHEDULE_ID = L_CAMPAIGN_ACTIVITY_ID;
1127
1128 CURSOR c_media_id_exists(l_schedule_id IN NUMBER) IS
1129 SELECT activity_id FROM ams_campaign_schedules_b where SCHEDULE_ID=l_schedule_id;
1130
1131 CURSOR c_content_id_exists(l_campaign_activity_id IN NUMBER) IS
1132 SELECT Assn.content_item_id , Citem.live_citem_version_id
1133 FROM IBC_ASSOCIATIONS Assn, IBC_CONTENT_ITEMS Citem
1134 WHERE Assn.ASSOCIATED_OBJECT_VAL1 = TO_CHAR(l_campaign_activity_id)
1135 AND Assn.CONTENT_ITEM_ID = Citem.CONTENT_ITEM_ID
1136 AND Citem.CONTENT_ITEM_STATUS <> 'APPROVED'
1137 AND Assn.ASSOCIATION_TYPE_CODE in ('AMS_PLCE') ;
1138
1139 -- if required
1140 -- mp.placement_mp_id ,sched.schedule_id, mp.content_item_id
1141 -- l_mp_activity_rec c_campaign_activity_id_exists%rowtype;
1142
1143 l_content_item_rec c_content_id_exists%rowtype;
1144
1145
1146 BEGIN
1147
1148 -- Standard Start of API savepoint
1149 SAVEPOINT WEBMARKETING_CONTENT_STATUS_S ;
1150
1151 -- Standard call to check for call compatibility.
1152 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1153 p_api_version_number,
1154 l_api_name,
1155 G_PKG_NAME)
1156 THEN
1157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1158 END IF;
1159
1160 -- Initialize message list if p_init_msg_list is set to TRUE.
1161 IF FND_API.to_Boolean( p_init_msg_list )
1162 THEN
1163 FND_MSG_PUB.initialize;
1164 END IF;
1165
1166 -- Debug Message
1167 --Bug Fix 4652859
1168 IF (AMS_DEBUG_HIGH_ON) THEN
1169 AMS_UTILITY_PVT.debug_message('Private API: ' || L_API_NAME || 'start');
1170 END IF;
1171
1172
1173 -- Initialize API return status to SUCCESS
1174 x_return_status := FND_API.G_RET_STS_SUCCESS;
1175
1176 IF (p_campaign_activity_id IS NOT NULL ) THEN
1177
1178 OPEN c_media_id_exists(p_campaign_activity_id);
1179 FETCH c_media_id_exists INTO l_media_id;
1180 CLOSE c_media_id_exists;
1181
1182 --Added the Media_id=510 for Web Dynamic Rec Activity Bug : 5468790
1183
1184 IF (l_media_id = 30 OR l_media_id = 40 OR l_media_id = 510) THEN
1185
1186 OPEN c_campaign_activity_id_exists(p_campaign_activity_id);
1187 FETCH c_campaign_activity_id_exists INTO l_campaign_activity_id;
1188 CLOSE c_campaign_activity_id_exists;
1189
1190 -- valid association
1191 IF (l_campaign_activity_id IS NOT NULL) THEN
1192
1193 OPEN c_content_id_exists(l_campaign_activity_id);
1194 FETCH c_content_id_exists INTO l_content_item_rec;
1195 CLOSE c_content_id_exists;
1196
1197 IF (l_content_item_rec.content_item_id IS NULL) then
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199 ELSE
1200 x_return_status := FND_API. G_RET_STS_ERROR;
1201 FND_MESSAGE.set_name('AMS', 'AMS_WEB_PLCE_CITEM_NOT_APPR');
1202 FND_MSG_PUB.add;
1203 FND_MSG_PUB.Count_AND_Get
1204 ( p_count => x_msg_count,
1205 p_data => x_msg_data,
1206 p_encoded => FND_API.G_FALSE
1207 );
1208 END IF;
1209 -- Invalid association
1210 ELSE
1211 x_return_status := FND_API. G_RET_STS_ERROR;
1212 FND_MESSAGE.set_name('AMS', 'AMS_WEB_PLCE_ACTIVITY_INVALID');
1213 FND_MSG_PUB.add;
1214 FND_MSG_PUB.Count_AND_Get
1215 ( p_count => x_msg_count,
1216 p_data => x_msg_data,
1217 p_encoded => FND_API.G_FALSE
1218 );
1219 END IF;
1220
1221 ELSE
1222
1223 -- not required to validate
1224 x_return_status := FND_API. G_RET_STS_SUCCESS;
1225
1226 END IF;
1227
1228 ELSE
1229
1230 -- Invalid activity id passed
1231 x_return_status := FND_API. G_RET_STS_ERROR;
1232 FND_MESSAGE.set_name('AMS', 'AMS_WEB_PLCE_NO_ACTIVITY');
1233 FND_MSG_PUB.add;
1234 FND_MSG_PUB.Count_AND_Get
1235 ( p_count => x_msg_count,
1236 p_data => x_msg_data,
1237 p_encoded => FND_API.G_FALSE
1238 );
1239
1240 END IF;
1241
1242
1243 EXCEPTION
1244
1245 WHEN FND_API.G_EXC_ERROR THEN
1246 ROLLBACK TO WEBMARKETING_CONTENT_STATUS_S;
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248 -- Standard call to get message count and if count=1, get the message
1249 FND_MSG_PUB.Count_And_Get (
1250 p_encoded => FND_API.G_FALSE,
1251 p_count => x_msg_count,
1252 p_data => x_msg_data
1253 );
1254
1255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1256 ROLLBACK TO WEBMARKETING_CONTENT_STATUS_S;
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 -- Standard call to get message count and if count=1, get the message
1259 FND_MSG_PUB.Count_And_Get (
1260 p_encoded => FND_API.G_FALSE,
1261 p_count => x_msg_count,
1262 p_data => x_msg_data
1263 );
1264
1265 WHEN OTHERS THEN
1266 ROLLBACK TO WEBMARKETING_CONTENT_STATUS_S;
1267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1268 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1269 THEN
1270 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,L_API_NAME);
1271 END IF;
1272 -- Standard call to get message count and if count=1, get the message
1273 FND_MSG_PUB.Count_And_Get (
1274 p_encoded => FND_API.G_FALSE,
1275 p_count => x_msg_count,
1276 p_data => x_msg_data
1277 );
1278
1279 End WEBMARKETING_CONTENT_STATUS;
1280
1281
1282 END AMS_WEBMARKETING_PVT;
1283