-- Lucas - Sol. 11562 - Atualização da base de IR total para os transportadores autônomos com base isenta.
UPDATE VRHF_BASE_CALC_TERC TERC SET BDBASEIRTOT =

( SELECT BDBASEIRTOT FROM (

SELECT BDCODEMP, BDCODTER, BDCODSERV, SUM(BDVALORPROVENTOS - BDVALORDESCONTOS) BDBASEIRTOT FROM (

SELECT CAV.BDCODEMP, CAV.BDCODTER, CAV.BDCODSERV, SUM(CAV.BDVALOR) BDVALORPROVENTOS, 0 BDVALORDESCONTOS
FROM VRHF_BASE_ITEMS_CALCTERC CAV
JOIN VRHF_BASE_CALC_TERC CTER ON CTER.BDCODEMP = CAV.BDCODEMP AND CTER.BDCODTER = CAV.BDCODTER AND CTER.BDCODSERV = CAV.BDCODSERV
JOIN VW_TVERBAS_REF VER ON VER.BDCODVER = CAV.BDCODVER AND VER.BDIRVER <> 0 AND BDTIPOCODVER = 0 AND VER.BDCLAVER NOT LIKE '9.010.0.%'
JOIN VRHF_BASE_TTERC_FGTSGPS FGTS ON FGTS.BDCODTER = CAV.BDCODTER AND FGTS.BDREFTERC = (SELECT MAX(BDREFTERC) FROM VRHF_BASE_TTERC_FGTSGPS WHERE BDCODTER = FGTS.BDCODTER AND BDREFTERC <= CTER.BDREFIRSERV) AND FGTS.BDCATEGORIATER = 15
GROUP BY CAV.BDCODEMP, CAV.BDCODTER, CAV.BDCODSERV

UNION ALL

SELECT CAV.BDCODEMP, CAV.BDCODTER, CAV.BDCODSERV, 0 BDVALORPROVENTOS, SUM(CAV.BDVALOR) BDVALORDESCONTOS
FROM VRHF_BASE_ITEMS_CALCTERC CAV
JOIN VRHF_BASE_CALC_TERC CTER ON CTER.BDCODEMP = CAV.BDCODEMP AND CTER.BDCODTER = CAV.BDCODTER AND CTER.BDCODSERV = CAV.BDCODSERV
JOIN VW_TVERBAS_REF VER ON VER.BDCODVER = CAV.BDCODVER AND VER.BDIRVER <> 0 AND BDTIPOCODVER = 1 AND VER.BDCLAVER NOT LIKE '9.010.0.%'
JOIN VRHF_BASE_TTERC_FGTSGPS FGTS ON FGTS.BDCODTER = CAV.BDCODTER AND FGTS.BDREFTERC = (SELECT MAX(BDREFTERC) FROM VRHF_BASE_TTERC_FGTSGPS WHERE BDCODTER = FGTS.BDCODTER) AND FGTS.BDCATEGORIATER = 15
GROUP BY CAV.BDCODEMP, CAV.BDCODTER, CAV.BDCODSERV

) GROUP BY BDCODEMP, BDCODTER, BDCODSERV

) X WHERE X.BDCODEMP = TERC.BDCODEMP AND X.BDCODTER = TERC.BDCODTER AND X.BDCODSERV = TERC.BDCODSERV

);

COMMIT WORK;
