Okay - I would like some feedback on creating SQL functions.
I think I have only ever created one SQL function in Great Plains before. If I recall it was a simple function to return the status of a Manufacturing Order without have to create an 8-line case statement. I don't really understand their benefits and use.
Now I have a requirement to determine simply if a Sales Invoice is still open(1) paid off(2), partially paid off(3), written off(4) or partially written off(5) or adjusted (6) or partially adjusted (7)
Background information - Our collection process includes documenting the contact made on past due invoices. We gather data that says INV12345 was past due on 1/1/2022 - we sent letters (automated)/emails and now it's 4/4/2022 and we want to know if INV12345 was fully paid off - is it still completely unpaid, did we adjust the document, etc.
I think a SQL Function would do the trick. I know a stored procedure could be written pretty easily - but isn't it a little tricky calling a stored procedure from a SQL view which is used for a more comprehensive dataset? Basically, I need to 'add' the invoice status to an existing dataset (currently SQL View)
Any thoughts or if anybody has some previously done something similar please share.
Thanks in advance.
Bron Tamulis