The World Is Our Office

Where and Why Data Gets Stuck & What To Do About It The World Is Our Office

Agenda • Where Data Gets Stuck • Why Data Gets Stuck • Opportunities • Results of Data Getting Stuck • Reactive to Pro-active • Thresholds • Examples • Conclusion

Where Data Gets Stuck • Interface tables • Import tables • Workflow processes

Why Data Gets Stuck • Inaccurate data • Concurrent processes not performing • User intervention

Opportunities • In Oracle Financials and Manufacturing there are almost 100 interface tables • Concurrent processes are as many as you want • Just the velocity of data flowing through the systems

Results of Data Getting Stuck • In-accurate reporting • Increased negative customer facing issues • Negative impacts to suppliers • Lead time delays • Overall loss of confidence in systems and staff

Reactive to proactive • Tired of users knowing about a problem first • Tired of customers and suppliers seeing the problem first • Tired of always being in expedite mode • Tired of problems becoming huge before someone is aware there is a problem • Tired of working on immediate problems instead of value-added processes • Time to address the situation and turn it around

Thresholds • We started identifying the pain points • Determined whether we had data problems or processing problems • Identified the constraints and thresholds • How frequently should a concurrent process be performed • What is the acceptable number of rows in an interface table • Attacked the root cause • Deployed the solution

Examples Check for Concurrent programs with normal completion and on schedule: Concurrent Program Name Interval Cost Mgr Child Process 2 HOURS Cost Manager Process 2 HOURS WF Notification Mailer 1 DAYS VV NIghtly Inventory Load for Web - Shell 1 DAYS VV WEB CUSTOMER NEW + CHANGES 1 DAYS VV NIghtly Item Mater Load for Web - Shell 1 DAYS VV Nightly Item Attribute Load for Web 1 DAYS VV WEB Order New + Changes 1 DAYS

Examples (continued) Workflow Background Processes broken down by ITEM_ID: Concurrent Program Name Item Type Interval WF Background Process FNDWFBG VVEMAIL 2 HOURS WF Background Process FNDWFBG OEOL 2 HOURS WF Background Process FNDWFBG OEOH 2 HOURS WF Background Process FNDWFBG POERROR 2 HOURS WF Background Process FNDWFBG VVOECANC 2 HOURS WF Background Process FNDWFBG GLBATCH 2 HOURS WF Background Process FNDWFBG APEXP 2 HOURS WF Background Process FNDWFBG OECHGORD 2 HOURS WF Background Process FNDWFBG VVPORTV1 2 HOURS WF Background Process FNDWFBG IEM_MAIL 2 HOURS WF Background Process FNDWFBG CREATEPO 2 HOURS WF Background Process FNDWFBG WFERROR 2 HOURS

Examples (continued) Misc Interface and Workflow errors: WSH Trip Stops Interface 1 DAYS RA Interface Lines 1 DAYS RCV Transactions Interface 1 DAYS VVAR Credit Store Holds 1 DAYS VVAR Credit Res 1 DAYS MTL Transactions Interface 1 DAYS Workflow Errors 1 DAYS Check for Concurrent Manager up and running Inventory Manager 0 MINUTES

Examples (continued) #1) Material Transaction monitorA) Query to find errors in the material transaction interface table CURSOR v_interface_cur is SELECT count(*) Number_of_Records, trunc(CREATION_DATE) Creation_date, trunc(sysdate) - trunc(Creation_date) Days_Since_Creation, Error_Code FROM MTL_TRANSACTIONS_INTERFACE -- WHERE trunc(sysdate) - trunc(CREATION_DATE) > 1 GROUP BY trunc(CREATION_DATE), trunc(sysdate) - trunc(Creation_date), Error_Code ORDER BY trunc(CREATION_DATE) DESC;

Examples (continued) #2) Workflow errorsA) Query for workflow errors CURSOR v_wf_err_cur is select count(*) Number_of_Records,ias.item_type, pa.instance_label Label,ac.display_name Activity,ias.activity_result_code Result, ias.error_name ERROR_NAME, ias.error_message ERROR_MESSAGE from wf_item_activity_statuses ias, wf_process_activities pa, wf_activities_vl ac where ias.activity_status = 'ERROR' and ias.process_activity = pa.instance_id and pa.activity_name = ac.name and pa.activity_item_type = ac.item_type and ac.display_name != 'VV_GET_ORDER_INFO' and ac.display_name != 'VV_CUST_NOTIFY' and ac.display_name != 'Send Order Conf.' and ac.display_name != 'Ship Confirmation' and ac.display_name != 'Return Received' and ac.display_name != 'CC Refund' and ac.display_name != 'Order Cancelled' and ac.display_name != 'Line Cancelled' and ias.item_type != 'VVEMAIL' group by ias.item_type,pa.instance_label,ac.display_name,ias.activity_result_code,ias.error_name,ias.error_message;

Conclusion Keeping up with the flow of data throughout the Oracle Applications is a demanding job Analysis of areas for opportunity is a requirement A pro-active approach is required Results can be very rewarding to both the internal staff, customer, suppliers and the user community

The World Is Our Office