-- ============================================================ -- Story #55 - Sales Document Coverage incremental update -- ============================================================ -- Objetivo: -- Ajustar el modelo de documentos de venta ya creado para soportar -- facturacion por periodo/capita y trazabilidad de presupuestos cubiertos. -- -- Contexto: -- El scaffold actualizado ya incluye: -- - PhS_SalesDocuments -- - PhS_SalesDocumentDetails -- - PhS_SalesFiscalDocuments -- - PhS_SalesFiscalDocumentAssociations -- -- Decisiones: -- - No se recrean tablas existentes. -- - No se modifican modelos EF manualmente. -- - Se agrega periodo a la cabecera comercial. -- - Se agrega PhS_SalesDocumentCoverage como verdad de cobertura. -- - Coverage se usa tanto para facturacion 1 a 1 como para capita. -- - quote_id en PhS_SalesDocuments queda como referencia principal/rapida, -- no como verdad completa de facturacion. -- ============================================================ SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; -- ============================================================ -- PhS_SalesDocuments: periodo comercial opcional -- ============================================================ IF COL_LENGTH('dbo.PhS_SalesDocuments', 'period_from') IS NULL BEGIN ALTER TABLE dbo.PhS_SalesDocuments ADD period_from DATETIME NULL; END; IF COL_LENGTH('dbo.PhS_SalesDocuments', 'period_to') IS NULL BEGIN ALTER TABLE dbo.PhS_SalesDocuments ADD period_to DATETIME NULL; END; IF OBJECT_ID('dbo.CK_PhS_SalesDocuments_Period', 'C') IS NULL BEGIN EXEC sys.sp_executesql N' ALTER TABLE dbo.PhS_SalesDocuments ADD CONSTRAINT CK_PhS_SalesDocuments_Period CHECK (period_from IS NULL OR period_to IS NULL OR period_to >= period_from); '; END; -- ============================================================ -- PhS_SalesDocumentCoverage -- ============================================================ -- Representa que presupuestos/casos quedan cubiertos por un documento -- de venta. Es la fuente real para determinar si un presupuesto queda -- pendiente de facturacion o ya fue cubierto/facturado. -- ============================================================ IF OBJECT_ID('dbo.PhS_SalesDocumentCoverage', 'U') IS NULL BEGIN CREATE TABLE dbo.PhS_SalesDocumentCoverage ( id INT IDENTITY(1,1) NOT NULL, salesdocument_id INT NOT NULL, salesdocumentdetail_id INT NULL, quote_id INT NOT NULL, quote_detail_id INT NULL, -- Tipo de cobertura. -- Valores esperados en Domain: -- 1 = Direct -- 2 = Capita -- 3 = Adjustment coverage_type INT NOT NULL CONSTRAINT DF_PhS_SalesDocumentCoverage_coverage_type DEFAULT (1), -- Porcentaje/importe cubierto respecto del presupuesto/caso. -- En facturacion 1 a 1 normalmente sera 100. -- En obra social / particular puede ser 60/40. -- En capita puede ser 100 aunque la linea facturada sea agregada. coverage_percentage DECIMAL(9,4) NULL, coverage_amount DECIMAL(18,2) NULL, period_from DATETIME NULL, period_to DATETIME NULL, notes NVARCHAR(MAX) NULL, createdat DATETIME NOT NULL CONSTRAINT DF_PhS_SalesDocumentCoverage_createdat DEFAULT (GETDATE()), modifiedat DATETIME NULL, CONSTRAINT PK_PhS_SalesDocumentCoverage PRIMARY KEY (id), CONSTRAINT FK_PhS_SalesDocumentCoverage_SalesDocuments FOREIGN KEY (salesdocument_id) REFERENCES dbo.PhS_SalesDocuments(id), CONSTRAINT FK_PhS_SalesDocumentCoverage_SalesDocumentDetails FOREIGN KEY (salesdocumentdetail_id) REFERENCES dbo.PhS_SalesDocumentDetails(id), CONSTRAINT FK_PhS_SalesDocumentCoverage_QuoteHeaders FOREIGN KEY (quote_id) REFERENCES dbo.PhS_QuoteHeaders(id), CONSTRAINT FK_PhS_SalesDocumentCoverage_QuoteDetails FOREIGN KEY (quote_detail_id) REFERENCES dbo.PhS_QuoteDetails(id), CONSTRAINT CK_PhS_SalesDocumentCoverage_Period CHECK (period_from IS NULL OR period_to IS NULL OR period_to >= period_from), CONSTRAINT CK_PhS_SalesDocumentCoverage_Percentage CHECK (coverage_percentage IS NULL OR (coverage_percentage > 0 AND coverage_percentage <= 100)) ); END; -- ============================================================ -- Indices -- ============================================================ IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Document' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')) BEGIN CREATE INDEX IX_PhS_SalesDocumentCoverage_Document ON dbo.PhS_SalesDocumentCoverage(salesdocument_id); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_DocumentDetail' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')) BEGIN CREATE INDEX IX_PhS_SalesDocumentCoverage_DocumentDetail ON dbo.PhS_SalesDocumentCoverage(salesdocumentdetail_id); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Quote' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')) BEGIN CREATE INDEX IX_PhS_SalesDocumentCoverage_Quote ON dbo.PhS_SalesDocumentCoverage(quote_id); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_QuoteDetail' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')) BEGIN CREATE INDEX IX_PhS_SalesDocumentCoverage_QuoteDetail ON dbo.PhS_SalesDocumentCoverage(quote_detail_id); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocumentCoverage_Period' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage')) BEGIN CREATE INDEX IX_PhS_SalesDocumentCoverage_Period ON dbo.PhS_SalesDocumentCoverage(period_from, period_to); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_PhS_SalesDocuments_Period' AND object_id = OBJECT_ID(N'dbo.PhS_SalesDocuments')) BEGIN EXEC sys.sp_executesql N' CREATE INDEX IX_PhS_SalesDocuments_Period ON dbo.PhS_SalesDocuments(period_from, period_to); '; END; -- ============================================================ -- Extended properties / MS_Description -- ============================================================ IF NOT EXISTS ( SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocuments') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocuments'), N'period_from', 'ColumnId') AND name = N'MS_Description' ) BEGIN EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Fecha inicial del periodo comercial facturado. Aplica especialmente a facturacion por capita o periodos mensuales.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'PhS_SalesDocuments', @level2type = N'COLUMN', @level2name = N'period_from'; END; IF NOT EXISTS ( SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocuments') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocuments'), N'period_to', 'ColumnId') AND name = N'MS_Description' ) BEGIN EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Fecha final del periodo comercial facturado. Aplica especialmente a facturacion por capita o periodos mensuales.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'PhS_SalesDocuments', @level2type = N'COLUMN', @level2name = N'period_to'; END; IF NOT EXISTS ( SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = 0 AND name = N'MS_Description' ) BEGIN EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Presupuestos/casos cubiertos por un documento de venta. Es la fuente real para determinar si un presupuesto queda pendiente de facturacion o ya fue cubierto, incluyendo facturacion 1 a 1 y capita.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'PhS_SalesDocumentCoverage'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'id', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identificador interno de la cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'id'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'salesdocument_id', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Documento de venta que cubre el presupuesto/caso.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'salesdocument_id'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'salesdocumentdetail_id', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Detalle del documento de venta asociado a esta cobertura, cuando aplique. En capita puede apuntar a la linea agregada mensual.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'salesdocumentdetail_id'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'quote_id', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Presupuesto/caso cubierto por el documento de venta. Se usa tanto para facturacion directa como para capita.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'quote_id'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'quote_detail_id', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Detalle de presupuesto cubierto, cuando se requiera trazabilidad granular por item.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'quote_detail_id'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_type', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tipo de cobertura. Valores esperados en Domain: Direct, Capita, Adjustment.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_type'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_percentage', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Porcentaje del presupuesto/caso cubierto por el documento. Permite 100% en facturacion directa o particiones 60/40.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_percentage'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'coverage_amount', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Importe de referencia cubierto por el documento, cuando aplique.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'coverage_amount'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'period_from', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha inicial del periodo de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'period_from'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'period_to', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fecha final del periodo de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'period_to'; END; IF NOT EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage') AND minor_id = COLUMNPROPERTY(OBJECT_ID(N'dbo.PhS_SalesDocumentCoverage'), N'notes', 'ColumnId') AND name = N'MS_Description') BEGIN EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Notas internas de cobertura.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PhS_SalesDocumentCoverage', @level2type=N'COLUMN', @level2name=N'notes'; END; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH;