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