SBX - Search With Button

SBX - Forum Post Title

Prepayments re: AP Period Sensitive Report

Microsoft Dynamics SL Forum

Lisa asked a question on 5 Apr 2019 3:22 PM
My Badges

Question Status

Verified

Hi,

I have an issue which has been an area of concern for a period of time and I am hoping that someone encountered this issue before or/and could point me in the right direction.

On the Period Sensitive Aged AP report there is a prepayment for a particular vendor that is showing with a positive balance eg. 200.  This balance is not showing on the vendor period trial balance report only on this report. 

Therefore my concerns are, I have never seen a prepayment on a report showing as a positive value before, so anyone knows what can cause this to exist?  Secondly, why is it not showing on the vendor period trial balance report as well?

Any assistance will be greatly appreciated.

Thanks

Reply
Carolyn Froton responded on 22 Apr 2019 11:34 AM
My Badges

Hello Lisa,

What version of SL are you using?  I found an older bug

29157: Period Sensitive Aged AP report is incorrect if a check pays a PP, is voided, an AD is entered to offset the PP and a check pays the AD and PP, and then the PP is applied to other VOs.

1. In demo database, company 0060, open vendor maintenance and enter a new vendor.  

vendor ID = V123

vendor name = V123

terms = 05 (no discount)

2. In quick voucher and prepayment entry (03.020.00), enter a PP (prepayment) for vendor V123 for 10,000.00.  Print and keep a check so the PP is paid, or enter a manual check to pay the PP.

3. In voucher entry, enter a voucher for 9800.00 for vendor V123.  In the pre-pay nbr field, enter the number of the prepayment from step 2 so that the prepayment is applied to the voucher.

4. In void check entry, void the check created in step 2.

5. In voucher and adjustment entry (03.010.00), and a debit adjustment for 10,000 for vendor V123.

6. In manual check entry, enter a manual check for vendor V123.  Amount 9800.00.  In the grid, enter the 10,000 PP that was entered in step 2, the 9800.00 voucher that was entered in step 3, and the 10,000 AD that was entered in step 5.  Release the manual check batch.

7. In voucher entry, enter a batch of two vouchers for vendor V123.  On voucher for 9600.00 and one voucher for 400.00.  Release the batch.

8. In prepayment application and reversal, apply the prepayment to both vouchers from step 7.

9. Print the period sensitive Aged AP report (03.681.00).  Note the prepayment shows with a balance of 19,600.00.   The balance of the prepayment should actually show zero.  The check in step 6 paid the prepayment but didn't apply it to anything.  The two vouchers in step 7 were applied to the prepayment in step 8, so the prepayment balance should be showing 0.00.

Best Regards,

Jana MacDonald

Microsoft Dynamics Support

Reply
Lisa responded on 22 Apr 2019 7:27 PM
My Badges

Jana,

We are using Dynamics SLCU2.  Thanks for the detail information on how to recreate the issue.

However seeing that the document should be zero, how should this issue be corrected?

Regards

Reply
Carolyn Froton responded on 25 Apr 2019 3:26 PM
My Badges
Verified Answer

Lisa,

Whereas Jana has mentioned the Bug 29157, then Lisa replied asking what steps to follow to resolve this issue, I will add a few items that aims to say a possible resolution is to identify and delete invalid records in the AP_PPApplic and AP_PPApplicDet tables of the Application database using SQL Server Management Studio :

1. Bug 29157, the bug Jana provides detailed info about in her posting here, is not corrected yet and it has no workaround developed.
2. Bug 36483 "Error when Deleting Pre-Payment Application Batch and Creating New Pre-Payment Application Batch Using Same Pre-Payment Reference Numbers" is also not corrected but there is a workaround, which I list below.
3. It is not clear to me that the Bug29157 is caused by the system finding conflict between existing records in this AP_PPApplic and AP_PPApplicDet tables when the user gets to the point of trying to apply the PP to a different Voucher, but it would make sense to me and would not surprise me if this was found to be the cause which a developer seeking to fix the Bug would find, while keeping in mind that I am not a developer myself. 
4. I suspect that you may have records stranded in either or both of AP_PPApplic and AP_PPApplicDet which you would want to query for using the Batch Number. Which Batch number is in play for you I cannot say for certain but if there are records in the Tables for any Batches whose Status is Voided then those are candidates to be deleted from these tables for sure.

Workaround to Bug 36483:
Using SQL Studio run a delete statement on the AP_PPApplic table in the Application Database for the deleted batch:

/*
Begin Tran
Select * from AP_PPApplic where BatNbr = 'xxxxxx'
Delete from AP_PPApplic where BatNbr = 'xxxxxx'
Select * from AP_PPApplic where BatNbr = 'xxxxxx'

Select * from AP_PPApplicDet where BatNbr = 'xxxxxx'
Delete from AP_PPApplicDet where BatNbr = 'xxxxxx'
Select * from AP_PPApplicDet where BatNbr = 'xxxxxx'
Rollback
--Commit

*/

Kevin MacDonald

Microsoft Dynamics SL Support

Financials Support Team

Reply
Carolyn Froton responded on 25 Apr 2019 3:26 PM
My Badges
Verified Answer

Lisa,

Whereas Jana has mentioned the Bug 29157, then Lisa replied asking what steps to follow to resolve this issue, I will add a few items that aims to say a possible resolution is to identify and delete invalid records in the AP_PPApplic and AP_PPApplicDet tables of the Application database using SQL Server Management Studio :

1. Bug 29157, the bug Jana provides detailed info about in her posting here, is not corrected yet and it has no workaround developed.
2. Bug 36483 "Error when Deleting Pre-Payment Application Batch and Creating New Pre-Payment Application Batch Using Same Pre-Payment Reference Numbers" is also not corrected but there is a workaround, which I list below.
3. It is not clear to me that the Bug29157 is caused by the system finding conflict between existing records in this AP_PPApplic and AP_PPApplicDet tables when the user gets to the point of trying to apply the PP to a different Voucher, but it would make sense to me and would not surprise me if this was found to be the cause which a developer seeking to fix the Bug would find, while keeping in mind that I am not a developer myself. 
4. I suspect that you may have records stranded in either or both of AP_PPApplic and AP_PPApplicDet which you would want to query for using the Batch Number. Which Batch number is in play for you I cannot say for certain but if there are records in the Tables for any Batches whose Status is Voided then those are candidates to be deleted from these tables for sure.

Workaround to Bug 36483:
Using SQL Studio run a delete statement on the AP_PPApplic table in the Application Database for the deleted batch:

/*
Begin Tran
Select * from AP_PPApplic where BatNbr = 'xxxxxx'
Delete from AP_PPApplic where BatNbr = 'xxxxxx'
Select * from AP_PPApplic where BatNbr = 'xxxxxx'

Select * from AP_PPApplicDet where BatNbr = 'xxxxxx'
Delete from AP_PPApplicDet where BatNbr = 'xxxxxx'
Select * from AP_PPApplicDet where BatNbr = 'xxxxxx'
Rollback
--Commit

*/

Kevin MacDonald

Microsoft Dynamics SL Support

Financials Support Team

Reply

SBX - Two Col Forum

SBX - Migrated JS