[Home] [Help]
PACKAGE BODY: APPS.OZF_SCHEDULE_DENORM_PVT
Source
1 PACKAGE BODY OZF_SCHEDULE_DENORM_PVT AS
2 /* $Header: ozfvscdb.pls 120.1 2006/04/12 10:46:20 gramanat noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='OZF_SCHEDULE_DENORM_PVT';
5
6
7 PROCEDURE initial_load(l_org_id IN NUMBER )
8 IS
9
10 CURSOR c_items_hl_denorm IS
11 SELECT activity_hl_id, schedule_id,item, item_type
12 FROM ozf_activity_hl_denorm
13 WHERE item is not null;
14
15
16 CURSOR c_activity_person IS
17 SELECT distinct am.campaign_id,
18 ct.campaign_name,
19 am.campaign_class,
20 am.campaign_status,
21 am.campaign_type,
22 am.confidential_flag,
23 s.schedule_id,
24 'CSCH' object_class,
25 s.schedule_name,
26 s.status_code,
27 s.source_code,
28 s.start_date_time,
29 s.end_date_time,
30 s.owner_user_id,
31 s.activity_type_code,
32 s.activity_id,
33 s.marketing_medium_id,
34 am.custom_setup_id,
35 ps.party_id,
36 cs.cust_account_id,
37 cs.cust_acct_site_id,
38 cu.site_use_id,
39 cu.site_use_code,
40 decode(cu.site_use_code,'BILL_TO','QUALIFIER_ATTRIBUTE14','SHIP_TO','QUALIFIER_ATTRIBUTE11') qualifier_attribute,
41 'CUSTOMER' qualifier_context,
42 nvl(am.item_type,'ALLPRODUCTS') prod_indicator
43 FROM ams_campaign_schedules_vl s,
44 ozf_activity_hl_denorm am,
45 ams_campaigns_vl ct,
46 ams_list_entries le,
47 hz_parties b, hz_party_sites ps,hz_locations f,
48 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu,hz_locations hl, hz_party_sites hp
49 WHERE
50 s.schedule_id = am.schedule_id and
51 ct.campaign_id = s.campaign_id and
52 am.list_header_id is not null and
53 le.list_header_id = am.list_header_id and
54 le.party_id = b.party_id
55 and b.party_type = 'PERSON'
56 and ps.party_id = b.party_id
57 and ps.identifying_address_flag = 'Y'
58 and f.location_id = ps.location_id
59 and hl.city = f.city
60 and hl.state = f.state
61 and hl.postal_code = f.postal_code
62 and hl.country = f.country
63 and hp.location_id = hl.location_id
64 and hp.party_site_id = cs.party_site_id
65 and cu.cust_acct_site_id = cs.cust_acct_site_id
66 and cu.site_use_code in ('SHIP_TO','BILL_TO');
67
68 CURSOR c_activity_relationship IS
69 SELECT distinct am.campaign_id,
70 ct.campaign_name,
71 am.campaign_class,
72 am.campaign_status,
73 am.campaign_type,
74 am.confidential_flag,
75 s.schedule_id,
76 'CSCH' object_class,
77 s.schedule_name,
78 s.status_code,
79 s.source_code,
80 s.start_date_time,
81 s.end_date_time,
82 s.owner_user_id,
83 s.activity_type_code,
84 s.activity_id,
85 s.marketing_medium_id,
86 am.custom_setup_id,
87 hr.subject_id party_id,
88 cs.cust_account_id,
89 cs.cust_acct_site_id,
90 cu.site_use_id,
91 cu.site_use_code,
92 decode(cu.site_use_code,'BILL_TO','QUALIFIER_ATTRIBUTE14','SHIP_TO','QUALIFIER_ATTRIBUTE11') qualifier_attribute,
93 'CUSTOMER' qualifier_context,
94 nvl(am.item_type,'ALLPRODUCTS') prod_indicator
95 FROM ozf_activity_hl_denorm am,
96 ams_campaign_schedules_vl s,
97 ams_campaigns_vl ct,
98 ams_list_entries le,
99 hz_parties b, hz_cust_accounts ca,
100 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu, hz_relationships hr
101 WHERE
102 s.schedule_id = am.schedule_id
103 and ct.campaign_id = s.campaign_id
104 and am.list_header_id is not null
105 and le.list_header_id = am.list_header_id
106 and le.party_id = b.party_id
107 and b.party_type = 'PARTY_RELATIONSHIP'
108 and hr.party_id = b.party_id
109 and hr.subject_type = 'ORGANIZATION'
110 and ca.party_id = hr.subject_id
111 and cs.cust_account_id = ca.cust_account_id
112 and cu.cust_acct_site_id = cs.cust_acct_site_id
113 and cu.site_use_code in ('SHIP_TO','BILL_TO');
114
115 CURSOR c_activity_organization IS
116 SELECT distinct am.campaign_id,
117 ct.campaign_name,
118 am.campaign_class,
119 am.campaign_status,
120 am.campaign_type,
121 am.confidential_flag,
122 s.schedule_id,
123 'CSCH' object_class,
124 s.schedule_name,
125 s.status_code,
126 s.source_code,
127 s.start_date_time,
128 s.end_date_time,
129 s.owner_user_id,
130 s.activity_type_code,
131 s.activity_id,
132 s.marketing_medium_id,
133 am.custom_setup_id,
134 b.party_id,
135 cs.cust_account_id,
136 cs.cust_acct_site_id,
137 cu.site_use_id,
138 cu.site_use_code,
139 decode(cu.site_use_code,'BILL_TO','QUALIFIER_ATTRIBUTE14','SHIP_TO','QUALIFIER_ATTRIBUTE11') qualifier_attribute,
140 'CUSTOMER' qualifier_context,
141 nvl(am.item_type,'ALLPRODUCTS') prod_indicator
142 FROM ozf_activity_hl_denorm am,
143 ams_campaign_schedules_vl s,
144 ams_campaigns_vl ct,
145 ams_list_entries le,
146 hz_parties b, hz_cust_accounts ca,
147 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu
148 WHERE
149 am.schedule_id = s.schedule_id
150 and ct.campaign_id = s.campaign_id
151 and le.list_header_id = am.list_header_id
152 and le.party_id = b.party_id
153 and b.party_type = 'ORGANIZATION'
154 and ca.party_id = b.party_id
155 and cs.cust_account_id = ca.cust_account_id
156 and cu.cust_acct_site_id = cs.cust_acct_site_id
157 and cu.site_use_code in ('SHIP_TO','BILL_TO');
158
159 CURSOR c_all_campaigns IS
160 SELECT c.campaign_id, c.status_code, c.rollup_type, c.campaign_type,
161 c.campaign_name,c.private_flag
162 FROM
163 ams_campaigns_vl c
164 WHERE
165 c.status_code in ('ACTIVE', 'AVAILABLE', 'COMPLETED', 'PENDINGAPPROVAL');
166
167
168 CURSOR c_activity_product_family IS
169 SELECT distinct am.campaign_id, ct.campaign_name, am.campaign_class, am.campaign_status, am.campaign_type,am.confidential_flag,
170 s.schedule_id, 'CSCH' object_class,s.schedule_name,s.status_code,s.source_code, s.start_date_time, s.end_date_time,
171 s.owner_user_id, am.item,
172 decode(am.item_type,'PRODUCT','PRICING_ATTRIBUTE1','FAMILY','PRICING_ATTRIBUTE2','') item_type,
173 s.activity_type_code, s.activity_id, s.marketing_medium_id,am.custom_setup_id,
174 decode(am.list_header_id,null,'ALLCUSTOMERS','CUSTOMERS') cust_indicator
175 FROM
176 ozf_activity_hl_denorm am,
177 ams_campaign_schedules_vl s,
178 ams_campaigns_vl ct
179 WHERE
180 am.schedule_id = s.schedule_id
181 and ct.campaign_id = s.campaign_id;
182
183 CURSOR c_activity_products IS
184 SELECT am.campaign_id, ct.campaign_name, am.campaign_class, am.campaign_status, am.campaign_type,am.confidential_flag,
185 s.schedule_id, 'CSCH' object_class,s.schedule_name,s.status_code, s.source_code, s.start_date_time, s.end_date_time,
186 s.owner_user_id, mtl.inventory_item_id item, 'PRICING_ATTRIBUTE1' item_type,
187 s.activity_type_code, s.activity_id, s.marketing_medium_id,am.custom_setup_id,
188 decode(am.list_header_id,null,'ALLCUSTOMERS','CUSTOMERS') cust_indicator
189 FROM
190 ozf_activity_hl_denorm am,
191 ams_campaign_schedules_vl s,
192 ams_campaigns_vl ct,
193 ams_act_products ap,
194 mtl_item_categories mtl,
195 eni_prod_denorm_hrchy_v eni
196 WHERE
197 am.schedule_id = s.schedule_id
198 and ct.campaign_id = s.campaign_id
199 and ap.act_product_used_by_id = s.schedule_id
200 and ap.arc_act_product_used_by = 'CSCH'
201 and ap.level_type_code = 'FAMILY'
202 and mtl.category_set_id = eni.category_set_id
203 and mtl.category_id = eni.child_id
204 and eni.parent_id = ap.category_id
205 and mtl.organization_id = l_org_id;
206
207 BEGIN
208
209 ozf_utility_pvt.write_conc_log('-- Initial Load Start -- '|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
210 ozf_utility_pvt.write_conc_log('-- Insert into HLD -- ');
211
212 /* Drop all the indexes in ozf_activity_hl_denorm */
213
214 /* Recreate all the indexes on ozf_activity_hl_denorm */
215 FOR i IN c_all_campaigns LOOP
216 INSERT into ozf_activity_hl_denorm
217 (Activity_hl_id,Schedule_Id,Campaign_id,Campaign_status,Campaign_class,
218 Campaign_type,Campaign_name,Confidential_flag,Item,Item_type,List_header_id,Custom_setup_id,
219 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
220 SELECT ozf_activity_hl_denorm_s.nextval, s.schedule_id, i.campaign_id, i.status_code,
221 i.rollup_type, i.campaign_type, i.campaign_name,i.private_flag,
222 decode(ap.inventory_item_id, null, ap.category_id,ap.inventory_item_id) item,
223 ap.level_type_code item_type, al.list_header_id,s.custom_setup_id,
224 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id
225 FROM
226 ams_campaign_schedules_b s,
227 ams_act_products ap,
228 ams_act_lists al
229 WHERE
230 s.campaign_id = i.campaign_id AND
231 ap.act_product_used_by_id(+) = s.schedule_id AND
232 ap.arc_act_product_used_by(+) = 'CSCH' and
233 al.list_act_type(+) = 'TARGET' AND al.list_used_by(+) = 'CSCH' AND
234 al.list_used_by_id(+) = s.schedule_id;
235 END LOOP;
236
237 ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 1 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
238
239 FOR i IN c_activity_person LOOP
240 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
241 parent_status, parent_type, confidential_flag,object_id,object_class, object_desc,object_status,source_code,
242 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,custom_setup_id,
243 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,
244 qualifier_attribute,qualifier_context,prod_indicator,
245 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
246 values(ozf_activity_customers_s.nextval,
247 i.campaign_id,
248 i.campaign_name,
249 i.campaign_class,
250 i.campaign_status,
251 i.campaign_type,
252 i.confidential_flag,
253 i.schedule_id,
254 i.object_class,
255 i.schedule_name,
256 i.status_code,
257 i.source_code,
258 i.start_date_time,
259 i.end_date_time,
260 i.owner_user_id,
261 i.activity_type_code,
262 i.activity_id,
263 i.marketing_medium_id,
264 i.custom_setup_id,
265 i.party_id,i.cust_account_id,i.cust_acct_site_id,i.site_use_id,i.site_use_code,
266 i.qualifier_attribute,i.qualifier_context, i.prod_indicator,
267 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
268 END LOOP;
269
270
271 ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 2 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
272
273 FOR i IN c_activity_relationship LOOP
274 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
275 parent_status, parent_type,confidential_flag, object_id,object_class, object_desc,object_status,source_code,
276 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,custom_setup_id,
277 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,
278 qualifier_attribute,qualifier_context,prod_indicator,
279 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
280 values(ozf_activity_customers_s.nextval,
281 i.campaign_id, i.campaign_name,i.campaign_class, i.campaign_status, i.campaign_type,i.confidential_flag,
282 i.schedule_id,i.object_class,i.schedule_name,i.status_code,i.source_code,i.start_date_time,i.end_date_time,
283 i.owner_user_id, i.activity_type_code,i.activity_id,i.marketing_medium_id,i.custom_setup_id,
284 i.party_id,i.cust_account_id,i.cust_acct_site_id,i.site_use_id,i.site_use_code,
285 i.qualifier_attribute,i.qualifier_context, i.prod_indicator,
286 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
287 END LOOP;
288
289
290 ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 3 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
291
292 FOR i IN c_activity_organization LOOP
293 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
294 parent_status, parent_type,confidential_flag, object_id,object_class, object_desc,object_status,source_code,
295 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,custom_setup_id,
296 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,
297 qualifier_attribute,qualifier_context,prod_indicator,
298 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
299 values(ozf_activity_customers_s.nextval,
300 i.campaign_id, i.campaign_name,i.campaign_class, i.campaign_status, i.campaign_type,i.confidential_flag,
301 i.schedule_id,i.object_class,i.schedule_name,i.status_code,i.source_code,i.start_date_time,i.end_date_time,
302 i.owner_user_id, i.activity_type_code,i.activity_id,i.marketing_medium_id,i.custom_setup_id,
303 i.party_id,i.cust_account_id,i.cust_acct_site_id,i.site_use_id,i.site_use_code,
304 i.qualifier_attribute,i.qualifier_context, i.prod_indicator,
305 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
306 END LOOP;
307
308
309 ozf_utility_pvt.write_conc_log('-- Insert into Activity Products 1 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
310
314 start_date, end_date, owner_id,item,item_type, activity_type_code,activity_id, marketing_medium_id,custom_setup_id,
311 FOR i in c_activity_product_family LOOP
312 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
313 parent_status, parent_type, confidential_flag,object_id,object_class, object_desc,object_status,source_code,
315 cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
316 values (ozf_activity_products_s.nextval,i.campaign_id, i.campaign_name, i.campaign_class, i.campaign_status, i.campaign_type,i.confidential_flag,
317 i.schedule_id, i.object_class,i.schedule_name,i.status_code,i.source_code, i.start_date_time, i.end_date_time,
318 i.owner_user_id, i.item, i.item_type,
319 i.activity_type_code, i.activity_id, i.marketing_medium_id,i.custom_setup_id,i.cust_indicator,
320 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
321 END LOOP;
322
323 ozf_utility_pvt.write_conc_log('-- Insert into Activity Products 2 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
324
325 FOR i in c_activity_products LOOP
326 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
327 parent_status, parent_type,confidential_flag, object_id,object_class, object_desc,object_status,source_code,
328 start_date, end_date, owner_id,item,item_type, activity_type_code,activity_id, marketing_medium_id,custom_setup_id,
329 cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
330 values (ozf_activity_products_s.nextval,i.campaign_id, i.campaign_name, i.campaign_class, i.campaign_status, i.campaign_type,i.confidential_flag,
331 i.schedule_id, i.object_class,i.schedule_name,i.status_code,i.source_code, i.start_date_time, i.end_date_time,
332 i.owner_user_id, i.item, i.item_type,
333 i.activity_type_code, i.activity_id, i.marketing_medium_id,i.custom_setup_id,i.cust_indicator,
334 sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
335 END LOOP;
336
337
338 ozf_utility_pvt.write_conc_log('-- Initial Load End --');
339
340 END;
341
342 PROCEDURE refresh_schedules(
343 ERRBUF OUT NOCOPY VARCHAR2,
344 RETCODE OUT NOCOPY VARCHAR2,
345 x_return_status OUT NOCOPY VARCHAR2,
346 p_increment_flag IN VARCHAR2 := 'N',
347 p_latest_comp_date IN DATE
348 )
349 IS
350
351
352 CURSOR c_new_items_hl_denorm(l_latest_comp_date DATE) IS
353 SELECT activity_hl_id
354 FROM ozf_activity_hl_denorm a, ams_act_products b
355 WHERE a.schedule_id = b.act_product_used_by_id
356 AND b.arc_act_product_used_by = 'CSCH'
357 AND b.creation_date > l_latest_comp_date;
358
359 CURSOR c_items_hl_denorm IS
360 SELECT activity_hl_id, schedule_id,item, item_type
361 FROM ozf_activity_hl_denorm
362 WHERE item is not null;
363
364 CURSOR c_new_schedules_cust(l_latest_comp_date DATE) IS
365 SELECT DISTINCT am.Schedule_Id,am.Campaign_id,am.Campaign_status,am.Campaign_class,am.Campaign_type,
366 am.Campaign_name,am.List_header_id,am.confidential_flag,am.item, am.item_type, b.party_id, b.party_type
367 FROM ozf_activity_hl_denorm am, ams_list_entries le, hz_parties b
368 WHERE am.schedule_update_date > l_latest_comp_date
369 AND am.list_header_id is not null
370 AND le.list_header_id = am.list_header_id
371 AND le.party_id = b.party_id;
372
373 CURSOR c_new_schedules_prod(l_latest_comp_date DATE) IS
374 SELECT DISTINCT am.Schedule_Id,am.Campaign_id,am.Campaign_status,am.Campaign_class,am.Campaign_type,
375 am.Campaign_name,am.list_header_id,am.confidential_flag, am.item, am.item_type
376 FROM ozf_activity_hl_denorm am
377 WHERE am.schedule_update_date > l_latest_comp_date
378 AND am.item is not null;
379
380 CURSOR c_changed_list_headers(l_latest_comp_date DATE) IS
381 SELECT DISTINCT a.schedule_id
382 FROM ozf_activity_hl_denorm a,ams_list_headers_all lh
383 WHERE a.list_header_id is not null
384 AND lh.list_header_id = a.list_header_id
385 AND lh.last_update_date > l_latest_comp_date;
386
387 CURSOR c_product_exists(l_item_id NUMBER, l_schedule_id NUMBER) IS
388 SELECT 'Y'
389 FROM DUAL
390 WHERE EXISTS (
391 SELECT 1
392 FROM ams_act_products
393 WHERE inventory_item_id = l_item_id
394 AND act_product_used_by_id = l_schedule_id
395 AND arc_act_product_used_by = 'CSCH'
396 AND level_type_code = 'PRODUCT'
397 );
398
399 CURSOR c_category_exists(l_category_id NUMBER, l_schedule_id NUMBER) IS
400 SELECT 'Y'
401 FROM DUAL
402 WHERE EXISTS (
403 SELECT 1
404 FROM ams_act_products
405 WHERE category_id = l_category_id
406 AND act_product_used_by_id = l_schedule_id
407 AND arc_act_product_used_by = 'CSCH'
408 AND level_type_code = 'FAMILY'
409 );
410
411
412 /* product_update_date is update when a new product is added during the incremental refresh */
413 CURSOR c_activity_incr_products(l_latest_comp_date DATE) IS
414 SELECT distinct a.parent_id, a.parent_desc, a.parent_class,
418 FROM ozf_activity_products a,ozf_activity_hl_denorm b
415 a.parent_status, a.parent_type,b.confidential_flag, a.object_id, a.object_type,a.object_status,a.object_class,
416 a.source_code, a.start_date, a.end_date, a.owner_id, b.item, b.item_type, a.activity_type_code,
417 a.activity_id, a.marketing_medium_id, a.cust_indicator
419 WHERE a.object_id = b.schedule_id
420 and a.object_class = 'CSCH'
421 and b.product_update_date > l_latest_comp_date;
422
423
424 /* Get the newly added Categories exploded into products */
425 CURSOR c_activity_incr_categories(l_latest_comp_date DATE,l_org_id NUMBER) IS
426 SELECT distinct a.parent_id, a.parent_desc, a.parent_class,
427 a.parent_status, a.parent_type,b.confidential_flag, a.object_id, a.object_type,a.object_status,a.object_class,
428 a.source_code,a.start_date,a.end_date,a.owner_id,mtl.inventory_item_id item,'PRODUCT' item_type, a.activity_type_code,
429 a.activity_id, a.marketing_medium_id, a.cust_indicator
430 FROM ozf_activity_products a,
431 ozf_activity_hl_denorm b,
432 mtl_item_categories mtl,
433 eni_prod_denorm_hrchy_v eni
434 WHERE b.product_update_date > l_latest_comp_date
435 and b.item_type = 'FAMILY'
436 and a.object_id = b.schedule_id
437 and a.object_class = 'CSCH'
438 and eni.parent_id = b.item
439 and mtl.category_set_id = eni.category_set_id
440 and mtl.category_id = eni.child_id
441 and mtl.organization_id = l_org_id;
442
443
444 CURSOR c_activity_incr_person( l_schedule_id NUMBER,l_party_id NUMBER,l_item_type VARCHAR) IS
445 SELECT distinct s.campaign_id, ct.campaign_name, ct.rollup_type, ct.status_code parent_status,
446 ct.campaign_type,ct.private_flag, s.schedule_id, 'CSCH' object_class, s.schedule_name, s.status_code,s.source_code,
447 s.start_date_time, s.end_date_time, s.owner_user_id, s.activity_type_code,
448 s.activity_id, s.marketing_medium_id, ps.party_id,cs.cust_account_id,
449 cs.cust_acct_site_id, cu.site_use_id,cu.site_use_code,nvl(l_item_type,'ALLPRODUCTS') prod_indicator
450 FROM ams_campaign_schedules_vl s,
451 ams_campaigns_vl ct,
452 hz_party_sites ps,hz_locations f,
453 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu,hz_locations hl, hz_party_sites hp
454 WHERE
455 s.schedule_id = l_schedule_id
456 and ct.campaign_id = s.campaign_id
457 and ps.party_id = l_party_id
458 and ps.identifying_address_flag = 'Y'
459 and f.location_id = ps.location_id
460 and hl.city = f.city
461 and hl.state = f.state
462 and hl.postal_code = f.postal_code
463 and hl.country = f.country
464 and hp.location_id = hl.location_id
465 and hp.party_site_id = cs.party_site_id
466 and cu.cust_acct_site_id = cs.cust_acct_site_id
467 and cu.site_use_code in ('SHIP_TO','BILL_TO');
468
469 CURSOR c_activity_incr_relationship( l_schedule_id NUMBER,l_party_id NUMBER,l_item_type VARCHAR) IS
470 SELECT distinct s.campaign_id, ct.campaign_name, ct.rollup_type, ct.status_code parent_status,
471 ct.campaign_type,ct.private_flag, s.schedule_id, 'CSCH' object_class, s.schedule_name,s.status_code, s.source_code,
472 s.start_date_time, s.end_date_time, s.owner_user_id, s.activity_type_code,
473 s.activity_id, s.marketing_medium_id, hr.subject_id party_id, cs.cust_account_id,
474 cs.cust_acct_site_id, cu.site_use_id,cu.site_use_code,nvl(l_item_type,'ALLPRODUCTS') prod_indicator
475 FROM ams_campaign_schedules_vl s,
476 ams_campaigns_vl ct,
477 hz_cust_accounts ca,
478 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu, hz_relationships hr
479 WHERE
480 s.schedule_id = l_schedule_id
481 and ct.campaign_id = s.campaign_id
482 and hr.party_id = l_party_id
483 and hr.subject_type = 'ORGANIZATION'
484 and ca.party_id = hr.subject_id
485 and cs.cust_account_id = ca.cust_account_id
486 and cu.cust_acct_site_id = cs.cust_acct_site_id
487 and cu.site_use_code in ('SHIP_TO','BILL_TO');
488
489 CURSOR c_activity_incr_organization( l_schedule_id NUMBER,l_party_id NUMBER,l_item_type VARCHAR) IS
490 SELECT distinct s.campaign_id, ct.campaign_name, ct.rollup_type, ct.status_code parent_status,
491 ct.campaign_type,ct.private_flag, s.schedule_id, 'CSCH' object_class, s.schedule_name,s.status_code, s.source_code,
492 s.start_date_time, s.end_date_time, s.owner_user_id, s.activity_type_code,
493 s.activity_id, s.marketing_medium_id, ca.party_id,cs.cust_account_id,
494 cs.cust_acct_site_id, cu.site_use_id,cu.site_use_code,nvl(l_item_type,'ALLPRODUCTS') prod_indicator
495 FROM ams_campaign_schedules_vl s,
496 ams_campaigns_vl ct,
497 hz_cust_accounts ca,
498 hz_cust_acct_sites_all cs,hz_cust_site_uses_all cu
499 WHERE
500 s.schedule_id = l_schedule_id
501 and ct.campaign_id = s.campaign_id
502 and ca.party_id = l_party_id
506
503 and cs.cust_account_id = ca.cust_account_id
504 and cu.cust_acct_site_id = cs.cust_acct_site_id
505 and cu.site_use_code in ('SHIP_TO','BILL_TO');
507 CURSOR c_activity_incr_prod_new(l_schedule_id NUMBER, l_list_header_id NUMBER) IS
508 SELECT distinct s.campaign_id, ct.campaign_name, ct.rollup_type, ct.status_code parent_status,
509 ct.campaign_type,ct.private_flag, s.schedule_id, 'CSCH' object_class, s.schedule_name, s.status_code,s.source_code,
510 s.start_date_time, s.end_date_time, s.owner_user_id, s.activity_type_code,
511 s.activity_id, s.marketing_medium_id,
512 decode(ap.inventory_item_id, null, ap.category_id,ap.inventory_item_id) item,
513 ap.level_type_code item_type,
514 decode(l_list_header_id,null,'ALLCUSTOMERS','CUSTOMERS') cust_indicator
515 FROM ams_campaign_schedules_vl s,
516 ams_campaigns_vl ct,
517 ams_act_products ap
518 WHERE
519 s.schedule_id = l_schedule_id AND
520 ct.campaign_id = s.campaign_id and
521 ap.act_product_used_by_id = s.schedule_id AND
522 ap.arc_act_product_used_by = 'CSCH';
523
524 CURSOR c_activity_incr_cat_new(l_schedule_id NUMBER,l_org_id NUMBER,l_list_header_id NUMBER) IS
525 SELECT distinct s.campaign_id, ct.campaign_name, ct.rollup_type, ct.status_code parent_status,
526 ct.campaign_type,ct.private_flag, s.schedule_id, 'CSCH' object_class, s.schedule_name,s.status_code, s.source_code,
527 s.start_date_time, s.end_date_time, s.owner_user_id, s.activity_type_code,
528 s.activity_id, s.marketing_medium_id,
529 mtl.inventory_item_id item,
530 'PRODUCT' item_type,
531 decode(l_list_header_id,null,'ALLCUSTOMERS','CUSTOMERS') cust_indicator
532 FROM ams_campaign_schedules_vl s,
533 ams_campaigns_vl ct,
534 ams_act_products ap,
535 mtl_item_categories mtl,
536 eni_prod_denorm_hrchy_v eni
537 WHERE
538 s.schedule_id = l_schedule_id AND
539 ct.campaign_id = s.campaign_id AND
540 ap.act_product_used_by_id = s.schedule_id AND
541 ap.arc_act_product_used_by = 'CSCH' AND
542 ap.level_type_code = 'FAMILY' AND
543 mtl.category_set_id = eni.category_set_id AND
544 mtl.category_id = eni.child_id AND
545 eni.parent_id = ap.category_id AND
546 mtl.organization_id = l_org_id;
547
548 CURSOR c_incr_campaigns IS
549 SELECT c.campaign_id, c.status_code, c.rollup_type, c.campaign_type, c.campaign_name,c.private_flag
550 FROM
551 ams_campaigns_vl c
552 WHERE
553 c.status_code in ('ACTIVE', 'AVAILABLE', 'COMPLETED', 'PENDINGAPPROVAL');
554
555 l_api_version CONSTANT NUMBER := 1.0;
559 l_msg_count NUMBER;
556 l_api_name CONSTANT VARCHAR2(30) := 'refresh_denorm';
557 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
558 l_return_status VARCHAR2(1);
560 l_msg_data VARCHAR2(2000);
561
562 l_conc_program_id NUMBER;
563 l_app_id NUMBER;
564 l_latest_comp_date DATE;
565 l_offer_changed VARCHAR2(1);
566 l_qualifier_changed VARCHAR2(1);
567 l_dummy VARCHAR2(1);
568 l_index_tablespace VARCHAR2(100);
569 l_increment_flag VARCHAR2(1);
570
571 l_stmt_denorm VARCHAR2(32000) := NULL;
572 l_stmt_offer VARCHAR2(32000) := NULL;
573 l_stmt_product VARCHAR2(32000) := NULL;
574 l_stmt_hl_denorm VARCHAR2(32000) := NULL;
575 l_product_changed VARCHAR2(1) := NULL;
576 l_org_id NUMBER;
577
578 l_denorm_csr NUMBER;
579 l_ignore NUMBER;
580
581
582 BEGIN
583 SAVEPOINT refresh_schedule;
584
585 ERRBUF := NULL;
586 RETCODE := '0';
587
588 IF p_increment_flag = 'N' THEN
589 l_increment_flag := 'N' ;
590 ELSE
591 l_increment_flag := 'Y';
592 END IF;
593
594 l_org_id := FND_PROFILE.VALUE('QP_ORGANIZATION_ID');
595 l_latest_comp_date := p_latest_comp_date;
596
597 ozf_Utility_PVT.debug_message(l_full_name || ': Start Schedule Denorm');
598 ozf_utility_pvt.write_conc_log('-- pIncrement Flag is : ' || p_increment_flag );
599 ozf_utility_pvt.write_conc_log('-- lIncrement Flag is : ' || l_increment_flag );
600 ozf_utility_pvt.write_conc_log('-- l_latest_comp_date is: ' || l_latest_comp_date );
601 ozf_utility_pvt.write_conc_log('-- l_org_id is : ' || l_org_id );
602
603
604 IF NOT FND_API.compatible_api_call(l_api_version,
605 l_api_version,
606 l_api_name,
607 g_pkg_name)
608 THEN
609 RAISE FND_API.g_exc_unexpected_error;
610 END IF;
611
612
613 If l_increment_flag = 'N' then
614 /* Full Load or Initial Load */
615 -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_hl_denorm';
616
617 ozf_utility_pvt.write_conc_log('-- Full Load: Deleting from Temp Tables --');
618
619 --DELETE FROM ozf_activity_products_temp where object_class = 'CSCH' ;
620 --DELETE FROM ozf_activity_customers_temp where object_class = 'CSCH' ;
621
622 ozf_utility_pvt.write_conc_log('-- Full Load: Deleting from Denorm Tables --');
623
624 delete from ozf_activity_hl_denorm;
625 delete from ozf_activity_products where object_class = 'CSCH' ;
626 delete from ozf_activity_customers where object_class = 'CSCH' ;
627
628 initial_load(l_org_id);
629 else
630
631 /* Incremental Refresh */
632
633 ozf_utility_pvt.write_conc_log('-- Incremental Load Start --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
634
635 FOR i IN c_items_hl_denorm LOOP
636
637 l_product_changed := null;
638 if (i.item_type = 'PRODUCT') THEN
639 OPEN c_product_exists(i.item, i.schedule_id);
640 FETCH c_product_exists INTO l_product_changed;
641 CLOSE c_product_exists;
642 elsif (i.item_type = 'FAMILY') then
643 OPEN c_category_exists(i.item, i.schedule_id);
644 FETCH c_category_exists INTO l_product_changed;
645 CLOSE c_category_exists;
646 end if;
647
648 ozf_utility_pvt.write_conc_log('-- l_product_changed is : ' || l_product_changed || '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
649
650 IF l_product_changed IS NULL THEN
651 -- product has been deleted
652
653 ozf_utility_pvt.write_conc_log('-- Deleting product from denorm : '|| i.item_type
654 || ' - '
655 || i.item );
656
657 DELETE from ozf_activity_hl_denorm where activity_hl_id = i.activity_hl_id;
658
659 if (i.item_type = 'PRODUCT') then
660
661 DELETE from ozf_activity_products
662 WHERE item = i.item
663 AND object_id = i.schedule_id
664 AND object_class = 'CSCH';
665
666 elsif (i.item_type = 'FAMILY') then
667
668 DELETE from ozf_activity_products
669 WHERE object_id = i.schedule_id
670 AND object_class = 'CSCH'
671 AND items_category = i.item;
672
673 end if;
674
675 end if;
676
677 END LOOP;
678
679 ozf_utility_pvt.write_conc_log('-- Adding the new products to ozf_activity_hl_denorm table -- '|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
680
681 INSERT into ozf_activity_hl_denorm
682 (Activity_hl_id,Schedule_Id,Campaign_id,Campaign_status,Campaign_class,
683 Campaign_type,Campaign_name,confidential_flag,Item,Item_type,List_header_id,
684 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login,product_update_date)
685 SELECT ozf_activity_hl_denorm_s.nextval, Schedule_Id, Campaign_id, Campaign_status, Campaign_class, Campaign_type,
686 Campaign_name, confidential_flag,Item, Item_type, List_header_id,sysdate,null,sysdate,null,null,sysdate
687 FROM ozf_activity_hl_denorm a, ams_act_products b
688 WHERE a.schedule_id = b.act_product_used_by_id
689 AND b.arc_act_product_used_by = 'CSCH'
690 AND b.creation_date > l_latest_comp_date;
691
692 ozf_utility_pvt.write_conc_log('-- Adding the new product to the denorm table -- '|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
693
694 /* product_update_date is update when a new product is added during the incremental refresh */
695 FOR i in c_activity_incr_products(l_latest_comp_date) LOOP
696 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
697 parent_status, parent_type, confidential_flag,object_id,object_type,object_status,object_class, source_code,
698 start_date, end_date, owner_id, item, item_type, activity_type_code,
699 activity_id, marketing_medium_id, cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
700 values (ozf_activity_products_s.nextval,i.parent_id, i.parent_desc, i.parent_class,
701 i.parent_status, i.parent_type,i.confidential_flag, i.object_id, i.object_type,i.object_status,i.object_class,
702 i.source_code, i.start_date, i.end_date, i.owner_id, i.item, i.item_type, i.activity_type_code,
703 i.activity_id, i.marketing_medium_id, i.cust_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
704 END LOOP;
705
706 ozf_utility_pvt.write_conc_log('-- Adding category products to the denorm table -- '|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
707
708 /* Get the newly added Categories exploded into products */
709
710 FOR i in c_activity_incr_categories(l_latest_comp_date,l_org_id) LOOP
711 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
712 parent_status, parent_type,confidential_flag, object_id,object_type,object_status,object_class, source_code,
713 start_date, end_date, owner_id, item, item_type, activity_type_code,
717 i.source_code, i.start_date, i.end_date, i.owner_id, i.item, i.item_type, i.activity_type_code,
714 activity_id, marketing_medium_id, cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
715 values (ozf_activity_products_s.nextval,i.parent_id, i.parent_desc, i.parent_class,
716 i.parent_status, i.parent_type,i.confidential_flag, i.object_id, i.object_type,i.object_status,i.object_class,
718 i.activity_id, i.marketing_medium_id, i.cust_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
719 END LOOP;
720
721 /* Need to look into Campaign Status also */
722
723 ozf_utility_pvt.write_conc_log('-- Update the status in the ozf_activity_hl_denorm table from Campaign Schedules --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
724 update ozf_activity_hl_denorm h
725 set (h.schedule_status,h.schedule_update_date) =
726 (SELECT b.status_code,sysdate
727 FROM ams_campaign_schedules_b b
728 WHERE b.schedule_id = h.schedule_id
729 AND b.last_update_date > l_latest_comp_date
730 AND b.creation_date < l_latest_comp_date);
731
732 ozf_utility_pvt.write_conc_log('-- After the update, the status of some schedules may change --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
733 ozf_utility_pvt.write_conc_log('-- Delete Cancelled Schedules and its depedants --');
734
735 DELETE from ozf_activity_customers a where EXISTS
736 (select b.schedule_id
737 from ozf_activity_hl_denorm b
738 where b.schedule_status in ('CANCELLED','ARCHIVED','ONHOLD')
739 and b.schedule_id = a.object_id and a.object_class = 'CSCH');
740 DELETE from ozf_activity_products a where EXISTS
741 (select b.schedule_id
742 from ozf_activity_hl_denorm b
743 where b.schedule_status in ('CANCELLED','ARCHIVED','ONHOLD')
744 and b.schedule_id = a.object_id and a.object_class = 'CSCH');
745 DELETE from ozf_activity_hl_denorm where schedule_status in ('CANCELLED', 'ARCHIVED', 'ONHOLD');
746
747 /*If the list have been re-regenerated, mark them for to picked up as new schedules and also delete them
748 from ozf_activity_customers */
749
750 FOR i in c_changed_list_headers(l_latest_comp_date) LOOP
751
752 ozf_utility_pvt.write_conc_log('-- List changed for schedule : ' || i.schedule_id || '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
753
754 UPDATE ozf_activity_hl_denorm
755 SET schedule_update_date = sysdate
756 WHERE schedule_id = i.schedule_id
757 AND list_header_id is not null;
758
759 DELETE from ozf_activity_customers where object_id = i.schedule_id and object_class = 'CSCH';
760 END LOOP;
761
762 /* Add new schedules to both the hl_denorm table */
763 FOR i in c_incr_campaigns LOOP
764 INSERT into ozf_activity_hl_denorm
765 (Activity_hl_id,Schedule_Id,Campaign_id,Campaign_status,Campaign_class,
766 Campaign_type,Campaign_name,confidential_flag,Item,Item_type,List_header_id,
767 Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login,schedule_update_date)
768 SELECT ozf_activity_hl_denorm_s.nextval, s.schedule_id, i.campaign_id, i.status_code,
769 i.rollup_type, i.campaign_type, i.campaign_name,i.private_flag,
770 decode(ap.inventory_item_id, null, ap.category_id,ap.inventory_item_id) item,
771 ap.level_type_code item_type, al.list_header_id,sysdate,null,sysdate,null,null,sysdate
772 FROM
773 ams_campaign_schedules_b s,
774 ams_act_products ap,
775 ams_act_lists al
776 WHERE
777 s.campaign_id = i.campaign_id AND
778 s.creation_date > l_latest_comp_date and
779 ap.act_product_used_by_id(+) = s.schedule_id AND
780 ap.arc_act_product_used_by(+) = 'CSCH' and
781 al.list_act_type(+) = 'TARGET' AND al.list_used_by(+) = 'CSCH' AND
782 al.list_used_by_id(+) = s.schedule_id;
783 END LOOP;
784
785 FOR i in c_new_schedules_cust(l_latest_comp_date) LOOP
786
787 ozf_utility_pvt.write_conc_log('-- Denorm parties for new or changed schedule : ' || i.schedule_id|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss') );
788
789 FOR j in c_activity_incr_person( i.schedule_id, i.party_id,i.item_type) LOOP
790 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
791 parent_status, parent_type, confidential_flag,object_id,object_class,object_desc,object_status,source_code,
792 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,
793 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,prod_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
794 values(ozf_activity_customers_s.nextval,j.campaign_id, j.campaign_name, j.rollup_type, j.parent_status,
795 j.campaign_type,j.private_flag, j.schedule_id, j.object_class, j.schedule_name, j.status_code,j.source_code,
796 j.start_date_time, j.end_date_time, j.owner_user_id, j.activity_type_code,
797 j.activity_id, j.marketing_medium_id, j.party_id, j.cust_account_id,
798 j.cust_acct_site_id, j.site_use_id, j.site_use_code, j.prod_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
799 END LOOP;
800
801 FOR j in c_activity_incr_relationship( i.schedule_id, i.party_id,i.item_type) LOOP
802 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
803 parent_status, parent_type,confidential_flag, object_id,object_class,object_desc,object_status,source_code,
804 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,
805 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,prod_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
806 values(ozf_activity_customers_s.nextval,j.campaign_id, j.campaign_name, j.rollup_type, j.parent_status,
810 j.cust_acct_site_id, j.site_use_id, j.site_use_code, j.prod_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
807 j.campaign_type,j.private_flag, j.schedule_id, j.object_class, j.schedule_name, j.status_code,j.source_code,
808 j.start_date_time, j.end_date_time, j.owner_user_id, j.activity_type_code,
809 j.activity_id, j.marketing_medium_id, j.party_id, j.cust_account_id,
811 END LOOP;
812
813 FOR j in c_activity_incr_organization( i.schedule_id, i.party_id,i.item_type) LOOP
814 INSERT into ozf_activity_customers (activity_customer_id,parent_id, parent_desc, parent_class,
815 parent_status, parent_type,confidential_flag, object_id,object_class,object_desc,object_status,source_code,
816 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,
817 party_id, cust_account_id,cust_acct_site_id,site_use_id,site_use_code,prod_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
818 values(ozf_activity_customers_s.nextval,j.campaign_id, j.campaign_name, j.rollup_type, j.parent_status,
819 j.campaign_type,j.private_flag, j.schedule_id, j.object_class, j.schedule_name, j.status_code,j.source_code,
820 j.start_date_time, j.end_date_time, j.owner_user_id, j.activity_type_code,
821 j.activity_id, j.marketing_medium_id, j.party_id, j.cust_account_id,
822 j.cust_acct_site_id, j.site_use_id, j.site_use_code, j.prod_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
823 END LOOP;
824
825 END LOOP;
826
827
828 FOR i in c_new_schedules_prod(l_latest_comp_date) LOOP
829
830 ozf_utility_pvt.write_conc_log('-- Denorm products for new or changed schedule : ' || i.schedule_id|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss') );
831
832 FOR j in c_activity_incr_prod_new( i.schedule_id,i.list_header_id) LOOP
833 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
834 parent_status, parent_type,confidential_flag, object_id,object_class, object_desc,object_status,source_code,
835 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,
836 item,item_type,cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
837 values(ozf_activity_products_s.nextval,j.campaign_id, j.campaign_name, j.rollup_type, j.parent_status,
838 j.campaign_type,j.private_flag, j.schedule_id, j.object_class, j.schedule_name, j.status_code,j.source_code,
839 j.start_date_time, j.end_date_time, j.owner_user_id, j.activity_type_code,
840 j.activity_id, j.marketing_medium_id, j.item, j.item_type,
841 j.cust_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
842 END LOOP;
843 FOR j in c_activity_incr_cat_new(i.schedule_id, l_org_id,i.list_header_id) LOOP
844 INSERT into ozf_activity_products (activity_product_id,parent_id, parent_desc, parent_class,
845 parent_status, parent_type,confidential_flag, object_id,object_class, object_desc,object_status,source_code,
846 start_date, end_date, owner_id, activity_type_code,activity_id, marketing_medium_id,
847 item,item_type,cust_indicator,Last_Update_date,Last_updated_by,Creation_date,Created_by,last_update_login)
848 values(ozf_activity_products_s.nextval,j.campaign_id, j.campaign_name, j.rollup_type, j.parent_status,
849 j.campaign_type,j.private_flag, j.schedule_id, j.object_class, j.schedule_name, j.status_code,j.source_code,
850 j.start_date_time, j.end_date_time, j.owner_user_id, j.activity_type_code,
851 j.activity_id, j.marketing_medium_id, j.item, j.item_type,
852 j.cust_indicator,sysdate,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id);
853 END LOOP;
854 END LOOP;
855
856 ozf_utility_pvt.write_conc_log('-- Incremental Load End --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));
857
858 End If;
859
860 EXCEPTION
861 WHEN FND_API.g_exc_unexpected_error THEN
862 ozf_utility_pvt.write_conc_log('-- Error: --'||SQLERRM);
863 x_return_status := FND_API.g_ret_sts_unexp_error ;
864 ERRBUF := SQLERRM || ' ' || l_msg_data;
865 RETCODE := '2';
866
867 WHEN OTHERS THEN
868 --ROLLBACK TO refresh_denorm;
869 ozf_utility_pvt.write_conc_log('-- Error: --'||SQLERRM);
870 x_return_status := FND_API.g_ret_sts_unexp_error ;
871 ERRBUF := SQLERRM || ' ' || l_stmt_denorm;
872 RETCODE := '2';
873
874 END refresh_schedules;
875
876
877
878 END OZF_SCHEDULE_DENORM_PVT;