WHENEVER SQLERROR EXIT FAILURE ROLLBACK; 
/

DECLARE
  v_exists INTEGER;
BEGIN

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

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

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

 EXECUTE IMMEDIATE 'CREATE TABLE LayerData_Backup 
   (	
    /*системные поля*/
  	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), 
	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),
  "PRODUCTREF" NUMBER(10,0),   


    /* поля общего назначения */ 
    KFSR                NUMBER(10,0),
    KVSR                NUMBER(10,0),
    KESR                NUMBER(10,0),
    KVR                 NUMBER(10,0),
    SUBKESR             NUMBER(10,0),
    FINSOURCE           NUMBER(10,0),
    FACT                NUMBER(10,0),
    DIRECTIONCLS        NUMBER(10,0),
    MEANSTYPE           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),
    INTFIELD11          NUMBER(10,0),
    INTFIELD12          NUMBER(10,0),
    INTFIELD13          NUMBER(10,0),
    INTFIELD14          NUMBER(10,0),
    INTFIELD15          NUMBER(10,0),
    INTFIELD16          NUMBER(10,0),
    INTFIELD17          NUMBER(10,0),
    INTFIELD18          NUMBER(10,0),
    INTFIELD19          NUMBER(10,0),
    INTFIELD20          NUMBER(10,0),
    INTFIELD21          NUMBER(10,0),
    INTFIELD22          NUMBER(10,0),
    INTFIELD23          NUMBER(10,0),
    INTFIELD24          NUMBER(10,0),
    INTFIELD25          NUMBER(10,0),
    INTFIELD26          NUMBER(10,0),
    INTFIELD27          NUMBER(10,0),
    INTFIELD28          NUMBER(10,0),
    INTFIELD29          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,
    SUMFIELD16          NUMBER(15,2) default null,
    SUMFIELD17          NUMBER(15,2) default null,
    SUMFIELD18          NUMBER(15,2) default null,
    SUMFIELD19          NUMBER(15,2) default null,
    SUMFIELD20          NUMBER(15,2) default null,
    SUMFIELD21          NUMBER(15,2) default null,
    SUMFIELD22          NUMBER(15,2) default null,
    SUMFIELD23          NUMBER(15,2) default null,
    SUMFIELD24          NUMBER(15,2) default null,
    SUMFIELD25          NUMBER(15,2) default null,
    SUMFIELD26          NUMBER(15,2) default null,
    SUMFIELD27          NUMBER(15,2) default null,
    SUMFIELD28          NUMBER(15,2) default null,
    SUMFIELD29          NUMBER(15,2) default null,
    SUMFIELD30          NUMBER(15,2) default null,
    SUMFIELD31          NUMBER(15,2) default null,
    SUMFIELD32          NUMBER(15,2) default null,
    SUMFIELD33          NUMBER(15,2) default null,
    SUMFIELD34          NUMBER(15,2) default null,
    SUMFIELD35          NUMBER(15,2) default null,
    SUMFIELD36          NUMBER(15,2) default null,
    SUMFIELD37          NUMBER(15,2) default null,
    SUMFIELD38          NUMBER(15,2) default null,
    SUMFIELD39          NUMBER(15,2) default null,
    SUMFIELD40          NUMBER(15,2) default null,
    SUMFIELD41          NUMBER(15,2) default null,
    SUMFIELD42          NUMBER(15,2) default null,
    SUMFIELD43          NUMBER(15,2) default null,
    SUMFIELD44          NUMBER(15,2) default null,
    SUMFIELD45          NUMBER(15,2) default null,
    DATEFIELD00         NUMBER,
    DATEFIELD01         NUMBER,
    DATEFIELD02         NUMBER,
    DATEFIELD03         NUMBER,
    DATEFIELD04         NUMBER,
    DATEFIELD05         NUMBER,
    DATEFIELD06         NUMBER,
    DATEFIELD07         NUMBER,
    DATEFIELD08         NUMBER,
    DATEFIELD09         NUMBER,
    DATEFIELD10         NUMBER,
    DATEFIELD11         NUMBER,
    DATEFIELD12         NUMBER,
    DATEFIELD13         NUMBER,
    DATEFIELD14         NUMBER,
    STRFIELD00          VARCHAR(1024),
    STRFIELD01          VARCHAR(1024),
    STRFIELD02          VARCHAR(1024),
    STRFIELD03          VARCHAR(1024),
    STRFIELD04          VARCHAR(1024),
    STRFIELD05          VARCHAR(1024),
    STRFIELD06          VARCHAR(1024),
    STRFIELD07          VARCHAR(1024),
    STRFIELD08          VARCHAR(1024),
    STRFIELD09          VARCHAR(1024),
    STRFIELD10          VARCHAR(1024),
    STRFIELD11          VARCHAR(1024),
    STRFIELD12          VARCHAR(1024),
    STRFIELD13          VARCHAR(1024),
    STRFIELD14          VARCHAR(1024),
    STRFIELD15          VARCHAR(1024),
    STRFIELD16          VARCHAR(1024),
    STRFIELD17          VARCHAR(1024),
    STRFIELD18          VARCHAR(1024),
    STRFIELD19          VARCHAR(1024),
    STRFIELD20          VARCHAR(1024),
    STRFIELD21          VARCHAR(1024),
    STRFIELD22          VARCHAR(1024),
    STRFIELD23          VARCHAR(1024),
    STRFIELD24          VARCHAR(1024),
    STRFIELD25          VARCHAR(1024),
    STRFIELD26          VARCHAR(1024),
    STRFIELD27          VARCHAR(1024),
    STRFIELD28          VARCHAR(1024),
    STRFIELD29          VARCHAR(1024),
    FLOATFIELD00        NUMBER,
    FLOATFIELD01        NUMBER,
    FLOATFIELD02        NUMBER,
    FLOATFIELD03        NUMBER,
    FLOATFIELD04        NUMBER,
    FLOATFIELD05        NUMBER,
    FLOATFIELD06        NUMBER,
    FLOATFIELD07        NUMBER,
    FLOATFIELD08        NUMBER,
    FLOATFIELD09        NUMBER,
    FLOATFIELD10        NUMBER,
    DEPARTMENTCLS       NUMBER(10,0),
    VARIANT             NUMBER(10,0),
    SUBSIDYCLS          NUMBER(10,0),
    TRANSFERTCLS        NUMBER(10,0),
    REGIONCLS           NUMBER(10,0),
    ORG_REF             NUMBER(10,0),
    KCSR                VARCHAR(20),
    
    /*Предметные поля*/
    DOCUMENTID          NUMBER(10,0),
    BACKUPDATE          DATE,
    SOURCEID            NUMBER(10,0),
        
    
    CONSTRAINT "PK_LD_BK" 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 ((IntField11<=2147483647)AND(IntField11>-2147483648)) ENABLE, 
	 CHECK ((IntField12<=2147483647)AND(IntField12>-2147483648)) ENABLE, 
	 CHECK ((IntField13<=2147483647)AND(IntField13>-2147483648)) ENABLE, 
	 CHECK ((IntField14<=2147483647)AND(IntField14>-2147483648)) ENABLE, 
	 CHECK ((IntField15<=2147483647)AND(IntField15>-2147483648)) ENABLE, 
	 CHECK ((IntField16<=2147483647)AND(IntField16>-2147483648)) ENABLE, 
	 CHECK ((IntField17<=2147483647)AND(IntField17>-2147483648)) ENABLE, 
	 CHECK ((IntField18<=2147483647)AND(IntField18>-2147483648)) ENABLE, 
	 CHECK ((IntField19<=2147483647)AND(IntField19>-2147483648)) ENABLE, 
	 CHECK ((IntField20<=2147483647)AND(IntField20>-2147483648)) ENABLE,
	 CHECK ((IntField21<=2147483647)AND(IntField21>-2147483648)) ENABLE, 
	 CHECK ((IntField22<=2147483647)AND(IntField22>-2147483648)) ENABLE, 
	 CHECK ((IntField23<=2147483647)AND(IntField23>-2147483648)) ENABLE, 
	 CHECK ((IntField24<=2147483647)AND(IntField24>-2147483648)) ENABLE, 
	 CHECK ((IntField25<=2147483647)AND(IntField25>-2147483648)) ENABLE, 
	 CHECK ((IntField26<=2147483647)AND(IntField26>-2147483648)) ENABLE, 
	 CHECK ((IntField27<=2147483647)AND(IntField27>-2147483648)) ENABLE, 
	 CHECK ((IntField28<=2147483647)AND(IntField28>-2147483648)) ENABLE, 
	 CHECK ((IntField29<=2147483647)AND(IntField29>-2147483648)) ENABLE, 
   
   CHECK ((Org_Ref<=2147483647)AND(Org_Ref>-2147483648)) ENABLE,
   
	 CHECK ((KVSR<=2147483647)AND(KVSR>-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 ((SUBSIDYCLS<=2147483647)AND(SUBSIDYCLS>-2147483648)) ENABLE, 
	 CHECK ((REGIONCLS<=2147483647)AND(REGIONCLS>-2147483648)) ENABLE, 
   CHECK ((DEPARTMENTCLS<=2147483647)AND(DEPARTMENTCLS>-2147483648)) ENABLE
)';

 --EXECUTE IMMEDIATE 'CREATE SEQUENCE  G_LD_BACKUP  MINVALUE 1 MAXVALUE 999999999999999999999999999';
 EXECUTE IMMEDIATE 'INSERT INTO GENERATORS (NAME,Value) VALUES (''G_LD_BACKUP'',1)';


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

 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_BK_LAYERREF ON LAYERDATA_BACKUP(LAYERREF)';
 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_BK_LAYER ON LAYERDATA_BACKUP(LAYERREF,DOCUMENTID)';

/* Предметные индексы */

 EXECUTE IMMEDIATE 'CREATE INDEX IDX_LD_BK_DOCID ON LAYERDATA_BACKUP(DOCUMENTID)';


 COMMIT WORK;
 
 END IF;

END;

/

CREATE OR REPLACE TRIGGER "TR_LD_BK_BI" 
  BEFORE INSERT
  ON LayerData_Backup
  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 
      :new.id := GEN_ID('G_LD_BACKUP',1);
    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;
   
-- - - - - - - - - - - - -
-- BI_500
-- Create 05.09.2005
-- - - - - - - - - - - - -
  if(:NEW.CreateDate is NULL) then :NEW.CreateDate := SYSDATE; end if;
  if(:NEW.UpdateDate is NULL) then :NEW.UpdateDate := :NEW.CreateDate; end if;
  if(:NEW.CreaterUser is NULL) then :NEW.CreaterUser := USER; end if;
  if(:NEW.UpdaterUser is NULL) then :NEW.UpdaterUser := USER; end if;
end;
/
ALTER TRIGGER "TR_LD_BK_BI" ENABLE;


CREATE OR REPLACE TRIGGER "TR_LD_BK_BU" 
  BEFORE UPDATE
  ON LayerData_Backup
  FOR EACH ROW
begin
    if (:new.LayerVersion is null) then :new.LayerVersion := 0; end if;
    if (:new.ExecuterUser is null) then :new.ExecuterUser := :new.OwnerUser; end if;
    :new.UpdateDate := SYSDATE;
    :new.UpdaterUser := User;
end;
/
ALTER TRIGGER "TR_LD_BK_BU" ENABLE;



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

/

COMMIT WORK;

