Winshuttle Query
Winshuttle Query Using Winshuttle Query to get Real Time answers
Dave Kelly SAP Sr. Product Data Analyst, Dorman Products
1
Agenda Introduction The Challenge The Solution
Benefits, Lessons Learned & Future Plans Q&A
2
Dorman Products A leading automotive aftermarket supplier located in PA • High-volume data maintenance ✓ Over 640,000 unique stock keeping units (SKU’s) ✓ Creating 60,000 – 80,000 new SKU’s each year ✓ Five plants, high level of complexity
•
• •
500+ different Winshuttle scripts in all SAP disciplines (SD, MM, FI, LE, etc.) Widespread & robust use – taking full advantage of WS functionality SAP Go Live in October, 2014 3
Insight on Dave Kelly Dorman contributor for 30+ years • • • •
Colmar, Pennsylvania Most recently a Support Analyst in IT and then moved to the Master Data office Oversee the building of Winshuttle Transaction and Query scripts to support the business We started using Winshuttle for our data migration into SAP Enjoy golfing, wood working, being outdoors and driving my classic car
4
Why Winshuttle query? ✓ ✓ ✓ ✓ ✓
When real-time data is required Data not available from other sources - reporting environment Save time and money over ABAP coding Monitor data integrity from simple master data queries Published scripts empower end-users to run on demand
5
The Challenge: Price comparison Issue Customer price on the order doesn’t match the system price.
Problem Customer levies fines for price discrepancy.
Requirements Find customer orders with pricing that doesn’t match the system price. Make the necessary adjustments before the order is shipped and invoiced.
6
The Solution: Pricing Audit Query ✓ Join tables for extracting condition type: ✓ ✓ ✓
VBAK – Sales Document Header Details VBAP – Sales Document item Detail KONV – Conditions (Transaction Details)
7
Applying Selection Criteria ✓ Output will send it to excel ✓ Selection Type allows filters to be applied: Runtime or Fixed
8
Sold-to party filter Using an ‘In’ Operator from an ‘Input’ list.
9
The Solution: Pricing Audit Query Create an Excel template with an ‘input’ tab for listing the sold-to account numbers and Item Categories.
10
Mapping to Excel and add Excel functions
11
Excel Results tab • •
Results tab built from ZPCM and ZCCP outputs Use Excel formulas to highlight differences
12
Benefits of using Winshuttle Query • • • • •
Much faster to develop than ABAP coding. Modifications/tweaks made in minutes. Easier and less risky than using SQVI. Can be scheduled to run at specific times or reoccurring intervals. Embed Excel functions into the output.
13
Other value added Winshuttle queries ✓ Number range query to identify how close we are to exhausting a number range. (NRIV & TNROT) ✓ Quality Management open notifications for inspection lots. (QMEL, QMFE, QAMV, QAMR) ✓ Data Integrity reports for checking master data. (MARA, MARC & MVKE) ✓ Query current data to be modified and reloaded. (MARA & MARC) ✓ Inventory valuation reports. (MB5L)
14
Best Practices • • • • •
Build templates to support business processes. Publish the scripts to the template. Use logical descriptive names for easy identification. Build a library to store your templates. Catalog your templates and scripts.
15
Thank you Remember to complete your session evaluation!
16
Number Range Query ▪Scenario: a configurable number range uses the last available number in the sequence preventing the creation of new documents. ▪Requirement: ▪Audit number ranges to avoid emergency situations ▪Estimate the time remaining before a range is ‘used up’ ▪Automate monthly report by email
17
Number Range Query
Benefits:
▪ Scheduled output via email ▪ Utilizes Excel functionality
▪ ▪
Easy maintenance Macro-enhanced
18
Dave Kelly SAP Sr. Product Data Analyst, Dorman Products
1
Agenda Introduction The Challenge The Solution
Benefits, Lessons Learned & Future Plans Q&A
2
Dorman Products A leading automotive aftermarket supplier located in PA • High-volume data maintenance ✓ Over 640,000 unique stock keeping units (SKU’s) ✓ Creating 60,000 – 80,000 new SKU’s each year ✓ Five plants, high level of complexity
•
• •
500+ different Winshuttle scripts in all SAP disciplines (SD, MM, FI, LE, etc.) Widespread & robust use – taking full advantage of WS functionality SAP Go Live in October, 2014 3
Insight on Dave Kelly Dorman contributor for 30+ years • • • •
Colmar, Pennsylvania Most recently a Support Analyst in IT and then moved to the Master Data office Oversee the building of Winshuttle Transaction and Query scripts to support the business We started using Winshuttle for our data migration into SAP Enjoy golfing, wood working, being outdoors and driving my classic car
4
Why Winshuttle query? ✓ ✓ ✓ ✓ ✓
When real-time data is required Data not available from other sources - reporting environment Save time and money over ABAP coding Monitor data integrity from simple master data queries Published scripts empower end-users to run on demand
5
The Challenge: Price comparison Issue Customer price on the order doesn’t match the system price.
Problem Customer levies fines for price discrepancy.
Requirements Find customer orders with pricing that doesn’t match the system price. Make the necessary adjustments before the order is shipped and invoiced.
6
The Solution: Pricing Audit Query ✓ Join tables for extracting condition type: ✓ ✓ ✓
VBAK – Sales Document Header Details VBAP – Sales Document item Detail KONV – Conditions (Transaction Details)
7
Applying Selection Criteria ✓ Output will send it to excel ✓ Selection Type allows filters to be applied: Runtime or Fixed
8
Sold-to party filter Using an ‘In’ Operator from an ‘Input’ list.
9
The Solution: Pricing Audit Query Create an Excel template with an ‘input’ tab for listing the sold-to account numbers and Item Categories.
10
Mapping to Excel and add Excel functions
11
Excel Results tab • •
Results tab built from ZPCM and ZCCP outputs Use Excel formulas to highlight differences
12
Benefits of using Winshuttle Query • • • • •
Much faster to develop than ABAP coding. Modifications/tweaks made in minutes. Easier and less risky than using SQVI. Can be scheduled to run at specific times or reoccurring intervals. Embed Excel functions into the output.
13
Other value added Winshuttle queries ✓ Number range query to identify how close we are to exhausting a number range. (NRIV & TNROT) ✓ Quality Management open notifications for inspection lots. (QMEL, QMFE, QAMV, QAMR) ✓ Data Integrity reports for checking master data. (MARA, MARC & MVKE) ✓ Query current data to be modified and reloaded. (MARA & MARC) ✓ Inventory valuation reports. (MB5L)
14
Best Practices • • • • •
Build templates to support business processes. Publish the scripts to the template. Use logical descriptive names for easy identification. Build a library to store your templates. Catalog your templates and scripts.
15
Thank you Remember to complete your session evaluation!
16
Number Range Query ▪Scenario: a configurable number range uses the last available number in the sequence preventing the creation of new documents. ▪Requirement: ▪Audit number ranges to avoid emergency situations ▪Estimate the time remaining before a range is ‘used up’ ▪Automate monthly report by email
17
Number Range Query
Benefits:
▪ Scheduled output via email ▪ Utilizes Excel functionality
▪ ▪
Easy maintenance Macro-enhanced
18
