eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_SCHEDULE_DENORM_PVT dependencies on OZF_ACTIVITY_HL_DENORM

Line 12: FROM ozf_activity_hl_denorm

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

Line 44: ozf_activity_hl_denorm am,

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

Line 95: FROM ozf_activity_hl_denorm am,

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,

Line 142: FROM ozf_activity_hl_denorm am,

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,

Line 176: ozf_activity_hl_denorm am,

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

Line 190: ozf_activity_hl_denorm am,

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,

Line 212: /* Drop all the indexes in ozf_activity_hl_denorm */

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

Line 214: /* Recreate all the indexes on ozf_activity_hl_denorm */

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,

Line 216: INSERT into ozf_activity_hl_denorm

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,

Line 220: SELECT ozf_activity_hl_denorm_s.nextval, s.schedule_id, i.campaign_id, i.status_code,

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

Line 354: FROM ozf_activity_hl_denorm a, ams_act_products b

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:

Line 361: FROM ozf_activity_hl_denorm

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,

Line 367: FROM ozf_activity_hl_denorm am, ams_list_entries le, hz_parties b

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;

Line 376: FROM ozf_activity_hl_denorm am

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

Line 382: FROM ozf_activity_hl_denorm a,ams_list_headers_all lh

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:

Line 418: FROM ozf_activity_products a,ozf_activity_hl_denorm b

414: SELECT distinct a.parent_id, a.parent_desc, a.parent_class,
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
418: FROM ozf_activity_products a,ozf_activity_hl_denorm b
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:

Line 431: ozf_activity_hl_denorm b,

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'

Line 615: -- EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_activity_hl_denorm';

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' ;

Line 624: delete from ozf_activity_hl_denorm;

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);

Line 657: DELETE from ozf_activity_hl_denorm where activity_hl_id = i.activity_hl_id;

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

Line 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'));

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,

Line 681: INSERT into ozf_activity_hl_denorm

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,

Line 685: SELECT ozf_activity_hl_denorm_s.nextval, Schedule_Id, Campaign_id, Campaign_status, Campaign_class, Campaign_type,

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'

Line 687: FROM ozf_activity_hl_denorm a, ams_act_products b

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:

Line 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'));

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

Line 724: update ozf_activity_hl_denorm h

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

Line 737: from ozf_activity_hl_denorm b

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

Line 742: 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:

Line 745: DELETE from ozf_activity_hl_denorm where schedule_status in ('CANCELLED', 'ARCHIVED', 'ONHOLD');

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:

Line 754: UPDATE ozf_activity_hl_denorm

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:

Line 764: INSERT into ozf_activity_hl_denorm

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,

Line 768: SELECT ozf_activity_hl_denorm_s.nextval, s.schedule_id, i.campaign_id, i.status_code,

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