Stuart McLachlan
stuart at lexacorp.com.pg
Thu Sep 25 17:57:12 CDT 2003
On 25 Sep 2003 at 13:00, Gary Miller wrote: > > Trailers start rolling in from a given field section under a > given contract#. Weighmaster assigns the new lot number ( > there goes our autonumber PK's, may still use them and force > the user assigned to be unique ) You weren't planning on using the Lot Number as PK anyway where you? ....*where you*? ....WHERE YOU? :-) > > Weigh tickets are sent in to the office for processing. When > all weigh tickets are entered they will then 'Finalize' the > Lot and the total Field Run pounds will be permanently > captured as part of the Lot record as eventually the weigh > tickets will be discarded from the program. This number will > never change Until someone discovers a Weigh Ticket that didn't get entered until after someone else "finalised* the Lot :-( Believe me - it *will* happen! >and is a ongoing basis as to quality of the > growers operation based on the comparison of Clean Seed > weight achieved after cleaning. > > The FieldRun product is now stored somewhat indefinitely in > loose form until it is processed for cleaning. When it goes > to cleaning process it will lose 20 - 50 % of it's weight > depending on the quality of the grower's operation. It will > now come out of cleaning in a bag form and is usually stored > on pallets. All clean seed will always be in bags of varying > weights, but are always counted and referred to in pounds. > > After cleaning a sample is sent for testing analysis that > will give it a purity% and quality grade. A decision is then > made whether to keep the seed purity as is or to send it in > for another cleaning to improve the purity. A third cleaning > is rare, but does happen. > > After the cleaning processes are completed, we now have a > weight (Clean Seed) that will be the basis of the Owner's > shares and at that point the shares should be converted from > a percentage to an actual quantity as any subsequent loses > from spoilage or rodent damage become the liability of the > Company. > > Based on what we did on the inventory side I am now thinking > that the best way to go may be take this point in the > process to create records in a LotOwnership table that would > store LotID, OwnerID, QtyOwned (Lbs.), SellDate, SellPrice, > CompanyOwned(Y/N). QtyOwned to come from the Lot > CleanSeedWeight / % of ownership. SellDate and SellPrice > would remain blank until the day that they decide to > transfer ownership to the Company. The day they sell we > would fill in the date and the price and now flag it as > Company owned. > Do they have to sell the whole LotOwnership in one go., or can they sell just part of it to the Company? If so, you will need to child table to record sales qtys, prices and dates. In either case, you don't really need the flag. Default SellDate to Null. Then if you have a valid date, it must be company owned. > There will be occasional additional cleanings that will > neccesitate a recalculation of these poundage numbers, but > they will always happen before any sales transactions are > allowed to occur. It should be routine as long as I always > create or recalculate existing on any cleaning entry. > > Another complication that just came up and affects the above > is that sometimes an Owner will want to sell 5,000 of the > 10,000 that they own of a given lot. I am thinking that if a > transaction occurs that is less than the total share value I > then prompt to check if this is an error or do they want to > close the existing share record, reduce the Qty there and > create a new one for the Qty balance. Some owners would end > up with multiple lot ownership records in this instance. See comment above :-) Use a child table to record sales from a lot. (Start Wt - Total of sales to date) will always give you a balance which you can check against on each new sale to see whether the amout in valid and to close the lot if all is sold. You could also use this child table to record reductions by additional cleaning above - "sale price $0.00" > > As a big reporting requirement I will need to constantly be > showing the Company's 'position' on the all of the Lots and > my thought is that this simple CompanyOwned flag will be any > easy way to pull the amount that they own and is available > for final outside sales. > > As these records will never be deleted, only deactivated > with the Lot finalization, would I really need a transaction > history table such as the Inventory one? > The child table I suggested above is, in fact, a transaction table. > There is another interesting twist to what happens after the > final cleaning and the percentage amounts have done a final > conversion to poundage. The Company will very routinely sell > to the outside ALL of the product and ship it away even > though the Customers have not yet actually sold it to the > Company. This is what they refer as to going into a 'Short > Position' on the product. This works because the final > ownership amounts have now been set in stone once the final > Clean Seed Weight is established and the only thing that > really remains is for the Owners to pick a day when the > market price seems to be most beneficial compared to their > financial situation. Kind of like a Futures Market. > Apparently one Owner held out for seven years after his > product was physically out the door before he did his actual > sale. > > I will probably need to hold a Lot as active until the last > ownership sale is transacted in combination with it being > totally out of inventory. I need to figure a good trigger > for resetting this flag in the tblLots as I don't want to > burden all of my queries with unneccessary conditions and I > don't want to trust the users to do it. Maybe a second > maintence db linked in that I could run on a schedule at > night to do updates like this looking for nothing in > inventory and all owners settled with. > A trigger would be better, on each sale, check how much is still not company owned. -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support.