/*
* Автор: Галкин А.И.
* Дата: 2014-10-02
* Назначение: Исправление проблемы с исчезновением списка документов
* Изменяются объекты:
		update package PKG_MAIN
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2014-10-02.FixListDisappearance.sql';
	purpose       varchar2(4000) := 'Исправление проблемы с исчезновением списка документов';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	/*
	****** Begin update script ******
	*/
		-- Транзакционный апдейт
		-- execute immediate '';
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_MAIN" as

  function CheckOperation return number is
    Result number;
  begin

    Result := CheckIsMainAllowed;
    if Result <> 0 then
      Return(Result);
    end if;

    Return(Result);
  end;

  function CheckIsMainAllowed return number
  -- Допустимость выполнения активных операций
   is
    Result        number;
    IsMainAllowed number;
  begin
    Result := 0;

    select TO_NUMBER(trim(p.paramvalue))
    into IsMainAllowed
    from PASSPORT_KEY_PARAMETERS p
    where p.paramname = 'IsMainAllowed';

    if IsMainAllowed <> 1 then
      Result := -1000;
    end if;

    return(Result);

  exception
    when NO_DATA_FOUND then
      Return(Result);
  end;

  procedure GetError(pErrorCode        in number,
                     pErrorName        out nvarchar2,
                     pErrorDescription out nvarchar2,
                     pstatus           out number) is
    vlang         varchar2(10 char);
    sessionnumber number default 0;
    vDefaultError varchar2(4000 char);
    session_ex exception;
    vErrorParse          varchar2(4000 char);
    pDefaultException_kz varchar2(4000 char) := 'Error is not translated: ';

  begin

    begin
      select SID
      into sessionnumber
      from v$session
      where audsid = userenv('SESSIONID');
      select userlanguage
      into vlang
      from sessions
      where sessioncode = sessionnumber;
    exception

      when others then
        select e.errorname,
               e.errordescription,
               e.status
        into vDefaultError,
             pErrorDescription,
             pstatus
        from Errors e
        where e.errorcode = pErrorCode;

        vDefaultError := 'Неправильно установлены парамеры сессии для определения языка исключений: ' || vDefaultError;
        raise session_ex;

    end;
    --  vlang:='Russian';
    case vlang
      when 'Russian' then
        select e.errorname,
               e.errordescription,
               e.status
        into pErrorName,
             pErrorDescription,
             pstatus
        from Errors e
        where e.errorcode = pErrorCode;

        if pErrorCode = -3333 then
          select errorsaving
          into verrorparse
          from sessions
          where sessioncode = sessionnumber;
          pErrorName        := 'Ошибочные данные в сообщении';
          pErrorDescription := verrorparse;
          pstatus           := 1;
          --  sessionnumber:=pkg_mess_cfm_2.ClearErrorReportIntoSessions;
        end if;

      when 'Kazakh' then
        select e.errorname_kz,
               e.errordescription_kz,
               e.status
        into pErrorName,
             pErrorDescription,
             pstatus
        from Errors e
        where e.errorcode = pErrorCode;

        if (pErrorDescription is null) and (pErrorName is null) then
          select e.errorname,
                 e.errordescription,
                 e.status
          into pErrorName,
               pErrorDescription,
               pstatus
          from Errors e
          where e.errorcode = pErrorCode;

          pErrorDescription := pDefaultException_kz || pErrorDescription;
        end if;
        if pErrorCode = -3333 then
          select errorsaving
          into verrorparse
          from sessions
          where sessioncode = sessionnumber;
          pErrorName        := 'Ошибочные данные в сообщении';
          pErrorDescription := verrorparse;
          pstatus           := 1;
          --  sessionnumber:=pkg_mess_cfm_2.ClearErrorReportIntoSessions;
        end if;
      when 'English' then
        select e.errorname_en,
               e.errordescription_en,
               e.status
        into pErrorName,
             pErrorDescription,
             pstatus
        from Errors e
        where e.errorcode = pErrorCode;
    end case;

  exception
    when NO_DATA_FOUND then
      case vlang
        when 'Russian' then
          pErrorName        := 'Ошибка не определена в БД(Errors)';
          pErrorDescription := 'Ошибка не определена в БД(Errors)';
        when 'Kazakh' then
          pErrorName        := 'Ошибка не определена в БД(Errors)';
          pErrorDescription := 'Ошибка не определена в БД(Errors)';
      end case;
      pstatus := 0;
    WHEN session_ex then
      pErrorName        := vDefaultError;
      pErrorDescription := vDefaultError;
      pstatus           := 0;

  end;
  function GetSimpleError(pnumber number) return nvarchar2 is
    pError nvarchar2(4000 char);
    vlang  varchar2(10 char) default 'Russian';
  begin
    vlang := GetUserLangSettings;
    select decode(vlang, 'Kazakh', errorname_kz, errorname)
    into pError
    from errors
    where errorcode = pnumber;
    return pError;
  exception
    when NO_DATA_FOUND then
      pError := 'Ошибка не определена в БД(Errors)';
      return pError;
  end;

  function GetAnalyticShortNames(pAnalyticType in number,
                                 pResCursor    out sys_refcursor,
                                 pLanguageCode in number default 1) return number is
    result number default 0;
    vcount number default 0;
    vlang  varchar2(10 char);
  begin
    vlang := GetUserLangSettings;
    select count(*)
    into vcount
    from analytictypes
    where analytictype = pAnalyticType;
    if vcount = 1 then
      if pAnalyticType = 21008 then
        open pResCursor for
          select analyticcode,
                 decode(vlang, 'Kazakh', shortname_kz, shortname) as shortname,
                 decode(vlang, 'Kazakh', name_kz, name) as name,
                 0 as isdefault
          from analyticcodes
          where analytictype = pAnalyticType
          --and altermode <> -1
          order by analyticcode;
      else
        case vlang
          when 'Kazakh' then
            open pResCursor for
              select analyticcode,
                     shortname_kz    as shortname,
                     name_kz as name,
                     0               as isdefault
              from analyticcodes
              where analytictype = pAnalyticType
              --and altermode <> -1
              order by shortname;
          else
            open pResCursor for
              select analyticcode,
                     shortname,
                     name as name,
                     0 as isdefault
              from analyticcodes
              where analytictype = pAnalyticType
              --and altermode <> -1
              order by shortname;
        end case;
      end if;
    else
      result := -10120; --Тип аналитической информации задан неверно
    end if;
    return result;
  end GetAnalyticShortNames;

  function GetAnalyticCodesDescriptions(pAnalyticType                 in number,
                                        vAnalyticCodesDescriptionsCur out sys_refcursor) return number is
    result number default 0;
    vcount number default 0;
  begin

    select count(*)
    into vcount
    from analytictypes
    where analytictype = pAnalyticType;
    if vcount = 1 then
      open vAnalyticCodesDescriptionsCur for
        select Analyticcode,
               name,
               shortname
        from analyticcodes
        where analytictype = pAnalyticType;
        --and altermode <> -1;
    else
      result := -10120; --Тип аналитической информации задан неверно
    end if;
    return result;
  end;

  function GetKATOInfoByParent(pParentID  in number,
                               pResCursor out sys_refcursor) return number is
  begin

    open pResCursor for
      select k.teparent,
             k.tenumber,
             k.rus_name,
             decode((select count(*)
                    from KATO k2
                    where k2.teparent = k.tenumber),
                    0,
                    0,
                    1) as IsParent
      from KATO k
      where k.teparent = pParentID
      order by k.rus_name; --k.teparent, k.tenumber;

    return(0);
  end GetKATOInfoByParent;

  function GetModuleOwner(pmodulename in varchar2) return varchar2 is

    isowner number;
  begin
    select owner
    into isowner
    from soft
    where upper(trim(softname)) = upper(trim(pmodulename));
    if isowner = 1 then
      return 'CFM';
    elsif isowner = 0 then
      return 'KFM';
    end if;

  exception
    when no_data_found then
      return - 12;
  end;

  function SetLanguage(pLang in varchar2) return number is
    sessionnumber number default 0;
  begin
    select SID
    into sessionnumber
    from v$session
    where audsid = userenv('SESSIONID');
    insert into sessions
      (sessioncode,
       userid,
       userlanguage)
    values
      (sessionnumber,
       pkg_users.GetCurrentUserId,
       pLang);
    return 0;
  end;

FUNCTION SetSessionParams (pLang IN VARCHAR2, pModuleName IN VARCHAR2)
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   sessionnumber     NUMBER DEFAULT 0;
   isExistsSession   INT DEFAULT 0;
BEGIN
   SELECT SID
     INTO sessionnumber
     FROM v$session
    WHERE audsid = USERENV ('SESSIONID');

   SELECT COUNT (*)
     INTO isExistsSession
     FROM sessions ss
    WHERE     ss.sessioncode = sessionnumber
          AND ss.userlanguage = pLang
          AND ss.modulename = pModuleName
          AND ss.userid = pkg_users.GetCurrentUserId;

   IF (isExistsSession = 0)
   THEN
      INSERT INTO sessions (sessioncode,
                            userid,
                            userlanguage,
                            modulename)
           VALUES (sessionnumber,
                   pkg_users.GetCurrentUserId,
                   pLang,
                   pModuleName);

      COMMIT;
      pkg_audits.WriteUserAudit (1,
                                 'pkg_main.SetSessionParams',
                                 'Язык: ' || plang);
   END IF;

   RETURN 0;
END;

  function GetLastException(pExceptionCode        out number,
                            pExceptionDescription out varchar2,
                            pProcedure            out varchar2) return number is
    result    number default 0;
    pdate_exc date;
  begin
    select max(date_exc)
    into pdate_exc
    from exceptionstable;
    select oraexceptionid,
           stacktrace,
           procedure_exc
    into pExceptionCode,
         pExceptionDescription,
         pProcedure
    from exceptionstable
    where date_exc = pdate_exc;
    return result;
  end;

  procedure Reset_Sequence(seq_name  varchar2 default Null,
                           seq_owner varchar2 default USER) is
    seq_maxvalue  number;
    seq_minvalue  number;
    seq_curvalue  number;
    seq_cycle     varchar2(7);
    seq_cachesize varchar2(30 char);
    seq_value     number;
    pNUM          number;
  begin
    -- If seq_name is NULL then goto Nothing_To_DO; end if;
    SELECT messnumb_temp.Nextval
    INTO pNUM
    FROM DUAL;
    -- Get Info
    select MAX_VALUE,
           MIN_VALUE,
           DECODE(CACHE_SIZE, '0', 'NOCACHE', 'Cache ' || CACHE_SIZE) CACHE_SIZE,
           DECODE(CYCLE_FLAG, 'Y', 'CYCLE', 'NOCYCLE') CYCLE_FLAG
    into seq_maxvalue,
         seq_minvalue,
         seq_cachesize,
         seq_cycle
    from all_sequences
    where sequence_owner = UPPER(seq_owner)
    and sequence_name = UPPER(seq_name)
    and rownum < 2;

    -- No CACHE Values for Next Seq Numbers
    execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' NOCACHE';

    -- Get Current Value Number
    select LAST_NUMBER - INCREMENT_BY as cur_value
    into seq_curvalue
    from all_sequences
    where sequence_owner = UPPER(seq_owner)
    and sequence_name = UPPER(seq_name)
    and rownum < 2;

    -- Cycle with MaxValue as CurValue
    execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' CYCLE MaxValue ' || Seq_CurValue;

    -- Reset to Min
    execute immediate 'BEGIN select ' || seq_owner || '.' || seq_name || '.NextVal INTO :a from dual;
                    END;' /* alter sequence '||seq_owner||'.'||seq_name||' start with '||'1'||';*/
      using out seq_value;

    -- Return All Rules BACK
    execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' MAXVALUE ' || seq_maxvalue || ' ' || seq_cachesize || ' ' || seq_cycle;

    --<> Null;*/

  EXCEPTION
    WHEN NO_DATA_FOUND then
      raise_application_error(-20900,
                              'Sequence
                                     ' || Seq_Owner || '.' || Seq_Name || ' does not exist');
    WHEN OTHERS THEN
      raise_application_error(-20901,
                              'Error occure with reset of Sequence
                                     ' || Seq_Owner || '.' || Seq_Name);

  END Reset_Sequence;

  function GetUserLangSettings return varchar2 is
    vlangname     varchar2(30 char);
    sessionnumber number default 0;
  begin
    select SID
    into sessionnumber
    from v$session
    where audsid = userenv('SESSIONID');
    select userlanguage
    into vlangname
    from sessions
    where sessioncode = sessionnumber;

    return vlangname;
  exception

    when others then
      return null; -- session failed

  end;

  function GetCurrentSettings(vLangName   out varchar2,
                              vModuleName out varchar2) return number is
    result        number default 0;
    sessionnumber number default 0;
  begin
    select SID
    into sessionnumber
    from v$session
    where audsid = userenv('SESSIONID');
    select userlanguage,
           upper(trim(modulename))
    into vLangName,
         vModuleName
    from sessions
    where sessioncode = sessionnumber;
    return result;

  exception
    when others then
        return - 5000; --
  end;

end PKG_Main;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MAIN FOR KFM.PKG_MAIN;


GRANT EXECUTE ON KFM.PKG_MAIN TO KFM_ROLE;
