[Home] [Help]
PACKAGE BODY: APPS.AMS_ADI_CAMP_SCHED_PVT
Source
1 PACKAGE BODY AMS_ADI_CAMP_SCHED_PVT AS
2 /* $Header: amsvadsb.pls 120.5 2006/03/14 03:00:27 mayjain noship $ */
3
4 type onameArray is table of ams_adi_campaigns_interface.object_name%type index by binary_integer;
5 type oidArray is table of ams_adi_campaigns_interface.object_id%type index by binary_integer;
6 type scodeArray is table of ams_adi_campaigns_interface.source_code%type index by binary_integer;
7 type dateArray is table of ams_adi_campaigns_interface.start_date%type index by binary_integer;
8 type timeArray is table of ams_adi_campaigns_interface.start_time%type index by binary_integer;
9 type objectiveArray is table of ams_adi_campaigns_interface.objective%type index by binary_integer;
10 type lookupCodeArray is table of ams_adi_campaigns_interface.approval_action_code%type index by binary_integer;
11 type flexAttrArray is table of ams_adi_campaigns_interface.attribute1%type index by binary_integer;
12 type flexContextArray is table of ams_adi_campaigns_interface.attribute_category%type index by binary_integer;
13 /* types added by mayjain */
14 type notesArray is table of ams_adi_campaigns_interface.notes%type index by binary_integer;
15 type puwsArray is table of ams_adi_campaigns_interface.pretty_url_website%type index by binary_integer;
16 type wptitleArray is table of ams_adi_campaigns_interface.wp_placement_title%type index by binary_integer;
17 type ctdAhParamArray is table of ams_adi_campaigns_interface.ctd_adhoc_param1%type index by binary_integer;
18 type ctdAhParamValArray is table of ams_adi_campaigns_interface.ctd_adhoc_param_val1%type index by binary_integer;
19 /* types added by mayjain */
20
21
22
23 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26
27 PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_ADI_CAMP_SCHED_PVT';
28
29 ADI_DATE_FORMAT CONSTANT VARCHAR2(15) := 'DD-MON-RRRR';
30 ADI_TIME_FORMAT CONSTANT VARCHAR2(15) := 'HH24:MI:SS';
31
32 function Get_System_Status_Code(
33 p_user_status_id IN NUMBER,
34 p_system_status_type IN VARCHAR2
35 )
36 return VARCHAR2
37 IS
38 BEGIN
39 null;
40 END;
41
42
43 function Get_Activity_Type_Code (
44 p_activity_id IN NUMBER
45 )
46 return VARCHAR2;
47
48
49 --========================================================================
50 -- PROCEDURE
51 -- Updates Campaign Schedules from Web ADI
52 -- Purpose
53 -- Updates Campaign Schedules based on Web ADI input in staging table AMS_ADI_CAMPAIGNS_INTERFACE
54 -- The algorithm is as follows :
55 -- 1. call AMS_ADI_COMMON_PVT.init
56 -- 2. BULK COLLECT data from staging table in batches of AMS_ADI_COMMON_PVT.g_batch_size(set as 100). Then for
57 -- each batch do the following :
58 -- 2.1. call AMS_ADI_COMMON_PVT.init_for_batch
59 -- 2.2 repeat the following Steps for each Row
60 -- 2.2.1 call Schedule UpdateAPI for each ROW
61 -- 2.2.2 if successful, call AMS_ADI_COMMON_PVT.handleSuccesRow
62 -- else call AMS_ADI_COMMON_PVT.handleErrorRow
63 -- 2.3 when done with batch, call AMS_ADI_COMMON_PVT.done_with_batch
64 -- 3. call AMS_ADI_COMMON_PVT.done_with_all_rows
65 -- HISTORY
66 --
67 --========================================================================
68 PROCEDURE update_campaign_schedules(
69 x_errbuf OUT NOCOPY VARCHAR2,
70 x_retcode OUT NOCOPY NUMBER,
71 p_upload_batch_id IN NUMBER,
72 p_ui_instance_id IN NUMBER := 0
73 )
74 IS
75 PRAGMA AUTONOMOUS_TRANSACTION;
76 L_API_NAME CONSTANT VARCHAR2(30) := 'update_campaign_schedules';
77 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
78 l_schedule_rec AMS_Camp_Schedule_PUB.schedule_rec_type;
79 l_error_recs AMS_ADI_COMMON_PVT.ams_adi_error_rec_t := AMS_ADI_COMMON_PVT.ams_adi_error_rec_t();
80 l_batch_size PLS_INTEGER := AMS_ADI_COMMON_PVT.g_batch_size;
81 l_return_status VARCHAR2(1);
82 l_msg_count NUMBER;
83 l_msg_data VARCHAR2(4000);
84 l_object_version_number NUMBER;
85 l_avail_default_user_status NUMBER;
86
87 cursor c(l_upload_batch_id NUMBER) is
88 select a.object_id,a.object_name,a.parent_object_id,a.source_code,a.start_date,a.end_date,a.start_time,a.end_time,
89 a.marketing_medium_id,a.country_id,a.owner_user_id,a.timezone_id,a.objective,a.approval_action_code,a.user_status_id,a.status_code,
90 a.attribute1,a.attribute2,a.attribute3,a.attribute4,a.attribute5,a.attribute6,a.attribute7,a.attribute8,
91 a.attribute9,a.attribute10,a.attribute11,a.attribute12,a.attribute13,a.attribute14,a.attribute15,
92 a.activity_attribute1,a.activity_attribute2,a.activity_attribute3,a.activity_attribute4,a.activity_attribute5,
93 a.activity_attribute6,a.activity_attribute7,a.activity_attribute8,a.activity_attribute9,a.activity_attribute10,
94 a.activity_attribute11,a.activity_attribute12,a.activity_attribute13,a.activity_attribute14,a.activity_attribute15,
95 a.activity_attribute_category,a.object_version_number,a.currency_code,a.actual_activity_id,b.media_type_code
96 from ams_adi_campaigns_interface a, ams_media_b b
97 where a.upload_batch_id = l_upload_batch_id
98 and a.actual_activity_id = b.media_id(+)
99 and b.enabled_flag(+) = 'Y';
100
101 l_objectIds oidArray ;
102 l_objectNames onameArray;
103 l_parentIds oidArray ;
104 l_srcCodes scodeArray;
105 l_startDates dateArray;
106 l_endDates dateArray;
107 l_startTimes timeArray;
108 l_endTimes timeArray;
109 l_mktMediumIds oidArray;
110 l_countryIds oidArray;
111 l_ownerIds oidArray;
112 l_timezoneIds oidArray;
113 l_objectives objectiveArray;
114 l_apprActionCodes lookupCodeArray;
115 l_nextStatusIds oidArray;
116 l_nextStatusCodes lookupCodeArray;
117 l_currencyCodes lookupCodeArray;
118 l_attribute1s flexAttrArray;
119 l_attribute2s flexAttrArray;
120 l_attribute3s flexAttrArray;
121 l_attribute4s flexAttrArray;
122 l_attribute5s flexAttrArray;
123 l_attribute6s flexAttrArray;
124 l_attribute7s flexAttrArray;
125 l_attribute8s flexAttrArray;
126 l_attribute9s flexAttrArray;
127 l_attribute10s flexAttrArray;
128 l_attribute11s flexAttrArray;
129 l_attribute12s flexAttrArray;
130 l_attribute13s flexAttrArray;
131 l_attribute14s flexAttrArray;
132 l_attribute15s flexAttrArray;
133 l_actAttribute1s flexAttrArray;
134 l_actAttribute2s flexAttrArray;
135 l_actAttribute3s flexAttrArray;
136 l_actAttribute4s flexAttrArray;
137 l_actAttribute5s flexAttrArray;
138 l_actAttribute6s flexAttrArray;
139 l_actAttribute7s flexAttrArray;
140 l_actAttribute8s flexAttrArray;
141 l_actAttribute9s flexAttrArray;
142 l_actAttribute10s flexAttrArray;
143 l_actAttribute11s flexAttrArray;
144 l_actAttribute12s flexAttrArray;
145 l_actAttribute13s flexAttrArray;
146 l_actAttribute14s flexAttrArray;
147 l_actAttribute15s flexAttrArray;
148 l_actContexts flexContextArray;
149 l_objVersionNos oidArray;
150 l_activityIds oidArray;
151 l_activityTypes lookupCodeArray;
152
153 BEGIN
154 FND_MSG_PUB.initialize;
155
156 -- Debug Message
157 IF (AMS_DEBUG_HIGH_ON) THEN
158 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start with batch id : '||p_upload_batch_id||' ui instance id : '||p_ui_instance_id);
159 END IF;
160
161
162 -- Initialize API return status to SUCCESS
163 l_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 select user_status_id into l_avail_default_user_status
166 from ams_user_statuses_vl
167 where system_status_type = 'AMS_CAMPAIGN_SCHEDULE_STATUS'
168 and system_status_code = 'AVAILABLE'
169 and default_flag = 'Y';
170
171
172 AMS_ADI_COMMON_PVT.init();
173
174 -- Debug Message
175 IF (AMS_DEBUG_HIGH_ON) THEN
176 AMS_UTILITY_PVT.debug_message('Start to read feom ams_adi_campaigns_interface with limit '||AMS_ADI_COMMON_PVT.g_batch_size);
177 END IF;
178
179 open c(p_upload_batch_id);
180
181 --fetches data using BULK COLLECT but limited to AMS_ADI_COMMON_PVT.g_batch_size each time
182 loop
183 fetch c BULK COLLECT
184 into l_objectIds,l_objectNames,l_parentIds,l_srcCodes,l_startDates,l_endDates,l_startTimes,l_endTimes,l_mktMediumIds,
185 l_countryIds,l_ownerIds,l_timezoneIds,l_objectives,l_apprActionCodes,l_nextStatusIds,l_nextStatusCodes,
186 l_attribute1s,l_attribute2s,l_attribute3s,l_attribute4s,l_attribute5s,l_attribute6s,l_attribute7s,l_attribute8s,
187 l_attribute9s,l_attribute10s,l_attribute11s,l_attribute12s,l_attribute13s,l_attribute14s,l_attribute15s,
188 l_actAttribute1s,l_actAttribute2s,l_actAttribute3s,l_actAttribute4s,l_actAttribute5s,l_actAttribute6s,l_actAttribute7s,
189 l_actAttribute8s,l_actAttribute9s,l_actAttribute10s,l_actAttribute11s,l_actAttribute12s,l_actAttribute13s,l_actAttribute14s,
190 l_actAttribute15s,l_actContexts,l_objVersionNos,l_currencyCodes,l_activityIds,l_activityTypes
191 limit AMS_ADI_COMMON_PVT.g_batch_size;
192
193
194 -- Debug Message
195 IF (AMS_DEBUG_HIGH_ON) THEN
196 AMS_UTILITY_PVT.debug_message('done with fetch');
197 END IF;
198
199 -- Debug Message
200 IF (AMS_DEBUG_HIGH_ON) THEN
201 AMS_UTILITY_PVT.debug_message('calling init_for_batch');
202 END IF;
203
204 AMS_ADI_COMMON_PVT.init_for_batch(l_error_recs); --initialize batch operation
205
206 -- Debug Message
207 IF (AMS_DEBUG_HIGH_ON) THEN
208 AMS_UTILITY_PVT.debug_message('Done init_for_batch with loopsize '||l_objectIds.COUNT);
209 END IF;
210
211 FOR i in l_objectIds.FIRST .. l_objectIds.LAST LOOP
212
213 SAVEPOINT EXPORT_SCHEDULE;
214
215 IF (AMS_DEBUG_HIGH_ON) THEN
216 AMS_UTILITY_PVT.debug_message('inside loop');
217 END IF;
218
219 --create a Schedule record object for calling Scheule Public API
220
221 l_schedule_rec.schedule_id := l_objectIds(i);
222 l_schedule_rec.schedule_name := l_objectNames(i);
223 IF(l_srcCodes(i) IS NOT NULL) THEN
224 l_schedule_rec.source_code := l_srcCodes(i);
225 END IF;
226 IF(l_startTimes(i) IS NOT NULL) THEN
227 l_schedule_rec.start_date_time := to_date (to_char(l_startDates(i), ADI_DATE_FORMAT) || ' ' ||l_startTimes(i), ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT);
228 ELSE
229 l_schedule_rec.start_date_time := trunc(l_startDates(i));
230 END IF;
231 IF(l_endDates(i) IS NOT NULL) THEN
232 l_schedule_rec.end_date_time := to_date (to_char(l_endDates(i), ADI_DATE_FORMAT) || ' ' ||l_endTimes(i), ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT);
233 ELSE
234 l_schedule_rec.end_date_time := NULL;
235 END IF;
236 IF(l_activityIds(i) IS NOT NULL AND l_activityTypes(i) IS NOT NULL) THEN
237 l_schedule_rec.activity_id := l_activityIds(i);
238 l_schedule_rec.activity_type_code := l_activityTypes(i);
239 END IF;
240 IF(l_mktMediumIds(i) IS NOT NULL AND l_activityIds(i) IS NOT NULL) THEN
241 l_schedule_rec.marketing_medium_id := l_mktMediumIds(i);
242 END IF;
243 IF(l_countryIds(i) IS NOT NULL) THEN
244 l_schedule_rec.country_id := l_countryIds(i);
245 END IF;
246 l_schedule_rec.owner_user_id := l_ownerIds(i);
247 IF(l_timezoneIds(i) IS NOT NULL) THEN
248 l_schedule_rec.timezone_id := l_timezoneIds(i);
249 END IF;
250 IF(l_objectives(i) IS NOT NULL) THEN
251 l_schedule_rec.description := l_objectives(i);
252 END IF;
253 IF(l_nextStatusIds(i) IS NOT NULL) THEN
254 l_schedule_rec.user_status_id := l_nextStatusIds(i);
255 l_schedule_rec.status_code := l_nextStatusCodes(i);
256 IF (AMS_DEBUG_HIGH_ON) THEN
257 AMS_UTILITY_PVT.debug_message('next status id : '||TO_CHAR(l_schedule_rec.user_status_id));
258 AMS_UTILITY_PVT.debug_message('next status code : '||l_schedule_rec.status_code);
259 END IF;
260 END IF;
261
262 l_schedule_rec.object_version_number := l_objVersionNos (i);
263
264 IF(l_currencyCodes(i) IS NOT NULL) THEN
265 l_schedule_rec.transaction_currency_code := l_currencyCodes(i);
266 END IF;
267
268 l_schedule_rec.attribute1 := l_attribute1s(i);
269 l_schedule_rec.attribute2 := l_attribute2s(i);
270 l_schedule_rec.attribute3 := l_attribute3s(i);
271 l_schedule_rec.attribute4 := l_attribute4s(i);
272 l_schedule_rec.attribute5 := l_attribute5s(i);
273 l_schedule_rec.attribute6 := l_attribute6s(i);
274 l_schedule_rec.attribute7 := l_attribute7s(i);
275 l_schedule_rec.attribute8 := l_attribute8s(i);
276 l_schedule_rec.attribute9 := l_attribute9s(i);
277 l_schedule_rec.attribute10 := l_attribute10s(i);
278 l_schedule_rec.attribute11 := l_attribute11s(i);
279 l_schedule_rec.attribute12 := l_attribute12s(i);
280 l_schedule_rec.attribute13 := l_attribute13s(i);
281 l_schedule_rec.attribute14 := l_attribute14s(i);
282 l_schedule_rec.attribute15 := l_attribute15s(i);
283
284 l_schedule_rec.activity_attribute1 := l_actAttribute1s(i);
285 l_schedule_rec.activity_attribute2 := l_actAttribute2s(i);
286 l_schedule_rec.activity_attribute3 := l_actAttribute3s(i);
287 l_schedule_rec.activity_attribute4 := l_actAttribute4s(i);
288 l_schedule_rec.activity_attribute5 := l_actAttribute5s(i);
289 l_schedule_rec.activity_attribute6 := l_actAttribute6s(i);
290 l_schedule_rec.activity_attribute7 := l_actAttribute7s(i);
291 l_schedule_rec.activity_attribute8 := l_actAttribute8s(i);
292 l_schedule_rec.activity_attribute9 := l_actAttribute9s(i);
293 l_schedule_rec.activity_attribute10 := l_actAttribute10s(i);
294 l_schedule_rec.activity_attribute11 := l_actAttribute11s(i);
295 l_schedule_rec.activity_attribute12 := l_actAttribute12s(i);
296 l_schedule_rec.activity_attribute13 := l_actAttribute13s(i);
297 l_schedule_rec.activity_attribute14 := l_actAttribute14s(i);
298 l_schedule_rec.activity_attribute15 := l_actAttribute15s(i);
299
300 -- Debug Message
301 IF (AMS_DEBUG_HIGH_ON) THEN
302 AMS_UTILITY_PVT.debug_message('Calling AMS_Camp_Schedule_PUB.Update_Camp_Schedule');
303 END IF;
304
305 AMS_Camp_Schedule_PUB.Update_Camp_Schedule(
306 1.0,
307 FND_API.G_FALSE,
308 FND_API.G_FALSE,
309 FND_API.g_valid_level_full,
310 l_return_status,
311 l_msg_count,
312 l_msg_data,
313 l_schedule_rec,
314 l_object_version_number
315 );
316
317 -- Debug Message
318 IF (AMS_DEBUG_HIGH_ON) THEN
319 AMS_UTILITY_PVT.debug_message('Return Status from Update_Camp_Schedule '||l_return_status);
320 END IF;
321
322 -- Approvals Integration Starts here
323 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
324 IF (l_apprActionCodes(i) = 'COMPLETE') THEN
325 IF (AMS_DEBUG_HIGH_ON) THEN
326 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name || ' Schedule Update before Approval was successful '||TO_CHAR(l_objectIds(i)));
327 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name || ' Now proceeding with Approval');
328 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name || ' Now calling AMS_ScheduleRules_PVT.Update_Schedule_Status');
329 END IF;
330
331 BEGIN
332 AMS_ScheduleRules_PVT.Update_Schedule_Status( p_schedule_id => l_objectIds(i),
333 p_campaign_id => l_parentIds(i),
334 p_user_status_id => AMS_UTILITY_PVT.get_default_user_status
335 (p_status_type => 'AMS_CAMPAIGN_SCHEDULE_STATUS',
336 p_status_code => 'AVAILABLE'),
337 p_budget_amount => null
338 );
339 EXCEPTION
340 WHEN FND_API.G_EXC_ERROR THEN
341 l_return_status := FND_API.G_RET_STS_ERROR;
342 END;
343
344 IF (AMS_DEBUG_HIGH_ON) THEN
345 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
346 ' Return Status from AMS_ScheduleRules_PVT.Update_Schedule_Status API '||l_return_status);
347 END IF;
348 END IF;
349 END IF;
350 -- Approvals Integration Ends here
351
352 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
353 AMS_ADI_COMMON_PVT.handle_success_row(FND_API.G_FALSE); -- do not need commit for every row
354 ELSE
355 ROLLBACK TO EXPORT_SCHEDULE; --rollback to save_point if errors
356
357 AMS_ADI_COMMON_PVT.handle_error_row(
358 FND_API.G_TRUE,
359 FND_API.G_FALSE, -- do not need a rollback from procedure
360 NULL,
361 NULL,
362 l_objectIds(i),
363 NULL,
364 NULL,
365 l_error_recs --the table containing error records
366 );
367 END IF;
368
369 FND_MSG_PUB.initialize; --initializes message table for next loop
370
371 END LOOP; --end inner loop for batch size
372
373 FND_MSG_PUB.initialize; --initializes message table
374
375 -- Debug Message
376 IF (AMS_DEBUG_HIGH_ON) THEN
377 AMS_UTILITY_PVT.debug_message('Calling Complete batch');
378 END IF;
379
380 AMS_ADI_COMMON_PVT.complete_batch(
381 'AMS_ADI_CAMPAIGNS_INTERFACE',
382 p_upload_batch_id,
383 FND_API.G_TRUE,
384 FND_API.G_FALSE, -- do not need COMMIT for batches as well
385 l_error_recs
386 );
387
388 exit when c%notfound;
389
390 end loop;
391
392 close c;
393
394 -- Debug Message
395 IF (AMS_DEBUG_HIGH_ON) THEN
396 AMS_UTILITY_PVT.debug_message('Calling Complete all');
397 END IF;
398
399 AMS_ADI_COMMON_PVT.complete_all(FND_API.G_TRUE,FND_API.G_TRUE,p_upload_batch_id); --commit everything here only!
400
401 -- Debug Message
402 IF (AMS_DEBUG_HIGH_ON) THEN
403 AMS_UTILITY_PVT.debug_message('Done with all');
404 END IF;
405
406 EXCEPTION
407
408 WHEN FND_API.G_EXC_ERROR THEN
409 ROLLBACK TO EXPORT_SCHEDULE; --rollback to save_point if errors
410
411
412 IF (c%ISOPEN)
413 THEN
414 CLOSE c;
415 END IF;
416
417 AMS_ADI_COMMON_PVT.handle_fatal_error();
418 x_retcode := 2;
419 x_errbuf := SQLERRM;
420
421 RAISE;
422
423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
424 ROLLBACK TO EXPORT_SCHEDULE; --rollback to save_point if errors
425
426 IF (c%ISOPEN)
427 THEN
428 CLOSE c;
429 END IF;
430
431 AMS_ADI_COMMON_PVT.handle_fatal_error();
432 RAISE;
433
434 WHEN OTHERS THEN
435 ROLLBACK TO EXPORT_SCHEDULE; --rollback to save_point if errors
436
437 IF (c%ISOPEN)
438 THEN
439 CLOSE c;
440 END IF;
441
442 AMS_ADI_COMMON_PVT.handle_fatal_error();
443 RAISE;
444 END update_campaign_schedules;
445
446 --========================================================================
447 -- PROCEDURE Handle_Ret_Status_For_Import
448 -- handle Return Status for Import
449 -- Purpose
450 --
451 -- HISTORY
452 --
453 --========================================================================
454
455 procedure Handle_Ret_Status_For_Import (
456 p_return_status IN VARCHAR2,
457 p_object_name IN VARCHAR2,
458 p_parent_object_id IN NUMBER,
459 p_error_records IN OUT NOCOPY AMS_ADI_COMMON_PVT.ams_adi_error_rec_t,
460 p_commit IN VARCHAR2 := FND_API.G_FALSE,
461 p_purge_metrics IN VARCHAR2 := FND_API.G_FALSE
462 );
463
464 --========================================================================
465 -- PROCEDURE Associate_Product_Category
466 -- Product/Category Association
467 -- Purpose
468 --
469 -- HISTORY
470 --
471 --========================================================================
472
473 procedure Associate_Product_Category(
474 p_product_id IN NUMBER,
475 p_category_id IN NUMBER,
476 p_schedule_id IN NUMBER,
477 p_primary_flag IN VARCHAR2,
478 x_act_product_id OUT NOCOPY NUMBER,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2
482 );
483
484 --========================================================================
485 -- PROCEDURE Associate_Collaboration_Item
486 -- Collaboration Association API CALL
487 -- Purpose
488 --
489 -- HISTORY
490 --
491 --========================================================================
492 procedure Associate_Collaboration_Item(
493 p_collab_type IN VARCHAR2,
494 p_collab_assoc_id IN NUMBER,
495 p_schedule_id IN NUMBER,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2
499 );
500
501 --========================================================================
502 -- PROCEDURE Associate_Web_Planner
503 -- Collaboration Association API CALL
504 -- Purpose
505 --
506 -- HISTORY
507 --
508 --========================================================================
509 PROCEDURE Associate_Web_Planner(
510 p_application_id IN NUMBER,
511 p_placement_id IN NUMBER,
512 p_content_item_id IN NUMBER,
513 p_placement_title IN VARCHAR2,
514 p_activity_id IN NUMBER,
515 p_schedule_id IN NUMBER,
516 x_placement_mp_id OUT NOCOPY NUMBER,
517 x_placement_citem_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2,
520 x_return_status OUT NOCOPY VARCHAR2
521 );
522
523 --========================================================================
524 -- PROCEDURE Get_Object_Source_Code
525 -- Get the Source Code of an Object
526 -- Purpose
527 --
528 -- HISTORY
529 --
530 --========================================================================
531 FUNCTION Get_Object_Source_Code(
532 p_object_type IN VARCHAR2,
533 p_object_id IN NUMBER
534 ) RETURN VARCHAR2;
535
536
537
538 --========================================================================
539 -- PROCEDURE
540 -- Imports New Campaign Schedules from Web ADI
541 -- Purpose
542 -- Imports New Campaign Schedules based on Web ADI input in staging table AMS_ADI_CAMPAIGNS_INTERFACE
543 -- The algorithm is as follows :
544 -- 1. call AMS_ADI_COMMON_PVT.init
545 -- 2. BULK COLLECT data from staging table in batches of AMS_ADI_COMMON_PVT.g_batch_size(set as 100). Then for
546 -- each batch do the following :
547 -- 2.1. call AMS_ADI_COMMON_PVT.init_for_batch
548 -- 2.2 repeat the following Steps for each Row
549 -- 2.2.1 call Schedule Create API for each ROW
550 -- 2.2.2 if successful, call AMS_ADI_COMMON_PVT.handleSuccesRow
551 -- else call AMS_ADI_COMMON_PVT.handleErrorRow
552 -- 2.3 when done with batch, call AMS_ADI_COMMON_PVT.done_with_batch
553 -- 3. call AMS_ADI_COMMON_PVT.done_with_all_rows
554 -- HISTORY
555 --
556 --========================================================================
557
558 PROCEDURE Import_Campaign_Schedules(
559 x_errbuf OUT NOCOPY VARCHAR2,
560 x_retcode OUT NOCOPY VARCHAR2,
561 p_upload_batch_id IN NUMBER,
562 p_ui_instance_id IN NUMBER := 0
563 )
564 IS
565
566 L_API_NAME CONSTANT VARCHAR2(30) := 'IMPORT_CAMPAIGN_SCHEDULES';
567 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
568 l_schedule_rec AMS_Camp_Schedule_PUB.schedule_rec_type;
569 l_error_recs AMS_ADI_COMMON_PVT.ams_adi_error_rec_t := AMS_ADI_COMMON_PVT.ams_adi_error_rec_t();
570 l_batch_size PLS_INTEGER := AMS_ADI_COMMON_PVT.g_batch_size;
571 l_return_status VARCHAR2(1);
572 l_msg_count NUMBER;
573 l_msg_data VARCHAR2(4000);
574 l_start_date_time DATE;
575 l_end_date_time DATE;
576 l_object_version_number NUMBER;
577 l_schedule_id NUMBER;
578 l_temp_num NUMBER;
579 l_note_id NUMBER;
580
581 l_assoc_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
582 l_assoc_objects1 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
583
584 l_placement_mp_id NUMBER;
585 l_placement_citem_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
586 l_ctd_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
587 l_tmp_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
588 l_activity_type_code VARCHAR2(30);
589 l_ctd_id NUMBER;
590 l_primary_flag VARCHAR2(1);
591 l_ctd_act_product_id NUMBER;
592
593
594
595 -- cursor C_IMPORT_BATCH
596 cursor C_IMPORT_BATCH(l_upload_batch_id NUMBER) is
597 SELECT
598 object_name, source_code, parent_object_id, start_date, end_date,
599 start_time, end_time, template_id, actual_activity_id, marketing_medium_id,
600 purpose, country_id, owner_user_id, timezone_id, currency_code,
601 objective, approval_action_code, rep_sched_freq_code, rep_sched_frequency, rep_sched_tg_exclude_flag,
602 product_id1, product_id2, product_category_id1, product_category_id2, cover_letter_id,
603 collab_citem_id1, collab_citem_id2, collab_citem_id3, collab_script_id,
604 notes, pretty_url_website, additional_url_param,
605 wp_application_id, wp_placement_id, wp_placement_title, wp_citem_id,
606 ctd_action, ctd_param1, ctd_param2, ctd_param3, ctd_param4, ctd_url_text,
607 ctd_adhoc_param1, ctd_adhoc_param_val1, ctd_adhoc_param2, ctd_adhoc_param_val2, ctd_adhoc_param3,
608 ctd_adhoc_param_val3, ctd_adhoc_param4, ctd_adhoc_param_val4, ctd_adhoc_param5, ctd_adhoc_param_val5,
609 attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
610 attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
611 activity_attribute1, activity_attribute2, activity_attribute3, activity_attribute4, activity_attribute5,
612 activity_attribute6, activity_attribute7, activity_attribute8, activity_attribute9, activity_attribute10,
613 activity_attribute11, activity_attribute12, activity_attribute13, activity_attribute14, activity_attribute15
614 --last_update_date, last_updated_by, creation_date, created_by, last_update_login
615 FROM
616 AMS_ADI_CAMPAIGNS_INTERFACE
617 where
618 upload_batch_id = l_upload_batch_id and
619 operation_status = 'NEW' and
620 operation_type = 'IMPORT' and
621 object_type = 'CSCH' and
622 parent_object_type = 'CAMP';
623 -- cursor C_IMPORT_BATCH
624 /*
625 -- cursor C_DOES_COVLETT_EXIST
626 cursor C_DOES_COVLETT_EXIST(p_covlett_id NUMBER) is
627 select
628 1
629 from
630 IBC_CONTENT_ITEMS
631 where
632 content_item_id = p_covlett_id;
633 -- cursor C_DOES_COVLETT_EXIST
634 */
635
636 -- array Declarations
637 l_objectNames onameArray;
638 l_srcCodes scodeArray;
639 l_campIds oidArray;
640 l_startDates dateArray;
641 l_endDates dateArray;
642 l_startTimes timeArray;
643 l_endTimes timeArray;
644 l_templateIds oidArray;
645 l_ActivityIds oidArray;
646 l_mktMediumIds oidArray;
647 l_purposeCodes lookupCodeArray;
648 l_countryIds oidArray;
649 l_ownerIds oidArray;
650 l_timezoneIds oidArray;
651 l_currCodes lookupCodeArray;
652 l_objectives objectiveArray;
653 l_apprActionCodes lookupCodeArray;
654 l_repschedFreqCodes lookupCodeArray;
655 l_repschedFreqs oidArray;
656 l_repschedTgFlags lookupCodeArray;
657 l_prodIds1 oidArray;
658 l_prodIds2 oidArray;
659 l_catIds1 oidArray;
660 l_catIds2 oidArray;
661 l_covLettIds oidArray;
662 l_collabCitemIds1 oidArray;
663 l_collabCitemIds2 oidArray;
664 l_collabCitemIds3 oidArray;
665 l_collabScrIds oidArray;
666 l_notes notesArray;
667 l_puWebsites puwsArray;
668 l_puAddlParam puwsArray;
669 l_wpAppIds oidArray;
670 l_wpPlceIds oidArray;
671 l_wpTitles wptitleArray;
672 l_wpCitemIds oidArray;
673 l_ctdActions oidArray;
674 l_ctdParams1 oidArray;
675 l_ctdParams2 oidArray;
676 l_ctdParams3 oidArray;
677 l_ctdParams4 oidArray;
678 l_ctdUrlText objectiveArray;
679 l_ctdAhParams1 ctdAhParamArray;
680 l_ctdAhParamVals1 ctdAhParamValArray;
681 l_ctdAhParams2 ctdAhParamArray;
682 l_ctdAhParamVals2 ctdAhParamValArray;
683 l_ctdAhParams3 ctdAhParamArray;
684 l_ctdAhParamVals3 ctdAhParamValArray;
685 l_ctdAhParams4 ctdAhParamArray;
686 l_ctdAhParamVals4 ctdAhParamValArray;
687 l_ctdAhParams5 ctdAhParamArray;
688 l_ctdAhParamVals5 ctdAhParamValArray;
689 l_attrCategories flexContextArray;
690 l_attribute1s flexAttrArray;
691 l_attribute2s flexAttrArray;
692 l_attribute3s flexAttrArray;
693 l_attribute4s flexAttrArray;
694 l_attribute5s flexAttrArray;
695 l_attribute6s flexAttrArray;
696 l_attribute7s flexAttrArray;
697 l_attribute8s flexAttrArray;
698 l_attribute9s flexAttrArray;
699 l_attribute10s flexAttrArray;
700 l_attribute11s flexAttrArray;
701 l_attribute12s flexAttrArray;
702 l_attribute13s flexAttrArray;
703 l_attribute14s flexAttrArray;
704 l_attribute15s flexAttrArray;
705 l_actAttribute1s flexAttrArray;
706 l_actAttribute2s flexAttrArray;
707 l_actAttribute3s flexAttrArray;
708 l_actAttribute4s flexAttrArray;
709 l_actAttribute5s flexAttrArray;
710 l_actAttribute6s flexAttrArray;
711 l_actAttribute7s flexAttrArray;
712 l_actAttribute8s flexAttrArray;
713 l_actAttribute9s flexAttrArray;
714 l_actAttribute10s flexAttrArray;
715 l_actAttribute11s flexAttrArray;
716 l_actAttribute12s flexAttrArray;
717 l_actAttribute13s flexAttrArray;
718 l_actAttribute14s flexAttrArray;
719 l_actAttribute15s flexAttrArray;
720 -- array Declarations
721
722 BEGIN
723
724
725 IF (AMS_DEBUG_HIGH_ON) THEN
726 AMS_UTILITY_PVT.Write_Conc_Log('Private API: '|| pkg_name || '.' || l_api_name || 'start with batch id : '||p_upload_batch_id||' ui instance id : '||p_ui_instance_id);
727 END IF;
728
729
730 -- Initialize API return status to SUCCESS
731 l_return_status := FND_API.G_RET_STS_SUCCESS;
732
733 AMS_ADI_COMMON_PVT.init();
734
735 -- Debug Message
736 IF (AMS_DEBUG_HIGH_ON) THEN
737 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Start to read from ams_adi_campaigns_interface with limit '||AMS_ADI_COMMON_PVT.g_batch_size);
738 END IF;
739
740 OPEN C_IMPORT_BATCH(p_upload_batch_id);
741 LOOP -- C_IMPORT_BATCH loop
742
743 FETCH C_IMPORT_BATCH BULK COLLECT
744 INTO
745 l_objectNames, l_srcCodes, l_campIds ,l_startDates ,l_endDates ,
746 l_startTimes,l_endTimes ,l_templateIds ,l_ActivityIds ,l_mktMediumIds,
747 l_purposeCodes ,l_countryIds ,l_ownerIds ,l_timezoneIds,l_currCodes ,
748 l_objectives , l_apprActionCodes , l_repschedFreqCodes , l_repschedFreqs , l_repschedTgFlags ,
749 l_prodIds1 , l_prodIds2 , l_catIds1 , l_catIds2 , l_covLettIds ,
750 l_collabCitemIds1 , l_collabCitemIds2 , l_collabCitemIds3 , l_collabScrIds ,
751 l_notes , l_puWebsites , l_puAddlParam,
752 l_wpAppIds , l_wpPlceIds , l_wpTitles , l_wpCitemIds ,
753 l_ctdActions , l_ctdParams1 , l_ctdParams2 , l_ctdParams3 , l_ctdParams4 , l_ctdUrlText,
754 l_ctdAhParams1 , l_ctdAhParamVals1 , l_ctdAhParams2 , l_ctdAhParamVals2 , l_ctdAhParams3 ,
755 l_ctdAhParamVals3 , l_ctdAhParams4 , l_ctdAhParamVals4 , l_ctdAhParams5 , l_ctdAhParamVals5 ,
756 l_attrCategories , l_attribute1s , l_attribute2s , l_attribute3s , l_attribute4s , l_attribute5s ,
757 l_attribute6s , l_attribute7s , l_attribute8s , l_attribute9s , l_attribute10s , l_attribute11s ,
758 l_attribute12s , l_attribute13s , l_attribute14s , l_attribute15s ,
759 l_actAttribute1s , l_actAttribute2s , l_actAttribute3s , l_actAttribute4s , l_actAttribute5s ,
760 l_actAttribute6s , l_actAttribute7s , l_actAttribute8s , l_actAttribute9s , l_actAttribute10s ,
761 l_actAttribute11s , l_actAttribute12s , l_actAttribute13s , l_actAttribute14s , l_actAttribute15s
762 LIMIT AMS_ADI_COMMON_PVT.g_batch_size;
763
764 -- Debug Message
765 IF (AMS_DEBUG_HIGH_ON) THEN
766 AMS_UTILITY_PVT.Write_Conc_Log('Private API: '|| pkg_name || '.' || l_api_name ||'done with fetch');
767 END IF;
768
769 -- Debug Message
770 IF (AMS_DEBUG_HIGH_ON) THEN
771 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'calling init_for_batch');
772 END IF;
773
774 --initialize batch operation
775 AMS_ADI_COMMON_PVT.init_for_batch(l_error_recs);
776
777 -- Debug Message
778 IF (AMS_DEBUG_HIGH_ON) THEN
779 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Done init_for_batch with loopsize'||l_objectNames.COUNT);
780 END IF;
781
782
783 -- start looping the arrays to call the Schedule Public API
784 FOR i in l_objectNames.FIRST .. l_objectNames.LAST
785 LOOP -- l_objectNames LOOP
786 BEGIN
787 -- SAVEPOINT for each row
788 SAVEPOINT Import_Schedule_PVT;
789 --init the schedule rec type with a gmiss record;
790 --l_schedule_rec := AMS_Camp_Schedule_PUB.g_miss_schedule_rec;
791
792 l_return_status := FND_API.G_RET_STS_SUCCESS;
793 --check for duplicate schedules in the interface table
794 BEGIN
795 SELECT
796 1 into l_temp_num
797 FROM
798 AMS_ADI_CAMPAIGNS_INTERFACE
799 WHERE
800 upload_batch_id = p_upload_batch_id and
801 operation_status = 'NEW' and
802 operation_type = 'IMPORT' and
803 object_type = 'CSCH' and
804 parent_object_type = 'CAMP' and
805 parent_object_id = l_campIds(i) and
806 object_name = l_objectNames(i);
807 EXCEPTION
808 WHEN TOO_MANY_ROWS THEN
809 AMS_Utility_PVT.Error_Message('AMS_CSCH_DUPLICATE_ID');
810 l_return_status := FND_API.G_RET_STS_ERROR;
811 Handle_Ret_Status_For_Import (
812 p_return_status => l_return_status,
813 p_object_name => l_objectNames(i),
814 p_parent_object_id => l_campIds(i),
815 p_error_records => l_error_recs
816 );
817 END;
818
819 IF (l_return_status = FND_API.G_RET_STS_SUCCESS)
820 THEN
821 --populate the schedule record start
822 l_schedule_rec.last_update_date := sysdate;
823 l_schedule_rec.last_updated_by := FND_GLOBAL.USER_ID;
824 l_schedule_rec.creation_date := sysdate;
825 l_schedule_rec.created_by := FND_GLOBAL.USER_ID;
826 l_schedule_rec.last_update_login := FND_GLOBAL.USER_ID;
827 l_schedule_rec.object_version_number := 1;
828
829 l_schedule_rec.campaign_id := l_campIds(i);
830
831 -- get the defult user status from AMS_UTILITY_PVT
832 l_schedule_rec.user_status_id := AMS_UTILITY_PVT.get_default_user_status(p_status_type => 'AMS_CAMPAIGN_SCHEDULE_STATUS',
833 p_status_code => 'NEW');
834
835 l_schedule_rec.status_code := 'NEW';
836 l_schedule_rec.status_date := sysdate;
837
838 l_schedule_rec.source_code := l_srcCodes(i);
839
840 l_schedule_rec.use_parent_code_flag := 'N';
841
842 -- start date and start time need to be appended
843 if (l_startTimes(i) is not NULL)
844 THEN
845 l_start_date_time := to_date (to_char(l_startDates(i), ADI_DATE_FORMAT) || ' ' ||l_startTimes(i), ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT);
846 ELSE
847 l_start_date_time := trunc(l_startDates(i));
848 END IF;
849
850 l_schedule_rec.start_date_time := l_start_date_time;
851
852 IF (AMS_DEBUG_HIGH_ON) THEN
853 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'l_endDates '|| to_char(l_endDates(i), ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT));
854 END IF;
855
856 IF (l_endDates(i) is not NULL)
857 THEN
858 IF (l_endTimes(i) is not NULL)
859 THEN
860 l_end_date_time := to_date (to_char(l_endDates(i), ADI_DATE_FORMAT) || ' ' ||l_endTimes(i), ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT);
861 ELSE
862 l_end_date_time := trunc(l_endDates(i));
863 END IF;
864
865 l_schedule_rec.end_date_time := l_end_date_time;
866 ELSE
867 l_schedule_rec.end_date_time := null;
868 END IF;
869
870 IF (AMS_DEBUG_HIGH_ON) THEN
871 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Start Date Time'|| to_char(l_schedule_rec.start_date_time, ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT));
872 END IF;
873
874 IF (AMS_DEBUG_HIGH_ON) THEN
875 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'End Date Time'|| to_char(l_schedule_rec.end_date_time, ADI_DATE_FORMAT || ' ' || ADI_TIME_FORMAT));
876 END IF;
877
878 l_schedule_rec.timezone_id := l_timezoneIds(i);
879
880 l_schedule_rec.activity_type_code := Get_Activity_Type_Code(l_ActivityIds(i));
881 l_schedule_rec.activity_id := l_ActivityIds(i);
882
883 --l_schedule_rec.arc_marketing_medium_from
884
885 l_schedule_rec.marketing_medium_id := l_mktMediumIds(i);
886
887 l_schedule_rec.custom_setup_id := l_templateIds(i);
888
889 --l_schedule_rec.trigger_id
890 --l_schedule_rec.notify_user_id
891 --l_schedule_rec.l_schedule_rec.approver_user_id
892
893 l_schedule_rec.owner_user_id := l_ownerIds(i);
894 --l_schedule_rec.active_flag
895
896 l_schedule_rec.cover_letter_id := l_covLettIds(i);
897
898 --l_schedule_rec.reply_to_mail
899 --l_schedule_rec.mail_sender_name
900 --l_schedule_rec.mail_subject
901 --l_schedule_rec.from_fax_no
902 l_schedule_rec.accounts_closed_flag := 'N';
903
904 l_schedule_rec.org_id := FND_PROFILE.value('ORG_ID'); -- need to chek this.
905 --l_schedule_rec.objective_code
906 IF (l_countryIds(i) is not null)
907 THEN
908 l_schedule_rec.country_id := l_countryIds(i);
909 ELSE
910 l_schedule_rec.country_id := FND_PROFILE.value('AMS_SRCGEN_USER_CITY');
911 END IF;
912
913
914 --l_schedule_rec.campaign_calendar
915 --l_schedule_rec.start_period_name
916 --l_schedule_rec.end_period_name
917 --l_schedule_rec.priority
918 --l_schedule_rec.l_schedule_rec.workflow_item_key
919 l_schedule_rec.transaction_currency_code := l_currCodes(i);
920
921 --l_schedule_rec.functional_currency_code
922 --l_schedule_rec.budget_amount_tc
923 --l_schedule_rec.budget_amount_fc
924 l_schedule_rec.language_code := 'US'; --there is an issue related to this. we need to change this when the language issue is fixed.
925 --l_schedule_rec.task_id
926 --l_schedule_rec.related_event_from
927 --l_schedule_rec.related_event_id
928
929 l_schedule_rec.attribute_category := l_attrCategories(i);
930 l_schedule_rec.attribute1 := l_attribute1s(i);
931 l_schedule_rec.attribute2 := l_attribute2s(i);
932 l_schedule_rec.attribute3 := l_attribute3s(i);
933 l_schedule_rec.attribute4 := l_attribute4s(i);
934 l_schedule_rec.attribute5 := l_attribute5s(i);
935 l_schedule_rec.attribute6 := l_attribute6s(i);
936 l_schedule_rec.attribute7 := l_attribute7s(i);
937 l_schedule_rec.attribute8 := l_attribute8s(i);
938 l_schedule_rec.attribute9 := l_attribute9s(i);
939 l_schedule_rec.attribute10 := l_attribute10s(i);
940 l_schedule_rec.attribute11 := l_attribute11s(i);
941 l_schedule_rec.attribute12 := l_attribute12s(i);
942 l_schedule_rec.attribute13 := l_attribute13s(i);
943 l_schedule_rec.attribute14 := l_attribute14s(i);
944 l_schedule_rec.attribute15 := l_attribute15s(i);
945
946 l_schedule_rec.activity_attribute1 := l_actAttribute1s(i);
947 l_schedule_rec.activity_attribute2 := l_actAttribute2s(i);
948 l_schedule_rec.activity_attribute3 := l_actAttribute3s(i);
949 l_schedule_rec.activity_attribute4 := l_actAttribute4s(i);
950 l_schedule_rec.activity_attribute5 := l_actAttribute5s(i);
951 l_schedule_rec.activity_attribute6 := l_actAttribute6s(i);
952 l_schedule_rec.activity_attribute7 := l_actAttribute7s(i);
953 l_schedule_rec.activity_attribute8 := l_actAttribute8s(i);
954 l_schedule_rec.activity_attribute9 := l_actAttribute9s(i);
955 l_schedule_rec.activity_attribute10 := l_actAttribute10s(i);
956 l_schedule_rec.activity_attribute11 := l_actAttribute11s(i);
957 l_schedule_rec.activity_attribute12 := l_actAttribute12s(i);
958 l_schedule_rec.activity_attribute13 := l_actAttribute13s(i);
959 l_schedule_rec.activity_attribute14 := l_actAttribute14s(i);
960 l_schedule_rec.activity_attribute15 := l_actAttribute15s(i);
961
962 l_schedule_rec.schedule_name := l_objectNames(i);
963
964 l_schedule_rec.description := l_objectives(i);
965
966 -- bug 5094316
967 IF (l_schedule_rec.activity_type_code = 'EVENTS')
968 THEN
969 l_schedule_rec.related_source_code := null;
970 l_schedule_rec.related_source_object := null;
971 l_schedule_rec.related_source_id := null;
972 END IF;
973 -- bug 5094316
974
975 --l_schedule_rec.query_id
976 --l_schedule_rec.include_content_flag
977 --l_schedule_rec.content_type
978 --l_schedule_rec.test_email_address
979 --l_schedule_rec.greeting_text
980 --l_schedule_rec.footer_text
981
982 -- For Repeating Schedule
983 IF ( (l_repschedFreqCodes(i) is not null) AND (l_repschedFreqCodes(i) <> 'NONE') AND
984 (l_repschedFreqs(i) is not null) AND (l_repschedFreqs(i) > 0) AND
985 (l_repschedTgFlags(i) is not null) AND
986 (l_schedule_rec.activity_type_code <> 'INTERNET')
987 )
988 THEN
989 l_schedule_rec.trig_repeat_flag := 'Y';
990 l_schedule_rec.triggerable_flag := 'N';
991 l_schedule_rec.tgrp_exclude_prev_flag := l_repschedTgFlags(i);
992 ELSE
993 l_schedule_rec.trig_repeat_flag := 'N';
994 END IF;
995
996 --l_schedule_rec.tgrp_exclude_prev_flag
997 --l_schedule_rec.orig_csch_id
998 --l_schedule_rec.cover_letter_version
999 l_schedule_rec.usage := 'LITE';
1000
1001 l_schedule_rec.purpose := l_purposeCodes(i);
1002 --populate the schedule record end
1003
1004 -- Debug Message
1005 IF (AMS_DEBUG_HIGH_ON) THEN
1006 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Calling Campaign Schedule Public API');
1007 END IF;
1008
1009 AMS_Camp_Schedule_PUB.Create_Camp_Schedule(
1010 1.0,
1011 FND_API.G_FALSE,
1012 FND_API.G_FALSE,
1013 FND_API.g_valid_level_full,
1014 l_return_status,
1015 l_msg_count,
1016 l_msg_data,
1017 l_schedule_rec,
1018 l_schedule_id
1019 );
1020 -- Debug Message
1021 IF (AMS_DEBUG_HIGH_ON) THEN
1022 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Return Status from Schedule API'||l_return_status);
1023 END IF;
1024
1025 Handle_Ret_Status_For_Import (
1026 p_return_status => l_return_status,
1027 p_object_name => l_objectNames(i),
1028 p_parent_object_id => l_campIds(i),
1029 p_error_records => l_error_recs
1030 );
1031 END IF;
1032
1033 -- Metrics Integration starts here
1034 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1035 THEN
1036 IF (AMS_DEBUG_HIGH_ON) THEN
1037 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1038 ' Association before Metrics association was successful '||TO_CHAR(l_schedule_id));
1039 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1040 ' Now proceeding with Metrics association');
1041 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1042 ' Now calling AMS_ADI_MEDIA_PLANNER_PVT.LOAD_METRICS API');
1043 END IF;
1044
1045 AMS_ADI_MEDIA_PLANNER_PVT.LOAD_METRICS(
1046 p_api_version => 1.0,
1047 p_init_msg_list => FND_API.G_FALSE,
1048 p_commit => FND_API.G_FALSE,
1049 x_return_status => l_return_status,
1050 x_msg_count => l_msg_count,
1051 x_msg_data => l_msg_data,
1052 p_upload_batch_id => 1, -- workaround
1053 p_object_type => 'CSCH',
1054 p_object_name => l_objectNames(i),
1055 p_parent_type => 'CAMP',
1056 p_parent_id => l_campIds(i),
1057 p_object_id => l_schedule_id
1058 );
1059
1060 IF (AMS_DEBUG_HIGH_ON) THEN
1061 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1062 ' Return Status from AMS_ADI_MEDIA_PLANNER_PVT.LOAD_METRICS API '||l_return_status);
1063 END IF;
1064
1065 Handle_Ret_Status_For_Import (
1066 p_return_status => l_return_status,
1067 p_object_name => l_objectNames(i),
1068 p_parent_object_id => l_campIds(i),
1069 p_error_records => l_error_recs,
1070 p_purge_metrics => FND_API.G_TRUE
1071 );
1072
1073 END IF;
1074 -- Metrics Integration end here
1075
1076 -- Repeat Schedule Integration Start
1077 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1078 THEN
1079 IF ( (l_repschedFreqCodes(i) is not null) AND (l_repschedFreqCodes(i) <> 'NONE') AND
1080 (l_repschedFreqs(i) is not null) AND (l_repschedFreqs(i) > 0) AND
1081 (l_repschedTgFlags(i) is not null) AND
1082 (l_schedule_rec.activity_type_code <> 'INTERNET')
1083 )
1084 THEN
1085 IF (AMS_DEBUG_HIGH_ON) THEN
1086 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation successful '||TO_CHAR(l_schedule_id));
1087 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with repeating schedule association ');
1088 END IF;
1089
1090 AMS_SCHEDULER_PVT.Create_Scheduler(
1091 p_obj_type => 'CSCH',
1092 p_obj_id => l_schedule_id,
1093 p_freq => l_repschedFreqs(i),
1094 p_freq_type => l_repschedFreqCodes(i),
1095 x_msg_count => l_msg_count,
1096 x_msg_data => l_msg_data,
1097 x_return_status => l_return_status,
1098 x_scheduler_id => l_temp_num
1099 );
1100
1101 IF (AMS_DEBUG_HIGH_ON) THEN
1102 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Return Status from AMS_SCHEDULER_PVT.Create_Scheduler API '||l_return_status || ' ' || to_char(l_temp_num));
1103 END IF;
1104
1105 Handle_Ret_Status_For_Import (
1106 p_return_status => l_return_status,
1107 p_object_name => l_objectNames(i),
1108 p_parent_object_id => l_campIds(i),
1109 p_error_records => l_error_recs
1110 );
1111 END IF;
1112 END IF;
1113
1114 -- Repeat Schedule Integration End
1115
1116 -- Cover Letter Integration start
1117 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1118 THEN
1119 IF (AMS_DEBUG_HIGH_ON) THEN
1120 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation successful '||TO_CHAR(l_schedule_id));
1121 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with cover letter association for cover letter '||TO_CHAR(l_covLettIds (i)));
1122 END IF;
1123
1124 IF (l_covLettIds (i) is not null)
1125 THEN
1126 l_assoc_type_codes := JTF_VARCHAR2_TABLE_100();
1127 l_assoc_objects1 := JTF_VARCHAR2_TABLE_300();
1128 l_assoc_type_codes.extend();
1129 l_assoc_objects1.extend();
1130 l_assoc_type_codes(1) := 'AMS_CSCH';
1131 l_assoc_objects1(1) := to_char(l_schedule_id); --where 123456 is the schedule id
1132
1133 IF (AMS_DEBUG_HIGH_ON) THEN
1134 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now calling IBC_CITEM_ADMIN_GRP.insert_associations API');
1135 END IF;
1136 IBC_CITEM_ADMIN_GRP.insert_associations(
1137 p_content_item_id => l_covLettIds(i)
1138 ,p_assoc_type_codes => l_assoc_type_codes
1139 ,p_assoc_objects1 => l_assoc_objects1
1140 ,p_commit => FND_API.g_false
1141 ,x_return_status => l_return_status
1142 ,x_msg_count => l_msg_count
1143 ,x_msg_data => l_msg_data
1144 );
1145
1146 IF (AMS_DEBUG_HIGH_ON) THEN
1147 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Return Status from IBC Association API for Cover Letter '||l_return_status);
1148 END IF;
1149
1150 Handle_Ret_Status_For_Import (
1151 p_return_status => l_return_status,
1152 p_object_name => l_objectNames(i),
1153 p_parent_object_id => l_campIds(i),
1154 p_error_records => l_error_recs
1155 );
1156 END IF;
1157 END IF;
1158 -- Cover Letter Integration end
1159
1160 l_ctd_act_product_id := null;
1161 -- Product Integration starts here
1162 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1163 THEN
1164 IF (AMS_DEBUG_HIGH_ON) THEN
1165 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Cover letter associaiton successful '||TO_CHAR(l_schedule_id));
1166 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with product/category association ');
1167 END IF;
1168
1169
1170 IF ((l_prodIds1(i) is not null) OR (l_catIds1(i) is not null) )
1171 THEN
1172 IF (AMS_DEBUG_HIGH_ON) THEN
1173 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' At least one product/category is present to be associated'||TO_CHAR(l_schedule_id));
1174 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_prodIds1(i) '||TO_CHAR(l_prodIds1(i)));
1175 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_catIds1(i) '||TO_CHAR(l_catIds1(i)));
1176 END IF;
1177
1178 Associate_Product_Category(
1179 p_product_id => l_prodIds1(i),
1180 p_category_id => l_catIds1(i),
1181 p_schedule_id => l_schedule_id,
1182 p_primary_flag => 'Y', -- 31-Aug-2005 mayjain Support for Primary Flag
1183 x_act_product_id => l_ctd_act_product_id,
1184 x_return_status => l_return_status,
1185 x_msg_count => l_msg_count,
1186 x_msg_data => l_msg_data
1187 );
1188
1189 IF (AMS_DEBUG_HIGH_ON) THEN
1190 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_ctd_act_product_id '||TO_CHAR(l_ctd_act_product_id));
1191 END IF;
1192
1193 Handle_Ret_Status_For_Import (
1194 p_return_status => l_return_status,
1195 p_object_name => l_objectNames(i),
1196 p_parent_object_id => l_campIds(i),
1197 p_error_records => l_error_recs
1198 );
1199
1200 END IF;
1201
1202 END IF;
1203
1204 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1205 THEN
1206 IF (AMS_DEBUG_HIGH_ON) THEN
1207 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Product 1 association successful '||TO_CHAR(l_schedule_id));
1208 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with product/category association ');
1209 END IF;
1210
1211 IF ((l_prodIds2(i) is not null) OR (l_catIds2(i) is not null) )
1212 THEN
1213 IF (AMS_DEBUG_HIGH_ON) THEN
1214 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' At least one product/category is present to be associated'||TO_CHAR(l_schedule_id));
1215 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_prodIds2(i) '||TO_CHAR(l_prodIds2(i)));
1216 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_catIds2(i) '||TO_CHAR(l_catIds2(i)));
1217 END IF;
1218
1219 -- 31-Aug-2005 mayjain Support for Primary Flag
1220 l_primary_flag := 'N';
1221
1222 -- 31-Aug-2005 mayjain Support for Primary Flag
1223 IF ((l_prodIds1(i) is null) AND (l_catIds1(i) is null) )
1224 THEN
1225 l_primary_flag := 'Y';
1226 END IF;
1227
1228 Associate_Product_Category(
1229 p_product_id => l_prodIds2(i),
1230 p_category_id => l_catIds2(i),
1231 p_schedule_id => l_schedule_id,
1232 p_primary_flag => l_primary_flag, -- 31-Aug-2005 mayjain Support for Primary Flag
1233 x_act_product_id => l_temp_num,
1234 x_return_status => l_return_status,
1235 x_msg_count => l_msg_count,
1236 x_msg_data => l_msg_data
1237 );
1238
1239 IF ((l_prodIds1(i) is null) AND (l_catIds1(i) is null) )
1240 THEN
1241 l_ctd_act_product_id := l_temp_num;
1242 IF (AMS_DEBUG_HIGH_ON) THEN
1243 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_ctd_act_product_id '||TO_CHAR(l_ctd_act_product_id));
1244 END IF;
1245 END IF;
1246
1247 Handle_Ret_Status_For_Import (
1248 p_return_status => l_return_status,
1249 p_object_name => l_objectNames(i),
1250 p_parent_object_id => l_campIds(i),
1251 p_error_records => l_error_recs
1252 );
1253
1254 END IF;
1255
1256 END IF;
1257 -- Product Integration ends here
1258
1259 -- Collaboration Integration Starts here
1260 IF (l_schedule_rec.activity_type_code <> 'INTERNET')
1261 THEN
1262 -- Content Item 1
1263 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1264 THEN
1265 IF (AMS_DEBUG_HIGH_ON) THEN
1266 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Product 1 association successful '||TO_CHAR(l_schedule_id));
1267 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with Collaboration CItem 1 association ');
1268 END IF;
1269
1270 IF (l_collabCitemIds1(i) is not null)
1271 THEN
1272 IF (AMS_DEBUG_HIGH_ON) THEN
1273 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Collaboration CItem 1 is present to be associated to '||TO_CHAR(l_schedule_id));
1274 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_collabCitemIds1(i) '||TO_CHAR(l_collabCitemIds1(i)));
1275 END IF;
1276
1277 Associate_Collaboration_Item(
1278 p_collab_type => 'AMS_CONTENT',
1279 p_collab_assoc_id => l_collabCitemIds1(i),
1280 p_schedule_id => l_schedule_id,
1281 x_return_status => l_return_status,
1282 x_msg_count => l_msg_count,
1283 x_msg_data => l_msg_data
1284 );
1285
1286 Handle_Ret_Status_For_Import (
1287 p_return_status => l_return_status,
1288 p_object_name => l_objectNames(i),
1289 p_parent_object_id => l_campIds(i),
1290 p_error_records => l_error_recs
1291 );
1292
1293 END IF;
1294
1295 END IF;
1296 -- Content Item 1
1297
1298 -- Content Item 2
1299 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1300 THEN
1301 IF (AMS_DEBUG_HIGH_ON) THEN
1302 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Product 2 association successful '||TO_CHAR(l_schedule_id));
1303 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with Collaboration CItem 2 association ');
1304 END IF;
1305
1306 IF (l_collabCitemIds2(i) is not null)
1307 THEN
1308 IF (AMS_DEBUG_HIGH_ON) THEN
1309 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Collaboration CItem 2 is present to be associated to '||TO_CHAR(l_schedule_id));
1310 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_collabCitemIds2(i) '||TO_CHAR(l_collabCitemIds2(i)));
1311 END IF;
1312
1313 Associate_Collaboration_Item(
1314 p_collab_type => 'AMS_CONTENT',
1315 p_collab_assoc_id => l_collabCitemIds2(i),
1316 p_schedule_id => l_schedule_id,
1317 x_return_status => l_return_status,
1318 x_msg_count => l_msg_count,
1319 x_msg_data => l_msg_data
1320 );
1321
1322 Handle_Ret_Status_For_Import (
1323 p_return_status => l_return_status,
1324 p_object_name => l_objectNames(i),
1325 p_parent_object_id => l_campIds(i),
1326 p_error_records => l_error_recs
1327 );
1328
1329 END IF;
1330
1331 END IF;
1332 -- Content Item 2
1333
1334 -- Content Item 3
1335 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1336 THEN
1337 IF (AMS_DEBUG_HIGH_ON) THEN
1338 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Product 3 association successful '||TO_CHAR(l_schedule_id));
1339 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with Collaboration CItem 3 association ');
1340 END IF;
1341
1342 IF (l_collabCitemIds3(i) is not null)
1343 THEN
1344 IF (AMS_DEBUG_HIGH_ON) THEN
1345 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Collaboration CItem 3 is present to be associated to '||TO_CHAR(l_schedule_id));
1346 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_collabCitemIds3(i) '||TO_CHAR(l_collabCitemIds3(i)));
1347 END IF;
1348
1349 Associate_Collaboration_Item(
1350 p_collab_type => 'AMS_CONTENT',
1351 p_collab_assoc_id => l_collabCitemIds3(i),
1352 p_schedule_id => l_schedule_id,
1353 x_return_status => l_return_status,
1354 x_msg_count => l_msg_count,
1355 x_msg_data => l_msg_data
1356 );
1357
1358 Handle_Ret_Status_For_Import (
1359 p_return_status => l_return_status,
1360 p_object_name => l_objectNames(i),
1361 p_parent_object_id => l_campIds(i),
1362 p_error_records => l_error_recs
1363 );
1364
1365 END IF;
1366
1367 END IF;
1368 -- Content Item 3
1369
1370 -- Script
1371 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1372 THEN
1373 IF (AMS_DEBUG_HIGH_ON) THEN
1374 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Schedule Creation/Content Item 3 association successful '||TO_CHAR(l_schedule_id));
1375 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Now proceeding with Collaboration CItem 3 association ');
1376 END IF;
1377
1378 IF (l_collabScrIds(i) is not null)
1379 THEN
1380 IF (AMS_DEBUG_HIGH_ON) THEN
1381 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' Collaboration CItem 3 is present to be associated to '||TO_CHAR(l_schedule_id));
1382 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||' l_collabScrIds(i) '||TO_CHAR(l_collabScrIds(i)));
1383 END IF;
1384
1385 Associate_Collaboration_Item(
1386 p_collab_type => 'INBOUND_SCRIPT',
1387 p_collab_assoc_id => l_collabScrIds(i),
1388 p_schedule_id => l_schedule_id,
1389 x_return_status => l_return_status,
1390 x_msg_count => l_msg_count,
1391 x_msg_data => l_msg_data
1392 );
1393
1394 Handle_Ret_Status_For_Import (
1395 p_return_status => l_return_status,
1396 p_object_name => l_objectNames(i),
1397 p_parent_object_id => l_campIds(i),
1398 p_error_records => l_error_recs
1399 );
1400
1401 END IF;
1402
1403 END IF;
1404 -- Script
1405 END IF; -- l_schedule_rec.activity_type_code <> 'INTERNET'
1406 -- Collaboration Integration Ends here
1407
1408
1409 -- Web Planner Integration Starts here
1410 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1411 THEN
1412 IF (AMS_DEBUG_HIGH_ON) THEN
1413 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1414 ' Association before Web Planner association was successful '||TO_CHAR(l_schedule_id));
1415 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1416 ' Now proceeding with Web Planner association');
1417 END IF;
1418
1419 IF ( l_schedule_rec.activity_type_code = 'INTERNET')
1420 THEN
1421 IF (AMS_DEBUG_HIGH_ON) THEN
1422 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1423 ' It is a Web Schedule. No checking if WP Details are present');
1424 END IF;
1425
1426 IF ((l_wpAppIds(i) is not null) AND
1427 (l_wpPlceIds(i) is not null) AND
1428 (l_wpCitemIds(i) is not null))
1429 THEN
1430 IF (AMS_DEBUG_HIGH_ON) THEN
1431 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1432 ' Now calling Associate_Web_Planner API');
1433 END IF;
1434
1435 l_placement_citem_id_tbl := JTF_NUMBER_TABLE();
1436
1437 Associate_Web_Planner(
1438 p_application_id => l_wpAppIds(i),
1439 p_placement_id => l_wpPlceIds(i),
1440 p_content_item_id => l_wpCitemIds(i),
1441 p_placement_title => l_wpTitles(i),
1442 p_activity_id => l_schedule_rec.activity_id,
1443 p_schedule_id => l_schedule_id,
1444 x_placement_mp_id => l_placement_mp_id,
1445 x_placement_citem_id_tbl => l_placement_citem_id_tbl,
1446 x_msg_count => l_msg_count,
1447 x_msg_data => l_msg_data,
1448 x_return_status => l_return_status
1449 );
1450
1451 IF (AMS_DEBUG_HIGH_ON) THEN
1452 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1453 ' Return Status from Associate_Web_Planner API '||l_return_status);
1454 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1455 ' Placement Citem ID Table count '||TO_CHAR(l_placement_citem_id_tbl.count));
1456 END IF;
1457
1458 Handle_Ret_Status_For_Import (
1459 p_return_status => l_return_status,
1460 p_object_name => l_objectNames(i),
1461 p_parent_object_id => l_campIds(i),
1462 p_error_records => l_error_recs
1463 );
1464
1465
1466
1467 -- CTD Integration for Web Starts here
1468 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1469 THEN
1470 IF (l_ctdActions(i) is not null)
1471 THEN
1472 IF (AMS_DEBUG_HIGH_ON) THEN
1473 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1474 ' Association before CTD association was successful '||TO_CHAR(l_schedule_id));
1475 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1476 ' Now proceeding with CTD association');
1477 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1478 ' Now calling AMS_ADI_CTD_PVT.CREATE_CTDS API');
1479 END IF;
1480
1481 AMS_ADI_CTD_PVT.CREATE_CTDS(
1482 p_action_id => l_ctdActions(i),
1483 p_parameter_id1 => l_ctdParams1(i),
1484 p_parameter_id2 => l_ctdParams2(i),
1485 p_parameter_id3 => l_ctdParams3(i),
1486 p_url_text => l_ctdUrlText(i),
1487 p_adhoc_param_name1 => l_ctdAhParams1(i),
1488 p_adhoc_param_name2 => l_ctdAhParams2(i),
1489 p_adhoc_param_name3 => l_ctdAhParams3(i),
1490 p_adhoc_param_name4 => l_ctdAhParams4(i),
1491 p_adhoc_param_name5 => l_ctdAhParams5(i),
1492 p_adhoc_param_val1 => l_ctdAhParamVals1(i),
1493 p_adhoc_param_val2 => l_ctdAhParamVals2(i),
1494 p_adhoc_param_val3 => l_ctdAhParamVals3(i),
1495 p_adhoc_param_val4 => l_ctdAhParamVals4(i),
1496 p_adhoc_param_val5 => l_ctdAhParamVals5(i),
1497 p_used_by_id_list => l_placement_citem_id_tbl,
1498 p_schedule_id => l_schedule_id,
1499 p_activity_id => l_schedule_rec.activity_id,
1500 p_schedule_src_code => Get_Object_Source_Code('CSCH', l_schedule_id),
1501 x_ctd_id_list => l_ctd_id_tbl,
1502 x_msg_count => l_msg_count,
1503 x_msg_data => l_msg_data,
1504 x_return_status => l_return_status,
1505 p_activity_product_id => l_ctd_act_product_id
1506 );
1507
1508 IF (AMS_DEBUG_HIGH_ON) THEN
1509 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1510 ' Return Status from AMS_ADI_CTD_PVT.CREATE_CTDS API '||l_return_status);
1511 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1512 ' CTD ID Table count '||TO_CHAR(l_ctd_id_tbl.count));
1513 END IF;
1514
1515 Handle_Ret_Status_For_Import (
1516 p_return_status => l_return_status,
1517 p_object_name => l_objectNames(i),
1518 p_parent_object_id => l_campIds(i),
1519 p_error_records => l_error_recs
1520 );
1521
1522 END IF;
1523 END IF;
1524 -- CTD Integration for Web Ends here
1525
1526 END IF;
1527 END IF;
1528 END IF;
1529 -- Web Planner Integration Ends here
1530
1531 -- Pretty URL Association Starts here
1532 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1533 THEN
1534 IF (l_puWebsites(i) is not null)
1535 THEN
1536 IF (AMS_DEBUG_HIGH_ON) THEN
1537 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1538 ' Pretty URL detail is present. Now checking for Activity Type ' || l_schedule_rec.activity_type_code);
1539 END IF;
1540
1541 IF ((l_schedule_rec.activity_type_code = 'BROADCAST') OR
1542 (l_schedule_rec.activity_type_code = 'PUBLIC_RELATIONS' ) OR
1543 (l_schedule_rec.activity_type_code = 'IN_STORE') OR
1544 ((l_schedule_rec.activity_type_code = 'DIRECT_MARKETING') AND
1545 (l_schedule_rec.activity_id <> 20) AND -- Exclude Email
1546 (l_schedule_rec.activity_id <> 460))) -- Exclude Telemarketing
1547 THEN
1548 l_ctd_id := null;
1549
1550 IF (l_ctdActions(i) is not null)
1551 THEN
1552 IF (AMS_DEBUG_HIGH_ON) THEN
1553 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1554 ' Association before Pretty URL CTD association was successful '||TO_CHAR(l_schedule_id));
1555 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1556 ' Now proceeding with Pretty URL CTD association');
1557 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1558 ' Now calling AMS_ADI_CTD_PVT.CREATE_CTDS API');
1559 END IF;
1560
1561 l_tmp_tbl := JTF_NUMBER_TABLE();
1562 l_tmp_tbl.extend;
1563 l_tmp_tbl(1) := -1;
1564
1565 AMS_ADI_CTD_PVT.CREATE_CTDS(
1566 p_action_id => l_ctdActions(i),
1567 p_parameter_id1 => l_ctdParams1(i),
1568 p_parameter_id2 => l_ctdParams2(i),
1569 p_parameter_id3 => l_ctdParams3(i),
1570 p_url_text => l_ctdUrlText(i),
1571 p_adhoc_param_name1 => l_ctdAhParams1(i),
1572 p_adhoc_param_name2 => l_ctdAhParams2(i),
1573 p_adhoc_param_name3 => l_ctdAhParams3(i),
1574 p_adhoc_param_name4 => l_ctdAhParams4(i),
1575 p_adhoc_param_name5 => l_ctdAhParams5(i),
1576 p_adhoc_param_val1 => l_ctdAhParamVals1(i),
1577 p_adhoc_param_val2 => l_ctdAhParamVals2(i),
1578 p_adhoc_param_val3 => l_ctdAhParamVals3(i),
1579 p_adhoc_param_val4 => l_ctdAhParamVals4(i),
1580 p_adhoc_param_val5 => l_ctdAhParamVals5(i),
1581 p_used_by_id_list => l_tmp_tbl,
1582 p_schedule_id => l_schedule_id,
1583 p_activity_id => l_schedule_rec.activity_id,
1584 p_schedule_src_code => Get_Object_Source_Code('CSCH', l_schedule_id),
1585 x_ctd_id_list => l_ctd_id_tbl,
1586 x_msg_count => l_msg_count,
1587 x_msg_data => l_msg_data,
1588 x_return_status => l_return_status,
1589 p_activity_product_id => l_ctd_act_product_id
1590 );
1591
1592 IF (AMS_DEBUG_HIGH_ON) THEN
1593 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1594 ' Return Status from AMS_ADI_CTD_PVT.CREATE_CTDS API '||l_return_status);
1595 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1596 ' CTD ID Table count '||TO_CHAR(l_ctd_id_tbl.count));
1597 END IF;
1598
1599 Handle_Ret_Status_For_Import (
1600 p_return_status => l_return_status,
1601 p_object_name => l_objectNames(i),
1602 p_parent_object_id => l_campIds(i),
1603 p_error_records => l_error_recs
1604 );
1605
1606 l_ctd_id := l_ctd_id_tbl(1);
1607
1608 END IF; -- (l_ctdActions(i) is not null)
1609
1610 -- Now call Pretty URL API
1611 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1612 THEN
1613 IF (AMS_DEBUG_HIGH_ON) THEN
1614 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1615 ' Pretty URL CTD association was successful '||TO_CHAR(l_schedule_id));
1616 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1617 ' Now proceeding with Pretty URL association');
1618 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1619 ' Now calling AMS_ADI_PRETTY_URL_PVT.CREATE_PRETTY_URL API');
1620 END IF;
1621
1622 AMS_ADI_PRETTY_URL_PVT.CREATE_PRETTY_URL(
1623 p_pretty_url => l_puWebsites(i),
1624 p_add_url_param => l_puAddlParam(i),
1625 p_ctd_id => l_ctd_id,
1626 p_schedule_id => l_schedule_id,
1627 p_activity_id => l_schedule_rec.activity_id,
1628 p_schedule_src_code => Get_Object_Source_Code('CSCH', l_schedule_id),
1629 x_msg_count => l_msg_count,
1630 x_msg_data => l_msg_data,
1631 x_return_status => l_return_status
1632 ) ;
1633
1634 IF (AMS_DEBUG_HIGH_ON) THEN
1635 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1636 ' Return Status from AMS_ADI_PRETTY_URL_PVT.CREATE_PRETTY_URL API '||l_return_status);
1637 END IF;
1638
1639 Handle_Ret_Status_For_Import (
1640 p_return_status => l_return_status,
1641 p_object_name => l_objectNames(i),
1642 p_parent_object_id => l_campIds(i),
1643 p_error_records => l_error_recs
1644 );
1645
1646 END IF;
1647 END IF; -- Activity Type Check
1648 END IF; --l_puWebsites(i) is not null
1649 END IF;
1650 -- Pretty URL Association Ends here
1651
1652
1653 -- Notes Integration starts here
1654 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1655 THEN
1656 IF (l_notes(i) is not null)
1657 THEN
1658 IF (AMS_DEBUG_HIGH_ON) THEN
1659 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1660 ' Association before Notes association was successful '||TO_CHAR(l_schedule_id));
1661 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1662 ' Now proceeding with Notes association');
1663 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1664 ' Now calling Jtf_Notes_Pub.Create_note API');
1665 END IF;
1666
1667 Jtf_Notes_Pub.Create_note(
1668 p_api_version => 1.0 ,
1669 x_return_status => l_return_status,
1670 x_msg_count => l_msg_count,
1671 x_msg_data => l_msg_data,
1672 p_source_object_id => l_schedule_id,-- schedule_id
1673 p_source_object_code => 'AMS_CSCH',
1674 p_notes => l_notes(i), -- varchar2(4000)
1675 p_notes_detail => NULL, -- for upto 32K Note
1676 p_note_status => 'I' , -- fnd_lookup JTF_NOTE_STATUS (E,I,P)
1677 p_entered_by => FND_GLOBAL.USER_ID,
1678 p_entered_date => sysdate,
1679 p_last_updated_by => FND_GLOBAL.USER_ID,
1680 x_jtf_note_id => l_note_id ,
1681 p_note_type => '', -- fnd_lookup JTF_NOTE_TYPE
1682 p_last_update_date => sysdate ,
1683 p_creation_date => sysdate ) ;
1684
1685 IF (AMS_DEBUG_HIGH_ON) THEN
1686 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1687 ' Return Status from Jtf_Notes_Pub.Create_note API '||l_return_status);
1688 END IF;
1689
1690 Handle_Ret_Status_For_Import (
1691 p_return_status => l_return_status,
1692 p_object_name => l_objectNames(i),
1693 p_parent_object_id => l_campIds(i),
1694 p_error_records => l_error_recs
1695 );
1696 END IF;
1697 END IF;
1698
1699 -- Notes Integration ends here
1700
1701 -- Approvals Integration Starts here
1702 IF(l_return_status = FND_API.G_RET_STS_SUCCESS)
1703 THEN
1704 IF (l_apprActionCodes(i) = 'COMPLETE')
1705 THEN
1706 IF (AMS_DEBUG_HIGH_ON) THEN
1707 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1708 ' Association before Approval was successful '||TO_CHAR(l_schedule_id));
1709 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1710 ' Now proceeding with Approval');
1711 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1712 ' Now calling AMS_ScheduleRules_PVT.Update_Schedule_Status');
1713 END IF;
1714
1715 BEGIN
1716
1717 AMS_ScheduleRules_PVT.Update_Schedule_Status(
1718 p_schedule_id => l_schedule_id,
1719 p_campaign_id => l_campIds(i),
1720 p_user_status_id => AMS_UTILITY_PVT.get_default_user_status
1721 (p_status_type => 'AMS_CAMPAIGN_SCHEDULE_STATUS',
1722 p_status_code => 'AVAILABLE'),
1723 p_budget_amount => null
1724 );
1725 EXCEPTION
1726 WHEN FND_API.G_EXC_ERROR THEN
1727 l_return_status := FND_API.G_RET_STS_ERROR;
1728 END;
1729
1730 IF (AMS_DEBUG_HIGH_ON) THEN
1731 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||
1732 ' Return Status from AMS_ScheduleRules_PVT.Update_Schedule_Status API '||l_return_status);
1733 END IF;
1734
1735 END IF;
1736 END IF;
1737 -- Approvals Integration Ends here
1738
1739 Handle_Ret_Status_For_Import (
1740 p_return_status => l_return_status,
1741 p_object_name => l_objectNames(i),
1742 p_parent_object_id => l_campIds(i),
1743 p_error_records => l_error_recs,
1744 p_commit => FND_API.G_TRUE
1745 );
1746
1747 FND_MSG_PUB.initialize; --initializes message table for next loop
1748 EXCEPTION
1749
1750 WHEN FND_API.G_EXC_ERROR THEN
1751 l_return_status := FND_API.G_RET_STS_ERROR;
1752
1753 Handle_Ret_Status_For_Import (
1754 p_return_status => l_return_status,
1755 p_object_name => l_objectNames(i),
1756 p_parent_object_id => l_campIds(i),
1757 p_error_records => l_error_recs,
1758 p_purge_metrics => FND_API.G_TRUE
1759 );
1760
1761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1762 AMS_ADI_COMMON_PVT.handle_fatal_error();
1763
1764 AMS_ADI_MEDIA_PLANNER_PVT.purge_import_metrics(
1765 p_object_type => 'CSCH',
1766 p_object_name => l_objectNames(i),
1767 p_parent_type => 'CAMP',
1768 p_parent_id => l_campIds(i)
1769 );
1770 COMMIT;
1771
1772 RAISE;
1773
1774 WHEN OTHERS THEN
1775 AMS_ADI_COMMON_PVT.handle_fatal_error();
1776
1777 AMS_ADI_MEDIA_PLANNER_PVT.purge_import_metrics(
1778 p_object_type => 'CSCH',
1779 p_object_name => l_objectNames(i),
1780 p_parent_type => 'CAMP',
1781 p_parent_id => l_campIds(i)
1782 );
1783 COMMIT;
1784
1785 RAISE;
1786 END;
1787
1788 END LOOP; -- l_objectNames LOOP
1789
1790 FND_MSG_PUB.initialize; --initializes message table for next loop
1791
1792 IF (AMS_DEBUG_HIGH_ON) THEN
1793 AMS_UTILITY_PVT.debug_message('Calling Complete batch');
1794 END IF;
1795
1796
1797 AMS_ADI_COMMON_PVT.complete_batch(
1798 'AMS_ADI_CAMPAIGNS_INTERFACE',
1799 p_upload_batch_id,
1800 FND_API.G_FALSE,
1801 FND_API.G_TRUE,
1802 l_error_recs
1803 );
1804
1805
1806 EXIT WHEN C_IMPORT_BATCH%NOTFOUND;
1807 END LOOP; -- C_IMPORT_BATCH loop
1808 CLOSE C_IMPORT_BATCH;
1809
1810 -- Debug Message
1811 IF (AMS_DEBUG_HIGH_ON) THEN
1812 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Calling Complete all');
1813 END IF;
1814
1815 AMS_ADI_COMMON_PVT.complete_all();
1816
1817 -- Debug Message
1818 IF (AMS_DEBUG_HIGH_ON) THEN
1819 AMS_UTILITY_PVT.Write_Conc_Log('Private API: ' || pkg_name || '.' || l_api_name ||'Done with all');
1820 END IF;
1821
1822 x_retcode := 0;
1823 EXCEPTION
1824
1825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1826 IF (C_IMPORT_BATCH%ISOPEN)
1827 THEN
1828 CLOSE C_IMPORT_BATCH;
1829 END IF;
1830
1831 x_retcode := 2;
1832 x_errbuf := SQLERRM;
1833
1834 RAISE;
1835
1836 WHEN OTHERS THEN
1837 IF (C_IMPORT_BATCH%ISOPEN)
1838 THEN
1839 CLOSE C_IMPORT_BATCH;
1840 END IF;
1841
1842 x_retcode := 2;
1843 x_errbuf := SQLERRM;
1844
1845 RAISE;
1846
1847 END Import_Campaign_Schedules;
1848
1849
1850
1851
1852 --========================================================================
1853 -- PROCEDURE Get_Activity_Type_Code
1854 -- handles successful API call for a row during Web ADI ->
1855 -- Marketing integration call
1856 -- Purpose
1857 -- COMMIT successful row in database
1858 -- HISTORY
1859 --
1860 --========================================================================
1861 function Get_Activity_Type_Code (
1862 p_activity_id IN NUMBER
1863 )
1864 return VARCHAR2
1865 IS
1866
1867 CURSOR C_GET_ACTIVITY_TYPE_CODE
1868 IS
1869 SELECT MEDIA_TYPE_CODE
1870 FROM AMS_MEDIA_B
1871 WHERE media_id = p_activity_id;
1872
1873 l_activity_type_code VARCHAR2(30);
1874
1875 BEGIN
1876
1877 OPEN C_GET_ACTIVITY_TYPE_CODE;
1878 FETCH C_GET_ACTIVITY_TYPE_CODE into l_activity_type_code;
1879 CLOSE C_GET_ACTIVITY_TYPE_CODE;
1880
1881 return l_activity_type_code;
1882
1883 END Get_Activity_Type_Code;
1884
1885 --========================================================================
1886 -- PROCEDURE Handle_Ret_Status_For_Import
1887 -- handles return status
1888 -- Purpose
1889 --
1890 -- HISTORY
1891 --
1892 --========================================================================
1893 procedure Handle_Ret_Status_For_Import (
1894 p_return_status IN VARCHAR2,
1895 p_object_name IN VARCHAR2,
1896 p_parent_object_id IN NUMBER,
1897 p_error_records IN OUT NOCOPY AMS_ADI_COMMON_PVT.ams_adi_error_rec_t,
1898 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1899 p_purge_metrics IN VARCHAR2 := FND_API.G_FALSE
1900 )
1901 IS
1902 BEGIN
1903 IF(p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1904
1905 IF (FND_API.To_Boolean(p_purge_metrics))
1906 THEN
1907 AMS_ADI_MEDIA_PLANNER_PVT.purge_import_metrics(
1908 p_object_type => 'CSCH',
1909 p_object_name => p_object_name,
1910 p_parent_type => 'CAMP',
1911 p_parent_id => p_parent_object_id
1912 );
1913 END IF;
1914
1915 AMS_ADI_COMMON_PVT.handle_success_row(p_commit);
1916
1917 ELSE
1918 ROLLBACK to Import_Schedule_PVT;
1919
1920 AMS_ADI_COMMON_PVT.handle_error_row(
1921 FND_API.G_TRUE,
1922 FND_API.G_FALSE,
1923 NULL,
1924 NULL,
1925 NULL,
1926 p_object_name,
1927 p_parent_object_id,
1928 p_error_records --the table containing error records
1929 );
1930
1931 AMS_ADI_MEDIA_PLANNER_PVT.purge_import_metrics(
1932 p_object_type => 'CSCH',
1933 p_object_name => p_object_name,
1934 p_parent_type => 'CAMP',
1935 p_parent_id => p_parent_object_id
1936 );
1937 END IF;
1938 END Handle_Ret_Status_For_Import;
1939
1940
1941 --========================================================================
1942 -- PROCEDURE Associate_Product_Category
1943 -- Product/Category Association API CALL
1944 -- Purpose
1945 --
1946 -- HISTORY
1947 --
1948 --========================================================================
1949 procedure Associate_Product_Category(
1950 p_product_id IN NUMBER,
1951 p_category_id IN NUMBER,
1952 p_schedule_id IN NUMBER,
1953 p_primary_flag IN VARCHAR2,
1954 x_act_product_id OUT NOCOPY NUMBER,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2
1958 )
1959 IS
1960
1961 l_act_Product_rec AMS_ActProduct_PVT.act_Product_rec_type;
1962 l_act_product_id NUMBER;
1963
1964 BEGIN
1965 l_act_Product_rec.ACT_PRODUCT_USED_BY_ID := p_schedule_id;
1966 l_act_Product_rec.ARC_ACT_PRODUCT_USED_BY := 'CSCH';
1967 l_act_Product_rec.CATEGORY_ID := p_category_id;
1968 l_act_Product_rec.CATEGORY_SET_ID := AMS_ActProduct_PVT.GET_CATEGORY_SET_ID();
1969 l_act_Product_rec.ORGANIZATION_ID := fnd_profile.value('AMS_ITEM_ORGANIZATION_ID');
1970 l_act_Product_rec.INVENTORY_ITEM_ID := p_product_id;
1971 l_act_Product_rec.LEVEL_TYPE_CODE := AMS_ActProduct_PVT.GET_LEVEL_TYPE_CODE( p_product_id,p_category_id);
1972 -- mayjain 31-Aug-2005 Support for Primary Flag
1973 l_act_Product_rec.PRIMARY_PRODUCT_FLAG := p_primary_flag;
1974
1975 IF (AMS_DEBUG_HIGH_ON) THEN
1976 AMS_UTILITY_PVT.Write_Conc_Log('Calling AMS_ActProduct_PVT API');
1977 END IF;
1978
1979 AMS_ActProduct_PVT.Create_Act_Product
1980 ( p_api_version => 1.0,
1981 p_init_msg_list => FND_API.G_FALSE,
1982 p_commit => FND_API.G_FALSE,
1983 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1984 x_return_status => x_return_status,
1985 x_msg_count => x_msg_count,
1986 x_msg_data => x_msg_data,
1987 p_act_Product_rec => l_act_Product_rec,
1988 x_act_product_id => l_act_product_id
1989 );
1990
1991 IF (AMS_DEBUG_HIGH_ON) THEN
1992 AMS_UTILITY_PVT.Write_Conc_Log('Return Status from AMS_ActProduct_PVT API ' || x_return_status);
1993 AMS_UTILITY_PVT.Write_Conc_Log('x_act_product_id ' || to_char(l_act_product_id));
1994 END IF;
1995
1996 x_act_product_id := l_act_product_id;
1997 END Associate_Product_Category;
1998
1999 --========================================================================
2000 -- PROCEDURE Associate_Collaboration_Item
2001 -- Collaboration Association API CALL
2002 -- Purpose
2003 --
2004 -- HISTORY
2005 --
2006 --========================================================================
2007 procedure Associate_Collaboration_Item(
2008 p_collab_type IN VARCHAR2,
2009 p_collab_assoc_id IN NUMBER,
2010 p_schedule_id IN NUMBER,
2011 x_return_status OUT NOCOPY VARCHAR2,
2012 x_msg_count OUT NOCOPY NUMBER,
2013 x_msg_data OUT NOCOPY VARCHAR2
2014 )
2015 IS
2016 l_collab_assoc_rec_type AMS_Collab_assoc_PVT.collab_assoc_rec_type;
2017 l_collab_item_id NUMBER;
2018 BEGIN
2019
2020 l_collab_assoc_rec_type.Collab_type := p_collab_type;
2021 l_collab_assoc_rec_type.collab_assoc_id := p_collab_assoc_id;
2022 l_collab_assoc_rec_type.obj_type := 'CSCH';
2023 l_collab_assoc_rec_type.obj_id := p_schedule_id;
2024
2025 IF (AMS_DEBUG_HIGH_ON) THEN
2026 AMS_UTILITY_PVT.Write_Conc_Log('Calling AMS_Collab_assoc_PVT.Create_collab_Assoc API');
2027 END IF;
2028
2029 AMS_Collab_assoc_PVT.Create_collab_Assoc(
2030 p_api_version_number => 1.0,
2031 p_init_msg_list => FND_API.G_FALSE,
2032 p_commit => FND_API.G_FALSE,
2033 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2034 x_return_status => x_return_status,
2035 x_msg_count => x_msg_count,
2036 x_msg_data => x_msg_data,
2037 p_collab_assoc_rec_type => l_collab_assoc_rec_type,
2038 x_collab_item_id => l_collab_item_id
2039 );
2040
2041 IF (AMS_DEBUG_HIGH_ON) THEN
2042 AMS_UTILITY_PVT.Write_Conc_Log('Return Status from AMS_ActProduct_PVT API ' || x_return_status);
2043 AMS_UTILITY_PVT.Write_Conc_Log('l_collab_item_id ' || to_char(l_collab_item_id));
2044 END IF;
2045
2046 END Associate_Collaboration_Item;
2047
2048
2049 --========================================================================
2050 -- PROCEDURE Associate_Web_Planner
2051 -- Collaboration Association API CALL
2052 -- Purpose
2053 --
2054 -- HISTORY
2055 --
2056 --========================================================================
2057 PROCEDURE Associate_Web_Planner(
2058 p_application_id IN NUMBER,
2059 p_placement_id IN NUMBER,
2060 p_content_item_id IN NUMBER,
2061 p_placement_title IN VARCHAR2,
2062 p_activity_id IN NUMBER,
2063 p_schedule_id IN NUMBER,
2064 x_placement_mp_id OUT NOCOPY NUMBER,
2065 x_placement_citem_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
2066 x_msg_count OUT NOCOPY NUMBER,
2067 x_msg_data OUT NOCOPY VARCHAR2,
2068 x_return_status OUT NOCOPY VARCHAR2
2069 )
2070 IS
2071 l_web_mp_rec AMS_WEBMARKETING_PVT.web_mp_track_rec_type;
2072 BEGIN
2073
2074 l_web_mp_rec.application_id := p_application_id;
2075 l_web_mp_rec.placement_id := p_placement_id;
2076 l_web_mp_rec.content_item_id := p_content_item_id;
2077 l_web_mp_rec.activity_id := p_activity_id;
2078 l_web_mp_rec.placement_mp_title := p_placement_title;
2079 l_web_mp_rec.object_used_by_id := p_schedule_id;
2080 l_web_mp_rec.object_used_by := 'CSCH';
2081 l_web_mp_rec.publish_flag := null; -- Web Planner API will take care of the Publish Flag
2082 l_web_mp_rec.max_recommendations := 1;
2083 l_web_mp_rec.display_priority := null;
2084
2085 IF (AMS_DEBUG_HIGH_ON) THEN
2086 AMS_UTILITY_PVT.Write_Conc_Log('Calling AMS_WEBMARKETING_PVT.CREATE_WEB_PLCE_ASSOC API');
2087 END IF;
2088
2089 AMS_WEBMARKETING_PVT.CREATE_WEB_PLCE_ASSOC(
2090 p_api_version_number => 1.0,
2091 p_init_msg_list => FND_API.G_FALSE,
2092 p_commit => FND_API.G_FALSE,
2093 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2094 p_web_mp_rec => l_web_mp_rec,
2095 x_placement_mp_id => x_placement_mp_id,
2096 x_placement_citem_id_tbl => x_placement_citem_id_tbl,
2097 x_msg_count => x_msg_count,
2098 x_msg_data => x_msg_data,
2099 x_return_status => x_return_status
2100 );
2101
2102 IF (AMS_DEBUG_HIGH_ON) THEN
2103 AMS_UTILITY_PVT.Write_Conc_Log('Return Status from AMS_WEBMARKETING_PVT.CREATE_WEB_PLCE_ASSOC API ' || x_return_status);
2104 AMS_UTILITY_PVT.Write_Conc_Log('x_placement_mp_id ' || to_char(x_placement_mp_id));
2105 END IF;
2106
2107 END Associate_Web_Planner;
2108
2109 --========================================================================
2110 -- PROCEDURE Get_Object_Source_Code
2111 -- Get the Source Code of an Object
2112 -- Purpose
2113 --
2114 -- HISTORY
2115 --
2116 --========================================================================
2117 FUNCTION Get_Object_Source_Code(
2118 p_object_type IN VARCHAR2,
2119 p_object_id IN NUMBER
2120 )
2121 RETURN VARCHAR2
2122 IS
2123 l_source_code VARCHAR2(30);
2124
2125 CURSOR C_SOURCE_CODE (p_object_type IN VARCHAR2, p_object_id IN NUMBER)
2126 IS
2127 SELECT
2128 source_code
2129 FROM
2130 ams_source_codes
2131 WHERE
2132 ARC_SOURCE_CODE_FOR = p_object_type and
2133 SOURCE_CODE_FOR_ID = p_object_id and
2134 active_flag = 'Y';
2135
2136 BEGIN
2137
2138 OPEN C_SOURCE_CODE(p_object_type, p_object_id);
2139 FETCH C_SOURCE_CODE INTO l_source_code;
2140 CLOSE C_SOURCE_CODE;
2141
2142 return l_source_code;
2143
2144 END Get_Object_Source_Code;
2145
2146
2147
2148
2149 END AMS_ADI_CAMP_SCHED_PVT;