INSERT Data into E1 Tables - Issue with NULL Values

We have a 3rd part application inserting data in to the F0411Z1/F0911Z1 directly. The source system is an Oracle DB. The issue is that for the over 100 coulumns that we have not populated data, NULL values default. This make it impossible to view the data via an E1 app. Any suggestions on how we can get the SQL to default Balnks or Zeroes as needed, without specifically setting every single column to a Blank or Zero? Thanks.

Hi,

I had the same problem, try to initialize the field POST with a blank value (' ')

We did force some of the fields to a ' ' (blank). We can at least run the R04110ZA over the data. This still does not allow us to view the data from the Batch Revisions screen. In the event of an error, it becomes next to impossible to troubleshoot, since the error report is not very useful.

Teresa,

as you have encountered JDE apps do not like NULLs - the JDE runtime prohibits nulls from being written to the tables (except under certain conditions).

If you are unable to modify the 3rd party application directly to include the proper defaults, then I suggest you write a SQL script that updates the Z table columns left null by the application, if possible triggering it to run immediately after the third party application completes (another possibility is to use a database trigger to do the same).

Don't try to get the JDE apps to accept nulls - there's no value in that approach - just try to get clean data to it.

My advice for what its worth ;-P

Thanks for the suggestion Larry - willforce the custom app to default.

I had this problem with a company merger we just had. After inserting the data via a third party app, it wasn't showing up, and my analyst was starting to get frustrated with me.

I took the query the batch revisions screen used via a debug log and manually ran it. The problem was that the business view required certain columns to match between the tables, and if they were both null, it wasn't treated as a match. The solution was I had to use process of elimination on the query to determine which columns required a value. You can do this by noticing the SQL query has a series of T0.col1=T1.col2 clauses on either the table join or the where condition.

Once I found which columns needed to match, I simply set the third party app to set them both to a '.' and everything appeared.

In E1, if you have Batch Approval turned on, the Batch Revisions screen is hard coded to only allow you to see batchs that you are authorized to approve or that have your User ID on them. This is different from World.

As for Nulls, I don't know what your platform is, but I do know on as AS400, Nulls DO NOT prevent the JDE apps from displaying data.

--------------------
AS400
8.9, upgrading to 9.0, in about 15 months

Thanks Gary. This is a good start.

I made sure that we do not use Batch Approval. Besides, if I 'key' in the same data on the batch revisions, I am able to pull up the data via the app. I will try Gary's solution and let you all know the result.

We found the issue. Did what Gary suggested to run the app in Debug. I found the SQL's (no joins here) and ran them interactively & isolated the issue. The F0411Z1/VLEDSP was initially NULL which we forced to a Blank, which was a mistake. Although the processor looks at Blanks or Zero, the interactive app looks for "0" specifically. Once we changed this value, the data displayed.

Thanks to everyone for their suggestions.