/*
* Автор: Борисов Д.Г.
* Дата: 2014-09-28
* Задача: http://jira.softage.ru/jira/browse/SDFO-XX (нет в Jira)
* Назначение: Исправление в паспорте для сохранения AT-кодов
* Изменяются объекты:
		create function NewFunction
		drop procedure DelProc
		update table UpdatedTable
*/

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

declare
	filename      varchar2(256) := 'sfm.database-update.2014-09-28.UpdatePassport.sql';
	purpose       varchar2(4000) := 'Исправление в паспорте для сохранения AT-кодов';
	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;
/

	/*
	****** Begin update script ******
	*/
		-- Не транзакционный апдейт
		-- Необходимы свои транзакции, если требуются
		create or replace 
PACKAGE BODY     "PKG_PASSPORT_CFM" as
  
  function GetLatestAnalyticType(pDataCode in number) return number is
  analyticType number default 0;
  begin
	select max(analytictypes.analytictype)
	into analyticType
	from analytictypes
	where analytictypes.datacode = pDataCode and
    (analytictypes.start_date is null or analytictypes.start_date <= sysdate);
    return analyticType;
  end;
  
  function UpdatePassport(pCFMCODE           in number,
                          pMAINCODE          in varchar2,
                          pTAXCODE           in varchar2,
                          pOKPO              in varchar2,
                          pOKED              in varchar2,
                          pFULLNAME          in varchar2,
                          pSHORTNAME         in varchar2,
                          pREGISTR_DATE      in date,
                          pREGGISTR_NUMBER   in varchar2,
                          pREGISTR_WHO       in varchar2,
                          pREG_KATO          in varchar2,
                          pREG_STREET        in varchar2,
                          pREG_HOUSE         in varchar2,
                          pREG_OFFICE        in varchar2,
                          pREG_POSTINDEX     in varchar2,
                          pREG_DIRECTORY     in varchar2,
                          pREG_AREA          in varchar2,
                          pREG_DISTRICT      in varchar2,
                          pREG_CITY          in varchar2,
                          pBankCode          in varchar2,
                          pFasti_name        in varchar2,
                          pREG_AREA_CODE     in number default null,
                          pREG_DISTRICT_CODE in number default null,
                          pREG_CITY_CODE     in number default null,
                          pREG_STREET_CODE   in number default null,
                          pREG_HOUSE_CODE    in number default null,
                          pREG_OFFICE_CODE   in number default null,
                          pCFMTYPE           in NUMBER default null,
                          pORGCODE           in NUMBER default null,
                          pSECONDNAME        in VARCHAR2 default null,
                          pFIRSTNAME         in VARCHAR2 default null,
                          pMIDDLENAME        in VARCHAR2 default null,
                          pAC_DOC_TYPECODE   in NUMBER default null,
                          pAC_DOC_NUMBER     in VARCHAR2 default null,
                          pAC_DOC_SERIES     in VARCHAR2 default null,
                          pAC_DOC_WHOM       in VARCHAR2 default null,
                          pAC_DOC_DATE       in DATE default null,
                          pOKED_CODE         in number default null,
			  pCFM_CODE_AT       in number default null,
		          pOKED_AT           in number default null,
			  pREG_AREA_CODE_AT  in number default null,
			  pREG_DISTRICT_CODE_AT in number default null,
			  pREG_CITY_CODE_AT  in number default null,
			  pDOC_TYPECODE_AT   in number default null,
			  pORGCODE_AT        in number default null)
    return number is
    Result      number default 0;
    pPassportId number default null;

  begin
    
    update passport_cfm
       set CFMCODE           = pCFMCODE,
           MAINCODE          = pMAINCODE,
           TAXCODE           = pTAXCODE,
           OKPO              = pOKPO,
           OKED              = pOKED,
           FULLNAME          = pFULLNAME,
           SHORTNAME         = pSHORTNAME,
           REGISTR_DATE      = pREGISTR_DATE,
           REGGISTR_NUMBER   = pREGGISTR_NUMBER,
           REGISTR_WHO       = pREGISTR_WHO,
           REG_KATO          = pREG_KATO,
           REG_STREET        = pREG_STREET,
           REG_HOUSE         = pREG_HOUSE,
           REG_OFFICE        = pREG_OFFICE,
           REG_POSTINDEX     = pREG_POSTINDEX,
           REG_DIRECTORY     = pREG_DIRECTORY,
           REG_AREA          = pREG_AREA,
           REG_DISTRICT      = pREG_DISTRICT,
           REG_CITY          = pREG_CITY,
           BANKCODE          = pBankcode,
           FASTI_NAME        = pfasti_name,
           REG_AREA_CODE     = pREG_AREA_CODE,
           REG_DISTRICT_CODE = pREG_DISTRICT_CODE,
           REG_CITY_CODE     = pREG_CITY_CODE,
           REG_STREET_CODE   = pREG_STREET_CODE,
           REG_HOUSE_CODE    = pREG_HOUSE_CODE,
           REG_OFFICE_CODE   = pREG_OFFICE_CODE,
           CFMTYPE           = pCFMTYPE,
           ORGCODE           = pORGCODE,
           SECONDNAME        = pSECONDNAME,
           FIRSTNAME         = pFIRSTNAME,
           MIDDLENAME        = pMIDDLENAME,
           AC_DOC_TYPECODE   = pAC_DOC_TYPECODE,
           AC_DOC_NUMBER     = pAC_DOC_NUMBER,
           AC_DOC_SERIES     = pAC_DOC_SERIES,
           AC_DOC_WHOM       = pAC_DOC_WHOM,
           AC_DOC_DATE       = pAC_DOC_DATE,
           OKED_CODE         = pOKED_CODE,
	   CFMCODE_AT        = pCFM_CODE_AT,
	   OKED_AT           = pOKED_AT,
	   REG_AREA_CODE_AT  = pREG_AREA_CODE_AT,
	   REG_DISTRICT_CODE_AT = pREG_DISTRICT_CODE_AT,
	   REG_CITY_CODE_AT  = pREG_CITY_CODE_AT,
	   DOC_TYPECODE_AT   = pDOC_TYPECODE_AT,
	   ORGCODE_AT        = pORGCODE_AT,
           PASSPORT_STATUS   = 1 --     данные только изменились
     where (PASSPORTID = 1);

    insert into passport_cfm_history
      (CFMCODE,
       MAINCODE,
       TAXCODE,
       OKPO,
       OKED,
       FULLNAME,
       SHORTNAME,
       REGISTR_DATE,
       REGGISTR_NUMBER,
       REGISTR_WHO,
       REG_KATO,
       REG_STREET,
       REG_HOUSE,
       REG_OFFICE,
       REG_POSTINDEX,
       REG_DIRECTORY,
       REG_AREA,
       REG_DISTRICT,
       REG_CITY,
       BANKCODE,
       FASTI_NAME,
       REG_AREA_CODE,
       REG_DISTRICT_CODE,
       REG_CITY_CODE,
       REG_STREET_CODE,
       REG_HOUSE_CODE,
       REG_OFFICE_CODE,
       CFMTYPE,
       ORGCODE,
       SECONDNAME,
       FIRSTNAME,
       MIDDLENAME,
       AC_DOC_TYPECODE,
       AC_DOC_NUMBER,
       AC_DOC_SERIES,
       AC_DOC_WHOM,
       AC_DOC_DATE,
       OKED_CODE,
       alter_date)
    values
      (pCFMCODE,
       pMAINCODE,
       pTAXCODE,
       pOKPO,
       pOKED,
       pFULLNAME,
       pSHORTNAME,
       pREGISTR_DATE,
       pREGGISTR_NUMBER,
       pREGISTR_WHO,
       pREG_KATO,
       pREG_STREET,
       pREG_HOUSE,
       pREG_OFFICE,
       pREG_POSTINDEX,
       pREG_DIRECTORY,
       pREG_AREA,
       pREG_DISTRICT,
       pREG_CITY,
       pBANKCODE,
       pFASTI_NAME,
       pREG_AREA_CODE,
       pREG_DISTRICT_CODE,
       pREG_CITY_CODE,
       pREG_STREET_CODE,
       pREG_HOUSE_CODE,
       pREG_OFFICE_CODE,
       pCFMTYPE,
       pORGCODE,
       pSECONDNAME,
       pFIRSTNAME,
       pMIDDLENAME,
       pAC_DOC_TYPECODE,
       pAC_DOC_NUMBER,
       pAC_DOC_SERIES,
       pAC_DOC_WHOM,
       pAC_DOC_DATE,
       pOKED_CODE,
       SYSDATE);


    pkg_audits.WriteUserAudit(7, 'pkg_passport_cfm.UpdatePassport', null);
    return Result;

  exception
    when others then
      Result := -2000; --Попытка добавить неверные по формату  данные в таблицу или иная ошибка
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  ' PKG_PASSPORT.UpdatePassport');
      return Result;
  end UpdatePassport;

  function SetPassport(pCFMCODE           in number,
                       pMAINCODE          in varchar2,
                       pTAXCODE           in varchar2,
                       pOKPO              in varchar2,
                       pOKED              in varchar2,
                       pFULLNAME          in varchar2,
                       pSHORTNAME         in varchar2,
                       pREGISTR_DATE      in date,
                       pREGGISTR_NUMBER   in varchar2,
                       pREGISTR_WHO       in varchar2,
                       pREG_KATO          in varchar2,
                       pREG_STREET        in varchar2,
                       pREG_HOUSE         in varchar2,
                       pREG_OFFICE        in varchar2,
                       pREG_POSTINDEX     in varchar2,
                       pREG_DIRECTORY     in varchar2,
                       pREG_AREA          in varchar2,
                       pREG_DISTRICT      in varchar2,
                       pREG_CITY          in varchar2,
                       pBankCode          in varchar2,
                       pFasti_name        in varchar2,
                       pREG_AREA_CODE     in number default null, -- изменить обязательно XXX
                       pREG_DISTRICT_CODE in number default null, -- изменить обязательно XXX
                       pREG_CITY_CODE     in number default null,
                       pREG_STREET_CODE   in number default null, -- изменить обязательно XXX
                       pREG_HOUSE_CODE    in number default null, -- изменить обязательно XXX
                       pREG_OFFICE_CODE   in number default null,
                       pCFMTYPE           in NUMBER default 1,
                       pORGCODE           in NUMBER default null, -- изменить обязательно XXX
                       pSECONDNAME        in VARCHAR2 default null,
                       pFIRSTNAME         in VARCHAR2 default null,
                       pMIDDLENAME        in VARCHAR2 default null,
                       pAC_DOC_TYPECODE   in NUMBER default null,
                       pAC_DOC_NUMBER     in VARCHAR2 default null,
                       pAC_DOC_SERIES     in VARCHAR2 default null,
                       pAC_DOC_WHOM       in VARCHAR2 default null,
                       pAC_DOC_DATE       in DATE default null,
                       pOKED_CODE            in number default null
                       ) return number is
    Result         number default 0;
    result1        number default 0;
    vpassportcount number default 0;
    vcountCahanged number default 0;
    vOldPassport   passport_cfm%rowtype;
    vNewPassport   passport_cfm%rowtype;
    pPassportId    number default null;
	pCfmCodeAt number;
	pOkedAt number;
	pRegAreaCodeAt number;
	pRegDistrictCodeAt number;
	pRegCityCodeAt number;
	pDocTypeCodeAt number;
	pOrgCodeAt number;

  begin
    if (pMAINCODE is null or pCFMCODE is null or pORGCODE is null or
       pFULLNAME is null) then
      return - 2020; -- Не задано ни одно из ключевых значений для Организции, такие как ИИН/БИН, ОПФ, Код СФМ
    elsif (pREG_AREA_CODE is null or pREG_DISTRICT_CODE is null) then
      result := -2021;

    end if;
	
	-- Получить актуальные справочники
	pCfmCodeAt := GetLatestAnalyticType(1005); -- 1005 - датакод справочника видов СФМ
	pOkedAt := GetLatestAnalyticType(1021); -- 1021 - датакод справочника ОКЭД
    pRegAreaCodeAt := GetLatestAnalyticType(3001); -- 3001 - датакод области
    pRegDistrictCodeAt := GetLatestAnalyticType(3002); -- 3002	- датакод района
	pRegCityCodeAt := GetLatestAnalyticType(3003); -- 3003 - датакод города
	pDocTypeCodeAt := GetLatestAnalyticType(100); -- 100 - тип документа
	pOrgCodeAt := GetLatestAnalyticType(1003); -- 1003 - датакод справочника ОПФ СФМ
	
    select count(*) into vpassportcount from passport_cfm;
    if vpassportcount = 1 then
      select count(*)
        into vcountCahanged
        from passport_cfm t
       where t.passportid = 1
         and CFMCODE = pCFMCODE
         and MAINCODE = pMAINCODE
         and TAXCODE = pTAXCODE
         and OKPO = pOKPO
         and OKED = pOKED
         and FULLNAME = pFULLNAME
         and SHORTNAME = pSHORTNAME
         and REGISTR_DATE = pREGISTR_DATE
         and REGGISTR_NUMBER = pREGGISTR_NUMBER
         and REGISTR_WHO = pREGISTR_WHO
         and REG_KATO = pREG_KATO
         and REG_STREET = pREG_STREET
         and REG_HOUSE = pREG_HOUSE
         and REG_OFFICE = pREG_OFFICE
         and REG_POSTINDEX = pREG_POSTINDEX
         and REG_DIRECTORY = pREG_DIRECTORY
         and REG_AREA = pREG_AREA
         and REG_DISTRICT = pREG_DISTRICT
         and REG_CITY = pREG_CITY
         and BANKCODE = pBankcode
         and FASTI_NAME = pfasti_name
         and REG_AREA_CODE = pREG_AREA_CODE
         and REG_DISTRICT_CODE = pREG_DISTRICT_CODE
         and REG_CITY_CODE = pREG_CITY_CODE
         and REG_STREET_CODE = pREG_STREET_CODE
         and REG_HOUSE_CODE = pREG_HOUSE_CODE
         and REG_OFFICE_CODE = pREG_OFFICE_CODE
         and CFMTYPE = pCFMTYPE
         and ORGCODE = pORGCODE
         and SECONDNAME = pSECONDNAME
         and FIRSTNAME = pFIRSTNAME
         and MIDDLENAME = pMIDDLENAME
         and AC_DOC_TYPECODE = pAC_DOC_TYPECODE
         and AC_DOC_NUMBER = pAC_DOC_NUMBER
         and AC_DOC_SERIES = pAC_DOC_SERIES
         and AC_DOC_WHOM = pAC_DOC_WHOM
         and AC_DOC_DATE = pAC_DOC_DATE
         and OKED_CODE = pOKED_CODE;
      /* select t.* into vOldPassport from  passport_cfm t where t.passportid=1;
      select 1,pCFMCODE, pMAINCODE, pTAXCODE, pOKPO, pOKED, pFULLNAME, pSHORTNAME,
                              pREGISTR_DATE, pREGGISTR_NUMBER, pREGISTR_WHO, pREG_KATO, pREG_STREET,
                              pREG_HOUSE, pREG_OFFICE, pREG_POSTINDEX, pREG_DIRECTORY,pREG_AREA, pREG_DISTRICT, pREG_CITY,pBANKCODE,pFASTI_NAME,
                              pREG_AREA_CODE,pREG_DISTRICT_CODE,pREG_CITY_CODE,pREG_STREET_CODE,pREG_HOUSE_CODE,pREG_OFFICE_CODE,t.passport_status,
                              pCFMTYPE,pORGCODE,
                              pSECONDNAME, pFIRSTNAME , pMIDDLENAME , pAC_DOC_TYPECODE , pAC_DOC_NUMBER , pAC_DOC_SERIES, pAC_DOC_WHOM ,pAC_DOC_DATE
             into vNewPassport from passport_cfm t where t.passportid=1;
      if vOldPassport.*=vNewPassport.*
         then return -2023;
      end if; */
      if vcountCahanged = 1 then
        return - 2023;
      end if;
      result1 := UpdatePassport(pCFMCODE,
                                pMAINCODE,
                                pTAXCODE,
                                pOKPO,
                                pOKED,
                                pFULLNAME,
                                pSHORTNAME,
                                pREGISTR_DATE,
                                pREGGISTR_NUMBER,
                                pREGISTR_WHO,
                                pREG_KATO,
                                pREG_STREET,
                                pREG_HOUSE,
                                pREG_OFFICE,
                                pREG_POSTINDEX,
                                pREG_DIRECTORY,
                                pREG_AREA,
                                pREG_DISTRICT,
                                pREG_CITY,
                                pBANKCODE,
                                pFASTI_NAME,
                                pREG_AREA_CODE,
                                pREG_DISTRICT_CODE,
                                pREG_CITY_CODE,
                                pREG_STREET_CODE,
                                pREG_HOUSE_CODE,
                                pREG_OFFICE_CODE,
                                pCFMTYPE,
                                pORGCODE,
                                pSECONDNAME,
                                pFIRSTNAME,
                                pMIDDLENAME,
                                pAC_DOC_TYPECODE,
                                pAC_DOC_NUMBER,
                                pAC_DOC_SERIES,
                                pAC_DOC_WHOM,
                                pAC_DOC_DATE,
                                pOKED_CODE,
								pCfmCodeAt,
								pOkedAt,
								pRegAreaCodeAt,
								pRegDistrictCodeAt,
								pRegCityCodeAt,
								pDocTypeCodeAt,
								pOrgCodeAt
								);
      if result1 <> 0 then
        return result1;
      end if;
    else
	  
      insert into passport_cfm
        (PASSPORTID,
         CFMCODE,
         MAINCODE,
         TAXCODE,
         OKPO,
         OKED,
         FULLNAME,
         SHORTNAME,
         REGISTR_DATE,
         REGGISTR_NUMBER,
         REGISTR_WHO,
         REG_KATO,
         REG_STREET,
         REG_HOUSE,
         REG_OFFICE,
         REG_POSTINDEX,
         REG_DIRECTORY,
         REG_AREA,
         REG_DISTRICT,
         REG_CITY,
         BANKCODE,
         FASTI_NAME,
         REG_AREA_CODE,
         REG_DISTRICT_CODE,
         REG_CITY_CODE,
         REG_STREET_CODE,
         REG_HOUSE_CODE,
         REG_OFFICE_CODE,
         CFMTYPE,
         ORGCODE,
         SECONDNAME,
         FIRSTNAME,
         MIDDLENAME,
         AC_DOC_TYPECODE,
         AC_DOC_NUMBER,
         AC_DOC_SERIES,
         AC_DOC_WHOM,
         AC_DOC_DATE,
         OKED_CODE,
		 CFMCODE_AT,
		 OKED_AT,
		 REG_AREA_CODE_AT,
		 REG_DISTRICT_CODE_AT,
		 REG_CITY_CODE_AT,
		 DOC_TYPECODE_AT,
		 ORGCODE_AT)
      values
        (1,
         pCFMCODE,
         pMAINCODE,
         pTAXCODE,
         pOKPO,
         pOKED,
         pFULLNAME,
         pSHORTNAME,
         pREGISTR_DATE,
         pREGGISTR_NUMBER,
         pREGISTR_WHO,
         pREG_KATO,
         pREG_STREET,
         pREG_HOUSE,
         pREG_OFFICE,
         pREG_POSTINDEX,
         pREG_DIRECTORY,
         pREG_AREA,
         pREG_DISTRICT,
         pREG_CITY,
         pBANKCODE,
         pFASTI_NAME,
         pREG_AREA_CODE,
         pREG_DISTRICT_CODE,
         pREG_CITY_CODE,
         pREG_STREET_CODE,
         pREG_HOUSE_CODE,
         pREG_OFFICE_CODE,
         pCFMTYPE,
         pORGCODE,
         pSECONDNAME,
         pFIRSTNAME,
         pMIDDLENAME,
         pAC_DOC_TYPECODE,
         pAC_DOC_NUMBER,
         pAC_DOC_SERIES,
         pAC_DOC_WHOM,
         pAC_DOC_DATE,
         pOKED_CODE,
		 pCfmCodeAt,
		 pOkedAt,
		 pRegAreaCodeAt,
		 pRegDistrictCodeAt,
		 pRegCityCodeAt,
     pDocTypeCodeAt,
     pOrgCodeAt);
    end if;
    insert into passport_cfm_history
      (CFMCODE,
       MAINCODE,
       TAXCODE,
       OKPO,
       OKED,
       FULLNAME,
       SHORTNAME,
       REGISTR_DATE,
       REGGISTR_NUMBER,
       REGISTR_WHO,
       REG_KATO,
       REG_STREET,
       REG_HOUSE,
       REG_OFFICE,
       REG_POSTINDEX,
       REG_DIRECTORY,
       REG_AREA,
       REG_DISTRICT,
       REG_CITY,
       BANKCODE,
       FASTI_NAME,
       REG_AREA_CODE,
       REG_DISTRICT_CODE,
       REG_CITY_CODE,
       REG_STREET_CODE,
       REG_HOUSE_CODE,
       REG_OFFICE_CODE,
       CFMTYPE,
       ORGCODE,
       SECONDNAME,
       FIRSTNAME,
       MIDDLENAME,
       AC_DOC_TYPECODE,
       AC_DOC_NUMBER,
       AC_DOC_SERIES,
       AC_DOC_WHOM,
       AC_DOC_DATE,
       OKED_CODE,
       alter_date)
    values
      (pCFMCODE,
       pMAINCODE,
       pTAXCODE,
       pOKPO,
       pOKED,
       pFULLNAME,
       pSHORTNAME,
       pREGISTR_DATE,
       pREGGISTR_NUMBER,
       pREGISTR_WHO,
       pREG_KATO,
       pREG_STREET,
       pREG_HOUSE,
       pREG_OFFICE,
       pREG_POSTINDEX,
       pREG_DIRECTORY,
       pREG_AREA,
       pREG_DISTRICT,
       pREG_CITY,
       pBANKCODE,
       pFASTI_NAME,
       pREG_AREA_CODE,
       pREG_DISTRICT_CODE,
       pREG_CITY_CODE,
       pREG_STREET_CODE,
       pREG_HOUSE_CODE,
       pREG_OFFICE_CODE,
       pCFMTYPE,
       pORGCODE,
       pSECONDNAME,
       pFIRSTNAME,
       pMIDDLENAME,
       pAC_DOC_TYPECODE,
       pAC_DOC_NUMBER,
       pAC_DOC_SERIES,
       pAC_DOC_WHOM,
       pAC_DOC_DATE,
       pOKED_CODE,
       SYSDATE)
    returning PassportId into pPassportId;
    insert into passport_cfm_stages_history
      (passportid, status, alter_date, request_id, last_answer_id, userid)
    values
      (pPassportId, 1, SYSDATE, null, null, pkg_users.GetCurrentUserId);

    pkg_audits.WriteUserAudit(7, 'pkg_passport_cfm.SetPassport', null);
    Return Result;

  exception
    when others then
      Result := -2000; --Попытка добавить неверные по формату  данные в таблицу или иная ошибка
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  ' PKG_PASSPORT.SetPassport');
      return Result;
  end SetPassport;

  function GetPassport(vPassportCur out sys_refcursor) return number is
    Result number := 0;
  begin

    open vPassportCur for
      select CFMCODE,
             MAINCODE,
             TAXCODE,
             OKPO,
             OKED,
             FULLNAME,
             SHORTNAME,
             REGISTR_DATE,
             REGGISTR_NUMBER,
             REGISTR_WHO,
             REG_KATO,
             REG_STREET,
             REG_HOUSE,
             REG_OFFICE,
             REG_POSTINDEX,
             REG_DIRECTORY,
             REG_AREA,
             REG_DISTRICT,
             REG_CITY,
             BANKCODE,
             FASTI_NAME,
             REG_AREA_CODE,
             REG_DISTRICT_CODE,
             REG_CITY_CODE,
             REG_STREET_CODE,
             REG_HOUSE_CODE,
             REG_OFFICE_CODE,
             cfmtype,
             orgcode,
             secondname,
             firstname,
             middlename,
             ac_doc_typecode,
             ac_doc_number,
             ac_doc_series,
             ac_doc_whom,
             ac_doc_date,
             systemid,
             cfmcode_at,
             oked_at,
             reg_area_code_at,
             reg_district_code_at,
             reg_city_code_at,
             orgcode_at,
             doc_typecode_at,
             oked_code
        from passport_cfm;
    return Result;
  end;

  function SetDirectory(pREG_DIRECTORY in varchar2) return number is
    Result number default 0;
  begin
    update passport_cfm
       set REG_DIRECTORY = pREG_DIRECTORY
     where PASSPORTID = 1;
    return Result;
  end;

  function GetDirectory(pREG_DIRECTORY out varchar2) return number is
    Result number default 0;
  begin
    select REG_DIRECTORY
      into pREG_DIRECTORY
      from passport_cfm
     where passportid = 1;
    return Result;
  end;

  function GetAdditionalAcData_1(pcfmtype in number) return clob is
    pxml clob;

  begin
    --алексей 2012.10.23
    case pcfmtype
      when 1 then
        /*
              select  XMLElement("AdditionalAcData",XMLATTRIBUTES('False' as "IsAc"),null).getclobval()
                into pxml from dual;
        */
        select XMLElement("AdditionalAcData", XMLATTRIBUTES('False' as "IsAc"), XMLElement("FirstName", firstname), XMLElement("SecondName", secondname), XMLElement("MiddleName", middlename), XMLElement("DocumentIdentity", ac_doc_typecode), XMLElement("SeriesDocIdentity", ac_doc_series), XMLElement("NumberDocIdentity", ac_doc_number), XMLElement("DocumentIssued", ac_doc_whom), XMLElement("DateIssuance", to_char(ac_doc_date, 'dd.mm.yyyy')))
               .getclobval()
          into pxml
          from passport_cfm
         where passportid = 1;
      else
        select XMLElement("AdditionalAcData", XMLATTRIBUTES('True' as "IsAc"), XMLElement("FirstName", firstname), XMLElement("SecondName", secondname), XMLElement("MiddleName", middlename), XMLElement("DocumentIdentity", ac_doc_typecode), XMLElement("SeriesDocIdentity", ac_doc_series), XMLElement("NumberDocIdentity", ac_doc_number), XMLElement("DocumentIssued", ac_doc_whom), XMLElement("DateIssuance", to_char(ac_doc_date, 'dd.mm.yyyy')))
               .getclobval()
          into pxml
          from passport_cfm
         where passportid = 1;
    end case;
    return pxml;
  end;

  function GetFoundersData_1 return clob is
    pxml   clob;
    qryCtx DBMS_XMLGEN.CTXHANDLE;
  begin
    /*select  XMLElement("Founders",XMLATTRIBUTES('True' as "IsAc"),
            XMLElement("FirstName",firstname) ,
            XMLElement("SecondName",secondname),
            XMLElement("MiddleName",middlename),
            XMLElement("DocumentIdentity",ac_doc_typecode),
            XMLElement("SeriesDocIdentity",ac_doc_series),
            XMLElement("NumberDocIdentity",ac_doc_number),
            XMLElement("DocumentIssued",ac_doc_whom),
            XMLElement("DateIssuance",ac_doc_date)
            )
    into pxml from passport_cfm where passportid=1;*/

    /*qryCtx := DBMS_XMLGEN.NEWCONTEXT('select  founder_type as "FounderType",
                                            founder_orgcode as "OpfCode",
                                            founder_ur_name as "UrName",
                                            case founder_type
                                             when 1 then
                                               ''''
                                             else
                                              XMLAGG(XMLConcat(XMLElement("FirstName",founder_ac_firstname),
                                              XMLElement("SecondName",founder_ac_secondname),
                                              XMLElement("MiddleName",founder_ac_middlename)
                                              )).getclobval()

                                            end as "AdditionalAcData"
                                      from founders_cfm')
                                          ;
    DBMS_XMLGEN.setRowTag(qryCtx, 'Founder');
    DBMS_XMLGEN.setRowSetTag(qryCtx, 'Founders');*/
    select XMLElement("Founders", XMLAgg(XMLElement("Founder",
           --          nvl2(imm.MEMBER_ROLE,XMLElement("ParticipantOperation",imm.MEMBER_ROLE),XMLElement("ParticipantOperation",XMLATTRIBUTES('true' as "xsi:nil"))),
           XMLElement("FounderType", founder_type), XMLElement("OpfCode", founder_orgcode), XMLElement("UrName", founder_ur_name),case
            founder_type
             when 1 then
              XMLElement("AdditionalAcData",
                         XMLATTRIBUTES('False' as
                                       "IsAc"),
                         '')
             else
              XMLElement("AdditionalAcData",
                         XMLATTRIBUTES('True' as
                                       "IsAc"),
                         XMLForest(founder_ac_firstname as
                                   "FirstName",
                                   founder_ac_secondname as
                                   "SecondName",
                                   founder_ac_middlename as
                                   "MiddleName"))
           end, XMLElement("ResidenceCode", founder_residence_c_code))))
           .getclobval()
      INTO pxml
      from founders_cfm;
    return pxml;
  end;

  function GetUsersData_1 return clob is
    pxml   clob;
    qryCtx DBMS_XMLGEN.CTXHANDLE;

  begin
    /*  qryCtx := DBMS_XMLGEN.NEWCONTEXT('select firstname   as "FirstName",
                                            lastname    as "SecondName",
                                            middlename  as "MiddleName",
                                            pers_number as "IndividualNumber",
                                            jobname as "JobName",
                                            phones  as "Phone",
                                            emails as "Email",
                                            sertificate as "Certificate",
                                            sertificate_name as "CertificateName"
                                      from users  u
                                      where
                                      exists (select 1 from user_roles ur where ur.userid = u.userid and
                                                                                bitand(ur.roleid,4)=4 and
                                                                                sysdate between ur.datebegin and ur.dateend)
                                      and u.isenable = 1')
                                          ;
    DBMS_XMLGEN.setRowTag(qryCtx, 'Person');
    DBMS_XMLGEN.setRowSetTag(qryCtx, 'Persons');

    pxml   := DBMS_XMLGEN.GETXMLTYPE(qryCtx, DBMS_XMLGEN.NONE);  */

    select XMLElement("Persons", XMLAgg(XMLElement("Person",
           --          nvl2(imm.MEMBER_ROLE,XMLElement("ParticipantOperation",imm.MEMBER_ROLE),XMLElement("ParticipantOperation",XMLATTRIBUTES('true' as "xsi:nil"))),
           XMLElement("FirstName", firstname), XMLElement("SecondName", lastname), XMLElement("MiddleName", middlename), XMLElement("IndividualNumber", pers_number), XMLElement("JobName", jobname), XMLElement("Phone", phones), XMLElement("Email", emails), XMLElement("Certificate", XMLATTRIBUTES(sertificate_name as "Name", sertificate_size as "Size"), sertificate))))
           .getclobval()
      INTO pxml
      from users u
     where exists (select 1
              from user_roles ur
             where ur.userid = u.userid
               and bitand(ur.roleid, 4) = 4
               and sysdate between ur.datebegin and ur.dateend)
       and u.isenable = 1 and u.IS_DISMISSED = 0;

    return pxml;
  end;

  function GetPassportXML_1(pOUTXML out clob) return number is
    lRes              clob;
    lXML              xmltype;
    pCfmType          number;
    lAdditionalAcData clob;
    lFoundersData     clob;
    lUsersData        clob;
    resultValidate    clob;

    result number;
  begin

    begin
      select cfmtype into pCFMtype from passport_cfm where passportid = 1;
    exception
      when others then
        pkg_exceptions.AddException(sqlcode,
                                    sqlerrm,
                                    ' PKG_PASSPORT.GetPassportXML_1');
        return - 2001; ---Не заданы ключевые данные об организации: ИИН/БИН, РНН
    end;

    lAdditionalAcData := GetAdditionalAcData_1(pCfmType);
    lFoundersData     := GetFoundersData_1;
    lUsersData        := GetUsersData_1;
    select XMLElement("Root", XMLElement("OrganisationData",

           /* XMLElement("Persons",
                (select XMLAgg(
                   XMLElement("Person",
           --          nvl2(imm.MEMBER_ROLE,XMLElement("ParticipantOperation",imm.MEMBER_ROLE),XMLElement("ParticipantOperation",XMLATTRIBUTES('true' as "xsi:nil"))),
                      XMLElement("FirstName",dbms_xmlgen.CONVERT(firstname, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("SecondName",dbms_xmlgen.CONVERT(lastname, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("MiddleName",dbms_xmlgen.CONVERT(middlename, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("IndividualNumber",dbms_xmlgen.CONVERT(pers_number, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("JobName",dbms_xmlgen.CONVERT(jobname, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("Phone",dbms_xmlgen.CONVERT(phones, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("Email",dbms_xmlgen.CONVERT(emails, dbms_xmlgen.ENTITY_ENCODE)),
                      XMLElement("Certificate",XMLATTRIBUTES(dbms_xmlgen.CONVERT(sertificate_name, dbms_xmlgen.ENTITY_ENCODE) as "Name",sertificate_size as "Size"),
                      pkg_common.toClOb(sertificate))
                     ))
                        from users  u    where
                                               exists (select 1 from user_roles ur where ur.userid = u.userid and
                                                                                         bitand(ur.roleid,4)=4 and
                                                                                         sysdate between ur.datebegin and ur.dateend)
                                               and u.isenable = 1)
                   ),
                  */

           XMLElement("SystemId", systemid), XMLElement("CfmCode", cfmcode), XMLElement("OpfCode", orgcode), XMLElement("OrgName", fullname), XMLElement("ShortName", shortname), XMLElement("RNN", taxcode), XMLElement("IINBIN", maincode), XMLElement("BIK", bankcode), XMLElement("OKED", oked), XMLElement("RegisterDate", to_char(registr_date, 'dd.mm.yyyy')), XMLElement("RegisterNumber", reggistr_number), XMLElement("RegisterAgency", registr_who), xmltype(lAdditionalAcData), XMLElement("PostalIndex", reg_postindex), XMLElement("Area", XMLATTRIBUTES(reg_area_code as "code"), reg_area), XMLElement("District", XMLATTRIBUTES(reg_district_code as "code"), reg_district), XMLElement("City", XMLATTRIBUTES(reg_city_code as "code"), reg_city), XMLElement("Street", XMLATTRIBUTES(reg_street_code as "code"), reg_street), XMLElement("House", XMLATTRIBUTES(reg_house_code as "code"), reg_house), XMLElement("Office", XMLATTRIBUTES(reg_office_code as "code"), reg_office), xmltype(lUsersData), xmltype(lFoundersData)

           ), XMLElement("Version", 'C5ACEF29-0D1D-4B2B-A11F-58BC2C45BD54'), XMLElement("DocumentUniqueIdentifier", sys_guid()))
           .getclobval()
      INTO lRes
      from passport_cfm
     where passportid = 1;

    lXML    := SYS.XMLType.CreateXML(lRes);
    pOUTXML := lXML.getclobval();

    resultValidate := pkg_messverifyprocs.VerifyMessageRegistration(pOUTXML,
                                                                    11,
                                                                    1);

    if length(resultValidate) <> 0 then
      pOutXML := resultValidate;
      return - 900;
    end if;
    return 0;
  end;

  function UpdatePassportData(pXML in clob) return number is
    result number default 0;
  begin
    update passport_cfm
       set SystemId =
           (select extractvalue(xmltype(pXML), '/Root/SystemId/text()')
              from dual)
     where passportid = 1
       and SystemId is null;
    return result;

  exception
    when others then
      return - 2035;
  end;

  function GetCfmId return number is
    result number default null;
  begin
    select systemid into result from passport_cfm where passportid = 1;
    return result;
  end;

end PKG_PASSPORT_CFM;
/
	/*
	****** End update script ******
	*/
