Monday, November 3, 2014

CASE to convert MOP10213.MO_Activity_Reason_I to usable text values - SQL

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Manufacturing Order Activity Reason Code field in Dynamics GP AKA (MO_Activity_Reason_I ), use the following case statement to return text versions of the default statuses in Dynamics GP - if the code is not correctly translated the code value will be returned.

CASE MOP10213.MO_ACTIVITY_REASON_I
WHEN 17 THEN 'Scheduled' --Odd but sometimes this value is used
WHEN 31 THEN 'Status Change'
WHEN 32 THEN 'Allocate'
WHEN 33 THEN 'Reverse Allocate'
WHEN 34 THEN 'Issue'
WHEN 35 THEN 'Reverse Issue'
WHEN 36 THEN 'Scrap'
WHEN 37 THEN 'Reverse Scrap'
WHEN 38 THEN 'Raw Material Relief'
WHEN 39 THEN 'Finished Good Post'
WHEN 40 THEN 'Reverse Finished Good Post'
WHEN 41 THEN 'Close'
WHEN 42 THEN 'Complete'
WHEN 43 THEN 'Post Variance From WIP'
WHEN 44 THEN 'Labor Data Collection'
WHEN 45 THEN 'Machine Data Collection'
WHEN 46 THEN 'Outsourced Costs'
WHEN 47 THEN 'Scheduled'
WHEN 48 THEN 'Picklist Built'
WHEN 49 THEN 'Change Working Routing'
WHEN 50 THEN 'Financial Activity'
ELSE convert(Varchar(2),MOP10213.MO_ACTIVITY_REASON_I,1)
END AS Reason_Code

No comments:

Post a Comment