[Home] [Help]
PACKAGE BODY: APPS.AMS_SYNC_CALENDAR_PVT
Source
1 PACKAGE BODY AMS_Sync_Calendar_PVT AS
2 /* $Header: amsvcalb.pls 120.6 2006/07/28 07:26:01 anskumar noship $ */
3 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6
7 PROCEDURE Sync_Cal_Items
8 (
9 x_remove_only IN VARCHAR2 := FND_API.G_False,
10 x_api_version IN NUMBER := 1.0,
11 x_init_msg_list IN VARCHAR2 := FND_API.G_False,
12 x_commit IN VARCHAR2 := FND_API.G_False,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 x_criteria_rec IN cal_criteria_rec
17 )
18 IS
19 CURSOR c_get_delete_csch_ids (x_criteria_rec cal_criteria_rec) IS
20 SELECT obj.SCHEDULE_ID
21 FROM AMS_CAMPAIGN_SCHEDULES_B obj, AMS_CAMPAIGNS_ALL_B b, JTF_CAL_ITEMS_B cal
22 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
23 AND obj.schedule_id = cal.source_id
24 AND cal.source_code = 'AMS_CSCH'
25 AND obj.ACTIVITY_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.ACTIVITY_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
26 AND obj.ACTIVITY_ID = DECODE(x_criteria_rec.ACTIVITY_ID, NULL, obj.ACTIVITY_ID, x_criteria_rec.ACTIVITY_ID)
27 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
28 AND obj.SCHEDULE_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.SCHEDULE_ID, x_criteria_rec.OBJECT_ID)
29 AND obj.CAMPAIGN_ID = b.CAMPAIGN_ID
30 AND (
31 ( (b.private_flag = 'N') AND obj.STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING') ) --'CANCELLED', 'COMPLETED', 'CLOSED', 'ARCHIVED') )
32 OR ( (b.private_flag = 'Y') AND obj.STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE') )
33 )
34 AND obj.START_DATE_TIME IS NOT NULL
35 AND b.actual_exec_end_date IS NOT NULL
36 AND obj.START_DATE_TIME <= nvl(obj.END_DATE_TIME, b.actual_exec_end_date)
37 AND obj.TIMEZONE_ID IS NOT NULL
38 AND (
39 (TRUNC(obj.START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
40 AND (TRUNC(nvl(obj.END_DATE_TIME, b.ACTUAL_EXEC_END_DATE)) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(obj.END_DATE_TIME, b.ACTUAL_EXEC_END_DATE)), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
41 )
42 );
43 l_get_delete_csch_id_rec c_get_delete_csch_ids%ROWTYPE;
44
45 --BugFix 4256877 ams_event_offers_vl to ams_event_offers_all_b
46
47 CURSOR c_get_delete_eveo_ids (x_criteria_rec cal_criteria_rec) IS
48 SELECT obj.EVENT_OFFER_ID
49 FROM ams_event_offers_all_b obj, jtf_cal_items_b cal
50 WHERE (obj.SETUP_TYPE_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.SETUP_TYPE_ID, x_criteria_rec.CUSTOM_SETUP_ID)
51 AND obj.event_offer_id = cal.source_id
52 AND (cal.source_code = 'AMS_EVEO' or cal.source_code = 'AMS_EONE')
53 AND obj.EVENT_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.EVENT_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
54 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
55 AND obj.EVENT_OFFER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.EVENT_OFFER_ID, x_criteria_rec.OBJECT_ID)
56 AND (
57 ( (obj.private_flag = 'N') AND obj.SYSTEM_STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING') )
58 OR ( (obj.private_flag = 'Y') AND obj.SYSTEM_STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE') )
59 )
60 AND obj.event_standalone_flag = DECODE(x_criteria_rec.OBJECT_TYPE, 'EVEO', 'N', 'EONE', 'Y')
61 AND obj.EVENT_START_DATE_TIME IS NOT NULL
62 AND obj.EVENT_END_DATE_TIME IS NOT NULL
63 AND obj.EVENT_START_DATE_TIME <= obj.EVENT_END_DATE_TIME
64 AND obj.TIMEZONE_ID IS NOT NULL
65 AND (
66 (TRUNC(obj.EVENT_START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.EVENT_START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
67 AND (TRUNC(obj.EVENT_END_DATE_TIME) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(obj.EVENT_END_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
68 )
69 );
70 l_get_delete_eveo_id_rec c_get_delete_eveo_ids%ROWTYPE;
71
72 --BugFix 4256877 qp_list_headers_vl to qp_list_headers_b
73
74 CURSOR c_get_delete_offr_ids (x_criteria_rec cal_criteria_rec) IS
75 SELECT obj.QP_LIST_HEADER_ID
76 FROM OZF_OFFERS obj, qp_list_headers_b g, jtf_cal_items_b cal
77 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
78 AND obj.QP_LIST_HEADER_ID = cal.source_id
79 AND cal.source_code = 'AMS_OFFR'
80 AND obj.OFFER_TYPE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.OFFER_TYPE, x_criteria_rec.ACTIVITY_TYPE_CODE)
81 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
82 AND obj.QP_LIST_HEADER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.QP_LIST_HEADER_ID, x_criteria_rec.OBJECT_ID)
83 AND obj.QP_LIST_HEADER_ID = g.LIST_HEADER_ID
84 AND (
85 ( (obj.confidential_flag = 'N') AND obj.STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING') )
86 OR ( (obj.confidential_flag = 'Y') AND obj.STATUS_CODE NOT IN ('ACTIVE', 'AVAILABLE') )
87 )
88 AND (
89 (TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
90 )
91 );
92 l_get_delete_offr_id_rec c_get_delete_offr_ids%ROWTYPE;
93
94 BEGIN
95 IF (x_remove_only = FND_API.G_True) THEN
96 Delete_Cal_Items(x_return_status => x_return_status,
97 x_msg_count => x_msg_count,
98 x_msg_data => x_msg_data,
99 x_criteria_rec => x_criteria_rec
100 );
101 ELSE
102 IF (x_criteria_rec.OBJECT_TYPE = 'CSCH') THEN
103 OPEN c_get_delete_csch_ids(x_criteria_rec);
104 LOOP
105 FETCH c_get_delete_csch_ids INTO l_get_delete_csch_id_rec;
106 IF (c_get_delete_csch_ids%NOTFOUND) THEN
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108 CLOSE c_get_delete_csch_ids;
109 l_get_delete_csch_id_rec := NULL;
110 EXIT;
111 ELSE
112 Delete_Cal_Items(x_return_status => x_return_status,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 x_obj_id => l_get_delete_csch_id_rec.SCHEDULE_ID,
116 x_criteria_rec => x_criteria_rec
117 );
118 END IF;
119 END LOOP;
120 ELSE
121 IF ((x_criteria_rec.OBJECT_TYPE = 'EVEO') OR (x_criteria_rec.OBJECT_TYPE = 'EONE')) THEN
122 OPEN c_get_delete_eveo_ids(x_criteria_rec);
123 LOOP
124 FETCH c_get_delete_eveo_ids INTO l_get_delete_eveo_id_rec;
125 IF (c_get_delete_eveo_ids%NOTFOUND) THEN
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127 CLOSE c_get_delete_eveo_ids;
128 l_get_delete_eveo_id_rec := NULL;
129 EXIT;
130 ELSE
131 Delete_Cal_Items(x_return_status => x_return_status,
132 x_msg_count => x_msg_count,
133 x_msg_data => x_msg_data,
134 x_obj_id => l_get_delete_eveo_id_rec.EVENT_OFFER_ID,
135 x_criteria_rec => x_criteria_rec
136 );
137 END IF;
138 END LOOP;
139 END IF;
140 IF (x_criteria_rec.OBJECT_TYPE = 'OFFR') THEN
141 OPEN c_get_delete_offr_ids(x_criteria_rec);
142 LOOP
143 FETCH c_get_delete_offr_ids INTO l_get_delete_offr_id_rec;
144 IF (c_get_delete_offr_ids%NOTFOUND) THEN
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146 CLOSE c_get_delete_offr_ids;
147 l_get_delete_offr_id_rec := NULL;
148 EXIT;
149 ELSE
150 Delete_Cal_Items(x_return_status => x_return_status,
151 x_msg_count => x_msg_count,
152 x_msg_data => x_msg_data,
153 x_obj_id => l_get_delete_offr_id_rec.QP_LIST_HEADER_ID,
154 x_criteria_rec => x_criteria_rec
155 );
156 END IF;
157 END LOOP;
158 END IF;
159 END IF;
160 Update_Cal_Items
161 (
162 x_return_status => x_return_status,
163 x_msg_count => x_msg_count,
164 x_msg_data => x_msg_data,
165 x_criteria_rec => x_criteria_rec
166 );
167 Create_Cal_Items
168 (
169 x_return_status => x_return_status,
170 x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data,
172 x_criteria_rec => x_criteria_rec
173 );
174 END IF;
175 COMMIT;
176 END Sync_Cal_Items;
177
178 PROCEDURE Create_Cal_Items
179 (
180 x_full_mode IN VARCHAR2 := 'N',
181 x_api_version IN NUMBER := 1.0,
182 x_init_msg_list IN VARCHAR2 := FND_API.G_False,
183 x_commit IN VARCHAR2 := FND_API.G_False,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_msg_count OUT NOCOPY NUMBER,
186 x_msg_data OUT NOCOPY VARCHAR2,
187 x_criteria_rec IN cal_criteria_rec
188 )
189 IS
190 CURSOR c_get_grp_id IS
191 --BugFix 4256877
192 SELECT B.GROUP_ID RESOURCE_ID
193 FROM JTF_RS_GROUP_USAGES B, JTF_RS_GROUPS_B A
194 WHERE B.USAGE = 'CALENDAR_ITEMS'
195 AND A.GROUP_ID = B.GROUP_ID
196 AND SYSDATE > A.START_DATE_ACTIVE
197 AND (A.END_DATE_ACTIVE IS NULL OR
198 A.END_DATE_ACTIVE >= SYSDATE);
199
200 t_group_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
201
202 CURSOR c_get_cre_csch_items (x_criteria_rec cal_criteria_rec) IS
203 SELECT obj.SCHEDULE_Id
204 , obj.START_DATE_TIME
205 , obj.END_DATE_TIME
206 , b.ACTUAL_EXEC_END_DATE --End Date id the CSCH end date is null
207 , obj.TIMEZONE_ID --TIMEZONE_ID
208 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
209 , obj.CREATED_BY
210 , obj.CREATION_DATE
211 , obj.LAST_UPDATED_BY
212 , obj.LAST_UPDATE_DATE
213 , obj.LAST_UPDATE_LOGIN
214 FROM AMS_CAMPAIGN_SCHEDULES_B obj, AMS_CAMPAIGNS_ALL_B b
215 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
216 AND obj.ACTIVITY_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.ACTIVITY_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
217 AND DECODE(x_criteria_rec.ACTIVITY_ID,NULL,-9999,obj.ACTIVITY_ID) = DECODE(x_criteria_rec.ACTIVITY_ID, NULL, -9999, x_criteria_rec.ACTIVITY_ID)
218 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
219 AND obj.SCHEDULE_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.SCHEDULE_ID, x_criteria_rec.OBJECT_ID)
220 AND DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', obj.PRIORITY) = DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', x_criteria_rec.PRIORITY_ID)
221 AND obj.CAMPAIGN_ID = b.CAMPAIGN_ID
222 AND (
223 ( (b.private_flag = 'N') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
224 OR ( (b.private_flag = 'Y') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
225 )
226 AND obj.START_DATE_TIME IS NOT NULL
227 AND b.ACTUAL_EXEC_END_DATE IS NOT NULL
228 AND obj.START_DATE_TIME <= nvl(obj.END_DATE_TIME, b.actual_exec_end_date)
229 AND obj.TIMEZONE_ID IS NOT NULL
230 AND NOT EXISTS ( SELECT 1
231 FROM JTF_CAL_ITEMS_B cal
232 WHERE cal.source_code = 'AMS_CSCH' --obj_type
233 AND cal.source_id = obj.SCHEDULE_ID
234 )
235 AND (
236 (TRUNC(obj.START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
237 AND (TRUNC(nvl(obj.END_DATE_TIME, b.ACTUAL_EXEC_END_DATE)) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(obj.END_DATE_TIME, b.actual_exec_end_date)), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
238 )
239 );
240 l_get_cre_csch_items_rec c_get_cre_csch_items%ROWTYPE;
241
242 --BugFix 4256877
243
244 CURSOR c_get_cre_eveo_items (x_criteria_rec cal_criteria_rec) IS
245 SELECT obj.EVENT_OFFER_ID --SOURCE_ID
246 , obj.EVENT_START_DATE_TIME --START_DATE
247 , obj.EVENT_END_DATE_TIME --END_DATE
248 , obj.TIMEZONE_ID --TIMEZONE_ID
249 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
250 , obj.CREATED_BY
251 , obj.CREATION_DATE
252 , obj.LAST_UPDATED_BY
253 , obj.LAST_UPDATE_DATE
254 , obj.LAST_UPDATE_LOGIN
255 FROM ams_event_offers_all_b obj
256 WHERE (obj.SETUP_TYPE_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.SETUP_TYPE_ID, x_criteria_rec.CUSTOM_SETUP_ID)
257 AND obj.EVENT_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.EVENT_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
258 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
259 AND obj.EVENT_OFFER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.EVENT_OFFER_ID, x_criteria_rec.OBJECT_ID)
260 AND DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', obj.PRIORITY_TYPE_CODE) = DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', x_criteria_rec.PRIORITY_ID)
261 AND (
262 ( (obj.private_flag = 'N') AND obj.SYSTEM_STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
263 OR ( (obj.private_flag = 'Y') AND obj.SYSTEM_STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
264 )
265 AND obj.event_standalone_flag = DECODE(x_criteria_rec.OBJECT_TYPE, 'EVEO', 'N', 'EONE', 'Y')
266 AND obj.EVENT_START_DATE_TIME IS NOT NULL
267 AND obj.EVENT_END_DATE_TIME IS NOT NULL
268 AND obj.EVENT_START_DATE_TIME <= obj.EVENT_END_DATE_TIME
269 AND obj.TIMEZONE_ID IS NOT NULL
270 AND (obj.EVENT_OBJECT_TYPE = 'EVEO' OR (obj.EVENT_OBJECT_TYPE = 'EONE' AND (obj.parent_type is NULL OR obj.parent_type <>'CAMP')))
271 AND NOT EXISTS ( SELECT 1
272 FROM JTF_CAL_ITEMS_B cal
273 WHERE ( (cal.source_code = 'AMS_EVEO') OR (cal.source_code = 'AMS_EONE') ) --obj_type
274 AND cal.source_id = obj.EVENT_OFFER_ID
275 )
276 AND (
277 (TRUNC(obj.EVENT_START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.EVENT_START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
278 AND (TRUNC(obj.EVENT_END_DATE_TIME) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(obj.EVENT_END_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
279 )
280 );
281 l_get_cre_eveo_items_rec c_get_cre_eveo_items%ROWTYPE;
282
283 CURSOR c_get_cre_offr_items (x_criteria_rec cal_criteria_rec) IS
284 SELECT obj.QP_LIST_HEADER_ID --SOURCE_ID
285 , g.START_DATE_ACTIVE --START_DATE
286 , g.END_DATE_ACTIVE --END_DATE
287 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
288 , obj.CREATED_BY
289 , obj.CREATION_DATE
290 , obj.LAST_UPDATED_BY
291 , obj.LAST_UPDATE_DATE
292 , obj.LAST_UPDATE_LOGIN
293 FROM OZF_OFFERS obj, qp_list_headers_b g
294 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
295 AND obj.OFFER_TYPE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.OFFER_TYPE, x_criteria_rec.ACTIVITY_TYPE_CODE)
296 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
297 AND obj.QP_LIST_HEADER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.QP_LIST_HEADER_ID, x_criteria_rec.OBJECT_ID)
298 AND obj.QP_LIST_HEADER_ID = g.LIST_HEADER_ID
299 AND (
300 ( (obj.confidential_flag = 'N') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
301 OR ( (obj.confidential_flag = 'Y') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
302 )
303 AND (
304 (TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
305 AND (TRUNC(nvl(g.END_DATE_ACTIVE, add_months(SYSDATE, 60))) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(g.END_DATE_ACTIVE, add_months(SYSDATE, 60))), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
306 )
307 AND NOT EXISTS ( SELECT 1
308 FROM JTF_CAL_ITEMS_B cal
309 WHERE cal.source_code = 'AMS_OFFR' --obj_type
310 AND cal.source_id = obj.QP_LIST_HEADER_ID
311 )
312 );
313 l_get_cre_offr_items_rec c_get_cre_offr_items%ROWTYPE;
314
315 l_jtf_cre_itm_rec JTF_CAL_ITEMS_PUB.calItemRec;
316 l_cal_item_id NUMBER;
317
318 BEGIN
319 OPEN c_get_grp_id;
320 FETCH c_get_grp_id
321 BULK COLLECT INTO t_group_id;
322 CLOSE c_get_grp_id;
323
324 IF t_group_id.COUNT > 0 THEN
325
326 IF (x_criteria_rec.OBJECT_TYPE = 'CSCH') THEN
327 OPEN c_get_cre_csch_items(x_criteria_rec);
328 LOOP
329 FETCH c_get_cre_csch_items INTO l_get_cre_csch_items_rec;
330 IF (c_get_cre_csch_items%NOTFOUND) THEN
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332 CLOSE c_get_cre_csch_items;
333 l_get_cre_csch_items_rec := NULL;
334 EXIT;
335 ELSE
336 l_jtf_cre_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP'; --x_criteria_rec.RESOURCE_TYPE;
337 l_jtf_cre_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
338 l_jtf_cre_itm_rec.ITEM_NAME := ''; --l_get_cre_csch_items_rec.SCHEDULE_NAME;
339 l_jtf_cre_itm_rec.ITEM_DESCRIPTION := ''; --l_get_cre_csch_items_rec.DESCRIPTION;
340 l_jtf_cre_itm_rec.SOURCE_CODE := 'AMS_CSCH'; --l_get_cre_csch_items_rec.SOURCE_CODE;
341 l_jtf_cre_itm_rec.SOURCE_ID := l_get_cre_csch_items_rec.SCHEDULE_ID;
342 l_jtf_cre_itm_rec.START_DATE := l_get_cre_csch_items_rec.START_DATE_TIME;
343
344 IF (l_get_cre_csch_items_rec.END_DATE_TIME IS NOT NULL) THEN
345 l_jtf_cre_itm_rec.END_DATE := l_get_cre_csch_items_rec.END_DATE_TIME;
346 ELSE
347 l_jtf_cre_itm_rec.END_DATE := l_get_cre_csch_items_rec.ACTUAL_EXEC_END_DATE;
348 END IF;
349
350 l_jtf_cre_itm_rec.TIMEZONE_ID := l_get_cre_csch_items_rec.TIMEZONE_ID;
351 l_jtf_cre_itm_rec.URL := NULL ;
352 l_jtf_cre_itm_rec.CREATED_BY := l_get_cre_csch_items_rec.CREATED_BY;
353 l_jtf_cre_itm_rec.CREATION_DATE := l_get_cre_csch_items_rec.CREATION_DATE;
354 l_jtf_cre_itm_rec.LAST_UPDATED_BY := l_get_cre_csch_items_rec.LAST_UPDATED_BY;
355 l_jtf_cre_itm_rec.LAST_UPDATE_DATE := l_get_cre_csch_items_rec.LAST_UPDATE_DATE;
356 l_jtf_cre_itm_rec.LAST_UPDATE_LOGIN := l_get_cre_csch_items_rec.LAST_UPDATE_LOGIN;
357 l_jtf_cre_itm_rec.OBJECT_VERSION_NUMBER := l_get_cre_csch_items_rec.OBJECT_VERSION_NUMBER;
358 l_jtf_cre_itm_rec.APPLICATION_ID := '530';
359
363 , x_return_status => x_return_status
360 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
361 l_jtf_cre_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
362 JTF_CAL_ITEMS_PUB.CREATEITEM( p_api_version => '1.0' --x_api_version
364 , x_msg_count => x_msg_count
365 , x_msg_data => x_msg_data
366 , p_itm_rec => l_jtf_cre_itm_rec
367 , x_cal_item_id => l_cal_item_id
368 );
369 END LOOP;
370 END IF;
371 END LOOP;
372 END IF;
373
374 IF ((x_criteria_rec.OBJECT_TYPE = 'EVEO') OR (x_criteria_rec.OBJECT_TYPE = 'EONE')) THEN
375 OPEN c_get_cre_eveo_items(x_criteria_rec);
376 LOOP
377 FETCH c_get_cre_eveo_items INTO l_get_cre_eveo_items_rec;
378 IF (c_get_cre_eveo_items%NOTFOUND) THEN
379 x_return_status := FND_API.G_RET_STS_SUCCESS;
380 CLOSE c_get_cre_eveo_items;
381 l_get_cre_eveo_items_rec := NULL;
382 EXIT;
383 ELSE
384 l_jtf_cre_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP'; --x_criteria_rec.RESOURCE_TYPE;
385 l_jtf_cre_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
386 l_jtf_cre_itm_rec.ITEM_NAME := ''; --l_get_cre_eveo_items_rec.EVENT_OFFER_NAME; --append campaign name?
387 l_jtf_cre_itm_rec.ITEM_DESCRIPTION := ''; --l_get_cre_eveo_items_rec.description;
388 IF (x_criteria_rec.OBJECT_TYPE = 'EVEO') THEN
389 l_jtf_cre_itm_rec.SOURCE_CODE := 'AMS_EVEO'; --l_get_cre_eveo_items_rec.SOURCE_CODE;
390 END IF;
391 IF (x_criteria_rec.OBJECT_TYPE = 'EONE') THEN
392 l_jtf_cre_itm_rec.SOURCE_CODE := 'AMS_EONE'; --l_get_cre_eveo_items_rec.SOURCE_CODE;
393 END IF;
394 l_jtf_cre_itm_rec.SOURCE_ID := l_get_cre_eveo_items_rec.EVENT_OFFER_ID;
395 l_jtf_cre_itm_rec.START_DATE := l_get_cre_eveo_items_rec.EVENT_START_DATE_TIME;
396 l_jtf_cre_itm_rec.END_DATE := l_get_cre_eveo_items_rec.EVENT_END_DATE_TIME;
397 l_jtf_cre_itm_rec.TIMEZONE_ID := l_get_cre_eveo_items_rec.TIMEZONE_ID;
398 l_jtf_cre_itm_rec.URL := NULL ;
399 l_jtf_cre_itm_rec.CREATED_BY := l_get_cre_eveo_items_rec.CREATED_BY;
400 l_jtf_cre_itm_rec.CREATION_DATE := l_get_cre_eveo_items_rec.CREATION_DATE;
401 l_jtf_cre_itm_rec.LAST_UPDATED_BY := l_get_cre_eveo_items_rec.LAST_UPDATED_BY;
402 l_jtf_cre_itm_rec.LAST_UPDATE_DATE := l_get_cre_eveo_items_rec.LAST_UPDATE_DATE;
403 l_jtf_cre_itm_rec.LAST_UPDATE_LOGIN := l_get_cre_eveo_items_rec.LAST_UPDATE_LOGIN;
404 l_jtf_cre_itm_rec.OBJECT_VERSION_NUMBER := l_get_cre_eveo_items_rec.OBJECT_VERSION_NUMBER;
405 l_jtf_cre_itm_rec.APPLICATION_ID := '530';
406
407 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
408 l_jtf_cre_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
409
410 JTF_CAL_ITEMS_PUB.CREATEITEM( p_api_version => '1.0' --x_api_version
411 , x_return_status => x_return_status
412 , x_msg_count => x_msg_count
413 , x_msg_data => x_msg_data
414 , p_itm_rec => l_jtf_cre_itm_rec
415 , x_cal_item_id => l_cal_item_id
416 );
417 END LOOP;
418 END IF;
419 END LOOP;
420 END IF;
421
422 IF (x_criteria_rec.OBJECT_TYPE = 'OFFR') THEN
423 OPEN c_get_cre_offr_items(x_criteria_rec);
424 LOOP
425 FETCH c_get_cre_offr_items INTO l_get_cre_offr_items_rec;
426 IF (c_get_cre_offr_items%NOTFOUND) THEN
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428 CLOSE c_get_cre_offr_items;
429 l_get_cre_offr_items_rec := NULL;
430 EXIT;
431 ELSE
432 l_jtf_cre_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP'; --x_criteria_rec.RESOURCE_TYPE;
433 l_jtf_cre_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
434 l_jtf_cre_itm_rec.ITEM_NAME := ''; --l_get_cre_offr_items_rec.name;
435 l_jtf_cre_itm_rec.ITEM_DESCRIPTION := ''; --l_get_cre_offr_items_rec.DESCRIPTION;
436 l_jtf_cre_itm_rec.SOURCE_CODE := 'AMS_OFFR'; --l_get_cre_offr_items_rec.SOURCE_CODE;
437 l_jtf_cre_itm_rec.SOURCE_ID := l_get_cre_offr_items_rec.QP_LIST_HEADER_ID;
438
439
440 IF (l_get_cre_offr_items_rec.START_DATE_ACTIVE IS NOT NULL) THEN
441 l_jtf_cre_itm_rec.START_DATE := l_get_cre_offr_items_rec.START_DATE_ACTIVE;
442 ELSE
443 l_jtf_cre_itm_rec.START_DATE := l_get_cre_offr_items_rec.CREATION_DATE;
444 END IF;
445
446 IF (l_get_cre_offr_items_rec.END_DATE_ACTIVE IS NOT NULL) THEN
447 l_jtf_cre_itm_rec.END_DATE := l_get_cre_offr_items_rec.END_DATE_ACTIVE;
448 ELSE
449 l_jtf_cre_itm_rec.END_DATE := add_months(SYSDATE, 60); --add 5 years here
450 END IF;
451
452 l_jtf_cre_itm_rec.TIMEZONE_ID := '4'; --Pacific Standard Timezone ID
453 l_jtf_cre_itm_rec.URL := NULL ;
454 l_jtf_cre_itm_rec.CREATED_BY := l_get_cre_offr_items_rec.CREATED_BY;
455 l_jtf_cre_itm_rec.CREATION_DATE := l_get_cre_offr_items_rec.CREATION_DATE;
456 l_jtf_cre_itm_rec.LAST_UPDATED_BY := l_get_cre_offr_items_rec.LAST_UPDATED_BY;
457 l_jtf_cre_itm_rec.LAST_UPDATE_DATE := l_get_cre_offr_items_rec.LAST_UPDATE_DATE;
458 l_jtf_cre_itm_rec.LAST_UPDATE_LOGIN := l_get_cre_offr_items_rec.LAST_UPDATE_LOGIN;
459 l_jtf_cre_itm_rec.OBJECT_VERSION_NUMBER := l_get_cre_offr_items_rec.OBJECT_VERSION_NUMBER;
463 l_jtf_cre_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
460 l_jtf_cre_itm_rec.APPLICATION_ID := '530';
461
462 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
464
465 JTF_CAL_ITEMS_PUB.CREATEITEM( p_api_version => '1.0' --x_api_version
466 , x_return_status => x_return_status
467 , x_msg_count => x_msg_count
468 , x_msg_data => x_msg_data
469 , p_itm_rec => l_jtf_cre_itm_rec
470 , x_cal_item_id => l_cal_item_id
471 );
472 END LOOP;
473
474 END IF;
475 END LOOP;
476 END IF;
477 END IF; -- COUNT
478 COMMIT;
479 END Create_Cal_Items;
480
481 PROCEDURE Update_Cal_Items
482 (
483 x_full_mode IN VARCHAR2 := 'N',
484 x_api_version IN NUMBER := 1.0,
485 x_init_msg_list IN VARCHAR2 := FND_API.G_False,
486 x_commit IN VARCHAR2 := FND_API.G_False,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 x_criteria_rec IN cal_criteria_rec
491 )
492 IS
493 CURSOR c_get_grp_id IS
494 SELECT B.GROUP_ID RESOURCE_ID
495 FROM JTF_RS_GROUP_USAGES B, JTF_RS_GROUPS_B A
496 WHERE B.USAGE = 'CALENDAR_ITEMS'
497 AND A.GROUP_ID = B.GROUP_ID
498 AND SYSDATE > A.START_DATE_ACTIVE
499 AND (A.END_DATE_ACTIVE IS NULL OR
500 A.END_DATE_ACTIVE >= SYSDATE); -- BugFix 4256877 jtf_rs_groups_vl to jtf_rs_groups_b
501 t_group_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
502
503 --BugFix 4256877 AMS_CAMPAIGNS_V to AMS_CAMPAIGNS_ALL_B
504
505 CURSOR c_get_upd_csch_items (x_criteria_rec cal_criteria_rec) IS
506 SELECT obj.SCHEDULE_ID --SOURCE_ID
507 , obj.START_DATE_TIME --START_DATE
508 , obj.END_DATE_TIME --END_DATE
509 , b.ACTUAL_EXEC_END_DATE
510 , obj.TIMEZONE_ID --TIMEZONE_ID
511 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
512 , obj.CREATED_BY
513 , obj.CREATION_DATE
514 , obj.LAST_UPDATED_BY
515 , obj.LAST_UPDATE_DATE
516 , obj.LAST_UPDATE_LOGIN
517 FROM AMS_CAMPAIGN_SCHEDULES_B obj, AMS_CAMPAIGNS_ALL_B b, JTF_CAL_ITEMS_B cal
518 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
519 AND cal.source_code = 'AMS_CSCH'
520 AND cal.source_id = obj.SCHEDULE_ID
521 AND ( cal.start_date <> obj.START_DATE_TIME
522 OR cal.end_date <> nvl(obj.END_DATE_TIME, b.actual_exec_end_date)
523 OR cal.timezone_id <> obj.TIMEZONE_ID
524 OR cal.OBJECT_VERSION_NUMBER <> obj.OBJECT_VERSION_NUMBER
525 )
526 AND obj.ACTIVITY_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.ACTIVITY_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
527 AND DECODE(x_criteria_rec.ACTIVITY_ID,NULL,-9999,obj.ACTIVITY_ID) = DECODE(x_criteria_rec.ACTIVITY_ID, NULL, -9999, x_criteria_rec.ACTIVITY_ID)
528 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
529 AND obj.SCHEDULE_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.SCHEDULE_ID, x_criteria_rec.OBJECT_ID)
530 AND DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', obj.PRIORITY) = DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', x_criteria_rec.PRIORITY_ID)
531 AND obj.CAMPAIGN_ID = b.CAMPAIGN_ID
532 AND (
533 ( (b.private_flag = 'N') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
534 OR ( (b.private_flag = 'Y') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
535 )
536 AND obj.START_DATE_TIME IS NOT NULL
537 AND b.ACTUAL_EXEC_END_DATE IS NOT NULL
538 AND obj.START_DATE_TIME <= nvl(obj.END_DATE_TIME, b.actual_exec_end_date)
539 AND obj.TIMEZONE_ID IS NOT NULL
540 AND (
541 (TRUNC(obj.START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
542 AND (TRUNC(nvl(obj.END_DATE_TIME, b.ACTUAL_EXEC_END_DATE)) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(obj.END_DATE_TIME, b.actual_exec_end_date)), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
543 )
544 );
545 l_get_upd_csch_items_rec c_get_upd_csch_items%ROWTYPE;
546
547 CURSOR c_get_upd_eveo_items (x_criteria_rec cal_criteria_rec) IS
548 SELECT obj.EVENT_OFFER_ID --SOURCE_ID
549 , obj.EVENT_START_DATE_TIME --START_DATE
550 , obj.EVENT_END_DATE_TIME --END_DATE
551 , obj.TIMEZONE_ID --TIMEZONE_ID
552 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
553 , obj.CREATED_BY
554 , obj.CREATION_DATE
555 , obj.LAST_UPDATED_BY
556 , obj.LAST_UPDATE_DATE
557 , obj.LAST_UPDATE_LOGIN
558 FROM ams_event_offers_all_b obj, JTF_CAL_ITEMS_B cal
559 WHERE (obj.SETUP_TYPE_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.SETUP_TYPE_ID, x_criteria_rec.CUSTOM_SETUP_ID)
560 AND (cal.source_code = 'AMS_EVEO' OR cal.source_code = 'AMS_EONE')
561 AND cal.source_id = obj.EVENT_OFFER_ID
562 AND ( cal.start_date <> obj.EVENT_START_DATE_TIME
563 OR cal.end_date <> obj.EVENT_START_DATE_TIME
564 OR cal.timezone_id <> obj.TIMEZONE_ID
565 OR cal.OBJECT_VERSION_NUMBER <> obj.OBJECT_VERSION_NUMBER
569 AND obj.EVENT_OFFER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.EVENT_OFFER_ID, x_criteria_rec.OBJECT_ID)
566 )
567 AND obj.EVENT_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.EVENT_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
568 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
570 AND DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', obj.PRIORITY_TYPE_CODE) = DECODE(x_criteria_rec.PRIORITY_ID, NULL, '1', x_criteria_rec.PRIORITY_ID)
571 AND (
572 ( (obj.private_flag = 'N') AND obj.SYSTEM_STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
573 OR ( (obj.private_flag = 'Y') AND obj.SYSTEM_STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
574 )
575 AND obj.EVENT_START_DATE_TIME IS NOT NULL
576 AND obj.EVENT_END_DATE_TIME IS NOT NULL
577 AND obj.EVENT_START_DATE_TIME <= obj.EVENT_END_DATE_TIME
578 AND obj.TIMEZONE_ID IS NOT NULL
579 AND (
580 (TRUNC(obj.EVENT_START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.EVENT_START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
581 AND (TRUNC(obj.EVENT_END_DATE_TIME) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(obj.EVENT_END_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
582 )
583 AND obj.event_standalone_flag = DECODE(x_criteria_rec.OBJECT_TYPE, 'EVEO', 'N', 'EONE', 'Y')
584 );
585 l_get_upd_eveo_items_rec c_get_upd_eveo_items%ROWTYPE;
586
587 CURSOR c_get_upd_offr_items (x_criteria_rec cal_criteria_rec) IS
588 SELECT obj.QP_LIST_HEADER_ID --SOURCE_ID
589 , g.START_DATE_ACTIVE --START_DATE
590 , g.END_DATE_ACTIVE --END_DATE
591 , obj.OBJECT_VERSION_NUMBER --OBJECT_VERSION_NUMBER
592 , obj.CREATED_BY
593 , obj.CREATION_DATE
594 , obj.LAST_UPDATED_BY
595 , obj.LAST_UPDATE_DATE
596 , obj.LAST_UPDATE_LOGIN
597 FROM OZF_OFFERS obj , qp_list_headers_b g, JTF_CAL_ITEMS_B cal
598 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
599 AND cal.source_code = 'AMS_OFFR'
600 AND cal.source_id = obj.QP_LIST_HEADER_ID
601 AND ( cal.start_date <> nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)
602 OR DECODE(g.END_DATE_ACTIVE, null, SYSDATE, cal.end_date) <> nvl(g.END_DATE_ACTIVE, SYSDATE)
603 OR cal.OBJECT_VERSION_NUMBER <> obj.OBJECT_VERSION_NUMBER
604 )
605 AND obj.OFFER_TYPE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.OFFER_TYPE, x_criteria_rec.ACTIVITY_TYPE_CODE)
606 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
607 AND obj.QP_LIST_HEADER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.QP_LIST_HEADER_ID, x_criteria_rec.OBJECT_ID)
608 AND obj.QP_LIST_HEADER_ID = g.LIST_HEADER_ID
609 AND (
610 ( (obj.confidential_flag = 'N') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE', 'NEW', 'PLANNING'))
611 OR ( (obj.confidential_flag = 'Y') AND obj.STATUS_CODE IN ('ACTIVE', 'AVAILABLE') )
612 )
613 AND (
614 (TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)) >= DECODE( x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)), TRUNC(x_criteria_rec.CRITERIA_START_DATE) ) )
615 AND (TRUNC(nvl(g.END_DATE_ACTIVE, add_months(SYSDATE, 60))) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(g.END_DATE_ACTIVE, add_months(SYSDATE, 60))), TRUNC(x_criteria_rec.CRITERIA_END_DATE) ) )
616 )
617 );
618 l_get_upd_offr_items_rec c_get_upd_offr_items%ROWTYPE;
619
620 l_jtf_upd_itm_rec JTF_CAL_ITEMS_PUB.calItemRec;
621 l_cal_obj_ver_number NUMBER;
622 --BugFix 4676786 sikalyan
623 CURSOR c_get_cal_item_id (l_source_id NUMBER, l_resource_id NUMBER) IS
624 SELECT cal_item_id, resource_id, source_id, jcal.object_version_number
625 FROM JTF_CAL_ITEMS_B jcal, ams_campaign_schedules_b camp
626 where camp.schedule_id = jcal.source_id and jcal.source_id = l_source_id and jcal.resource_id = l_resource_id ;
627
628 l_get_cal_item_rec c_get_cal_item_id%ROWTYPE;
629
630 CURSOR c_get_e_cal_item_id (l_source_id NUMBER, l_resource_id NUMBER) IS
631 SELECT cal_item_id, resource_id, source_id, jcal.object_version_number
632 FROM JTF_CAL_ITEMS_B jcal, ams_event_offers_all_b aevent
633 where aevent.event_offer_id = jcal.source_id and jcal.source_id = l_source_id and jcal.resource_id = l_resource_id ;
634
635 l_get_e_cal_item_rec c_get_e_cal_item_id%ROWTYPE;
636
637 CURSOR c_get_o_cal_item_id (l_source_id NUMBER, l_resource_id NUMBER) IS
638 SELECT cal_item_id, resource_id, source_id, jcal.object_version_number
639 FROM JTF_CAL_ITEMS_B jcal, ozf_offers qlh
640 where qlh.qp_list_header_id = jcal.source_id and jcal.source_id = l_source_id and jcal.resource_id = l_resource_id ;
641
642 l_get_o_cal_item_rec c_get_o_cal_item_id%ROWTYPE;
643
644 BEGIN
645 OPEN c_get_grp_id;
646 FETCH c_get_grp_id
647 BULK COLLECT INTO t_group_id;
648 CLOSE c_get_grp_id;
649
650 IF t_group_id.COUNT > 0 THEN
651
652 IF (x_criteria_rec.OBJECT_TYPE = 'CSCH') THEN
653 OPEN c_get_upd_csch_items(x_criteria_rec);
654 LOOP
655 FETCH c_get_upd_csch_items INTO l_get_upd_csch_items_rec;
656 IF (c_get_upd_csch_items%NOTFOUND) THEN
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 CLOSE c_get_upd_csch_items;
659 l_get_upd_csch_items_rec := NULL;
660 EXIT;
661 ELSE
665 l_jtf_upd_itm_rec.ITEM_DESCRIPTION := NULL; --l_get_upd_csch_items_rec.DESCRIPTION;
662 l_jtf_upd_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP';
663 l_jtf_upd_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
664 l_jtf_upd_itm_rec.ITEM_NAME := NULL; --l_get_upd_csch_items_rec.SCHEDULE_NAME;
666 l_jtf_upd_itm_rec.SOURCE_CODE := 'AMS_CSCH';
667 l_jtf_upd_itm_rec.SOURCE_ID := l_get_upd_csch_items_rec.SCHEDULE_ID;
668 l_jtf_upd_itm_rec.START_DATE := l_get_upd_csch_items_rec.START_DATE_TIME;
669 IF (l_get_upd_csch_items_rec.END_DATE_TIME IS NOT NULL) THEN
670 l_jtf_upd_itm_rec.END_DATE := l_get_upd_csch_items_rec.END_DATE_TIME;
671 ELSE
672 l_jtf_upd_itm_rec.END_DATE := l_get_upd_csch_items_rec.ACTUAL_EXEC_END_DATE;
673 END IF;
674
675 l_jtf_upd_itm_rec.TIMEZONE_ID := l_get_upd_csch_items_rec.TIMEZONE_ID;
676 l_jtf_upd_itm_rec.URL := NULL;
677 l_jtf_upd_itm_rec.CREATED_BY := l_get_upd_csch_items_rec.CREATED_BY;
678 l_jtf_upd_itm_rec.CREATION_DATE := l_get_upd_csch_items_rec.CREATION_DATE;
679 l_jtf_upd_itm_rec.LAST_UPDATED_BY := l_get_upd_csch_items_rec.LAST_UPDATED_BY;
680 l_jtf_upd_itm_rec.LAST_UPDATE_DATE := l_get_upd_csch_items_rec.LAST_UPDATE_DATE;
681 l_jtf_upd_itm_rec.LAST_UPDATE_LOGIN := l_get_upd_csch_items_rec.LAST_UPDATE_LOGIN;
682 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_upd_csch_items_rec.OBJECT_VERSION_NUMBER;
683 l_jtf_upd_itm_rec.APPLICATION_ID := '530';
684
685 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
686 l_jtf_upd_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
687
688 -- get the CAL_ITEM_ID for each of the record under consideration
689 --BugFix 4676786
690 OPEN c_get_cal_item_id(l_jtf_upd_itm_rec.SOURCE_ID,l_jtf_upd_itm_rec.CAL_RESOURCE_ID);
691 LOOP
692 FETCH c_get_cal_item_id INTO l_get_cal_item_rec;
693 IF (c_get_cal_item_id%NOTFOUND) THEN
694 x_return_status := FND_API.G_RET_STS_SUCCESS;
695 CLOSE c_get_cal_item_id;
696 l_get_cal_item_rec := NULL;
697 EXIT;
698 ELSE
699 l_jtf_upd_itm_rec.CAL_ITEM_ID := l_get_cal_item_rec.CAL_ITEM_ID;
700 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_cal_item_rec.OBJECT_VERSION_NUMBER;
701 --End BugFix 4676786
702 JTF_CAL_ITEMS_PUB.UPDATEITEM( p_api_version => x_api_version
703 , p_init_msg_list => x_init_msg_list
704 , p_commit => x_commit
705 , x_return_status => x_return_status
706 , x_msg_count => x_msg_count
707 , x_msg_data => x_msg_data
708 , p_itm_rec => l_jtf_upd_itm_rec
709 , x_object_version_number =>l_cal_obj_ver_number
710 );
711 END IF;
712 END LOOP;
713 END LOOP;
714 END IF;
715 END LOOP;
716 END IF;
717
718 IF ( (x_criteria_rec.OBJECT_TYPE = 'EVEO') OR (x_criteria_rec.OBJECT_TYPE = 'EONE') ) THEN
719 OPEN c_get_upd_eveo_items(x_criteria_rec);
720 LOOP
721 FETCH c_get_upd_eveo_items INTO l_get_upd_eveo_items_rec;
722 IF (c_get_upd_eveo_items%NOTFOUND) THEN
723 x_return_status := FND_API.G_RET_STS_SUCCESS;
724 CLOSE c_get_upd_eveo_items;
725 l_get_upd_eveo_items_rec := NULL;
726 EXIT;
727 ELSE
728 l_jtf_upd_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP';
729 l_jtf_upd_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
730 l_jtf_upd_itm_rec.ITEM_NAME := NULL; --l_get_upd_eveo_items_rec.EVENT_OFFER_NAME;
731 l_jtf_upd_itm_rec.ITEM_DESCRIPTION := NULL; --l_get_upd_eveo_items_rec.description;
732 l_jtf_upd_itm_rec.SOURCE_CODE := 'AMS_'|| x_criteria_rec.OBJECT_TYPE; ---Removed Hardcoded value for EONE UPDATE Bug: 5178344
733 l_jtf_upd_itm_rec.SOURCE_ID := l_get_upd_eveo_items_rec.EVENT_OFFER_ID;
734 l_jtf_upd_itm_rec.START_DATE := l_get_upd_eveo_items_rec.EVENT_START_DATE_TIME;
735 l_jtf_upd_itm_rec.END_DATE := l_get_upd_eveo_items_rec.EVENT_END_DATE_TIME;
736 l_jtf_upd_itm_rec.TIMEZONE_ID := l_get_upd_eveo_items_rec.TIMEZONE_ID;
737 l_jtf_upd_itm_rec.URL := NULL;
738 l_jtf_upd_itm_rec.CREATED_BY := l_get_upd_eveo_items_rec.CREATED_BY;
739 l_jtf_upd_itm_rec.CREATION_DATE := l_get_upd_eveo_items_rec.CREATION_DATE;
740 l_jtf_upd_itm_rec.LAST_UPDATED_BY := l_get_upd_eveo_items_rec.LAST_UPDATED_BY;
741 l_jtf_upd_itm_rec.LAST_UPDATE_DATE := l_get_upd_eveo_items_rec.LAST_UPDATE_DATE;
742 l_jtf_upd_itm_rec.LAST_UPDATE_LOGIN := l_get_upd_eveo_items_rec.LAST_UPDATE_LOGIN;
743 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_upd_eveo_items_rec.OBJECT_VERSION_NUMBER;
744 l_jtf_upd_itm_rec.APPLICATION_ID := '530';
745
746 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
747 l_jtf_upd_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
748
749 -- get the CAL_ITEM_ID for each of the record under consideration
750 --BugFix 4676786
751 OPEN c_get_e_cal_item_id(l_jtf_upd_itm_rec.SOURCE_ID,l_jtf_upd_itm_rec.CAL_RESOURCE_ID);
752 LOOP
753 FETCH c_get_e_cal_item_id INTO l_get_e_cal_item_rec;
754 IF (c_get_e_cal_item_id%NOTFOUND) THEN
755 x_return_status := FND_API.G_RET_STS_SUCCESS;
756 CLOSE c_get_e_cal_item_id;
757 l_get_e_cal_item_rec := NULL;
758 EXIT;
759 ELSE
760 l_jtf_upd_itm_rec.CAL_ITEM_ID := l_get_e_cal_item_rec.CAL_ITEM_ID;
761 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_e_cal_item_rec.OBJECT_VERSION_NUMBER;
762 --End BugFix 4676786
763 JTF_CAL_ITEMS_PUB.UPDATEITEM( p_api_version => x_api_version
764 , p_init_msg_list => x_init_msg_list
765 , p_commit => x_commit
769 , p_itm_rec => l_jtf_upd_itm_rec
766 , x_return_status => x_return_status
767 , x_msg_count => x_msg_count
768 , x_msg_data => x_msg_data
770 , x_object_version_number =>l_cal_obj_ver_number
771 );
772 END IF;
773 END LOOP;
774 END LOOP;
775 END IF;
776 END LOOP;
777 END IF;
778
779 IF (x_criteria_rec.OBJECT_TYPE = 'OFFR') THEN
780 OPEN c_get_upd_offr_items(x_criteria_rec);
781 LOOP
782 FETCH c_get_upd_offr_items INTO l_get_upd_offr_items_rec;
783 IF (c_get_upd_offr_items%NOTFOUND) THEN
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785 CLOSE c_get_upd_offr_items;
786 l_get_upd_offr_items_rec := NULL;
787 EXIT;
788 ELSE
789 l_jtf_upd_itm_rec.CAL_RESOURCE_TYPE := 'RS_GROUP';
790 l_jtf_upd_itm_rec.ITEM_TYPE_CODE := 'CALENDAR';
791 l_jtf_upd_itm_rec.ITEM_NAME := NULL; --l_get_upd_offr_items_rec.name;
792 l_jtf_upd_itm_rec.ITEM_DESCRIPTION := NULL; --l_get_upd_offr_items_rec.DESCRIPTION;
793 l_jtf_upd_itm_rec.SOURCE_CODE := 'AMS_OFFR';
794 l_jtf_upd_itm_rec.SOURCE_ID := l_get_upd_offr_items_rec.QP_LIST_HEADER_ID;
795 IF (l_get_upd_offr_items_rec.START_DATE_ACTIVE IS NOT NULL) THEN
796 l_jtf_upd_itm_rec.START_DATE := l_get_upd_offr_items_rec.START_DATE_ACTIVE;
797 ELSE
798 l_jtf_upd_itm_rec.START_DATE := l_get_upd_offr_items_rec.CREATION_DATE;
799 END IF;
800
801 IF (l_get_upd_offr_items_rec.END_DATE_ACTIVE IS NOT NULL) THEN
802 l_jtf_upd_itm_rec.END_DATE := l_get_upd_offr_items_rec.END_DATE_ACTIVE;
803 ELSE
804 l_jtf_upd_itm_rec.END_DATE := add_months(SYSDATE, 60); --add 5 years here
805 END IF;
806
807 l_jtf_upd_itm_rec.TIMEZONE_ID := '4'; --l_get_upd_offr_items_rec.TIMEZONE_ID;
808 l_jtf_upd_itm_rec.URL := NULL;
809 l_jtf_upd_itm_rec.CREATED_BY := l_get_upd_offr_items_rec.CREATED_BY;
810 l_jtf_upd_itm_rec.CREATION_DATE := l_get_upd_offr_items_rec.CREATION_DATE;
811 l_jtf_upd_itm_rec.LAST_UPDATED_BY := l_get_upd_offr_items_rec.LAST_UPDATED_BY;
812 l_jtf_upd_itm_rec.LAST_UPDATE_DATE := l_get_upd_offr_items_rec.LAST_UPDATE_DATE;
813 l_jtf_upd_itm_rec.LAST_UPDATE_LOGIN := l_get_upd_offr_items_rec.LAST_UPDATE_LOGIN;
814 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_upd_offr_items_rec.OBJECT_VERSION_NUMBER;
815 l_jtf_upd_itm_rec.APPLICATION_ID := '530';
816
817 FOR i in t_group_id.FIRST .. t_group_id.LAST LOOP
818 l_jtf_upd_itm_rec.CAL_RESOURCE_ID := t_group_id(i);
819
820 -- get the CAL_ITEM_ID for each of the record under consideration
821 --BugFix 4676786
822 OPEN c_get_o_cal_item_id(l_jtf_upd_itm_rec.SOURCE_ID,l_jtf_upd_itm_rec.CAL_RESOURCE_ID);
823 LOOP
824 FETCH c_get_o_cal_item_id INTO l_get_o_cal_item_rec;
825 IF (c_get_o_cal_item_id%NOTFOUND) THEN
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827 CLOSE c_get_o_cal_item_id;
828 l_get_o_cal_item_rec := NULL;
829 EXIT;
830 ELSE
831 l_jtf_upd_itm_rec.CAL_ITEM_ID := l_get_o_cal_item_rec.CAL_ITEM_ID;
832 l_jtf_upd_itm_rec.OBJECT_VERSION_NUMBER := l_get_o_cal_item_rec.OBJECT_VERSION_NUMBER;
833 --End BugFix 4676786
834 JTF_CAL_ITEMS_PUB.UPDATEITEM( p_api_version => x_api_version
835 , p_init_msg_list => x_init_msg_list
836 , p_commit => x_commit
837 , x_return_status => x_return_status
838 , x_msg_count => x_msg_count
839 , x_msg_data => x_msg_data
840 , p_itm_rec => l_jtf_upd_itm_rec
841 , x_object_version_number =>l_cal_obj_ver_number
842 );
843 END IF;
844 END LOOP;
845 END LOOP;
846 END IF;
847 END LOOP;
848 END IF;
849 END IF;
850 COMMIT;
851 END Update_Cal_Items;
852
853 PROCEDURE Delete_Cal_Items
854 ( x_full_mode IN VARCHAR2 := 'N',
855 x_api_version IN NUMBER := 1.0,
856 x_init_msg_list IN VARCHAR2 := FND_API.G_False,
857 x_commit IN VARCHAR2 := FND_API.G_False,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_count OUT NOCOPY NUMBER,
860 x_msg_data OUT NOCOPY VARCHAR2,
861 x_criteria_rec IN cal_criteria_rec,
862 x_obj_id IN NUMBER := 0
863 )
864 IS
865
866 --------------------------------------------------------------------------------------------------------------------------------------
867 CURSOR c_get_del_item (x_obj_id NUMBER) IS
868 SELECT cal.cal_item_id
869 ,cal.object_version_number
870 FROM JTF_CAL_ITEMS_B cal
871 WHERE (cal.SOURCE_ID = x_obj_id);
872 l_get_del_item_rec c_get_del_item%ROWTYPE;
873 --------------------------------------------------------------------------------------------------------------------------------------
874
875 CURSOR c_get_del_csch_items (x_criteria_rec cal_criteria_rec) IS
876 SELECT cal.cal_item_id
877 , cal.object_version_number
878 FROM AMS_CAMPAIGN_SCHEDULES_B obj, JTF_CAL_ITEMS_B cal, AMS_CAMPAIGNS_ALL_B b
879 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
880 AND obj.ACTIVITY_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.ACTIVITY_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
884 AND cal.SOURCE_ID(+) = obj.SCHEDULE_ID
881 AND DECODE(x_criteria_rec.ACTIVITY_ID,NULL,-9999,obj.ACTIVITY_ID) = DECODE(x_criteria_rec.ACTIVITY_ID, NULL, -9999, x_criteria_rec.ACTIVITY_ID)
882 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
883 AND obj.SCHEDULE_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.SCHEDULE_ID, x_criteria_rec.OBJECT_ID)
885 AND obj.CAMPAIGN_ID = b.CAMPAIGN_ID
886 AND cal.cal_item_id IS NOT NULL
887 AND obj.START_DATE_TIME IS NOT NULL
888 AND b.ACTUAL_EXEC_END_DATE IS NOT NULL
889 AND obj.START_DATE_TIME <= nvl(obj.END_DATE_TIME, b.actual_exec_end_date)
890 AND obj.TIMEZONE_ID IS NOT NULL
891 AND (
892 (TRUNC(obj.START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
893 AND (TRUNC(nvl(obj.END_DATE_TIME, b.ACTUAL_EXEC_END_DATE)) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(nvl(obj.END_DATE_TIME, b.actual_exec_end_date)), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
894 )
895 );
896 l_get_del_csch_items_rec c_get_del_csch_items%ROWTYPE;
897
898 --BugFix
899
900 CURSOR c_get_del_eveo_items (x_criteria_rec cal_criteria_rec) IS
901 SELECT cal.cal_item_id
902 , cal.object_version_number
903 FROM ams_event_offers_all_b obj, JTF_CAL_ITEMS_B cal
904 WHERE (obj.SETUP_TYPE_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.SETUP_TYPE_ID, x_criteria_rec.CUSTOM_SETUP_ID)
905 AND obj.EVENT_TYPE_CODE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.EVENT_TYPE_CODE, x_criteria_rec.ACTIVITY_TYPE_CODE)
906 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
907 AND obj.EVENT_OFFER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.EVENT_OFFER_ID, x_criteria_rec.OBJECT_ID)
908 AND cal.SOURCE_ID(+) = obj.EVENT_OFFER_ID
909 AND obj.event_standalone_flag = DECODE(x_criteria_rec.OBJECT_TYPE, 'EVEO', 'N', 'EONE', 'Y')
910 AND cal.cal_item_id IS NOT NULL
911 AND obj.EVENT_START_DATE_TIME IS NOT NULL
912 AND obj.EVENT_END_DATE_TIME IS NOT NULL
913 AND obj.EVENT_START_DATE_TIME <= obj.EVENT_END_DATE_TIME
914 AND obj.TIMEZONE_ID IS NOT NULL
915 AND (
916 (TRUNC(obj.EVENT_START_DATE_TIME) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(obj.EVENT_START_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
917 AND (TRUNC(obj.EVENT_END_DATE_TIME) <= DECODE(x_criteria_rec.CRITERIA_END_DATE, NULL, TRUNC(obj.EVENT_END_DATE_TIME), TRUNC(x_criteria_rec.CRITERIA_END_DATE)))
918 )
919 );
920 l_get_del_eveo_items_rec c_get_del_eveo_items%ROWTYPE;
921
922 CURSOR c_get_del_offr_items (x_criteria_rec cal_criteria_rec) IS
923 SELECT cal.cal_item_id
924 , cal.object_version_number
925 FROM OZF_OFFERS obj, JTF_CAL_ITEMS_B cal, qp_list_headers_vl g
926 WHERE (obj.CUSTOM_SETUP_ID = DECODE(x_criteria_rec.CUSTOM_SETUP_ID, NULL, obj.CUSTOM_SETUP_ID, x_criteria_rec.CUSTOM_SETUP_ID)
927 AND obj.OFFER_TYPE = DECODE(x_criteria_rec.ACTIVITY_TYPE_CODE, NULL, obj.OFFER_TYPE, x_criteria_rec.ACTIVITY_TYPE_CODE)
928 AND obj.USER_STATUS_ID = DECODE(x_criteria_rec.STATUS_ID, NULL, obj.USER_STATUS_ID, x_criteria_rec.STATUS_ID)
929 AND obj.QP_LIST_HEADER_ID = DECODE(x_criteria_rec.OBJECT_ID, NULL, obj.QP_LIST_HEADER_ID, x_criteria_rec.OBJECT_ID)
930 AND cal.SOURCE_ID(+) = obj.QP_LIST_HEADER_ID
931 AND g.LIST_HEADER_ID = obj.QP_LIST_HEADER_ID
932 AND cal.cal_item_id IS NOT NULL
933 AND (
934 (TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)) >= DECODE(x_criteria_rec.CRITERIA_START_DATE, NULL, TRUNC(nvl(g.START_DATE_ACTIVE, obj.CREATION_DATE)), TRUNC(x_criteria_rec.CRITERIA_START_DATE)))
935 )
936 );
937 l_get_del_offr_items_rec c_get_del_offr_items%ROWTYPE;
938 --------------------------------------------------------------------------------------------------------------------------------------
939 BEGIN
940 IF (x_obj_id > 0) THEN
941 OPEN C_GET_DEL_ITEM(x_obj_id);
942 FETCH C_GET_DEL_ITEM INTO l_get_del_item_rec;
943 IF (C_GET_DEL_ITEM%NOTFOUND) THEN
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945 CLOSE C_GET_DEL_ITEM;
946 l_get_del_item_rec := NULL;
947 ELSE
948 JTF_CAL_ITEMS_PUB.DELETEITEM( p_api_version => x_api_version
949 , p_init_msg_list => x_init_msg_list
950 , p_commit => x_commit
951 , x_return_status => x_return_status
952 , x_msg_count => x_msg_count
953 , x_msg_data => x_msg_data
954 , p_cal_item_id => l_get_del_item_rec.cal_item_id
955 , p_object_version_number => l_get_del_item_rec.object_version_number
956 );
957 END IF;
958 ELSE
959 IF (x_criteria_rec.OBJECT_TYPE = 'CSCH') THEN
960 OPEN c_get_del_csch_items(x_criteria_rec);
961 LOOP
962 FETCH c_get_del_csch_items INTO L_GET_DEL_CSCH_ITEMS_REC;
963 IF (c_get_del_csch_items%NOTFOUND) THEN
964 x_return_status := FND_API.G_RET_STS_SUCCESS;
965 CLOSE c_get_del_csch_items;
966 L_GET_DEL_CSCH_ITEMS_REC := NULL;
967 EXIT;
968 ELSE
969 JTF_CAL_ITEMS_PUB.DELETEITEM( p_api_version => x_api_version
970 , p_init_msg_list => x_init_msg_list
971 , p_commit => x_commit
972 , x_return_status => x_return_status
973 , x_msg_count => x_msg_count
974 , x_msg_data => x_msg_data
975 , p_cal_item_id => L_GET_DEL_CSCH_ITEMS_REC.cal_item_id
976 , p_object_version_number => L_GET_DEL_CSCH_ITEMS_REC.object_version_number
977 );
978 END IF;
979 END LOOP;
980 END IF;
981
982 IF ( (x_criteria_rec.OBJECT_TYPE = 'EVEO') OR (x_criteria_rec.OBJECT_TYPE = 'EONE') )THEN
983 OPEN c_get_del_eveo_items(x_criteria_rec);
984 LOOP
985 FETCH c_get_del_eveo_items INTO l_get_del_eveo_items_rec;
986 IF (c_get_del_eveo_items%NOTFOUND) THEN
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988 CLOSE c_get_del_eveo_items;
989 l_get_del_eveo_items_rec := NULL;
990 EXIT;
991 ELSE
992 JTF_CAL_ITEMS_PUB.DELETEITEM( p_api_version => x_api_version
993 , p_init_msg_list => x_init_msg_list
994 , p_commit => x_commit
995 , x_return_status => x_return_status
996 , x_msg_count => x_msg_count
997 , x_msg_data => x_msg_data
998 , p_cal_item_id => l_get_del_eveo_items_rec.cal_item_id
999 , p_object_version_number => l_get_del_eveo_items_rec.object_version_number
1000 );
1001 END IF;
1002 END LOOP;
1003 END IF;
1004 IF (x_criteria_rec.OBJECT_TYPE = 'OFFR') THEN
1005 OPEN c_get_del_offr_items(x_criteria_rec);
1006 LOOP
1007 FETCH c_get_del_offr_items INTO l_get_del_offr_items_rec;
1008 IF (c_get_del_offr_items%NOTFOUND) THEN
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010 CLOSE c_get_del_offr_items;
1011 l_get_del_offr_items_rec := NULL;
1012 EXIT;
1013 ELSE
1014 JTF_CAL_ITEMS_PUB.DELETEITEM( p_api_version => x_api_version
1015 , p_init_msg_list => x_init_msg_list
1016 , p_commit => x_commit
1017 , x_return_status => x_return_status
1018 , x_msg_count => x_msg_count
1019 , x_msg_data => x_msg_data
1020 , p_cal_item_id => l_get_del_offr_items_rec.cal_item_id
1021 , p_object_version_number => l_get_del_offr_items_rec.object_version_number
1022 );
1023 END IF;
1024 END LOOP;
1025 END IF;
1026 END IF;
1027 COMMIT;
1028 END Delete_Cal_Items;
1029
1030 END AMS_Sync_Calendar_PVT;