SQL Insert of Price History Records

[jdesmm]

We have been having a periodic problem that we have not yet been able to trace (although I've found some interesting food for thought in some past postings to the list) in which lines in the F4074 - Price History table - "disappear." This disappearance occurs at some late stage of the game apparently, because all of the related information - sales order detail unit price, G/L distributions, etc. are there to show that the lines did exist and were processed properly. We are pursuing the cause of this problem, but in the meantime, for reporting and auditing purposes, the company needs the lines re-added to the F4074 and has requested that IT insert them via SQL.

I have been researching the possible impact of doing this and so far, it looks like since we already have all of the financial information in place, it is a matter of inserting to the F4074 only, emulating the original data. To determine this, I went through the code in the P4210 / P4074 to follow along with what happens when users add pricing information during their normal process. I feel pretty confident at this stage that we can do this without adverse impact (we will of course test thoroughly in PY first!), and I would like to confirm with any gurus out there that there are no other related tables we need to hit given our scenario.

Thanks in advance!!

--Shelley

 

[Sef]

Hi Shelley

Could the reason for the loss of F4074 records be that the processing option to purge F4074 records is set to '1' in one or more of your End of day (R42800) versions?
Records in F4074 are no longer required once end of day has run, other than for reporting purposes and to be able to use the price history row exit in the Cust Service Inquiry. There are no other tables that need to be populated than the F4074. Populating the table by SQL is technically possible, but it would be quite tricky to determine the exact make up of the records especially when multiple adjustments are used. If you don't use accruals or other adjustments that don't affect the unit price, maybe it is an idea to calculate the difference in unit price (UPRC) and List Price (LPRC) (exF42119) and populate the difference in F4074 under a pseudo adjustment name?

Anyway, good luck and I hope you find the reason for the loss soon

Rgds,

 

[andyklee]

I second Sef's comments, and just wanted to add that you should be aware of
the SO09 field (ess, oh, zero, nine). This field is in the F4211 and is an
indicator that there are F4074 records attached to the F4211.

Andy Klee
www.JDETips.com

 

[jdesmm]

Sef,

No, we checked the processing options, and they are not the culprit. The loss of data is very occasional - they're finding about one problem per week, so it's not like we have anything happening on a mass or even regular basis. My gut tells me that something is going wrong procedurally, but we have not been able to reproduce it.

As for populating the table, the users have given us the exact information required to get all the lines in as they were originally, so I think we'll be okay, as long as there aren't other tables to worry about.

Thanks!

 

[Larry_Jones]

Shelley,

you shouldn't have to worry about any other tables being populated or requiring changes due to your SQL insertions.

You should of course ensure that the entries balance to the F4211 amounts for posterity's sake.

Have you thought of putting a database trigger on F4074 that logs deletions to a audit table? It would certainly catch the culprit. Just a thought.

 

[DSauve]

Shelley,

We have had instances in the past where we also lost Advanced Pricing for a variety of reasons, and have been able to add the F4074 records back in with SQL just fine. I don't know if your records are actually being deleted or the records being blanked out, but one other thing you could do is create a trigger (I'd use a database trigger) to capture the exact date/time that the loss of data occurs. This might help track down what was going on at that time.

 

[jdesmm]

Larry,

Thanks. Regarding the trigger, we tried that approach at one point on a separate enhancement and found that we had a lot of locking issues due to conflict between the triggers and JDE business functions. I think we are pretty trigger gunshy at this point. What I am looking into is a tool called Log Explorer, which allows you to review database transaction logs and will show you statements run over particular tables.

 

[jdesmm]

You didn't have any locking issues setting up a trigger? We have had them in the past, although we weren't trying to set a trigger on that particular table. Also, just to clarify, do you mean a JDE trigger or a backend database trigger? Thanks.

 

[DSauve]

Shelley,

We have never had any locking issues with our triggers, which are all Oracle (backend) database triggers. I've not tried any of the JDE triggers, but have seen several posts about problems with them. We currently have triggers on Item Master, Item Branch, WO Master, and WO Parts List tables. Our transaction volume is fairly low, as we are a relatively small company (250 employees) and we do engineer-to-order primarily -- no large production/process runs.