 select  concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM SITE WHERE REFCODE = ( ''', substring(p.refcode,2,1),'Z03',''' ))',' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query'  from posdocpaymentMBS p left join posdocheaderMBS H on p.inventorydocId = h.inventorydocid  left join site s on s.sitecode = h.sitecode  where paymentTypecode = 'SHOPEE' and concat(substring(p.refcode,1,1), substring(p.refcode,2,1)) = 'SF'   and p.inventorydocid  in (select h.inventorydocid from posdocheaderMBS where docdate >= '09/01/2020')  and s.refcode <> 'FZ03' AND H.docdate >= '09/01/2020' union all select concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM SITE WHERE REFCODE =  ( ''', substring(p.refcode,2,1), 'Z01', '''))', ' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query' from posdocpaymentMBS p left join posdocheaderMBS H on p.inventorydocId = h.inventorydocid inner join site s on s.sitecode = h.sitecode where paymentTypecode = '308'  and p.inventorydocid  in (select inventorydocid from
 posdocheaderMBS where docdate >= '09/01/2020' ) and  concat(substring(p.refcode,2,1),'Z01') <>  s.refcode and concat(substring(p.refcode,2,1),'Z08') <>  s.refcode AND H.docdate >= '09/01/2020'  union all select concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM SITE WHERE REFCODE =  ( ''', substring(p.refcode,2,1), 'Z02', '''))', ' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query' from posdocpaymentMBS p left join posdocheaderMBS H on p.inventorydocId = h.inventorydocid inner join site s on s.sitecode = h.sitecode where paymentTypecode = '309' and p.inventorydocId in (select inventorydocid from posdocheaderMBS where docdate >= '09/01/2020' ) and  concat(substring(p.refcode,2,1),'Z02') <>  s.refcode AND H.docdate >= '09/01/2020'  union all select  concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM SITE WHERE REFCODE = ( ''', substring(p.refcode,2,1),'Z06',''' ))',' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query' from posdocpaymentMBS p left join posdocheaderMBS H on p.inve
ntorydocId = h.inventorydocid  left join site s on s.sitecode = h.sitecode where paymentTypecode = 'SHOPEE' and concat(substring(p.refcode,1,1), substring(p.refcode,2,1)) = 'SP'   and p.inventorydocid in (select h.inventorydocid from posdocheaderMBS where docdate >= '09/01/2020' ) and s.refcode <> 'PZ06' AND H.docdate >= '09/01/2020' union all select  concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM SITE WHERE REFCODE = ( ''', substring(p.refcode,2,1),'Z03',''' ))',' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query' from posdocpaymentMBS p left join posdocheaderMBS H on p.inventorydocId = h.inventorydocid  left join site s on s.sitecode = h.sitecode where paymentTypecode = 'SHOPEE' and concat(substring(p.refcode,1,1), substring(p.refcode,2,1)) = 'SX'   and p.inventorydocid in (select h.inventorydocid from posdocheaderMBS where docdate >= '09/01/2020' ) and s.refcode <> 'XZ03' AND H.docdate >= '09/01/2020' union all select  concat('UPDATE POSDOCHEADERMBS SET SITECODE = (SELECT SITECODE FROM 
SITE WHERE REFCODE = ( ''', substring(p.refcode,2,1),'Z04',''' ))',' WHERE INVENTORYDOCID = ',p.inventorydocid) 'query' from posdocpaymentMBS p left join posdocheaderMBS H on p.inventorydocId = h.inventorydocid  left join site s on s.sitecode = h.sitecode where paymentTypecode = 'SHOPEE' and concat(substring(p.refcode,1,1), substring(p.refcode,2,1)) = 'SR'   and p.inventorydocid in (select h.inventorydocid from posdocheaderMBS where docdate >= '09/01/2020' ) and s.refcode <> 'RZ04' AND H.docdate >= '09/01/2020' union all Select 'UPDATE POSDOCHEADERMBS SET SITECODE = ' + (select sitecode from  site where refcode in(select substring(partyremarks,charindex(CONVERT(varchar,YEAR(GETDATE())) +'|',partyremarks) + 5,4))) + ' WHERE INVENTORYDOCID = ' + convert(varchar,inventorydocid) 'query'  from posdocheaderMBS where sitecode <>  (select sitecode from site where refcode in   (select substring(partyremarks,charindex(CONVERT(varchar,YEAR(GETDATE())) +'|',partyremarks) + 5,4)))  AND docdate >= '09/01/2020' AND TRANSCODE
 = 'REC'