
CREATE trigger ttbpedido_item_processado_b11u for tbpedido_item_processado
active before update position 10
AS
declare variable id integer;
declare variable qtd integer;
begin
  if(old.situacao <> new.situacao and new.situacao = 6)then
  begin

     for
        select  first 1
            t.id,
            coalesce((select count(ss.id) from VWREMESSA_TALAO_DETALHE ss where ss.remessa_id = t.remessa_id and ss.remessa_talao_id = t.remessa_talao_id and ss.status < 3),0) TALAO_PENDENTE
        from vwremessa_talao t
        where t.remessa_id = new.remessa
        and t.remessa_talao_id = new.REMESSA_ACUMULADO_CONTROLE
        and t.status < 3
     into
        :id,
        :qtd
     do begin
        if(:qtd = 1)then
        begin
            update vwremessa_talao s set s.status = 3 where s.id = :id;
        end
     end

  end
end;


ALTER TABLE TBUSUARIO
    ADD USUARIO_TIPO INTEGER DEFAULT 2;

COMMENT ON COLUMN TBUSUARIO.USUARIO_TIPO IS
'1 - desktop
2 - web
3 - webservice';


ALTER TABLE TBMODELO_LINHA
    ADD HABILITAR_ECOMMERCE INTEGER;

COMMENT ON COLUMN TBMODELO_LINHA.HABILITAR_ECOMMERCE IS
'1 - sim
0 - nao';

CREATE OR ALTER procedure spc_skus_ecommerce 
returns (
    produto_id integer,
    cor_id integer,
    modelo_id numeric(18,8),
    tamanho integer,
    tamanho_descricao varchar(10),
    sku varchar(20),
    ean varchar(50))
as
begin
  for
    SELECT
    
        p.codigo as ID,
        p.cor_codigo,
        p.modelo_codigo
    
    from tbproduto p, tbmodelo m, tbmodelo_linha l
    where p.salesforce = 0
    and m.codigo = p.modelo_codigo
    and l.codigo = m.linha_codigo
    and l.habilitar_ecommerce = 1
  into
    :PRODUTO_ID,
    :COR_ID,
    :MODELO_ID
  do begin
     for
        select TAMANHO, TAMANHO_DESCRICAO from SPC_MODELO_GRADE_TAMANHO(:MODELO_ID)
     into
        :TAMANHO,
        :TAMANHO_DESCRICAO
     do begin
        :SKU = :PRODUTO_ID||'-'||lpad(:TAMANHO,2,'0');
        :EAN = (select first 1 e.prefixo||e.codigo from TBEAN e where e.modelo_id = :MODELO_ID and e.cor_id = :COR_ID and e.tamanho = :TAMANHO);
       suspend;
     end
  end

end;

CREATE TABLE TBUPDATE_ECOMMERCE (
    ID BIGINT NOT NULL,
    TABELA INTEGER,
    TABELA_ID INTEGER);

ALTER TABLE TBUPDATE_ECOMMERCE
ADD CONSTRAINT PK_TBUPDATE_ECOMMERCE
PRIMARY KEY (ID);

CREATE SEQUENCE GTBUPDATE_ECOMMERCE;

create trigger ttbupdate_ecommerce_b01i for tbupdate_ecommerce
active before insert position 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gtbupdate_ecommerce,1);
end;

ALTER TABLE TBUPDATE_ECOMMERCE
    ALTER TABELA TYPE VARCHAR(50);

ALTER TABLE TBUPDATE_ECOMMERCE
    ADD SUB_ID INTEGER;

CREATE INDEX ITBUPDATE_ECOMMERCE_02
ON TBUPDATE_ECOMMERCE (TABELA,TABELA_ID,SUB_ID,TAMANHO);

CREATE INDEX ITBUPDATE_ECOMMERCE_03
ON TBUPDATE_ECOMMERCE (TABELA,TABELA_ID,SUB_ID);

CREATE INDEX ITBUPDATE_ECOMMERCE_01
ON TBUPDATE_ECOMMERCE (TABELA,TABELA_ID);

ALTER TABLE TBPERFIL
    ADD AMOSTRA INTEGER;

COMMENT ON COLUMN TBPERFIL.AMOSTRA IS
'1 - perfil de amostra
0 - nao';

ALTER TABLE TBPAGAMENTO_FORMA
    ADD HABILITAR_ECOMMERCE INTEGER;

COMMENT ON COLUMN TBPAGAMENTO_FORMA.HABILITAR_ECOMMERCE IS
'0 - nao
1 - sim';


