/*
  Sol: 11939 Patrick T. Sá 07/10/2015. Importação do layout Bauhaus.
*/
UPDATE OR INSERT INTO VEF_BASE_TTIPOISS (BDCODTIPOISS, BDDESTIPOISS, BDFORMULARIO, BDCODINTTPISS) VALUES (22, 'Bauhaus', NULL, 1) MATCHING (BDCODTIPOISS);
COMMIT WORK;

--Normal
UPDATE OR INSERT INTO VEF_BASE_TTABTPNF (BDCODTPNF, BDDESTPNF, BDCODTPNFEXP, BDTIPONF, BDCODTIPOISS)
VALUES ((SELECT FIRST 1 COALESCE((SELECT bdcodtpnf FROM(SELECT t.bdcodtpnf FROM VEF_BASE_TTABTPNF t WHERE t.bddestpnf = 'Normal' AND t.bdcodtpnfexp ='N' AND t.bdtiponf = 1 AND t.bdcodtipoiss = 22)), (SELECT MAX(nf.bdcodtpnf)+1 AS BDCODTPNF FROM VEF_BASE_TTABTPNF nf)) AS BDCODTPNF FROM VEF_BASE_TTABTPNF), 'Normal', 'N', 1, 22) MATCHING (BDCODTPNF);

--Retida
UPDATE OR INSERT INTO VEF_BASE_TTABTPNF (BDCODTPNF, BDDESTPNF, BDCODTPNFEXP, BDCODTIPOISS)
VALUES ((SELECT FIRST 1 COALESCE((SELECT bdcodtpnf FROM(SELECT t.bdcodtpnf FROM VEF_BASE_TTABTPNF t WHERE t.bddestpnf = 'Retida' AND t.bdcodtpnfexp ='R' AND t.bdcodtipoiss = 22)), (SELECT MAX(nf.bdcodtpnf)+1 AS BDCODTPNF FROM VEF_BASE_TTABTPNF nf)) AS BDCODTPNF FROM VEF_BASE_TTABTPNF), 'Retida', 'R', 22) MATCHING (BDCODTPNF);

--Substituto
UPDATE OR INSERT INTO VEF_BASE_TTABTPNF (BDCODTPNF, BDDESTPNF, BDCODTPNFEXP, BDCODTIPOISS)
VALUES ((SELECT FIRST 1 COALESCE((SELECT bdcodtpnf FROM(SELECT t.bdcodtpnf FROM VEF_BASE_TTABTPNF t WHERE t.bddestpnf = 'Substituta' AND t.bdcodtpnfexp ='S' AND t.bdcodtipoiss = 22)), (SELECT MAX(nf.bdcodtpnf)+1 AS BDCODTPNF FROM VEF_BASE_TTABTPNF nf)) AS BDCODTPNF FROM VEF_BASE_TTABTPNF), 'Substituta', 'S', 22) MATCHING (BDCODTPNF);

--Calcelada
UPDATE OR INSERT INTO VEF_BASE_TTABTPNF (BDCODTPNF, BDDESTPNF, BDCODTPNFEXP, BDTIPONF, BDCODTIPOISS)
VALUES ((SELECT FIRST 1 COALESCE((SELECT bdcodtpnf FROM(SELECT t.bdcodtpnf FROM VEF_BASE_TTABTPNF t WHERE t.bddestpnf = 'Cancelada' AND t.bdcodtpnfexp ='C' AND t.bdtiponf = 2 AND t.bdcodtipoiss = 22)), (SELECT MAX(nf.bdcodtpnf)+1 AS BDCODTPNF FROM VEF_BASE_TTABTPNF nf)) AS BDCODTPNF FROM VEF_BASE_TTABTPNF), 'Cancelada', 'C', 2, 22) MATCHING (BDCODTPNF);

--Tomador
UPDATE OR INSERT INTO VEF_BASE_TTABTPNF (BDCODTPNF, BDDESTPNF, BDCODTPNFEXP, BDCODTIPOISS)
VALUES ((SELECT FIRST 1 COALESCE((SELECT bdcodtpnf FROM(SELECT t.bdcodtpnf FROM VEF_BASE_TTABTPNF t WHERE t.bddestpnf = 'Tomador' AND t.bdcodtpnfexp ='T' AND t.bdcodtipoiss = 22)), (SELECT MAX(nf.bdcodtpnf)+1 AS BDCODTPNF FROM VEF_BASE_TTABTPNF nf)) AS BDCODTPNF FROM VEF_BASE_TTABTPNF), 'Tomador',    'T', 22) MATCHING (BDCODTPNF);

COMMIT WORK;

SET TERM ^;

EXECUTE BLOCK
AS
  DECLARE VARIABLE wCodCid INTEGER;
BEGIN
  FOR SELECT bdcodcid from tcidade c where c.bdcodibge in(3104304, 4201406, 4201950, 4220000, 1501303, 2800670, 3111606, 4203956, 3113909, 3117900, 3120201, 4205456, 4206009, 4207007, 3130507, 4208302, 4208807, 3137809, 4209607, 1504422, 4210803, 3142601, 3303906, 1506138, 1506500, 3162005, 4217253, 4218707) INTO :wCodCid DO
    BEGIN
      UPDATE OR INSERT INTO VEF_BASE_TVINCULOISS(BDCODCID, BDREFVINCISS, BDCODTIPOISS, BDIMPVARARQ)VALUES(:wCodCid, 201201, 22, 'TRUE') MATCHING(BDCODCID);
    END
END^

SET TERM ; ^

COMMIT WORK;