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_UTILITY_PVT

Line 209: ozf_utility_pvt.write_conc_log('-- Initial Load Start -- '|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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:

Line 210: ozf_utility_pvt.write_conc_log('-- Insert into HLD -- ');

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 */

Line 237: ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 1 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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,

Line 271: ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 2 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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,

Line 290: ozf_utility_pvt.write_conc_log('-- Insert into Activity Customers 3 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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,

Line 309: ozf_utility_pvt.write_conc_log('-- Insert into Activity Products 1 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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:
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,

Line 323: ozf_utility_pvt.write_conc_log('-- Insert into Activity Products 2 --'|| to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));

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,

Line 338: ozf_utility_pvt.write_conc_log('-- Initial Load End --');

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(

Line 597: ozf_Utility_PVT.debug_message(l_full_name || ': Start Schedule Denorm');

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

Line 598: ozf_utility_pvt.write_conc_log('-- pIncrement Flag is : ' || p_increment_flag );

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:

Line 599: ozf_utility_pvt.write_conc_log('-- lIncrement Flag is : ' || l_increment_flag );

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:

Line 600: ozf_utility_pvt.write_conc_log('-- l_latest_comp_date is: ' || l_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,

Line 601: ozf_utility_pvt.write_conc_log('-- l_org_id is : ' || l_org_id );

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,

Line 617: ozf_utility_pvt.write_conc_log('-- Full Load: Deleting from Temp Tables --');

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:

Line 622: ozf_utility_pvt.write_conc_log('-- Full Load: Deleting from Denorm 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' ;

Line 633: ozf_utility_pvt.write_conc_log('-- Incremental Load Start --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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;

Line 648: ozf_utility_pvt.write_conc_log('-- l_product_changed is : ' || l_product_changed || '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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:

Line 653: ozf_utility_pvt.write_conc_log('-- Deleting product from denorm : '|| i.item_type

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;

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 692: ozf_utility_pvt.write_conc_log('-- Adding the new product to the denorm table -- '|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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,

Line 706: ozf_utility_pvt.write_conc_log('-- Adding category products to the denorm table -- '|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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

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

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

Line 733: ozf_utility_pvt.write_conc_log('-- Delete Cancelled Schedules and its depedants --');

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

Line 752: ozf_utility_pvt.write_conc_log('-- List changed for schedule : ' || i.schedule_id || '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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

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

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,

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

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,

Line 856: ozf_utility_pvt.write_conc_log('-- Incremental Load End --'|| '-'||to_char(sysdate,'dd-mon-yyy-hh:mi:ss'));

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

Line 862: ozf_utility_pvt.write_conc_log('-- Error: --'||SQLERRM);

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:

Line 869: ozf_utility_pvt.write_conc_log('-- Error: --'||SQLERRM);

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: