drop table if exists _vatsalejob;

create table _vatsalejob
select docno,datedoc,arcard.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0000000000.00 as vattotal
from arcard inner join armst on arcard.ACCID = armst.ACCID
where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
vatamt <> 0 
and arcard.vatperiod = :m11;

insert into _vatsalejob
select docno,datedoc,downrec.accid,invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
from downrec inner join armst on downrec.ACCID = armst.ACCID
where vatamt <> 0
and downrec.vatperiod = :m11;

insert into _vatsalejob
select docno,datedoc,"",invamt,vatamt,000000000.00 as invamt2,000000000.00 as vatamt2,
XTAXNO,TAXSITETYPE,TAXSITE,mCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,0,0
from vatgl 
where vatamt <> 0 and vatxtype = "S"
and vatgl.vatperiod = :m11;

insert into _vatsalejob
select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
from arcard inner join armst on arcard.ACCID = armst.ACCID
where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
vatamt = 0 and left(arcard.TAXTYPE,2)="03" and arcard.vat = 7 and void = 0 
and arcard.vatperiod = :m11;

insert into _vatsalejob
select docno,datedoc,arcard.accid,000000000.00 as invamt,000000000.00 as vatamt,invamt as invamt2,vatamt as vatamt2,
armst.TAXNO, armst.TAXSITETYPE, armst.TAXSITE, armst.XCOMP,SPACE(5) AS XSITE1, SPACE(5) AS XSITE2,VOID,0
from arcard inner join armst on arcard.ACCID = armst.ACCID
where (left(trtype,2) = 'AR' or left(trtype,2) = 'DR') and left(docno,2) <> "AI" and 
vatamt = 0 and arcard.vat = 7 and void = 1 
and arcard.vatperiod = :m11;

update _vatsalejob set xsite1 = 'X' where left(taxsitetype,2) = '01';
update _vatsalejob set xsite2 = taxsite where left(taxsitetype,2) <>  '01';
update _vatsalejob set invamt = invamt * -1, vatamt = vatamt * -1 ,invamt2 =  invamt2 * -1, vatamt2 = vatamt2 * -1 where 
( left(docno,2)='RI' OR left(docno,2)='DR');
update  _vatsalejob set xcomp = '** เอกสารยกเลิก **', invamt = 0, vatamt =0, invamt2 = 0, vatamt2 =-0 where
void = 1;
update _vatsalejob set vattotal = invamt+vatamt+invamt2+vatamt2;

select  docno as "เลขที่เอกสาร",datedoc,accid,invamt,vatamt,invamt2,vatamt2,taxno,taxsitetype,taxsite,
xcomp,xsite1,xsite2,void,vattotal
 from _vatsalejob order by datedoc, docno
