Developing ASN - Best way to find when item numbers are invoiced

[pticarl]

Hello everyone,

I am currently using Macola 7.6.1 and I am trying to develop an ASN type solution for one of our internal customers, and if it works well, possibly many of them.
My problem is finding when particular customer orders are invoiced. I can link the customer order data from OEORDLN_SQL, and I know that I can look in the IMINVTRX_SQL table to find items that are invoiced by checking source and doc_type? This can work for me but the SQL gets fairly complicated, and I am unsure how to do it in a single query.
Is there a better table that can provide this data? We have some documentation for Macola but the DRM we have is version 6.0 (I think) and it seems somewhat outdated.
I will be using KnowdledgeSync's EventManager as part of this solution for database access, report generation, and email delivery.

Any suggestions?

Thank you,
Carl

 

[dgillz]

In OE setup, on the bottom of page 2 there is a flag for "Update IM for orders". What is the setting of that flag?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pticarl]

The OE Setup "Update IM for Orders" is set to "B-Bill"

I was also exploring joining the two tables on a sales order number as an alternative, since it is a common field across both tables.

We traditionally invoice items the same day that we ship them to the customer. This date is entered as the "promise date" in OEORDLIN. So, in theory I can trap the invoiced amounts by targetting a specific date, or use a currentdate type function in SQL.

Thanks
Carl

 

[johnCronin]

When the orders are invoiced and posted they move to the oehdrhst and oelinhst tables. So you won't be able to get the information from oeordhdr and orordlin once the order is invoiced and posted. I would use the oehdrhst and oelinhst tables if waiting till the invoices are posted is acceptable.

 

[MacolaHelp]

As long as you are going with the event manager, how about creating a crystal report or file of your choosing after printing invoices and prior to posting to select orders whose status is 9 (invoiced) for oeordhdr to send the shipping notification? You could even email it using event manager. Since the order header & line files are much smaller than the history files, it should run pretty fast.
I would not use iminvtrx, I would link oeordhdr & oeordlin & use the ord_type, ord_no, cus_no, inv_no to link those tables together.

EDI also creates ASN docs, but might be overkill for what you want to do.

 

[pticarl]

MacolaHelp,
That's a great suggestion and one that I will run down. I was looking at using IMINVTRX because we would also like to catch short shipments as much as possible. I was using IMINVTRX to catch shipments to a customer order, but I can still do that via the OELINHST or OEDHRDST tables also couldn't I, by listing the original order amount and the actual amount invoiced?

I am sorry if this question doesn't make sense, or seems contradictory. I am struggling with the table structures and the lack of good documentation forces me to take educated quesses on a lot of things.

Thanks!

 

[MacolaHelp]

You can compare qty ordered to qty shipped in the order line or line history files. Iminvtrx will show you what shipped but not what is backordered if you allow backorders. You would still have an allocation in iminvtrx for the backorder if you didn't close the customer order after a partial shipment, but your visibility to partial shipments is much better in the order entry files.

File definitions are in the technotes folder on one of the CDs. Be sure you have at least version 7.5.103d, which is the last time file definitions & ddfs were changed. They are in pdf & rtf format w/file names such as oe103d75.rtf. These docs & many others on data structures can be downloaded from the reference section of the macola customer portal. There are a few docs that relate to the screen sets & what tables are opened during program updates, similar to the DRM we got for version 6. Can't remember if I got those docs from the website or from the flex install. There is a lot of info about data structures, etc in the flex documents & manuals. Many of the programs open the same (and more tables) in version 7 than in v6, so some of the info in the v6 DRMs can be helpful.

Macola is a complex program, over 7 million lines of cobol, & depending on which switches you have set can be very feature rich or very limited in scope & function. I've worked with many versions of macola for almost 15 years & find I learn something new every day. It would be pretty difficult for documentation to exist to explain if you have the IM setup configured with these 4 switches set this way & the other 10 set this way, exactly how all the processes would work. Some of this will just be a learning and testing process for you.

Another method I find helpful is to check the file handles open in the pervasive monitor while executing a particular process. I'm not sure how to do this in SQL, tho. Someone else on this forum can probably educate us on this.

 

[pticarl]

Thanks for the excellent responses everyone, especially Macolahelp!

I have narrowed down some of my problems and I will get Flex installed ASAP since I need it for some other work also.

It looks like I have a lot of reading ahead of me!

Thanks again!