
/*------ ATD 423907 ------*/
delete from CONTROLESFILTRO where IDPROGRAMA=0
/*------ ATD 425549 ------*/
DECLARE v_count NUMBER:=0;

BEGIN

SELECT Count(1) INTO v_count FROM USER_TABLES WHERE UPPER(table_name) = 'ITEM_PEDIDO_ECOMMERCE';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'CREATE TABLE ITEM_PEDIDO_ECOMMERCE(
		ID INT NOT NULL,
		NUMPED INT NOT NULL,
		NUMPEDECOMMERCE VARCHAR2(100) NOT NULL,
		CODIGOFRANQUIA VARCHAR2(3) NOT NULL,
		CODIGOVARIACAO INT NOT NULL,
		CODIGOPRODUTO INT NOT NULL,
		CODPRO CHAR(5) NOT NULL,
		QUANTIDADE DECIMAL(15,3) NOT NULL,
		PRECOUNITARIO DECIMAL(15,5) NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE ITEM_PEDIDO_ECOMMERCE ADD CONSTRAINT PK_ITEM_PEDIDO_ECOMMERCE PRIMARY KEY (ID)';
EXECUTE IMMEDIATE 'CREATE ITEM_PEDIDO_ECOMMERCE_IDX1 ON ITEM_PEDIDO_ECOMMERCE(NUMPED)';
EXECUTE IMMEDIATE 'CREATE ITEM_PEDIDO_ECOMMERCE_IDX2 ON ITEM_PEDIDO_ECOMMERCE(NUMPEDECOMMERCE)';
EXECUTE IMMEDIATE 'CREATE SEQUENCE ITEM_PEDIDO_ECOMMERCE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ITEM_PEDIDO_ECOMMERCE_I
BEFORE INSERT ON ITEM_PEDIDO_ECOMMERCE FOR EACH ROW
WHEN (new.ID IS NULL)
BEGIN
SELECT ITEM_PEDIDO_ECOMMERCE_SEQ.NEXTVAL INTO :new.ID FROM dual;
END;';

END IF;

SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'ITEMCLICOMPLEMENTO' and UPPER(column_name) = 'IDPEDIDOORIGEM';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD IdPedidoOrigem VARCHAR2(100)';

END IF;

SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'ITEMCLICOMPLEMENTO' and UPPER(column_name) = 'CODIGOFRANQUIAECOMMERCE';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD CODIGOFRANQUIAECOMMERCE VARCHAR2(3)';

END IF;

SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'PEDIDO_ECOMMERCE' and UPPER(column_name) = 'GRUPO';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE PEDIDO_ECOMMERCE ADD GRUPO VARCHAR2(50)';

END IF;

SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'NOTA_FISCAL_ECOMMERCE' and UPPER(column_name) = 'NUMPEDECOMMERCE';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE NOTA_FISCAL_ECOMMERCE ADD NUMPEDECOMMERCE VARCHAR2(100)';

END IF;

SELECT Count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE UPPER(table_name) = 'NOTA_FISCAL_ECOMMERCE' and UPPER(column_name) = 'CODIGOFRANQUIA';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE NOTA_FISCAL_ECOMMERCE ADD CODIGOFRANQUIA VARCHAR2(3)';

END IF;

END;
/