Issue:
There is an advanced deposit showing on the Open Credit report, but the deposit was reversed or has already been used.
Resolution:
The report looks at the information for the advanced deposit in the AR_Advanced_Deposit and the AR_Advanced_Deposit_Detail tables. If there is mismatched information or the detailed record is missing, this can happen.
If the detailed record is missing, it can be inserted with information from the deposit.
The scripts below can be used to find and insert a record into the AR_Advanced_Deposit_Detail table.
--- Find Customer_id and Advanced deposit records ---
select * from ar_customer where Customer_Number='02ESKU07' select * from AR_Advance_Deposit where Customer_Id= 4819 select * from AR_Advance_Deposit where Advance_Deposit_Id in(4071,4072) select * from AR_Advance_Deposit_Detail where Advance_Deposit_Id in(4071,4072)
--- Update the values for the Advanced Deposit Detail records ---
INSERT INTO AR_Advance_Deposit_Detail
(Advance_Deposit_Id,Date,Type_UIM,Unapplied_Cash_Id,
Invoice_Id,Misc_Inc_Account_Id,Amount,Register_Id,
Branch_Id,PrePay_Register_Id,Category_Id,Reversed,
UserCode)
VALUES
(4071,'2023-11-09 00:00:00.000','M',1,1,1,70000.00,1,14,1,1,'Y','Admin') ,(4072,'2023-11-09 00:00:00.000','M',1,1,1,-70000.00,1,14,1,1,'Y','Admin')
The problem could also be from incorrect information in the detailed record. If the dates or amounts are not correct, the record may still show on the report.
If the used amounts do not match or the date of the detail may be incorrect.
--- Check the values for the Advanced Deposit and Detail records ---
select * from AR_Advance_Deposit where Advance_Deposit_Id in(4071,4072) select * from AR_Advance_Deposit_Detail where Advance_Deposit_Id in(4071,4072)
--- Update the values for the Advanced Deposit Detail records ---
update AR_Advance_Deposit_Detail set amount = -70000.00 where Advance_Deposit_Detail_Id =3699 update AR_Advance_Deposit_Detail set date ='2023-11-09 00:00:00.000' where Advance_Deposit_Detail_Id in(3698,3699)
