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

Is there a report to relate a General Ledger Journal Entry to a Manufacturing Order in Dynamics GP? - SQL

This information can be found by clicking on Inquiry > Manufacturing > Manufacturing Orders > Order Activity. This is a great screen, which a user can employ to drill back to the Journal Entry that was created by the transaction that is listed on this window. Once there, highlight the transaction and click the blue arrow on top tool bar of scrolling window to identify the Journal Entry. 

That being said, while this approach can get you from a particular Manufacturing Order to the related Journal Entry (Entries), it does not support rapid analysis of high transaction volumes. 

The following query links Inventory transactions in the Manufacturing Order Activity table MOP10213 to the Journal Entry information contained in the HITB table SEE30303 - obviously HITB must be deployed for this to work. Additionally, only detail transactions posted after the initialization of the HITB tool will be available for linking.

SELECT 

A.MANUFACTUREORDER_I MO_Number,

CASE A.MO_ACTIVITY_REASON_I

      WHEN 34 THEN 'Issue'

      WHEN 38 THEN 'Raw Material Relief'

      WHEN 39 THEN 'Finished Good Post'

      ELSE convert(Varchar(2),a.MO_ACTIVITY_REASON_I,1)

END AS Reason_Code,

CASE A.DOCTYPE

      WHEN 1 THEN 'IV Transaction'

      ELSE 'Not An Inventory Transaction'

END AS Document_Type,

B.DOCNUMBR Document_Number,

convert(varchar(10),B.DOCDATE,101) Document_Date,

B.ITEMNMBR Item_Number,

B.QTYBSUOM*B.TRXQTY AS Quantity,

C.JRNENTRY,

C.GLPOSTDT,

C.TRXREFERENCE

FROM [MOP10213] AS A

LEFT JOIN IV30300 AS B

ON A.IVDOCNBR = B.DOCNUMBR

Left Join SEE30303 as C

on B.DOCNUMBR = C.DOCNUMBR

WHERE A.DOCTYPE = 1

ORDER BY A.MANUFACTUREORDER_I, A.IVDOCNBR