Journal Source SQL Table

[prog_0927]

Macola Progression 7.9.310
What is the table name that holds journal entries?

Thanks,

 

[dgillz]

Posted journal entries go into the GLTRXFIL_SQL and GLTRXHST_SQL tables. Unposted go into the GLGENTRX_SQL table.

I offer an Excel2GL application which allows you to post journal entries directly from Excel.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[prog_0927]

Thank you for the response. I don't see anything in the GLGENTRX_SQL and we have not posted yet today. What is the source (SQL table) of the jnl_src in the IMNINVTRX_SQL table? I see there is a systjnlhst_sql that looks like the jnl_src but I don't see where the non history table for that one resides?

 

[dgillz]

Are you asking about the journal entries that result from the IMINVTRX_SQL records? If so, these first go to the IMDISFIL_SQL before you post from subledger. The actual value is the transaction made (example IP=Invoice Post) and the COMPFILE_SQL.start_jnl_hist_no. Neither the IMDISFIL_SQL nor the IMTRXFIL_SQL has a history table.

What are you trying to accomplish?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[prog_0927]

Thank you! So if I wanted to automate an inventory transaction with an offsetting G/L transaction I would just need the code to update the IMINVTRX_SQL for the part moving and then the IMDISFIL_SQL for the debit/credit of the accounts?

 

[dgillz]

It isn't that easy. First you would need to increment the start_jrnl_hist_no in the COMPFILE_SQL table.

If your transaction increased inventory AND was at a different cost, you would have to recalculate average cost and update the IMINVLOC_SQL table.

If the transaction was on a serial, lot or bin controlled item, you would need to update the IMINVBIN_SQL, IMLSMST_SQL, IMBINTRX_SQL and IMLSTRX_SQL tables at a minimum.

Please answer 2 questions:

1) What are you trying to do?
2) Do you own Wisys?

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[prog_0927]

Thank you.

Would that start_jrnl_hist_no be the next number I would use for the IMXXXX number for the IMINVTRX_SQL transaction? The costs are set at model year and I am pulling that data from a SQL query. We don't use serial, lot, or bin.

1. I am trying to reverse the error that the Wisys component is doing on the creation of production orders. It is not returning our negative BOM into raw materials despite Macola having no issue doing this when the order is closed.

2. We do own Wisys and I am using the default Create POP order component but they do not support a negative BOM in their code. I have their SDK code but there is nothing in there for POP orders. I am thinking it is returning the -1 BOM as a positive 1 before processing.

 

[dgillz]

Yes, the start_jrnl_hist_no is where you get the number and has to be incremented. I cannot believe Wisys didn'y cover this. What does Wisys do when a negative qty per is encountered? Throw an error? Nothing?

You could build a grid in Wisys to do a receipt of these items. What a pain though.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[prog_0927]

Thank you very much for your help on this. It does nothing and throws no error just leaves the item in finished goods and ignores the two sided transaction to return the money to raw materials. I have a meeting with their developers today and will let you know what they say.

 

[dgillz]

I am interested in what they say. In the meantime you could create a Wisys process grid to put the negative component in stock. Just use the Wisys receipt object.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html