• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Table Trigger Alternatives?

BBritain

VIP Member
Hi Gurus,

I have been working on a table trigger on the F0911 (After Record is Inserted) which will take an F0911 record, determine if it is eligible for splitting, and if so, create a reversing record and multiple split records.

Unfortunately, I have only gotten to creating messages in a custom table before I started getting errors related to "ASYNCHRONOUS BUSINESS FUNCTION ERROR" and "TRANSACTION ERROR" (see below for complete errors). So this was narrowed down to an Table I/O Insert to a custom table.

I was able to avoid the errors by going in to the application, finding the F0911 End Document function and unchecking the "Include in Transaction" checkbox (in multiple locations). Philisophically I feel this is not the way to go, since there could be multiple more applications that would need this modification, so I am looking for alternatives.

Can anyone recommend (with experience) using subsystem jobs, or database triggers, or any other method/combination that might accomplish this task? I would even still consider table triggers if I could get around the transaction processing issues without modifying the Applications/MBFs.

I worry that since I am using MBFs to create the new records that the recursiveness will also make the table trigger alternative a poor choice.

Thanks for any and all suggestions,
Ben Again,

Error 1: ASYNCHRONOUS BUSINESS FUNCTION ERROR - Asynchronous business function did not finish successfully for form P0911_W0911A_ZJDE0001/nThere was a problem with the server while running the business function F0911FSEndDoc. The server may still be available, but because of state information, the entire unit of work must be resubmitted. Please exit the application and restart it.

Error 2: TRANSACTION ERROR - Transaction cannot be committed. It was canceled.
 

johndanter

Legendary Poster
Hi,

Do you need to have something so immediate? Why can't you have a scheduled job selecting on what you need and sweeping the F0911 and then making the changes?
The GL is only of major importance prior to month ends...? Debatable concept, but you know what I mean

I'm guessing you don't want to do this for EVERY record? The trigger will fire off for every record, so maybe use a UBE to do this.

I have something which reverses consigned stock F0911 journal entries. I'd be happy to bore you to death about how I've done that and send on specs etc.

But for now, it reads over the cardex, identifies the GL class codes and suppliers and reverses our receipt entries. It also does some other fancy stuff once a week on consumption of said stock that isn't ours.

So maybe, instead of asking about the trigger, explain what you are trying to achieve, as there maybe many options open to you that don't involve a trigger:)
 

BBritain

VIP Member
Thanks John for your response. Because it was such a long post, the first time I typed it up, I had just what you asked for and the system timed out/logged me out and didn't save any of the original post. I guess in the re-writing, I left it out.

So the users want to be able to enter journal entries (mainly from P0911), hit the OK button and then hit Find and be able to see the reversal record and all split records along with the original record(s)(for any eligible original records). Any records not eligible will be marked so that they will not be re-considered (as will the eligible records).

Ben Again,
 
Last edited:

peterbruce

Legendary Poster
Ben,

Here is a suggestion:

Create a business function for the functionality with journal ID columns as parameters and modify the applications where it is needed.

The big draw back with this is that it requires standard JDE applications to be modified, which may or may not be within the organisations JDE development policy.
 
Last edited:

johndanter

Legendary Poster
Hi Ben,

I'd just query the actual necessity of why they want to be able to see them straight away as it will cause you headaches.
Seeing something in a grid is totally different to something actually balancing at month end. Which in my eyes is far more important that being able to see grid records.

Sometimes, for the greater good and common sense, we have to tell these pesky users NO :)

That said, you could modify F0911 Edit Line etc?

My solution also uses the MSTR BSFNs by the way....just every 15 minutes (for receipts and WO completions) or another job that runs prior to month end and invoicing for the consumption.
 
Last edited:

BBritain

VIP Member
Thanks Peter, Yes, the modifying JDE applications is a "last resort" situation. I am finishing up a proof-of-concept that involves a "Master UBE" that runs continuously and checks every few seconds for candidate F0911 records and if records found the "Master UBE" will call an already-existing "F0911 record splitter UBE". This gets me 'near real time' and doesn't force me to interfere with the transaction processing or modifying the standard application(s).

Ben again,
 

BBritain

VIP Member
Hi John,

Ha ha! You young whippersnappers! You're such a hoot! Telling a user no. Ha ha, you must think I'm a Business Analyst or something. No, I'm just a developer. Sure, I won't create an application that allows a user to fudge F0911 records, but if a user wants a process to do X, then I try to do X, or xx or x.

BTW, I think I'm settling on something very similar to what I imagined your solution was, so you could say that you originated the idea.

Ben Again,
 

johndanter

Legendary Poster
You're welcome Ben :)

As for the grid issue, create dummy buffer grids? You could save them as they are created I guess, but its the back end stuff that will cause you issues.

Happy to help
 
Top