How to add shipped items to items to be invoiced? 1

[MAKNIGHT71]

I am writing a custom interface between UPS WorldShip and Macola 7.5.103 (Progression version). I am taking this route because the small company I work for simply cannot afford a full-fledged shipping solution nor any of the available Macola-to-WorldShip modules that are available. Also, non of the canned interface modules provide item-level verification as my little module does.

Anyway, what I need to know is what table do I add records to so accounting can properly invoice the shipped goods the following morning without manually entering them? I have the data out working fine and I am able to retrieve the tracking numbers back in, but I don't know how to add the invoice info.

I know this description may be vague, so please ask for any clarification needed.

Thanks.

 

[dgillz]

In order to answer this question, I need to know the value of the Update IM for Orders flag in OE Setup. If this value is not B-Bill, then you will be writing code for a long, long time.

Also, in IM Setup, is allocation set to hard or soft?

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

http://www.gainfocus.biz

 

[MAKNIGHT71]

Thanks for the super-quick response...

In OE Setup, the update setting is set to B=Bill.
In IM Setup, allocation is set to S=Soft.

I know with our old shipping software (now obsolete due to PLD handling changes at UPS) we simply ran QuickShip each evening. I don't know exactly what this did, or if it is even part of Progression originally.

Thanks.

 

[dgillz]

In OEORDHDR_SQL, change the status to 8, populate frt_amt (if you want to do this) change billed_dt to an 8 digit integer in a YYYYMMDD format, and change selection_cd to 'S'.

Assumming you have no backorder quantities (the order line either ships in total or it doesn't) in the OEORDLIN_SQL change the qty_to_ship to the qty_ordered, change the select_cd to 'S', and change the billed_dt to the YYYYMMDD date.

If you get this working, you may have a sellable piece of code.

What are you using to write this?

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

http://www.gainfocus.biz

 

[MAKNIGHT71]

I am currently using VB.NET 2003, but may try it also in Visual C++ to see if it offers anything (speed, etc).

Do I change the OEORDHDR_SQL file even if all line items have not shipped? If I change the billed_dt and frt_amt fields each time, does this affect later shipments based on this order, or will Progression correctly pick these later shipments up for billing as well?

Nearly everything we ship is done so in individual boxes and thus not everything may ship at the same time, and items are invoiced as shipped (hence the need for item level verification of shipment).

Thanks.

 

[dgillz]

Yes, change the OEORDHDR_SQL file. Only the line items with selection code S wil lactually invoice, and the backorder will be created, provided that the customer accepts backorders.

Changing the billed_dt and frt_amt will affect the next invoice printed. If you have multiple shipments during a single day, you may want to accumulate the frt amounts, as well as updating the OEORDLIN_SQL.

As far as each time you update the OEORDLIN_SQL table, the key here is going to be to update the qty_to_ship. Will you be creating backorders? In other words, a partially shipped line item? If so the data you need to update is not complete, as I said in my reply these fields to update assumes the entire line is either shipped, or not shipped. Backorders opens up a whole new can of worms.

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

http://www.gainfocus.biz

 

[MAKNIGHT71]

Okay. That all makes pretty good sense - roughly what I expected knowing how other bits in Progression work.

I am receiving confirmation from our sales and accounting departments on whether we partial-ship line items, however, I believe we do when the customer requests it.

Is that can of worms too complicated to worry about if we do not (for the sake of being "full featured")?

Regards.

 

[MAKNIGHT71]

I have received confirmation that we do indeed partial-ship line items, thus necessitating line item backorders.

Thanks.

 

[dgillz]

This is doable, but I am away from the office until Friday. I will post back then. One quesion however, how does Worldship know the qty shipped?

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

http://www.gainfocus.biz

 

[MAKNIGHT71]

WorldShip does not track the qty's. The interface handles all of that. It prompts the user to enter a qty for each item in the package.

Would it be easier/simpler/smarter to implement using the QuickShip flat file format?

I look forward to hearing from you Friday. The entire application is written except for the passback code.

Thanks.

 

[MAKNIGHT71]

dgillz,

Have you had any further thoughts regarding this? Thanks.

MAKnight

 

[stravis]

I would suggest using the quickship feature. There are a number of other issues that have not been addressed by dgillz that need to be considered. These include but are not limited to the following:

1. Inventory Allocations need to be set when you change the qty to ship.
2. If you use Serialized or Lotted Items you need to specify the Serial No or Lot when you modify the qty to ship and perform the select for billing status as advised.
3. If you use commissions the sales order totals need to be calculated and updated when you select for billing.
4. You must update the tot_sls_amt and tot_dollars fields on the order header when you select for billing.
5. Taxes must be calculated when you select for billing if any of your customers are taxable.
6. Order Header and Order Line Audit Trails?
7. Kits?
8. etc...

This is a very big can of worms that may need to be reengineered when you do a version upgrade. Using quickship the interface stays the same and it handles all the heavy lifting.

Scott Travis

http://www.infospring.net

 

[MAKNIGHT71]

Thanks. While we don't use many of the features you mentioned, I believe quickship would solve a lot of potential headaches by automating the items you mentioned - epecially in the Pervasive.SQL world (potential locked records stopping info from being updated, etc). What is the quickship flat file format? Thanks.

I'm sooo close. While this first version won't be full-featured, it should handle most common shipping situations.

MAKnight

 

[stravis]

I would copy the format here but it is rather large and copyrighted. You can find the file format in the bar coding help for Macola Progression. Just do a search for ASCII and you will find the various file formats.

Scott Travis
infospring.net

 

[MAKNIGHT71]

Thanks. I think this should do it.

Thank you, dgillz and stravis. I will post a followup here if I decide to make the software available.

MAKnight

 

[MAKNIGHT71]

Quick follow-up...

REC-TYPE, I assume I can use a detail record for every record/entry in the flat file.

UNQ-SEQ-NO - Is this the seq_no from progression, or just a unique number in the specific flat file?

UNIT-COST - Is this required for O/E Shipping?

Thanks,
MAKnight

 

[stravis]

I have not designed for the Quick Ship flat file before but I will give this my best shot.

REC-TYPE - Other file formats require a single H record and 0 or more D records. Because the documentation is somewhat lacking, I would suggest a little trial and error with a text editor. Make your changes, import the odb, and then check for quick ship exceptions.

UNQ-SEQ-NO - Again no experience but typically this would relate to the sequencing in Macola. I would suggest that this relates to oeordlin_sql.line_no or oeordlin_sql.line_seq_no. These two values can be the same or they can differ if you delete a line item from an order from the middle of the list of order line items and then save your order. I would suggest setting up a test order, deleting a line from the middle of the order line list, and then attempt to import a quick ship transaction for the last line item on the order.

UNIT-COST - I believe this should either be all zeros or all spaces.

I hope this was at least some help.

Scott Travis

http://www.infospring.net