Re: [WebERP-developers] Bug? MRP for WO at reqdate instead of startdate

[Tim S. <ti...@we...>]

Hi Klaus,

On 27 May 2012 11:43, Tim Schofield <ti...@we...> wrote:
>
> Agreed. Adding extra fields in the database for BOMs would not have to
> complicate matters too much. I suggested in an earlier thread to Exson
> that we could define a function that returned a BOM (in the current
> format) based on a particular date. Then all we have to do is to use
> this functionality wherever in the code we query the bom table (ok
> there is a bit of work to do, but locating all the points shouldn't be
> hard). I would suggest we looked at how we wanted to extend the bom
> table, add in those fields, define this function I mentioned, then we
> could start to incrementally improve the manufacturing functionality.
>

I was giving some thought to this today while out in the garden
soaking up the sun. It should be easy to upgrade the bom table
definition without affecting backwards compatibility with the current
scripts. As a test I created two tables as follows:

CREATE TABLE bomheader (
`bomid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`productid` VARCHAR(20) NOT NULL DEFAULT '',
`uom` VARCHAR(20) NOT NULL DEFAULT '',
`revisionlevel` INT NOT NULL DEFAULT 1,
`loccode` char(5) NOT NULL DEFAULT '',
KEY `ProductID` (`productid`),
CONSTRAINT `bomheader_ibfk_1` FOREIGN KEY (`productid`) REFERENCES
`stockmaster` (`stockid`),
CONSTRAINT `bomheader_ibfk_2` FOREIGN KEY (`uom`) REFERENCES
`unitsofmeasure` (`unitname`),
CONSTRAINT `bomheader_ibfk_3` FOREIGN KEY (`loccode`) REFERENCES
`locations` (`loccode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE bomlines (
`lineid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`headerid` INT NOT NULL DEFAULT 0,
`componentid` VARCHAR(20) NOT NULL DEFAULT '',
`uom` VARCHAR(20) NOT NULL DEFAULT '',
`revisionlevel` INT NOT NULL DEFAULT 1,
`quantity` double NOT NULL DEFAULT '1',
`effectivefrom` DATE NOT NULL DEFAULT '0000-00-00',
`effectiveto` DATE NOT NULL DEFAULT '0000-00-00',
`workcentreadded` char(5) NOT NULL DEFAULT '',
`autoissue` tinyint(4) NOT NULL DEFAULT '0',
KEY `ComponentID` (`componentid`),
CONSTRAINT `bomlines_ibfk_1` FOREIGN KEY (`componentid`) REFERENCES
`stockmaster` (`stockid`),
CONSTRAINT `bomlines_ibfk_2` FOREIGN KEY (`headerid`) REFERENCES
`bomheader` (`bomid`),
CONSTRAINT `bomlines_ibfk_3` FOREIGN KEY (`uom`) REFERENCES
`unitsofmeasure` (`unitname`),
CONSTRAINT `bomlines_ibfk_4` FOREIGN KEY (`workcentreadded`)
REFERENCES `workcentres` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I know there are other things we could add in or not put in here but
stay with me its an experiment.
Then I copied the current bom data over to these news tables with:

INSERT INTO `bomheader` (`productid`, `uom`, `revisionlevel`,
`loccode`) (SELECT DISTINCT parent, (SELECT units FROM stockmaster
WHERE stockid=bom.parent), 1, loccode FROM `bom`)

and :

INSERT INTO `bomlines` (`headerid`, `componentid`, `uom`,
`revisionlevel`, `quantity`, `effectivefrom`, `effectiveto`,
`workcentreadded`, `autoissue`)
	(SELECT (SELECT bomid FROM bomheader WHERE productid=bom.parent),
component, (SELECT units FROM stockmaster WHERE stockid=bom.parent),
1, quantity, effectiveafter, effectiveto, workcentreadded, autoissue
FROM `bom`)

Then I drop the original bom table:

DROP TABLE bom

Finally I create a view which identically matches the original bom
table taking the data from the new tables:

CREATE VIEW bom (`parent`, `component`, `workcentreadded`, `loccode`,
`effectiveafter`, `effectiveto`, `quantity`, `autoissue`)
AS SELECT bomheader.productid, bomlines.componentid,
bomlines.workcentreadded, bomheader.loccode, bomlines.effectivefrom,
bomlines.effectiveto, sum(bomlines.quantity), bomlines.autoissue FROM
bomheader LEFT JOIN bomlines ON bomheader.bomid=bomlines.headerid

All of the old scripts with the exception of BOMs.php will continue to
work as before, but now I have expanded the bills of material so that
we can include a lot of extra functionality, that we can then start to
bring into webERP without harming what is there already.

Does this rambling make sense or have I had too much sun?

Thanks
Tim

-- 
WebERP Africa Ltd
+447710427049
+254706554559
www.weberpafrica.com
@TimSchofield2