Many Thanks for the help. It worked. Below is the final query;
SELECT CASE
WHEN T2.REFERENCECATEGORY=0 THEN 'Sales'
WHEN T2.REFERENCECATEGORY=3 THEN 'Purchase'
WHEN T2.REFERENCECATEGORY=4 THEN 'Transaction'
WHEN T2.REFERENCECATEGORY=5 THEN 'Stock Adjustment'
WHEN T2.REFERENCECATEGORY=6 THEN 'Transfer'
WHEN T2.REFERENCECATEGORY=20 THEN 'Fixed Asset' ELSE 'Others' END AS TransactionType,
CASE WHEN T1.QTY < 0 THEN 'OUT' ELSE 'IN' END AS StockMovementType,
ISNULL(T52.NAME, N'') AS Department, ISNULL(T62.NAME,N'') AS RequestedBy, T1.VOUCHERPHYSICAL, T1.DATEPHYSICAL, T1.ITEMID, T32.NAME, T1.QTY, T1.COSTAMOUNTPHYSICAL,
CASE WHEN ISNULL(T1.PACKINGSLIPID,N'')='' THEN T1.INVOICEID ELSE T1.PACKINGSLIPID END AS SupplierRef
FROM INVENTTRANS AS T1
INNER JOIN INVENTTRANSORIGIN AS T2 ON T1.INVENTTRANSORIGIN = T2.RECID
LEFT JOIN ECORESPRODUCT AS T3 ON T3.DISPLAYPRODUCTNUMBER = T1.ITEMID
LEFT JOIN ECORESPRODUCTTRANSLATION AS T32 ON T32.PRODUCT = T3.RECID AND T32.LANGUAGEID = 'en-us'
LEFT JOIN INVENTTRANSPOSTING AS T4 ON T4.INVENTTRANSORIGIN=T1.INVENTTRANSORIGIN AND T4.VOUCHER = T1.VOUCHERPHYSICAL AND T4.ISPOSTED=1
LEFT JOIN DEFAULTDIMENSIONVIEW AS T5 ON T5.DEFAULTDIMENSION = T4.DEFAULTDIMENSION AND T5.NAME = 'Department'
LEFT JOIN DIMATTRIBUTEOMDEPARTMENT AS T52 ON T52.VALUE = T5.DISPLAYVALUE
LEFT JOIN DEFAULTDIMENSIONVIEW AS T6 ON T6.DEFAULTDIMENSION = T4.DEFAULTDIMENSION AND T6.NAME = 'Worker'
LEFT JOIN DIMATTRIBUTEHCMWORKER AS T62 ON T62.VALUE = T6.DISPLAYVALUE
WHERE T1.DATAAREAID IN(@DataAreaId)
