AX production Production Tables Relations

My question is more related to the database level. I am working on production module; more specifically, PRODTABLE (contains production order header), PRODJOURNALPROD (contains reports as finished production journal lines) and PRODJOURNALPROD (contains all transactions created when posting posting a line from route sheet). I am struggling to find a relationship between these tables, as I need to formulate a query using these three tables. I tried to inner join using PRODID, but that leads to a cartesian join between JOURNAL and ROUTE table. Any suggestions?

Following querry which I am trying for my Monthly Production Report.

SELECT

PT.PRODID,

PT.ITEMID,

PT.[NAME] AS ITEMNAME,

SUM(PT.QTYSCHED) AS QTYSCHED,

CONVERT(VARCHAR(12),PJ.TransDate,103) AS FINISHDATE,

SUM(PJ.QTYGOOD) AS GOODQTY,

SUM(PJ.QTYERROR) AS REJECTEDQTY,

WT.PRODUNITID,

SUM(PRT.HOURS) AS HOURS,

SUM(PR.CalcSetUp+PR.CalcProc) AS STDTIME,

CASE WHEN PJ.ERRORCAUSE=‘0’ THEN

‘NONE’

ELSE

CASE WHEN PJ.ERRORCAUSE=‘1’ THEN

‘Material’

ELSE

CASE WHEN PJ.ERRORCAUSE=‘2’ THEN

‘Machine’

ELSE

CASE WHEN PJ.ERRORCAUSE=‘3’ THEN

‘OperatingStaff’

END

END

END

END AS ERRORCAUSE

FROM PRODTABLE AS PT

INNER JOIN PRODJOURNALPROD AS PJ ON PT.PRODID=PJ.PRODID AND PT.DATAAREAID=PJ.DATAAREAID

INNER JOIN ProdRouteTrans AS PRT ON PT.PRODID=PRT.PRODID AND PT.DATAAREAID=PRT.DATAAREAID

INNER JOIN WrkCtrTable AS WT ON PRT.WRKCTRID=WT.WRKCTRID AND PRT.DATAAREAID=WT.DATAAREAID

INNER JOIN PRODROUTE AS PR ON PT.PRODID=PR.PRODID AND PT.DATAAREAID=PR.DATAAREAID

AND PR.OPRID=PRT.OPRID

WHERE PRT.DateWIP=@DATE AND PT.DATAAREAID=@COMPANY AND CONVERT(VARCHAR(12),PJ.TransDate,101)=@DATE and WT.PRODUNITID=@DEPARTMENT AND PRT.TRANSTYPE=‘0’ AND PRT.CategoryId LIKE’%Proc’

–WHERE PT.DATAAREAID=‘EPL’ AND CONVERT(VARCHAR(12),PJ.TransDate,103)=‘30/06/2011’

–AND PRT.CategoryId LIKE’%Proc’ OR PRT.CategoryId=’%Setup’ AND PRT.TRANSTYPE=‘0’

AND PRT.CategoryId LIKE’%Proc’AND PRT.TRANSTYPE=‘0’

GROUP BY

PT.PRODID,

PT.ITEMID,

PT.[NAME],

PJ.TransDate,

WT.PRODUNITID,

PJ.ERRORCAUSE