SELECT a.materialgroup, a.mtrdoctype, a.mtrno, a.priority, e.codevaluedesc as prior, f.codevaluedesc as mtrtype, a.receiveddate, a.consigneecode as consignee, a.regdate, a.mtrapproveddate as appdate, a.parentdocno as parentdoc, 
g.codevaluedesc as mtrstat, b.itemcode, b.vocab, b.partno, 
Upper(c.description) as descript, b.doq, ISNULL(b.demandqty, 0) as demandqty, isnull(b.totalissueqty, 0) as totissued, b.ismtitemclosed as itemclosed, (isnull(stk.catastk, 0) + isnull(stk.catbstk, 0)) as serstk, isnull(stk.catdstk, 0) as repstk, isnull(stk.catcstk, 0) as catcstk, j.fieldname, 'Remarks' = Case When j.fieldvalue is null Then 'No Remarks' Else Ltrim(Rtrim(j.fieldvalue)) End, suppart.editpartno, 'Storecode' = Case When stk.Storecode Is Null Then 'NTC' Else Upper(stk.Storecode) End, 'Aircraftno' = Case When a.aircraftno Is Null Then 'N/A' Else Upper(a.aircraftno) End 
FROM Tbmtmtr a (NOLOCK) 
INNER JOIN Tbmtmtritems b (NOLOCK) ON b.MTRNo = a.MTRNo 
INNER JOIN tbGLItem_M c (NOLOCK) ON b.ItemCode = c.ItemCode 
iNNER JOIN tbglcodevalues e (NOLOCK) ON a.priority = e.Codevalue 
INNER JOIN tbglcodevalues f (NOLOCK) ON a.requesttype = f.Codevalue 
INNER JOIN tbglcodevalues g (NOLOCK) ON a.mtrstatus = g.Codevalue 
inner join tbglSupplierParts_m suppart (NOLOCK) 
on b.itemcode = suppart.itemcode 
left join tbmtmtritemsmodifyhistory j (NOLOCK) 
on b.mtrno = j.mtrno and b.itemcode = j.itemcode 
and j.amendmentno = (select max(amendmentno) from 
tbmtmtritemsmodifyhistory k (NOLOCK) where k.mtrno = b.mtrno and 
k.itemcode = b.itemcode AND J.FIELDNAME = 'Consignor Remarks') 
left join 
(SELECT A.ITEMCODE, a.storecode, (SELECT ISNULL(SUM(QTY), 0) FROM tBSPSTORESTOCKSTATUS (NOLOCK) 
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4401) AS CATASTK, 
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK) 
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4402) AS CATBSTK, 
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK) 
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4403) AS CATCSTK, 
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK) 
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4404) AS CATDSTK, 
SUM(QTY) AS TOTALSTK 
FROM TBSPSTORESTOCKSTATUS A (nolock) 
WHERE upper(a.storecode) = ' SITE4 ' 
GROUP BY a.itemcode, a.storecode) Stk 
on b.itemcode = stk.Itemcode 
where a.priority in (2903) AND 
g.codevaluedesc = 'Incoming' and b.ismtitemclosed != 'Y' and a.consignorcode = '26ED' and 
suppart.Isdefaultpartno = 'Y' and 
Case 
When a.receiveddate is null 
Then Convert(Varchar(12), a.senioritydate, 105) 
Else Convert(Varchar(12), a.receiveddate, 105) 
End>= '01-01-1999'
and 
Case 
When a.receiveddate is null
Then Convert(Varchar(12), a.senioritydate, 105)
Else Convert(Varchar(12), a.receiveddate, 105) 
End<= '01-01-2009'
 and 
(Upper(stk.Storecode) = 'SITE4' or stk.Storecode is null) 
order by b.vocab, suppart.editpartno, 
Case When a.receiveddate is null Then a.Senioritydate Else a.Receiveddate End