Real Estate Utilities

One SQL repair for real estate contracts: recalculating the system paid, collect requested and collected by financial papers values when installment payment entries have lost their reference.

Fix System Paid, Collect Requested and Collected by Financial Papers in Contracts

First run the following statement and then put the output in recommit file:

select distinct paymentDocEntityType,paymentDocId from InstallmentPaymentEntry where refid is null

Now Delete all bad entries:

delete  from InstallmentPaymentEntry where refid is null

Run The following Query

with paidValues as (
select sum(case when effectType = 'SystemPaid' then e.paidValue else 0 end) SystemPaid
,sum(case when effectType = 'CollectedByFP' then e.paidValue else 0 end) CollectedByFP
,sum(case when effectType = 'RequestedCollect' then e.paidValue else 0 end) RequestedCollect
,e.installmentCode,e.installmentDocId from InstallmentPaymentEntry e
group by e.installmentCode,e.installmentDocId
)
update l set systemPaidValue = coalesce(pv.SystemPaid,0),requestedCollectValue = coalesce(pv.RequestedCollect,0),collectedByFPValue=coalesce(pv.CollectedByFP,0)
,remainingValue = l.netValue -l.systemPaidValue,remainingCollectVal = l.netValue-l.requestedCollectValue,remainingFPVal = l.netValue - l.collectedByFPValue
from RESalesLine l left join paidValues pv on pv.installmentCode = l.installmentCode and pv.installmentDocId = l.rESalesDoc_id

--------------------------------------------------------------------
go

with paidValues as (
select sum(case when effectType = 'SystemPaid' then e.paidValue else 0 end) SystemPaid
,sum(case when effectType = 'CollectedByFP' then e.paidValue else 0 end) CollectedByFP
,sum(case when effectType = 'RequestedCollect' then e.paidValue else 0 end) RequestedCollect
,e.installmentCode,e.installmentDocId from InstallmentPaymentEntry e
group by e.installmentCode,e.installmentDocId
)
update l set systemPaidValue = coalesce(pv.SystemPaid,0),requestedCollectValue = coalesce(pv.RequestedCollect,0),collectedByFPValue=coalesce(pv.CollectedByFP,0) 
,remainingValue = l.netValue -l.systemPaidValue,remainingCollectVal = l.netValue-l.requestedCollectValue,remainingFPVal = l.netValue - l.collectedByFPValue
from REOpeningSalesLines l left join paidValues pv on pv.installmentCode = l.installmentCode and pv.installmentDocId = l.rEOpeningSales_id
	
go
--------------------------------------------------------------------


with paidValues as (
select sum(case when effectType = 'SystemPaid' then e.paidValue else 0 end) SystemPaid
,sum(case when effectType = 'CollectedByFP' then e.paidValue else 0 end) CollectedByFP
,sum(case when effectType = 'RequestedCollect' then e.paidValue else 0 end) RequestedCollect
,e.installmentCode,e.installmentDocId from InstallmentPaymentEntry e
group by e.installmentCode,e.installmentDocId
)
update l set systemPaidValue = coalesce(pv.SystemPaid,0),requestedCollectValue = coalesce(pv.RequestedCollect,0),collectedByFPValue=coalesce(pv.CollectedByFP,0) 
,remainingValue = l.netValue -l.systemPaidValue,remainingCollectVal = l.netValue-l.requestedCollectValue,remainingFPVal = l.netValue - l.collectedByFPValue
from RERentInstalmentLine l left join paidValues pv on pv.installmentCode = l.installmentCode and pv.installmentDocId = l.rERentContract_id
go
--------------------------------------------------------------------

with paidValues as (
select sum(case when effectType = 'SystemPaid' then e.paidValue else 0 end) SystemPaid
,sum(case when effectType = 'CollectedByFP' then e.paidValue else 0 end) CollectedByFP
,sum(case when effectType = 'RequestedCollect' then e.paidValue else 0 end) RequestedCollect
,e.installmentCode,e.installmentDocId from InstallmentPaymentEntry e
group by e.installmentCode,e.installmentDocId
)
update l set systemPaidValue = coalesce(pv.SystemPaid,0),requestedCollectValue = coalesce(pv.RequestedCollect,0),collectedByFPValue=coalesce(pv.CollectedByFP,0) 
,remainingValue = l.netValue -l.systemPaidValue,remainingCollectVal = l.netValue-l.requestedCollectValue,remainingFPVal = l.netValue - l.collectedByFPValue
from REOpeningRentContractLine l left join paidValues pv on pv.installmentCode = l.installmentCode and pv.installmentDocId = l.rEOpeningRentContract_id
--------------------------------------------------------------------
go
--------------------------------------------------------------------

with paidValues as (
select sum(case when effectType = 'SystemPaid' then e.paidValue else 0 end) SystemPaid
,sum(case when effectType = 'CollectedByFP' then e.paidValue else 0 end) CollectedByFP
,sum(case when effectType = 'RequestedCollect' then e.paidValue else 0 end) RequestedCollect
,e.installmentCode,e.installmentDocId from InstallmentPaymentEntry e
group by e.installmentCode,e.installmentDocId
)
update l set systemPaid = coalesce(pv.SystemPaid,0),
remaining = l.paymentValue - l.systemPaid, paid = case when l.remaining <=0 then 1 else 0 end
from SalesInvScheduledPayLine l left join paidValues pv on pv.installmentCode = l.installmentCode and pv.installmentDocId = l.salesInvoice_id

--------------------------------------------------------------------
go

• Now recommit the files