USE T202021

CREATE TABLE Tmp_Data (VouStatus Varchar(255), VouCustCode Varchar(255),VouSuppCode Varchar(255),FirmCode Varchar(255),SuppBillNo Varchar(255))

INSERT INTO Tmp_Data SELECT 'PENDING', VouCustCode, VouSuppCode, FirmCode,SuppBillNo FROM PURCHASE_DETAIL 
INSERT INTO Tmp_Data SELECT 'PENDING', VouCustCode, VouSuppCode, FirmCode,SuppBillNo FROM OPENING_PURCHASE_DETAIL 

UPDATE Tmp_Data SET VouStatus='PART' FROM (SELECT RECEIVED_DETAIL.RecCustCode AS F1, RECEIVED_DETAIL.RecSuppCode AS F2, RECEIVED_DETAIL.RecFirmCode AS F3, RECEIVED_ADDLESS_DETAIL.RecBillNo AS F4 FROM RECEIVED_ADDLESS_DETAIL INNER JOIN RECEIVED_DETAIL ON RECEIVED_ADDLESS_DETAIL.RecSrNo = RECEIVED_DETAIL.RecSrNo WHERE (RECEIVED_ADDLESS_DETAIL.Status = 'PART')) AS T1 WHERE T1.F1=VouCustCode AND T1.F2=VouSuppCode AND T1.F3=FirmCode AND T1.F4=SuppBillNo 
UPDATE Tmp_Data SET VouStatus='PART' FROM (SELECT RETURN_GOODS_DETAIL.RtnCustCode AS F1, RETURN_GOODS_DETAIL.RtnSuppCode AS F2, RETURN_GOODS_DETAIL.RtnFirmCode AS F3, RETURN_GOODS_ADDLESS_DETAIL.RtnBillNo AS F4 FROM RETURN_GOODS_ADDLESS_DETAIL INNER JOIN RETURN_GOODS_DETAIL ON RETURN_GOODS_ADDLESS_DETAIL.RtnSrNo = RETURN_GOODS_DETAIL.RtnSrNo WHERE (RETURN_GOODS_ADDLESS_DETAIL.Status = 'PART')) AS T1 WHERE T1.F1=VouCustCode AND T1.F2=VouSuppCode AND T1.F3=FirmCode AND T1.F4=SuppBillNo 

UPDATE Tmp_Data SET VouStatus='FULL' FROM (SELECT RECEIVED_DETAIL.RecCustCode AS F1, RECEIVED_DETAIL.RecSuppCode AS F2, RECEIVED_DETAIL.RecFirmCode AS F3, RECEIVED_ADDLESS_DETAIL.RecBillNo AS F4 FROM RECEIVED_ADDLESS_DETAIL INNER JOIN RECEIVED_DETAIL ON RECEIVED_ADDLESS_DETAIL.RecSrNo = RECEIVED_DETAIL.RecSrNo WHERE (RECEIVED_ADDLESS_DETAIL.Status = 'FULL')) AS T1 WHERE T1.F1=VouCustCode AND T1.F2=VouSuppCode AND T1.F3=FirmCode AND T1.F4=SuppBillNo 
UPDATE Tmp_Data SET VouStatus='FULL' FROM (SELECT RETURN_GOODS_DETAIL.RtnCustCode AS F1, RETURN_GOODS_DETAIL.RtnSuppCode AS F2, RETURN_GOODS_DETAIL.RtnFirmCode AS F3, RETURN_GOODS_ADDLESS_DETAIL.RtnBillNo AS F4 FROM RETURN_GOODS_ADDLESS_DETAIL INNER JOIN RETURN_GOODS_DETAIL ON RETURN_GOODS_ADDLESS_DETAIL.RtnSrNo = RETURN_GOODS_DETAIL.RtnSrNo WHERE (RETURN_GOODS_ADDLESS_DETAIL.Status = 'FULL')) AS T1 WHERE T1.F1=VouCustCode AND T1.F2=VouSuppCode AND T1.F3=FirmCode AND T1.F4=SuppBillNo 

UPDATE PURCHASE_DETAIL SET VouStatus=T1.F1 FROM (SELECT VouStatus AS F1, VouCustCode AS F2, VouSuppCode AS F3, FirmCode AS F4, SuppBillNo AS F5 FROM Tmp_Data) AS T1 WHERE T1.F2=VouCustCode AND T1.F3=VouSuppCode AND T1.F4=FirmCode AND T1.F5=SuppBillNo 
UPDATE OPENING_PURCHASE_DETAIL SET VouStatus=T1.F1 FROM (SELECT VouStatus AS F1, VouCustCode AS F2, VouSuppCode AS F3, FirmCode AS F4, SuppBillNo AS F5 FROM Tmp_Data) AS T1 WHERE T1.F2=VouCustCode AND T1.F3=VouSuppCode AND T1.F4=FirmCode AND T1.F5=SuppBillNo 

DROP TABLE Tmp_Data