OAT » TA Admin

PR ready for review: adr-seedbury — Task 16890: Copias certificadas: replicar proceso adoptado para SUMAC de Tribunal de Primera Instancia → status: Pending Review

Changes requested by EBS-SEEDBURY on PR #376

PR ready for review: EBS-SEEDBURY — Task 16890: Copias certificadas: replicar proceso adoptado para SUMAC de Tribunal de Primera Instancia → status: Pending Review

INSERT INTO catalogTypes (catalogTypeCode, catalogTypeDescription)
 VALUES (72, 'certifiedCopyStatus')
 
 INSERT INTO catalogValues (catalogValueCode, catalogValueDescription, catalogTypeCode, catalogTypeDescription, isLocked)
 VALUES 
 		(891, 'Pendiente de Pago', 72, 'certifiedCopyStatus', 1),
 		(892, 'Pendiente de Exención', 72, 'certifiedCopyStatus', 1),
 		(893, 'Exención Denegada', 72, 'certifiedCopyStatus', 1),
 		(894, 'Completado', 72, 'certifiedCopyStatus', 1)
 		
 CREATE TABLE certifiedCopyDetails (
 	certifiedCopyDetailId int IDENTITY(1,1) NOT NULL,
 	certifiedCopyId int NOT NULL,
 	certifiedCopyStatusCode int NOT NULL,
 	certifiedCopyStatusDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
 	certifiedCopyDeniedReason varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
 	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	CONSTRAINT PK__certifiedCopyDetail PRIMARY KEY (certifiedCopyDetailId),
	CONSTRAINT FK__certifiedCopyId FOREIGN KEY (certifiedCopyId) REFERENCES dbo.certifiedCopies(certifiedCopyId),
	CONSTRAINT FK__certifiedCopyStatusCode FOREIGN KEY (certifiedCopyStatusCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__certifiedCopy_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__certifiedCopy_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId)
 )
 
 CREATE TABLE documents (
 	documentId int IDENTITY(1,1) NOT NULL,
 	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	encoding1 uniqueidentifier DEFAULT newid() NOT NULL,
	encoding2 uniqueidentifier DEFAULT newid() NOT NULL,
	CONSTRAINT PK__documentId PRIMARY KEY (documentId),
	CONSTRAINT FK__documents_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__documents_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId),
	CONSTRAINT documents_unique UNIQUE (encoding1),
	CONSTRAINT documents_unique_1 UNIQUE (encoding2)
 )
 
 CREATE TABLE documentDetails (
 	documentDetailId int IDENTITY(1,1) NOT NULL,
 	documentId int NOT NULL,
 	documentRoleCode int NOT NULL,
	documentRoleDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	documentTypeCode int NOT NULL,
	documentTypeDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	description varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	url varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	fileExtension varchar(5) COLLATE Modern_Spanish_CI_AS NULL,
	fileName varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	hash varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	isConfidential bit DEFAULT 0 NOT NULL,
	orderPosition int NULL,
	fileSize int NULL,
	filePageCount int NULL,
 	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	CONSTRAINT PK__documentDetailId PRIMARY KEY (documentDetailId),
	CONSTRAINT FK__document_details_documentId FOREIGN KEY (documentId) REFERENCES dbo.documents(documentId),
	CONSTRAINT FK__document_details_role FOREIGN KEY (documentRoleCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__document_details_type FOREIGN KEY (documentTypeCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__document_details_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__document_details_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId),
 )
 
 CREATE TABLE certifiedCopyExemptionDocument (
 	certifiedCopyExemptionDocumentId int IDENTITY(1,1) NOT NULL,
 	certifiedCopyId int NOT NULL,
 	documentId int NOT NULL,
 	dateCreated dateTime DEFAULT getdate() NOT NULL,
 	creatorId int NOT NULL,
 	isDeleted bit DEFAULT 0 NOT NULL,
 	CONSTRAINT PK__certifiedCopyExemptionDocumentId PRIMARY KEY (certifiedCopyExemptionDocumentId)
 	CONSTRAINT FK__certifiedCopyExemptionDocument_certifiedCopyId FOREIGN KEY (certifiedCopyId) REFERENCES dbo.certifiedCopies(certifiedCopyId),
 	CONSTRAINT FK__certifiedCopyExemptionDocument_documentId FOREIGN KEY (documentId) REFERENCES dbo.documents(documentId),
 	CONSTRAINT FK__certifiedCopyExemptionDocument_creatorId FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId)
 )

Fix syntax errors

INSERT INTO catalogTypes
	(catalogTypeCode, catalogTypeDescription)
VALUES
	(72, 'certifiedCopyStatus')

INSERT INTO catalogValues
	(catalogValueCode, catalogValueDescription, catalogTypeCode, catalogTypeDescription, isLocked)
VALUES
	(891, 'Pendiente de Pago', 72, 'certifiedCopyStatus', 1),
	(892, 'Pendiente de Exención', 72, 'certifiedCopyStatus', 1),
	(893, 'Exención Denegada', 72, 'certifiedCopyStatus', 1),
	(894, 'Completado', 72, 'certifiedCopyStatus', 1)

CREATE TABLE certifiedCopyDetails
(
	certifiedCopyDetailId int IDENTITY(1,1) NOT NULL,
	certifiedCopyId int NOT NULL,
	certifiedCopyStatusCode int NOT NULL,
	certifiedCopyStatusDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	certifiedCopyDeniedReason varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	CONSTRAINT PK__certifiedCopyDetail PRIMARY KEY (certifiedCopyDetailId),
	CONSTRAINT FK__certifiedCopyId FOREIGN KEY (certifiedCopyId) REFERENCES dbo.certifiedCopies(certifiedCopyId),
	CONSTRAINT FK__certifiedCopyStatusCode FOREIGN KEY (certifiedCopyStatusCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__certifiedCopy_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__certifiedCopy_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId)
)

CREATE TABLE documents
(
	documentId int IDENTITY(1,1) NOT NULL,
	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	encoding1 uniqueidentifier DEFAULT newid() NOT NULL,
	encoding2 uniqueidentifier DEFAULT newid() NOT NULL,
	CONSTRAINT PK__documentId PRIMARY KEY (documentId),
	CONSTRAINT FK__documents_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__documents_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId),
	CONSTRAINT documents_unique UNIQUE (encoding1),
	CONSTRAINT documents_unique_1 UNIQUE (encoding2)
)

CREATE TABLE documentDetails
(
	documentDetailId int IDENTITY(1,1) NOT NULL,
	documentId int NOT NULL,
	documentRoleCode int NOT NULL,
	documentRoleDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	documentTypeCode int NOT NULL,
	documentTypeDescription varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	description varchar(250) COLLATE Modern_Spanish_CI_AS NOT NULL,
	url varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	fileExtension varchar(5) COLLATE Modern_Spanish_CI_AS NULL,
	fileName varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	hash varchar(250) COLLATE Modern_Spanish_CI_AS NULL,
	isConfidential bit DEFAULT 0 NOT NULL,
	orderPosition int NULL,
	fileSize int NULL,
	filePageCount int NULL,
	dateCreated datetime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	deleterId int NULL,
	dateDeleted datetime NULL,
	CONSTRAINT PK__documentDetailId PRIMARY KEY (documentDetailId),
	CONSTRAINT FK__document_details_documentId FOREIGN KEY (documentId) REFERENCES dbo.documents(documentId),
	CONSTRAINT FK__document_details_role FOREIGN KEY (documentRoleCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__document_details_type FOREIGN KEY (documentTypeCode) REFERENCES dbo.catalogValues(catalogValueCode),
	CONSTRAINT FK__document_details_creator FOREIGN KEY (creatorId) REFERENCES dbo.persons(personId),
	CONSTRAINT FK__document_details_deleter FOREIGN KEY (deleterId) REFERENCES dbo.persons(personId)
)

CREATE TABLE certifiedCopyExemptionDocument
(
	certifiedCopyExemptionDocumentId int IDENTITY(1,1) NOT NULL,
	certifiedCopyId int NOT NULL,
	documentId int NOT NULL,
	dateCreated dateTime DEFAULT getdate() NOT NULL,
	creatorId int NOT NULL,
	isDeleted bit DEFAULT 0 NOT NULL,
	CONSTRAINT PK__certifiedCopyExemptionDocumentId PRIMARY KEY (certifiedCopyExemptionDocumentId),
	CONSTRAINT FK__certifiedCopyExemptionDocument_certifiedCopyId FOREIGN KEY
	(certifiedCopyId) REFERENCES dbo.certifiedCopies
	(certifiedCopyId),
 	CONSTRAINT FK__certifiedCopyExemptionDocument_documentId FOREIGN KEY
	(documentId) REFERENCES dbo.documents
	(documentId),
 	CONSTRAINT FK__certifiedCopyExemptionDocument_creatorId FOREIGN KEY
	(creatorId) REFERENCES dbo.persons
	(personId)
 )

Alejandro De Jesús Reyes wrote in #note-17:

Fix syntax errors

Fixed*