WHENEVER SQLERROR EXIT FAILURE ROLLBACK; 
/

DECLARE
  v_exists INTEGER;
BEGIN

 SELECT count(*) 
    INTO v_exists 
    FROM user_tables 
    WHERE UPPER(Table_Name)='LAYERDATA_REGBK';
    
 IF v_exists=0 THEN

/*  Таблица для хранения данных учётных регистров с измерением по КБК  */

/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/

 EXECUTE IMMEDIATE 'CREATE TABLE LayerData_RegBK 
   (	
    /*системные поля*/
  	ID NUMBER(10,0) NOT NULL, 
	LAYERREF NUMBER(10,0) NOT NULL, 
	LAYERVERSION NUMBER(10,0), 
	FACIALACCCLS NUMBER(10,0), 
  	SENDERID NUMBER(10,0), 
	SENDERRECORDID NUMBER(10,0), 
  	SENDERID_FORCHECK NUMBER(10,0),
	RVERSION NUMBER(10,0), 
	CREATEDATE DATE NOT NULL, 
	CREATERUSER VARCHAR2(30) NOT NULL, 
	UPDATEDATE DATE NOT NULL, 
	UPDATERUSER VARCHAR2(30) NOT NULL, 
	TASKREF NUMBER(10,0), 
  "PRODUCTREF" NUMBER(10,0), 
	OWNERUSER NUMBER(10,0) DEFAULT null NOT NULL, 
	EXECUTERUSER NUMBER(10,0) DEFAULT null NOT NULL, 
	UPDATESTATUS NUMBER(10,0), 
  UPDATEDRECORDINDEX NUMBER(10,0), 
	UPDATEGROUPINDEX NUMBER(10,0), 
  ACCEPTDATE NUMBER, 
	REJECTDATE NUMBER, 
	REJECTCAUSE VARCHAR2(255), 
	NATURALKEY VARCHAR2(255), 
	DOCSTATEREF NUMBER(10,0), 
	CRYPTOSTATUS NUMBER(10,0), 
	LOCKSTATUS NUMBER(2,0), 
	LOCKINFO VARCHAR2(255), 
	DATEOPEN NUMBER, 
	DATECLOSE NUMBER, 
	BUDGETREF NUMBER(10,0),


    /* поля общего назначения */  
    INTFIELD00          NUMBER(10,0),
    INTFIELD01          NUMBER(10,0),
    INTFIELD02          NUMBER(10,0),
    INTFIELD03          NUMBER(10,0),
    INTFIELD04          NUMBER(10,0),
    INTFIELD05          NUMBER(10,0),
    INTFIELD06          NUMBER(10,0),
    INTFIELD07          NUMBER(10,0),
    INTFIELD08          NUMBER(10,0),
    INTFIELD09          NUMBER(10,0),
    INTFIELD10          NUMBER(10,0),

    SUMFIELD00          NUMBER(15,2) default null,
    SUMFIELD01          NUMBER(15,2) default null,
    SUMFIELD02          NUMBER(15,2) default null,
    SUMFIELD03          NUMBER(15,2) default null,
    SUMFIELD04          NUMBER(15,2) default null,
    SUMFIELD05          NUMBER(15,2) default null,
    SUMFIELD06          NUMBER(15,2) default null,
    SUMFIELD07          NUMBER(15,2) default null,
    SUMFIELD08          NUMBER(15,2) default null,
    SUMFIELD09          NUMBER(15,2) default null,
    SUMFIELD10          NUMBER(15,2) default null,
    SUMFIELD11          NUMBER(15,2) default null,
    SUMFIELD12          NUMBER(15,2) default null,
    SUMFIELD13          NUMBER(15,2) default null,
    SUMFIELD14          NUMBER(15,2) default null,
    SUMFIELD15          NUMBER(15,2) default null,

    DATEFIELD00         NUMBER,
    DATEFIELD01         NUMBER,
    DATEFIELD02         NUMBER,
    DATEFIELD03         NUMBER,
    DATEFIELD04         NUMBER,
    
    STRFIELD00          VARCHAR2(1024),
    STRFIELD01          VARCHAR2(1024),
    STRFIELD02          VARCHAR2(1024),
    STRFIELD03          VARCHAR2(1024),
    STRFIELD04          VARCHAR2(1024),

    FLOATFIELD00        NUMBER,
    FLOATFIELD01        NUMBER,
    FLOATFIELD02        NUMBER,
    FLOATFIELD03        NUMBER,
    FLOATFIELD04        NUMBER,
    FLOATFIELD05        NUMBER,

    ORG_REF             NUMBER(10,0),
    
    /* поля регистра */
    ID_KBK              NUMBER(10,0) NOT NULL,
    REG_DATE            NUMBER NOT NULL,
    YEAR_SUM            NUMBER(10,0) NOT NULL,
    YEAR_PLAN           NUMBER(10,0) NOT NULL,
    ID_DOCUMENT         NUMBER(10,0) NOT NULL,

    /*предметные поля*/
    /* бюджетная классификация */
    KVSR                NUMBER(10,0),
    BA                  NUMBER(10,0),
    
    KFSR                NUMBER(10,0) /* Раздел/подраздел NUMBER(10,0) = INTEGER */,
    KCSR                VARCHAR2(20)  /* КЦСР NOTE20 = VARCHAR(20) */,
    KVR                 NUMBER(10,0) /* КВР NUMBER(10,0) = INTEGER */,
    KESR                NUMBER(10,0) /* КОСГУ NUMBER(10,0) = INTEGER */,
    SUBKESR             NUMBER(10,0) /* СубКОСГУ NUMBER(10,0) = INTEGER */,
    MEANSTYPE           NUMBER(10,0) /* тип средств NUMBER(10,0) = INTEGER */,
    FACT                VARCHAR2(20)  /* мероприятие NOTE20 = VARCHAR(20) */,
    DIRECTION           VARCHAR2(20)  /* код направления NOTE20 = VARCHAR(20) */,
    SUBSIDY             NUMBER(10,0) /* код субсидии NUMBER(10,0) = INTEGER */,
    CS                  NUMBER(10,0) /* целевые средства NUMBER(10,0) = INTEGER */,

    REGION              NUMBER(10,0) /* код района NUMBER(10,0) = INTEGER */,
    DEPARTMENT          NUMBER(10,0) /* код отдела NUMBER(10,0) = INTEGER */,
    OKPD                NUMBER(10,0) /* ОКПД NUMBER(10,0) = INTEGER */,
    
    
    
    CONSTRAINT "PK_LD_REGBK" PRIMARY KEY ("ID"),
    
   CHECK ((ID<=2147483647)AND(ID>-2147483648)) ENABLE, 
   CHECK ((LayerRef<=2147483647)AND(LayerRef>-2147483648)) ENABLE, 
	 CHECK ((LayerVersion<=2147483647)AND(LayerVersion>-2147483648)) ENABLE, 
   CHECK ((CryptoStatus<=2147483647)AND(CryptoStatus>-2147483648)) ENABLE, 
	 CHECK ((DocStateRef<=2147483647)AND(DocStateRef>-2147483648)) ENABLE, 
	 CHECK ((ExecuterUser<=2147483647)AND(ExecuterUser>-2147483648)) ENABLE, 
	 CHECK ((FacialAccCls<=2147483647)AND(FacialAccCls>-2147483648)) ENABLE,
   CHECK ((OwnerUser<=2147483647)AND(OwnerUser>-2147483648)) ENABLE, 
   CHECK ((TASKREF<=2147483647)AND(TASKREF>-2147483648)) ENABLE, 
	 CHECK ((ProductRef<=2147483647)AND(ProductRef>-2147483648)) ENABLE,
   CHECK ((Rversion<=2147483647)AND(Rversion>-2147483648)) ENABLE, 
	 CHECK ((SenderID<=2147483647)AND(SenderID>-2147483648)) ENABLE, 
	 CHECK ((SenderRecordID<=2147483647)AND(SenderRecordID>-2147483648)) ENABLE, 
   CHECK ((UpdatedRecordIndex<=2147483647) and (UpdatedRecordIndex>=-2147483648)) ENABLE, 
	 CHECK ((UpdateGroupIndex<=2147483647) and (UpdateGroupIndex>=-2147483648)) ENABLE, 
	 CHECK ((UpdateStatus<=2147483647) and (UpdateStatus>=-2147483648)) ENABLE, 
   CHECK ((SENDERID_FORCHECK<=2147483647) and (SENDERID_FORCHECK>=-2147483648)) ENABLE, 
	 CHECK ((BUDGETREF<=2147483647)AND(BUDGETREF>-2147483648)) ENABLE, 
   	 
	 CHECK ((IntField00<=2147483647)AND(IntField00>-2147483648)) ENABLE, 
	 CHECK ((IntField01<=2147483647)AND(IntField01>-2147483648)) ENABLE, 
	 CHECK ((IntField02<=2147483647)AND(IntField02>-2147483648)) ENABLE, 
	 CHECK ((IntField03<=2147483647)AND(IntField03>-2147483648)) ENABLE, 
	 CHECK ((IntField04<=2147483647)AND(IntField04>-2147483648)) ENABLE, 
	 CHECK ((IntField05<=2147483647)AND(IntField05>-2147483648)) ENABLE, 
	 CHECK ((IntField06<=2147483647)AND(IntField06>-2147483648)) ENABLE, 
	 CHECK ((IntField07<=2147483647)AND(IntField07>-2147483648)) ENABLE, 
	 CHECK ((IntField08<=2147483647)AND(IntField08>-2147483648)) ENABLE, 
	 CHECK ((IntField09<=2147483647)AND(IntField09>-2147483648)) ENABLE, 
	 CHECK ((IntField10<=2147483647)AND(IntField10>-2147483648)) ENABLE, 
   
   CHECK ((Org_Ref<=2147483647)AND(Org_Ref>-2147483648)) ENABLE,
   
	 CHECK ((KVSR<=2147483647)AND(KVSR>-2147483648)) ENABLE, 
   CHECK ((BA<=2147483647)AND(BA>-2147483648)) ENABLE, 
	 CHECK ((KFSR<=2147483647)AND(KFSR>-2147483648)) ENABLE, 
	 CHECK ((KVR<=2147483647)AND(KVR>-2147483648)) ENABLE, 
   CHECK ((KESR<=2147483647)AND(KESR>-2147483648)) ENABLE, 
	 CHECK ((SubKESR<=2147483647)AND(SubKESR>-2147483648)) ENABLE, 	 
	 CHECK ((MeansType<=2147483647)AND(MeansType>-2147483648)) ENABLE, 
	 CHECK ((SUBSIDY<=2147483647)AND(SUBSIDY>-2147483648)) ENABLE, 
   CHECK ((CS<=2147483647)AND(CS>-2147483648)) ENABLE, 
	 CHECK ((REGION<=2147483647)AND(REGION>-2147483648)) ENABLE, 
   CHECK ((DEPARTMENT<=2147483647)AND(DEPARTMENT>-2147483648)) ENABLE, 
	 CHECK ((OKPD<=2147483647)AND(OKPD>-2147483648)) ENABLE,
   
   CONSTRAINT "FK_LD_RegBK_PRODUCTREF" FOREIGN KEY ("PRODUCTREF")
	  REFERENCES "TENDEROBJECTS" ("ID") ENABLE, 
	 CONSTRAINT "FK_LD_RegBK_TASKREF" FOREIGN KEY ("TASKREF")
	  REFERENCES "LAYERTASKS" ("ID") ENABLE, 
	 CONSTRAINT "FK_LD_RegBK_EXECUTER" FOREIGN KEY ("EXECUTERUSER")
	  REFERENCES "TB_DBUSERS" ("ID") ENABLE, 
	 CONSTRAINT "FK_LD_RegBK_OWNER" FOREIGN KEY ("OWNERUSER")
	  REFERENCES "TB_DBUSERS" ("ID") ENABLE, 
	 CONSTRAINT "FK_LD_RegBK_LAYERREF" FOREIGN KEY ("LAYERREF")
	  REFERENCES "LAYERS" ("ID") ENABLE, 
	 CONSTRAINT "FK_LD_RegBK_FACIALACCCLS" FOREIGN KEY ("FACIALACCCLS")
	  REFERENCES "TB_FACIALACC_CLS" ("ID") ENABLE   
)';

/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

 EXECUTE IMMEDIATE 'CREATE INDEX "FK_LD_RegBK_BUDGETREF" ON LAYERDATA_RegBK ("BUDGETREF")'; 
 EXECUTE IMMEDIATE 'CREATE INDEX "FK_LD_RegBK_LAYER" ON LAYERDATA_RegBK (LAYERREF, LAYERVERSION)';
/* Индексы регистра */
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_ID_KBK ON LAYERDATA_RegBK (ID_KBK)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_REG_DATE ON LAYERDATA_RegBK (REG_DATE)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_REG_STATE ON LAYERDATA_RegBK (DOCSTATEREF)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_YEAR_SUM ON LAYERDATA_RegBK (YEAR_SUM)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_YEAR_PLAN ON LAYERDATA_RegBK (YEAR_PLAN)';
/* Индексы КБК */
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_KVSR ON LAYERDATA_RegBK (KVSR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_BA ON LAYERDATA_RegBK (BA)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_KFSR ON LAYERDATA_RegBK (KFSR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_KCSR ON LAYERDATA_RegBK (KCSR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_KVR ON LAYERDATA_RegBK (KVR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_KESR ON LAYERDATA_RegBK (KESR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_SUBKESR ON LAYERDATA_RegBK (SUBKESR)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_MEANSTYPE ON LAYERDATA_RegBK (MEANSTYPE)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_FACT ON LAYERDATA_RegBK (FACT)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_DIRECTION ON LAYERDATA_RegBK (DIRECTION)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_REGIONCLS ON LAYERDATA_RegBK (REGION)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_SUBSIDYCLS ON LAYERDATA_RegBK (SUBSIDY)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_CS ON LAYERDATA_RegBK (CS)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_RegBK_OKPD ON LAYERDATA_RegBK (OKPD)';


 COMMIT WORK;
 
 END IF;

END;

/




CREATE OR REPLACE TRIGGER "TR_LD_RegBK_AD" 
  AFTER DELETE
  ON LayerData_RegBK
  FOR EACH ROW
begin

-- - - - - - - - - - - - -
-- AD_500
-- Create 05.09.2005
-- - - - - - - - - - - - -

    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (174, :old.ID, SYSDATE);
    
end;
/
ALTER TRIGGER "TR_LD_RegBK_AD" ENABLE;

  CREATE OR REPLACE TRIGGER "TR_LD_RegBK_AI" 
  AFTER INSERT
  ON LayerData_RegBK
  FOR EACH ROW
begin

-- - - - - - - - - - - - -
-- AI_500
-- Create 05.09.2005
-- - - - - - - - - - - - -

  delete from DelRecIndexes where (TableID = 174) and (RecordID = :new.ID);
  
end;
/
ALTER TRIGGER "TR_LD_RegBK_AI" ENABLE;

  CREATE OR REPLACE TRIGGER "TR_LD_RegBK_AU" 
  AFTER UPDATE
  ON LayerData_RegBK
  FOR EACH ROW
begin

-- - - - - - - - - - - - -
-- AU_500
-- Create 05.09.2005
-- - - - - - - - - - - - -

  if (:new.ID<>:old.ID) then
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (174, :old.ID, SYSDATE);
    delete from DelRecIndexes where (TableID=174) and (RecordId=:new.ID);
  end if;
  
end;
/
ALTER TRIGGER "TR_LD_RegBK_AU" ENABLE;

  CREATE OR REPLACE TRIGGER "TR_LD_RegBK_BI" 
  BEFORE INSERT
  ON LayerData_RegBK
  FOR EACH ROW
declare
  fac NUMBER(10);
begin

-- - - - - - - - - - - - -
-- BI_(0)
-- Create 05.09.2005
-- Last Update 22.05.2009
-- - - - - - - - - - - - -

    if (:new.id is null) then select g_Shared.nextval into :new.Id from DUAL; end if;
    if (:new.LayerVersion is null) then :new.LayerVersion := 0; end if;
    if (:new.ExecuterUser is null) then :new.ExecuterUser := :new.OwnerUser; end if;
    if ((:new.FacialAccCls is null) and (:new.TaskRef is not null)) then
      BEGIN
        select FacialAccCls into fac from LayerTasks where ID = :new.TaskRef;
        :new.FacialAccCls := fac;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN NULL;
      END;
    end if;
    if (:new.TaskRef is not null) then
      BEGIN
        select LayerVersion, DocStateRef, AcceptDate, RejectDate, OwnerUser, ExecuterUser
          into :new.LayerVersion, :new.DocStateRef, :new.AcceptDate, :new.RejectDate,
          :new.OwnerUser, :new.ExecuterUser
        from LayerTasks where ID = :new.TaskRef;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          :new.LayerVersion := NULL;
          :new.DocStateRef := NULL;
          :new.AcceptDate := NULL;
          :new.RejectDate := NULL;
          :new.OwnerUser := NULL;
          :new.ExecuterUser := NULL;
      END;  
    end if;
    
-- - - - - - - - - - - - -
-- BI_500
-- Create 05.09.2005
-- - - - - - - - - - - - -
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
end;
/
ALTER TRIGGER "TR_LD_RegBK_BI" ENABLE;

  CREATE OR REPLACE TRIGGER "TR_LD_RegBK_BU" 
  BEFORE UPDATE
  ON LayerData_RegBK
  FOR EACH ROW
begin

-- - - - - - - - - - - - -
-- BU_(0)
-- Create 05.09.2005
-- - - - - - - - - - - - -

    if (:new.LayerVersion is null) then :new.LayerVersion := 0; end if;
    if (:new.ExecuterUser is null) then :new.ExecuterUser := :new.OwnerUser; end if;
    
-- - - - - - - - - - - - -
-- BU_700
-- Create 05.09.2005
-- - - - - - - - - - - - -

  if (:new.RVersion is null) then
    :new.RVersion := 1;
  elsif ((:new.RVersion = -1) and (:old.RVersion <> -1)) then
    :new.RVersion := :old.RVersion;
  else
    :new.RVersion := :old.RVersion+1;
  end if;

end;
/
ALTER TRIGGER "TR_LD_RegBK_BU" ENABLE;

  CREATE OR REPLACE TRIGGER "TR_LD_RegBK_BU_500" 
  BEFORE UPDATE
  ON LayerData_RegBK
  FOR EACH ROW
begin
-- - - - - - - - - - - - -
-- BU_500
-- - - - - - - - - - - - -
    :new.UpdateDate := SYSDATE;
    :new.UpdaterUser := User;
end;
/
ALTER TRIGGER "TR_LD_RegBK_BU_500" ENABLE;




/* Privileges of users */
GRANT ALL ON LAYERDATA_RegBK TO PUBLIC;

/

COMMIT WORK;

