Manual Creation of doctype=I (Issue)

[JZ2016]

Does anyone know the sql behind the Inventory Transaction History Report? I am attaching a screen shot.

I am manually entering an Issue (doctype=I) into the macola database, instead of entering it through the Macola application. I have successfully entered a transaction into the IMINVTRX_SQL table, and that record shows up in the attached report. I entered the cooresponding debit/credit into the GLTRXHST_SQL table, thinking that these would be the child records that would show up as "disbursements" in this report. However, they do not show and I instead get the message "No distributions on file". Does anyone know the sql behind this Inventory Transaction History Report so that I can see where it is trying to find distributions? Or can someone explain which tables need to be populated for these child records if the GLTRXHST_SQL table is not what should be used?

I realize entering records directly in the database is opening a can of worms. My hands are a bit tied on that, as my customer is trying to use an outside application to insert these records and I have limited knowledge of Macola.

 

[JZ2016]

Upon further review I am wondering if I should be adding entries to IMDISFIL_SQL instead of GLTRXHST_SQL. Any thoughts?

 

[dgillz]

You are correct, create the records in the IMDISFIL_SQL. Make sre the int_fg field is NULL. These records will update the GLTRXFIL_SQL, GLTRXHST_SQL, GLBALFIL_SQL, and potentially JOBHIST_SQL when you post from subledger.

Please explain "disbursements". I assume this is a typo as this has nothing to do with cash.

Question: When you are inserting a type I transaction, are you also reducing the quantity on hand? Because you should.

What transaction exactly does this record in the IMINVTRX_SQL table represent? Why are they using an external program? What business issue is this actually solving?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:

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

 

[JZ2016]

Thank you for your help. It seems to be working now, for the most part.

Yes I am updating the quantity by reducing the qty_on_hand in table IMINVLOC_SQL.
Also, "disbursements" was a typo. I meant "distributions".

I do have a couple more questions:
1. In the IMDISFIL_SQL table, what goes in the jnl_src field? Right now I am putting in an empty string.
2. In the IMINVTRX_SQL table, what does the cus_no value link to? I'm currently just putting in a customer name here as a placeholder and it shows up on this Inventory Transaction History Report but I'd like to give my client a drop-down list of customers to choose from so I can put that customer in the field. What customer table should I be pulling that data from?
3. I don't completely understand what you mean by "post from subledger". After I insert records into IMINVTRX_SQL and IMDISFIL_SQL, and update the qty_on_hand in IMINVLOC_SQL, do I also have to add records to GLTRXFIL_SQL, GLTRXHST_SQL, GLBALFIL_SQL, and JOBHIST_SQL as you mentioned? Or does this happen by some other process?

To answer your questions:
Q: What transaction exactly does this record in the IMINVTRX_SQL table represent?
A: Removing an inventory item from stock and consumine/using the item on the factory floor. They supply the account (mn_no and sb_no) that wants the item and then also use the same asset account to remove from inventory.

Q: Why are they using an external program? What business issue is this actually solving?
A: They want to have a custom application on a Microsoft Surface tablet so they can perform some Macola transactions in a more mobile way.

 

[dgillz]

Sorry for the later reply.

The post from subledger process is a Macola process run at least monthly. It will take the data out of the IMDISFIL_SQL and populate the GLTRXFIL, GLTRXHST, GLBALFIL, JOBHIST, etc., as it is designed to do.

You will likely have an issue with leaving the source journal blank. It should be "IM", then append to the the value found in the compfile_sql.start_jrnl_hist_no field, where comp_key_1 = 1. Then increment this value every time you write the 2 records to the IMDISFIL_SQL table.

The customer table and field is ARCUSFIL_SQL.cus_no.

You should look into Macola 10 which already supports tablets. There are also Macola Business Objects available so you wouldn't have to recreate the wheel on an application like this, you would just call the inventory transaction object and pass it a few pieces of data like transaction type, item, location, date and quantity. The objects do everything else.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Macola 10, Synergy, and Crystal Reports.
Check out our Macola tools:

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

 

[JZ2016]

Thanks I will try your suggestions, just haven't gotten a chance to.