OAT » TPI Admin

CREATE TABLE dbo.SUMACCertifiedCopyDetails (
	CertifiedCopyDetailId int IDENTITY(1,1) NOT NULL,
	CertifiedCopyId int NOT NULL,
	StatusCode int NOT NULL,
	DeniedReason varchar(250) NULL,
	IsHidden bit DEFAULT 0 NOT NULL,
	HiddenReason varchar(100) NULL,
	CreatedDate datetime NOT NULL,
	CreatedBy uniqueidentifier NOT NULL,
	ModifiedDate datetime NOT NULL,
	ModifiedBy uniqueidentifier NOT NULL,
	UpdateVersion timestamp NOT NULL,
	CONSTRAINT PK__CertifiedCopyDetailId PRIMARY KEY (CertifiedCopyDetailId),
	CONSTRAINT FK_fromSUMACCertifiedCopyDetailsToSUMACCertifiedCopyOnActionCertifiedCopyId FOREIGN KEY (CertifiedCopyId) REFERENCES dbo.SUMACCertifiedCopy(CertifiedCopyId),
	CONSTRAINT FK_fromSUMACCertifiedCopyDetailsToSUMACCatalogValue FOREIGN KEY (StatusCode) REFERENCES dbo.SUMACCatalogValue(Code)
);

INSERT INTO SUMACCatalogValueType (Code,DescriptionES,IsHidden,HiddenReason,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy) VALUES
(19, 'Estatus de Copias Certificadas' ,0 ,NULL ,GETDATE(), 'BA188E4A-E2B5-45F3-9835-32C94121C00E' ,GETDATE(), 'BA188E4A-E2B5-45F3-9835-32C94121C00E');

INSERT INTO SUMACCatalogValue (Code,DescriptionES,CatalogTypeCode,IsHidden,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy,IsLocked,Comment) 
VALUES (350, 'Pendiente de Pago', 19, 0, GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', 1, null),
	(351, 'Pendiente de Exencion', 19, 0, GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', 1, null),
	(352, 'Exencion Denegada', 19, 0, GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', 1, null),
	(353, 'Completado', 19, 0, GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', GETDATE(), '3FC398D2-D249-4D64-BFD9-257898F9E75D', 1, null)

-- query para todas las CC existentes
INSERT INTO dbo.SUMACCertifiedCopyDetails (
    CertifiedCopyId,
    StatusCode,
    CreatedDate,
    CreatedBy,
    ModifiedDate,
    ModifiedBy
)
SELECT 
	cc.CertifiedCopyId,
    CASE 
        WHEN cf.StatusCode = 1 AND cf.IsExempt = 0 THEN 350
        WHEN cf.StatusCode = 1 AND cf.IsExempt = 1 THEN 351
        WHEN cf.StatusCode = 5 THEN 353
    END AS StatusCode,
    GETDATE(),
	'3FC398D2-D249-4D64-BFD9-257898F9E75D',
    GETDATE(),
    '3FC398D2-D249-4D64-BFD9-257898F9E75D'
FROM SUMACCertifiedCopy cc WITH (NOLOCK)
INNER JOIN SUMACCourtFeeRelatedRecord cfrr WITH (NOLOCK)
    ON cfrr.RelatedRecordId = cc.RecordId
    AND cfrr.IsHidden = 0
INNER JOIN SUMACCourtFee cf WITH (NOLOCK)
    ON cf.CourtFeeId = cfrr.CourtFeeId
    AND cf.IsHidden = 0
LEFT JOIN SUMACCourtFeeStatus cfs WITH (NOLOCK)
    ON cfs.Code = cf.StatusCode
    AND cfs.IsHidden = 0
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.SUMACCertifiedCopyDetails ccd
    WHERE ccd.CertifiedCopyId = cc.CertifiedCopyId
);