CREATE OR REPLACE PACKAGE BODY INCARCARE_STRUCT IS

---****** Start -> Proceduri pentru uz in interiorul pachetului *******---
PROCEDURE DROP_TABLE(sTableName VARCHAR2, pCascade BOOLEAN DEFAULT FALSE) IS
  sDropDDL VARCHAR2(1000);
BEGIN
  sDropDDL := 'DROP TABLE '||sTableName;
  IF pCascade THEN
    sDropDDL := sDropDDL||' CASCADE CONSTRAINTS';
  END IF;
  sDropDDL := sDropDDL||' PURGE';

  EXECUTE IMMEDIATE sDropDDL ;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;

PROCEDURE Run_SQL (sSqlText CLOB) IS
  eroare_compilare exception;
  pragma exception_init( eroare_compilare,     -24344 ); --ORA-24344: Reusit cu eroare de compilare

  sSqlErr VARCHAR2(2000);
BEGIN
  EXECUTE IMMEDIATE sSqlText;
EXCEPTION
  WHEN eroare_compilare     THEN NULL; --ORA-24344: Reusit cu eroare de compilare // poate fi din cauza altor obiecte dependente care nu a fost create inca - nu e panica man (decat daca nu se pot compila la final)
  WHEN OTHERS THEN
    sSqlErr := SQLERRM;
    EXECUTE IMMEDIATE 'INSERT INTO WINMADMIN.DEBUG_STRUCT(SKEMA, SQL_TEXT, SQL_ERR, SQL_TIME) VALUES ( :sSkemaFirma, :sSqlText, :sSqlErr, :CURRENT_TIMESTAMP)' USING 'WINMADMIN', sSqlText, sSqlErr, CURRENT_TIMESTAMP;
    COMMIT;
END;

PROCEDURE PROCESS_Bulk_SQL ( sSelect CLOB) IS
  TYPE t_rec_ddl IS RECORD (DDLCreate CLOB);
  TYPE t_tab_ddl IS TABLE OF t_rec_ddl;
  bulk_ddl t_tab_ddl;
BEGIN
  EXECUTE IMMEDIATE sSelect BULK COLLECT INTO bulk_ddl;
  FOR i IN 1..bulk_ddl.Count LOOP
    Run_SQL(bulk_ddl(i).DDLCreate);
  END LOOP;
END;
---****** Stop -> Proceduri pentru uz in interiorul pachetului *******---

FUNCTION HEX_TO_BLOB (cHEX CLOB) RETURN BLOB IS
  b BLOB                := NULL;
  s VARCHAR2(4000 CHAR) := NULL;
  l NUMBER              := 4000;
BEGIN
  IF cHEX IS NOT NULL THEN
    dbms_lob.createtemporary(b, FALSE);
    FOR i IN 0 .. LENGTH(cHEX) / 4000 LOOP
      dbms_lob.read(cHEX, l, i * 4000 + 1, s);
      dbms_lob.append(b, to_blob(hextoraw(s)));
    END LOOP;
  END IF;
  RETURN b;
END;

PROCEDURE INCEPE_INCARCARE IS
  vCnt PLS_INTEGER;
BEGIN
  EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss']';

  SELECT Count(1) INTO vCnt FROM USER_TABLES WHERE TABLE_NAME = 'DEBUG_SLOAD';
  IF vCnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE DEBUG_SLOAD(TEXT CLOB, ERRTIME TIMESTAMP) TABLESPACE WMDATA';
  END IF;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DEBUG_SLOAD';

  SELECT Count(1) INTO vCnt FROM USER_TABLES WHERE TABLE_NAME = 'OBJECTS_DDL';
  IF vCnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE OBJECTS_DDL(TYPE VARCHAR2(30), NAME VARCHAR2(30), TEMP VARCHAR2(1), REFTABLE  VARCHAR2(30), HASHVAL VARCHAR2(64), TEXT CLOB, NIVEL NUMBER(5)) TABLESPACE WMDATA';
    EXECUTE IMMEDIATE 'ALTER TABLE OBJECTS_DDL ADD CONSTRAINT PK_OBJECTS_DDL PRIMARY KEY ( TYPE, NAME )';
  END IF;
  SELECT Count(1) INTO vCnt FROM USER_TABLES WHERE TABLE_NAME = 'OBJECTS_OLD';
  IF vCnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE OBJECTS_OLD(TYPE VARCHAR2(30), NAME VARCHAR2(30), TEMP VARCHAR2(1), REFTABLE  VARCHAR2(30), HASHVAL VARCHAR2(64)) TABLESPACE WMDATA';
    EXECUTE IMMEDIATE 'ALTER TABLE OBJECTS_OLD ADD CONSTRAINT PK_OBJECTS_OLD PRIMARY KEY ( TYPE, NAME )';
  END IF;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE OBJECTS_OLD';
  EXECUTE IMMEDIATE 'INSERT INTO OBJECTS_OLD SELECT TYPE, NAME, TEMP, REFTABLE, HASHVAL FROM OBJECTS_DDL';

  SELECT Count(1) INTO vCnt FROM USER_TABLES WHERE TABLE_NAME = 'DATE_OLDH';
  IF vCnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE DATE_OLDH(NAME VARCHAR2(30), HASHVAL VARCHAR2(64)) TABLESPACE WMDATA';
    EXECUTE IMMEDIATE 'ALTER TABLE DATE_OLDH ADD CONSTRAINT PK_DATE_OLDH PRIMARY KEY ( NAME )';
  END IF;

  SELECT Count(1) INTO vCnt FROM USER_TABLES WHERE TABLE_NAME = 'DATE_HASH';
  IF vCnt > 0 THEN
    SELECT Count(1) INTO vCnt FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DATE_HASH' AND COLUMN_NAME IN ('NAME', 'HASHVAL') ;
    IF vCnt = 2 THEN
      EXECUTE IMMEDIATE 'TRUNCATE TABLE DATE_OLDH';
      EXECUTE IMMEDIATE 'INSERT INTO DATE_OLDH SELECT NAME, HASHVAL FROM DATE_HASH';
    END IF;

    SELECT Count(1) INTO vCnt FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DATE_HASH' AND COLUMN_NAME IN ('NAME', 'HASHVAL', 'LINECOUNT') ;
    IF vCnt < 3 THEN
      DROP_TABLE('DATE_HASH');
      vCnt := 0;
    END IF;
  END IF;
  IF vCnt = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE DATE_HASH(NAME VARCHAR2(30), HASHVAL VARCHAR2(64), LINECOUNT NUMBER(20)) TABLESPACE WMDATA';
    EXECUTE IMMEDIATE 'ALTER TABLE DATE_HASH ADD CONSTRAINT PK_DATE_HASH PRIMARY KEY ( NAME )';
  END IF;
END;

PROCEDURE INCARCA_XML(pTableName VARCHAR2, cXML CLOB) IS
  dbVer VARCHAR2(300);

  PROCEDURE INCARCA_XML_MOD1 IS
    insCtx DBMS_XMLSTORE.ctxType;
    vCount NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE '||pTableName;
    insCtx := DBMS_XMLSTORE.newContext(pTableName); -- Get saved context
    DBMS_XMLSTORE.clearUpdateColumnList(insCtx);    -- Clear the update settings

    -- Set the columns to be updated as a list of values
    FOR vRec IN (
      SELECT column_name
      FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME = pTableName
      ORDER BY COLUMN_ID
    ) LOOP
      DBMS_XMLSTORE.setUpdateColumn(insCtx, vRec.column_name);
    END LOOP;
    -- Insert the doc.
    vCount := DBMS_XMLSTORE.insertXML(insCtx, cXML);
    -- Close the context
    DBMS_XMLSTORE.closeContext(insCtx);
    COMMIT;
  EXCEPTION WHEN OTHERS THEN
    EXECUTE IMMEDIATE 'INSERT INTO DEBUG_SLOAD SELECT :text, systimestamp FROM dual ' USING cXML;
  END;

  PROCEDURE INCARCA_XML_MOD2 IS
    varCols CLOB;
    varSels CLOB;
    varPath CLOB;
    varSQL  CLOB;
  BEGIN
    FOR vRec IN (
      SELECT
        column_id,
        column_name,
        data_type,
        column_name||' '||
        CASE
          WHEN data_type IN 'DATE' THEN 'VARCHAR2(19)'
          WHEN data_type IN 'BLOB' THEN 'CLOB'
          ELSE data_type
        END||
        CASE
          WHEN data_type IN ('NVARCHAR2','VARCHAR2','VARCHAR','CHAR') THEN '('||char_length||CASE WHEN char_used = 'C' THEN ' CHAR)' ELSE ')' END
          WHEN data_precision IS NOT NULL                             THEN '('||data_precision||','||data_scale||')'
        END||
        ' PATH '''||column_name||'''' xmlcol
      FROM USER_TAB_COLUMNS
      WHERE TABLE_NAME = pTableName
      ORDER BY COLUMN_ID
    ) LOOP
      varCols := varCols||CASE WHEN vRec.column_id > 1 THEN ',' END||'
              '||vRec.column_name;
      varSels := varSels||CASE WHEN vRec.column_id > 1 THEN ',' END||'
              '||CASE WHEN vRec.data_type = 'BLOB' THEN 'INCARCARE_STRUCT.HEX_TO_BLOB('||vRec.column_name||')' ELSE vRec.column_name END;
      varPath := varPath||CASE WHEN vRec.column_id > 1 THEN ',' END||'
              '||vRec.xmlcol;
    END LOOP;

    varSQL :=
      'DECLARE
        varXML XMLTYPE;
      BEGIN
        varXML:=:refXML;
        INSERT INTO '||pTableName||'('||varCols||')
        SELECT '||varSels||'
        FROM XMLTABLE (''/ROWSET/ROW'' PASSING varXML
            COLUMNS'||
              varPath||'
        );
        COMMIT;
      END;';

    EXECUTE IMMEDIATE 'TRUNCATE TABLE '||pTableName;
    EXECUTE IMMEDIATE varSQL USING xmltype(cXML);
  EXCEPTION WHEN OTHERS THEN
    EXECUTE IMMEDIATE 'INSERT INTO DEBUG_SLOAD SELECT :text, systimestamp FROM dual ' USING varSQL;
    EXECUTE IMMEDIATE 'INSERT INTO DEBUG_SLOAD SELECT :text, systimestamp FROM dual ' USING cXML;
  END;

BEGIN
  SELECT Max(version)
  INTO dbVer
  FROM PRODUCT_COMPONENT_VERSION
  WHERE Upper(SubStr(PRODUCT,1,15)) = 'ORACLE DATABASE' ;

  IF    SubStr(dbVer,1,8) IN ( '11.2.0.1', '11.2.0.2' ) AND pTableName = 'WMECFG'  THEN NULL;
  ELSIF SubStr(dbVer,1,8) IN ( '11.2.0.3' , '11.2.0.4' , '12.1.0.1' , '12.1.0.2' ) THEN INCARCA_XML_MOD2;
  ELSE                                                                                  INCARCA_XML_MOD1;
  END IF;
END;

PROCEDURE STERGE_TABELE IS
  TYPE t_rec_tab IS RECORD ( tabname VARCHAR2(30));
  TYPE t_tab_tab IS TABLE OF t_rec_tab;
  bulk_tab t_tab_tab;
BEGIN
  EXECUTE IMMEDIATE
  'SELECT T.NAME
  FROM OBJECTS_OLD T LEFT JOIN OBJECTS_DDL TN ON TN.TYPE = ''TABLE'' AND TN.NAME = T.NAME AND TN.HASHVAL = T.HASHVAL
  WHERE T.TYPE = ''TABLE'' AND T.TEMP = ''N'' AND TN.HASHVAL IS NULL'
  BULK COLLECT INTO bulk_tab;

  FOR i IN 1..bulk_tab.Count LOOP
    DROP_TABLE(bulk_tab(i).tabname, TRUE);
    EXECUTE IMMEDIATE 'DELETE DATE_OLDH WHERE NAME ='''||bulk_tab(i).tabname||'''';
    EXECUTE IMMEDIATE 'DELETE OBJECTS_OLD WHERE TYPE = ''TABLE'' AND NAME ='''||bulk_tab(i).tabname||'''';
  END LOOP;
  EXECUTE IMMEDIATE 'DELETE DATE_OLDH   D WHERE NOT EXISTS(SELECT 1 FROM USER_TABLES T WHERE T.TABLE_NAME = D.NAME)';
  EXECUTE IMMEDIATE 'DELETE OBJECTS_OLD O WHERE NOT EXISTS(SELECT 1 FROM USER_TABLES T WHERE T.TABLE_NAME = O.NAME) AND O.TYPE = ''TABLE'' AND O.TEMP = ''N''';
  COMMIT;
END;

PROCEDURE CREARE_TABELE IS
  TYPE t_rec_tab IS RECORD ( tabname VARCHAR2(30), tabddl CLOB);
  TYPE t_tab_tab IS TABLE OF t_rec_tab;
  bulk_tab t_tab_tab;
BEGIN
  EXECUTE IMMEDIATE 'DELETE OBJECTS_OLD T WHERE T.TYPE = ''TABLE'' AND T.TEMP = ''Y'' AND EXISTS (SELECT 1 FROM OBJECTS_DDL TN WHERE TN.TYPE = ''TABLE'' AND TN.TEMP = ''N'' AND TN.NAME = T.NAME)';
  COMMIT;

  EXECUTE IMMEDIATE
  'SELECT TN.NAME, TN.TEXT FROM OBJECTS_DDL TN WHERE TN.TYPE = ''TABLE'' AND TN.TEMP = ''N'' AND NOT EXISTS (SELECT 1 FROM OBJECTS_OLD T WHERE T.NAME = TN.NAME AND T.TYPE = TN.TYPE)'
  BULK COLLECT INTO bulk_tab;

  FOR i IN 1..bulk_tab.Count LOOP
    BEGIN
      EXECUTE IMMEDIATE bulk_tab(i).tabddl;
    EXCEPTION
      WHEN OTHERS THEN
        EXECUTE IMMEDIATE 'INSERT INTO DEBUG_SLOAD SELECT :text, systimestamp FROM dual ' USING bulk_tab(i).tabddl;
        COMMIT;
        raise_application_error(SQLCODE,SQLERRM);
    END;
  END LOOP;
END;

PROCEDURE GOLESTE_TABELE IS
  TYPE t_rec_tab IS RECORD ( tabname VARCHAR2(30), linecount NUMBER(20));
  TYPE t_tab_tab IS TABLE OF t_rec_tab;
  bulk_tab t_tab_tab;

  vLineCount NUMBER(20);
BEGIN
  -- verific cazuri unde nu bate numarul de inregistrari
  EXECUTE IMMEDIATE
  'SELECT TN.NAME, Coalesce(D.LINECOUNT,0)
  FROM OBJECTS_DDL TN
    INNER JOIN USER_TABLES T ON T.TABLE_NAME = TN.NAME
    LEFT  JOIN DATE_HASH D ON D.NAME = TN.NAME
  WHERE TN.TYPE = ''TABLE'' AND TN.TEMP = ''N'''
  BULK COLLECT INTO bulk_tab;

  FOR i IN 1..bulk_tab.Count LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||bulk_tab(i).tabname INTO vLineCount;
    IF vLineCount <> bulk_tab(i).linecount THEN
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||bulk_tab(i).tabname;
      EXECUTE IMMEDIATE 'DELETE DATE_OLDH WHERE NAME = '''||bulk_tab(i).tabname||'''';
    END IF;
  END LOOP;

  -- verific cazuri unde nu coincide hashul XML-ui de date
  EXECUTE IMMEDIATE
  'SELECT D.NAME, 0
  FROM DATE_OLDH D LEFT JOIN DATE_HASH DN ON DN.NAME = D.NAME AND DN.HASHVAL = D.HASHVAL
  WHERE DN.NAME IS NULL'
  BULK COLLECT INTO bulk_tab;

  FOR i IN 1..bulk_tab.Count LOOP
      EXECUTE IMMEDIATE 'TRUNCATE TABLE '||bulk_tab(i).tabname;
      EXECUTE IMMEDIATE 'DELETE DATE_OLDH WHERE NAME = '''||bulk_tab(i).tabname||'''';
  END LOOP;

  COMMIT;
END;

PROCEDURE INSTAL_WINMADMIN IS
BEGIN
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''SEQUENCE'' ORDER BY name');               -- SEQUENCE
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''TABLE'' AND temp = ''Y'' ORDER BY name'); -- TEMP
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''CONSTRAINT'' ORDER BY nivel, reftable');  -- CONSTRAINTS
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''INDEX'' ORDER BY name');                  -- INDEX
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''TYPE'' ORDER BY name');                   -- TYPE
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''TYPE BODY'' ORDER BY name');              -- TYPE BODY
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''PACKAGE'' ORDER BY name');                -- PACKAGE
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''PROCEDURE'' ORDER BY name');              -- PROCEDURE
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''FUNCTION'' ORDER BY name');               -- FUNCTION
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''PACKAGE BODY'' ORDER BY name');           -- PACKAGE BODY
  PROCESS_Bulk_SQL('SELECT TEXT FROM objects_ddl WHERE type = ''VIEW'' ORDER BY nivel, reftable');        -- VIEW

  BEGIN
    EXECUTE IMMEDIATE 'CREATE ROLE ROLE_WINMADMIN_OBJ';
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  --********************************* ACTUALIZARE ROLURI ******************************--
  FOR v_rec IN (
    SELECT 'GRANT DELETE,INSERT,SELECT,UPDATE ON '||table_name||' TO ROLE_WINMADMIN_OBJ' sGrant
    FROM all_tables
    WHERE owner = 'WINMADMIN'
    ORDER BY table_name
  ) LOOP
    Run_SQL(v_Rec.sGrant);
  END LOOP;

  FOR v_rec IN (
    SELECT 'GRANT SELECT ON '||sequence_name||' TO ROLE_WINMADMIN_OBJ' sGrant
    FROM all_sequences
    WHERE sequence_owner = 'WINMADMIN'
    ORDER BY sequence_name
  ) LOOP
    Run_SQL(v_Rec.sGrant);
  END LOOP;

  FOR v_rec IN (
    SELECT 'GRANT EXECUTE ON '||object_name||' TO ROLE_WINMADMIN_OBJ' sGrant
    FROM all_objects
    WHERE owner = 'WINMADMIN' AND object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION' )
    ORDER BY object_type, object_name
  ) LOOP
    Run_SQL(v_Rec.sGrant);
  END LOOP;

  FOR v_rec IN (
    SELECT 'GRANT SELECT ON '||view_name||' TO ROLE_WINMADMIN_OBJ' sGrant
    FROM all_views
    WHERE owner = 'WINMADMIN' AND NOT view_name IN ('V_CONST_GRAMADA', 'V_LISTA_TASKURI')  -- EXCEPTII
    ORDER BY view_name
  ) LOOP
    Run_SQL(v_Rec.sGrant);
  END LOOP;

  --********* HASH
  Run_SQL ('CREATE TABLE OBJECTS_HASH (TYPE VARCHAR2(30), NAME VARCHAR2(30), TEMP VARCHAR2(1), REFTABLE VARCHAR2(30), HASHVAL VARCHAR2(64)) TABLESPACE WMDATA');
  Run_SQL ('ALTER TABLE OBJECTS_HASH ADD CONSTRAINT PK_OBJECTS_HASH PRIMARY KEY (TYPE, NAME)');
  Run_SQL ('INSERT INTO objects_hash(TEMP, REFTABLE, HASHVAL, TYPE, NAME) SELECT TEMP, REFTABLE, HASHVAL, TYPE, NAME FROM objects_ddl');
  COMMIT;

  DROP_TABLE('DEBUG_SLOAD');
  DROP_TABLE('OBJECTS_DDL');
  DROP_TABLE('OBJECTS_OLD');
  DROP_TABLE('DATE_HASH');
  DROP_TABLE('DATE_OLDH');
END;

FUNCTION  DATE_INCARCATE_OK RETURN NUMBER IS
  TYPE t_rec_tab IS RECORD ( tabname VARCHAR2(30), linecount NUMBER(20));
  TYPE t_tab_tab IS TABLE OF t_rec_tab;
  bulk_tab t_tab_tab;

  vCnt    NUMBER(20);
  vResult NUMBER := 1;
BEGIN
  EXECUTE IMMEDIATE
  'SELECT O.NAME, Coalesce(D.LINECOUNT, 0)
  FROM OBJECTS_DDL O LEFT JOIN DATE_HASH D ON D.NAME = O.NAME
  WHERE O.TYPE = ''TABLE''
    AND O.TEMP = ''N''
    AND NOT O.NAME IN (''WMECFG'', ''SAFT_RESET_SEQ'') ' -- exceptii
  BULK COLLECT INTO bulk_tab;

  FOR i IN 1..bulk_tab.Count LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT Count(1) FROM '||bulk_tab(i).tabname INTO vCnt;
      IF vCnt <> bulk_tab(i).linecount THEN
        vResult := 0;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN vResult := 0;
    END;
    EXIT WHEN vResult = 0;
  END LOOP;

  IF vResult = 1 THEN
    EXECUTE IMMEDIATE 'SELECT Count(1) FROM DATE_HASH' INTO vCnt;
    IF vCnt = 0 THEN
      vResult := 0;
    END IF;
  END IF;

  RETURN vResult;
END;

PROCEDURE TERMINA_INCARCARE IS
  vCnt PLS_INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE OBJECTS_OLD';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DATE_OLDH';
END;

END INCARCARE_STRUCT;