Nancy Lytle
nancy.lytle at auatac.com
Tue Sep 23 06:05:08 CDT 2003
>>>>> P.S.: Nancy... How did I do on asking a question???>>>>>> You talkin' ta me?:) That was one of the best laid out questions I have ever seen. The detail was wonderful, I only wish I had some answers for you. What a major project to come back to, just keeping track of the lots will be a headache, not to mention multiple owners. Good Luck, Nancy L. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gary Miller Sent: Tuesday, September 23, 2003 1:44 AM To: AccessD at databaseadvisors.com Subject: [AccessD] Fw: DB design Brainteaser Howdy gang. After a bit of a hiatus I am back with a major project on the chopping block and find myself in need of some learned advise from some of the valuable listers that may have had some more experience with intricate inventory systems than I have had in the past. Just came out of a 6 hour 'Overview' meeting and my head is spinning with some of the intracicies involved. Goals from the Access-Dev Group: Conceptual advice on approaches to handle the tracking, especially on the constant Inventory side and ownership tracking or online resources available for the study of this type of model. Scope: An agricultural product tracking system from pre-birth to final sale. Constant inventory tracking, periodic inventory reduction due to 'Cleaning' the product, very regular inventory location shuffling, disbursements to Owners / Growers ( not always the same ), final sales receipts and P&L. Flow: Grower contracts with company to 'process and store' the Ag product. Processing involves cleaning and purity testing of a given 'Lot' of product. Assigned Lot# needs to live with the product or fraction thereof for it's full life time. Processing will reduce the original poundage by 10-50% depending on initial purity. After this initial adjustment of quantity ( probably in pounds ) the specific lots need to be tracked throughout their lifetime in the system until sold to an outside buyer. Product put into the system may have an initial sale price through contract or the contract may specify that the buyer reserves the right not to 'sell' until a later date when market prices improve, therefore basically leaving the product in the companies storage facilities for an undetermined time. Company is always the buyer, but it is the growers discretion when to sell at prevailing Company buying prices. They can just store and gamble on the prices for an underteminate time. One grower apparently stored for seven years before deciding to sell. Product quality declined, but that can be handled easily as no price is assigned until the sale. Parts of Lots may be purchased by many different contracts. Twists <grin>: 1) As mentioned above, all parts of a given lot must retain that lot# for it's full life, including when it is sold to another company that may resell it or part of it. 2) As a given Field that a Lot is generated from may be owned by one or many owners, any given Lot may have many owners. Apparently with some ownership related issues having to do with inheretinces on Indian Reservations, some Lots may have as many of 60 different owners. Actually it is the Field that the Lot is assigned to that has the owners <grin>. 4) A Lot may start at 10,000 lbs and then will end up being only 6,500 after 'Processing'. 3) A given Lot may be split between many different storage bins, locations and sometimes even different states. 4) As I said in 2), there may be many multiple owners for any given Lots and they may share ownership of multiple Lots on a given Field. Any owner at any time can elect to cash out to the Company at any given time to cash in on Today's market price while the others do not. This reflects a need for the Company to be able to transfer/sell/earmark a section of a given Lot as the section sold without having the particular owner being assigned to a particular section of the Lot. 5) A Lot constantly will change size. Really don't want to go to the point of tracking every individual 'Pound' in a lot as this involves millions of them. 5) It is critically necessary to know what product from what lots are being stored in what locations at the present time. Not so critical to pick a point in history and identify inventory quantities. Not having a tremendous amount of experience with advanced inventory systems, I would appreciate all constructive advice on conceptual approaches to the inventory process especially, on the billing and tracking secondarily. Thanks in advance for any and all insights into what may be needed for a solid foundation, efficient approaches or any other helpful advice. P.S.: Nancy... How did I do on asking a question??? Gary Gary Miller Gary Miller Computer Services Sisters, OR gmiller at sistersnet.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com