Proyecto I 

Bases de datos 

II Semestre 2018 

Wilson Lopez , Oscar Cortez 

Prefesor : Franco Quiros

Análisis de Resultados

Tiempo invertido: 2168 Minutos 35 horas

El tiempo invertido es un aproximado del tiempo de trabajo , se considera tiempo de trabajo individual tanto como grupal , por lo tanto es un aproximado ya que muchas veces ese tiempo se dobla debido al trabajo de ambos a la vez.

Solución al problema: En el siguiente análisis se realizara una leve explicación de cada una de las tablas presentes en nuestro proyecto , así como su funcionalidad y los datos que contienen. También se explicara la funcionalidad de cada uno de los procedimientos almacenados que se realizaron para darle solución al problema.

Tablas: 

-Abonado: La tabla abonado es un tabla realmente simple y se encarga de contener los abonados dueños de las propiedades , inicialmente la tabla abonado solamente tenia el nombre de cada uno de los abonados , pero conforme avanzo el proyecto se agrego un nuevo campo de identificación este se utiliza para identificar el abonado único en el inicio de sesión de la capa lógica

-ConsumoAgua: Esta tabla contiene una serie de datos que funcionan como una lectura de la cantidad de metros cúbicos de agua que consume mensualmente cada propiedad , estos datos también se utilizan a la hora de la facturación , pues el recibo se cobra acorde el consumo de agua de cada propiedad. 

-Linea: Esta tabla es un complemento de las facturas , cada vez que se crea una factura se crea una linea por cada servicio que se incluye en la factura , se puede decir que cumple la función de detallar la factura

-Municipalidad: La municipalidad es la tabla sujeto , en esta se detalla información de configuración así como información acerca de la municipalidad de la propia municipalidad como el código postal , nombre y precio del agua por metro cubico. 

-Propiedad: La tabla propiedad contiene todas las propiedades de todas las municipalidades registradas , aquí se relacionan con su respectivo dueño y con la municipalidad a la que pertenecen , son los que a la postre van a generar impuestos.

-Recibo: Es lo que maneja los cobros , el dia de emisión de cada municipalidad se crea un cobro para cada propiedad por los servicios que se le brindan , para eso se genera una entrada en la tabla recibo , estos recibos pueden estar en 2 estados , Pagado o pendiente , esto se refleja en la fecha de pago , pues si estos están pendientes esta fecha es inexistente y nula.

-ServicioXpropiedad: Esta tabla contiene todos los servicios que están asociados a una propiedad determinada.

-TipoServicio: Tiene los tipos de servicios que pueden ofrecer las distintas propiedades , tambien el valor de cada uno de los servicios y si son valores fijos o variantes.

Procedimientos almacenados: 

-Calculodeinteresmoratorio: Este procedimiento se encarga de calcular los intereses moratorias de los recibos , pero con una leve variante ya que se puede definir como un trigger que se ejecuta diariamente durante la simulación  y todos los días revisa si una factura ya esta vencida y empieza a acumular intereses.

-CancelarRecibo:Este procedimiento te permite hacer el pago de un recibo , por medio de su ID propiedad , este busca y calcula los intereses moratorias pendientes si es que hay , posteriormente el recibo se pone en condición de cancelado , en caso de que no exista ningún recibo que cancelar la respuesta es un 5 que indica que no hay recibos para dicha propiedad

-GenerarReciboa: Es un proceso de la simulación que revisas todos los días de cierre de las municipalidades y genera un recibo para cada propiedad tomando en cuenta los cargos fijos y el consumo de agua.

-ProcedureMain: Es el proceso principal se encarga de ejecutar la simulación , este inicia en una fecha inicial y simula el paso de los días hasta una fecha final ejecutando los demás stores procedures bajo un determinado orden y bajo ciertas condiciones.

-UltimoReciboPendiente: Este produce lo único que hace es darnos la ultima factura pendiente de una propiedad si es que esta existe , de otra forma la respuesta sera vacía

-readXML pagos: Este procedimiento se encarga de leer los pagos desde los xml , luego mapea las facturas pendientes para cada propiedad y realiza el respectivo pago del mismo.

-readXML... : Estos son una serie de procedimientos que únicamente se encarga de leer información directamente de la base de datos y la matea en sus respectivas tablas para luego durante la simulación , alimentarse de dicha información.

Diagrama:

Fecha Inicial : .30/05/2018
Fecha Final :  .30/05/2018
Hora Inicio: .1:15 PM
Hora Final: .6:12
Horas invertidas(Minutos): 
Colaboradores: Oscar Cortés.

Actividad

SP de mayor morosidad por personas y distritos

SP de corte de agua



Codigo

SP de morosidades

create procedure MayorMorosidad_Abonados

as
begin
select TOP 20 A.ID, A.Identificacion, A.Nombre, sum( R.PagoTotalNoIntereses + R.InteresMoratorio) as 'PagoTotal', count (R.ID) as 'Pendientes'
from Abonado A
inner join Propiedad P on P.FK_Abonado = A.ID
inner join Recibo R on R.FK_Propiedad = P.ID

where  R.TotalPagado = 0
group by A.Nombre, A.ID, A.Identificacion
order by PagoTotal DESC

end

go


create procedure MayorMorosidad_DistritoPendientes

as
begin
select TOP 1  P.CodPostal as 'Distrito', count (P.CodPostal) as 'Pendientes'
--SELECT *
from Propiedad P 
inner join Recibo R on R.FK_Propiedad = P.ID

where  R.TotalPagado = 0
group by P.CodPostal
order by Pendientes DESC

end

go

create procedure MayorMorosidad_DistritoMonto

as
begin
select TOP 1  P.CodPostal as 'Distrito', sum( R.PagoTotalNoIntereses + R.InteresMoratorio) as 'PagoTotal'
--SELECT *
from Propiedad P 
inner join Recibo R on R.FK_Propiedad = P.ID

where  R.TotalPagado = 0
group by P.CodPostal
order by PagoTotal DESC

end


go

Fecha Inicial : .30/05/2018
Fecha Final :  .30/05/2018
Hora Inicio: .12:52 AM
Hora Final: .6:12
Horas invertidas(Minutos): 320
Colaboradores: Oscar Cortés.

Actividad


Configurar la pc para poder cumplir los requisitos para correr la simulación -- .



Detectar usuarios para inicio de sesión -- .Listo e interfaz tambien


Filtrar recibos para abonados -- .

Consulta de recibos -- .

Configuración de municipalidad -- .

Configuración de abonados -- .

Problemas

No se puede crear la base de datos por fallos en los permisos -- .Se modifican los permisos luego de encontrar la carpeta que daba los problemas.


Referencias
.
https://stackoverflow.com/questions/11178536/create-file-encountered-operating-system-error-5failed-to-retrieve-text-for-thi

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2c5188ac-50d7-42cf-b983-44616028cc03/create-file-encountered-operating-system-error-5access-is-denied-on-the-same-server?forum=sqldatabaseengine

Fecha Inicial : 29/05/2018
Fecha Final :  29/05/2018
Hora Inicio: 10:36 am
Hora Final: 8:24 pm 
Horas invertidas(Minutos): 325
Colaboradores: Wilson Lopez


Actividad: Finalizacion del proceso masivo de la simulación , así como agregar algunas cosas que faltaban en el planeamiento del proyecto a nivel de base de datos y de datos en los xmls ,
se crea ademas Stores de vistas de recibos por propiedad 

Pausas:
12:05-13:30 - Almuerzo 
14:20:16:56 Lavar/Clases
17:13-17:30 Tender la Ropa

Cambios aplicados: 
Para el calculo de intereses moratorios se toma la decisión de realizar un proceso masivo que revisa todos los días si una factura ya esta vencida y calcula los intereses moratorias para que de esta forma la simulación de pagos cancele la cantidad adecuada , el requerimiento dice q se calculan durante la consulta pero veo un par de problemas de complejidad a la hora de los pagos y de las consultas del tipo top deudores. 

Código: 

CREATE PROCEDURE [dbo].[CalculoInteresMoratorio] @Fecha Date

AS

BEGIN

    DECLARE @TasaInteres float

    DECLARE @DiasRetraso int

    DECLARE @MuniID int

    DECLARE @PropiedadID int

    DECLARE @FechaLimite date

    DECLARE @MontoTotal Money

    DECLARE @MontoInteresMoratorio float

    DECLARE @Recibos table (ID int,FK_Propiedad int,PagoTotalNoIntereses money,FechaLimite datetime)

    BEGIN TRY

        begin

            begin tran

                --DO HERE

                INSERT INTO @Recibos(ID,FK_Propiedad,PagoTotalNoIntereses,FechaLimite) SELECT r.ID,r.FK_Propiedad,r.PagoTotalNoIntereses,r.FechaLimite FROM Recibo r WHERE r.TotalPagado = 0 AND r.FechaLimite < @Fecha

                --SELECT * FROM @Recibos

                DECLARE @RecibosId int = 0

                WHILE(1 = 1)

                    BEGIN

                        SELECT @RecibosId = MIN(ID)FROM @Recibos WHERE ID > @RecibosId

                        IF @RecibosId IS NULL BREAK

                            BEGIN

                            -- Monto a Cobrar x (tasa de interese moratorio/360) x cantidad de días de atraso.

                            --Obtener FK_Propiedad

                            SELECT @PropiedadID = FK_Propiedad FROM @Recibos WHERE ID = @RecibosId

                            --Obtener el IDMunipal

                            SELECT @MuniID = p.FK_Municipalidad FROM Propiedad p WHERE p.ID = @PropiedadID

                            SELECT @TasaInteres = TasaInteresMorosidad FROM Municipalidad WHERE ID = @MuniID

                            --Obtener diferencia de dias

                            SELECT @FechaLimite = r.FechaLimite FROM @Recibos r WHERE r.ID = @RecibosId

                            SELECT @MontoTotal = re.PagoTotalNoIntereses FROM Recibo re WHERE ID = @RecibosID

                            SELECT @DiasRetraso = DATEDIFF(DAY,@FechaLimite,@Fecha)

                            SET @MontoInteresMoratorio = @MontoTotal*(@TasaInteres/360)* @DiasRetraso

                            UPDATE Recibo SET InteresMoratorio = @MontoInteresMoratorio WHERE ID = @RecibosID

                            

                        END

                    END

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            return 0 --failed

    END CATCH

END

GO

Código :

--|||StoreProcedures|||--

--Eliminar los datos de las tablas

CREATE PROCEDURE [dbo].[VaciarTablas]

AS

BEGIN

    EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?';

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

    EXEC sp_MSForEachTable 'DELETE FROM ?';

    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

    EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?';

    EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 0)'

END

GO

--Leer Abonados del XML

CREATE PROCEDURE [dbo].[readXML_Abonados]

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Abonados.xml', SINGLE_BLOB) as Abonados(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_Abonados FROM OPENXML(@hDoc, 'XML/Abonado/dbo.Abonado')

                WITH(

                    Nombre [nvarchar](50),

                    Identificacion int

                    

                )

                begin

                    INSERT INTO Abonado(Nombre,Identificacion) SELECT Nombre,Identificacion FROM #tmp_Abonados          

                end

                DROP TABLE #tmp_Abonados

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            return 0 --failed

    END CATCH

END

GO

--Leer Tipos de servicio del XML

CREATE PROCEDURE [dbo].[readXML_TipoServicio]

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Tiposervicio.xml', SINGLE_BLOB) as TipoServicio(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_TipoServicio FROM OPENXML(@hDoc,'XML/TipoServicio/dbo.TipoServicio')

                WITH(

                    Nombre [nvarchar](50),

Valor float,

Variable int

                    

                )

                begin

                    INSERT INTO TipoServicio(Nombre,Valor,Variante) SELECT Nombre,Valor,Variable FROM #tmp_TipoServicio         

                end

                DROP TABLE #tmp_TipoServicio

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            SELECT ERROR_MESSAGE() --MOSTRAR EL ERROR

            return 0 --failed

    END CATCH

END

GO

--Leer Municipalidades del XML

CREATE PROCEDURE [dbo].[readXML_Municipalidades]

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Municipalidades.xml', SINGLE_BLOB) as Municipalidades(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_Municipalidades FROM OPENXML(@hDoc,'XML/Municipalidades/dbo.Municipalidades')

                WITH(

                    Nombre [nvarchar](50),

DiaEmite int,

DiaLimite int,

TasaInteres float,

ValorMAgua float

                )

                begin

                    INSERT INTO Municipalidad(Nombre,DiaEmite,DiaLimite,TasaInteresMorosidad,ValorMCubicoAgua)

                    SELECT Nombre,DiaEmite,DiaLimite,TasaInteres,ValorMAgua FROM #tmp_Municipalidades

                end

                DROP TABLE #tmp_Municipalidades

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            SELECT ERROR_MESSAGE() --MOSTRAR EL ERROR

            return 0 --failed

    END CATCH

END

GO

--Leer Propiedades del XML

CREATE PROCEDURE [dbo].[readXML_Propiedades]

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Propiedades.xml', SINGLE_BLOB) as Propiedades(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_Propiedades FROM OPENXML(@hDoc,'XML/Propiedades/dbo.Propiedades')

                WITH(

                    NumeroFinca int,

IdMunicipalidad int,

IdAbonado int,

Descripcion nvarchar(100),

Valor money,

CodigoPostal int

                )

                begin

                    INSERT INTO Propiedad(NumeroFinca,FK_Municipalidad,FK_Abonado,Descripcion,Valor,CodPostal)

                    SELECT NumeroFinca,IdMunicipalidad,IdAbonado,Descripcion,Valor,CodigoPostal FROM #tmp_Propiedades

                end

                DROP TABLE #tmp_Propiedades

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            SELECT ERROR_MESSAGE() --MOSTRAR EL ERROR

            return 0 --failed

    END CATCH

END

GO

--Leer Servicios por propiedad del XML

CREATE PROCEDURE [dbo].[readXML_ServiciosXpropiedad]

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Servicioxpropiedad.xml', SINGLE_BLOB) as ServiciosXpropiedad(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_ServiciosXpropiedad FROM OPENXML(@hDoc,'XML/ServicioXPropiedad/dbo.ServicioXPropiedad')

                WITH(

                    NumeroFinca int,

IDServicio int,

FechaContratacion Date

                )

                begin

                    

                    INSERT INTO ServicioXPropiedad (FK_Propiedad,FK_Servicio,FechaContratacionServicio)

                    SELECT NumeroFinca,IDServicio,FechaContratacion FROM #tmp_ServiciosXpropiedad

                end

                DROP TABLE #tmp_ServiciosXpropiedad

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            SELECT ERROR_MESSAGE() --MOSTRAR EL ERROR

            return 0 --failed

    END CATCH

END

GO

---Lectura de consumo de agua

CREATE PROCEDURE readXML_Lectura @Fecha date

AS

BEGIN

    DECLARE @xml xml

    DECLARE @hdoc int

    DECLARE @ID int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\ConsumoAgua.xml', SINGLE_BLOB) as ConsumoAgua(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_ConsumoAgua FROM OPENXML(@hDoc, 'XML/ConsumoAgua/dbo.ConsumoAgua')

                WITH(

                    NumeroFinca int,

                    FechaLectura date,

                    QM3 int

                    

                )WHERE FechaLectura = @Fecha

                begin

                    INSERT INTO ConsumoAgua(FK_Propiedad,FechaLectura,Q) SELECT dbo.Propiedad.ID,#tmp_ConsumoAgua.FechaLectura,#tmp_ConsumoAgua.QM3

                    FROM #tmp_ConsumoAgua INNER JOIN

                                 dbo.Propiedad ON #tmp_ConsumoAgua.NumeroFinca = dbo.Propiedad.NumeroFinca                    

                end

                DROP TABLE #tmp_ConsumoAgua

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            return 0 --failed

    END CATCH

END

GO

------Generar recibos

CREATE PROCEDURE [dbo].[generar_recibos] @IDMuni int , @Fecha date

AS

BEGIN

    DECLARE @Propiedades table (ID int,NumeroFinca int)

    DECLARE @Servicios table (ID int, FKServicio int, Nombre VARCHAR(30),valor FLOAT)

    DECLARE @DAY int

    DECLARE @MONTH int

    DECLARE @YEAR int

   DECLARE @FechaLimite date

    DECLARE @MontoTotal Money = 0

    DECLARE @ConsumoDeAgua float

    DECLARE @ValorAgua float

    DECLARE @FacturaID int

    BEGIN TRY

        begin tran

        --Calcular la fecha limite de pago

        SELECT @DAY = m.DiaLimite FROM Municipalidad m WHERE m.ID = @IDMuni 

        SELECT @YEAR = DATEPART ( YEAR , @Fecha)

        SELECT @MONTH = DATEPART ( MONTH , @Fecha)

        SELECT @FechaLimite = dateadd(mm, (@YEAR - 1900) * 12 + @MONTH - 1 , @DAY - 1)

    INSERT INTO @Propiedades(ID,NumeroFinca) SELECT p.ID,p.NumeroFinca FROM Propiedad p WHERE p.FK_Municipalidad = @IDMuni

    DECLARE @PropiedadId int = 0

    WHILE(1 = 1)

        BEGIN

            SELECT @PropiedadId = MIN(ID)FROM @Propiedades WHERE ID > @PropiedadId

            IF @PropiedadId IS NULL BREAK

                BEGIN   

                 --Calcular monto propiedades

                    --Llenar una tabla con lo servicios por cada propiedad.

                    INSERT INTO @Servicios(ID,FKServicio, Nombre ,valor) SELECT dbo.TipoServicio.ID,dbo.ServicioXPropiedad.FK_Servicio, dbo.TipoServicio.Nombre, dbo.TipoServicio.Valor

                    FROM dbo.ServicioXPropiedad INNER JOIN dbo.TipoServicio ON dbo.ServicioXPropiedad.FK_Servicio = dbo.TipoServicio.ID WHERE FK_Propiedad = @PropiedadId

                    --CREAR EL NUEVO RECIBO

                    INSERT INTO Recibo (FK_Propiedad,FechaEmision,PagoTotalNoIntereses,InteresMoratorio,TotalPagado,FechaLimite,FechaPagado)

                    VALUES ( @PropiedadId,@Fecha,0,0,0,@FechaLimite,NULL)

                    --Asigno el ID de factura

                    SELECT @FacturaID = MAX(ID) FROM Recibo

                    

                    --Recorremos cada uno de los servicios de la propiedad

                    DECLARE @ServiciosId int = 0

                        WHILE(1 = 1)

                        BEGIN

                            SELECT @ServiciosId = MIN(ID)FROM @Servicios WHERE ID > @ServiciosId

                            IF @ServiciosId IS NULL BREAK

                                BEGIN

                                    DECLARE @Monto float = 0

                                    DECLARE @IDServicio int;

                                    SELECT @IDServicio = ID FROM @Servicios WHERE ID = @ServiciosId

                                    IF(@IDServicio=1)

                                    BEGIN

                                         --Sumar el valor del agua multiplicado por el precio de la ultima lectura

                                         SELECT @ValorAgua = ts.Valor FROM @Servicios ts WHERE ts.ID = @IDServicio

                                         SELECT TOP 1 @ConsumoDeAgua = Q FROM ConsumoAgua ca WHERE ca.FK_Propiedad = @PropiedadId ORDER BY ca.FechaLectura DESC

                                         SET @MontoTotal = @MontoTotal+CAST(@ConsumoDeAgua*@ValorAgua AS MONEY)

                                         --INSERTAR UNA LINEA

                                         INSERT INTO Linea (FK_Propiedad,FK_Recibo,FK_TipoServicio,Monto,NumeroLinea) VALUES (@PropiedadId,@FacturaID,@ServiciosId,CAST(@ConsumoDeAgua*@ValorAgua AS MONEY),@IDServicio);

                                    END

                                    ELSE

                                    BEGIN

                                        --Sumar el valor de todos los servicios de una propiedad

                                        SELECT @Monto = s.valor FROM @Servicios s WHERE s.ID = @IDServicio;

                                        SET @MontoTotal = @MontoTotal+CAST(@Monto AS MONEY)

                                        --INSERTAR UNA LINEA

                                        INSERT INTO Linea (FK_Propiedad,FK_Recibo,FK_TipoServicio,Monto,NumeroLinea) VALUES (@PropiedadId,@FacturaID,@ServiciosId,CAST(@Monto AS MONEY),@IDServicio);

                                    END

                                END

                         END

                    

                    UPDATE Recibo SET PagoTotalNoIntereses = @MontoTotal WHERE ID = @FacturaID

                END

                DELETE FROM @Servicios

                SET @MontoTotal = 0

                

        END

    commit tran

            return 1

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            SELECT ERROR_MESSAGE() --MOSTRAR EL ERROR

            return 0 --failed

    END CATCH

END

GO

--Calculo de interes Moratorio

CREATE PROCEDURE [dbo].[CalculoInteresMoratorio] @Fecha Date

AS

BEGIN

    DECLARE @TasaInteres float

    DECLARE @DiasRetraso int

    DECLARE @MuniID int

    DECLARE @PropiedadID int

    DECLARE @FechaLimite date

    DECLARE @MontoTotal Money

    DECLARE @MontoInteresMoratorio float

    DECLARE @Recibos table (ID int,FK_Propiedad int,PagoTotalNoIntereses money,FechaLimite datetime)

    BEGIN TRY

        begin

            begin tran

                --DO HERE

                INSERT INTO @Recibos(ID,FK_Propiedad,PagoTotalNoIntereses,FechaLimite) SELECT r.ID,r.FK_Propiedad,r.PagoTotalNoIntereses,r.FechaLimite FROM Recibo r WHERE r.TotalPagado = 0 AND r.FechaLimite < @Fecha

                --SELECT * FROM @Recibos

                DECLARE @RecibosId int = 0

                WHILE(1 = 1)

                    BEGIN

                        SELECT @RecibosId = MIN(ID)FROM @Recibos WHERE ID > @RecibosId

                        IF @RecibosId IS NULL BREAK

                            BEGIN

                            -- Monto a Cobrar x (tasa de interese moratorio/360) x cantidad de días de atraso.

                            --Obtener FK_Propiedad

                            SELECT @PropiedadID = FK_Propiedad FROM @Recibos WHERE ID = @RecibosId

                            --Obtener el IDMunipal

                            SELECT @MuniID = p.FK_Municipalidad FROM Propiedad p WHERE p.ID = @PropiedadID

                            SELECT @TasaInteres = TasaInteresMorosidad FROM Municipalidad WHERE ID = @MuniID

                            --Obtener diferencia de dias

                            SELECT @FechaLimite = r.FechaLimite FROM @Recibos r WHERE r.ID = @RecibosId

                            SELECT @MontoTotal = re.PagoTotalNoIntereses FROM Recibo re WHERE ID = @RecibosID

                            SELECT @DiasRetraso = DATEDIFF(DAY,@FechaLimite,@Fecha)

                            SET @MontoInteresMoratorio = @MontoTotal*(@TasaInteres/360)* @DiasRetraso

                            UPDATE Recibo SET InteresMoratorio = @MontoInteresMoratorio WHERE ID = @RecibosID

                            

                        END

                    END

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            return 0 --failed

    END CATCH

END

GO

--Lectura de pagos

CREATE PROCEDURE [dbo].[readXML_Pagos] @Fecha date

AS

BEGIN

    DECLARE @Pagos table (ID int identity(1,1),NumeroFinca int,FechaPago date)

    DECLARE @xml xml

    DECLARE @hdoc int

    DECLARE @ID int

    DECLARE @MontoInteres Money

    DECLARE @MontoNoInteres Money

    DECLARE @MontoTotal Money

    DECLARE @PropiedadID int

    DECLARE @ReciboID int

    BEGIN TRY

        SELECT @xml = roow FROM OPENROWSET (BULK 'C:\data\SQL-Data-P2\Pagos.xml',

SINGLE_BLOB) as Pagos(roow)

        if (@xml IS NOT NULL)

        begin

            begin tran

                EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

                SELECT * INTO #tmp_Pagos FROM OPENXML(@hDoc, 'XML/Pagos/pago')

                WITH(

                    NumeroFinca int,

                    FechaPago date

                    

                )WHERE FechaPago = @Fecha

                begin

                    DECLARE @PractitionerId int = 0

                    WHILE(1 = 1)

                    BEGIN

                        SELECT @PractitionerId = MIN(NumeroFinca)FROM #tmp_Pagos WHERE NumeroFinca > @PractitionerId

                        IF @PractitionerId IS NULL BREAK

                            BEGIN

                                SELECT @PropiedadID = f.ID FROM Propiedad f WHERE f.NumeroFinca = @PractitionerId

                                --SELECT @PractitionerId,@PropiedadID

                                SELECT TOP 1 @ReciboID = re.ID FROM Recibo re WHERE re.FK_Propiedad = @PropiedadID ORDER BY re.FechaLimite DESC

                                SELECT @MontoNoInteres = re1.PagoTotalNoIntereses FROM Recibo re1 WHERE re1.ID = @ReciboID

                                SELECT @MontoInteres = re2.InteresMoratorio FROM Recibo re2 WHERE re2.ID = @ReciboID

                                --SELECT @MontoInteres,@MontoNoInteres,@MontoInteres+@MontoNoInteres

                                

                                UPDATE Recibo SET TotalPagado = (@MontoNoInteres+@MontoInteres),FechaPagado = @Fecha WHERE Recibo.ID = @ReciboID

                            END

                    END                       

                end

                DROP TABLE #tmp_Pagos

        end

        commit tran

        return 1 --successful

    END TRY

    BEGIN CATCH

            IF @@TRANCOUNT > 0 ROLLBACK;

            return 0 --failed

    END CATCH

END

Problemas encontrados: 
-Para la generación de recibos , asume que la lectura de consumo de agua se realiza de manera mensual por lo que el calculo del monto a pagar es producto de la cantidad de consumo de agua de la ultima lectura presente , sin embargo los xmls no tienen lecturas de agua para todo el año así que en un punto la lectura de agua es la misma así que el valor del recibo es igual al del mes anterior  , se considera esto como un error mas de la generación de datos simulados ya que los compañeros no toman no generan datos de manera homogénea , así que se aplica navaja de okcan para este caso.

En el requerimiento se solicita un numero de identificación para el acceso sin embargo este no es proveído por los xmls así q se toma la decisión de agregarlo a los xmls de Abonados para que de esta forma se puedan identicar los usuarios a nivel de capa lógica 

XML: 

<XML>

    <Abonado>

        <dbo.Abonado Nombre="Latonya Joyce" Identificacion = "1000" />

        <dbo.Abonado Nombre="Carlton Cardenas" Identificacion = "1001" />

        <dbo.Abonado Nombre="Charity Dickerson" Identificacion = "1002" />

        <dbo.Abonado Nombre="Jacquelyn Banks" Identificacion = "1003" />

        <dbo.Abonado Nombre="Mindy Garcia" Identificacion = "1004" />

        <dbo.Abonado Nombre="Sonia Chang" Identificacion = "1005" />

        <dbo.Abonado Nombre="Damon Bates" Identificacion = "1006" />

        <dbo.Abonado Nombre="Evan Blair" Identificacion = "1007" />

        <dbo.Abonado Nombre="Brenda Zuniga" Identificacion = "1008" />

        <dbo.Abonado Nombre="Benny Macias" Identificacion = "1009" />

        <dbo.Abonado Nombre="Leanne Patton" Identificacion = "1010" />

        <dbo.Abonado Nombre="Leo Brown" Identificacion = "1011" />

        <dbo.Abonado Nombre="Tabatha Rodriguez" Identificacion = "1012" />

        <dbo.Abonado Nombre="Kelly Wood" Identificacion = "1013" />

        <dbo.Abonado Nombre="Ericka Cruz" Identificacion = "1014" />

        <dbo.Abonado Nombre="Jayson Stevens" Identificacion = "1015" />

        <dbo.Abonado Nombre="Marco Lane" Identificacion = "1016" />

        <dbo.Abonado Nombre="Vickie Curtis" Identificacion = "1017" />

        <dbo.Abonado Nombre="Guillermo Wong" Identificacion = "1018" />

        <dbo.Abonado Nombre="Alice Andrews" Identificacion = "1019" />

        <dbo.Abonado Nombre="Roberto Davila" Identificacion = "1020" />

        <dbo.Abonado Nombre="Lillian Kirk" Identificacion = "1021" />

        <dbo.Abonado Nombre="Joanne Henderson" Identificacion = "1022" />

        <dbo.Abonado Nombre="Debbie Brooks" Identificacion = "1023" />

        <dbo.Abonado Nombre="Warren Mayer" Identificacion = "1024" />

        <dbo.Abonado Nombre="Shawn Arnold" Identificacion = "1025" />

        <dbo.Abonado Nombre="Tom Mc Intyre" Identificacion = "1026" />

        <dbo.Abonado Nombre="Jose Horne" Identificacion = "1027" />

        <dbo.Abonado Nombre="Wade Mullins" Identificacion = "1028" />

        <dbo.Abonado Nombre="Randy Price" Identificacion = "1029" />

        <dbo.Abonado Nombre="Larry Douglas" Identificacion = "1030" />

        <dbo.Abonado Nombre="Grant Ritter" Identificacion = "1031" />

        <dbo.Abonado Nombre="Wendi Glenn" Identificacion = "1032" />

        <dbo.Abonado Nombre="Tamiko Guerra" Identificacion = "1033" />

        <dbo.Abonado Nombre="Darla Valentine" Identificacion = "1034" />

        <dbo.Abonado Nombre="Vickie Shannon" Identificacion = "1035" />

        <dbo.Abonado Nombre="Nora Dudley" Identificacion = "1036" />

        <dbo.Abonado Nombre="Josephine Dalton" Identificacion = "1037" />

        <dbo.Abonado Nombre="Janet Jensen" Identificacion = "1038" />

        <dbo.Abonado Nombre="Kurt Forbes" Identificacion = "1039" />

    </Abonado>

</XML>