×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Manual Creation of doctype=I (Issue)

Manual Creation of doctype=I (Issue)

Manual Creation of doctype=I (Issue)

(OP)
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.


RE: Manual Creation of doctype=I (Issue)

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

RE: Manual Creation of doctype=I (Issue)

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:
www.gainfocus.biz/exceladdin.html

RE: Manual Creation of doctype=I (Issue)

(OP)
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.

RE: Manual Creation of doctype=I (Issue)

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:
www.gainfocus.biz/exceladdin.html

RE: Manual Creation of doctype=I (Issue)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close