IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0CLIENTTASKS')
BEGIN
DROP TABLE LD0P0CLIENTTASKS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0COMPONENTDOCUMENTTYPEMAPPING')
BEGIN
DROP TABLE LD0P0COMPONENTDOCUMENTTYPEMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0DECISIONEHRDOCUMENTTYPEMAPPING')
BEGIN
DROP TABLE LD0P0DECISIONEHRDOCUMENTTYPEMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0SAVEDCOMPONENTS')
BEGIN
DROP TABLE LD0P0SAVEDCOMPONENTS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSSTEPCOMPONENTMAPPING')
BEGIN
DROP TABLE LD0P0PROCESSSTEPCOMPONENTMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0EHRWelfareDocuments')
BEGIN
DROP TABLE LD0P0EHRWelfareDocuments
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0JOURNALNOTE')
BEGIN
DROP TABLE LD0P0JOURNALNOTE
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0NAVIGATIONCOMPONENTMAPPING')
BEGIN
DROP TABLE LD0P0NAVIGATIONCOMPONENTMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0NEXTPROCESSMAPPING')
BEGIN
DROP TABLE LD0P0NEXTPROCESSMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSLINKS')
BEGIN
DROP TABLE LD0P0PROCESSLINKS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP TABLE LD0P0PROCESSSERVICECATEGORYMAPPING
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSSPECIFICTYPEDETAILS')
BEGIN
DROP TABLE LD0P0PROCESSSPECIFICTYPEDETAILS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSSUMMARY')
BEGIN
DROP TABLE LD0P0PROCESSSUMMARY
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSSTEPS')
BEGIN
DROP TABLE LD0P0PROCESSSTEPS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0TASK')
BEGIN
DROP TABLE LD0P0TASK
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSMETADATA')
BEGIN
DROP TABLE LD0P0PROCESSMETADATA
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESS')
BEGIN
DROP TABLE LD0P0PROCESS
END
;
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0PROCESSCOMPONENTS')
BEGIN
DROP TABLE LD0P0PROCESSCOMPONENTS
END
;


CREATE TABLE LD0P0PROCESS
(
	ID INT IDENTITY(1,1) NOT NULL,
	NAME VARCHAR(100) NOT NULL,
	DESCRIPTION VARCHAR(100) NULL,
	ACTIVE DECIMAL(1) NOT NULL,
	PROCESSTYPEID INT DEFAULT 0 NOT NULL,
	PROCESSOWNERTYPE TINYINT DEFAULT 0 NOT NULL,
	CANOPENTIMEBOOK DECIMAL(1) DEFAULT 0 NOT NULL,
	CONSTRAINT 	LD0P0PROCESS_PKID PRIMARY KEY (ID)
);

CREATE TABLE LD0P0PROCESSCOMPONENTS
(
	ID INT IDENTITY(1,1) NOT NULL,
	COMPONENTMANIFESTID VARCHAR(100) NOT NULL,
	NAME VARCHAR(100) NOT NULL,
	FRIENDLYNAME VARCHAR(100) NOT NULL,
	ACTIVE DECIMAL(1) NOT NULL,
	COMPONENTSTATE INT NOT NULL,
	CONSTRAINT 	LD0P0PROCESSCOMPONENTS_PKID PRIMARY KEY (ID)
)
;

CREATE TABLE LD0P0TASK
(
TASKID				INT IDENTITY(1,1) NOT NULL,
TASKNAME			VARCHAR(100) NOT NULL,
PROCESSID			INT NOT NULL,
TEXT				VARCHAR(250) NULL,
STARTDATE			VARCHAR(8) NOT NULL,
ENDDATE				VARCHAR(8) NULL,
PRIORITY			INT NULL,
ISMANDATORY			DECIMAL(1,0) NOT NULL,
UPDATEDDATE			VARCHAR(8) NOT NULL,
UPDATEDSIGNATURE	VARCHAR(10) NOT NULL,
ISADDON				DECIMAL(1) DEFAULT 0 NOT NULL,
CONSTRAINT 	LD0P0TASK_PKID PRIMARY KEY (TASKID),
FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0PROCESSMETADATA
(
	ID INT IDENTITY(1,1) NOT NULL,
	OWNERID INT NOT NULL,
	MODIFIEDBY VARCHAR(100) NOT NULL,
	PROCESSID INT NOT NULL,
	SELECTEDSTEPINDEX INT NOT NULL,
	COMPLETEDSTEPINDEX INT NOT NULL,
	ISPROCESSCOMPLETED DECIMAL(1) NOT NULL,
	PERSONKEY  INT DEFAULT 0 NOT NULL,
	PREVIOUSMETADATAID INT NOT NULL,
	CASEWORKERID VARCHAR(100) NULL,
	COCASEWORKERID VARCHAR(100) NULL, 
	STARTDATE VARCHAR(8) DEFAULT '20100101' NOT NULL, 
	ENDDATE VARCHAR(8) NULL,
	ORGANIZATIONID INT NULL,
	CASEWORKERORGUNITID VARCHAR(10) NULL,
	COCASEWORKERORGUNITID VARCHAR(10) NULL,
	SERVICECATEGORYID INT NULL,
	CONSTRAINT 		LD0P0PROCESSMETADATA_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0CLIENTTASKS
(
	ID int IDENTITY(1,1) NOT NULL,
	TASKID int NOT NULL,
	PROCESSMETADATAID int NOT NULL,
	CASEWORKERID varchar(10) NULL,
	ISCOMPLETED decimal(1, 0) NOT NULL,
	COMPLETEDDATE varchar(8) NOT NULL,
	ORGANIZATIONID int NULL,
	ORGANIZATIONUNITID varchar(10) NULL,
	TASKNOTE NVARCHAR(2000) NULL,
	CONSTRAINT 	LD0P0CLIENTTASKS_PKID PRIMARY KEY (ID),
	FOREIGN KEY (TASKID) REFERENCES LD0P0TASK(TASKID),
	FOREIGN KEY (PROCESSMETADATAID) REFERENCES LD0P0PROCESSMETADATA(ID)
)
;

CREATE TABLE LD0P0PROCESSSTEPS
(
	ID INT IDENTITY(1,1) NOT NULL,
	NAME VARCHAR(100) NOT NULL,
	FRIENDLYNAME VARCHAR(100) NOT NULL,
	DESCRIPTION VARCHAR(100) NULL,
	PROCESSID INT DEFAULT 0 NOT NULL,
	STEPORDER INT DEFAULT 0 NOT NULL,
	ISTASKAUTOOPEN DECIMAL(1) NOT NULL,
	CONSTRAINT 		LD0P0PROCESSSTEPS_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
)
;

CREATE TABLE LD0P0PROCESSSTEPCOMPONENTMAPPING
(
	ID INT IDENTITY(1,1) NOT NULL,
	STEPID INT NOT NULL,
	COMPONENTID INT NOT NULL,
	ORDERNUMBER INT NOT NULL,
	MANDATORY DECIMAL(1) NOT NULL,
	ISACTIVE DECIMAL(1) DEFAULT 1 NOT NULL,
	CONSTRAINT 	LD0P0PROCESSSTEPCOMPONENTMAPPING_PKID PRIMARY KEY (ID),
	FOREIGN KEY (STEPID) REFERENCES LD0P0PROCESSSTEPS(ID),
	FOREIGN KEY (COMPONENTID) REFERENCES LD0P0PROCESSCOMPONENTS(ID)
)
;

CREATE TABLE LD0P0COMPONENTDOCUMENTTYPEMAPPING
(                          
	ID INT IDENTITY(1,1) NOT NULL,
	STEPCOMPONENTMAPPINGID INT NOT NULL,
	DOCUMENTTYPEOID VARCHAR(100) NOT NULL,
	EHRDOCUMENTNAME VARCHAR(200) NOT NULL,
	ISDOCUMENTTYPEUSED DECIMAL(1,0) DEFAULT 0 NOT NULL,
	ISEHRDOCUMENTACTIVE DECIMAL(1) NOT NULL,
	CONSTRAINT 	LD0P0COMPONENTDOCUMENTTYPEMAPPING_PKID PRIMARY KEY (ID),
	FOREIGN KEY (STEPCOMPONENTMAPPINGID) REFERENCES LD0P0PROCESSSTEPCOMPONENTMAPPING(ID)
);

CREATE TABLE LD0P0DECISIONEHRDOCUMENTTYPEMAPPING 
(
	ID INT IDENTITY(1,1) NOT NULL,
	STEPCOMPONENTMAPPINGID INT NOT NULL,
	DECISIONTYPEID INT NOT NULL,
	EHRDOCUMENTNAME VARCHAR(200) NULL,
	ISEHRDOCUMENTACTIVE DECIMAL(1,0) NOT NULL,
	ISDOCUMENTTYPEUSED decimal(1, 0) NOT NULL,
	CONSTRAINT LD0P0DECISIONEHRDOCUMENTTYPEMAPPING_PKID PRIMARY KEY CLUSTERED (ID),
	FOREIGN KEY (STEPCOMPONENTMAPPINGID) REFERENCES LD0P0PROCESSSTEPCOMPONENTMAPPING(ID)
)
;

CREATE TABLE LD0P0SAVEDCOMPONENTS(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	PROCESSMETADATAID INT NOT NULL,
	STEPCOMPONENTMAPPINGID INT NOT NULL,
	FOREIGN KEY (PROCESSMETADATAID) REFERENCES LD0P0PROCESSMETADATA(ID),
	FOREIGN KEY (STEPCOMPONENTMAPPINGID) REFERENCES LD0P0PROCESSSTEPCOMPONENTMAPPING(ID)
)
;

CREATE TABLE LD0P0EHRWelfareDocuments
(
	ID INT IDENTITY(1,1) NOT NULL,
	ENCOUNTERID VARCHAR(100) NOT NULL,
	DSCID VARCHAR(100) NOT NULL,
	METADATAID INT NOT NULL,
	MODIFYDATE VARCHAR(8) NOT NULL,
	CASEWORKERID VARCHAR(100) NOT NULL,
	COMPONENTDOCUMENTTYPEMAPPINGID INT NOT NULL,
	COMPOSITIONSTATE TINYINT NOT NULL,
	DECISIONID INT NOT NULL,
	EHRDOCUMENTTYPE TINYINT NOT NULL,
	CONSTRAINT 	LD0P0EHRWelfareDocuments_PKID PRIMARY KEY (ID),
	FOREIGN KEY (METADATAID) REFERENCES LD0P0PROCESSMETADATA(ID)
)
;

CREATE TABLE LD0P0JOURNALNOTE
(
	ID 			INT IDENTITY(1,1) NOT NULL,
	PROCESSID 	INT NOT NULL,
	NAME 		VARCHAR(500) NOT NULL,
	ISACTIVE 	DECIMAL(1, 0) NOT NULL,
	CONSTRAINT LD0P0JOURNALNOTE_PKID PRIMARY KEY CLUSTERED (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
)
;

CREATE TABLE LD0P0NAVIGATIONCOMPONENTMAPPING
(
	ID INT IDENTITY(1,1) NOT NULL,
	PROCESSID INT NOT NULL,
	COMPONENTMANIFESTID VARCHAR(100) NOT NULL,
	DISPLAYNAME VARCHAR(100) NOT NULL,
	CONSTRAINT 	LD0P0NAVIGATIONCOMPONENTMAPPING_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0NEXTPROCESSMAPPING
(
	ID INT IDENTITY(1,1) NOT NULL,
	PROCESSID INT NOT NULL,
	NEXTPROCESSID INT NOT NULL,
	SHOWLINKEDPROCESS DECIMAL(1) DEFAULT 0 NOT NULL,
	CONSTRAINT 	LD0P0NEXTPROCESSMAPPING_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID),
	FOREIGN KEY (NEXTPROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0PROCESSLINKS
(
ID INT IDENTITY(1,1) NOT NULL,
PROCESSID INT NOT NULL CONSTRAINT LD0P0PROCESSLINKS_FK REFERENCES LD0P0PROCESS(ID),
DESCRIPTION VARCHAR(100) NOT NULL,
URL VARCHAR(200) NOT NULL,
ORDERNUMBER INT NOT NULL,
CONSTRAINT 	LD0P0PROCESSLINKS_PKID PRIMARY KEY (ID),
FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0PROCESSSERVICECATEGORYMAPPING
(
	ID int IDENTITY(1,1) NOT NULL,
	PROCESSID int NOT NULL,
	SERVICECATEGORYID int NOT NULL,
	CONSTRAINT 	LD0P0PROCESSSERVICECATEGORYMAPPING_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
)
;

CREATE TABLE LD0P0PROCESSSPECIFICTYPEDETAILS
(
	ID INT IDENTITY(1,1) NOT NULL,
	PROCESSID INT NOT NULL,
	KOD DECIMAL(9) NOT NULL,
	KODTYP VARCHAR(10) NOT NULL,
	ISCONFIGURED DECIMAL(1) DEFAULT 1 NOT NULL,
	CONSTRAINT 	LD0P0PROCESSSPECIFICTYPEDETAILS_PKID PRIMARY KEY (ID),
	FOREIGN KEY (PROCESSID) REFERENCES LD0P0PROCESS(ID)
);

CREATE TABLE LD0P0PROCESSSUMMARY
(
ID			INT NOT NULL,
PROCESSMETADATAID	INT NOT NULL,
STEPID			INT NOT NULL,
SUMMARYTYPE INT NOT NULL,
SUMMARYTYPEID INT NOT NULL,
SUMMARYDETAILS		VARCHAR(250) NOT NULL,
CONSTRAINT 		LD0P0PROCESSSUMMARY_PKID PRIMARY KEY (ID),
FOREIGN KEY (PROCESSMETADATAID) REFERENCES LD0P0PROCESSMETADATA(ID),
FOREIGN KEY (STEPID) REFERENCES LD0P0PROCESSSTEPS(ID)
);

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0CLIENTTASKS')
BEGIN
DROP VIEW LD0V0CLIENTTASKS
END
;
CREATE VIEW LD0V0CLIENTTASKS AS
	SELECT * FROM LD0P0CLIENTTASKS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0COMPONENTDOCUMENTTYPEMAPPING')
BEGIN
DROP VIEW LD0V0COMPONENTDOCUMENTTYPEMAPPING
END
;
CREATE VIEW LD0V0COMPONENTDOCUMENTTYPEMAPPING AS
	SELECT * FROM LD0P0COMPONENTDOCUMENTTYPEMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0DECISIONEHRDOCUMENTTYPEMAPPING')
BEGIN
DROP VIEW LD0V0DECISIONEHRDOCUMENTTYPEMAPPING
END
;
CREATE VIEW LD0V0DECISIONEHRDOCUMENTTYPEMAPPING AS
	SELECT * FROM LD0P0DECISIONEHRDOCUMENTTYPEMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0SAVEDCOMPONENTS')
BEGIN
DROP VIEW LD0V0SAVEDCOMPONENTS
END
;
CREATE VIEW LD0V0SAVEDCOMPONENTS AS
	SELECT * FROM LD0P0SAVEDCOMPONENTS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSTEPCOMPONENTMAPPING')
BEGIN
DROP VIEW LD0V0PROCESSSTEPCOMPONENTMAPPING
END
;
CREATE VIEW LD0V0PROCESSSTEPCOMPONENTMAPPING AS
	SELECT * FROM LD0P0PROCESSSTEPCOMPONENTMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0EHRWelfareDocuments')
BEGIN
DROP VIEW LD0V0EHRWelfareDocuments
END
;
CREATE VIEW LD0V0EHRWelfareDocuments AS
	SELECT * FROM LD0P0EHRWelfareDocuments
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0JOURNALNOTE')
BEGIN
DROP VIEW LD0V0JOURNALNOTE
END
;
CREATE VIEW LD0V0JOURNALNOTE AS
	SELECT * FROM LD0P0JOURNALNOTE
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0NAVIGATIONCOMPONENTMAPPING')
BEGIN
DROP VIEW LD0V0NAVIGATIONCOMPONENTMAPPING
END
;
CREATE VIEW LD0V0NAVIGATIONCOMPONENTMAPPING AS
	SELECT * FROM LD0P0NAVIGATIONCOMPONENTMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0NEXTPROCESSMAPPING')
BEGIN
DROP VIEW LD0V0NEXTPROCESSMAPPING
END
;
CREATE VIEW LD0V0NEXTPROCESSMAPPING AS
	SELECT * FROM LD0P0NEXTPROCESSMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSLINKS')
BEGIN
DROP VIEW LD0V0PROCESSLINKS
END
;
CREATE VIEW LD0V0PROCESSLINKS AS
	SELECT * FROM LD0P0PROCESSLINKS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSERVICECATEGORYMAPPING')
BEGIN
DROP VIEW LD0V0PROCESSSERVICECATEGORYMAPPING
END
;
CREATE VIEW LD0V0PROCESSSERVICECATEGORYMAPPING AS
	SELECT * FROM LD0P0PROCESSSERVICECATEGORYMAPPING
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSPECIFICTYPEDETAILS')
BEGIN
DROP VIEW LD0V0PROCESSSPECIFICTYPEDETAILS
END
;
CREATE VIEW LD0V0PROCESSSPECIFICTYPEDETAILS AS
	SELECT * FROM LD0P0PROCESSSPECIFICTYPEDETAILS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSUMMARY')
BEGIN
DROP VIEW LD0V0PROCESSSUMMARY
END
;
CREATE VIEW LD0V0PROCESSSUMMARY AS
	SELECT * FROM LD0P0PROCESSSUMMARY
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSSTEPS')
BEGIN
DROP VIEW LD0V0PROCESSSTEPS
END
;
CREATE VIEW LD0V0PROCESSSTEPS AS
	SELECT * FROM LD0P0PROCESSSTEPS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0TASK')
BEGIN
DROP VIEW LD0V0TASK
END
;
CREATE VIEW LD0V0TASK AS
	SELECT * FROM LD0P0TASK
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSMETADATA')
BEGIN
DROP VIEW LD0V0PROCESSMETADATA
END
;
CREATE VIEW LD0V0PROCESSMETADATA AS
	SELECT * FROM LD0P0PROCESSMETADATA
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESS')
BEGIN
DROP VIEW LD0V0PROCESS
END
;
CREATE VIEW LD0V0PROCESS AS
	SELECT * FROM LD0P0PROCESS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0PROCESSCOMPONENTS')
BEGIN
DROP VIEW LD0V0PROCESSCOMPONENTS
END
;
CREATE VIEW LD0V0PROCESSCOMPONENTS AS
	SELECT * FROM LD0P0PROCESSCOMPONENTS
;

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V1EHRWELFAREDOCUMENTTYPES')
BEGIN
DROP VIEW LD0V1EHRWELFAREDOCUMENTTYPES
END
;
CREATE VIEW LD0V1EHRWELFAREDOCUMENTTYPES          
AS          
SELECT           
 D.DOCUMENTTYPEOID,          
 K.VALUE DOCUMENTTYPEDESCRIPTION,           
 S.PROCESSID,          
 C.COMPONENTMANIFESTID AS PROCESSCOMPONENTNAME,          
 CM.ID COMPONENTMAPPINGID,           
 D.EHRDOCUMENTNAME,         
 D.ID as COMPONENTDOCUMENTTYPEMAPPINGID,          
 D.ISEHRDOCUMENTACTIVE          
FROM LD0V0PROCESSSTEPS S             
INNER JOIN LD0V0PROCESSSTEPCOMPONENTMAPPING CM ON S.ID = CM.STEPID             
INNER JOIN LD0V0COMPONENTDOCUMENTTYPEMAPPING D ON CM.ID = D.STEPCOMPONENTMAPPINGID            
LEFT JOIN LD0V0KANTA K ON K.OID = D.DOCUMENTTYPEOID
INNER JOIN LD0V0PROCESSCOMPONENTS C  ON C.ID = CM.COMPONENTID
;

INSERT INTO LD0P0DOCUMENTQUEUE(DOCUMENTGROUP,ARCHIVEACTION,ARCHIVECOUNT,CASEID,CASESTATE,CLOSEDATE)
SELECT 2,2,0,C.id,2,GETDATE() from LD0P0CASE c where ARCHSTATE IN (3,4) 
and c.ID IN (select id from LD0P0CASE where enddat='9999-12-31 00:00:00.000' and ID not in (select CASEID from LD0P0H) and ID not in (select CASEID from LD0P0C));
;
UPDATE LD0P0CASE SET STATE = 2, ARCHSTATE = 3, ENDDAT=GETDATE()
  WHERE ID IN (select id from LD0P0CASE where ID not in 
  (select CASEID from LD0P0H) and ID not in (select caseid from LD0P0C))
 AND ARCHSTATE in (3,4) and enddat='9999-12-31 00:00:00.000';
;


