Not able to change or delete dmaai records when SQL'd into F4095

[Jill Bader]

Not able to change or delete dmaai records when SQL\'d into F4095

We are in the process of upgrading from 8.0 to 9.0 and are doing a extensive redesign to our chart of accounts. Rather that reentering all the DMAAI's manually, I used SQL to load the F4095 with the new AAI's. The DMAAI's work fine in recording transactions, but if we need to change them or delete them through the DMAAI application, we cannot. No error message is given - it just doesn't take the change. Has anyone tried loading the table through SQL and have you been successful in doing so? Here is an example of the statement used to insert the records:
Insert INTO F4095 (MLANUM,MLCO, MLDCTO, MLDCT, MLGLPT,MLCOST,MLMCU, MLOBJ) VALUES (3110,'00010','WO','IM','IN30',' ',' 10','26110');
Thanks, Jill Bader
E1 9.0 - TR 8.98 on Win 2008, 64 bit. Oracle 11g 64bit

 

[JdeInNJ]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

It looks like you did not populate every field in the table. I am most knowledgable in GL, so I noticed that you did not enter a subsidiary account. I don't have access to F4095 to check, but I would expect it to also have audit fields for the date, time, program and user that did the last update. If F4095 does have these audit fields, and they are blank, that might be causing your problem.

Try adding a new row manually through the interactive application and then use UTB or Databrowser to compare the results to a value entered with SQL to see what is different. That should help you figure out what is going wrong.

Brad

 

[Jill Bader]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

Thanks Brad. You are my hero I thought that I could skip the sub column because we aren't using it. When I added a blank insert into that column, it looks to be the solution.
Oddly, there are none of the audit fields in this table. I surely would love to have that information available!

 

[JdeInNJ]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

Yea! I'm glad it worked. Thanks for letting me know the outcome. I'm always glad to get feedback from my posts.

Brad

 

[kmm_ksk]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

I want to use sql to update my aai tables... and am being halted by an IT manganer who is afraid to do anything through SQL... I went to change the SO types manually, but the extra option is not available, as it is with IV/WO types... I want to use the MLCOST field to post to different Sub accounts...

any help would be greatly appreciated...

km

 

[Craig_Potter]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

I assume that you have turned on the processing option to enable Cost Types in P40950. If so, it only enables this field on the AAI's that actually use this functionality (as listed in the Processing Option) so SQLing values in these AAI records would only lead to that awful moment when your manager proudly boasts... "see - I told you so"...

 

[fi0317]

Re: Not able to change or delete dmaai records when SQL\'d into F4095

Hi, against AAI table F0012, you don't need to use sql statement to insert them. Actually you can do it interactively by uploading them from Excel.
Thanks.