
/*------ ATD 438892 ------*/
DECLARE v_count NUMBER:=0;

BEGIN

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

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'CREATE TABLE CONTROLECASHBACK(
		OidCliente INT NOT NULL,
		ValorInicial NUMBER(15,2) NOT NULL,
		Saldo NUMBER(15,2) NOT NULL,
		Validade Date NOT NULL,
		CNPJCPF Char(18) NOT NULL,
		Utilizado NUMBER(1) DEFAULT 0 NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE CONTROLECASHBACK ADD CONSTRAINT PK_CONTROLECASHBACK PRIMARY KEY (OidCliente)';
EXECUTE IMMEDIATE 'CREATE INDEX CONTROLECASHBACK_IDX1 ON CONTROLECASHBACK(CNPJCPF)';

END IF;

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

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'CREATE TABLE CONTROLECASHBACKDETALHE(
		ID INT NOT NULL,
		OidCliente INT NOT NULL,
		IdentificadorOperacao Int NOT NULL,
		Valor Decimal(15,2) NOT NULL,
		Observacao Varchar2(254) NOT NULL,
		CriadoEm Date NOT NULL,
		OidUsuario INT NOT NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE CONTROLECASHBACKDETALHE ADD CONSTRAINT PK_CONTROLECASHBACKDETALHE PRIMARY KEY (ID)';
EXECUTE IMMEDIATE 'CREATE INDEX CONTROLECASHBACKDETALHE_IDX1 ON CONTROLECASHBACKDETALHE(OidCliente)';
EXECUTE IMMEDIATE 'CREATE INDEX CONTROLECASHBACKDETALHE_IDX2 ON CONTROLECASHBACKDETALHE(IdentificadorOperacao)';
EXECUTE IMMEDIATE 'CREATE SEQUENCE CONTROLECASHBACKDETALHE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 NOCYCLE NOORDER' ;
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER CONTROLECASHBACKDETALHE_I
BEFORE INSERT ON CONTROLECASHBACKDETALHE FOR EACH ROW
WHEN (new.ID IS NULL)
BEGIN
SELECT CONTROLECASHBACKDETALHE_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) = 'VALORCASHBACKTOTAL';

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE ITEMCLICOMPLEMENTO ADD ValorCashbackTotal NUMBER(15,2) NULL ';

ENDIF

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

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE ITNFSAICOMPLEMENTO ADD ValorCashbackTotal NUMBER(15,2) NULL ';

ENDIF

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

IF v_count = 0 THEN

EXECUTE IMMEDIATE 'ALTER TABLE ITNFENTCOMPLEMENTO ADD ValorEstornoCashback NUMBER(15,2) NULL ';

ENDIF


END;
/