Transcript No Slide Title

Where and Why Data Gets Stuck
&
What To Do About It
The World Is Our Office
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Agenda
-Where Data Gets Stuck
-Why Data Gets Stuck
-Opportunities
-Results of Data Getting Stuck
-Reactive to Pro-active
-Thresholds
-Examples
-Conclusion
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Where Data Gets Stuck
•Interface tables
•Import tables
•Workflow processes
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Why Data Gets Stuck
•Inaccurate data
•Concurrent processes not performing
•User intervention
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
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
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
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
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
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
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
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
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Examples
Check for Concurrent programs with normal completion and on
schedule:
Concurrent Program Name
Cost Mgr Child Process
Cost Manager Process
WF Notification Mailer
VV NIghtly Inventory Load for Web - Shell
VV WEB CUSTOMER NEW + CHANGES
VV NIghtly Item Mater Load for Web - Shell
VV Nightly Item Attribute Load for Web
VV WEB Order New + Changes
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Interval
2
HOURS
2
HOURS
1
DAYS
1
DAYS
1
DAYS
1
DAYS
1
DAYS
1
DAYS
Examples (continued)
Workflow Background Processes broken down by ITEM_ID:
Concurrent Program Name
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
WF Background Process FNDWFBG
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Item Type
VVEMAIL
OEOL
OEOH
POERROR
VVOECANC
GLBATCH
APEXP
OECHGORD
VVPORTV1
IEM_MAIL
CREATEPO
WFERROR
Interval
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
2
HOURS
Examples (continued)
Misc Interface and Workflow errors:
WSH Trip Stops Interface
RA Interface Lines
RCV Transactions Interface
VVAR Credit Store Holds
VVAR Credit Res
MTL Transactions Interface
Workflow Errors
1
1
1
1
1
1
1
Check for Concurrent Manager up and running
Inventory Manager
0
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
DAYS
DAYS
DAYS
DAYS
DAYS
DAYS
DAYS
MINUTES
Examples (continued)
#1) Material Transaction monitor
A) 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;
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
Examples (continued)
#2) Workflow errors
A) 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_me
ssage;
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.
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
Privileged and Confidential. No part of this may be reproduced or
transmitted without permission of Motion International.