--[VK 20130205 Defect #12101 - JS Error "Exception thrown and not caught" occurs when order batches by Last User/Module. (WVC)] - Update any previously set sorting setting to a new value
--[RO][2013-03-20][ALM #12186 - nclob-data should be converted to varchar. "DBMS_LOB.SUBSTR(VALUE, 19)- returns 19 characters of a NCLOB, starting from the beginning of the VALUE-field. "]
DECLARE
	v_count NUMBER := 0;
BEGIN
	select COUNT(*) into v_count from Setting s inner join SettingType st on s.SettingsTypeId=st.Id where st.Name='AppBatchSortingColumn' and DBMS_LOB.SUBSTR(VALUE, 19)='LastUser,LastModule';
	if v_count>0 then
		update Setting
		set Value='LastAccessedUser'
		where DBMS_LOB.SUBSTR(VALUE, 19)='LastUser,LastModule' and SettingsTypeId=(select Id from SettingType where Name='AppBatchSortingColumn');
	end if;
END;
/

DECLARE
	v_count NUMBER := 0;
BEGIN
	select COUNT(*) into v_count from SettingType where Name='SysAppAllowDocumentViewPageReordering';
	if v_count=0 then
		INSERT INTO SettingType(Name, Type) VALUES('SysAppAllowDocumentViewPageReordering', 3);
	end if;
END;
/

DECLARE
	v_count NUMBER := 0;
BEGIN
	select count(Id) into v_count from SettingType where Name='AppSplitterPosition';
	if (v_count > 0) then
		update SettingType set Name='AppIndexingSplitterPosition' where Name='AppSplitterPosition';
	end if;
	select count(Id) into v_count from SettingType where Name='AppIndexingSplitterPosition';
	if (v_count = 0) then
		insert into SettingType(Name, Type) values('AppIndexingSplitterPosition', 1);
	end if;
  select count(Id) into v_count from SettingType where Name='AppIndexingSplitterPosition' and Type=4;
  if(v_count > 0) then
    update SettingType set Type=1 where Name='AppIndexingSplitterPosition';
  end if;
	select count(Id) into v_count from SettingType where Name='AppDocumentSplitterPosition';
	if (v_count = 0) then
		insert into SettingType(Name, Type) values('AppDocumentSplitterPosition', 1);
	end if;
END;
/

DECLARE
	adminRoleId NUMBER := 0;
	v_count NUMBER := 0;
BEGIN
	select Id into adminRoleId from Role where Name='Administrator';
	select COUNT(*) into v_count from RoleToUserRel where RoleId=adminRoleId and UserId=1;
	if v_count=0 then
		insert into RoleToUserRel(RoleId, UserId) values(adminRoleId, 1);
	end if;
	select COUNT(*) into v_count from RoleToUserRel where RoleId=adminRoleId and UserId=3;
	if v_count=0 then
		insert into RoleToUserRel(RoleId, UserId) values(adminRoleId, 3);
	end if;
END;
/


--[OR 2011-07-28] Filtering role with correct role ID
DECLARE
  v_cnt NUMBER;
  v_wrongRoleId NUMBER;
  CURSOR c_wrongRoleId IS SELECT ID FROM ROLE WHERE NAME = 'Verifier Filtering' and id <> 6;
BEGIN
    v_wrongRoleId := 0;
    INSERT INTO ROLE (NAME, DESCRIPTION )  VALUES ('Verifier Filtering', 'Allows Verifier user to apply filtering for the batch list view' );
    SELECT count (ID) INTO v_cnt FROM ROLE WHERE NAME = 'Verifier Filtering' AND ID = 6;
    IF v_cnt = 0 THEN 
      INSERT INTO ROLE (ID, NAME, DESCRIPTION )  VALUES (6, 'Verifier Filtering', 'Allows Verifier user to apply filtering for the batch list view' );
    END IF;
    OPEN c_wrongRoleId;
    loop 
      FETCH c_wrongRoleId into v_wrongRoleId;
      exit WHEN c_wrongRoleId%NOTFOUND;
        BEGIN          
          UPDATE RoleToGroupRel
          SET RoleId = 6
          WHERE RoleId = v_wrongRoleId;        
          UPDATE RoleToUserRel
          SET RoleId = 6
          WHERE RoleId = v_wrongRoleId;
          DELETE ROLE WHERE NAME = 'Verifier Filtering' AND ID = v_wrongRoleId;
        END;
    END loop;
    CLOSE c_wrongRoleId;
    COMMIT;
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
END;
/

-- [RO][10.02.2012][ORA-02291: integrity constraint (TargetDBSchemaName.FK_SETTING_SETTINGCONFIGURATIO) violated]
declare
  RTSCONFIGURATIONID number := 0; 
  RTSSETTINGSTYPEID NUMBER := 0;
  v_count NUMBER := 0;
begin
  select COUNT(*) into V_COUNT from SETTINGCONFIGURATION where CREATIONDATE=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') and DEFAULT_ = 1;
  if v_count=0 then
    INSERT INTO SettingConfiguration(CreationDate, Default_)	VALUES (TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS'), 1);
    RTSCONFIGURATIONID := SQLSERVER_UTILITIES.identity;
  else
    select id into RTSCONFIGURATIONID from SettingConfiguration where CreationDate=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') AND Default_ = 1;
  end if;
  select COUNT(*) into v_count from UserToSettingConfigurationRel where UserId=1;
  if V_COUNT=0 then
    insert into USERTOSETTINGCONFIGURATIONREL(USERID, SETTINGSCONFIGURATIONID) values(1, RTSCONFIGURATIONID);
  end if;
  select COUNT(*) into v_count from SettingType where Name='SysAppUrlSignatureExpirationPeriod';
  if v_count=0 then
    insert into SETTINGTYPE(name, type) values('SysAppUrlSignatureExpirationPeriod', 4);
    rtsSettingsTypeId := sqlserver_utilities.identity;
  else  
    select id into RTSSETTINGSTYPEID from SETTINGTYPE ST where ST.name='SysAppUrlSignatureExpirationPeriod' and ST.type = 4;
  end if;
  insert into SETTING(SETTINGSCONFIGURATIONID, SETTINGSTYPEID, value) values(RTSCONFIGURATIONID, RTSSETTINGSTYPEID, '300');
end;
/

-- [RO][10.02.2012][ORA-02291: integrity constraint (TargetDBSchemaName.FK_SETTING_SETTINGCONFIGURATIO) violated]
declare
  RTSCONFIGURATIONID number := 0;
  RTSSETTINGSTYPEID NUMBER := 0;
  v_count NUMBER := 0;
begin
  select COUNT(*) into V_COUNT from SETTINGCONFIGURATION where CREATIONDATE=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') and DEFAULT_ = 1;
  if v_count=0 then
    INSERT INTO SettingConfiguration(CreationDate, Default_)	VALUES (TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS'), 1);
    RTSCONFIGURATIONID := SQLSERVER_UTILITIES.identity;
  else
    select id into RTSCONFIGURATIONID from SettingConfiguration where CreationDate=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') AND Default_ = 1;
  end if;
  select COUNT(*) into v_count from UserToSettingConfigurationRel where UserId=1;
  if V_COUNT=0 then
    insert into USERTOSETTINGCONFIGURATIONREL(USERID, SETTINGSCONFIGURATIONID) values(1, RTSCONFIGURATIONID);
  end if;
  select COUNT(*) into v_count from SettingType where Name='SysAppMaxDocumentImageResolution';
  if v_count=0 then
    insert into SETTINGTYPE(name, type) values('SysAppMaxDocumentImageResolution', 4);
    rtsSettingsTypeId := sqlserver_utilities.identity;
  else  
    select id into RTSSETTINGSTYPEID from SETTINGTYPE ST where ST.name='SysAppMaxDocumentImageResolution' and ST.type = 4;
  end if;  
  INSERT INTO Setting(SettingsConfigurationId, SettingsTypeId, Value) VALUES(rtsConfigurationId, rtsSettingsTypeId, '3072');
end;
/


declare
  RTSCONFIGURATIONID number := 0;
  RTSSETTINGSTYPEID NUMBER := 0;
  v_count NUMBER := 0;
begin
  select COUNT(*) into V_COUNT from SETTINGCONFIGURATION where CREATIONDATE=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') and DEFAULT_ = 1;
  if v_count=0 then
    INSERT INTO SettingConfiguration(CreationDate, Default_)	VALUES (TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS'), 1);
    RTSCONFIGURATIONID := SQLSERVER_UTILITIES.identity;
  else
    select id into RTSCONFIGURATIONID from SettingConfiguration where CreationDate=TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') AND Default_ = 1;
  end if;
  select COUNT(*) into v_count from UserToSettingConfigurationRel where UserId=1;
  if V_COUNT=0 then
    insert into USERTOSETTINGCONFIGURATIONREL(USERID, SETTINGSCONFIGURATIONID) values(1, RTSCONFIGURATIONID);
  end if;
  select COUNT(*) into v_count from SettingType where Name='SysAppImageCompressionLevel';
  if v_count=0 then
    insert into SETTINGTYPE(name, type) values('SysAppImageCompressionLevel', 4);
    rtsSettingsTypeId := sqlserver_utilities.identity;
  else  
    select id into RTSSETTINGSTYPEID from SETTINGTYPE ST where ST.name='SysAppImageCompressionLevel' and ST.type = 4;
  end if;  
  INSERT INTO Setting(SettingsConfigurationId, SettingsTypeId, Value) VALUES(rtsConfigurationId, rtsSettingsTypeId, '60');
end;
/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_UNIQUE_DOCUMENTID' and TABLE_NAME='LOCKERTOBATCHREL' and TABLE_OWNER='TargetDBSchemaName';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_UNIQUE_DOCUMENTID ON LOCKERTOBATCHREL (DOCUMENTID ASC)';
END IF;
END;
/

--------------------------- begin [EB 2010/11/01 create BDD_ExternalBatch table] ----------------------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='BDD_EXTERNALBATCH' AND owner='TargetDBSchemaName';
IF v_count = 0 THEN
EXECUTE IMMEDIATE '
  CREATE TABLE "BDD_EXTERNALBATCH" 
  (	
    "ID" NUMBER(10,0) NOT NULL, 
    "NAME" NVARCHAR2(64) NOT NULL
  )
';
EXECUTE IMMEDIATE '
  ALTER TABLE "BDD_EXTERNALBATCH" ADD CONSTRAINT "PK_BDD_EXTERNALBATCH" PRIMARY KEY ("ID") ENABLE  
';
EXECUTE IMMEDIATE '
  CREATE SEQUENCE  "BDD_EXTERNALBATCH_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  
';
END IF;
END;
/
CREATE OR REPLACE TRIGGER "BDD_EXTERNALBATCH_ID_TRG" BEFORE INSERT OR UPDATE ON BDD_EXTERNALBATCH  FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_maxNumber NUMBER(12) := 0;
BEGIN
  IF INSERTING  THEN
    IF :new.Id IS NULL  THEN
          SELECT "BDD_EXTERNALBATCH_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL; 
                        ELSE
    SELECT  NVL(MAX(Id),0) INTO v_maxNumber FROM BDD_EXTERNALBATCH;  -- NVL used to load 0 instead of NULL
      IF  v_maxNumber < :new.Id   THEN    -- readjust Sequence (i.e., if a Row was inserted with an ID not NULL and > Max(ID) + 1 )
      LOOP
                EXIT WHEN v_newVal >= :new.Id;
                SELECT "BDD_EXTERNALBATCH_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL;
      END LOOP;
                                  ELSE   -- no action on Sequence  
           v_newVal := :new.Id;  -- assign value
    END IF;
    END IF;   -- of Id IS NULL OR NOT NULL
       sqlserver_utilities.identity := v_newVal;   -- save this to emulate @@identity 
       :new.Id := v_newVal;   -- assign this to emulate the identity column
  END IF;  -- of INSERTING
 END;    -- of Trigger
/
ALTER TRIGGER "BDD_EXTERNALBATCH_ID_TRG" ENABLE;
------------------------------- end [EB 2010/11/01 create BDD_ExternalBatch table] --------------------------------
--------------------------- begin [EB 2010/11/03 create BDD_UserAudit table] --------------------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='BDD_USERAUDIT' AND owner='TargetDBSchemaName';
IF v_count = 0 THEN
EXECUTE IMMEDIATE '
  CREATE TABLE "BDD_USERAUDIT" 
  (	
  "ID" NUMBER(20,0) NOT NULL, 
  "USERID" NUMBER(10,0) NOT NULL,
  "MACHINENAME" VARCHAR(32) NOT NULL,
  "CLIENTIP" VARCHAR(15) NOT NULL,
  "BATCHID" NUMBER(10,0) NULL,
  "TRANSACTIONID" NUMBER(10,0) NULL,
  "EXTERNALBATCHID" NUMBER(10,0) NULL,
  "ACCESSTIME" DATE NOT NULL,
	"ACCESSTYPEID" NUMBER(10,0) NOT NULL
  )
';
EXECUTE IMMEDIATE '
  ALTER TABLE "BDD_USERAUDIT" ADD CONSTRAINT "PK_BDD_USERAUDIT" PRIMARY KEY ("ID") ENABLE
';
EXECUTE IMMEDIATE '
  CREATE SEQUENCE  "BDD_USERAUDIT_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE
';
EXECUTE IMMEDIATE '
  ALTER TABLE "BDD_USERAUDIT" ADD CONSTRAINT "FK_BDD_USERAUDIT_BDD_EXTERNALB" FOREIGN KEY ("EXTERNALBATCHID")
	  REFERENCES "BDD_EXTERNALBATCH" ("ID") ENABLE
';
EXECUTE IMMEDIATE '
  ALTER TABLE "BDD_USERAUDIT" ADD CONSTRAINT "FK_BDD_USERAUDIT_USER" FOREIGN KEY ("USERID")
	  REFERENCES "USER_" ("ID") ENABLE
';
END IF;
END;
/
CREATE OR REPLACE TRIGGER "BDD_USERAUDIT_ID_TRG" BEFORE INSERT OR UPDATE ON BDD_USERAUDIT  FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_maxNumber NUMBER(12) := 0;
BEGIN
  IF INSERTING  THEN
    IF :new.Id IS NULL  THEN
          SELECT "BDD_USERAUDIT_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL; 
                        ELSE
    SELECT  NVL(MAX(Id),0) INTO v_maxNumber FROM BDD_USERAUDIT;  -- NVL used to load 0 instead of NULL
      IF  v_maxNumber < :new.Id   THEN    -- readjust Sequence (i.e., if a Row was inserted with an ID not NULL and > Max(ID) + 1 )
      LOOP
                EXIT WHEN v_newVal >= :new.Id;
                SELECT "BDD_USERAUDIT_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL;
      END LOOP;
                                  ELSE   -- no action on Sequence  
           v_newVal := :new.Id;  -- assign value
    END IF;
    END IF;   -- of Id IS NULL OR NOT NULL
       sqlserver_utilities.identity := v_newVal;   -- save this to emulate @@identity 
       :new.Id := v_newVal;   -- assign this to emulate the identity column
  END IF;  -- of INSERTING
 END;    -- of Trigger
/
ALTER TRIGGER "BDD_USERAUDIT_ID_TRG" ENABLE;
/
--------------------------- end [EB 2010/11/03 create BDD_UserAudit table] ----------------------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM ALL_CONS_COLUMNS WHERE OWNER = 'TargetDBSchemaName' AND TABLE_NAME = 'USER_' AND COLUMN_NAME = 'FIRSTNAME';
IF v_count > 0 THEN
  EXECUTE IMMEDIATE '
  ALTER TABLE
    USER_
    MODIFY
    (
      FIRSTNAME  nvarchar2(64) NULL
    )
  ';
END IF;
v_count :=0;
SELECT COUNT(*) INTO v_count FROM ALL_CONS_COLUMNS WHERE OWNER = 'TargetDBSchemaName' AND TABLE_NAME = 'USER_' AND COLUMN_NAME = 'EMAIL';
IF v_count > 0 THEN
  EXECUTE IMMEDIATE '
  ALTER TABLE
    USER_
    MODIFY
    (
      EMAIL nvarchar2(256) NULL
    )
  ';
END IF;
END;
/

------------------------------- begin [VP 11/9/2011 Add new column in User_ table] -------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'USER_'
                                                   AND column_name = 'PASSWORDEXPIRES';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "USER_" ADD ("PASSWORDEXPIRES" NUMBER(10,0) DEFAULT -1 NOT NULL)';
END IF;
END;
/
------------------------------- end [VladimirPerminov 11/9/2011 Add new column in User_ table] -------------------------------

------------------------------- begin VladimirPerminov 2/1/2012 60387 Splitter position saved in version 5.4 is interpreted incorrectly -------------------------------
DECLARE 
AppIndSplPosType NUMBER(12);
AppDocSplPosType NUMBER(12);
AppIndSplPosId NUMBER(12); 
AppDocSplPosId NUMBER(12);

BEGIN

SELECT Type INTO AppIndSplPosType FROM SETTINGTYPE WHERE Name = 'AppIndexingSplitterPosition';
SELECT Type INTO AppDocSplPosType FROM SETTINGTYPE WHERE Name = 'AppDocumentSplitterPosition';

-- 1 == string
IF (AppIndSplPosType = 1) THEN
  -- UPDATE value begin
  SELECT Id INTO AppIndSplPosId FROM SETTINGTYPE WHERE Name = 'AppIndexingSplitterPosition';   
  UPDATE SETTING SET Value = TO_CLOB(TO_CHAR(100 - TO_NUMBER(TO_CHAR(Value)))) WHERE settingstypeid = AppIndSplPosId;   
  -- UPDATE value end

	-- UPDATE datatype of value FROM VARCHAR to float begin
	-- 7 == Float
	UPDATE SETTINGTYPE SET Type = 7 WHERE Name = 'AppIndexingSplitterPosition';	
  -- UPDATE datatype of value FROM VARCHAR to float end
END IF;

-- 1 == string
IF (AppDocSplPosType = 1) THEN
  -- UPDATE value begin  
  SELECT Id INTO AppDocSplPosId FROM SETTINGTYPE WHERE Name = 'AppDocumentSplitterPosition';  
  UPDATE SETTING SET Value = TO_CLOB(TO_CHAR(100 - TO_NUMBER(TO_CHAR(Value)))) WHERE settingstypeid = AppDocSplPosId;  
  -- UPDATE value end

  -- UPDATE datatype of value FROM VARCHAR to float begin
  -- 7 == Float	
  UPDATE SETTINGTYPE SET Type = 7 WHERE Name = 'AppDocumentSplitterPosition';
  -- UPDATE datatype of value FROM VARCHAR to float end
END IF;

END;
/
------------------------------- end VladimirPerminov 2/1/2012 60387 Splitter position saved in version 5.4 is interpreted incorrectly -------------------------------

------------------------------- begin [EB 2010/11/01 Add new columns in Batch table] -----------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'EXTERNALBATCHID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("EXTERNALBATCHID" NUMBER(10,0) NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD CONSTRAINT "FK_BATCH_BDD_EXTERNALBATCH" FOREIGN KEY ("EXTERNALBATCHID")
	  REFERENCES "BDD_EXTERNALBATCH" ("ID") ENABLE';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'EXTERNALGROUPID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("EXTERNALGROUPID" NUMBER(10,0) NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'TRANSACTIONID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("TRANSACTIONID" NUMBER(10,0) NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'TRANSACTIONTYPE';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("TRANSACTIONTYPE" NUMBER(10,0) NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'STATE';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("STATE" NUMBER(10,0) DEFAULT 0 NOT NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'FOLDERCOUNT';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("FOLDERCOUNT" NUMBER(10,0) DEFAULT 0 NOT NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'DOCUMENTCOUNT';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("DOCUMENTCOUNT" NUMBER(10,0) DEFAULT 0 NOT NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'LASTACCESSEDDATE';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("LASTACCESSEDDATE" DATE NULL)';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                     AND column_name = 'LASTMODULEID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("LASTMODULEID" NUMBER(10,0) NULL)';
END IF;
END;
/
DECLARE
  v_count NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BATCH'
                                                       AND column_name = 'LASTUSERID';                                                  
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE "BATCH" ADD ("LASTUSERID" NUMBER(10,0) NULL)';
  END IF;
END;
/
------------------------------- begin [EB 2010/11/01 Add new columns in tables] -----------------------------
DECLARE
  v_count NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'USER_'
                                                       AND column_name = 'ISAUTOIMPORTED';                                                  
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE "USER_" ADD ("ISAUTOIMPORTED" NUMBER(1) DEFAULT 0 NOT NULL)';
  END IF;
END;
/

DECLARE
  v_count NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'USERGROUP'
                                                       AND column_name = 'EXTERNALGROUPID';                                                  
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE "USERGROUP" ADD ("EXTERNALGROUPID" NUMBER(10,0) NULL)';
  END IF;
END;
/
------------------------------- end [EB 2010/11/01 Add new columns in tables] -----------------------------
-------------------------------begin [DM 2011/10/28 Add new columns in tables]--------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'DOCUMENTPAGE'
                                                     AND column_name = 'DISPLAYFILEID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTPAGE" ADD ("DISPLAYFILEID" NUMBER(10,0) NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTPAGE" ADD CONSTRAINT "FK_DOCUMENTPAGE_DISPLAYFILE" FOREIGN KEY ("DISPLAYFILEID")
	  REFERENCES "FILE_" ("ID") ON DELETE SET NULL ENABLE';
END IF;
END;
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'DOCUMENTFILE'
                                                     AND column_name = 'ORIGINALFILEID';
IF v_count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTFILE" ADD ("ORIGINALFILEID" NUMBER(10,0) NULL)';
EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTFILE" ADD CONSTRAINT "FK_DOCUMENTFILE_ORIGINALFILE" FOREIGN KEY ("ORIGINALFILEID")
	  REFERENCES "FILE_" ("ID") ENABLE';
END IF;
END;
/
--------------------------------end [DM 2011/10/28 Add new columns in tables]--------------------------------
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE NAME='AppBatchGridColumnOrder';
IF v_count = 0 THEN
insert into SETTINGTYPE(NAME, TYPE) values ('AppBatchGridColumnOrder', 1);
END IF;
END;
/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE NAME='AppSettingsGeneralApplicationLanguage';
IF v_count = 0 THEN
insert into SETTINGTYPE(NAME, TYPE) values ('AppSettingsGeneralApplicationLanguage', 1);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcWorkflowDisableNavigationToValidDocuments';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcWorkflowDisableNavigationToValidDocuments', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcWorkflowHideBatchReleaseDialog';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcWorkflowHideBatchReleaseDialog', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcOpenForVerifyNextInvalidBatch';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcOpenForVerifyNextInvalidBatch', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcAppDocumentSeparationPageViewMode';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcAppDocumentSeparationPageViewMode', 4);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcAppViewShowCompanyLogo';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcAppViewShowCompanyLogo', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcGeneralTypingAutoCompletionEnabled';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcGeneralTypingAutoCompletionEnabled', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcGeneralTypingAutoCompletionCurrentPageOnly';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcGeneralTypingAutoCompletionCurrentPageOnly', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningKnowledgeBaseJobID';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningKnowledgeBaseJobID', 4);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningUseProjectDefaults';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningUseProjectDefaults', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningEnabled';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningEnabled', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningAutoExtract';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningAutoExtract', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningWhenAddToKnowledgeBase';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningWhenAddToKnowledgeBase', 4);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='AppSettingsSupervisedLearningInvalidFieldsThreshold';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('AppSettingsSupervisedLearningInvalidFieldsThreshold', 4);
END IF;
END;
/

-- Begin [EB 2010/11/18 add setting types for print dialog]
DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingAlwaysShowPrintSetup';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingAlwaysShowPrintSetup', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingPrintPages';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingPrintPages', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingPrintForm';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingPrintForm', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingPrintHiddenFields';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingPrintHiddenFields', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingPrintTableFields';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingPrintTableFields', 3);
END IF;
END;
/

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='VwcAppPrintingColumnHeaderPerPage';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('VwcAppPrintingColumnHeaderPerPage', 3);
END IF;
END;
/
-- end

DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM SETTINGTYPE WHERE Name='WvcAppFilteringCustomFilteringConditionString';
IF v_count = 0 THEN
INSERT INTO SettingType(Name, Type) values ('WvcAppFilteringCustomFilteringConditionString', 1);
END IF;
END;
/

-- Begin [VK 20101104] fill in new columns of a batch table (only for that entries that had not been filled yet
-- TMP_BATCHES and TMP_BATCHRECENTDOC tables are now used only during update
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_BATCHES' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_BATCHES';
END IF;
END;
/

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_BATCHES 
   (	
    ID NUMBER NOT NULL, 
	TOTALDOCUMENTSCOUNT NUMBER null, 
	STATE NUMBER null, 
	LASTACCESSEDDATE DATE, 
	BATCHFOLDERSCOUNT NUMBER null
   ) ON COMMIT PRESERVE ROWS
 
/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_BATCHRECENTDOC' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_BATCHRECENTDOC';
END IF;
END;
/

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_BATCHRECENTDOC 
   (	
    BATCHID NUMBER NOT NULL, 
	RECENTDOCID NUMBER NOT NULL
   ) ON COMMIT PRESERVE ROWS 
 
/
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM ROLETOUSERREL WHERE (ROLEID=1 AND USERID=1);
IF v_count = 0 THEN
INSERT INTO ROLETOUSERREL (ID,ROLEID,USERID) values (null,1,1);
END IF;
SELECT COUNT(*) INTO v_count FROM ROLETOUSERREL WHERE (ROLEID=1 AND USERID=2);
IF v_count = 0 THEN
INSERT INTO ROLETOUSERREL (ID,ROLEID,USERID) values (null,1,2);
END IF;
SELECT COUNT(*) INTO v_count FROM ROLETOUSERREL WHERE (ROLEID=1 AND USERID=3);
IF v_count = 0 THEN
INSERT INTO ROLETOUSERREL (ID,ROLEID,USERID) values (null,1,3);
END IF;
END;
/

insert into TMP_BATCHES (ID, TOTALDOCUMENTSCOUNT, STATE, LASTACCESSEDDATE, BATCHFOLDERSCOUNT)
SELECT DISTINCT 
		BatchFolder.BatchId
		,COUNT(DISTINCT Document.Id) AS TotalDocumentsCount
		,nvl(MIN(Document.State),0)
        ,MAX(Document.LastAccessedDate)
        ,COUNT(DISTINCT Document.BatchFolderId) AS BatchFoldersCount
	FROM Document
		inner join DocumentFile on DocumentFile.DocumentId=Document.Id
		right JOIN BatchFolder ON Document.BatchFolderId = BatchFolder.Id 
		INNER JOIN Batch ON Batch.Id = BatchFolder.BatchId
	where Batch.DocumentCount=0
	group by BatchFolder.BatchId;

INSERT INTO TMP_BATCHRECENTDOC
		SELECT
				tmp_batches.Id
				,nvl(MAX (Document.Id),-1)
		FROM Document
			INNER JOIN BatchFolder ON Document.BatchFolderId = BatchFolder.Id 
			right JOIN TMP_BATCHES ON (tmp_batches.Id = BatchFolder.BatchId
						AND NVL(Document.LastAccessedDate,'01-JAN-1900') = NVL(tmp_batches.LastAccessedDate,'01-JAN-1900'))
		group by tmp_batches.Id;
/
update Batch
set 
(
  Batch.State, 
  Batch.DocumentCount, 
  Batch.FolderCount, 
  Batch.LastAccessedDate, 
  Batch.LastModuleId, 
  Batch.LastUserId
) = 
(
  select
    nvl(tmp_batches.State, 0),
    nvl(tmp_batches.TotalDocumentsCount, 0),
    nvl(tmp_batches.BatchFoldersCount, 0),
    tmp_batches.LastAccessedDate,
    document.LastModuleId,
    document.LastUserId
  from tmp_batches
  inner join tmp_batchrecentdoc on tmp_batchrecentdoc.batchid=tmp_batches.id
  inner join document on document.id=tmp_batchrecentdoc.recentdocid
  where tmp_batches.id=Batch.id
)
where Batch.DocumentCount=0
/
TRUNCATE TABLE TargetDBSchemaName.TMP_BATCHES;
/
DROP TABLE TargetDBSchemaName.TMP_BATCHES
/
TRUNCATE TABLE TargetDBSchemaName.TMP_BATCHRECENTDOC;
/
DROP TABLE TargetDBSchemaName.TMP_BATCHRECENTDOC
/
-- End [VK 20101104] fill in new columns of a batch table (only for that entries that had not been filled yet


DECLARE 
  v_count number :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_sequences WHERE SEQUENCE_NAME='BDD_VERSIONHISTORY_ID_SEQ' AND SEQUENCE_OWNER = 'TargetDBSchemaName';
if V_COUNT = 0 then
  EXECUTE IMMEDIATE 'CREATE SEQUENCE  "BDD_VERSIONHISTORY_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE';
END IF;
END;
/

DECLARE
v_count NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE TABLE_NAME='BDD_VERSIONHISTORY' AND owner='TargetDBSchemaName'; 
  IF v_count = 0 THEN  
    EXECUTE IMMEDIATE '
      CREATE TABLE "BDD_VERSIONHISTORY" 
        (	"ID" NUMBER(10,0), 
          "Version" NVARCHAR2(32), 	
          "InstallDate" timestamp
        )
    ';  
    EXECUTE IMMEDIATE '
      ALTER TABLE "BDD_VERSIONHISTORY" ADD CONSTRAINT "PK_BDD_VERSIONHISTORY" PRIMARY KEY ("ID") ENABLE
    ';
    EXECUTE IMMEDIATE '
      ALTER TABLE "BDD_VERSIONHISTORY" MODIFY ("ID" NOT NULL ENABLE)
    ';
  END IF;
END;
/

CREATE OR REPLACE TRIGGER "BDD_VERSIONHISTORY_ID_TRG" BEFORE INSERT OR UPDATE ON BDD_VERSIONHISTORY  FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_maxNumber NUMBER(12) := 0;
BEGIN
  IF INSERTING  THEN
    IF :new.Id IS NULL  THEN
          SELECT "BDD_VERSIONHISTORY_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL; 
                        ELSE
    SELECT  NVL(MAX(Id),0) INTO v_maxNumber FROM BDD_VERSIONHISTORY;  
      IF  v_maxNumber < :new.Id   THEN    
      LOOP
                EXIT WHEN v_newVal >= :new.Id;
                SELECT "BDD_VERSIONHISTORY_ID_SEQ".NEXTVAL INTO v_newVal FROM DUAL;
      END LOOP;
                                  ELSE    
           v_newVal := :new.Id;  
    END IF;
    END IF;   
       sqlserver_utilities.identity := v_newVal;   
       :new.Id := v_newVal;   
  END IF; 
 END;    
/
ALTER TRIGGER "BDD_VERSIONHISTORY_ID_TRG" ENABLE;

declare 
d_currentdate DATE;
BEGIN
  SELECT SYSDATE INTO D_CURRENTDATE FROM DUAL;
	insert into BDD_VersionHistory("Version", "InstallDate") values('5.6.5608.155', d_currentdate);
END;
/

  CREATE OR REPLACE PROCEDURE SP_SETGLOBALAPPLICATIONSETTING
(
  v_sSettingName IN NVARCHAR2,
  v_svalue in nvarchar2,
  v_bActivate in number 
)
is
  pragma autonomous_transaction;
  v_dsettingvalue date; 
  v_isettingconfigurationid number :=-1;
  v_isettinguserid number :=-1;  
  v_isettingtypetype number :=-1;
  v_isettingtypeid number := 0;
  v_isettingid number :=-1;
begin
  
  select TO_DATE('1977-05-30 04:00:01', 'YYYY-MM-DD HH24:MI:SS') into v_dsettingvalue from dual;
  
  select NVL(MAX(sc.id),-1) into v_isettingconfigurationid from settingconfiguration sc where sc.creationdate = v_dsettingvalue;  
  
  if (v_bactivate = 0 )  THEN
    begin
      dbms_output.put_line('Setting: ' || v_ssettingname || ' has been deactivated.');    
      if (v_isettingconfigurationid = -1) THEN
        goto lblexit;
      else
        begin
          select nvl(max(st.id ),-1) into v_isettingtypeid from settingtype st where st.name = v_ssettingname;
          if (v_isettingtypeid = -1)  THEN
            goto lblExit;
          else
            begin 
              select nvl(max(s.id ),-1) into v_isettingid from setting s 
                      inner join settingtype st on st.id = s.settingstypeid
                      inner join settingconfiguration sc on sc.id = s.settingsconfigurationid
              where (sc.id = v_isettingconfigurationid) and (st.id = v_isettingtypeid);
              if (v_isettingid = -1) THEN
                goto lblExit;
              else
                begin 
                  delete from setting where id = v_isettingid;
                  goto lblExit;
                End;
              end if;              
            End; 
          end if;
        END;
      end if; 
    end; 
  End if;  
  
  
  
  if ( v_isettingconfigurationid = -1) then
    BEGIN
      insert into settingconfiguration (CreationDate, default_)
      values (v_dsettingvalue, 1);      
      v_isettingconfigurationid := sqlserver_utilities.identity;
    END;  
   END IF;

  select nvl(max(usc.userid),-1) into v_isettinguserid from usertosettingconfigurationrel usc where usc.settingsconfigurationid = v_isettingconfigurationid;
  
  if ( v_isettinguserid = -1 ) then   
    BEGIN  
      INSERT INTO UserToSettingConfigurationRel (SettingsConfigurationId, UserId)
      values (v_isettingconfigurationid, 1);
      v_isettinguserid := sqlserver_utilities.identity;
    END;
   END IF;

  v_iSettingTypeType := CASE 
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForPage' THEN 3
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForClass' THEN 3
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForDocument' THEN 3
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForField' THEN 3
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForTableCell' THEN 3
                              WHEN v_sSettingName = 'SysAppHistoryReportingActivatedForLearning' THEN 3
                              WHEN v_sSettingName = 'SysAppBatchDeletionDisabledInRTS' THEN 3
                              WHEN v_sSettingName = 'SysAppBatchDeletionDisabledInDesigner' THEN 3
                              WHEN v_sSettingName = 'SysAppDisableWindowsAuthentication' THEN 3
                              WHEN v_sSettingName = 'SysAppBatchColumnExternalGroupId' THEN 1
                              WHEN v_sSettingName = 'SysAppBatchColumnExternalBatchId' THEN 1
                              WHEN v_sSettingName = 'SysAppBatchColumnTransactionId' THEN 1
                              WHEN v_sSettingName = 'SysAppBatchColumnTransactionType' THEN 1
                              WHEN v_sSettingName = 'SysAppUrlSignatureExpirationPeriod' THEN 4
                              ELSE 4
                        end ;
   
   SELECT NVL(MAX(st.Id),0) INTO v_iSettingTypeId FROM SettingType st WHERE st.NAME = v_sSettingName;
   if ( v_isettingtypeid = 0 ) then   
    BEGIN
      insert into settingtype (name, type)
      values (v_ssettingname, v_isettingtypetype);
      v_isettingtypeid := sqlserver_utilities.identity;
    END;      
   END IF;
   
   SELECT  NVL(MAX(id),-1) INTO v_iSettingId  FROM Setting WHERE SettingsConfigurationId = v_iSettingConfigurationId AND SettingsTypeId = v_iSettingTypeId;   
   if ( v_isettingid = -1 ) then   
    BEGIN
      INSERT INTO Setting  (SettingsConfigurationId, SettingsTypeId, VALUE)
      values ( v_isettingconfigurationid, v_isettingtypeid, v_svalue);
      v_isettingid := sqlserver_utilities.identity;
      dbms_output.put_line('Setting: ' || v_ssettingname || ' has been set up with value=' || v_svalue ||'.'); 
    END;  
   else 
    BEGIN
      UPDATE Setting
         SET SettingsConfigurationId = v_iSettingConfigurationId
         WHERE Id = v_iSettingId;
      UPDATE Setting
         SET SettingsTypeId = v_iSettingTypeId
         WHERE Id = v_iSettingId;
      UPDATE Setting
         SET VALUE = v_sValue
         where id = v_isettingid;
      dbms_output.put_line('Setting: ' || v_ssettingname || ' has been set up with value=' || v_svalue ||'.');    
    END;      
   end if;   

<<lblExit>>
   commit;

exception
   when others then
    BEGIN
      rollback;
      dbms_output.put_line('Rollback. Die �nderungen an der DB wurden nicht ausgef�hrtan.' || dbms_utility.format_error_stack ); 
    end;
end SP_SetGlobalApplicationSetting;

/

create or replace
FUNCTION USERHASADMINISTRATORROLE 
(
  userId in number  
) RETURN NUMBER 
AS 
  isAdmin number := 0;
  in_userId number := userId;
BEGIN
  select count(RoleName) into isAdmin
  from
  (
    SELECT Role.Name as RoleName
		FROM Role 
    INNER JOIN RoleToUserRel ON Role.Id = RoleToUserRel.RoleId 
		WHERE RoleToUserRel.UserId = in_userId AND Role.Name='Administrator'
		
    UNION ALL
		
    SELECT Role.Name as RoleName
		FROM Role 
    INNER JOIN RoleToGroupRel ON Role.Id = RoleToGroupRel.RoleId 
    INNER JOIN UserToUserGroupRel ON RoleToGroupRel.UserGroupId = UserToUserGroupRel.UserGroupId 
		WHERE UserToUserGroupRel.UserId = in_userId AND Role.Name='Administrator'
  );
  
  return isAdmin;
  
END USERHASADMINISTRATORROLE;
/

create or replace
FUNCTION GETUSEREXTERNALGROUPCHECKCODE 
(
  userId IN NUMBER  
) RETURN VARCHAR2 AS 
  code varchar2(300) := '';
BEGIN
  if (userId != -1 AND USERHASADMINISTRATORROLE(userId) = 0) then
    code := '
    (Batch.ExternalGroupId IN (
      SELECT	UserGroup.ExternalGroupId	
      FROM UserGroup 
      JOIN UserToUserGroupRel ON UserGroup.Id = UserToUserGroupRel.UserGroupId	
      WHERE UserToUserGroupRel.UserId = ' || userId || ') 
    OR Batch.ExternalGroupId IS NULL)';
  end if;
  RETURN code;
END GETUSEREXTERNALGROUPCHECKCODE;
/

create or replace
FUNCTION      "IS_BATCH_LOCKED" (
  documentId in int, 
  batchId in int, 
  userId in int, 
  moduleId in int, 
  currentDate number)
RETURN int
AS
	c_isLocked int := 0;
    c_lockerCount int := 0;
    
  -- these vars will hold parameter values to make comparisons work
  p_batchId number(10);
  p_userId number(10);
  p_moduleId number(10);
  p_currentDate number(20);
  p_documentId number(10);
BEGIN
	p_batchId := batchId;
  p_userId := userId;
  p_moduleId := moduleId;
  p_currentDate := currentDate;
  p_documentId := documentId;
  
  if (documentId != -1) then
        select sum(c_lockers)
        into c_lockerCount
        from(
        --Select count(*) as c_lockers
        --from (	Select locker.* 
				--From locker 
				--Inner Join LockerToDocumentRel doc on doc.LockerId = locker.Id AND doc.DocumentId = p_documentId
				--Where (locker.UserId != p_userId OR locker.ModuleId != p_moduleId) AND
				--	  (locker.LockInterval = -1 OR locker.LockInterval >= (p_currentDate - locker.CreationDate)))
        --Union
        Select count(*) as c_lockers
        from (	Select locker.* 
				From locker 
				Inner Join LockerToBatchRel batch on batch.LockerId = locker.Id 
				Inner Join Document doc on doc.Id = p_documentId
				Inner Join BatchFolder folder on folder.Id = doc.BatchFolderId
				Where (locker.UserId != p_userId OR locker.ModuleId != p_moduleId) AND
					  (locker.LockInterval = -1 OR locker.LockInterval >= (p_currentDate - locker.CreationDate)) AND
					folder.batchId = batch.DocumentId) 
        );
		if (c_lockerCount = 0) then
		c_isLocked:=0;
        else c_isLocked:=1;
        end if;
    end if;
    c_lockerCount := 0;
	if (batchId != -1) then
		Select count(*) into c_lockerCount from(
            Select locker.* 
			From locker 
			Inner Join LockerToBatchRel batch on batch.LockerId = locker.Id 
			Where (locker.UserId != p_userId OR locker.ModuleId != p_moduleId) AND
				  (locker.LockInterval = -1 OR locker.LockInterval >= (p_currentDate - locker.CreationDate)) AND 
				batch.DocumentId = p_batchId);
		if (c_lockerCount != 0) then
		c_isLocked := 1;
        end if;
    end if;
	
	RETURN c_isLocked;
END;

/

create or replace
FUNCTION GET_EXTBATCHES_QUERY 
(
  jobId IN NUMBER
, currentDate IN NUMBER  
, sortColumn IN VARCHAR2  
, sortDir IN VARCHAR2  
, states IN VARCHAR2  
, needIsLocked IN NUMBER
, userId IN NUMBER
, needCheckExternalGroup IN NUMBER
) RETURN VARCHAR2 AS 

res_query varchar2(4000);
c_sortCondition varchar2(200);
c_isLocked varchar2(100);
c_whereCondition varchar2(2000);

BEGIN
if (sortColumn is null or sortColumn='' or sortColumn=' ') then
  c_sortCondition := 'Priority';
else
  c_sortCondition := upper(sortColumn);
end if;
c_sortCondition := REPLACE(c_sortCondition,'BATCH.[NAME]','NAME');
c_sortCondition := REPLACE(c_sortCondition,'BATCH.NAME','NAME');
if (c_sortCondition = 'NAME') then
  c_sortCondition := 'nlssort(lpad(BATCH.NAME,256),''NLS_SORT=BINARY_CI'')';
end if;
c_sortCondition := REPLACE(c_sortCondition,'BATCH.PRIORITY','Priority');
--lastaccesseduser is for backward compatibility only. New code should never use this ordering expression
c_sortCondition := REPLACE(c_sortCondition,'LASTACCESSEDUSER','LASTUSER,LASTMODULE');
c_sortCondition := REPLACE(c_sortCondition,'MODULE.[NAME]','LASTMODULE');
c_sortCondition := REPLACE(c_sortCondition,'LASTMODULE','nlssort(Module.Name, ''NLS_SORT=BINARY_CI'')');
c_sortCondition := REPLACE(c_sortCondition,'LASTUSER','nlssort(trim(FirstName || '' '' || LastName),''NLS_SORT=BINARY_CI'') ' || sortDir);
c_sortCondition := REPLACE(c_sortCondition,'BATCHFOLDERSCOUNT', 'Batch.FolderCount');
c_sortCondition := REPLACE(c_sortCondition,'TOTALDOCUMENTSCOUNT', 'Batch.DocumentCount');
c_sortCondition := REPLACE(c_sortCondition,'EXTERNALBATCHNAME', 'BDD_ExternalBatch.Name');
c_sortCondition:=',row_number() over (order by ' || c_sortCondition || ' ' || sortDir || ', Batch.OriginalId asc) as recordnumber'; 
if (needIsLocked!=0) then
  c_isLocked := 'Is_Batch_Locked(-1, Batch.Id, -1, -1, ' || currentDate || ')	AS IsLocked';
else
  c_isLocked := '0 AS IsLocked';
end if;
c_whereCondition := GETUSEREXTERNALGROUPCHECKCODE(case when needCheckExternalGroup=1 then userId else -1 end);
if (states is not null and states!=' ') then
  if (length(c_whereCondition)>0) then c_whereCondition := c_whereCondition || ' AND Batch.State in (' || states || ')';
  else c_whereCondition := 'Batch.State in (' || states || ')';
  end if;
end if;
if (length(c_whereCondition) > 0) then
  c_whereCondition := 'WHERE ' || c_whereCondition;
end if;
res_query := 'SELECT
             Batch.Id 
						,Batch.Name
						,Batch.OriginalId
						,Batch.Priority
						,Batch.DocumentCount as TotalDocumentsCount
						,Batch.FolderCount as BatchFoldersCount
						,Batch.State
						,Batch.LastAccessedDate 
						,TRIM(CHR(32) FROM NVL2(User_.FirstName, User_.FirstName || CHR(32), null) || User_.LastName) AS LastUser 
						,Module.Name AS LastModule
						,Batch.CreationProjectId
						,Project.FilePath 
						,' || c_isLocked || '
						,NVL2(Batch.Client, Batch.Client, CHR(32)) AS Client
						,Batch.ImportDirectoryPath
            ,Batch.ExternalGroupId
            ,Batch.TransactionId
            ,Batch.TransactionType
            ,BDD_ExternalBatch.Name as ExternalBatchName
            ,-1 as LockerId
            ' || c_sortcondition || '
					FROM Batch  
						INNER JOIN JobToBatchRel on JobToBatchRel.BatchId=Batch.Id and JobToBatchRel.JobId=' || jobId || '
            LEFT JOIN User_ on User_.Id=Batch.LastUserId
            LEFT JOIN Module on Module.Id=Batch.LastModuleId
            LEFT JOIN Project on Project.Id=Batch.CreationProjectId
            LEFT JOIN BDD_ExternalBatch on BDD_ExternalBatch.Id=Batch.ExternalBatchId
            ' || c_whereCondition;
              
  RETURN res_query;
END GET_EXTBATCHES_QUERY;
/

-- [VK 20101104] This procedure is no longer used. Need to drop it if already exists
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_procedures WHERE owner='TargetDBSchemaName' and object_name='SP_TMP_PREPARE_EXTBATCHITEMS';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE TargetDBSchemaName.SP_TMP_PREPARE_EXTBATCHITEMS';
END IF;
END;
/

/*
create or replace
PROCEDURE SP_TMP_PREPARE_EXTBATCHITEMS 
(
  jobId IN int  
) AS 
c_jobId number(10);
BEGIN
c_jobId := jobId;
  insert into TMP_BATCHES (ID, TOTALDOCUMENTSCOUNT, STATE, LASTACCESSEDDATE, BATCHFOLDERSCOUNT)
SELECT DISTINCT 
				BatchFolder.BatchId
				,COUNT(DISTINCT Document.Id) AS TotalDocumentsCount
				,nvl(MIN(State),0)
        ,MAX(LastAccessedDate)
        ,COUNT(DISTINCT BatchFolderId) AS BatchFoldersCount
	FROM Document
		inner join DocumentFile on DocumentFile.DocumentId=Document.Id
		right JOIN BatchFolder 
			ON Document.BatchFolderId = BatchFolder.Id 
		INNER JOIN Batch 
			ON Batch.Id = BatchFolder.BatchId
		INNER JOIN JobToBatchRel
			ON JobToBatchRel.BatchId = Batch.Id --and JobToBatchRel.JobId = jobId
  where JobToBatchRel.JobId=c_jobId
	group by BatchFolder.BatchId;

INSERT INTO TMP_BATCHRECENTDOC
		SELECT
				tmp_batches.Id
				,nvl(MAX (Document.Id),-1)
		FROM Document
			INNER JOIN BatchFolder 
				ON Document.BatchFolderId = BatchFolder.Id 
			right JOIN TMP_BATCHES
				ON (tmp_batches.Id = BatchFolder.BatchId
						AND NVL(Document.LastAccessedDate,'01-JAN-1900') = NVL(tmp_batches.LastAccessedDate,'01-JAN-1900'))
		group by tmp_batches.Id;
    
END SP_TMP_PREPARE_EXTBATCHITEMS;

/
*/

-- [VK 20101104] This procedure is no longer used. Need to drop it if already exists
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_procedures WHERE owner='TargetDBSchemaName' and object_name='SP_TMP_CLEANUP_EXTBATCHITEMS';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP PROCEDURE TargetDBSchemaName.SP_TMP_CLEANUP_EXTBATCHITEMS';
END IF;
END;
/
/*
create or replace
PROCEDURE SP_TMP_CLEANUP_EXTBATCHITEMS AS 
BEGIN
  
  delete from tmp_batches;
  delete from tmp_batchrecentdoc;

END SP_TMP_CLEANUP_EXTBATCHITEMS;

/
*/

create or replace
PROCEDURE SP_GETBATCHPAGE 
(
jobId IN int
, batchId IN int  
, sortColumn IN VARCHAR2  
, sortDir IN VARCHAR2  
, states IN VARCHAR2  
, currentDate IN NUMBER  
, userId IN NUMBER
, needCheckExternalGroup IN NUMBER
, res OUT sys_refcursor
) AS 

c_result sys_refcursor;
res_query varchar2(4000);

BEGIN
  
  res_query := 'select recordnumber-1 as VALUE from (' ||
    GET_EXTBATCHES_QUERY(jobId, currentDate, sortColumn, sortDir, states, 0, userId, needCheckExternalGroup) || '
  ) where id=' || batchId;  
  
  open c_result for res_query;
  res := c_result;
  
END SP_GETBATCHPAGE;

/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_SUBRES' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_SUBRES';
END IF;
END;
/
CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_SUBRES ( 
Id number null,
    PageIndex number NULL,
PageConfidence float NULL,
DocumentId number NULL,
DocumentName nvarchar2(256) NULL,
DocumentState number NULL,
DocumentClassName nvarchar2(256) NULL,
DocumentIndex number NULL,
FolderId number NULL,
FolderName nvarchar2(256) NULL,
BatchId number NULL,
BatchOriginalId nvarchar2(32) NULL,
PageStatus number NULL
) ON COMMIT PRESERVE ROWS

/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_FOLDERS' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_FOLDERS';
END IF;
END;
/

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_FOLDERS ( 
    FID        	NUMBER(22,0) NOT NULL,
    FName      	NVARCHAR2(256) NULL,
    BID        	NUMBER(22,0) NULL,
    BORIGINALID	NVARCHAR2(32) NULL,
    FOLDERSTATE	NUMBER(22,0) NULL,
    BATCHSTATE 	NUMBER(22,0) NULL
    )  ON COMMIT PRESERVE ROWS

/

DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_ORDEREDFOLDERS' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_ORDEREDFOLDERS';
END IF;
END;
/

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_ORDEREDFOLDERS ( 
    FID        	NUMBER(22,0) NOT NULL,
    FName      	NVARCHAR2(256) NULL,
    BID        	NUMBER(22,0) NULL,
    BORIGINALID	NVARCHAR2(32) NULL,
    FOLDERSTATE	NUMBER(22,0) NULL,
    BATCHSTATE 	NUMBER(22,0) NULL,
    FOLDERINDEX	NUMBER(22,0) NULL 
    )  ON COMMIT PRESERVE ROWS

/

create or replace
PROCEDURE "SP_GETEXTBATCHITEMS"(
  jobId  in int,
  sortDir in nvarchar2, 
  sortColumn in nvarchar2,
  states in nvarchar2, 
  startIndex in int,
  maxRecords in int,
  currentDate in number,
  userId in number,
  needCheckExternalGroup IN NUMBER,
  res out sys_refcursor)
is
    c_result sys_refcursor;
    c_pagingCond varchar2(100);
    c_startIndex number;
    res_query varchar2(4000);
BEGIN

c_startIndex:=startIndex+1;

if (maxRecords<c_startIndex) then
 c_pagingCond:=null;
else
  c_pagingCond := ' where recordnumber between ' || c_startIndex || ' and ' || maxRecords;
end if;

res_query := GET_EXTBATCHES_QUERY(jobId, currentDate, sortColumn, sortDir, states, 1, userId, needCheckExternalGroup);

if (c_pagingCond is not null) then
  res_query := 'select * from (' || res_query || ') '||c_pagingCond;
end if;

open c_result for res_query;
res := c_result;

END sp_getExtBatchItems;
/


create or replace
PROCEDURE      "SP_GETEXTDOCUMENTVIEWITEMS"(batchId  in number,sortDir in nvarchar2, sortColumn in nvarchar2, states in nvarchar2, startIndex in number,maxRecords in number, res out sys_refcursor)
  IS
    c_result sys_refcursor;
    c_statesCond varchar2(100);
    c_pagingCond varchar2(100);
    c_startIndex number;
    res_query varchar2(4000);
    c_folderSortCol1 varchar2(100);
    c_folderSortCol2 varchar2(100);
    c_docSortCol varchar2(100);
c_tempStringCommand varchar2(4000);
BEGIN
  if (states!=' ') then
 c_statesCond:=' where Document.State in (' || states || ')';
else
 c_statesCond:='';
end if;

if (startIndex=0) then
 c_startIndex:=1;
else
 c_startIndex:=startIndex;
end if;

if (maxRecords=-1) then
 c_pagingCond:='';
else
 c_pagingCond:=' where FolderIndex between ' || startIndex || ' and ' || maxRecords;
end if;

if (sortColumn='State') then
 c_folderSortCol1 := 'FolderState';
 c_folderSortCol2 := 'FolderState '|| sortDir||',FId';
 c_docSortCol := 'DocumentState';
end if;

if (sortColumn='NameId') then
 c_folderSortCol1 := 'lpad(FName,256)';
 c_folderSortCol2 := 'lpad(FName,256) ';
 c_docSortCol := 'DocumentIndex';
end if;

if (sortColumn='Name') then
 c_folderSortCol1 := 'lpad(FName,256)';
 c_folderSortCol2 := 'lpad(FName,256) '|| sortDir||',FId';
 c_docSortCol := 'DocumentName';
end if;

if (sortColumn='DocumentClass') then
 c_folderSortCol1 := 'lpad(FName,256)';
 c_folderSortCol2 := 'lpad(FName,256) '|| sortDir||',FId';
 c_docSortCol := 'DocumentClassName';
end if;

c_tempStringCommand := 'begin
insert into TMP_SUBRES
select  DocumentPage.ID as Id, DocumentPage.INDEX_ as PageIndex, DocumentPage.CONFIDENCE as PageConfidence,Document.ID as DocumentId, Document.NAME as DocumentName, Document.STATE as DocumentState,DocumentClassName.NAME as DocumentClassName,Document.INDEX_ as DocumentIndex, BatchFolder.ID as FolderId, BatchFolder.NAME as FolderName, Batch.ID as BatchId, Batch.ORIGINALID as BatchOriginalId, DocumentPage.STATUS as PageStatus
from DocumentPage 
left join DocumentFile on documentPage.DOCUMENTFILEID=documentFile.ID 
left join Document on Document.ID= DocumentFile.DOCUMENTID
left join DocumentClassName on Document.DOCUMENTCLASSNAMEID=DocumentClassName.ID
right join BatchFolder on Document.BATCHFOLDERID=BatchFolder.ID
inner join Batch on Batch.ID=BatchFolder.BATCHID and Batch.ID=' || batchId || c_statesCond || ';

insert into TMP_FOLDERS  
select BatchFolder.Id as FId, BatchFolder.Name as FName, Batch.ID as BId, Batch.OriginalId as BOriginalId, min(DocumentState) as FolderState, Batch.State as BatchState
from  BatchFolder
inner join Batch on Batch.Id=BatchFolder.BatchId and batch.id=' || batchId || '
left join tmp_subres on BatchFolder.Id=FolderId
group by BatchFolder.Id,BatchFolder.Name, Batch.Id, Batch.OriginalId, Batch.State;

insert into TMP_ORDEREDFOLDERS
select FId, FName, BId, BOriginalId, FolderState, BatchState, row_number() over (order by ' || c_foldersortcol1 || ' ' || sortDir || ', FId ' || sortDir ||') as FolderIndex
from TMP_FOLDERS
where FolderState is not null;
end;';

execute immediate c_tempStringCommand;

res_query := '
select Id, PageIndex, PageConfidence,DocumentId, DocumentName, DocumentState, DocumentClassName, 
	DocumentIndex, FolderId, FolderName, BatchId , BatchOriginalId, FolderState, BatchState, PageStatus 
from TMP_SUBRES 
right join TMP_ORDEREDFOLDERS on FId=FolderId' || c_pagingCond || ' order by ' || c_folderSortCol2 || ' ' || sortDir || ','|| c_docSortCol || ' ' || sortDir;

open c_result for res_query;
res := c_result;
delete from TMP_ORDEREDFOLDERS;
delete from TMP_FOLDERS; 
delete from TMP_SUBRES;
commit;
END SP_GETEXTDOCUMENTVIEWITEMS;
/

CREATE OR REPLACE PROCEDURE "TargetDBSchemaName"."SP_LOADNEWDOCUMENT" (
    in_documentId IN number,
    in_batchId IN number,
    in_userId IN number,
    in_moduleId IN number,
    in_currentDate IN number,
    res out sys_refcursor)
IS
  c_result sys_refcursor;
  --lockInterval number;
  isLockerExists number;
  --lastInsertedId number;
  docId number;
  emptyStr nvarchar2(1);
BEGIN
emptyStr:=' ';
IF (IS_BATCH_LOCKED(in_documentId ,in_batchId ,in_userId ,in_moduleId ,in_currentDate)=0)
THEN

	--select modType.LockInterval 
  --  into lockInterval
	--from "MODULE" mod
	--inner join ModuleType modtype on modtype.Id = mod.ModuleTypeId
	--where mod.Id = in_moduleId;

	-- delete lockers
	--delete from Locker where Locker.Id in (
	--	select dloc.LockerId from LockerToDocumentRel dloc where dloc.DocumentId = in_documentId
	--	union
	--	select bloc.LockerId from LockerToBatchRel bloc where bloc.DocumentId = in_batchId);

    docId := in_documentId;
	-- select unlocked document
    open c_result for 
    select file1.Id, 
		   file1."VALUE" as "IMAGE",
		   0 as "STATE",
		   emptyStr as "NAME",
           "INDEX_" as "INDEX"
    from DOCUMENTFILE 
	inner join "FILE_" file1 on DOCUMENTFILE.DOCUMENTID = docId AND DOCUMENTFILE.FileId = file1.Id
    union all
    select file2.Id, 
		   file2."VALUE" as "IMAGE",
		   doc."STATE" as "STATE",
		   doc."NAME" as "NAME", 
           -1 as "INDEX" 
	from "DOCUMENT" doc
	inner join "FILE_" file2 on doc.FileId = file2.Id
	where doc.Id = in_documentId
    order by "NAME" desc, "INDEX" asc;
    
    res:= c_result;
    
    if(SQL%ROWCOUNT > 0)
    then

        -- set new locker
        --insert into Locker (Id, UserId, ModuleId, CreationDate, LockInterval, ProcessId)
        --values (LOCKER_ID_SEQ.nextval, in_userId, in_moduleId, in_currentDate, lockInterval*10000, null)
        --returning Id into lastInsertedId;

        --if(in_documentId <> -1)
        --then
        --    insert into LockerToDocumentRel (DocumentId, LockerId) values (in_documentId, lastInsertedId);
        --end if;

        --if(in_batchId <> -1) 
        --then
        --    insert into LockerToBatchRel (DocumentId, LockerId) values (in_batchId, lastInsertedId);
        --end if;

        -- update batch
        update "BATCH"
        set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
        where id = in_batchId;

		-- update document
        update "DOCUMENT"
        set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
        where id = in_documentId;

        -- update loggedInUser
        update LoggedInUser
        set LastActivityDate = sysdate
        where UserId = in_userId AND ModuleId = in_moduleId;
	   
	COMMIT;
	end if;

ELSE
  
  ROLLBACK;
  -- raise error "batch has been locked"
  raise_application_error (-20012,' Error-20012. An exception raised by the stored procedure sp_LoadNewDocument --> The batch with ID: ' || TO_CHAR(in_batchId) || ' could not be loaded because it is currently locked. ');
  
END IF;


END SP_LOADNEWDOCUMENT;

/
CREATE OR REPLACE PROCEDURE "TargetDBSchemaName"."SP_TOUCHDOCUMENT" (
    in_documentId IN number,
    in_batchId IN number,
    in_userId IN number,
    in_moduleId IN number,
    in_currentDate IN number,
    in_lockInterval IN number,
    res out sys_refcursor,
    existingLockerId IN number default -1)
IS
    c_result sys_refcursor;
    loc_lockInterval number;
    isLockerExists number;
    lastInsertedId number;
    temp number;
    activeLockerId number;
BEGIN
IF (IS_BATCH_LOCKED(in_documentId ,in_batchId ,in_userId ,in_moduleId ,in_currentDate)=0)
then
  activeLockerId := -1;
  if (existingLockerId > 0)
  then
    --if (in_documentId > 0) then insert into LockerToDocumentRel(DocumentId, LockerId) values (in_documentId, existingLockerId); end if;
    if (in_batchId > 0) then insert into LockerToBatchRel(DocumentId, LockerId) values(in_batchId, existingLockerId); end if;
    activeLockerId := existingLockerId;
  else
    --calculating lock interval
	if(in_lockInterval = -2)
	then
        select modType.LockInterval
                into loc_lockInterval 
                from "MODULE" mod
                inner join ModuleType modtype on modtype.Id = mod.ModuleTypeId
                where mod.Id = in_moduleId;
		loc_lockInterval := 10000 * loc_lockInterval;
    else
        loc_lockInterval := in_lockInterval;
	end if;

	-- update lockers
	Update Locker 
    set CreationDate = in_currentDate, LockInterval = ( case when LockInterval <> -1 then loc_lockInterval else -1 end)
	where Locker.Id in (
		--select dloc.LockerId from LockerToDocumentRel dloc where dloc.DocumentId = in_documentId
		--union
		select bloc.LockerId from LockerToBatchRel bloc where bloc.DocumentId = in_batchId) and
		Locker.UserId = in_userId and Locker.ModuleId = in_moduleId;

    --temp := SQL%ROWCOUNT ;

  --dbms_output.put_line(temp);

	if SQL%ROWCOUNT = 0
	then

		-- delete old lockers
		delete from Locker where Locker.Id in (
		--select dloc.LockerId from LockerToDocumentRel dloc where dloc.DocumentId = in_documentId
		--union
		select bloc.LockerId from LockerToBatchRel bloc where bloc.DocumentId = in_batchId);

		-- set new locker
        insert into Locker (Id, UserId, ModuleId, CreationDate, LockInterval, ProcessId)
        values (LOCKER_ID_SEQ.nextval, in_userId, in_moduleId, in_currentDate, loc_lockInterval, null)
        returning Id into lastInsertedId;

        --if(in_documentId <> -1)
        --then
        --    insert into LockerToDocumentRel (DocumentId, LockerId) values (in_documentId, lastInsertedId);
        --end if;

        if(in_batchId <> -1) 
        then
            insert into LockerToBatchRel (DocumentId, LockerId) values (in_batchId, lastInsertedId);
        end if;
        activeLockerId := lastInsertedId;
	end if;
 
    -- update batch
    update batch
    set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
    where id = in_batchId;
    
	-- update document
	update Document
	set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) 
	where id = in_documentId;
    
    -- update loggedInUser
    update LoggedInUser
    set LastActivityDate = sysdate
    where UserId = in_userId AND ModuleId = in_moduleId;

  end if;
    open c_result for
    select activelockerid as ID from dual;
	res:= c_result;

    COMMIT;
else
  
  ROLLBACK;
  -- raise error "batch has been locked"
  raise_application_error (-20010,' Error-20010. An exception raised by the stored procedure sp_TouchDocumentEx --> The batch with ID: ' || TO_CHAR(in_batchId) || ' could not be loaded because it is currently locked. ');
  
end if;

END SP_TOUCHDOCUMENT;
/
create or replace
PROCEDURE SP_GETBATCHCOUNT 
(
  in_job_id IN NUMBER  
, in_states IN VARCHAR2
, userId IN NUMBER
, needCheckExternalGroup IN NUMBER
, res OUT sys_refcursor
) AS
c_resQuery varchar2(1500);
c_whereQuery varchar2(1050);
c_result sys_refcursor;
BEGIN
  c_whereQuery := GETUSEREXTERNALGROUPCHECKCODE(case when needCheckExternalGroup=1 then userId else -1 end);
  if (length(c_whereQuery) > 0) then c_whereQuery := ' AND (' || c_whereQuery || ')'; end if;
  
  if (in_states is not null and length(in_states)>0) THEN 
    c_whereQuery:= c_whereQuery || ' AND Batch.State IN (' || in_states || ')';
  end if;
  
  c_resQuery := '
    SELECT COUNT(Batch.Id) as Value
    FROM Batch
    INNER JOIN JobToBatchRel on JobToBatchRel.BatchId=Batch.Id
    WHERE JobToBatchRel.JobId=' || in_job_id || c_whereQuery;
  
  open c_result for c_resQuery;
  res := c_result;
END SP_GETBATCHCOUNT;
/
create or replace
PROCEDURE SP_GETDOCUMENTCOUNT 
(
in_job_id IN NUMBER  
, in_states IN VARCHAR2
, userId IN NUMBER
, needCheckExternalGroup IN NUMBER
, res OUT sys_refcursor
) AS 
c_resQuery varchar2(2000);
c_whereQuery varchar2(1500);
c_result sys_refcursor;
BEGIN
  c_whereQuery := GETUSEREXTERNALGROUPCHECKCODE(case when needCheckExternalGroup=1 then userId else -1 end);
  if (length(c_whereQuery) > 0) then c_whereQuery := ' AND ' || c_whereQuery; end if;
  if (in_states is not null and length(in_states)>0) THEN 
    c_whereQuery:= c_whereQuery || ' AND Batch.State IN ('||in_states||')';
  end if;
  c_resQuery:='
    SELECT NVL(SUM(Batch.DocumentCount), 0) as Value
    FROM Batch
    INNER JOIN JobToBatchRel on JobToBatchRel.BatchId=Batch.Id
    WHERE JobToBatchRel.JobId=' || in_job_id || c_whereQuery;

open c_result for c_resQuery;
res := c_result;
END SP_GETDOCUMENTCOUNT;
/

--[VK 20100211] this table is no longer needed, sp_getbatchestoverify procedure has changed
--[VK 20101104] this table is used again. Used for GetNextInvalidBatch
DECLARE
v_count NUMBER :=0;
BEGIN
SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TMP_EXTBATCHITEMS' AND owner='TargetDBSchemaName';
IF v_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TMP_EXTBATCHITEMS';
END IF;
END;
/

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TMP_EXTBATCHITEMS
(
	ID NUMBER(10,0),
	NAME NVARCHAR2(256),
	ORIGINALID NVARCHAR2(32),
	PRIORITY NUMBER(10,0),
	TOTALDOCUMENTSCOUNT NUMBER(10,0),
	BATCHFOLDERSCOUNT NUMBER(10,0),
	STATE NUMBER(10,0),
	LASTACCESSEDDATE DATE,
	LASTUSER NVARCHAR2(256),
	LASTMODULE NVARCHAR2(256),
	CREATIONPROJECTID NUMBER(10,0),
	FILEPATH NVARCHAR2(256),
	ISLOCKED NUMBER(1,0),
	CLIENT NVARCHAR2(256),
	IMPORTDIRECTORYPATH NVARCHAR2(256),
	EXTERNALGROUPID NUMBER(10,0),
	TRANSACTIONID NUMBER(10,0),
	TRANSACTIONTYPE NUMBER(10,0),
	EXTERNALBATCHNAME NVARCHAR2(64)
) ON COMMIT DELETE ROWS ;

/************************* Index Optimizations *******************************/
DECLARE
v_count NUMBER :=0;
BEGIN

  /******* Foreignkey constraint - index. Issue (ORA-00060: Deadlock) *************/
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_DOCUMENT_ID' and TABLE_NAME='LOCKERTODOCUMENTREL' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_DOCUMENT_ID" ON "LOCKERTODOCUMENTREL" ("DOCUMENTID")';
  END IF;

  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_LOCKER_ID' and TABLE_NAME='LOCKERTODOCUMENTREL' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_LOCKER_ID" ON "LOCKERTODOCUMENTREL" ("LOCKERID")';
  END IF;
  
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_EXTERNALBATCHID' and TABLE_NAME='BATCH' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_EXTERNALBATCHID" ON "BATCH" ("EXTERNALBATCHID")';
  END IF;
  
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_BDDUSER_EXTERNALBATCHID' and TABLE_NAME='BDD_USERAUDIT' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_BDDUSER_EXTERNALBATCHID" ON "BDD_USERAUDIT" ("EXTERNALBATCHID")';
  END IF;
  
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_DOCUMENTPAGE_DISPLAYFILEID' and TABLE_NAME='DOCUMENTPAGE' and TABLE_OWNER='TargetDBSchemaName';
  IF V_COUNT = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_DOCUMENTPAGE_DISPLAYFILEID" ON "DOCUMENTPAGE" ("DISPLAYFILEID")';
  END IF;
  
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_DOCUMENTPAGE_ORIGINALFILEID' and TABLE_NAME='DOCUMENTFILE' and TABLE_OWNER='TargetDBSchemaName';
  IF V_COUNT = 0 THEN
  EXECUTE IMMEDIATE 'CREATE INDEX "IX_DOCUMENTPAGE_ORIGINALFILEID" ON "DOCUMENTFILE" ("ORIGINALFILEID")';
  END IF;
  
  /************************* Optimizations *******************************/
  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_BATCHSTATEIDPRIORITY' and TABLE_NAME='BATCH' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_BATCHSTATEIDPRIORITY ON BATCH ("STATE", "ID", "PRIORITY")';
  END IF;

  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_BATCHCATEGORYTOPROJECTREL' and TABLE_NAME='BATCH' and TABLE_OWNER='TargetDBSchemaName';
  IF V_COUNT = 0 THEN
  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_BATCHCATEGORYTOPROJECTREL ON BATCH ("ID", "CREATIONPROJECTID")';
  END IF;

  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_LASTACCESSEDDATESTATEID' and TABLE_NAME='BATCH' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_LASTACCESSEDDATESTATEID ON BATCH ("LASTACCESSEDDATE", "ID", "STATE")';
  END IF;

  SELECT count(*) into v_count FROM user_indexes WHERE INDEX_NAME = 'IX_DOCUMENT_DOCUMENTCLASSNAME' and TABLE_NAME='DOCUMENT' and TABLE_OWNER='TargetDBSchemaName';
  IF v_count = 0 THEN
  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_DOCUMENT_DOCUMENTCLASSNAME ON DOCUMENT ("ID", "DOCUMENTCLASSNAMEID")';
  END IF;

END;
/

DECLARE
  V_COUNT NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name = 'DOCUMENTPAGE' AND column_name = 'DISPLAYFILEID';
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTPAGE" ADD ("DISPLAYFILEID" NUMBER(10,0) NULL)';
    EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTPAGE" ADD CONSTRAINT "FK_DOCUMENTPAGE_DISPLAYFILE" FOREIGN KEY ("DISPLAYFILEID") REFERENCES "FILE_" ("ID") ENABLE';
  END IF;
END;
/

DECLARE
  V_COUNT NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name = 'DOCUMENTFILE' AND column_name = 'ORIGINALFILEID';
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTFILE" ADD ("ORIGINALFILEID" NUMBER(10,0) NULL)';
    EXECUTE IMMEDIATE 'ALTER TABLE "DOCUMENTFILE" ADD CONSTRAINT "FK_DOCUMENTFILE_ORIGINALFILE" FOREIGN KEY ("ORIGINALFILEID") REFERENCES "FILE_" ("ID") ENABLE';
  END IF;
END;
/

/************* Global temporary table TT_TMPBATCH *****************************/
DECLARE
v_count NUMBER :=0;
BEGIN  
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TT_TMPBATCH' AND owner='TargetDBSchemaName';
  IF v_count = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPBATCH';
  END IF;
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TT_TMPBATCHFOLDER' AND owner='TargetDBSchemaName';
  IF v_count = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPBATCHFOLDER';
  END IF;
  SELECT COUNT(*) INTO V_COUNT FROM ALL_TABLES WHERE TABLE_NAME='TT_TMPDOCS' AND OWNER='TargetDBSchemaName';
  IF v_count = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPDOCS';
  END IF;
  SELECT COUNT(*) INTO V_COUNT FROM ALL_TABLES WHERE TABLE_NAME='TT_TMPDOCFILE' AND OWNER='TargetDBSchemaName';
  IF v_count = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPDOCFILE';
  END IF;
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TT_TMPWDCFILE' AND owner='TargetDBSchemaName';
  IF v_count = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPWDCFILE';
  end if;  
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TT_TMPDOCPAGE' AND owner='TargetDBSchemaName';
  if V_COUNT = 1 then
      execute immediate 'DROP TABLE TargetDBSchemaName.TT_TMPDOCPAGE';
  END IF; 
  SELECT COUNT(*) INTO v_count FROM all_tables WHERE table_name='TT_TMPDOCPAGEFILE' AND owner='TargetDBSchemaName';
  IF V_COUNT = 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE TargetDBSchemaName.TT_TMPDOCPAGEFILE';
  end if; 

END;
/
CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPBATCH 
   (	
    "BATCHID" NUMBER(10,0)
   ) ON COMMIT DELETE ROWS;
   
CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPBATCHFOLDER
   (	
    "BATCHFOLDERID" NUMBER(10,0)
   ) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPDOCS
   (	
    "DOCID" NUMBER(10,0)
   ) on commit delete rows;

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPDOCFILE
   (	
    "DOCFILEID" NUMBER(10,0)
   ) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPWDCFILE
   (	
    "WDCFILEID" NUMBER(10,0)
   ) on commit delete rows;

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPDOCPAGEFILE
   (	
    "DOCPAGEFILEID" number(10,0)
   ) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE TargetDBSchemaName.TT_TMPDOCPAGE
   (	
    "DOCPAGEID" number(10,0)
   ) ON COMMIT DELETE ROWS;


/****** Object:  StoredProcedure [dbo].[sp_CleanupObsoleteData]    Script Date: 05/25/2011 13:31:07 ******/
CREATE OR REPLACE
PROCEDURE sp_CleanupObsoleteData
(
  v_TimeFrameInDays IN NUMBER DEFAULT NULL ,
  v_sJobName IN VARCHAR2 DEFAULT NULL ,
  v_inState IN NUMBER DEFAULT NULL 
)
AS
   v_d_lastAccessDate DATE;
   V_IJOBID NUMBER(10,0);
   v_sTime TIMESTAMP;
   v_stTime TIMESTAMP;
   V_ETIME TIMESTAMP;
   V_DUR NUMBER(10,0);
   v_iCnt Number(10,0);
   

BEGIN
   v_d_lastAccessDate := sqlserver_utilities.dateadd('DAY', -v_TimeFrameInDays, CURRENT_DATE) ;
   SELECT id 
     INTO v_iJobId
     FROM Job 
    WHERE NAME = v_sJobName;
   v_stTime := SYSTIMESTAMP ;
   DBMS_OUTPUT.PUT_LINE('Start time: ' || CAST(v_stTime AS VARCHAR2));
   
   ----------------------------
   --  FILE vs. DocumentFile --
   ----------------------------			
   v_sTime := SYSTIMESTAMP ;
   INSERT INTO tt_tmpDocFile
     ( SELECT F.Id DocFileID  
       FROM BATCH B
              JOIN JobToBatchRel JtBR
               ON JtBR.BatchId = B.Id
              JOIN BatchFolder BF
               ON B.Id = BF.BatchId
              JOIN DOCUMENT D
               ON BF.Id = D.BatchFolderId
              JOIN DocumentFile DF
               ON D.ID = DF.DocumentId
              JOIN FILE_ F
               ON DF.FileId = F.Id
        WHERE B.STATE = V_INSTATE
                AND TRUNC(B.LASTACCESSEDDATE) <= V_D_LASTACCESSDATE
                AND JTBR.JOBID = V_IJOBID );
    select count (DocFileID) into V_ICNT from  tt_tmpDocFile;           
 
   V_ETIME := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Create #tmpDocFile, insert into tt_tmpDocFile,  Duration, s:' || CAST(v_dur AS VARCHAR2));
   
   ----------------------------
   --	FILE vs. Document ------
   ----------------------------
   v_sTime := SYSTIMESTAMP ;
   INSERT INTO tt_tmpWDCFile
     ( SELECT F.Id WDCFileID  
       FROM BATCH B
              JOIN JobToBatchRel JtBR
               ON JtBR.BatchId = B.Id
              JOIN BatchFolder BF
               ON B.Id = BF.BatchId
              JOIN DOCUMENT D
               ON BF.Id = D.BatchFolderId
              JOIN FILE_ F
               ON D.FileId = F.Id
        WHERE B.STATE = V_INSTATE
                AND TRUNC(B.LastAccessedDate) <= v_d_lastAccessDate
                AND JTBR.JOBID = V_IJOBID );
    SELECT COUNT (WDCFILEID) INTO V_ICNT FROM  tt_tmpWDCFile;           

   V_ETIME := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (V_ETIME - V_STIME) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Create #tmpWDCFile, insert into tt_tmpWDCFile,  Duration, s:' || CAST(v_dur AS VARCHAR2));
   
   ----------------------------
   --  BatchFolder
   ----------------------------	
   v_sTime := SYSTIMESTAMP ;
   INSERT INTO tt_tmpBatchFolder
     ( SELECT BF.Id BatchFolderID  
       FROM BATCH B
              JOIN JobToBatchRel JtBR
               ON JtBR.BatchId = B.Id
              JOIN BatchFolder BF
               ON B.Id = BF.BatchId
        WHERE B.STATE = V_INSTATE
                AND TRUNC(B.LastAccessedDate) <= v_d_lastAccessDate
                AND JTBR.JOBID = V_IJOBID );

    SELECT COUNT (BATCHFOLDERID) INTO V_ICNT FROM  tt_tmpBatchFolder;           

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Create #tmpBatchFolder, insert into tt_tmpBatchFolder,  Duration, s:' || CAST(v_dur AS VARCHAR2));
   
   ----------------------------
   --  Batch
   ----------------------------	
   v_sTime := SYSTIMESTAMP ;
   INSERT INTO tt_tmpBatch
     ( SELECT B.Id BatchID  
       FROM BATCH B
              JOIN JOBTOBATCHREL JTBR
               ON JtBR.BatchId = B.Id
        WHERE B.STATE = V_INSTATE
                AND TRUNC(B.LastAccessedDate) <= v_d_lastAccessDate
                AND JTBR.JOBID = V_IJOBID );

    SELECT COUNT (BATCHID) INTO V_ICNT FROM  tt_tmpBatch;           

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Create #tmpBatch, insert into tt_tmpBatch,  Duration, s:' || CAST(v_dur AS VARCHAR2));
   
   ----------------------------
   -- DELETE
   ----------------------------
   
   ----------------------------------------------------
   --  Delete from FILE (cascade with Document -> ON) 
   ----------------------------------------------------
   V_STIME := SYSTIMESTAMP ;
   DELETE FROM FILE_ F
   WHERE EXISTS
    ( 
      SELECT * FROM TT_TMPWDCFILE TWDCF
      WHERE F.ID = TWDCF.WDCFILEID
    );

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Delete from FILE (->cascade with Document). Duration, s:' || CAST(v_dur AS VARCHAR2));

   ----------------------------------------------------
   --  Delete from FILE (cascade with DocumentFile -> ON) 
   ----------------------------------------------------
   V_STIME := SYSTIMESTAMP ;
   DELETE FROM FILE_ F
   WHERE EXISTS 
    (
      SELECT *  FROM TT_TMPDOCFILE TDF
      WHERE F.ID = TDF.DOCFILEID 
    );

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Delete from FILE (->cascade with DocumentFile ->cascade with DocumentPage). Duration, s:' || CAST(v_dur AS VARCHAR2));   
   ----------------------------------------------------
   --  Delete from BATCHFOLDER 
   ----------------------------------------------------
   V_STIME := SYSTIMESTAMP ;
   DELETE FROM BATCHFOLDER BF
   WHERE EXISTS 
    ( 
      SELECT * FROM TT_TMPBATCHFOLDER TBF
      WHERE BF.ID = TBF.BATCHFOLDERID 
    );

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Delete from BatchFolder. Duration, s:' || CAST(v_dur AS VARCHAR2));
   
   ----------------------------------------------------
   --  Delete from BATCH
   ----------------------------------------------------
   V_STIME := SYSTIMESTAMP ;
   DELETE FROM BATCH B
   WHERE EXISTS
    ( 
      SELECT * FROM TT_TMPBATCH TB
      WHERE B.ID = TB.BATCHID 
    );

   v_eTime := SYSTIMESTAMP ;
   V_DUR := EXTRACT(SECOND FROM (v_eTime - v_sTime) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Delete from Batch. Duration, s:' || CAST(v_dur AS VARCHAR2));
   -------------------------------------------------------------------------------
   
   v_eTime := SYSTIMESTAMP ;
   DBMS_OUTPUT.PUT_LINE('End time: ' || CAST(v_eTime AS VARCHAR2));
   V_DUR := EXTRACT(SECOND FROM (V_ETIME - V_STTIME) DAY TO SECOND);
   DBMS_OUTPUT.PUT_LINE('Duration, s: ' || CAST(V_DUR AS VARCHAR2));
END sp_CleanupObsoleteData;
/



create or replace
PROCEDURE SP_GETBATCHESTOVERIFY 
(
  jobId IN INT  
, sortDir IN NVARCHAR2  
, sortColumn IN NVARCHAR2  
, states IN NVARCHAR2  
, currentDate IN NUMBER  
, userId IN NUMBER
, needCheckExternalGroup IN NUMBER
, res OUT sys_refcursor
) AS 
c_resQuery varchar2(1500);
c_result sys_refcursor;
BEGIN
  
  c_resQuery := GET_EXTBATCHES_QUERY(jobId, currentDate, sortColumn, sortDir, states, 0, userId, needCheckExternalGroup);
    
  open c_result for c_resQuery;
  res := c_result;
  
END SP_GETBATCHESTOVERIFY;
/

create or replace
PROCEDURE SP_GETBATCHFOLDERSCOUNT
(
  batchId IN NUMBER  
, docStates IN VARCHAR2
, res OUT sys_refcursor
) AS
docQuery varchar2(70);
stateQuery varchar2(1050);
c_resQuery varchar2(1500);
c_result sys_refcursor;
BEGIN
   if (docStates is not null and length(docStates)>0) then
		docQuery := 'right join Document on Document.BatchFolderId = BatchFolder.Id';
		stateQuery := ' and Document.State in (' || docStates || ')';
   else
		docQuery := '';
		stateQuery := '';
   end if;
   c_resQuery := 'select count(distinct(BatchFolder.Id)) as VALUE from BatchFolder ' ||
		docQuery || 
		' where BatchFolder.BatchId =' || to_char(batchId) ||
		stateQuery;
open c_result for c_resQuery;
res := c_result;
END SP_GETBATCHFOLDERSCOUNT;
/

create or replace
FUNCTION GET_BATCHESEXTSQL_QUERY 
(
  jobId IN NUMBER,
  userId IN NUMBER,
  currentDate IN NUMBER,
  states IN VARCHAR2,
  whereQuery IN VARCHAR2,
  orderQuery IN VARCHAR2,
  needSorting IN NUMBER,
  forceFullLockCheck IN NUMBER
) RETURN VARCHAR2 AS 
res_query VARCHAR2(4000);
sortCondition VARCHAR2(550);
whereCondition VARCHAR2(2550);
BEGIN
  sortCondition := '';
  if (needSorting > 0) then
    if (orderQuery is null or orderQuery='') then 
      sortCondition := 'Batch.Priority asc, Batch.Id asc';
    else 
      sortCondition := orderQuery; 
    end if;
  end if;
  
  whereCondition := GetUserExternalGroupCheckCode(userId);
  if (wherecondition is not null) then 
    if length(replace(wherecondition, ' ', '')) > 0 then 
      wherecondition := 'WHERE ' || wherecondition; 
    end if;
  end if;

  if ((states is not null) and LENGTH(replace(states, ' ', '')) > 0)  then
    if (wherecondition is not null) and (length(replace(wherecondition, ' ', '')) > 0) then 
      wherecondition := wherecondition || ' AND Batch.State IN (' || states || ')';
    else
      wherecondition := 'WHERE Batch.State IN (' || states || ')';                 
    end if;
  end if;
    
  if (wherequery is not null and length(replace(wherequery,' ','')) > 0)  then  
      if whereCondition is not null and LENGTH(replace(whereCondition, ' ', '')) > 0 then 
        wherecondition := wherecondition || ' AND (' || wherequery || ')';
      else 
        wherecondition := 'WHERE ' || wherequery; 
      end if;
  end if;
    
  if (forceFullLockCheck != 0) then
    if (whereCondition is not null and length(replace(whereCondition, ' ', '')) > 0) then 
      whereCondition := whereCondition || ' AND IS_BATCH_LOCKED(-1, Batch.Id, ' || userId || ', -1, ' || currentDate || ')=0';
    else
      whereCondition := 'WHERE IS_BATCH_LOCKED(-1, Batch.Id, ' || userId || ', -1, ' || currentDate || ')=0';
    end if;
  end if;
    
  if (needSorting > 0) then
  sortCondition:=',row_number() over(order by ' || sortCondition || ' ) as RowNumber';
  whereCondition := whereCondition || '  order by RowNumber ASC';
  end if;
      
  res_query := '
  select
             Batch.Id 
            ,Batch.Name
            ,Batch.OriginalId
            ,Batch.Priority
            ,Batch.DocumentCount as TotalDocumentsCount
            ,Batch.FolderCount as BatchFoldersCount
            ,Batch.State
            ,Batch.LastAccessedDate 
            ,TRIM(CHR(32) FROM NVL2(User_.FirstName, User_.FirstName || CHR(32), null) || User_.LastName) AS LastUser 
            ,Module.Name AS LastModule
            ,Batch.CreationProjectId
            ,Project.FilePath  
            ,NVL(LockerToBatchRel.LockerId/LockerToBatchRel.LockerId, 0)  AS IsLocked
            ,NVL2(Batch.Client, Batch.Client, CHR(32)) AS Client
            ,Batch.ImportDirectoryPath
            ,Batch.ExternalGroupId
            ,Batch.TransactionId
            ,Batch.TransactionType
            ,BDD_ExternalBatch.Name as ExternalBatchName
            ' || sortCondition || '
            FROM Batch  
            LEFT JOIN LockerToBatchRel on LockerToBatchRel.DocumentId=Batch.Id
            INNER JOIN JobToBatchRel on JobToBatchRel.BatchId=Batch.Id and JobToBatchRel.JobId=' || jobId || '
            LEFT JOIN User_ on User_.Id=Batch.LastUserId
            LEFT JOIN Module on Module.Id=Batch.LastModuleId
            LEFT JOIN Project on Project.Id=Batch.CreationProjectId
            LEFT JOIN BDD_ExternalBatch on BDD_ExternalBatch.Id=Batch.ExternalBatchId
          ' || whereCondition;
  
  RETURN res_query;
END GET_BATCHESEXTSQL_QUERY;
/

create or replace
FUNCTION GET_CORRECT_CONDITION
(
  typecondition in number, -- 0 - sortquery; 1 - wherequery
  inQuery IN VARCHAR2 
) return varchar2 as 
res_query VARCHAR2(2550);
strquery varchar2(2550);
strnewfiltercln varchar2(250);
strfiltervalue varchar2(250);
strnewfiltervalue varchar2(550);
inidxfv1 int;
inidxfv2 int;
idxlastaccesseddate INT;
bUseLikeOp boolean;
strtmpquery  VARCHAR2(2550);
strLastPartOfDateFilter  VARCHAR2(2550);
strBckpLPDF VARCHAR2(2550);
strtmp1 varchar2(850);
strCurrTSFormat VARCHAR2(100);
begin
  case typecondition 
    when 0 then
      strQuery := upper(inQuery);
      strQuery :=  replace(strQuery, '[USER]', '[USER_]');
      strQuery :=  replace(replace(strQuery, '[', ''), ']', '');  
      strQuery :=  replace(strQuery, '+', '');
      strQuery :=  replace(strQuery, ''' ''', '');      
      if instr(REPLACE(strQuery,' ',''), 'USER_.LASTNAME)ASC') > 0 then
        STRQUERY :=  REPLACE(STRQUERY, '(USER_.FIRSTNAME', ' USER_.FIRSTNAME ASC, ');
        STRQUERY :=  REPLACE(STRQUERY, 'USER_.LASTNAME) ASC', ' USER_.LASTNAME ASC');
      elsif  instr(REPLACE(strQuery,' ',''), 'USER_.LASTNAME)DESC') > 0  then
        STRQUERY :=  REPLACE(STRQUERY, '(USER_.FIRSTNAME',' USER_.FIRSTNAME DESC, ');
        strQuery :=  replace(strQuery, 'USER_.LASTNAME) DESC','USER_.LASTNAME DESC');
      END IF;
      --[RO][08.08.2011][ALM 7280 - WVC-sorting on the LastUser/LastModule column]
      IF instr(REPLACE(strQuery,' ',''), 'USER_.FIRSTNAMEUSER_.LASTNAME,MODULE.NAMEDESC') > 0 THEN
        STRQUERY :=  REPLACE(STRQUERY, 'USER_.FIRSTNAME', ' USER_.FIRSTNAME DESC, ');
        STRQUERY :=  REPLACE(STRQUERY, 'USER_.LASTNAME', ' USER_.LASTNAME DESC ');
      END IF;
      IF instr(REPLACE(strQuery,' ',''), 'USER_.FIRSTNAMEUSER_.LASTNAME,MODULE.NAMEASC') > 0 THEN
        STRQUERY :=  REPLACE(STRQUERY, 'USER_.FIRSTNAME', ' USER_.FIRSTNAME ASC, ');
        STRQUERY :=  REPLACE(STRQUERY, 'USER_.LASTNAME', ' USER_.LASTNAME ASC ');
      END IF;      
      res_query:=strQuery;      
    when 1 then   
      strQuery := inQuery;
      STRQUERY :=  REPLACE(STRQUERY, '[USER]', '[USER_]');
      -- [RO][08.08.2011]
      -- [In the case where the FirstName is empty, the space before the LastName is unnecessary. This results to the wrong result of the query. The following correction fixes this error.] 
      strQuery := REPLACE(strQuery, '[USER_].[FirstName]', '[USER_].FirstName'); 
      strQuery := REPLACE (strQuery, '[USER_].FirstName + '' ''', 'NVL2(USER_.FirstName, USER_.FirstName || CHR(32), null)');
      strQuery :=  replace(strQuery, '+', '||');
      STRQUERY :=  REPLACE(REPLACE(STRQUERY, '[', ''), ']', '');

      IF INSTR(UPPER(STRQUERY), 'BATCH.CLIENT') > 0 THEN
        STRQUERY :=  REPLACE(UPPER(STRQUERY), 'BATCH.CLIENT', 'NVL2(BATCH.CLIENT, BATCH.CLIENT, '' '')');
      END IF;
      
      strCurrTSFormat := '''DD.MM.YYYY HH24:MI:SS''';  
      
      strtmpquery := strquery;      
      while (instr(upper(strtmpquery), 'BATCH.LASTACCESSEDDATE', -1) > 0)
      loop
        
        idxlastaccesseddate := instr(upper(strtmpquery), 'BATCH.LASTACCESSEDDATE', -1);
        inidxfv1 := instr(strtmpquery, '''',  (idxlastaccesseddate+1)); 
        inidxfv2 := instr(strtmpquery, '''',  (inidxfv1+1)); 
        strfiltervalue := substr(strtmpquery, inidxfv1, inidxfv2 - inidxfv1 + 1);
        strlastpartofdatefilter := substr (strtmpquery, idxlastaccesseddate-1, inidxfv2);
        strtmpquery := substr (strquery, 1, idxlastaccesseddate-1);
        
        strBckpLPDF:= strlastpartofdatefilter;
        
        IF (instr(substr(strquery, idxlastaccesseddate, inidxfv1 - idxlastaccesseddate), 'LIKE', idxlastaccesseddate)>0) THEN
          bUseLikeOp := true;
          strnewfiltervalue := strfiltervalue;
          strnewfiltercln := 'to_char(from_tz(to_timestamp(to_char(batch.lastaccesseddate,' || strCurrTSFormat || '), ' || strCurrTSFormat || '), dbtimezone) at time zone sessiontimezone,' || strCurrTSFormat || ')';
        ELSE  
            bUseLikeOp:= FALSE;
            strfiltervalue :=  REPLACE(REPLACE (strfiltervalue, ' A.M.', 'AM'), 'A.M.', 'AM');
            strfiltervalue := REPLACE(REPLACE (strfiltervalue, ' P.M.', 'PM'), 'P.M.', 'PM');
            IF ((instr(strfiltervalue, 'AM') > 0) OR (instr(strfiltervalue, 'PM') > 0)) THEN
              if instr (strlastpartofdatefilter, 'A.M.') > 0 then
                strlastpartofdatefilter := REPLACE(REPLACE(strlastpartofdatefilter, ' A.M.', 'AM'), 'A.M.', 'AM');
              elsif (instr (strlastpartofdatefilter, 'P.M.') > 0) THEN
                strlastpartofdatefilter := REPLACE(REPLACE(strlastpartofdatefilter, ' P.M.', 'PM'), 'P.M.', 'PM');
              END IF; 
              strnewfiltervalue := 'to_timestamp(to_char(sys_extract_utc(to_timestamp(' || strfiltervalue || ', ''DD.MM.YYYY HH12:MI:SSPM'')), ''DD.MM.YYYY HH12:MI:SSPM''), ''DD.MM.YYYY HH12:MI:SSPM'')';
              strnewfiltercln := 'to_timestamp(to_char(batch.lastaccesseddate, ''DD.MM.YYYY HH12:MI:SSPM''), ''DD.MM.YYYY HH12:MI:SSPM'')';
            else
              strnewfiltervalue := 'to_timestamp(to_char(sys_extract_utc(to_timestamp(' || strfiltervalue || ', ' || strCurrTSFormat || ')), ' || strCurrTSFormat || '), ' || strCurrTSFormat || ')';
              strnewfiltercln := 'to_timestamp(to_char(batch.lastaccesseddate, ' || strCurrTSFormat || '), ' || strCurrTSFormat || ')';            
            END IF; 
        END IF;          
        IF (LENGTH(strfiltervalue) < 19) AND (not bUseLikeOp) AND (NOT((instr(strlastpartofdatefilter, '>') > 0) OR (instr(strlastpartofdatefilter, '<') > 0))) THEN  
          strnewfiltercln := 'to_char(from_tz(to_timestamp(to_char(batch.lastaccesseddate,' || strCurrTSFormat || '), ' || strCurrTSFormat || '), dbtimezone) at time zone sessiontimezone,' || strCurrTSFormat || ')';
          strnewfiltervalue := 'to_char(from_tz(to_timestamp(to_char(sys_extract_utc(to_timestamp(' || strfiltervalue || ', ' || strcurrtsformat || ')), ' || strcurrtsformat || '), ' || strcurrtsformat || '), dbtimezone) at time zone sessiontimezone, ' || strcurrtsformat || ')'; 
          strtmp1 := substr(strquery, idxlastaccesseddate, inidxfv2-idxlastaccesseddate);
          strlastpartofdatefilter:=REPLACE(strlastpartofdatefilter, strtmp1, REPLACE (strtmp1, '=', ' LIKE '));          
          strnewfiltervalue := 'rtrim(trim(trailing ''0'' from replace (replace (' || strnewfiltervalue || ', '':00''), ''00,'' )))' || '|| ''%''' ;
        END IF;
        strlastpartofdatefilter := REPLACE(strlastpartofdatefilter, strfiltervalue, strnewfiltervalue);         
        strlastpartofdatefilter := REPLACE(strlastpartofdatefilter, 'BATCH.LASTACCESSEDDATE', strnewfiltercln);
        strQuery := REPLACE(strQuery, strBckpLPDF, strlastpartofdatefilter);
        strlastpartofdatefilter := '';
        strBckpLPDF:='';
        strnewfiltercln:= '';
        strnewfiltervalue:='';
        strfiltervalue:= '';
        strtmp1:='';
        
      end loop;

      IF INSTR(UPPER(REPLACE(STRQUERY,CHR(32),'')), '||''''||') > 0 THEN
        RES_QUERY:= '((' || STRQUERY || ') OR (' || REPLACE(STRQUERY, '|| '' '' ||', '||') || '))'; 
      ELSE
        RES_QUERY:= STRQUERY;
      END IF;
  end case;
  return res_query;
end get_correct_condition;
/


CREATE OR REPLACE
TYPE T_IN_LIST_TAB AS TABLE OF INTEGER;
/

/****** Stored Procedure SP_GETNEXTBATCHTOVERIFY *****************************/
create or replace
PROCEDURE SP_GETNEXTBATCHTOVERIFY
(
  jobId IN NUMBER
, currentBatchId IN NUMBER
, states IN VARCHAR2
, currentDate IN NUMBER
, userId IN NUMBER
, existingLockerId IN NUMBER
, moduleId IN NUMBER
, lockInterval IN NUMBER
, needCheckExternalGroup IN NUMBER
, whereQuery IN VARCHAR2
, orderQuery IN VARCHAR2
, res OUT sys_refcursor
) AS
strwherequery varchar2(550);
strorderquery varchar2(550);
sortcondition varchar2(550); 
wherecondition varchar2(2000); 
c_query varchar2(4000);
c_batchId number;
c_touchDocumentReturn sys_refcursor;
c_result sys_refcursor;
c_refcur sys_refcursor;
l_tab t_in_list_tab := t_in_list_tab();
c_row tmp_extbatchitems%rowtype;
c_lockerId number;
c_lockerIdOut number;
c_useridforsqlquery number;
C_CNT int :=0;
V_INDEX BINARY_INTEGER;
c_isLocked int :=0;
STRJOINEXTENSION VARCHAR(550) := ' '; 
STRCLRWHERE VARCHAR(2000) := ' '; -- [AE] [2013-02-01] Increased 550 to 2000: in case "states" list is too long, the "if" checks below would fail, because the "STRCLRWHERE" would be cut
STRCLRORDER VARCHAR(550) := ' ';
BEGIN

  strwherequery := get_correct_condition (1, wherequery);
  strorderquery := get_correct_condition (0, orderquery);

  c_lockerId := -1;
  c_userIdForSqlQuery := (case when needCheckExternalGroup=1 then userId else -1 end);
  
  sortCondition := '';
  if (strorderQuery is null or strorderQuery='') then
    sortCondition := 'Batch.Priority asc, Batch.Id asc';
  else
    sortCondition := strorderQuery;
  end if;

  whereCondition := GetUserExternalGroupCheckCode(userId);
  if (wherecondition is not null) then
    if length(replace(wherecondition, ' ', '')) > 0 then
      wherecondition := 'WHERE ' || wherecondition;
    end if;
  end if;

  if ((states is not null) and LENGTH(replace(states, ' ', '')) > 0)  then
    if (wherecondition is not null) and (length(replace(wherecondition, ' ', '')) > 0) then
      wherecondition := wherecondition || ' AND Batch.State IN (' || states || ')';
    else
      wherecondition := 'WHERE Batch.State IN (' || states || ')';
    end if;
  end if;

  if (strwherequery is not null and length(replace(strwherequery,' ','')) > 0)  then
      if (whereCondition is not null) and (LENGTH(replace(whereCondition, ' ', '')) > 0) then
        wherecondition := wherecondition || ' AND (' || strwherequery || ')';
      else
        wherecondition := 'WHERE ' || strwherequery;
      end if;
  end if;
  
  if ((WHERECONDITION is not null) and (length(replace(WHERECONDITION, ' ', '')) > 0)) then
    wherecondition := wherecondition || ' AND Batch.Id <> ' || CAST(currentBatchId as varchar); 
  else
    wherecondition := 'WHERE Id <> ' || CAST(currentBatchId as varchar);
  end if;
  
  sortCondition:=', row_number() over(order by ' || sortCondition || ' ) as RowNumber';

  STRCLRWHERE := UPPER(REPLACE(REPLACE(WHERECONDITION, ']', ''), '[', ''));
  STRCLRORDER := UPPER(REPLACE(REPLACE(SORTCONDITION, ']', ''), '[', ''));

  IF ((INSTR(STRCLRWHERE, 'MODULE.NAME') > 0) OR (INSTR(STRCLRORDER, 'MODULE.NAME') > 0)) THEN
    STRJOINEXTENSION := STRJOINEXTENSION || 'LEFT JOIN Module on Module.Id = Batch.LastModuleId ';
  END IF;

  IF( ((INSTR(STRCLRWHERE, 'USER_.FIRSTNAME') > 0 ) OR (INSTR(STRCLRWHERE, 'USER_.LASTNAME') > 0 ) ) OR ((INSTR(STRCLRORDER, 'USER_.FIRSTNAME') > 0 ) OR (INSTR(STRCLRORDER, 'USER_.LASTNAME') > 0 ) ) )THEN
    --[RO][08.08.2011][ALM 10261 case insensitivity]
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI'''; 
    STRJOINEXTENSION := STRJOINEXTENSION || 'LEFT JOIN USER_ on USER_.Id = Batch.LastUserId ';
  END IF;

  IF ((INSTR(STRCLRWHERE, 'BDD_EXTERNALBATCH.NAME') > 0) OR (INSTR(STRCLRORDER, 'BDD_EXTERNALBATCH.NAME') > 0))THEN
    --[VK 20130129 Defect 12100, 12103] Join Batch and DBB_ExternalBatch on ExternalBatchId column
    STRJOINEXTENSION := STRJOINEXTENSION || 'LEFT JOIN BDD_ExternalBatch on BDD_ExternalBatch.Id = Batch.ExternalBatchId ';
  END IF;

  C_QUERY := '    
    select Id 
    from (SELECT /*+ NOPARALLEL(Batch) */ Batch.Id
          , ltb.DocumentId As LockerBatchId
          ' || sortCondition || '
        FROM Batch
          LEFT JOIN LockerToBatchRel ltb on ltb.DocumentId=Batch.Id
          INNER JOIN JobToBatchRel on JobToBatchRel.BatchId=Batch.Id and JobToBatchRel.JobId=' || CAST(jobId as varchar) || ' '
          || STRJOINEXTENSION
          || WHERECONDITION || '  
          order by RowNumber ASC) 
    where LockerBatchId is null AND RowNumber between 1 and 64'; --[RO][13.09.2011]

  execute immediate (C_QUERY)	
  bulk collect into L_TAB;
  
  v_index := l_tab.first;

  while v_index is not null loop
    begin
      c_batchid := l_tab(v_index);
    exception
      when no_data_found then c_batchid := -1;
      when others then exit;     
    end;
    if (c_batchid = -1) then exit; end if;
    begin
      sp_touchdocument(-1, c_batchid, userid, moduleid, currentdate, lockinterval, c_touchdocumentreturn, existinglockerid);
      fetch c_touchdocumentreturn into c_lockerId;
      close c_touchDocumentReturn;
      c_lockerIdOut := c_lockerId;
      exit;
    exception
      --batch is locked
      when others then v_index := l_tab.next(v_index);
    end;
  end loop;
 
  OPEN c_result FOR
  SELECT
    C_BATCHID id
    ,'thename' as Name 
		,null as OriginalId 
		,null as Priority 
		,null as TotalDocumentsCount 
		,null as BatchFoldersCount					
		,null as State
		,null as LastAccessedDate 
		,NULL as LastUser
		,NULL as LastModule
		,-1 as CreationProjectId
		,null as FILEPATH
		,C_ISLOCKED as IsLocked
		,null as Client 
		,null as ImportDirectoryPath
		,null as ExternalGroupId 
		,null as TransactionId 
		,null as TransactionType 
		,null externalbatchname
		,c_lockeridout lockerid
  FROM dual WHERE c_batchId != -1;

  res := c_result;

END SP_GETNEXTBATCHTOVERIFY;
/

----------------------------------- [VK 2010/11/03] ------------------------------------------
create or replace
PROCEDURE SP_SETBATCHSTATEPRIORITY(
    in_iJobId        IN INT,
    in_sBatchId      IN nvarchar2,
    in_iNewState     IN INT,
    in_iPriority     IN INT,
    in_iUserId       IN INT,
    in_iModuleId     IN INT,
    in_biCurrentDate IN NUMBER,
    res OUT sys_refcursor )
AS
  cv_1 sys_refcursor;
  iBatchId NUMBER;
BEGIN
  iBatchId := 0 ;
  SELECT ID
  INTO iBatchId
  FROM Batch
  WHERE OriginalId = in_sBatchId
  AND Id          IN
    ( SELECT BatchId FROM JobToBatchRel WHERE JobId = in_iJobId
    )
  AND ROWNUM    <= 1;
  IF ( iBatchId <= 0 ) THEN
    BEGIN
      ROLLBACK;
      OPEN cv_1 FOR SELECT 2
    AS
      VALUE FROM DUAL ;
    END;
  ELSE
    IF ( IS_BATCH_LOCKED(-1, iBatchId, -1, -1, in_biCurrentDate) = 0 ) THEN
      DECLARE
        dtCurrentDateTime DATE;
      BEGIN
        dtCurrentDateTime := SYS_EXTRACT_UTC(SYSTIMESTAMP) ;
        IF ( in_iNewState  > -1 AND in_iPriority > -1 ) THEN
          BEGIN
            UPDATE BATCH 
            SET STATE = in_iNewState,
                Priority = in_iPriority,
                LastUserId = in_iUserId,
                LastModuleId = in_iModuleId,
                LastAccessedDate = dtCurrentDateTime
            WHERE Batch.Id = iBatchId;
            UPDATE DOCUMENT
            SET STATE            = in_iNewState,
              Priority           = in_iPriority,
              LastUserId         = in_iUserId,
              LastModuleId       = in_iModuleId,
              LastAccessedDate   = dtCurrentDateTime
            WHERE BatchFolderId IN
              ( SELECT Id FROM BatchFolder WHERE BatchId = iBatchId
              );
          END;
        ELSE
          IF ( in_iNewState > -1 ) THEN
            BEGIN
              UPDATE BATCH
              SET State            = in_iNewState,
                  LastUserId       = in_iUserId,
                  LastModuleId     = in_iModuleId,
                  LastAccessedDate = dtCurrentDateTime
              WHERE Batch.Id       = iBatchId;            
              UPDATE DOCUMENT
              SET STATE            = in_iNewState,
                LastUserId         = in_iUserId,
                LastModuleId       = in_iModuleId,
                LastAccessedDate   = dtCurrentDateTime
              WHERE BatchFolderId IN
                ( SELECT Id FROM BatchFolder WHERE BatchId = iBatchId
                );
            END;
          ELSE
            IF ( in_iPriority > -1 ) THEN
              BEGIN
                UPDATE Batch
                SET Priority         = in_iPriority,
                    LastUserId       = in_iUserId,
                    LastModuleId     = in_iModuleId,
                    LastAccessedDate = dtCurrentDateTime
                WHERE Batch.Id       = iBatchId;
                UPDATE DOCUMENT
                SET Priority         = in_iPriority,
                  LastUserId         = in_iUserId,
                  LastModuleId       = in_iModuleId,
                  LastAccessedDate   = dtCurrentDateTime
                WHERE BatchFolderId IN
                  ( SELECT Id FROM BatchFolder WHERE BatchId = iBatchId
                  );
              END;
            END IF;
          END IF;
        END IF;
        OPEN cv_1 FOR SELECT 0 VALUE FROM DUAL ;
      END;
    ELSE
      BEGIN
        ROLLBACK;
        OPEN cv_1 FOR SELECT 1 VALUE FROM DUAL ;
      END;
    END IF;
  END IF;
  res:=cv_1;
END SP_SETBATCHSTATEPRIORITY;
/

create or replace
PROCEDURE SP_GETBATCHESEXT
(
  jobId IN NUMBER,
  maxOverallRecords IN NUMBER,
  pageIndex IN NUMBER,
    pageSize IN NUMBER,
    userId IN NUMBER,
    currentDate IN NUMBER,
    states IN VARCHAR2,
    whereQuery IN VARCHAR2,
    orderQuery IN VARCHAR2,
  res OUT sys_refcursor
) AS 
  strwherequery varchar2(2550);
  strorderquery varchar2(550); 
  res_query VARCHAR2(4000);
  pagingCondition VARCHAR2(100);
  c_result sys_refcursor;
begin

  strwherequery := GET_CORRECT_CONDITION (1, wherequery);
  strorderquery := GET_CORRECT_CONDITION (0, orderquery);
    
  if (maxOverallRecords != -1) then pagingCondition := ' where RowNumber < ' || (maxOverallRecords+1);
  else
      if (pageIndex >= 0 and pageSize > 0) then pagingCondition := ' where RowNumber between ' || (pageIndex*pageSize +1) || ' and ' || ((pageIndex+1)*pageSize);
      else pagingCondition := ' where RowNumber > 0'; end if;
  end if;
  res_query := GET_BATCHESEXTSQL_QUERY(jobId, userId, currentDate, states, strwherequery, strorderquery, 1, 0);
  res_query := '
  select 
    Id,
    Name,
    OriginalId,
    Priority,
    TotalDocumentsCount,
    BatchFoldersCount,
    State,
    LastAccessedDate,
    LastUser,
    LastModule,
    CreationProjectId,
    FilePath,
    IsLocked,
    Client,
    ImportDirectoryPath,
    ExternalGroupId,
    TransactionId,
    TransactionType,
    ExternalBatchName,
    -1 as LockerId
  from (' || res_query || ') ' || pagingCondition;
  
  open c_result for res_query;
  res := c_result;

END SP_GETBATCHESEXT;
/

create or replace
PROCEDURE SP_GETBATCHESEXTPAGECOUNT 
(
  jobId IN NUMBER,
  pageSize IN NUMBER,
  userId IN NUMBER,
  currentDate IN NUMBER,
  states IN VARCHAR2,
  whereQuery IN VARCHAR2,
  res OUT sys_refcursor
) as 
strwherequery varchar2(2550);
res_query VARCHAR2(4000);
c_result sys_refcursor;
BEGIN

  --[RO][22.09.2011][ALM 10083. - case insensitivity]
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI''';
  
  strwherequery := GET_CORRECT_CONDITION (1, wherequery);
  
  res_query := GET_BATCHESEXTSQL_QUERY(jobId, userId, currentDate, states, strwherequery, '', 0, 0);
  
  -- [AE] [2011-01-27] Let's count actual number of batches and not wrong number of pages
  res_query := 'select (count(*)) as VALUE from (' || res_query || ')';
  -- res_query := 'select (count(*)/' || pageSize || ')+1 as VALUE from (' || res_query || ')';
  
  open c_result for res_query;
  res := c_result;
  
END SP_GETBATCHESEXTPAGECOUNT;
/

create or replace
PROCEDURE SP_GETBATCHESINFO 
(
  jobId IN NUMBER  
, userId IN NUMBER  
, batchId IN NUMBER  
, currentDate IN NUMBER  
, states IN VARCHAR2  
, whereQuery IN VARCHAR2
, orderQuery IN VARCHAR2
, needDocCount IN NUMBER
, res OUT sys_refcursor
) AS 

c_result sys_refcursor;
c_resQuery varchar2(4000);
strwherequery varchar2(1000);
strorderquery varchar2(550); 

BEGIN

  --[RO][08.08.2011][ALM 10261. - case insensitivity]
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI''';

  strwherequery := GET_CORRECT_CONDITION (1, wherequery);
  strorderquery := GET_CORRECT_CONDITION (0, orderquery);
  
  c_resQuery := 'WITH Batch_CTE AS (' || GET_BATCHESEXTSQL_QUERY(jobId, userId, currentDate, states, strwherequery, strorderquery, 1, 0) || ')';

  c_resQuery := c_resQuery || '
    SELECT COUNT(RowNumber) as Value
    FROM Batch_CTE';
  
  IF needDocCount=1 THEN
    c_resQuery := c_resQuery || '
      UNION ALL
      SELECT nvl(SUM(TotalDocumentsCount),0) as Value
      FROM Batch_CTE
      HAVING NVL(SUM(TotalDocumentsCount),0)>0';
  END IF;

  c_resQuery := c_resQuery || '
      UNION ALL
      SELECT RowNumber-1 as VALUE 
      FROM Batch_CTE
      WHERE id='||batchId;  
  
  open c_result for c_resQuery;
  res := c_result;
  

END SP_GETBATCHESINFO;
/

create or replace
PROCEDURE SP_LOADNEWWORKDOC (
    in_documentId IN number,
    in_batchId IN number,
    in_userId IN number,
    in_moduleId IN number,
    in_currentDate IN number,
    res out sys_refcursor)
IS
  c_result sys_refcursor;
  docId number;
BEGIN
IF (IS_BATCH_LOCKED(in_documentId ,in_batchId ,in_userId ,in_moduleId ,in_currentDate)=0)
THEN
    docId := in_documentId;
    open c_result for 
    select f."ID",
			   f."VALUE" as "IMAGE",
			   doc.STATE as "STATE",
			   doc."NAME" as "NAME",
			   doc.CREATIONDATE as "CREATIONDATE"
		from "DOCUMENT" doc
		inner join FILE_ f on doc.FILEID = f."ID"
		where doc."ID" = in_documentId;
    
    res:= c_result;
    
    if(SQL%ROWCOUNT > 0)
    then
        -- update batch
        update "BATCH"
        set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
        where id = in_batchId;
        
        -- update document
        update "DOCUMENT"
        set LastUserId = in_userId, LastModuleId = in_moduleId, LastAccessedDate = SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
        where id = in_documentId;

        -- update loggedInUser
        update LoggedInUser
        set LastActivityDate = sysdate
        where UserId = in_userId AND ModuleId = in_moduleId;
	   
	COMMIT;
	end if;

ELSE
  
  ROLLBACK;
  -- raise error "batch has been locked"
  raise_application_error (-20011,' Error-20011. An exception raised by the stored procedure sp_LoadNewWorkdoc --> The batch with ID: ' || TO_CHAR(in_batchId) || ' could not be loaded because it is currently locked. ');
  
END IF;
END SP_LOADNEWWORKDOC;
/

--VladimirPerminov 11/23/2011 16654 Bugfix WVC opens a batch which is hidden by Filter condition
create or replace
PROCEDURE SP_CHECKISBATCHFILTERED
(
    jobId IN NUMBER,  
    userId IN NUMBER,
    currentDate IN NUMBER,
    states IN VARCHAR2,
    whereQuery IN VARCHAR2,
    currentBatchId IN NUMBER,
  res OUT sys_refcursor
) AS 
  res_query VARCHAR2(4000);
  whereQueryLocal VARCHAR2(1000);
  c_result sys_refcursor;
begin

   if (length(whereQuery) = 0) then 
    whereQueryLocal:= whereQuery || '(Batch.Id = ' || cast(currentBatchId as VARCHAR2) || ')';
		else whereQueryLocal:= '(' || whereQuery || ') AND ' || '(Batch.Id = ' || cast(currentBatchId as VARCHAR2) || ')';	
    end if;
  
  res_query := GET_BATCHESEXTSQL_QUERY(jobId, userId, currentDate, states, whereQueryLocal, 'Batch.Priority', 1, 0);
  res_query := '
  select 
    Id,
    Name,
    OriginalId,
    Priority,
    TotalDocumentsCount,
    BatchFoldersCount,
    State,
    LastAccessedDate,
    LastUser,
    LastModule,
    CreationProjectId,
    FilePath,
    IsLocked,
    Client,
    ImportDirectoryPath,
    ExternalGroupId,
    TransactionId,
    TransactionType,
    ExternalBatchName,
    -1 as LockerId
  from (' || res_query || ') ';
  
  open c_result for res_query;
  res := c_result;
  
END SP_CHECKISBATCHFILTERED;
/


CREATE OR REPLACE
PROCEDURE SP_DELETEBATCH(
  in_iJobId IN INT,
  in_sBatchId IN NVARCHAR2,
  in_bDeleteDocFiles IN NUMBER,
  in_biCurrentDate IN NUMBER,
  in_iHours IN INT,
  res OUT sys_refcursor)
AS
  cv_1 sys_refcursor;
  iBatchId NUMBER;

BEGIN

  iBatchId := 0;

  SELECT ID
  INTO iBatchId
  FROM Batch
  WHERE OriginalId = in_sBatchId
  and id in (
      SELECT BatchId
      FROM JobToBatchRel
      WHERE JobId = in_iJobId)
  AND ROWNUM <= 1
  for update skip locked;


  IF (iBatchId <= 0) THEN
    BEGIN
      ROLLBACK;
      OPEN cv_1 FOR SELECT 2 -- "batch not found"
    AS
      VALUE FROM DUAL ;
    END;
  ELSE IF (IS_BATCH_LOCKED(-1, iBatchId, -1, -1, in_biCurrentDate) = 0) THEN
  BEGIN
  
      INSERT INTO TT_TMPDOCS
       ( SELECT D.Id DocID
         FROM BatchFolder BF
              LEFT JOIN DOCUMENT D ON BF.Id = D.BatchFolderId
         where BF.BATCHID = IBATCHID);
  
      --  FILE vs. DocumentFile --
      IF (in_bDeleteDocFiles = 1) THEN
      BEGIN      

        INSERT INTO tt_tmpDocFile
           ( select F.id DOCFILEID
             from TT_TMPDOCS TMPDOCS
                  inner join DOCUMENTFILE DF on TMPDOCS.DOCID = DF.DOCUMENTID
                  LEFT JOIN FILE_ F ON (DF.FileId = F.Id) OR (DF.OriginalFileID = F.Id) );

        --  FILE vs. DocumentPage --
        INSERT INTO tt_tmpDocPageFile
          ( select F.id DOCPAGEFILEID
            from TT_TMPDOCS TMPDOCS
                INNER JOIN DOCUMENTFILE DF ON TMPDOCS.DOCID = DF.DOCUMENTID
                LEFT JOIN DOCUMENTPAGE DP ON DF.ID = DP.DOCUMENTFILEID
                LEFT JOIN FILE_ F ON DP.DISPLAYFILEID = F.ID);

      END;
      END IF;


      --	FILE vs. Document ------
      INSERT INTO tt_tmpWDCFile
       ( SELECT F.Id WDCFileID
            from TT_TMPDOCS TMPDOCS
            inner join document D on TMPDOCS.DOCID = D.id
            LEFT JOIN File_ F ON D.FileId = F.Id);

      -----------------------------------------------------
      ------------  DELETE --------------------------------
      -----------------------------------------------------

      --  Delete from FILE (cascade with DocumentFile -> ON)
      IF (in_bDeleteDocFiles = 1) THEN
      BEGIN
      
        --  Delete from FILE (DocumentPage.DisplayFileId)
        DELETE FROM FILE_ F
        WHERE EXISTS
        (
          SELECT * FROM TT_TMPDOCPAGEFILE TDP
          WHERE F.ID = TDP.DOCPAGEFILEID
        );
      
        DELETE FROM FILE_ F
        WHERE EXISTS
        (
          SELECT *  FROM TT_TMPDOCFILE TDF
          WHERE F.ID = TDF.DOCFILEID
        );
      END;
      END IF;

      --  Delete from FILE (cascade with Document -> ON)
      DELETE FROM FILE_ F
      WHERE EXISTS
      (
        SELECT * FROM TT_TMPWDCFILE TWDCF
        WHERE F.ID = TWDCF.WDCFILEID
      );

      DELETE FROM BatchFolder BF WHERE BF.BatchId = iBatchId;
      DELETE FROM Batch B WHERE B.ID = iBatchId;

      COMMIT;
      OPEN cv_1 FOR SELECT 0 VALUE FROM DUAL ; -- "batch has been successfully deleted"

  END;
  ELSE
  BEGIN
    ROLLBACK;
    OPEN cv_1 FOR SELECT 1 VALUE FROM DUAL ; -- "batch has been locked"
  END;
    END IF;
  END IF;
  res:=cv_1;

END SP_DELETEBATCH;
/



-- create sysnonyms for copiing of batches beetwen databases 
begin
 execute immediate 'create or replace public synonym TargetDBSchemaName_BATCH for TargetDBSchemaName.BATCH ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_BDD_EXTERNALBATCH for TargetDBSchemaName.BDD_EXTERNALBATCH ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_BATCHFOLDER for TargetDBSchemaName.BATCHFOLDER ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_JOB for TargetDBSchemaName.JOB ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_JOBTOBATCHREL for TargetDBSchemaName.JOBTOBATCHREL ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_DOCUMENT for TargetDBSchemaName.DOCUMENT ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_DOCUMENTFILE for TargetDBSchemaName.DOCUMENTFILE ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_DOCUMENTCLASS for TargetDBSchemaName.DOCUMENTCLASS ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_DOCUMENTCLASSNAME for TargetDBSchemaName.DOCUMENTCLASSNAME ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_DOCUMENTPAGE for TargetDBSchemaName.DOCUMENTPAGE ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_FILE_ for TargetDBSchemaName.FILE_ ';
 execute immediate 'create or replace public synonym TargetDBSchemaName_PROJECT for TargetDBSchemaName.PROJECT ';
end;
/

create or replace
procedure SP_COPYBATCHES(
	DBSOURCENAME in varchar2, 
	DBTARGETNAME in varchar2, 
  IN_JOBID in number := -1,
  IN_WHEREQUERY in nvarchar2 := '',
  IN_COUNT in number := -1,
  IN_SUFFIX in NUMBER := -1,
  RES OUT SYS_REFCURSOR)
AS
  CV_1 SYS_REFCURSOR;
  STRDBSOURCENAME varchar2(50);
  STRDBTARGETNAME varchar2(50);
  CNT NUMBER;
  MAXID NUMBER;
  SRC_JOBID NUMBER := -1;
  IN_NEW_JOBID number :=-1;
  TMPQUERY varchar2(4000);
  VC_SUFFIX VARCHAR2(14) := '';
  VCJOBNAME VARCHAR(256) := '';
  VCWHERECONDITION varchar2(2000) := '';
  STRWHEREQUERY NVARCHAR2(2000) := '';
  VC_INJOINWITHJOB VARCHAR2(500) := '';
  VC_WHEREEXPRJOBNAME VARCHAR2(100) := '';
  VC_WHEREEXPRCNTBATCHES VARCHAR2(1500) := '';
  VC_TOPCNT VARCHAR(6) := '';
  B_INDEX BINARY_INTEGER;
  BF_INDEX BINARY_INTEGER;
  DF_INDEX BINARY_INTEGER;
  WDC_INDEX BINARY_INTEGER;
  DP_INDEX BINARY_INTEGER;
  ICRPRJID INTEGER := -1;
  VC_SRCCRPROJID VARCHAR2(10);
  VC_SRCVLPROJID VARCHAR2(10); 
  VC_SRCGLPROJID VARCHAR2(10); 
  VCPRJNAME VARCHAR2(256); 
  IVLPRJID INT := -1;
  IGLPRJID INT := -1;
  CNTB INT := -1;
  BATCH_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  BATCHFODER_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  WDCDOC_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  BATCHFOLDER_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  DOCFILE_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  DOCPAGE_TAB T_IN_LIST_TAB := T_IN_LIST_TAB();
  C_BATCHID number := -1;
  C_WDCDOCID number := -1;
  C_DOCFILEID number := -1;
  C_DPID NUMBER := -1;
  INEWCRPRJID INT := -1;
  INEWVLPRJID INT := -1;	
  INEWGLPRJID INT := -1;	
  IEXTBATCHID NUMBER := -1;
  INEWEXTBATCHID int := -1;
  NEW_BATCH_NAME NVARCHAR2 (256):= '';
  NEWORIGINALID NVARCHAR2 (32):= '';
  NEWBATCHID INT :=-1;
  DTCURRENTDATETIME DATE;
  CNT_JTBR INT := -1;
  C_BATCHFOLDERID number;
  NEW_BF_NAME NVARCHAR2(256) := '';
  NEWBFID int := -1;
  FILEID1 int := -1;
  VC_FILEID1 varchar2(10);
  NEW_DOC_NAME NVARCHAR2 (256) := '';  
  NEW_DOC_FILEID INT := -1;
  NEWDOCID INT := -1;
  NEW_DF_ORIGFILEID INT := -1;
  NEW_DP_DISPLAYFILEID INT := -1;
  DOCCLASSNAME NVARCHAR2(256) := '';
  DOCCLASSNAMEID INT := -1;
  NEWDOCCLASSNAMEID int := -1;  
  NEW_DOCFILE_NAME NVARCHAR2 (256);  
  FILEID2 INT := -1;
  FILEID3 INT := -1;
  FILEID4 int := -1;
  IX INT;
  NEW_DF_FILEID INT :=-1;
  NEWDOCFILEID int := -1;
  NEW_DOCPAGE int := -1;
  STRTABLE1 VARCHAR (256);
  STRTABLE2 varchar (256);
  VC_NEW_JOBID varchar (10);
  VC_NEWBATCHID varchar (10);
  C_SRCPARENTID int; 
  SRCSTATE int;
  SRCPRIORITY int; 
  SRCINDEX  int;
  SRCINDEXDF int;
  SRCORIGINALFILEID int;
  ECODE    NUMBER(38);
  EMESG varchar2(1500); 
  CNTCRPRJID INT;
  CNTVLPRJID INT;
  CNTGLPRJID INT;
  VCFILEPATH varchar2(256);
BEGIN

    STRDBSOURCENAME := DBSOURCENAME;
    STRDBTARGETNAME := DBTARGETNAME;
    STRWHEREQUERY := REPLACE(REPLACE(IN_WHEREQUERY, '[', ''), ']', '');
    STRWHEREQUERY := REPLACE(STRWHEREQUERY, 'Batch.', '');
  
    IF (IN_SUFFIX > -1) THEN
      VC_SUFFIX := '_' || TO_CHAR(IN_SUFFIX);
    END IF;
    
    IF (LENGTH(STRWHEREQUERY)>0) THEN 
        VCWHERECONDITION := ' Where ' || STRWHEREQUERY;
    END IF;    
    
    ---- Define JobId in Target DB ----   
    IF (IN_JOBID > -1) THEN
      BEGIN 
        
        STRTABLE1 := STRDBSOURCENAME || '_JOB';
        TMPQUERY := ' BEGIN ' ||
                    '   select jS.Name into :JobName ' ||
                    '   from ' || STRTABLE1 || ' jS ' || 
                    '   where jS.Id = :jobId ; ' ||
                    '   EXCEPTION ' ||
                    '     WHEN NO_DATA_FOUND THEN ' ||
                    '       :JobName := ''''; ' ||
                    ' END; ';

        execute immediate TMPQUERY using OUT VCJOBNAME, in IN_JOBID;
        
        IN_NEW_JOBID := -1;
        TMPQUERY :=
          ' Begin ' ||
          '   select jT.Id into :1 ' ||
          '   from ' || STRDBTARGETNAME || '_JOB jT ' ||
          '   where jT.Name = :2 ; ' ||
          '   EXCEPTION ' ||
          '     WHEN NO_DATA_FOUND THEN ' ||
          '       :1 := -1; ' ||
          ' End ;  ';
        EXECUTE IMMEDIATE TMPQUERY
          USING OUT IN_NEW_JOBID, IN '' || VCJOBNAME || '';
          
        IF (IN_NEW_JOBID = -1) THEN 
          TMPQUERY := ' Begin ' ||
                      '   Insert into ' || STRDBTARGETNAME || '_JOB ' ||
                      '   ( Name ' ||
                      '    , CreationDate' ||
                      '    , OriginalPath ) ' ||
                      '   ( Select jS.Name ' ||
                      '          , jS.CreationDate ' ||
                      '          , jS.OriginalPath ' || 
                      '     from ' || STRDBSOURCENAME || '_JOB jS ' ||
                      '     where jS.Id = :jobId ); ' ||
                      '     SELECT MAX(ID) INTO :TrgJobId ' || 
                      '     FROM ' || STRDBTARGETNAME || '_JOB ; ' ||
                      ' End ; ';
          execute immediate TMPQUERY 
            USING IN IN_JOBID
                , OUT IN_NEW_JOBID;
        END IF;
  
        VC_INJOINWITHJOB := ' inner join ' || STRDBSOURCENAME || '_JobToBatchRel jtbr on jtbr.BatchId = B.Id ';
        VC_WHEREEXPRJOBNAME := ' jtbr.JobId = ' || TO_CHAR(IN_JOBID);
        
        IF (LENGTH(STRWHEREQUERY) > 0) THEN
          VC_WHEREEXPRJOBNAME := ' AND ' || VC_WHEREEXPRJOBNAME;
        ELSE 
          VC_WHEREEXPRJOBNAME := ' Where ' || VC_WHEREEXPRJOBNAME; 
        END IF;  
          
      END;
    END IF;
  
    VCWHERECONDITION := VCWHERECONDITION || VC_WHEREEXPRJOBNAME;
    MAXID := -1;
    TMPQUERY := ' BEGIN ' ||
                '   SELECT MAX (B.ID) into :maxId ' ||
                '   FROM ' || STRDBSOURCENAME || '_Batch B ' || VC_INJOINWITHJOB || VCWHERECONDITION || '; ' || 
                ' END; '; 
                    
    EXECUTE IMMEDIATE TMPQUERY
      USING OUT MAXID;
    
    IF (NVL (MAXID, 0) <=0) THEN
      BEGIN
        ROLLBACK;
        OPEN CV_1 FOR SELECT 1 AS VALUE FROM DUAL; -- there are no batches to copy
        GOTO End_Of_SP;
      END;    
    END IF;
    
    IF (LENGTH(VCWHERECONDITION)>0) THEN
      VCWHERECONDITION := VCWHERECONDITION || ' AND ';
    ELSE
      VCWHERECONDITION := ' WHERE ';
    END IF;
  
    VC_WHEREEXPRCNTBATCHES :=  ' B.Id <= ' || TO_CHAR(MAXID);  
    VCWHERECONDITION := VCWHERECONDITION || VC_WHEREEXPRCNTBATCHES;
  
    TMPQUERY := ' BEGIN ' ||
                ' SELECT COUNT(B.ID) INTO :CNT ' || 
                ' FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                VC_INJOINWITHJOB || 
                VCWHERECONDITION || ' ;' ||
                ' END; ';
                
    EXECUTE IMMEDIATE TMPQUERY
      USING OUT CNT;

    IF (IN_COUNT>-1) THEN
      BEGIN
        IF (CNT<IN_COUNT) THEN
          VC_TOPCNT := TO_CHAR(CNT);
        ELSE
          VC_TOPCNT := TO_CHAR(IN_COUNT);
        END IF;
        VCWHERECONDITION := VCWHERECONDITION || 'AND ROWNUM < ' || VC_TOPCNT;     
      END;
    END IF;
    
    ------------------------------------------------------------------------------------------------------
    --Batch
    execute immediate 'truncate table TT_TMPBATCH ';
    TMPQUERY := ' BEGIN ' ||
                '   INSERT INTO TT_TMPBATCH ( BATCHID ) ' || 
                '     SELECT  B.ID ' || 
                '     FROM '  || STRDBSOURCENAME || '_BATCH B ' || VC_INJOINWITHJOB || 
                      VCWHERECONDITION || 
                '     ORDER BY B.ID ;' || 
                ' END; ';  
      
    EXECUTE IMMEDIATE TMPQUERY;
      
    SELECT COUNT (BATCHID) INTO CNTB FROM TT_TMPBATCH;

    IF (CNTB<=0) THEN
      BEGIN
        ROLLBACK;
        OPEN CV_1 FOR SELECT 1 AS VALUE FROM DUAL; -- there are no batches to copy
        GOTO End_Of_SP;
      END;
    else
      begin

        BATCH_TAB := T_IN_LIST_TAB();
        
        EXECUTE IMMEDIATE (' SELECT * FROM TT_TMPBATCH')	
        BULK COLLECT INTO BATCH_TAB;
        
        B_INDEX := BATCH_TAB.FIRST;
  
        WHILE B_INDEX IS NOT NULL LOOP
          BEGIN
            C_BATCHID := BATCH_TAB(B_INDEX);
            EXCEPTION
              WHEN NO_DATA_FOUND THEN 
                C_BATCHID := -1;
              WHEN OTHERS THEN 
                begin
                  emesg := SQLERRM;
                  ECODE := SQLCODE;
                  DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                  EXIT;
                END;  
          END;
          IF (C_BATCHID = -1 ) THEN 
            EXIT;
          END IF; 
          
          BEGIN
  
            -- Process Copy Batch with the ID = C_BATCHID 
            -------------------------------------------------------------------------------------------------------------------
            -- Project hints in the Batch and Project Tables of TargetDB             
            ICRPRJID := -1;
            VCPRJNAME := '';
            IVLPRJID := -1;          
            IGLPRJID := -1;

            TMPQUERY := ' BEGIN ' ||
                        '   SELECT  NVL(B.CREATIONPROJECTID, -1) ' ||
                        '         , P.NAME ' ||
                        '         , P.FILEPATH ' ||
                        '         , NVL(B.VERIFIERLOCALPROJECTID, -1) ' ||
                        '         , NVL(B.VERIFIERGLOBALPROJECTID, -1) ' ||
                        '   INTO :1, :2, :3, :4, :5 ' ||
                        '   FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                        '   INNER JOIN ' || STRDBSOURCENAME || '_PROJECT P ON P.ID = B.CREATIONPROJECTID ' ||
                        '   WHERE B.ID = :6 ; ' ||
                        ' END; ';
                        
            execute immediate TMPQUERY
              USING OUT ICRPRJID, OUT VCPRJNAME, OUT VCFILEPATH, OUT IVLPRJID, OUT IGLPRJID, IN C_BATCHID;

            INEWCRPRJID := -1;
            INEWVLPRJID := -1;	
            INEWGLPRJID := -1;	   

            -- Insert CreationProjectId into Target Project if not exists yet 
            TMPQUERY := ' BEGIN ' ||
                        '   SELECT Count (pT.id) into :1 ' ||
                        '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                        '   WHERE pT.NAME = :2 ' || 
                        '   AND pT.FilePath = :3 ;' ||
                        ' END; ';
                        
            execute immediate TMPQUERY
              USING OUT CNTCRPRJID, IN VCPRJNAME, IN VCFILEPATH; 
            
            IF (ICRPRJID > -1) THEN
              VC_SRCCRPROJID := TO_CHAR(ICRPRJID); 
            ELSE 
              ROLLBACK;
              OPEN CV_1 FOR SELECT 1 AS VALUE FROM DUAL; 
              GOTO End_Of_SP;
              EXIT;
            END IF;  
            
            IF (CNTCRPRJID = 0) THEN
              BEGIN
                TMPQUERY := ' BEGIN ' ||
                            '   INSERT INTO ' || STRDBTARGETNAME || '_PROJECT ' ||
                            '     (Name, FilePath) ' ||
                            '     SELECT pS.Name, pS.FilePath ' ||
                            '     FROM ' || STRDBSOURCENAME || '_PROJECT pS ' ||
                            '     WHERE pS.ID = :1; ' ||
                            '     SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_PROJECT ;' ||
                            ' END; ';
                EXECUTE IMMEDIATE TMPQUERY
                  USING IN VC_SRCCRPROJID, OUT INEWCRPRJID;
              end;
            else
              begin 
                TMPQUERY := ' BEGIN ' ||
                            '   SELECT pT.id into :1 ' ||
                            '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                            '   WHERE pT.NAME = :2 ' || 
                            '   AND pT.FilePath = :3 ;' ||
                            ' END; ';
                execute immediate TMPQUERY
                  using OUT INEWCRPRJID, in VCPRJNAME, in VCFILEPATH; --, OUT INEWCRPRJID;                             
              end;
            END IF;  
  
            IF (IVLPRJID > -1) THEN
              BEGIN

                -- Project hints in the Batch and Project Tables of TargetDB             
                VCPRJNAME := '';
    
                TMPQUERY := ' BEGIN ' ||
                            '   SELECT  P.NAME ' ||
                            '         , P.FILEPATH ' ||
                            '         , NVL(B.VERIFIERLOCALPROJECTID, -1) ' ||
                            '   INTO :1, :2, :3 ' ||
                            '   FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                            '   INNER JOIN ' || STRDBSOURCENAME || '_PROJECT P ON P.ID = B.VERIFIERLOCALPROJECTID ' ||
                            '   WHERE B.ID = :4 ; ' ||
                            ' END; ';
                            
                EXECUTE IMMEDIATE TMPQUERY
                  USING OUT VCPRJNAME, OUT VCFILEPATH, OUT IVLPRJID, IN C_BATCHID;
    
                -- Insert CreationProjectId into Target Project if not exists yet 
                TMPQUERY := ' BEGIN ' ||
                            '   SELECT Count (pT.id) into :1 ' ||
                            '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                            '   WHERE pT.NAME = :2 ' || 
                            '   AND pT.FilePath = :3 ;' ||
                            ' END; ';
                            
                EXECUTE IMMEDIATE TMPQUERY
                  USING OUT CNTVLPRJID, IN VCPRJNAME, IN VCFILEPATH; 
                
                VC_SRCVLPROJID := TO_CHAR(IVLPRJID);            
                
                IF (CNTVLPRJID = 0) THEN
                  BEGIN
                    TMPQUERY := ' BEGIN ' ||
                                '   INSERT INTO ' || STRDBTARGETNAME || '_PROJECT ' ||
                                '     (Name, FilePath) ' ||
                                '     SELECT pS.Name, pS.FilePath ' ||
                                '     FROM ' || STRDBSOURCENAME || '_PROJECT pS ' ||
                                '     WHERE pS.ID = :1; ' ||
                                '     SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_PROJECT ;' ||
                                ' END; ';
                    EXECUTE IMMEDIATE TMPQUERY
                      USING IN VC_SRCVLPROJID, OUT INEWVLPRJID;
                  end;
                else
                  begin 
                    TMPQUERY := ' BEGIN ' ||
                                '   SELECT pT.id into :1 ' ||
                                '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                                '   WHERE pT.NAME = :2 ' || 
                                '   AND pT.FilePath = :3 ;' ||
                                ' END; ';
                    EXECUTE IMMEDIATE TMPQUERY
                      using OUT INEWVLPRJID, in VCPRJNAME, in VCFILEPATH; --, OUT INEWCRPRJID;                             
                  END;
                END IF;  

              END;
            END IF;

            IF (IGLPRJID > -1) THEN
              BEGIN
              
                    -- Project hints in the Batch and Project Tables of TargetDB             
                    VCPRJNAME := '';
        
                    TMPQUERY := ' BEGIN ' ||
                                '   SELECT  P.NAME ' ||
                                '         , P.FILEPATH ' ||
                                '         , NVL(B.VERIFIERGLOBALPROJECTID, -1) ' ||
                                '   INTO :1, :2, :3 ' ||
                                '   FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                                '   INNER JOIN ' || STRDBSOURCENAME || '_PROJECT P ON P.ID = B.VERIFIERGLOBALPROJECTID ' ||
                                '   WHERE B.ID = :4 ; ' ||
                                ' END; ';
                                
                    EXECUTE IMMEDIATE TMPQUERY
                      USING OUT VCPRJNAME, OUT VCFILEPATH, OUT IGLPRJID, IN C_BATCHID;
        
                    -- Insert CreationProjectId into Target Project if not exists yet 
                    TMPQUERY := ' BEGIN ' ||
                                '   SELECT Count (pT.id) into :1 ' ||
                                '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                                '   WHERE pT.NAME = :2 ' || 
                                '   AND pT.FilePath = :3 ;' ||
                                ' END; ';
                                
                    EXECUTE IMMEDIATE TMPQUERY
                      USING OUT CNTGLPRJID, IN VCPRJNAME, IN VCFILEPATH; 
                    
                    VC_SRCGLPROJID := TO_CHAR(IGLPRJID);            
                    
                    IF (CNTGLPRJID = 0) THEN
                      BEGIN
                        TMPQUERY := ' BEGIN ' ||
                                    '   INSERT INTO ' || STRDBTARGETNAME || '_PROJECT ' ||
                                    '     (Name, FilePath) ' ||
                                    '     SELECT pS.Name, pS.FilePath ' ||
                                    '     FROM ' || STRDBSOURCENAME || '_PROJECT pS ' ||
                                    '     WHERE pS.ID = :1; ' ||
                                    '     SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_PROJECT ;' ||
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY
                          USING IN VC_SRCGLPROJID, OUT INEWGLPRJID;
                      end;
                    else
                      begin 
                        TMPQUERY := ' BEGIN ' ||
                                    '   SELECT pT.id into :1 ' ||
                                    '   FROM ' || STRDBTARGETNAME || '_Project pT ' ||
                                    '   WHERE pT.NAME = :2 ' || 
                                    '   AND pT.FilePath = :3 ;' ||
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY
                          using OUT INEWGLPRJID, in VCPRJNAME, in VCFILEPATH; --, OUT INEWCRPRJID;                             
                      END;
                    END IF;  

                  END;

            end if;
            
            if (INEWVLPRJID = -1) then 
              INEWVLPRJID := INEWCRPRJID;
            END IF;

            if (INEWGLPRJID = -1) then 
              INEWGLPRJID := INEWCRPRJID;
            END IF;

            -------------------------------------------------------------------------------------------------------------------
            --BDD_ExternalBatch
            
            IEXTBATCHID := -1;
            
            TMPQUERY := ' BEGIN ' ||
                        '   SELECT NVL(B.ExternalBatchId, -1) INTO :1 ' ||
                        '   FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                        '   WHERE B.ID = :2 ;' ||
                        '   EXCEPTION ' ||
                        '     WHEN NO_DATA_FOUND THEN ' ||
                        '       SELECT -1 INTO :3 FROM DUAL ; ' ||         
                        ' END; ';
  
            execute immediate TMPQUERY
              USING OUT IEXTBATCHID, IN C_BATCHID, OUT IEXTBATCHID;
              
            INEWEXTBATCHID := -1;
            
            IF (IEXTBATCHID > 0) THEN 
              begin
                
                TMPQUERY := ' BEGIN ' ||
                            '   Select EBt.id into :TRGExtBatchId ' || 
                            '   from ' || STRDBTARGETNAME || '_BDD_ExternalBatch EBt ' || 
                            '   where EBt.id = :SRCExtBatchId ) ' ||
                            '   EXCEPTION ' ||
                            '     WHEN NO_DATA_FOUND THEN ' ||
                            '       SELECT -1 INTO :TRGExtBatchId FROM DUAL ; ' ||         
                            ' END; ';
                execute immediate TMPQUERY
                  USING OUT INEWEXTBATCHID, IN IEXTBATCHID, OUT INEWEXTBATCHID;
                  
                IF (INEWEXTBATCHID = -1) THEN
                  begin
                    TMPQUERY := ' BEGIN ' ||
                                '   INSERT INTO ' || STRDBTARGETNAME || '_BDD_ExternalBatch ' ||
                                '     ( ID, Name ) ' ||
                                '     SELECT ebS.ID ' ||
                                '          , ebS.Name ' ||
                                '     FROM ' || STRDBSOURCENAME || '_BDD_ExternalBatch ebS ' ||
                                '     WHERE ebS.ID = :SRCEBID; ' ||
                                '   SELECT ID INTO :TRGNEWEBID ' || 
                                '   FROM ' || STRDBTARGETNAME || '_BDD_ExternalBatch ebT ' || 
                                '   WHERE ebT.ID = :SRCEBID ; ' ||
                                ' END ;';
                    execute immediate TMPQUERY
                      USING IN IEXTBATCHID, OUT INEWEXTBATCHID;
                  END; 
                END IF;  
              END;

            end if;
            
            if (INEWEXTBATCHID = -1) then
              INEWEXTBATCHID := NULL;
            END IF;
            
            -------------------------------------------------------------------------------------------------------------------
            -- insert into batch table of target db            
            TMPQUERY := ' BEGIN ' ||
                        '   SELECT '  || 
                        '       b.Name ' ||
                        '     , b.OriginalId ' ||
                        '     into :1, :2 ' ||
                        '   FROM ' || STRDBSOURCENAME || '_BATCH B ' ||
                        '   WHERE B.ID = :3 ;' || 
                        ' END; ';
            execute immediate TMPQUERY
                using OUT NEW_BATCH_NAME, OUT NEWORIGINALID, in C_BATCHID;             
  
            IF (IN_SUFFIX > -1) THEN
              BEGIN
                NEW_BATCH_NAME := NEW_BATCH_NAME || VC_SUFFIX;
                NEWORIGINALID := NEWORIGINALID || VC_SUFFIX;
              END;
            END IF;
            
            DTCURRENTDATETIME := SYS_EXTRACT_UTC(SYSTIMESTAMP) ;
            
            TMPQUERY := ' BEGIN ' ||
                        ' INSERT INTO ' || STRDBTARGETNAME || '_BATCH ' ||
                        ' (    VERIFIERLOCALPROJECTID ' || --1
                        '     ,VERIFIERGLOBALPROJECTID ' || --2
                        '     ,CREATIONPROJECTID ' || --3
                        '     ,NAME ' || --4
                        '     ,ORIGINALID ' || --5
                        '     ,PRIORITY ' ||
                        '     ,CLIENT ' ||
                        '     ,IMPORTDIRECTORYPATH ' ||
                        '     ,EXTERNALBATCHID ' || --6
                        '     ,EXTERNALGROUPID ' ||
                        '     ,TRANSACTIONID ' ||
                        '     ,TRANSACTIONTYPE ' ||
                        '     ,STATE ' ||
                        '     ,FOLDERCOUNT ' ||
                        '     ,DOCUMENTCOUNT ' ||
                        '     ,LASTACCESSEDDATE ' || --7
                        '     ,LASTMODULEID ' ||
                        '     ,LASTUSERID ) ' ||
                        ' Select  :1 ' || 
                        '       , :2 ' || 
                        '       , :3 ' || 
                        '       , :4 ' || 
                        '       , :5 ' || 
                        '       , b.PRIORITY ' ||
                        '       , b.Client ' ||
                        '       , b.ImportDirectoryPath ' ||
                        '       , NVL(:6, NULL) ' || 
                        '       , b.ExternalGroupId ' ||
                        '       , b.TransactionId ' ||
                        '       , b.TransactionType ' ||
                        '       , b.State ' ||
                        '       , b.FolderCount ' ||
                        '       , b.DocumentCount ' ||
                        '       , :7 ' || 
                        '       , b.LastModuleId ' ||
                        '       , 3 ' ||
                        ' FROM ' || STRDBSOURCENAME || '_Batch B ' ||
                        ' WHERE B.ID = :8 ; ' || 
                        ' SELECT MAX(BT.ID) INTO :9 FROM ' || STRDBTARGETNAME || '_BATCH BT; ' ||
                        ' END ; '; 
                        
            execute immediate TMPQUERY
                using   in INEWVLPRJID --1
                      , in INEWGLPRJID --2
                      , in INEWCRPRJID --3
                      , in NEW_BATCH_NAME --4
                      , in NEWORIGINALID --5
                      , in INEWEXTBATCHID --6
                      , in cast(DTCURRENTDATETIME as varchar2) --7
                      , in C_BATCHID --8
                      , OUT NEWBATCHID; --9   
                      
                      
            IF (IN_SUFFIX = -1) THEN
              begin

                IX := -1;
                IX := INSTR (NEWORIGINALID, '_');
                if (IX > 0) then
                  NEWORIGINALID := SUBSTR (NEWORIGINALID, 1, IX-1);     
                End if;
              
                NEWORIGINALID := NEWORIGINALID || '_' || TO_CHAR(NEWBATCHID);
                TMPQUERY := ' BEGIN ' ||
                            '   UPDATE ' || STRDBTARGETNAME || '_BATCH ' ||
                            '   SET OriginalId = ''' || NEWORIGINALID || '''' ||
                            '   WHERE Id = ' || TO_CHAR(NEWBATCHID) || '; ' ||
                            ' END; ';
                execute immediate TMPQUERY;
              END;
            END IF;  

            -------------------------------------------------------------------------------------------------------------------
            --Target DB Job Table
            -- !!! IN_JOBID is the input parameter - for the case if IN_JOBID = -1 -> all batches of all jobs
            IF (IN_NEW_JOBID = -1) THEN
              begin
                TMPQUERY := ' BEGIN ' ||
                            '   Select JobId into :1 ' ||
                            '   FROM ' || STRDBSOURCENAME || '_JobToBatchRel jtbr ' ||
                            '   WHERE B.ID = :2 ; ' ||
                            ' END; ';
                
                execute immediate TMPQUERY
                    USING OUT SRC_JOBID, IN C_BATCHID;
                    
                TMPQUERY := ' BEGIN ' || 
                            ' SELECT SRCJ.NAME INTO :1 ' ||
                            ' FROM ' || STRDBSOURCENAME || '_Job SRCJ ' ||
                            ' WHERE SRCJ.ID = :2 ;' ||
                            ' END; ';
                execute immediate TMPQUERY
                    using OUT VCJOBNAME, in SRC_JOBID;
                    --USING OUT VCJOBNAME, IN VC_INJOBID;
                            
                TMPQUERY := ' BEGIN' ||
                            '   Select jT.ID INTO :1 ' ||
                            '   FROM ' || STRDBTARGETNAME|| '_Job jT ' ||
                            '   WHERE jT.Name = :2 ; ' ||
                            ' END; ';
                            
                EXECUTE IMMEDIATE TMPQUERY
                    USING OUT IN_NEW_JOBID, IN VCJOBNAME;
                
                IF (IN_NEW_JOBID = -1) THEN
                  begin                
                    TMPQUERY := ' BEGIN ' ||
                                '   INSERT INTO ' || STRDBTARGETNAME || '_JOB tJ' ||
                                '   ( Name ' ||
                                '     CreationDate ' ||
                                '     OriginalPath ) ' ||
                                '   Select jS.Name, jS.CreationDate, jS.OriginalPath ' ||
                                '   From ' || STRDBSOURCENAME || '_JOB jS ' ||
                                '   WHERE jS.ID = :1 ; ' ||
                                '   SELECT MAX(jT.ID) INTO :2 FROM ' || STRDBTARGETNAME || '_JOB jT; ' || 
                                ' END; ';
                    execute immediate TMPQUERY
                      USING IN SRC_JOBID, OUT IN_NEW_JOBID;
                      --USING IN VC_INJOBID, OUT IN_NEW_JOBID;                    
                  END;
                END IF;
              END;
            END IF;
            -------------------------------------------------------------------------------------------------------------------
            --Target DB JobToBacthRel Table
  
            CNT_JTBR := -1;
            TMPQUERY := ' BEGIN ' ||
                        '   SELECT COUNT(JtBR.JobID) INTO :1 ' ||
                        '   FROM ' || STRDBTARGETNAME || '_JobToBatchRel JtBR ' ||
                        '   WHERE JtBR.JobID = :2 AND ' ||
                        '       JtBR.BatchID = :3 ;' ||
                        ' END; ';
            EXECUTE IMMEDIATE TMPQUERY
                USING OUT CNT_JTBR, IN IN_NEW_JOBID, IN NEWBATCHID;
  
  
            VC_NEW_JOBID := TO_CHAR(IN_NEW_JOBID); 
            VC_NEWBATCHID := TO_CHAR(NEWBATCHID);
            
            IF (CNT_JTBR <= 0) THEN 
              begin
                TMPQUERY := ' BEGIN ' ||
                            '   INSERT INTO ' || STRDBTARGETNAME || '_JobToBatchRel ' ||
                            '     ( JobId, BatchId ) ' ||
                            '   VALUES ' ||
                            '     ( :1, :2 ); ' ||
                            ' END; ';
                execute immediate TMPQUERY
                  USING IN VC_NEW_JOBID, IN VC_NEWBATCHID;            
  
              END;
            END IF;
        
            -------------------------------------------------------------------------------------------------------------------    
            --BatchFolders   
            execute immediate 'truncate table TT_TMPBATCHFOLDER ';

            TMPQUERY := ' BEGIN ' ||
                        '   INSERT INTO TT_TMPBATCHFOLDER ' || 
                        '     (BATCHFOLDERID) ' ||
                        '    Select BF.ID ' || 
                        '     FROM '|| STRDBSOURCENAME ||'_BATCHFOLDER BF ' ||
                        '   WHERE BF.BatchId = :1 ' ||
                        '   ORDER BY BF.ID; ' ||
                        ' END; ';
                        
            execute immediate TMPQUERY 
              USING IN C_BATCHID;

          BATCHFODER_TAB := T_IN_LIST_TAB();

            execute immediate (' SELECT * FROM TT_TMPBATCHFOLDER ')	
              BULK COLLECT INTO BATCHFOLDER_TAB;
            
            BF_INDEX := BATCHFOLDER_TAB.FIRST;
      
            WHILE BF_INDEX IS NOT NULL LOOP
              BEGIN
                -- Processing of Copy to BatchFolder
  
                C_BATCHFOLDERID := BATCHFOLDER_TAB(BF_INDEX);
                
                TMPQUERY := ' BEGIN ' ||
                            '   SELECT BF.NAME INTO :1' ||
                            '   FROM '|| STRDBSOURCENAME || '_BATCHFOLDER BF ' ||
                            '   WHERE BF.ID = :2 ;' ||
                            ' END; ';
                
                EXECUTE IMMEDIATE TMPQUERY 
                  USING OUT NEW_BF_NAME, IN C_BATCHFOLDERID;                          
                
                IF (IN_SUFFIX > -1) THEN
                  NEW_BF_NAME := NEW_BF_NAME + VC_SUFFIX;
                end if;  
                
                TMPQUERY := ' BEGIN ' ||
                            '     Select srcBF.ParentId into :1' ||
                            '     From ' || STRDBSOURCENAME || '_BatchFolder srcBF ' || 
                            '     Where srcBF.Id = :2 ;' ||
                            ' END; ';

                execute immediate TMPQUERY 
                  using OUT C_SRCPARENTID, in C_BATCHFOLDERID;

                TMPQUERY := ' BEGIN ' ||
                            '   INSERT INTO '|| STRDBTARGETNAME || '_BATCHFOLDER ' ||
                            '   ( ParentId ' ||
                            '   , BatchId ' ||
                            '   , Name ) ' ||
                            '   VALUES ' ||
                            '   (  :1  ' ||
                            '   , :2 ' ||
                            '   , :3 ) ; ' ||
                            ' SELECT MAX(ID) INTO :4 FROM ' || STRDBTARGETNAME || '_BATCHFOLDER ;' ||
                            ' END; ';
                execute immediate TMPQUERY 
                  USING IN C_SRCPARENTID, IN VC_NEWBATCHID, IN NEW_BF_NAME, OUT NEWBFID; 
                
                --------------------------------------------------------------------------------
                --Document
                execute immediate 'truncate table TT_TMPWDCFILE ';
                TMPQUERY := ' BEGIN ' ||
                            '   INSERT INTO TT_TMPWDCFILE ' || 
                            '   ( WDCFILEID ) ' ||
                            '   Select D.ID ' || 
                            '   FROM ' || STRDBSOURCENAME || '_DOCUMENT D ' ||
                            '     INNER JOIN ' || STRDBSOURCENAME || '_BATCHFOLDER bftbl ON bftbl.ID = D.BATCHFOLDERID ' ||
                            '   WHERE bftbl.ID = :1 ' ||
                            '   ORDER BY D.ID; ' ||
                            ' END; ';
                            
                EXECUTE IMMEDIATE TMPQUERY 
                  USING IN C_BATCHFOLDERID;
                            
                WDCDOC_TAB := T_IN_LIST_TAB();

                EXECUTE IMMEDIATE (' SELECT * FROM TT_TMPWDCFILE')	
                BULK COLLECT INTO WDCDOC_TAB;
                
                WDC_INDEX := WDCDOC_TAB.FIRST;
          
                WHILE WDC_INDEX IS NOT NULL LOOP
                  BEGIN
                    -- Processing of Copy to Document
                    C_WDCDOCID := WDCDOC_TAB(WDC_INDEX);
                    NEW_DOC_NAME := '';
                    
                    TMPQUERY := ' BEGIN ' ||
                                '   SELECT D.Name INTO :1 ' ||
                                '   FROM ' || STRDBSOURCENAME || '_Document D ' ||
                                '   WHERE D.ID = :2 ;' ||
                                ' END; ';
                    execute immediate TMPQUERY 
                      USING OUT NEW_DOC_NAME, IN C_WDCDOCID;
                      
                    --------------------------------------------------------------------------------
                    --File	for document  
                    
                    TMPQUERY := ' BEGIN ' ||
                                '   Select FILEID INTO :1 ' ||
                                '   FROM ' || STRDBSOURCENAME || '_Document ' ||
                                '   WHERE ID = :2 ;' || 
                                ' END; ';
                    execute immediate TMPQUERY 
                      using OUT FILEID1, in C_WDCDOCID;
                    
                    VC_FILEID1 := TO_CHAR(FILEID1);  
                      
                    TMPQUERY := ' BEGIN ' ||
                                '   INSERT INTO ' || STRDBTARGETNAME || '_FILE_ ' ||
                                '     ( VALUE ) ' ||
                                '     SELECT srcFile.VALUE ' ||
                                '     FROM ' || STRDBSOURCENAME || '_FILE_ srcFile ' || 
                                '     WHERE srcFile.ID = :1 ;  ' ||
                                '   SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_FILE_ ;' ||
                                ' END; ';
                    execute immediate TMPQUERY 
                      USING IN VC_FILEID1, OUT NEW_DOC_FILEID;
                                  
                    NEWDOCID := -1;
                    DOCCLASSNAME := '';
                    DOCCLASSNAMEID := -1;
                    NEWDOCCLASSNAMEID := -1;
                    
                    TMPQUERY := ' BEGIN ' ||
                                '   SELECT DCN.NAME, D.DocumentClassNameId ' || 
                                '   INTO :1, :2 ' ||
                                '   FROM ' || STRDBSOURCENAME || '_DOCUMENT D ' ||
                                '   INNER JOIN ' || STRDBSOURCENAME || '_DocumentClassName DCN ' ||
                                '   ON D.DocumentClassNameID = DCN.ID ' ||
                                '   WHERE D.ID = :3 ;' ||
                                '   EXCEPTION ' ||
                                '     WHEN NO_DATA_FOUND THEN ' ||
                                '       SELECT -1 INTO :4 FROM DUAL ; ' || 
                                ' END; ';                              
                    execute immediate TMPQUERY 
                      USING OUT DOCCLASSNAME, OUT DOCCLASSNAMEID, IN C_WDCDOCID, OUT DOCCLASSNAMEID;
                    
                    IF (NVL(DOCCLASSNAMEID, 0)>0) THEN
                      begin
                        TMPQUERY := ' BEGIN ' || 
                                    '   SELECT ID INTO :1 ' ||
                                    '   FROM ' || STRDBTARGETNAME || '_DocumentClassName ' ||
                                    '   WHERE NAME = :2 ; ' ||
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY 
                          USING OUT NEWDOCCLASSNAMEID, IN DOCCLASSNAME;
                      END;
                    ELSE
                      NEWDOCCLASSNAMEID := null;
                    END IF;
                    
                    IF (NEWDOCCLASSNAMEID = -1) THEN 
                      NEWDOCCLASSNAMEID := null;
                    END IF;

                    TMPQUERY := ' BEGIN ' ||
                                '       Select dS.State ' ||
                                '                , dS.Priority ' ||
                                '                , dS.Index_ ' ||
                                '       INTO :1, :2, :3 ' ||
                                '       FROM ' || STRDBSOURCENAME || '_DOCUMENT dS ' ||
                                '       WHERE dS.ID = :4 ;' ||
                                ' END; ';


                    execute immediate TMPQUERY 
                      using OUT SRCSTATE --1
                          , OUT SRCPRIORITY --2 
                          , OUT SRCINDEX -- 3
                          , in C_WDCDOCID; 
                    
                    TMPQUERY := ' BEGIN ' ||
                                ' INSERT INTO ' || STRDBTARGETNAME || '_DOCUMENT ' ||
                                ' ( DOCUMENTCLASSNAMEID ' ||
                                '   , FILEID ' ||
                                '   , LASTWORKFLOWHISTORYCLASSID ' ||
                                '   , LastModuleId ' ||
                                '   , LastUserId ' ||
                                '   , BatchFolderId ' ||
                                '   , Name ' ||
                                '   , CreationDate ' ||
                                '   , LastAccessedDate ' ||
                                '   , State ' ||
                                '   , Priority ' ||
                                '   , Index_ ' ||
                                ' ) ' ||
                                ' VALUES ( :1  ' ||
                                '        , :2 ' ||
                                '        , null ' ||
                                '        , null ' ||
                                '        , 3 ' ||
                                '       , :3 ' ||
                                '       , :4 ' ||
                                '       , :5 ' ||
                                '       , :6 ' ||
                                '       , :7 ' ||
                                '       , :8 ' ||
                                '       , :9 ); ' ||
                                ' SELECT MAX(ID) INTO :10 FROM ' || STRDBTARGETNAME || '_DOCUMENT ;' ||
                                ' END; ';
  
                    DTCURRENTDATETIME := SYS_EXTRACT_UTC(SYSTIMESTAMP) ;                  
  
                    execute immediate TMPQUERY 
                      using in NEWDOCCLASSNAMEID --1
                          , in NEW_DOC_FILEID --2 
                          , in NEWBFID -- 3
                          , in NEW_DOC_NAME -- 4
                          , IN DTCURRENTDATETIME -- 5 
                          , in DTCURRENTDATETIME -- 6 
                          , in SRCSTATE --7
                          , in SRCPRIORITY --8 
                          , in SRCINDEX -- 9
                          , out NEWDOCID; -- 10
                    
                    -----------------------------------------------------------------------------------
                    -- DocumentFile	                  
                    execute immediate 'truncate table TT_TMPDOCFILE ';
                    TMPQUERY := ' BEGIN ' ||
                                '   INSERT INTO TT_TMPDOCFILE (DOCFILEID) ' ||
                                '     SELECT DF.ID ' || 
                                '     FROM ' || STRDBSOURCENAME || '_DOCUMENTFILE DF ' ||
                                '     INNER JOIN  ' || STRDBSOURCENAME || '_DOCUMENT D ON D.ID = DF.DOCUMENTID ' ||
                                '     WHERE D.ID = :1 ' ||
                                '     ORDER BY DF.ID; ' ||
                                ' END; ';
                                
                    execute immediate TMPQUERY 
                      USING IN C_WDCDOCID;

                    DOCFILE_TAB := T_IN_LIST_TAB();
                                
                    EXECUTE IMMEDIATE (' SELECT * FROM TT_TMPDOCFILE')	
                    BULK COLLECT INTO DOCFILE_TAB;
                    
                    DF_INDEX := DOCFILE_TAB.FIRST;
              
                    WHILE DF_INDEX IS NOT NULL LOOP
                      BEGIN
                        -- Copy to DocumentFile
                        C_DOCFILEID := DOCFILE_TAB(DF_INDEX);
                        NEW_DOCFILE_NAME := '';
                        
                        TMPQUERY := ' BEGIN ' ||
                                    '   SELECT NAME INTO :1 ' ||
                                    '   FROM ' || STRDBSOURCENAME || '_DOCUMENTFILE ' ||
                                    '   WHERE ID = :2 ; ' ||
                                    ' END; ';

                        execute immediate TMPQUERY 
                          USING OUT NEW_DOCFILE_NAME, IN C_DOCFILEID;
  
                        -----------------------------------------------------------------------------------
                        ----File for DocumentFile                        
                        FILEID2 := -1;
                        
                        TMPQUERY := ' BEGIN ' ||
                                    '   SELECT FILEID INTO :1 ' || 
                                    '   FROM ' || STRDBSOURCENAME || '_DocumentFile ' ||
                                    '   WHERE ID = :2 ;' ||
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY 
                          USING OUT FILEID2, IN C_DOCFILEID; 
                          
                        NEW_DF_FILEID :=-1;
                        TMPQUERY := ' BEGIN ' ||
                                    '   INSERT INTO ' || STRDBTARGETNAME || '_File_ ' ||
                                    '     ( Value ) ' ||
                                    '      Select srcF.Value From ' || STRDBSOURCENAME || '_File_ srcF where srcF.Id = :1 ; ' ||
                                    '   SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_File_ ;' || 
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY 
                          USING IN FILEID2, OUT NEW_DF_FILEID;            
                        
                        FILEID3 := -1;
                        TMPQUERY := ' BEGIN ' ||
                                    '   SELECT ORIGINALFILEID INTO :1 ' || 
                                    '   FROM ' || STRDBSOURCENAME || '_DocumentFile ' ||
                                    '   WHERE ID = :2 ;' ||
                                    ' END; ';
                        EXECUTE IMMEDIATE TMPQUERY 
                          using OUT FILEID3, in C_DOCFILEID; 

                        NEW_DF_ORIGFILEID :=-1;  

                        if ( (FileId3 IS NOT null) AND (FileId3 > 0) ) THEN
                          Begin 
                            TMPQUERY := ' BEGIN ' ||
                                        '   INSERT INTO ' || STRDBTARGETNAME || '_File_ ' ||
                                        '     ( Value ) ' ||
                                        '      Select srcF.Value From ' || STRDBSOURCENAME || '_File_ srcF where srcF.Id = :1 ; ' ||
                                        '   SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_File_ ;' || 
                                        ' END; ';
                            EXECUTE IMMEDIATE TMPQUERY 
                              USING IN FILEID3, OUT NEW_DF_ORIGFILEID;            
                          End;
                        else 
                           NEW_DF_ORIGFILEID := null;
                        END IF;        
               
                        NEWDOCFILEID := -1;
                        
                        TMPQUERY := ' BEGIN ' ||
                                    '       Select dfS.INDEX_ ' ||
                                    '       INTO :1 ' ||
                                    '       FROM ' || STRDBSOURCENAME || '_DOCUMENTFILE dfS ' ||
                                    '       WHERE dfS.ID = :2 ;' ||
                                    ' END; ';
                        execute immediate TMPQUERY 
                          using OUT SRCINDEXDF --1
                              , in C_DOCFILEID; --2
                        
                        TMPQUERY := ' BEGIN ' ||
                                    '   INSERT INTO ' || STRDBTARGETNAME || '_DocumentFile ' ||
                                    '     ( DocumentId ' ||
                                    '       , FILEID ' ||
                                    '       , NAME ' ||
                                    '       , INDEX_ ' ||
                                    '       , ORIGINALFILEID ) ' ||
                                    '     VALUES ' ||
                                    '      ( :1 ' ||
                                    '      , :2 ' ||
                                    '      , :3 ' ||
                                    '      , :4 ' ||
                                    '      , :5 ); ' ||
                                    '   SELECT MAX(ID) INTO :6 FROM ' || STRDBTARGETNAME || '_DocumentFile  ;' || 
                                    ' END; '; 
                        EXECUTE IMMEDIATE TMPQUERY 
                          USING IN NEWDOCID --1
                              , IN NEW_DF_FILEID --2
                              , IN NEW_DOCFILE_NAME --3 
                              , IN SRCINDEXDF  --4
                              , IN NEW_DF_ORIGFILEID --5
                              , OUT NEWDOCFILEID; --6
  
                        --------------------------------------------------------------------------------
                        --DocumentPage
                        execute immediate 'truncate table TT_TMPDOCPAGE ';
                        TMPQUERY := ' BEGIN ' ||
                                    '   INSERT INTO TT_TMPDOCPAGE ' || 
                                    '     ( DOCPAGEID ) ' ||
                                    '     Select DP.ID ' || 
                                    '     FROM ' || STRDBSOURCENAME || '_DOCUMENTPAGE DP ' ||
                                    '       INNER JOIN ' || STRDBSOURCENAME || '_DOCUMENTFILE docfile ON docfile.ID = DP.DOCUMENTFILEID ' ||
                                    '     WHERE docfile.ID = :1 ' ||
                                    '     ORDER BY DP.ID ; ' ||
                                    ' END ; '; 
                                    
                        EXECUTE IMMEDIATE TMPQUERY 
                          USING IN C_DOCFILEID;
                          
                        DOCPAGE_TAB := T_IN_LIST_TAB();
                                    
                        EXECUTE IMMEDIATE (' SELECT * FROM TT_TMPDOCPAGE')	
                        BULK COLLECT INTO DOCPAGE_TAB;
                        
                        DP_INDEX := DOCPAGE_TAB.FIRST;
                        
                        new_DP_DisplayFileId := -1;                        
                  
                        WHILE DP_INDEX IS NOT NULL LOOP
                          BEGIN
                            -- Processing of Copy to Document
                            C_DPID := DOCPAGE_TAB(DP_INDEX);
                            NEW_DOCPAGE := -1;     
                            
                            FileId4 := -1;
                        
                            TMPQUERY := ' BEGIN ' ||
                                        '   SELECT DISPLAYFILEID INTO :1 ' || 
                                        '   FROM ' || STRDBSOURCENAME || '_DocumentPage ' ||
                                        '   WHERE ID = :2 ;' ||
                                        ' END; ';
                            EXECUTE IMMEDIATE TMPQUERY 
                              using OUT FILEID4, in C_DPID; 
    
                            NEW_DP_DISPLAYFILEID :=-1;  
    
                            if ( (FileId4 IS NOT null) AND (FileId4 > 0) ) THEN
                              Begin 
                                  TMPQUERY := ' BEGIN ' ||
                                              '   INSERT INTO ' || STRDBTARGETNAME || '_File_ ' ||
                                              '     ( Value ) ' ||
                                              '      Select srcF.Value From ' || STRDBSOURCENAME || '_File_ srcF where srcF.Id = :1 ; ' ||
                                              '   SELECT MAX(ID) INTO :2 FROM ' || STRDBTARGETNAME || '_File_ ;' || 
                                              ' END; ';
                                  EXECUTE IMMEDIATE TMPQUERY 
                                    USING IN FILEID4, OUT NEW_DP_DISPLAYFILEID;
                              End;
                            else
                               NEW_DP_DISPLAYFILEID := NULL;
                            END IF; 
                        
                            TMPQUERY := ' BEGIN ' ||
                                        '   INSERT INTO ' || STRDBTARGETNAME || '_DOCUMENTPAGE ' ||
                                        '   ( DOCUMENTFILEID ' ||
                                        '   , INDEX_ ' ||
                                        '   , CONFIDENCE ' ||
                                        '   , STATUS ' ||
                                        '   , DISPLAYFILEID )' ||                                        
                                        '   SELECT ' ||
                                        '     :1 ' ||
                                        '     , INDEX_ ' ||
                                        '     , CONFIDENCE ' ||
                                        '     , STATUS ' ||
                                        '     , :2  ' ||
                                        '   FROM ' || STRDBSOURCENAME || '_DOCUMENTPAGE dpS ' ||
                                        '   WHERE dpS.ID = :3 ;' ||
                                        '   SELECT MAX(ID) INTO :4 FROM ' || STRDBTARGETNAME || '_DOCUMENTPAGE ;' || 
                                        ' END; '; 
                            
                            EXECUTE IMMEDIATE TMPQUERY 
                              USING IN NEWDOCFILEID
                                  , IN NEW_DP_DISPLAYFILEID
                                  , IN C_DPID
                                  , OUT NEW_DOCPAGE;
                                        
                            EXCEPTION
                              WHEN NO_DATA_FOUND THEN 
                                C_DPID := -1;
                              WHEN OTHERS THEN 
                                begin
                                  emesg := SQLERRM;
                                  ECODE := SQLCODE;
                                  DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                                  EXIT;
                                END;   
                          END;
                          IF (C_DPID = -1 ) THEN 
                            EXIT;
                          END IF;  
                                        
                          DP_INDEX := DOCPAGE_TAB.NEXT(DP_INDEX);
              
                        END LOOP;
                        
                        --DocumentPage
                        --------------------------------------------------------------------------------                        
                        EXCEPTION
                          WHEN NO_DATA_FOUND THEN C_DOCFILEID := -1;
                          when OTHERS then 
                            begin
                              emesg := SQLERRM;
                              ECODE := SQLCODE;
                              DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                              EXIT;
                            END;     
                      END;
                      IF (C_DOCFILEID = -1 ) THEN 
                        EXIT;
                      END IF; 
                      
                      DF_INDEX := DOCFILE_TAB.NEXT(DF_INDEX);
          
                    END LOOP;
                                
                    -- DocumentFile	                  
                    -----------------------------------------------------------------------------------                    
                    EXCEPTION
                      WHEN NO_DATA_FOUND THEN 
                        C_WDCDOCID := -1;
                      when OTHERS then 
                        begin
                          emesg := SQLERRM;
                          ECODE := SQLCODE;
                          DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                          EXIT;
                        END;   
                  END;
                  IF (C_WDCDOCID = -1 ) THEN 
                    EXIT;
                  END IF;  
                                
                  WDC_INDEX := WDCDOC_TAB.NEXT(WDC_INDEX);
      
                END LOOP;
                            
                --Document                          
                --------------------------------------------------------------------------------                
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN 
                    C_BATCHFOLDERID := -1;
                  WHEN OTHERS THEN 
                    begin
                      emesg := SQLERRM;
                      ECODE := SQLCODE;
                      DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                      EXIT;
                    END;  
              END;
              IF (C_BATCHFOLDERID = -1 ) THEN 
                EXIT;
              END IF;  
                            
              BF_INDEX := BATCHFOLDER_TAB.NEXT(BF_INDEX);
  
            END LOOP;
            --BatchFolders   
            -------------------------------------------------------------------------------------------------------------------              
            EXCEPTION 
              when OTHERS then 
              begin
                emesg := SQLERRM;
                ECODE := SQLCODE;
                DBMS_OUTPUT.PUT_LINE(' - ' || ECODE || ' - ' || emesg );
                EXIT;
              END;  
          END;
  
          B_INDEX := BATCH_TAB.NEXT(B_INDEX);
  
        END LOOP;  
        -- Batch
        ------------------------------------------------------------------------------------------------------
        
        COMMIT;
        OPEN CV_1 FOR SELECT 0 AS VALUE FROM DUAL;
      END;
    end if;
    
<<End_Of_SP>>
    res:=cv_1;    

end SP_COPYBATCHES;
/
-- Move Entire Batch to Exception State feature

Declare 
  v_count Number := 0;
Begin  
    select count(Id) into v_count from SettingType where Name='DisableMoveEntireBatchToExceptionState';
    if (v_count = 0) then
      insert into SettingType(Name, Type) values('DisableMoveEntireBatchToExceptionState', 3);
    end if;
  
    select count(Id) into v_count from SettingType where Name='ByDefaultMoveEntireBatchToExceptionState';
    if (v_count = 0) then
      insert into SettingType(Name, Type) values('ByDefaultMoveEntireBatchToExceptionState', 3);
    end if;
  
    select count(Id) into v_count from SettingType where Name='MoveEntireBatchToExceptionState';
    if (v_count = 0) then
      insert into SettingType(Name, Type) values('MoveEntireBatchToExceptionState', 3);
    end if;
End;
/

-- End Move Entire Batch to Exception State feature

-- Extend LastIP and ClientIP to support IPv6
DECLARE
v_count NUMBER :=0;
allready_null EXCEPTION; 
PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'USER_'
                                                     AND column_name = 'LASTIP'
													 AND CHAR_COL_DECL_LENGTH = 45;
IF v_count = 0 THEN
	EXECUTE IMMEDIATE 'ALTER TABLE "USER_" MODIFY ("LASTIP" NVARCHAR2(45) DEFAULT '''')';
  BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "USER_" MODIFY ("LASTIP" NULL)';
  EXCEPTION
    WHEN allready_null THEN
          null;
  END;
END IF;

END;
/

DECLARE
v_count NUMBER :=0;
allready_null EXCEPTION; 
PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'BDD_USERAUDIT'
                                                     AND column_name = 'CLIENTIP'
													 AND CHAR_COL_DECL_LENGTH = 45;
IF v_count = 0 THEN
	EXECUTE IMMEDIATE 'ALTER TABLE "BDD_USERAUDIT" MODIFY ("CLIENTIP" NVARCHAR2(45) DEFAULT '''')';
  BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "BDD_USERAUDIT" MODIFY ("CLIENTIP" NULL)';
  EXCEPTION
    WHEN allready_null THEN
          null;
  END;
END IF;
END;
/

-- Extend Login to support external authentification
DECLARE
v_count NUMBER :=0;
allready_NotNull EXCEPTION; 
PRAGMA EXCEPTION_INIT(allready_NotNull, -1442);
BEGIN
SELECT COUNT(*) INTO v_count FROM USER_TAB_COLUMNS WHERE table_name =  'USER_'
                                                     AND column_name = 'LOGIN'
													 AND CHAR_COL_DECL_LENGTH = 256;
IF v_count = 0 THEN
	EXECUTE IMMEDIATE 'ALTER TABLE "USER_" MODIFY ("LOGIN" NVARCHAR2(256) DEFAULT '''')';
  BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "USER_" MODIFY ("LOGIN" NOT NULL)';
  EXCEPTION
    WHEN allready_NotNull THEN
          null;
  END;
END IF;
END;
/

COMMIT;