--------------------------------------------------------
--------********* TIENDA ********-----------------------
--------------------------------------------------------

DECLARE
VDBLINK VARCHAR2(30);
VTABLA VARCHAR2(30);
VTABLA2 VARCHAR2(30);
VSQL VARCHAR2(5000);

--SELECCIONA LOS CAMPOS QUE SON LLAVE PRIMARIA DE LA TABLA
CURSOR CPRIMARIAS IS
SELECT DISTINCT COLUMN_ID, COLUMN_NAME || ' ' || 
    CASE DATA_TYPE WHEN 'VARCHAR2' THEN
        ' ' || DATA_TYPE || '(' || DATA_LENGTH || '),'
    ELSE
        DATA_TYPE || ','
    END  DATO, COLUMN_NAME, '* ' || COLUMN_NAME || ' + ' || '' || COLUMN_NAME COLPRIM
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME = VTABLA AND COLUMN_NAME IN
    ('N_ID')--SE INDICAN LAS LLAVES PRIMARIAS******
ORDER BY COLUMN_ID ASC;

--SELECCIONA LOS CAMPOS DE LA TABLA
CURSOR CCAMPOS IS
SELECT DISTINCT COLUMN_ID, COLUMN_NAME || ' ' || 
    CASE DATA_TYPE WHEN 'VARCHAR2' THEN
        ' ' || DATA_TYPE || '(' || DATA_LENGTH || '),'
    ELSE
        DATA_TYPE || ','
    END  DATO, COLUMN_NAME
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = VTABLA
ORDER BY COLUMN_ID ASC;

--SELECCIONA LOS CAMPOS DE LA TABLA, SIN INCLUIIR LOS CAMPOS QUE SON LAS LLAVES PRIMARIAS
CURSOR CCOLS IS
SELECT DISTINCT COLUMN_ID, COLUMN_NAME
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME = VTABLA AND COLUMN_NAME NOT IN
(SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = VTABLA)
ORDER BY COLUMN_ID ASC;

VPRIMARIAS VARCHAR2(1000);--CONTENDRA LOS CAMPOS Y SUS TIPOS, QUE SON LLAVE PRIMARIA
VPRIMARIAS2 VARCHAR2(1000);--CONTENDRA LOS CAMPOS QUE SON LLAVE PRIMARIA
VPRIMARIAS3 VARCHAR2(1000);--CONTENDRA LOS CAMPOS QUE SON LLAVE PRIMARIA
VPRIMARIAS4 VARCHAR2(1000);--CONTENDRA LOS CAMPOS QUE SON LLAVE PRIMARIA
VPRIMARIAS5 VARCHAR2(1000);--CONTENDRA LOS CAMPOS QUE SON LLAVE PRIMARIA
VCOLUMNAS VARCHAR2(3000);--CONTENDRA LOS CAMPOS DE LA TABLA
VCOLUMNAS2 VARCHAR2(3000);--CONTENDRA LOS CAMPOS DE LA TABLA
VCOLUMNAS3 VARCHAR2(3000);--CONTENDRA LOS CAMPOS DE LA TABLA
VCOLPRIMS VARCHAR2(3000);--CONTENDRA LOS CAMPOS QUE SON LLAVE PRIMARIA DE LA TABLA 
VREPLICS NUMBER;--SECUENCIA REPLICA
BEGIN
    --INDICAR LA TABLA
    VTABLA := 'ZUN_ZUNIFY_TRANSACTION';
    --INDICAR LA TABLA SIN EL PREFIJO WMS, EKO O SIMILAR
    VTABLA2 := 'ZUNIFY_TRANSACTION';
    
    VPRIMARIAS := ''; VPRIMARIAS2 := ''; VPRIMARIAS2 := ''; VCOLPRIMS := '';
            
    --OBTENER DBLINK
    SELECT SUBSTR(USUARIO_BDD, LENGTH('EKONO_')+1, (LENGTH(USUARIO_BDD)-LENGTH('EKONO_'))) INTO VDBLINK FROM 
        WMS_BODEGAS WHERE BODEGA = CG_F_PARAMETER_VALUE('BREE');

    --TIENDA--
   INSERT INTO CG_REFRESH_TIMES
       (DBLINK, TABLE_NAME, LAST_REFRESH)
     VALUES
       ('LOCAL', VTABLA, TO_DATE('01/01/1990', 'MM/DD/YYYY'));
    COMMIT;
    
    --CENTRAL--
    INSERT INTO CG_REFRESH_TIMES@CENTRAL
       (DBLINK, TABLE_NAME, LAST_REFRESH)
     VALUES
       (VDBLINK, VTABLA, SYSDATE-1);
    COMMIT;

    --******************TABLA LOG
    FOR VPRIMARIA IN CPRIMARIAS LOOP
        VPRIMARIAS := VPRIMARIAS || ' ' || VPRIMARIA.DATO;
        VPRIMARIAS2 := VPRIMARIAS2 || '' || VPRIMARIA.COLUMN_NAME || ',';
        VPRIMARIAS3 := VPRIMARIAS3 || 'A.' || VPRIMARIA.COLUMN_NAME || '(+) = B.' || VPRIMARIA.COLUMN_NAME || ' AND ';
        VPRIMARIAS4 := VPRIMARIAS4 || '' || VPRIMARIA.COLUMN_NAME || ' = A.' || VPRIMARIA.COLUMN_NAME || ' AND ';
        VPRIMARIAS5 := VPRIMARIAS5 || '' || VPRIMARIA.COLPRIM || ' - ';
    END LOOP;
    
    --CREA LA TABLA
    VSQL := 'CREATE TABLE LOG_' || VTABLA2 || ' (' || VPRIMARIAS ||
              'TIPO      VARCHAR2(1),
              FECHA     DATE,
              ESQUEMA   VARCHAR2(30),
              PROCESAR  VARCHAR2(1)
            )';  
    EXECUTE IMMEDIATE VSQL;  
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);
    
    VSQL := 'ALTER TABLE LOG_' || VTABLA2 || ' ADD (CONSTRAINT LOG_' || VTABLA2 || '_PK PRIMARY KEY (' || VPRIMARIAS2 || ' TIPO))';    
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);   
    EXECUTE IMMEDIATE VSQL;    
    
    VCOLUMNAS := '';
    --******************TABLA DMP
    FOR VCAMPO IN CCAMPOS LOOP
        VCOLUMNAS := VCOLUMNAS || VCAMPO.DATO;
        VCOLUMNAS2 := VCOLUMNAS2 || VCAMPO.COLUMN_NAME || ',';
    END LOOP;
    
    VSQL := 'CREATE TABLE DMP_' || VTABLA2 || '
    (
      ' || VCOLUMNAS || ' 
      LOG_TIPO   VARCHAR2(1),
      LOG_FECHA  DATE
    )';
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);   
    EXECUTE IMMEDIATE VSQL; 
    
    VSQL := 'CREATE INDEX DM' || VTABLA2 || '_IN01 ON DMP_' || VTABLA2 || ' (' || SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1) || ')';
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);   
    EXECUTE IMMEDIATE VSQL; 
    
    --****************TABLA RCH
    VSQL := 'CREATE GLOBAL TEMPORARY TABLE RCH_' || VTABLA2 || '
    (
      ' || VCOLUMNAS || '  
      LOG_TIPO   VARCHAR2(1),
      LOG_FECHA  DATE
    )
    ON COMMIT DELETE ROWS
    NOCACHE';
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);   
    EXECUTE IMMEDIATE VSQL; 

    VSQL := 'CREATE INDEX RC' || VTABLA2 || '_IN01 ON RCH_' || VTABLA2 || ' (' || SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1) || ')';
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);   
    EXECUTE IMMEDIATE VSQL; 
    
    --********************VISTA RCH
    FOR VCOL IN CCOLS LOOP
        VCOLUMNAS3 := VCOLUMNAS3 || VCOL.COLUMN_NAME || ',';
    END LOOP;
    
    VSQL := 'CREATE OR REPLACE FORCE VIEW RCH_V_' || VTABLA2 || ' AS
            SELECT B.' || REPLACE(SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1), ',', ', B.') || ', A.' || 
            REPLACE(VCOLUMNAS3, ',', ', A.') || 
            ', B.TIPO LOG_TIPO, B.FECHA LOG_FECHA, B.ESQUEMA LOG_ESQUEMA, C.DBLINK CRT_DBLINK, B.PROCESAR
        FROM ' || VTABLA || ' A, LOG_' || VTABLA2 || ' B, CG_REFRESH_TIMES C WHERE ' || VPRIMARIAS3 || ' AND C.TABLE_NAME = ' 
        || ''''|| VTABLA || '''' || ' AND C.LAST_REFRESH <= B.FECHA';
    VSQL := REPLACE(VSQL, ' AND  AND ', ' AND ');
    VSQL := REPLACE(VSQL, ', A., ', ', ');
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);    
    EXECUTE IMMEDIATE VSQL;  
    
    --*************** TRIGGER
    VSQL := 'CREATE OR REPLACE TRIGGER "' || VTABLA2 || '_TR01" BEFORE INSERT OR UPDATE OR DELETE ON ' || VTABLA || ' FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        DELETE LOG_' || VTABLA2 || ' WHERE  ';
        
    VCOLPRIMS := REPLACE(REPLACE(VPRIMARIAS5, ' - * ', ' AND '), ' + ', ' = :NEW.');
    VCOLPRIMS := SUBSTR(VCOLPRIMS, 3, LENGTH(VCOLPRIMS) - 5);
    VSQL := VSQL || VCOLPRIMS ||';
        INSERT INTO LOG_' || VTABLA2 || ' (' || VPRIMARIAS2 || ' TIPO, FECHA) ';
    VSQL := VSQL || ' VALUES (:NEW.' || REPLACE(SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1), ',', ' , :NEW.') || ', ''I'', SYSDATE);
      ELSIF UPDATING THEN ';
      
    VCOLPRIMS := REPLACE(REPLACE(VPRIMARIAS5, ' - * ', ' OR :OLD.'), ' + ', ' <> :NEW.');
    VCOLPRIMS := SUBSTR(VCOLPRIMS, 3, LENGTH(VCOLPRIMS) - 5);
    
    VSQL := VSQL || ' IF (:OLD.' || VCOLPRIMS || ') THEN
          RAISE_APPLICATION_ERROR(-20000,''Esta tabla no permite actualizaciones a la llave primaria cuando esta habilitada la replicacion'');
        END IF;
        BEGIN
        INSERT INTO LOG_' || VTABLA2 || ' (' || VPRIMARIAS2 || ' TIPO, FECHA)
        VALUES (:NEW.' || REPLACE(SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1), ',', ' , :NEW.') || ', ''U'', SYSDATE);
        EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
          UPDATE LOG_' || VTABLA2 || '
          SET    FECHA = SYSDATE, ESQUEMA = NULL ';
          
    VCOLPRIMS := REPLACE(REPLACE(VPRIMARIAS5, ' - * ', ' AND '), ' + ', ' = :NEW.');
    VCOLPRIMS := SUBSTR(VCOLPRIMS, 3, LENGTH(VCOLPRIMS) - 5);
    
    VSQL := VSQL || ' WHERE ' || VCOLPRIMS || '  AND TIPO = ''U''; 
    END;
      ELSIF DELETING THEN
        DELETE LOG_' || VTABLA2 || '
        WHERE ' || VCOLPRIMS || ';
        INSERT INTO LOG_' || VTABLA2 || ' (' || VPRIMARIAS2 || ' TIPO, FECHA)
        VALUES (:OLD.' || REPLACE(SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1), ',', ' , :OLD.') || ', ''D'', SYSDATE);
      END IF;
    END;';
    SYS.DBMS_OUTPUT.PUT_LINE(VSQL);    
    EXECUTE IMMEDIATE VSQL; 
    
    --**************CG_REPLICATION_TABLES
    SELECT (MAX(REPLICATION_SEQUENCE) + 1) INTO VREPLICS FROM CG_REPLICATION_TABLES;
    -- CP replica de la central a la tienda, y viceversa
    -- CE replica de la tienda a la central 
    -- PR replica de la tienda a la copia en la central
    --CAMBIAR CP, si no se tienen que replicar los datos entre la tienda y la central, y viceversa    
    INSERT INTO CG_REPLICATION_TABLES
        (TABLE_NAME, PRIMARY_KEY, TRIGGER_NAME, TEMPORARY_TABLE, VIEW_NAME, 
        LOG_TABLE, REPLICATION_TYPE, REPLICATION_SEQUENCE, REPLICATION_SOURCE, ACTIVE, 
        EXECUTE_INSERTS, EXECUTE_UPDATES, EXECUTE_DELETES, UPDATE_CONDITION, COLUMN_LIST, 
        LOG_STATISTICS, DMP_TABLE, PK_CONSTRAINT, NAL_COLUMN)
    VALUES
        (VTABLA, SUBSTR(VPRIMARIAS2, 0, LENGTH(VPRIMARIAS2) - 1), VTABLA2 || '_TR01', 'RCH_' || VTABLA2, 'RCH_V_' || VTABLA2, 
        'LOG_' || VTABLA2, 'N', VREPLICS, 'PR', 'X', 
        'X', 'X', 'X', SUBSTR(VPRIMARIAS4, 0, LENGTH(VPRIMARIAS4)-4), SUBSTR(VCOLUMNAS2, 0, LENGTH(VCOLUMNAS2)-1), 
        'X', 'DMP_' || VTABLA2, 'ZUN_' || VTABLA2 || '_PK', NULL);
    COMMIT;    
END;