ALTER TABLE ISSDMSNOTAFISCAL ADD VALORISSRECOLHER NUMERIC(14,2);
#
UPDATE ISSDMSNOTAFISCAL SET VALORISSRECOLHER = (
	select case 
		when (nfp.IDCADASTRO is not null) then
        	nf.VALORISS - nf.VALORISSRETIDO
        when (nft.IDCADASTRO is not null) then
        	case when ((nf.NATUREZAOPERACAO = 2) and (nf.ISSRETIDO = 'S')) then
            	nf.VALORISSRETIDO
            else
            	0
            end
		else
        	0
		end as VALORISSRECOLHER
	from ISSDMS i
	join ISSDMSNOTAFISCAL inf on inf.IDDMS = i.IDDMS
	join ISSNOTAFISCAL nf on nf.IDNOTAFISCAL = inf.IDNOTAFISCAL
	left join ISSNOTAFISCALPRESTADOR nfp on nfp.IDNOTAFISCAL = nf.IDNOTAFISCAL and nfp.IDCADASTRO = i.IDCADASTRO
	left join ISSNOTAFISCALTOMADOR nft on nft.IDNOTAFISCAL = nf.IDNOTAFISCAL and nft.IDCADASTRO = i.IDCADASTRO
	where i.iddms = ISSDMSNOTAFISCAL.IDDMS
    and nf.IDNOTAFISCAL = ISSDMSNOTAFISCAL.IDNOTAFISCAL
)
#
ALTER TABLE ISSDMSNOTAFISCAL ALTER COLUMN VALORISSRECOLHER NUMERIC(14,2) NOT NULL;
#