I am in the process of developing a small database which will allow users to plan truck movements.
Within the database, there are currently three tables.
Within the MT_SKU_MASTER table, there are three fields that contain container quantities in them. The data in these fields is mandatory as it will be used to be perform calculations later.
My problem is, not every item inside this table contains the correct amount fo data. In some cases, it is all there, in other cases only partly and in worst cases, not there at all. What makes the problem worse, the issue is unfixable despite my constant nagging.
On average, the table contains a total of 17,000 records and has a key against the [ITM_REF_NO] field.
I am wanting someone to help me work out a method loop through the table and update the container quantities based on the following conditions.
1. Data exists for FCL_20 but not for FCL_40 or FCL_40H
2. Data exists for FCL_40 but not for FCL_20 or FCL_40H
3. Data exists for FCL_40H but not for FCL_40 or FCL_20
4. No data exists at all.
So in any case, the solution must be dynamic enough to handle any of these scenarios.
To handle scenario 4, I already have a function that calculates the qty for each container type, however, I have no idea on how to update the table with the results. It can be reviewed inside the test db.
For the other scenarios, calculation definitions are enclosed as follows:
For item 326, the following would apply.
Since there is a FCL_40_QTY of 500, we can determine how many cases we can fit per cubic metre of space.
Calculation = 500 (Quantity) / 60 (Cubic Capacity) therefore 8.333 VPs per Cube
So, to determine how many cases can fit inside a FCL_20, we multiply the 8.333 by the available space. i.e. 8.333 x 30 = 250
Similarly, we can apply the same method to determine the FCL_40H qty. i.e 8.333 x 70 = 583.33 or 582 rounded down to the nearest integer.
Once each value has been established, the record inside the table would then be updated.
Could someone please help me with this as I don't know what to do?