Batch Approval and Post Audit

peterbruce

peterbruce

Legendary Poster
JDEList,

Has anyone created an audit of batch approval and posing? I need to create a table that will keep at a minimum who approved and who posted each batch, preferable including the time and date of the action. My task is complicated by the fact that the audit columns in the F0011 are never updated. I need to get this up and running quickly, so if I could lever off anyone else who has done this, it would be really appreciated. Any and all comments and suggestions are welcome.

Our Current (as of 7th April) Config:

Oracle JD Edwards EnterpriseOne,
E8.11sp1 8.97.2.1, ES Sun, Oracle DB 10.2, Websphere 6 Win2K3.
Forms: Create!form Server 3/Server 6
 
Peter,

I have had such requirements in past and unfortunately turning JDE or another kinds of audit on this table was not an option as it has performance consideration too.
Usually, Audit should be turned on the Master table and not the transaction tables. Unfortunately even though this particular table is a master table but it works like a transaction tables. This gets update so often, it will fire the trigger for audit so many time, users might see the performance degradation.
Our problem was not who approved it and when, our problem was due to a system issue. Basically , if a user open the batch, they would become the owner of the batch. This created a problem. Hence, we went back to Oracle and they provided a paper fix, which fixed our issue.
In your case, I would suggest making code change to the approval application and store the information F55 table. This will store informtion only regarding approval of batch, not just about any change to F0011. I know you will be changing standard application, but you might want to evaluate if you want to setup AUDIT on F0011 and impact performance or Change the standard application.
 
Nick,

Thanks for your insights. I will keep the possibilty of a performance issue in mind. I agree with your inference that standard objects should not be modified - except when there is no other option.

As yet, I do not discount or write off anything.

I have considered both JDE table triggers and native database table triggers. Both are still "on the table." However using native database table triggers may not work unless the audit fields are updated (they aren't) or different database users are used for each JDE user (we don't do this).

Modifying standard objects is a possibility. Though identifying all the objects to modify might be challenging.

There is only one (standard) object which does the posting (R09801) - if there are others PLEASE LET ME KNOW. However, according to my research, the R09801 does not directly update the F0011, it uses a business function. I didn't take note of which one so I'll have to check again, but it might not matter as all that would be needed is to update the custom audit table at the same time as the F0011 is is set to posted. This may be able to be done in the R09801, it depends what the business function does. It may not update the F0011 to posted every time it is run.

I do not know if any other objects do the batch approval apart from P0011 - if there are others PLEASE LET ME KNOW. Once again, according to my research, it does not update the F0011 directly, it uses a business function. Again I didn't take note of which one so I'll have to check again.

I have attached an excel file containing a list of what I have discovered.

PS: Correction - R09801 does update the F0011 directly and that, I believe, is where the batch is set to posted. I have also found some more business functions that update the F0011 (other than in the attachment).
 

Attachments

  • 168036-F0011-IO-Objects.xls
    41.5 KB · Views: 332
Update:

After reading a thread on JDEList about JDE Triggers and the Oracle doc to which it referred, I have a better understanding of the difference between JDE triggers and database triggers and how JDE triggers work. So I have used the JDE trigger method and so far the testing has been positive and consistant.

I have noted that this may cause some performance degredation, but it is by far the simplest method with the least amount of work by a significant margin.
 
I am not sure if this affects what you want to accomplish, but at least one of the integrity reports updates the posted flag (if all the F0911 rows in a batch are posted but the F0011 row is unposted, F0011 will be updated to posted).
 
Brad,

That is an interesting one, do you happen to know the integrity report name(s)? It may result in some unusual entries in the custom audit table, but I would think it is the sort of thing which should be known.

I guess the situation that could lead to that is the addition of a document to an already posted batch, making the batch status "Pending", and the document is subsequently deleted without being posted, leaving the batch status as "Pending" when all documents in the batch have been posted.
 
Last edited:
I just pulled my 9.0 documentation; here we go: <ul type="square"> [*]Transactions to Batch Headers (R007021). Based on processing options, this UBE will create missing batch headers. This functionality is relatively new in E1. I'm guessing around 8.11, but I may be off a little. [*]Batch to Detail (R007031), This UBE will delete empty batch headers, i.e. batch headers with no GL, AP or AR detail. It will also update the batch status to "D" if all the rows in F0911 are posted ("P") and the batch status is unposted. Based on processing options this UBE will update the batch status to "D" if all the rows in F03B11 and F0411 are posted and the batch status is unposted. This is the UBE that I refered to in my previous post. [/list] The other G/L integrity reports do not affect batch headers. They look for inconsistencies between F0006, F0901, F0902 and F0911 or within F0902 or F0911. The A/P and A/R integrity reports look for inconsitencies between F0411, F03B11, F03B13 and F03B14(not positive) and F0911. There are also integrity reports that compare the total open amounts in F0411 and F03B11 to F0902. None of the A/P and A/R integrities update the batch headers.

Are you using the batch input methods? They create batch headers as well.

I do not know if there are any integrity reports in the other systems such as inventory that affect batch headers.

--------------------
Brad
OneWorld B7321 - EnterpriseOne 8.12
World F6.2 - A7.3
 
Brad,

I really appreciate the effort you put in to get that information, thanks. I wasn't expecting that much detail, thanks again.
 
You're welcome. After I answered, I noticed that you are on 8.11 and my answer was from 9.0. I think the answer applies to 8.11 as well. The functionality to create missing batch headers is "relatively" new in EnterpriseOne, but I think it goes back to 8.11.
 
Back
Top