Check number assigned after they are printed

[former_member629150]

Hello all,

I have a problem involving check payments and was wondering if it requires customization or if there are existing structures within SAP capturing the information that I need. 

The Problem: Checks that are printed a day or more after the outgoing payment is created have a check number assigned at that time.  Nothing (as far as I can tell) changes in the database to tell me that this check now has a check number (no field in OCHO).  I have a nightly report that grabs all the checks written the previous day that have a check number.  The problem arises when the check is printed after this report.  That check will still have a create date of the day it was entered into SAP, but will not have a check number until it is printed and so it will never be picked up by the report.

Does anyone know of any other information that can tell me that there are checks that have been updated?

[Former Member · Accepted solution]

Try:

SELECT DISTINCT

a.checknum AS 'Check No.',

a.CHECKSUM AS 'Amount',

CASE WHEN d.cardname = 'Special Purchases' THEN e.NumAtCard ELSE d.cardname END AS 'Payee',

a.Checkdate  AS 'Date Issued',

a.Canceled AS 'Canceled'

FROM OCHO a

LEFT OUTER JOIN OACT b ON a.VendorCode = b.AcctCode

INNER JOIN ODSC c ON a.BankNum = c.BankCode

INNER JOIN OCRD d ON d.CardCode = a.VendorCode

LEFT OUTER JOIN OPCH e ON  a.TransRef = e.ReceiptNum

WHERE  DateDiff(d,a.CreateDate,GETDATE())<=1

AND c.BankCode = 'Bank-xyz'

AND a.CHECKSUM >= 0.0

AND a.checknum <> 0

[former_member629150 · Accepted solution]

Gordon,

I'm not sure I explained the issue clearly.  The issue is not that the report is not working as is, it is just in the scenario when an outgoing payment is created one day and the check printed a day or more later.  The check will have a create date that will not change, but it will not have a check number, so it will never be caught by this report if it is not printed on the same day that it is created as an outgoing payment.  My question is: is there any way to capture checks that have been assigned a check number on a day that is not the day that they were created? I have been unable to find any other "modified" date field or anything like that to help me out.  I will customize this if necessary, but i wanted to see if there are existing data channels to use first.  Do you know of any?

[Former Member · Accepted solution]

Hi Chad,

Are you using automatic check number in outgoing payment? if that's the case, then you will not be able to capture any number because system will automatically assign the number once you have printed the check.

But if you look at the other side, it will be good for you that you will know which document/check has not been printed out.

Best Regards,

Lailus Shobah

[former_member629150 · Accepted solution]

Hi Lailus,

I have not yet implemented a solution, but I think you've pointed me in the right direction.   I'll post back after I've had a chance to try it out.  Thanks!

[former_member629150 · Accepted solution]

Lailus,

I was able to make a custom solution to this problem by creating a temporary holding table for checks that have not yet been assigned a check number yet.  I store the CheckKey number and then I updated my queries to look for any matching checkKey's that now have a check number.  To keep the temporary holding table current, I created a stored procedure to look for new checks without a check number and delete those that are either canceled or that now have a check number.  I run this stored procedure through the job agent in ms SQL server management studio.  It all seems to work, I was just hoping to avoid all that customization and query off of existing SAP table structures to detect these scenarios.  Thanks for your help!

[Former Member]

Hi,

Could you post your SQL query for the report?

Thanks,

Gordon

[former_member629150]

Here is the SQL:

SELECT DISTINCT

a.checknum AS 'Check No.',

a.CHECKSUM AS 'Amount',

CASE WHEN d.cardname = 'Special Purchases' THEN e.NumAtCard ELSE d.cardname END AS 'Payee', 

a.Checkdate  AS 'Date Issued',

a.Canceled AS 'Canceled'

FROM OCHO a

LEFT OUTER JOIN OACT b ON a.VendorCode = b.AcctCode

INNER JOIN ODSC c ON a.BankNum = c.BankCode

INNER JOIN OCRD d ON d.CardCode = a.VendorCode

LEFT OUTER JOIN OPCH e ON  a.TransRef = e.ReceiptNum

WHERE  CAST(a.CreateDate AS DATE)  =  CAST((GETDATE()-1) AS DATE)

AND c.BankCode = 'Bank-xyz'

AND a.CHECKSUM >= 0.0

AND a.checknum <> 0

[jitin_chawla]

Hi,

You can check Note No. :1151535 and confirm the working of the check number and its printing logic.

Kind Regards,

Jitin

SAP Business One Forum Team

[former_member629150]

Hi Jitin,

Thank you for your response, but I'm not sure what you are referring to with "Note No.:1151535"?  I searched SCN for this number and nothing comes up.  Also, I'm unclear on what you mean by "confirm the working of the check number and its printing logic".  Could you please elaborate a bit more?