web-erp-developers — Discussion on development of webERP

Re: [Web-erp-developers] Re: Desktop manuals

[Daintree <p.d...@pa...>]

Whoops! Sorry Jesse!

Phil
----- Original Message -----
From: "jesse" <je...@st...>
To: <web...@li...>
Sent: Monday, April 19, 2004 7:33 AM
Subject: RE: [Web-erp-developers] Re: Desktop manuals


> couple things -
> Jesse, not Jessie. Jesse is also literally missing an eye, which I
sometimes use as a spelling reminder, and a little pun that I get a kick out
of, so you can too
> Jesse is he, not she.
>
> that is all :)
>
> > -----Original Message-----
> > From: web...@li...
> > [mailto:web...@li...]On Behalf Of Phil
> > Daintree
> > Sent: Sunday, April 18, 2004 04:38
> > To: web...@li...
> > Subject: [Web-erp-developers] Re: Desktop manuals
> >
> >
> > Hi Danie,
> >
> > > How do I check out a specific version from CVS and then run a diff on
> > > that version. Specifically I am making the changes against 2.8 and
want
> > > to do a diff against that so I can send the diffs to you as I go
along.
> > > It should be easier to review than everything at once.
> >
> > The CVS always contains the very latest scripts I always update
> > as I make any
> > mods. Don't forget that Jessie is working on a substanital effort
> > to give us
> > serialised stock, which she is about to send me scripts for. So
> > if you have
> > major mods may be best to hold off till I get Jessie's stuff
> > committed to the
> > CVS.
> >
> >
> > Phil
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id70&alloc_id638&opk
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>

RE: [Web-erp-developers] Re: Desktop manuals

[jesse <je...@st...>]

couple things -=20
Jesse, not Jessie. Jesse is also literally missing an eye, which I =
sometimes use as a spelling reminder, and a little pun that I get a kick =
out of, so you can too
Jesse is he, not she.

that is all :)

> -----Original Message-----
> From: web...@li...
> [mailto:web...@li...]On Behalf Of =
Phil
> Daintree
> Sent: Sunday, April 18, 2004 04:38
> To: web...@li...
> Subject: [Web-erp-developers] Re: Desktop manuals
>=20
>=20
> Hi Danie,
>=20
> > How do I check out a specific version from CVS and then run a diff =
on
> > that version. Specifically I am making the changes against 2.8 and =
want
> > to do a diff against that so I can send the diffs to you as I go =
along.
> > It should be easier to review than everything at once.
>=20
> The CVS always contains the very latest scripts I always update=20
> as I make any=20
> mods. Don't forget that Jessie is working on a substanital effort=20
> to give us=20
> serialised stock, which she is about to send me scripts for. So=20
> if you have=20
> major mods may be best to hold off till I get Jessie's stuff=20
> committed to the=20
> CVS.
>=20
>=20
> Phil
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> =
administration.http://ads.osdn.com/?ad_id=3D1470&alloc_id=3D3638&op=3Dcli=
ck
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>=20

[Web-erp-developers] Re: Desktop manuals

[Phil D. <ph...@du...>]

Hi Danie,

> How do I check out a specific version from CVS and then run a diff on
> that version. Specifically I am making the changes against 2.8 and want
> to do a diff against that so I can send the diffs to you as I go along.
> It should be easier to review than everything at once.

The CVS always contains the very latest scripts I always update as I make any 
mods. Don't forget that Jessie is working on a substanital effort to give us 
serialised stock, which she is about to send me scripts for. So if you have 
major mods may be best to hold off till I get Jessie's stuff committed to the 
CVS.


Phil

Re: [Web-erp-developers] Reverse GRNs of Serialised stock

[Daintree <p.d...@pa...>]

> what about, if for nothing else but
> kicks, an interface to let 2 web-erp's interact w/ each other? It could be
> really useful when wholesaling and dealing w/ large(ish)distributors,
their
> large(ish) customers, sales agents, etc.
> It might also suffice to create another reporting template people could
> create to export data in any which way they wanted - just aim to make it
> capable of defining some of the edi related message formats?
> blabbering now...sleep, then code.
>

Yeah that would be ideal - but I have only got to sending invoices and am
working on recieving sales orders. Not got to sending purchase orders yet -
or receiving purchase invoices. Think I will leave it at sending orders.

Sleep tite!

Phil

RE: [Web-erp-developers] Reverse GRNs of Serialised stock

[jesse <je...@st...>]

Ok, I'll use my discretion and get on with it. I hope I am close - not a
problem with coding, but rather coding time. Worked 15 hrs today and didn't
get to write a line of code (like, for anything), but the weekend is here
too. I'll try to get you at least a piece this weekend to see what I'm
doing.

Correct on Chris' script and most others like it...

EDI and the such can be a pain...and I've found that having a use really
helps encourage implementation :)  what about, if for nothing else but
kicks, an interface to let 2 web-erp's interact w/ each other? It could be
really useful when wholesaling and dealing w/ large(ish)distributors, their
large(ish) customers, sales agents, etc.
It might also suffice to create another reporting template people could
create to export data in any which way they wanted - just aim to make it
capable of defining some of the edi related message formats?
blabbering now...sleep, then code.

> -----Original Message-----
> From: web...@li...
> [mailto:web...@li...]On Behalf Of
> Daintree
> Sent: Friday, April 16, 2004 02:54
> To: web...@li...
> Subject: Re: [Web-erp-developers] Reverse GRNs of Serialised stock
>
>
> Hard for me to comment really since I need to get hands dirty
> with the code.
> Sounds like I might have to arrange for a full weekend's battle
> when you are
> ready to let me loose on the code.
>
> Chris has done some work on a simple location movements script, I am
> guessing that this stuff will work ok anyway since you are looking at a
> level down from the stock movement in your work?
>
> Are you close. I can't get inspired to finsih off EDIOrders - I
> thought our
> business might use this but there is no commercial driver now and
> no way to
> ensure it works - live testing- so not quite so motivated to do it.
>
> Phil
> ----- Original Message -----
> From: "jesse" <je...@st...>
> To: <web...@li...>
> Sent: Friday, April 16, 2004 6:18 PM
> Subject: RE: [Web-erp-developers] Reverse GRNs of Serialised stock
>
>
> > -- I just realised this did not get sent (over 24 hrs ago).
> Explains why I
> > never saw a response ;)
> >
> >
> > > > => item must have been received against the PO we are reversing
> > > pieces of
> > >
> > > Why do you need to have the PO specified - this is held
> against the GRN?
> >
> > It is already specified, unless there's another way to get at doing a
> > Reverse GRN. When you process the reversal, PurchOrderDetails are being
> > updated to remove X Qty as being received and ensuring it is marked such
> > that has additional receiving necessary. Also, if I did not
> require that,
> I
> > would potentially have to find and update, say 10 different POs when
> > receiving items. Maybe necessary, though, as it may be
> difficult for some
> to
> > find where items they are reversing came from.
> >
> > > > => any conditions a specialised stock module checks
> > > > + Delete records from Item-specific tables (only affects you if you
> have
> > > specialised stock modules)
> > >
> > > You got me here - cant keep up - is this some new
> functionality module??
> >
> > Yes. I have them working... it is a pretty customizable setup (some
> assembly
> > required :) ) , but also easily (and I'd say by default) hidden...it
> > encompasses the standard serialised item (ie, you just have a
> single piece
> > of info - the serialno - that you want to store/maintain for the item),
> but
> > you wouldn't notice (from the UI, at least). You'll see soon - I don't
> think
> > it will interfere with anything. And I should have a couple examples.
> >
> > > >
> > > > then, either:
> > > >
> > > > 1) Change/Invalidate SerialNo in StockItems Records.
> > > > a) Change Serial in All affected StockItem records to something
> > > bogus like
> > > 'XXXXXXXX' or just ''
> > > > b) Update StockItems w/ new StockMove No. for each.
> > > > c) Create StockItemMove for tracking (StockItemNo,
> > > OldStockMoveNo, New/Eff
> > > StockMoveNo)
> > > >
> > >
> > > > 2) Delete Item history
> > > > a) Delete records from StockItems
> > > > c) Delete StockItemMoves
> > > >
> > > >
> > >
> > > What about
> > > 3) Hybrid record reversal and delete stock item
> > > a)  Delete records from StockItems
> > > b) Create StockItemMove of a GRN reversal type for tracking
> (StockItemNo,
> > > OldStockMoveNo, New/Eff StockMoveNo)
> > >
> > > For security purposes we need some record of what happened
> but there is
> no
> > > point having the stockitem existing if its gone. Just 2c
> >
> > Yes... the only problem (and it may just be in table struct) with 3b is
> that
> > StockItemNo and SerialNo are not the same. I keyed StockItems on an Int
> > Identity (StockItemNo) and allowed SerialNo to be just any var char with
> no
> > restrictions - I've just realized that I think I can leave the StockItem
> > entries in for that fact exactly. I figure when I have to select
> StockItems,
> > I'll always be coming through StockMoves, and you just wouldn't allow
> > someone to fullfill an order with an item you reversed, huh? So you're
> > hybrid works with out A which is the best scenario.
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

Re: [Web-erp-developers] Reverse GRNs of Serialised stock

[Daintree <p.d...@pa...>]

Hard for me to comment really since I need to get hands dirty with the code.
Sounds like I might have to arrange for a full weekend's battle when you are
ready to let me loose on the code.

Chris has done some work on a simple location movements script, I am
guessing that this stuff will work ok anyway since you are looking at a
level down from the stock movement in your work?

Are you close. I can't get inspired to finsih off EDIOrders - I thought our
business might use this but there is no commercial driver now and no way to
ensure it works - live testing- so not quite so motivated to do it.

Phil
----- Original Message -----
From: "jesse" <je...@st...>
To: <web...@li...>
Sent: Friday, April 16, 2004 6:18 PM
Subject: RE: [Web-erp-developers] Reverse GRNs of Serialised stock


> -- I just realised this did not get sent (over 24 hrs ago). Explains why I
> never saw a response ;)
>
>
> > > => item must have been received against the PO we are reversing
> > pieces of
> >
> > Why do you need to have the PO specified - this is held against the GRN?
>
> It is already specified, unless there's another way to get at doing a
> Reverse GRN. When you process the reversal, PurchOrderDetails are being
> updated to remove X Qty as being received and ensuring it is marked such
> that has additional receiving necessary. Also, if I did not require that,
I
> would potentially have to find and update, say 10 different POs when
> receiving items. Maybe necessary, though, as it may be difficult for some
to
> find where items they are reversing came from.
>
> > > => any conditions a specialised stock module checks
> > > + Delete records from Item-specific tables (only affects you if you
have
> > specialised stock modules)
> >
> > You got me here - cant keep up - is this some new functionality module??
>
> Yes. I have them working... it is a pretty customizable setup (some
assembly
> required :) ) , but also easily (and I'd say by default) hidden...it
> encompasses the standard serialised item (ie, you just have a single piece
> of info - the serialno - that you want to store/maintain for the item),
but
> you wouldn't notice (from the UI, at least). You'll see soon - I don't
think
> it will interfere with anything. And I should have a couple examples.
>
> > >
> > > then, either:
> > >
> > > 1) Change/Invalidate SerialNo in StockItems Records.
> > > a) Change Serial in All affected StockItem records to something
> > bogus like
> > 'XXXXXXXX' or just ''
> > > b) Update StockItems w/ new StockMove No. for each.
> > > c) Create StockItemMove for tracking (StockItemNo,
> > OldStockMoveNo, New/Eff
> > StockMoveNo)
> > >
> >
> > > 2) Delete Item history
> > > a) Delete records from StockItems
> > > c) Delete StockItemMoves
> > >
> > >
> >
> > What about
> > 3) Hybrid record reversal and delete stock item
> > a)  Delete records from StockItems
> > b) Create StockItemMove of a GRN reversal type for tracking
(StockItemNo,
> > OldStockMoveNo, New/Eff StockMoveNo)
> >
> > For security purposes we need some record of what happened but there is
no
> > point having the stockitem existing if its gone. Just 2c
>
> Yes... the only problem (and it may just be in table struct) with 3b is
that
> StockItemNo and SerialNo are not the same. I keyed StockItems on an Int
> Identity (StockItemNo) and allowed SerialNo to be just any var char with
no
> restrictions - I've just realized that I think I can leave the StockItem
> entries in for that fact exactly. I figure when I have to select
StockItems,
> I'll always be coming through StockMoves, and you just wouldn't allow
> someone to fullfill an order with an item you reversed, huh? So you're
> hybrid works with out A which is the best scenario.
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

RE: [Web-erp-developers] Reverse GRNs of Serialised stock

[jesse <je...@st...>]

-- I just realised this did not get sent (over 24 hrs ago). Explains why I
never saw a response ;)


> > => item must have been received against the PO we are reversing
> pieces of
>
> Why do you need to have the PO specified - this is held against the GRN?

It is already specified, unless there's another way to get at doing a
Reverse GRN. When you process the reversal, PurchOrderDetails are being
updated to remove X Qty as being received and ensuring it is marked such
that has additional receiving necessary. Also, if I did not require that, I
would potentially have to find and update, say 10 different POs when
receiving items. Maybe necessary, though, as it may be difficult for some to
find where items they are reversing came from.

> > => any conditions a specialised stock module checks
> > + Delete records from Item-specific tables (only affects you if you have
> specialised stock modules)
>
> You got me here - cant keep up - is this some new functionality module??

Yes. I have them working... it is a pretty customizable setup (some assembly
required :) ) , but also easily (and I'd say by default) hidden...it
encompasses the standard serialised item (ie, you just have a single piece
of info - the serialno - that you want to store/maintain for the item), but
you wouldn't notice (from the UI, at least). You'll see soon - I don't think
it will interfere with anything. And I should have a couple examples.

> >
> > then, either:
> >
> > 1) Change/Invalidate SerialNo in StockItems Records.
> > a) Change Serial in All affected StockItem records to something
> bogus like
> 'XXXXXXXX' or just ''
> > b) Update StockItems w/ new StockMove No. for each.
> > c) Create StockItemMove for tracking (StockItemNo,
> OldStockMoveNo, New/Eff
> StockMoveNo)
> >
>
> > 2) Delete Item history
> > a) Delete records from StockItems
> > c) Delete StockItemMoves
> >
> >
>
> What about
> 3) Hybrid record reversal and delete stock item
> a)  Delete records from StockItems
> b) Create StockItemMove of a GRN reversal type for tracking (StockItemNo,
> OldStockMoveNo, New/Eff StockMoveNo)
>
> For security purposes we need some record of what happened but there is no
> point having the stockitem existing if its gone. Just 2c

Yes... the only problem (and it may just be in table struct) with 3b is that
StockItemNo and SerialNo are not the same. I keyed StockItems on an Int
Identity (StockItemNo) and allowed SerialNo to be just any var char with no
restrictions - I've just realized that I think I can leave the StockItem
entries in for that fact exactly. I figure when I have to select StockItems,
I'll always be coming through StockMoves, and you just wouldn't allow
someone to fullfill an order with an item you reversed, huh? So you're
hybrid works with out A which is the best scenario.

Re: [Web-erp-developers] Reverse GRNs of Serialised stock

[Phil D. <ph...@du...>]

> When a Serialised Item has a Reverse GRN processed, here are the options
I've come up with:
>
> Under both ways:
> + all existing code is executed.
> + Additional conditions must be met
> => can only reverse GRN for stock that has not been sold (ie, current
StockMove for item cannot be Invoice type).

Makes sense.

> => item must have been received against the PO we are reversing pieces of

Why do you need to have the PO specified - this is held against the GRN?

> => any conditions a specialised stock module checks
> + Delete records from Item-specific tables (only affects you if you have
specialised stock modules)

You got me here - cant keep up - is this some new functionality module??

>
> then, either:
>
> 1) Change/Invalidate SerialNo in StockItems Records.
> a) Change Serial in All affected StockItem records to something bogus like
'XXXXXXXX' or just ''
> b) Update StockItems w/ new StockMove No. for each.
> c) Create StockItemMove for tracking (StockItemNo, OldStockMoveNo, New/Eff
StockMoveNo)
>

> 2) Delete Item history
> a) Delete records from StockItems
> c) Delete StockItemMoves
>
>

What about
3) Hybrid record reversal and delete stock item
a)  Delete records from StockItems
b) Create StockItemMove of a GRN reversal type for tracking (StockItemNo,
OldStockMoveNo, New/Eff StockMoveNo)

For security purposes we need some record of what happened but there is no
point having the stockitem existing if its gone. Just 2c

> I'm keen on using the second method b/c it creates a lesser chance for
problems in the future if the Items being reversed are actually received
later on. I figure at face value most would balk since I'm deleting, but I
really don't see much point in maintaining these records.

I agree - as long as there is a record of it having existed in the stock
movements.


Phil

[Web-erp-developers] Reverse GRNs of Serialised stock

[Jesse P. <jes...@st...>]

When a Serialised Item has a Reverse GRN processed, here are the options =
I've come up with:

Under both ways:=20
+ all existing code is executed.
+ Additional conditions must be met=20
	=3D> can only reverse GRN for stock that has not been sold (ie, current =
StockMove for item cannot be Invoice type).=20
	=3D> item must have been received against the PO we are reversing =
pieces of
	=3D> any conditions a specialised stock module checks
+ Delete records from Item-specific tables (only affects you if you have =
specialised stock modules)

then, either:

1) Change/Invalidate SerialNo in StockItems Records.
	a) Change Serial in All affected StockItem records to something bogus =
like 'XXXXXXXX' or just ''
	b) Update StockItems w/ new StockMove No. for each.
	c) Create StockItemMove for tracking (StockItemNo, OldStockMoveNo, =
New/Eff StockMoveNo)

2) Delete Item history
	a) Delete records from StockItems
	c) Delete StockItemMoves


I'm keen on using the second method b/c it creates a lesser chance for =
problems in the future if the Items being reversed are actually received =
later on. I figure at face value most would balk since I'm deleting, but =
I really don't see much point in maintaining these records.

Thoughts from others? Am I missing something or forgetting about =
something?

[Web-erp-developers] Away

[Phil D. <ph...@du...>]

I will be in the US for a week not answering emails - this is nothing
personal. My treo wont work in the US. So if anyone else knows the answer to
a posting then please do chip in.

Thanks
Phil

[Web-erp-developers] Re: Re-Ordering

[Phil D. <ph...@du...>]

I have been going through the re-ordering problem with a couple of =
knowledgeable friends. We were looking at it from the industrial and =
manufacturing side. It seems that the system is even more complicated =
than I originally thought.

>We came up with the following issues :
>1) If a stock Item has a reorder level and has a BOM all sub level =
items should have at least the same reorder level multiplied by quantity =
required. Enforcing this >will solve the problem later when we need to =
determine which items need re-ordering.

But wait there's more ..... what about uneven stock levels of the =
components - the stock of nails comes in boxes of 50,000 they are in the =
BOM for coffins x 20 peices per coffin. 2000x600 sheet of ply is also in =
the BOM x 2. and the warehouse is not that big to hold 50,000/20 =3D =
2,500 x 2 =3D 5,000 sheets of ply. Nor would the cash required for this =
much want to be held. It makes more sense to do the ordering based on =
EOQ and reorder quantity by each item - assembly items excepted - but =
the components of assembly items NOT excepted.

Bottom line .... everyone will want to do it differently - just my 2c. I =
do it using the planning report I wrote for the purpose.


>2) ???Should customer orders be deducted from LocStock Available =
quantity or is it already deducted. If not they should be included in =
the calculation process. >Or better yet we should add the field to =
LocStock although I am pretty sure I saw it there.

The quantity on order from customers -demand should be deducted off the =
stock on hand in the calculation - this is how it appears on the stock =
status inquiry -but this is derived from the orderdetails table and is =
not held as a quantity in the LocStock table. If we add the field to =
LocStock this needs on going maintainance better just to re-caclulate it =
when we need it. .... in my view.


>3) Some variable in config.php which will state how long after =
non-delivery a supplier order should still be considered as stock =
already ordered and therefore >included/discounted from re-ordering =
calculations

>This will then solve the problem for most retail shops when re-ordering =
is calculated.


I can't follow this logic.


>For Industrial companies we came up with the following issues.
>When placing an order for manufacturing is become important to =
calculate a delivery date for each order line containing manufactured =
item. We will also need to >distinguish between different delivery =
times. Also project planning/management will probably be a good way to =
get more accurate delivery dates and in order to >get that we will need =
basic human resources with employee skills / job descriptions, as well =
as availability, leave and sick leave.

Manufacturing Materials Requirements Planning - (MRP) is quite another =
long and tortuous story. However, one that I am eager to get stuck into. =
There is a whole area of manuafacturing that web-erp does not do ...yet. =
Work Orders, issues, receipts, variances, labour routings - how long it =
should take to make something. Cost roll ups from bills of materials. =
Master Production Schedule, Capacity buckets and capacity planning. So =
there is plenty to work on!

Project planning I see as outside the scope of the system - there are =
some pretty good ones about - dotproject looks good to me.


>Issues discussed around additional fields, are
>1) latest delivery date with customer order item line, would be used =
for project planning /  management.

There is currently only one delivery date per order - why not a new =
order when good to be delivered on different days?


>2) employee, leave, sick leave, job-sheet, customer importance/priority =
in terms of project planing and emergency re-planning in event of sick =
leave interfering or >breakdown of machinery.

>3) employee skill/job specification with priority and rate / hour which =
will be used to increase production speed and decrease cost, also =
minimum hours required >per month/week. Management substitutions must be =
allowed for projects.

>4) sales cost calculation should be available either on worst possible =
normal operation and average cost per hour, or average time per =
job/operation at average >cost.
>5) true cost is calculated with each job, average cost could then be =
maintained.

There is no HR stuff in the system at all - HR is different for each =
country although there may be some commonality. I think there is a =
danger in one system claiming to cover all bases. I would rather HR =
payroll be outside the scope of the system. Sick leave, holiday are part =
of a payroll system.

What you are considering is part of MRP and master production =
scheduling. There are 1000s of reasons to put one job (work order) ahead =
of another - this is the most complex part of manufacturing and needs =
very careful design and a whole lot of coding. I will confine my work to =
the work centre level though avoiding individual employees.

>This seems like a very tall order.

No doubt about that! We are some considerable way from being capable of =
offering manufacturing functionality to clients.



>I have been concentrating on getting WebERP 2.8 in a position where I =
can give it to a prospect without him asking me what a field, function =
or report is for when >it is not as yet used or required by him. When I =
do disable functionality or fields I disable them with a variable flag =
in the config.php file. I have for instance >disabled account checking, =
and re-ordering. I am also in the process of adding some quick links =
such as add new customer when order entry does not return a >customer =
for search criteria. I still need to add security check to this so it is =
only available for the correct security level. I will continue to do =
both the variable >adding / function disabling and the Manual writing =
process.=20

>I have already seen an advantage for this in customer configuration =
where he does not require full functionality. A simple installation =
wizard could enable and >disable these variables based on requirements. =
We could even incorporate them into the database at a later stage =
although I think this might slow things down.

There is some abiliity to tailor screens based on login from the user =
set up screen - individual clients could have links removed as =
necessary.

Phil

RE: [Web-erp-developers] Serialised stock - how to code this up

[Jesse P. <jes...@st...>]

Ok. Sounds like what I have here will work, but that certain views will =
be required to provide links to some easy way to track items back...

jesse

> -----Original Message-----
> From: Phil Daintree [mailto:ph...@du...]
> Sent: Thursday, March 25, 2004 17:32
> To: web...@li...
> Subject: Re: [Web-erp-developers] Serialised stock - how to=20
> code this up
>=20
>=20
> Typically a bundle of steel has a tracking bundle number as=20
> given to it by
> the vendor. Quality information would be available and=20
> certification by
> bundle reference. A manufacturer would want to know which=20
> steel went into
> its product so if there were some particularly rusty products=20
> produced or
> the carbon deposits causing structural flaws in the steel=20
> then these could
> be tracked to the bundle that they came from.
>=20
> Similarly, with cloth - a manufacturer receiving returned=20
> garments would
> want to know which roll of cloth this came from - so that the cloth
> manufacturer could be held accountable. Or when cutting the=20
> cloth to make
> garments a flaw is revealed - the cloth mill will need to=20
> know the roll
> reference they gave the roll when it was made to have a look=20
> at its quality
> processes.
>=20
> Food is the same - A batch of cheese with razor blades in it=20
> would need to
> be withdrawn and the manufacturer would need to issue a recall off the
> shelves immediately for batch xyz to all retail outlets - and=20
> need to be
> able to trace where this batch was sold to.
>=20
> Phil
>=20
> ----- Original Message -----
> From: "Jesse Peterson" <jes...@st...>
> To: <web...@li...>
> Sent: Friday, March 26, 2004 10:00 AM
> Subject: RE: [Web-erp-developers] Serialised stock - how to=20
> code this up
>=20
>=20
> > comments down below....
> >
> > > > > > > - how do we display all the serial numbers of items on
> > > > > hand at any one
> > > > > time?
> > > > > > >
> > > > > > >
> > > > > > I realized this a while ago as well. All Items will have
> > > > > their current
> > > > > > StockMove associated with them in their table - upkeep
> > > > > necessary during
> > > > > > adds/moves/etc. I figure to select on-hand items the base
> > > > > criteria will
> > > > > > be based on selecting a set of StockMoves with
> > > appropriate SysTypes,
> > > > > > then selecting Items still tagged with those StockMoves. Do
> > > > > you know the
> > > > > > SysTypes necessary without me digging around to find them?
> > > > > I don't understand this. The SysTypes table refers to=20
> the various
> > > > > transaction types - not all of which create stock movements.
> > > > It looks to me that finding all SerialisedStock-on-Hand can
> > > > be done by:
> > > > Get all StockMoves where Type=3D25, then get all
> > > > SerialisedItems with those
> > > > StockMoveNo's. Limits could easily be put to select all for
> > > > just a Location,
> > > > that a Customer have, etc... and just require determining
> > > > which Types in
> > > > StockMoves reflect the Stock you want. Make sense? The last
> > > > part is where i
> > > > was wondering about which ones should be checked.
> > >
> > > But the goods received stock movement has the total qty
> > > received - not the
> > > qty of the batch received? Even when linked to
> > > SerialisedStockMoves this
> > > table has no qty in it either?
> > > I just don't see how this system will track how many of each
> > > batch you have
> > > left.
> >
> > I'm assuming a Batch will be a set of like StockMaster=20
> types and using
> StockMoves.Bundle is an adequate tracking no. for each batch:
> > Let's see... GoodsReceived would create a StockMove /w the=20
> Qty received -
> if it is controlled, the Receiver would be required to enter=20
> a BundleId
> (that would go in StockMoves.Bundle). As more goods were=20
> received, we could
> allow it to be batched together with the previous one or=20
> given/require a new
> Bundle
> > So then, to query a Bundle, you'd pick all StockMoves=20
> On-Hand (type=3D25..
> more?), Sum their Qty for a total number of items, then sum=20
> the number of
> SerialisedItem moves where those StockMove(s) are set as the=20
> OrigStockMoveNo
> . That could be a little slow, but I think it will end up=20
> working out ok.
> For consistency (in that quantities always come from=20
> StockMoves), it may be
> better to retrieve the distinct NewStockMoves for the=20
> OldStockMoves and pull
> their Qty out of StockMoves... that would give us a Total and=20
> the number
> Removed from it, which obiously is enough to show us the=20
> number available.
> >
> > Tangent on BundleIds: do you think these should be=20
> auto-generated? I was
> thinking treat Bundle as it if were a hex number and just=20
> keep adding 1...
> so they'd go 00000001,...,00000009,0000000A,..0000000F,00000010 - or a
> 'base26'(A-Z) or 'base36'(0-9,A-Z) scheme to accomodate even=20
> more unique
> Bundle #s (I have some functions that count/convert things like that
> already)...
> >
> > You may want/need to explain to me how batches *need* to=20
> work so I can
> take a fresh look at that...
> >
> > > >
> > > > > Individual
> > > > > stockmoves contain the total balance of all stock in the
> > > > > location in the
> > > > > newqoh field if I understand your logic so the quantity in
> > > > > the batch is
> > > > > unknown. or do you mean to re-calcuate the quantity remaining
> > > > > on hand in the
> > > > > ControlledStockMoves record and just pick up the last
> > > > The Qty for a batch of SerialisedItems would simply be the
> > > Qty in the
> > > StockMove they refer to. We may need to talk about exactly
> > > how to account
> > > for StockMove Qty's, but it looks like the same as above,
> > > where certain
> > > Types can be referenced to get the total(s) we are looking for.
> > > >
> > > > > Perhaps summing the qty ins and outs in the
> > > > > ControlledStockMoves - this
> > > > > could be a substantial exercise with your data volumes?
> > > > Yes, it could. I did not intend on needing to do this.
> > > >
> > > > > Not using the LocStock table seems nice but what about items
> > > > > that need to
> > > > > mature before they are sold - so they need to be held or
> > > products -
> > > > > serialised items that must be sold before a given date as per
> > > > > Danie's note.
> > > > > I think if we are doing it, it would be a shame to limit
> > > the scope to
> > > > > exclude this.
> > > > > Are we looking at another table to hold serialised quantites
> > > > > on hand and
> > > > > these variables. The table being maintained like LocStock but
> > > > > at the serial
> > > > > number level - the total of serialised items always agreeing
> > > > > to the LocStock
> > > > > table QOH. There seems a disturbing amount of double up
> > > with LocStock
> > > > > though.
> > > > Agreed, I wouldn't want to double up like that... I read Danie's
> > > > requests - I've never really thought about having to do that,
> > > > so I will...
> > > > Right now, I'm thinking a StockMaster field for=20
> SellAfterDays and
> > > > SellByDays, and then determining Stock status (as above, by
> > > > Type) should
> > > > just be an exercise of totalling StockMoves and examining
> > > > date differences.
> > > What do you think?
> > >
> > > Clunky and processing intensive .... you asked what I thought :-)
> >
> > Ok :)... possibly so. If I do it now, I'll probably do it=20
> that way unless
> I can work in another way.
> >
> >
> > > I'm looking forward to seeing the code - since this is a
> > > substantial project
> > > I will confine my activities over the next 2 weeks to the EDI
> > > scripts and
> > > suggest Danie and Dick do similarly - to give you a free=20
> run without
> > > worrying about other changes in the scripts.
> >
> > That would be very nice. I'll keep my eye out in case=20
> changes are needed,
> though, so that you guys aren't completely strapped by me=20
> working on this.
> My hope is to have most of this done (assuming I don't hit=20
> any big snags)
> within a week or so - that's probably a little=20
> over-ambitious, though. The
> first thing I may end up sending back is reworkings of some=20
> sections of code
> to allow us to class out the StockItems, at least as a data=20
> structure...
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President=20
> and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id638&op=CCk
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> =
administration.http://ads.osdn.com/?ad_id=3D1470&alloc_id=3D3638&op=3Dcli=
ck
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>=20

Re: [Web-erp-developers] Serialised stock - how to code this up

[Phil D. <ph...@du...>]

Typically a bundle of steel has a tracking bundle number as given to it by
the vendor. Quality information would be available and certification by
bundle reference. A manufacturer would want to know which steel went into
its product so if there were some particularly rusty products produced or
the carbon deposits causing structural flaws in the steel then these could
be tracked to the bundle that they came from.

Similarly, with cloth - a manufacturer receiving returned garments would
want to know which roll of cloth this came from - so that the cloth
manufacturer could be held accountable. Or when cutting the cloth to make
garments a flaw is revealed - the cloth mill will need to know the roll
reference they gave the roll when it was made to have a look at its quality
processes.

Food is the same - A batch of cheese with razor blades in it would need to
be withdrawn and the manufacturer would need to issue a recall off the
shelves immediately for batch xyz to all retail outlets - and need to be
able to trace where this batch was sold to.

Phil

----- Original Message -----
From: "Jesse Peterson" <jes...@st...>
To: <web...@li...>
Sent: Friday, March 26, 2004 10:00 AM
Subject: RE: [Web-erp-developers] Serialised stock - how to code this up


> comments down below....
>
> > > > > > - how do we display all the serial numbers of items on
> > > > hand at any one
> > > > time?
> > > > > >
> > > > > >
> > > > > I realized this a while ago as well. All Items will have
> > > > their current
> > > > > StockMove associated with them in their table - upkeep
> > > > necessary during
> > > > > adds/moves/etc. I figure to select on-hand items the base
> > > > criteria will
> > > > > be based on selecting a set of StockMoves with
> > appropriate SysTypes,
> > > > > then selecting Items still tagged with those StockMoves. Do
> > > > you know the
> > > > > SysTypes necessary without me digging around to find them?
> > > > I don't understand this. The SysTypes table refers to the various
> > > > transaction types - not all of which create stock movements.
> > > It looks to me that finding all SerialisedStock-on-Hand can
> > > be done by:
> > > Get all StockMoves where Type=25, then get all
> > > SerialisedItems with those
> > > StockMoveNo's. Limits could easily be put to select all for
> > > just a Location,
> > > that a Customer have, etc... and just require determining
> > > which Types in
> > > StockMoves reflect the Stock you want. Make sense? The last
> > > part is where i
> > > was wondering about which ones should be checked.
> >
> > But the goods received stock movement has the total qty
> > received - not the
> > qty of the batch received? Even when linked to
> > SerialisedStockMoves this
> > table has no qty in it either?
> > I just don't see how this system will track how many of each
> > batch you have
> > left.
>
> I'm assuming a Batch will be a set of like StockMaster types and using
StockMoves.Bundle is an adequate tracking no. for each batch:
> Let's see... GoodsReceived would create a StockMove /w the Qty received -
if it is controlled, the Receiver would be required to enter a BundleId
(that would go in StockMoves.Bundle). As more goods were received, we could
allow it to be batched together with the previous one or given/require a new
Bundle
> So then, to query a Bundle, you'd pick all StockMoves On-Hand (type=25..
more?), Sum their Qty for a total number of items, then sum the number of
SerialisedItem moves where those StockMove(s) are set as the OrigStockMoveNo
. That could be a little slow, but I think it will end up working out ok.
For consistency (in that quantities always come from StockMoves), it may be
better to retrieve the distinct NewStockMoves for the OldStockMoves and pull
their Qty out of StockMoves... that would give us a Total and the number
Removed from it, which obiously is enough to show us the number available.
>
> Tangent on BundleIds: do you think these should be auto-generated? I was
thinking treat Bundle as it if were a hex number and just keep adding 1...
so they'd go 00000001,...,00000009,0000000A,..0000000F,00000010 - or a
'base26'(A-Z) or 'base36'(0-9,A-Z) scheme to accomodate even more unique
Bundle #s (I have some functions that count/convert things like that
already)...
>
> You may want/need to explain to me how batches *need* to work so I can
take a fresh look at that...
>
> > >
> > > > Individual
> > > > stockmoves contain the total balance of all stock in the
> > > > location in the
> > > > newqoh field if I understand your logic so the quantity in
> > > > the batch is
> > > > unknown. or do you mean to re-calcuate the quantity remaining
> > > > on hand in the
> > > > ControlledStockMoves record and just pick up the last
> > > The Qty for a batch of SerialisedItems would simply be the
> > Qty in the
> > StockMove they refer to. We may need to talk about exactly
> > how to account
> > for StockMove Qty's, but it looks like the same as above,
> > where certain
> > Types can be referenced to get the total(s) we are looking for.
> > >
> > > > Perhaps summing the qty ins and outs in the
> > > > ControlledStockMoves - this
> > > > could be a substantial exercise with your data volumes?
> > > Yes, it could. I did not intend on needing to do this.
> > >
> > > > Not using the LocStock table seems nice but what about items
> > > > that need to
> > > > mature before they are sold - so they need to be held or
> > products -
> > > > serialised items that must be sold before a given date as per
> > > > Danie's note.
> > > > I think if we are doing it, it would be a shame to limit
> > the scope to
> > > > exclude this.
> > > > Are we looking at another table to hold serialised quantites
> > > > on hand and
> > > > these variables. The table being maintained like LocStock but
> > > > at the serial
> > > > number level - the total of serialised items always agreeing
> > > > to the LocStock
> > > > table QOH. There seems a disturbing amount of double up
> > with LocStock
> > > > though.
> > > Agreed, I wouldn't want to double up like that... I read Danie's
> > > requests - I've never really thought about having to do that,
> > > so I will...
> > > Right now, I'm thinking a StockMaster field for SellAfterDays and
> > > SellByDays, and then determining Stock status (as above, by
> > > Type) should
> > > just be an exercise of totalling StockMoves and examining
> > > date differences.
> > What do you think?
> >
> > Clunky and processing intensive .... you asked what I thought :-)
>
> Ok :)... possibly so. If I do it now, I'll probably do it that way unless
I can work in another way.
>
>
> > I'm looking forward to seeing the code - since this is a
> > substantial project
> > I will confine my activities over the next 2 weeks to the EDI
> > scripts and
> > suggest Danie and Dick do similarly - to give you a free run without
> > worrying about other changes in the scripts.
>
> That would be very nice. I'll keep my eye out in case changes are needed,
though, so that you guys aren't completely strapped by me working on this.
My hope is to have most of this done (assuming I don't hit any big snags)
within a week or so - that's probably a little over-ambitious, though. The
first thing I may end up sending back is reworkings of some sections of code
to allow us to class out the StockItems, at least as a data structure...
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id70&alloc_id638&opÌk
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

RE: [Web-erp-developers] Serialised stock - how to code this up

[Jesse P. <jes...@st...>]

comments down below....

> > > > > - how do we display all the serial numbers of items on
> > > hand at any one
> > > time?
> > > > >
> > > > >
> > > > I realized this a while ago as well. All Items will have
> > > their current
> > > > StockMove associated with them in their table - upkeep
> > > necessary during
> > > > adds/moves/etc. I figure to select on-hand items the base
> > > criteria will
> > > > be based on selecting a set of StockMoves with=20
> appropriate SysTypes,
> > > > then selecting Items still tagged with those StockMoves. Do
> > > you know the
> > > > SysTypes necessary without me digging around to find them?
> > > I don't understand this. The SysTypes table refers to the various
> > > transaction types - not all of which create stock movements.
> > It looks to me that finding all SerialisedStock-on-Hand can=20
> > be done by:
> > Get all StockMoves where Type=3D25, then get all=20
> > SerialisedItems with those
> > StockMoveNo's. Limits could easily be put to select all for=20
> > just a Location,
> > that a Customer have, etc... and just require determining=20
> > which Types in
> > StockMoves reflect the Stock you want. Make sense? The last=20
> > part is where i
> > was wondering about which ones should be checked.
>=20
> But the goods received stock movement has the total qty=20
> received - not the
> qty of the batch received? Even when linked to=20
> SerialisedStockMoves this
> table has no qty in it either?
> I just don't see how this system will track how many of each=20
> batch you have
> left.

I'm assuming a Batch will be a set of like StockMaster types and using =
StockMoves.Bundle is an adequate tracking no. for each batch:
Let's see... GoodsReceived would create a StockMove /w the Qty received =
- if it is controlled, the Receiver would be required to enter a =
BundleId (that would go in StockMoves.Bundle). As more goods were =
received, we could allow it to be batched together with the previous one =
or given/require a new Bundle=20
So then, to query a Bundle, you'd pick all StockMoves On-Hand =
(type=3D25.. more?), Sum their Qty for a total number of items, then sum =
the number of SerialisedItem moves where those StockMove(s) are set as =
the OrigStockMoveNo . That could be a little slow, but I think it will =
end up working out ok. For consistency (in that quantities always come =
from StockMoves), it may be better to retrieve the distinct =
NewStockMoves for the OldStockMoves and pull their Qty out of =
StockMoves... that would give us a Total and the number Removed from it, =
which obiously is enough to show us the number available.

Tangent on BundleIds: do you think these should be auto-generated? I was =
thinking treat Bundle as it if were a hex number and just keep adding =
1... so they'd go 00000001,...,00000009,0000000A,..0000000F,00000010 - =
or a 'base26'(A-Z) or 'base36'(0-9,A-Z) scheme to accomodate even more =
unique Bundle #s (I have some functions that count/convert things like =
that already)...

You may want/need to explain to me how batches *need* to work so I can =
take a fresh look at that...

> >
> > > Individual
> > > stockmoves contain the total balance of all stock in the
> > > location in the
> > > newqoh field if I understand your logic so the quantity in
> > > the batch is
> > > unknown. or do you mean to re-calcuate the quantity remaining
> > > on hand in the
> > > ControlledStockMoves record and just pick up the last
> > The Qty for a batch of SerialisedItems would simply be the=20
> Qty in the
> StockMove they refer to. We may need to talk about exactly=20
> how to account
> for StockMove Qty's, but it looks like the same as above,=20
> where certain
> Types can be referenced to get the total(s) we are looking for.
> >
> > > Perhaps summing the qty ins and outs in the
> > > ControlledStockMoves - this
> > > could be a substantial exercise with your data volumes?
> > Yes, it could. I did not intend on needing to do this.
> >
> > > Not using the LocStock table seems nice but what about items
> > > that need to
> > > mature before they are sold - so they need to be held or=20
> products -
> > > serialised items that must be sold before a given date as per
> > > Danie's note.
> > > I think if we are doing it, it would be a shame to limit=20
> the scope to
> > > exclude this.
> > > Are we looking at another table to hold serialised quantites
> > > on hand and
> > > these variables. The table being maintained like LocStock but
> > > at the serial
> > > number level - the total of serialised items always agreeing
> > > to the LocStock
> > > table QOH. There seems a disturbing amount of double up=20
> with LocStock
> > > though.
> > Agreed, I wouldn't want to double up like that... I read Danie's
> > requests - I've never really thought about having to do that,=20
> > so I will...
> > Right now, I'm thinking a StockMaster field for SellAfterDays and
> > SellByDays, and then determining Stock status (as above, by=20
> > Type) should
> > just be an exercise of totalling StockMoves and examining=20
> > date differences.
> What do you think?
>=20
> Clunky and processing intensive .... you asked what I thought :-)

Ok :)... possibly so. If I do it now, I'll probably do it that way =
unless I can work in another way.

=20
> I'm looking forward to seeing the code - since this is a=20
> substantial project
> I will confine my activities over the next 2 weeks to the EDI=20
> scripts and
> suggest Danie and Dick do similarly - to give you a free run without
> worrying about other changes in the scripts.

That would be very nice. I'll keep my eye out in case changes are =
needed, though, so that you guys aren't completely strapped by me =
working on this. My hope is to have most of this done (assuming I don't =
hit any big snags) within a week or so - that's probably a little =
over-ambitious, though. The first thing I may end up sending back is =
reworkings of some sections of code to allow us to class out the =
StockItems, at least as a data structure...

Re: [Web-erp-developers] Serialised stock - how to code this up

[Daintree <p.d...@pa...>]

> > > > - how do we display all the serial numbers of items on
> > hand at any one
> > time?
> > > >
> > > >
> > > I realized this a while ago as well. All Items will have
> > their current
> > > StockMove associated with them in their table - upkeep
> > necessary during
> > > adds/moves/etc. I figure to select on-hand items the base
> > criteria will
> > > be based on selecting a set of StockMoves with appropriate SysTypes,
> > > then selecting Items still tagged with those StockMoves. Do
> > you know the
> > > SysTypes necessary without me digging around to find them?
> > I don't understand this. The SysTypes table refers to the various
> > transaction types - not all of which create stock movements.
> It looks to me that finding all SerialisedStock-on-Hand can be done by:
Get all StockMoves where Type=25, then get all SerialisedItems with those
StockMoveNo's. Limits could easily be put to select all for just a Location,
that a Customer have, etc... and just require determining which Types in
StockMoves reflect the Stock you want. Make sense? The last part is where i
was wondering about which ones should be checked.

But the goods received stock movement has the total qty received - not the
qty of the batch received? Even when linked to SerialisedStockMoves this
table has no qty in it either?
I just don't see how this system will track how many of each batch you have
left.

>
> > Individual
> > stockmoves contain the total balance of all stock in the
> > location in the
> > newqoh field if I understand your logic so the quantity in
> > the batch is
> > unknown. or do you mean to re-calcuate the quantity remaining
> > on hand in the
> > ControlledStockMoves record and just pick up the last
> The Qty for a batch of SerialisedItems would simply be the Qty in the
StockMove they refer to. We may need to talk about exactly how to account
for StockMove Qty's, but it looks like the same as above, where certain
Types can be referenced to get the total(s) we are looking for.
>
> > Perhaps summing the qty ins and outs in the
> > ControlledStockMoves - this
> > could be a substantial exercise with your data volumes?
> Yes, it could. I did not intend on needing to do this.
>
> > Not using the LocStock table seems nice but what about items
> > that need to
> > mature before they are sold - so they need to be held or products -
> > serialised items that must be sold before a given date as per
> > Danie's note.
> > I think if we are doing it, it would be a shame to limit the scope to
> > exclude this.
> > Are we looking at another table to hold serialised quantites
> > on hand and
> > these variables. The table being maintained like LocStock but
> > at the serial
> > number level - the total of serialised items always agreeing
> > to the LocStock
> > table QOH. There seems a disturbing amount of double up with LocStock
> > though.
> Agreed, I wouldn't want to double up like that... I read Danie's
requests - I've never really thought about having to do that, so I will...
Right now, I'm thinking a StockMaster field for SellAfterDays and
SellByDays, and then determining Stock status (as above, by Type) should
just be an exercise of totalling StockMoves and examining date differences.
What do you think?

Clunky and processing intensive .... you asked what I thought :-)

I'm looking forward to seeing the code - since this is a substantial project
I will confine my activities over the next 2 weeks to the EDI scripts and
suggest Danie and Dick do similarly - to give you a free run without
worrying about other changes in the scripts.

Phil

RE: [Web-erp-developers] Serialised stock - how to code this up

[Jesse P. <jes...@st...>]

> > Like the StockItemMoves table I sent you in those diffs. It=20
> would not
> > hold a record for the inital move (in stockmoves and breaks ref.
> > integrity), and would hole the item#,orig_stkmove, and new_stkmove .
>=20
> This only contains moves of controlled items right with their=20
> reference and
> stockmove id - why would it not show the qty of the batch=20
> movement - where
> is this recorded?  I guess the StockMoves table shows the=20
> total movement of
> all serialised items per your logic. FromStkMoveNo and=20
> ToStkMoveNo - the
> stock movements of a serialised item will not be in a range .... will
> they???
> There's a one StockMoves.StockID to many=20
> StockItemMoves.StockID (I think
> some ref to controlled items would be preferable in the=20
> naming of the table)
> and the StockMoves.NewQOH would contain the sum of all=20
> serialised items in
> stock - not just one serial number item .... would it not??
Yes, ItemMoves would only shows moves between StockMoves. And yes, Qty =
should not be needed there as it should be correct in StockMoves. From & =
To are not ranges... probably OrigStkMove and NewStkMove are better =
names.
Yes, 1 StockMove to many StockItemMoves - and StockMoves.NewQOH works as =
it does today, which is as you mentioned.=20
Remember, though, Controlled & Serialised would be StockMaster fields. =
Serialised implies controlled, but Controlled does not imply Serialised. =
Controlled just forces someone to give some Ref number to each StockMove =
of a StockMaster type.
Possibly we call the tables SerialisedStock & SerialisedStockMoves ??

> OK I get it - that seems fine, just no qty control - probably=20
> ok - I prefer
> my method ;-)
I will keep this in mind and find another way to provide that =
functionality.

> > > - Don't understand you templatey idea?
> > >
> > Do you mean the above, my plan on how to allow easy extension of
> > stockitem details,
>=20
> yes that bit. I see the StockModules tables - I guess it=20
> would reduce a bit
> of input but would require additional sql to get the=20
> necessary info. I don't
> see it as necessary? The stock category could contain any other data
> relevant to the category.
It does require more sql queries to get all the data and maybe a little =
confusing. The thought is, that I actually need to store, say 5 other =
fields fo data for *each* item inventoried, and it will change between =
lots of items. So, StockCat won't do for that... . For example, for an =
IP Phone, I may call the MAC address my serial number and put the =
devices' admin user&pass, assigned phone number, and other Phone =
specific data in a separate table. The Module would act as the 'glue' =
between the SerialisedItems.SerialNo and IPPhones.MAC . The majority of =
items would not need that, though. For example, the GenericStockItem =
module would not have an extra table, but the code would allow it to =
work just like the other one.
Adding a new business specific Stock type now becomes a simple task of =
creating a StockModule and a Table to hold the data... the StockModule =
would also help control the display, making it easier to present data in =
a way that is more specific to a product than general enough to cover =
all of them.

> >
> > > - how do we display all the serial numbers of items on=20
> hand at any one
> time?
> > >
> > >
> > I realized this a while ago as well. All Items will have=20
> their current
> > StockMove associated with them in their table - upkeep=20
> necessary during
> > adds/moves/etc. I figure to select on-hand items the base=20
> criteria will
> > be based on selecting a set of StockMoves with appropriate SysTypes,
> > then selecting Items still tagged with those StockMoves. Do=20
> you know the
> > SysTypes necessary without me digging around to find them?
> I don't understand this. The SysTypes table refers to the various
> transaction types - not all of which create stock movements.=20
It looks to me that finding all SerialisedStock-on-Hand can be done by: =
Get all StockMoves where Type=3D25, then get all SerialisedItems with =
those StockMoveNo's. Limits could easily be put to select all for just a =
Location, that a Customer have, etc... and just require determining =
which Types in StockMoves reflect the Stock you want. Make sense? The =
last part is where i was wondering about which ones should be checked.

> Individual
> stockmoves contain the total balance of all stock in the=20
> location in the
> newqoh field if I understand your logic so the quantity in=20
> the batch is
> unknown. or do you mean to re-calcuate the quantity remaining=20
> on hand in the
> ControlledStockMoves record and just pick up the last
The Qty for a batch of SerialisedItems would simply be the Qty in the =
StockMove they refer to. We may need to talk about exactly how to =
account for StockMove Qty's, but it looks like the same as above, where =
certain Types can be referenced to get the total(s) we are looking for.

> Perhaps summing the qty ins and outs in the=20
> ControlledStockMoves - this
> could be a substantial exercise with your data volumes?
Yes, it could. I did not intend on needing to do this.
=20
> Not using the LocStock table seems nice but what about items=20
> that need to
> mature before they are sold - so they need to be held or products -
> serialised items that must be sold before a given date as per=20
> Danie's note.
> I think if we are doing it, it would be a shame to limit the scope to
> exclude this.
> Are we looking at another table to hold serialised quantites=20
> on hand and
> these variables. The table being maintained like LocStock but=20
> at the serial
> number level - the total of serialised items always agreeing=20
> to the LocStock
> table QOH. There seems a disturbing amount of double up with LocStock
> though.
Agreed, I wouldn't want to double up like that... I read Danie's =
requests - I've never really thought about having to do that, so I =
will... Right now, I'm thinking a StockMaster field for SellAfterDays =
and SellByDays, and then determining Stock status (as above, by Type) =
should just be an exercise of totalling StockMoves and examining date =
differences. What do you think?

> >
> > > - how do we display on invoices the serial numbers of=20
> items sold - if we
> > >wanted to ?
> > >
> > >
> > This can be an option specified in the StockMaster table, in
> > StockModules, or even at the time of printing/viewing the=20
> invoice, and a
> > combination of those...I also forsee situations like I have=20
> where I want
> > to invoice someone for 50,000 cards on a regular invoice (obviously
> > without serialised line items) and have the option to=20
> provide/retrieve
> > the invoice detail in some other format, say a csv list of serial
> > numbers and possibly other data that can be sent to a=20
> customer for their
> > own inventory requirements.
> >
>=20
> I am probably wasting your time with my inability to grasp=20
> the concepts.
> Sorry I just can't get the logic of what you are proposing, I=20
> fear it seems
> to be some way short of acheiving what you hope for. I think=20
> I should just
> leave you to it - give it a go - if it works ... and I do=20
> hope so ... I'll
> include it!
You apologize too much. :) I'm going to get to work on things... if you =
come up with any other concerns of how something might work, bring it up =
so I can think about it as well. I may also end up differing from the =
plan a little once I get to coding since that happens sometimes. I'll =
mention it if I do, though.

Re: [Web-erp-developers] Need some help with MySQL query

[Danie B. <br...@na...>]

On Fri, 2004-03-26 at 01:24, Phil Daintree wrote:
> Hi Danie,
> 
> This SQL is only part of the story ... what about the quantity on sales orders 
> that cannot be delivered because of insufficient stock - we would wish to 
> purchase stock to cover these sales too?
> 
> I have kept all SQL simple with no subqueries etc to avoid cross SQL server 
> issues like this. I would do this in 3 round trips.
> 
> One for the demand - which would need to take into account the demand for 
> parent assemblies as well.
> One for the qty on order and another for the location stock quantites and 
> reorder level using PHP to calculate the quantity to buy.
Thanks, you have just highlighted that I have not as yet considered every thing,
the one thing that covers the report for now, is that it is just a
suggestion, however once again it should be reported that these late
delivery problems should also be highlighted.

I need to rethink this, with everything involved, including assemblies
and whether the products are manufactured etc. So I will come back with
something better.

Thanks


> I am confident that there is an answer to how the query could be constructed 
> in one bite ...but I'm too simple for that and it is possible that the 
> overhead associated with 3 round trips could be less than the LEFT RIGHT 
> INSIDE AND OUTSIDE JOINS necessary to acheive this in one SQL script. Also, 
> its a bitch to read monster SQL and understand the logic of scripts when the 
> SQL is too tough.
> 
> Take a look at StockStatus.php for how I did this.
I will, thanks. I was using stockValuation as a template.

Kind Regards
Danie Brink br...@na...


> 
> 
> Phil
> 
> 
> On Thursday 25 March 2004 21:25, Danie Brink wrote:
> > I have finalized the second query to this, and it works with some
> > casting under postgres, however it seems MySQL can not handle such a
> > query this way. What would be the correct way of doing this in MySQL?
> >
> > SELECT PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode,
> > Sum(PurchOrderDetails.QuantityOrd -
> > PurchOrderDetails.QuantityRecd) as Order
> > FROM PurchOrders, PurchOrderDetails, StockMaster
> > WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo
> > AND PurchOrderDetails.Completed = 0
> > AND StockMaster.StockID = PurchOrderDetails.ItemCode
> > AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode ) IN  (
> > SELECT LocStock.LocCode, LocStock.StockID
> > FROM LocStock, StockMaster
> > WHERE LocStock.Quantity <= LocStock.ReorderLevel
> > AND LocStock.StockID=StockMaster.StockID )
> > GROUP BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> > ORDER BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> >
> > Below is the final queries I have decided on, it will limit the amount
> > of work that needs to be performed by the PHP script
> >
> > ////////////////////////////////////
> > //
> > // FINAL QUERIES FOR REORDER REPORT
> > //
> > ////////////////////////////////////
> >
> > // To Reorder - produce a list of stock that might to be reorders based
> > on stock catagory and location
> > SQL1  = "SELECT LocStock.LocCode, LocStock.StockID, LocStock.Quantity,
> > LocStock.ReorderLevel ";
> > SQL1 .= "FROM LocStock, StockMaster ";
> > SQL1 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> > SQL1 .= "AND
> > LocStock.StockID::character(20)=StockMaster.StockID::character(20) ";
> > SQL1 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL1 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL1 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "' ";
> > SQL1 .= "ORDER BY LocStock.LocCode, LocStock.StockID ";
> >
> > // Already Ordered - proude a list of stock that is already ordered and
> > in the list of possible reorders
> > SQL2  = "SELECT PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode, ";
> > SQL2 .= "Sum(PurchOrderDetails.QuantityOrd -
> > PurchOrderDetails.QuantityRecd) as Order ";
> > SQL2 .= "FROM PurchOrders, PurchOrderDetails, StockMaster ";
> > SQL2 .= "WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo ";
> > SQL2 .= "AND PurchOrderDetails.Completed = 0 ";
> > SQL2 .= "AND StockMaster.StockID = PurchOrderDetails.ItemCode ";
> > SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL2 .= "AND PurchOrders.IntoStockLocation = '" . $_POST['Location'] .
> > "' ";
> > SQL2 .= "AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> > ) IN  (";
> > SQL2 .= "SELECT LocStock.LocCode, LocStock.StockID ";
> > SQL2 .= "FROM LocStock, StockMaster ";
> > SQL2 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> > SQL2 .= "AND LocStock.StockID=StockMaster.StockID ";
> > SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> > ";
> > SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> > SQL2 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "'";
> > SQL2 .= ") ";
> > SQL2 .= "GROUP BY PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode";
> > SQL2 .= "ORDER BY PurchOrders.IntoStockLocation,
> > PurchOrderDetails.ItemCode";
> >
> > ////////////////////////////////////
> >
> >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by: IBM Linux Tutorials
> > Free Linux tutorial presented by Daniel Robbins, President and CEO of
> > GenToo technologies. Learn everything from fundamentals to system
> > administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web...@li...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> 

Re: [Web-erp-developers] Re: Postgres

[Phil D. <ph...@du...>]

I fixed these 2 - thanks
Phil

On Thursday 25 March 2004 21:24, Danie Brink wrote:
> Hi Phil,
>
> In my testing of queries I have found that the field types used for
> referential integrity does not always match. As I have been testing with
> postgres as well, I have discovered postgres will not consider
> varchar(20) the same as char(20) and varchar(5) the same as char(5) and
> type casts are required. This is a correct, however mysql ignores the
> difference as it treats char the same as varchar. We should add database
> type checking to our to-do list.
>
> Things I have decovered so far.
> LocStock.LocCode = PurchOrders.IntoStockLocation = varchar(5)/char(5)
> LocStock.StockID = PurchOrderDetails.ItemCode = StockMaster.StockID =
> varchar(20)/char(20)
>
> Kind Regards
> Danie Brink br...@na...
>
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

Re: [Web-erp-developers] Need some help with MySQL query

[Phil D. <ph...@du...>]

Hi Danie,

This SQL is only part of the story ... what about the quantity on sales orders 
that cannot be delivered because of insufficient stock - we would wish to 
purchase stock to cover these sales too?

I have kept all SQL simple with no subqueries etc to avoid cross SQL server 
issues like this. I would do this in 3 round trips.

One for the demand - which would need to take into account the demand for 
parent assemblies as well.
One for the qty on order and another for the location stock quantites and 
reorder level using PHP to calculate the quantity to buy.

I am confident that there is an answer to how the query could be constructed 
in one bite ...but I'm too simple for that and it is possible that the 
overhead associated with 3 round trips could be less than the LEFT RIGHT 
INSIDE AND OUTSIDE JOINS necessary to acheive this in one SQL script. Also, 
its a bitch to read monster SQL and understand the logic of scripts when the 
SQL is too tough.

Take a look at StockStatus.php for how I did this.


Phil


On Thursday 25 March 2004 21:25, Danie Brink wrote:
> I have finalized the second query to this, and it works with some
> casting under postgres, however it seems MySQL can not handle such a
> query this way. What would be the correct way of doing this in MySQL?
>
> SELECT PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode,
> Sum(PurchOrderDetails.QuantityOrd -
> PurchOrderDetails.QuantityRecd) as Order
> FROM PurchOrders, PurchOrderDetails, StockMaster
> WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo
> AND PurchOrderDetails.Completed = 0
> AND StockMaster.StockID = PurchOrderDetails.ItemCode
> AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode ) IN  (
> SELECT LocStock.LocCode, LocStock.StockID
> FROM LocStock, StockMaster
> WHERE LocStock.Quantity <= LocStock.ReorderLevel
> AND LocStock.StockID=StockMaster.StockID )
> GROUP BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> ORDER BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
>
> Below is the final queries I have decided on, it will limit the amount
> of work that needs to be performed by the PHP script
>
> ////////////////////////////////////
> //
> // FINAL QUERIES FOR REORDER REPORT
> //
> ////////////////////////////////////
>
> // To Reorder - produce a list of stock that might to be reorders based
> on stock catagory and location
> SQL1  = "SELECT LocStock.LocCode, LocStock.StockID, LocStock.Quantity,
> LocStock.ReorderLevel ";
> SQL1 .= "FROM LocStock, StockMaster ";
> SQL1 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> SQL1 .= "AND
> LocStock.StockID::character(20)=StockMaster.StockID::character(20) ";
> SQL1 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> ";
> SQL1 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> SQL1 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "' ";
> SQL1 .= "ORDER BY LocStock.LocCode, LocStock.StockID ";
>
> // Already Ordered - proude a list of stock that is already ordered and
> in the list of possible reorders
> SQL2  = "SELECT PurchOrders.IntoStockLocation,
> PurchOrderDetails.ItemCode, ";
> SQL2 .= "Sum(PurchOrderDetails.QuantityOrd -
> PurchOrderDetails.QuantityRecd) as Order ";
> SQL2 .= "FROM PurchOrders, PurchOrderDetails, StockMaster ";
> SQL2 .= "WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo ";
> SQL2 .= "AND PurchOrderDetails.Completed = 0 ";
> SQL2 .= "AND StockMaster.StockID = PurchOrderDetails.ItemCode ";
> SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> ";
> SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> SQL2 .= "AND PurchOrders.IntoStockLocation = '" . $_POST['Location'] .
> "' ";
> SQL2 .= "AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
> ) IN  (";
> SQL2 .= "SELECT LocStock.LocCode, LocStock.StockID ";
> SQL2 .= "FROM LocStock, StockMaster ";
> SQL2 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
> SQL2 .= "AND LocStock.StockID=StockMaster.StockID ";
> SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
> ";
> SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
> SQL2 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "'";
> SQL2 .= ") ";
> SQL2 .= "GROUP BY PurchOrders.IntoStockLocation,
> PurchOrderDetails.ItemCode";
> SQL2 .= "ORDER BY PurchOrders.IntoStockLocation,
> PurchOrderDetails.ItemCode";
>
> ////////////////////////////////////
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Web-erp-developers] Need some help with MySQL query

[Danie B. <br...@na...>]

I have finalized the second query to this, and it works with some
casting under postgres, however it seems MySQL can not handle such a
query this way. What would be the correct way of doing this in MySQL?

SELECT PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode,
Sum(PurchOrderDetails.QuantityOrd -
PurchOrderDetails.QuantityRecd) as Order 
FROM PurchOrders, PurchOrderDetails, StockMaster 
WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo 
AND PurchOrderDetails.Completed = 0 
AND StockMaster.StockID = PurchOrderDetails.ItemCode 
AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode ) IN  (
SELECT LocStock.LocCode, LocStock.StockID 
FROM LocStock, StockMaster 
WHERE LocStock.Quantity <= LocStock.ReorderLevel 
AND LocStock.StockID=StockMaster.StockID )
GROUP BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
ORDER BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode

Below is the final queries I have decided on, it will limit the amount
of work that needs to be performed by the PHP script

////////////////////////////////////
//
// FINAL QUERIES FOR REORDER REPORT
//
////////////////////////////////////

// To Reorder - produce a list of stock that might to be reorders based
on stock catagory and location
SQL1  = "SELECT LocStock.LocCode, LocStock.StockID, LocStock.Quantity,
LocStock.ReorderLevel ";
SQL1 .= "FROM LocStock, StockMaster ";
SQL1 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
SQL1 .= "AND
LocStock.StockID::character(20)=StockMaster.StockID::character(20) ";
SQL1 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL1 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL1 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "' ";
SQL1 .= "ORDER BY LocStock.LocCode, LocStock.StockID ";

// Already Ordered - proude a list of stock that is already ordered and
in the list of possible reorders
SQL2  = "SELECT PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode, ";
SQL2 .= "Sum(PurchOrderDetails.QuantityOrd -
PurchOrderDetails.QuantityRecd) as Order ";
SQL2 .= "FROM PurchOrders, PurchOrderDetails, StockMaster ";
SQL2 .= "WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo ";
SQL2 .= "AND PurchOrderDetails.Completed = 0 ";
SQL2 .= "AND StockMaster.StockID = PurchOrderDetails.ItemCode ";
SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL2 .= "AND PurchOrders.IntoStockLocation = '" . $_POST['Location'] .
"' ";
SQL2 .= "AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
) IN  (";
SQL2 .= "SELECT LocStock.LocCode, LocStock.StockID ";
SQL2 .= "FROM LocStock, StockMaster ";
SQL2 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
SQL2 .= "AND LocStock.StockID=StockMaster.StockID ";
SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL2 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "'";
SQL2 .= ") ";
SQL2 .= "GROUP BY PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode";
SQL2 .= "ORDER BY PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode";

////////////////////////////////////

[Web-erp-developers] Re: Postgres

[Danie B. <br...@na...>]

Hi Phil,

In my testing of queries I have found that the field types used for
referential integrity does not always match. As I have been testing with
postgres as well, I have discovered postgres will not consider
varchar(20) the same as char(20) and varchar(5) the same as char(5) and
type casts are required. This is a correct, however mysql ignores the
difference as it treats char the same as varchar. We should add database
type checking to our to-do list.

Things I have decovered so far.
LocStock.LocCode = PurchOrders.IntoStockLocation = varchar(5)/char(5)
LocStock.StockID = PurchOrderDetails.ItemCode = StockMaster.StockID =
varchar(20)/char(20)

Kind Regards
Danie Brink br...@na...

Re: [Web-erp-developers] Serialised stock - how to code this up

[Daintree <p.d...@pa...>]

> Like the StockItemMoves table I sent you in those diffs. It would not
> hold a record for the inital move (in stockmoves and breaks ref.
> integrity), and would hole the item#,orig_stkmove, and new_stkmove .

This only contains moves of controlled items right with their reference and
stockmove id - why would it not show the qty of the batch movement - where
is this recorded?  I guess the StockMoves table shows the total movement of
all serialised items per your logic. FromStkMoveNo and ToStkMoveNo - the
stock movements of a serialised item will not be in a range .... will
they???
There's a one StockMoves.StockID to many StockItemMoves.StockID (I think
some ref to controlled items would be preferable in the naming of the table)
and the StockMoves.NewQOH would contain the sum of all serialised items in
stock - not just one serial number item .... would it not??

>
> > - how would the system deal with lots and batches?
> >
> >
> non controlled, non serialised item - no conept of batches at all -
> StockMoves.Bundles defaults to 1
> controlled, non serialised item - bundle serves as an id for a stock
> move. The move maybe for, say, 25 items...maybe hundreds. That can count
> as a Lot, right?
>

OK I get it - that seems fine, just no qty control - probably ok - I prefer
my method ;-)

> > - Don't understand you templatey idea?
> >
> Do you mean the above, my plan on how to allow easy extension of
> stockitem details,

yes that bit. I see the StockModules tables - I guess it would reduce a bit
of input but would require additional sql to get the necessary info. I don't
see it as necessary? The stock category could contain any other data
relevant to the category.

>
> > - how do we display all the serial numbers of items on hand at any one
time?
> >
> >
> I realized this a while ago as well. All Items will have their current
> StockMove associated with them in their table - upkeep necessary during
> adds/moves/etc. I figure to select on-hand items the base criteria will
> be based on selecting a set of StockMoves with appropriate SysTypes,
> then selecting Items still tagged with those StockMoves. Do you know the
> SysTypes necessary without me digging around to find them?

I don't understand this. The SysTypes table refers to the various
transaction types - not all of which create stock movements. Individual
stockmoves contain the total balance of all stock in the location in the
newqoh field if I understand your logic so the quantity in the batch is
unknown. or do you mean to re-calcuate the quantity remaining on hand in the
ControlledStockMoves record and just pick up the last

Perhaps summing the qty ins and outs in the ControlledStockMoves - this
could be a substantial exercise with your data volumes?

Not using the LocStock table seems nice but what about items that need to
mature before they are sold - so they need to be held or products -
serialised items that must be sold before a given date as per Danie's note.
I think if we are doing it, it would be a shame to limit the scope to
exclude this.

Are we looking at another table to hold serialised quantites on hand and
these variables. The table being maintained like LocStock but at the serial
number level - the total of serialised items always agreeing to the LocStock
table QOH. There seems a disturbing amount of double up with LocStock
though.

>
> > - how do we display on invoices the serial numbers of items sold - if we
> >wanted to ?
> >
> >
> This can be an option specified in the StockMaster table, in
> StockModules, or even at the time of printing/viewing the invoice, and a
> combination of those...I also forsee situations like I have where I want
> to invoice someone for 50,000 cards on a regular invoice (obviously
> without serialised line items) and have the option to provide/retrieve
> the invoice detail in some other format, say a csv list of serial
> numbers and possibly other data that can be sent to a customer for their
> own inventory requirements.
>

I am probably wasting your time with my inability to grasp the concepts.
Sorry I just can't get the logic of what you are proposing, I fear it seems
to be some way short of acheiving what you hope for. I think I should just
leave you to it - give it a go - if it works ... and I do hope so ... I'll
include it!

Thanks for trying to explain ... good luck!

Phil

Re: [Web-erp-developers] Serialised stock - how to code this up

[Jesse P. <je...@st...>]

>
>
>Only "assembly" item BOMs - manufacturing items would of course need BOMs
>that contain serialised items. Assembly items are just ghosted codes that
>refer to a final assembly put together by the pickers rather than
>manufacturing - there is no opportunity to enter serial numbers for the
>components in this situation.
>  
>
ok, I think I need to look over how all those types work again...

>
>I'm coming round .... but still need a little more flesh on the idea ...
>sorry:
> - what would this subsidiary serialstockmoves table look like?
>  
>
Like the StockItemMoves table I sent you in those diffs. It would not 
hold a record for the inital move (in stockmoves and breaks ref. 
integrity), and would hole the item#,orig_stkmove, and new_stkmove .

> - how would the system deal with lots and batches?
>  
>
non controlled, non serialised item - no conept of batches at all - 
StockMoves.Bundles defaults to 1
controlled, non serialised item - bundle serves as an id for a stock 
move. The move maybe for, say, 25 items...maybe hundreds. That can count 
as a Lot, right?

> - would there be any control on quantities in batches/lots/bundles or could
>these be any amount?
>  
>
Any amount  for now.  If needed, it could be a StockMaster field and 
something like 0 or -1 could be unlimited. Alternatively, this could be 
implemented in a Sales or Product Catalog that 'templates' the contents 
of a StockMove like a BOM does. That would, I believe, require the 
Catalog to be setup and used separate of the StockMaster... still 
something I'm thinking about.

> - Don't understand you templatey idea?
>  
>
Do you mean the above, my plan on how to allow easy extension of 
stockitem details, or the original mention of using templates for pages. 
No worries on the last, the middle is better explained once I have code 
written...

> - how do we display all the serial numbers of items on hand at any one time?
>  
>
I realized this a while ago as well. All Items will have their current 
StockMove associated with them in their table - upkeep necessary during 
adds/moves/etc. I figure to select on-hand items the base criteria will 
be based on selecting a set of StockMoves with appropriate SysTypes, 
then selecting Items still tagged with those StockMoves. Do you know the 
SysTypes necessary without me digging around to find them?

> - how do we display on invoices the serial numbers of items sold - if we
>wanted to ?
>  
>
This can be an option specified in the StockMaster table, in 
StockModules, or even at the time of printing/viewing the invoice, and a 
combination of those...I also forsee situations like I have where I want 
to invoice someone for 50,000 cards on a regular invoice (obviously 
without serialised line items) and have the option to provide/retrieve 
the invoice detail in some other format, say a csv list of serial 
numbers and possibly other data that can be sent to a customer for their 
own inventory requirements.

Re: [Web-erp-developers] Serialised stock - how to code this up

[Phil D. <ph...@du...>]

> Again, I agree on the functionality, just not the implementation. If there
> is something wrong with what I've proposed before, please just give me a
run
> down of what will not work and a sample so I can consider it and see what
> you would think about how I would make that scenario work.

I can't say I understand your schema completely as yet, but I like the sound
of what you are proposing. This stuff is hard to describe and understand -
at least for the simple like me. Especially when I had in mind a method for
how I did it before. Your points about minimising the data in stockMoves
makes sense - some kind of drill down separate table of SerialisedMoves
would add to the complexity but possibly improve performance I agree.

> One note was your mention of disallowing BOMs to contain Serialised items.
I
> disagree... I also think what I've laid out alleviates your concerns with
> this. That, and I already have BOMs setup in situations where I definitely
> want one to contain both non-controlled,non-serialised items and
> controlled,serialised items.

Only "assembly" item BOMs - manufacturing items would of course need BOMs
that contain serialised items. Assembly items are just ghosted codes that
refer to a final assembly put together by the pickers rather than
manufacturing - there is no opportunity to enter serial numbers for the
components in this situation.

> And, in my mind, the data you would add to StockMoves for each serialised
> item in large moves would just duplicate most of the data for the other
> items involved... things not duplicated are simply numbers (qty, newqoh).
So
> why not just keep 1 copy of that data, and move the 1 thing (serial num)
> that really needs to change, but rarely needs to be directly referenced
> elsewhere and get it out of the way?

Yes good point.

>
> Did it actually handle the numbers being thrown around here? As I said,
> desigining it this way makes me nervous...
>
>
No it never had that kind of load.

> > Variable batch
> > control is a substantial undertaking and will trash the
> > system throughout
> > until we get it stable. It does add a fair bit of complexity
> > to scripts and
> > is not a project for the feint of heart!
> > Are you going to take it on ?
>
> Yes, it was my intention to start coding this... soon in fact. Keep in
mind,
> my original draw to this project was that you have an enormous amount of
> work put forth in areas that I had determined as necessary - before I
found
> it, I would have ended up writing the whole thing (ouch). I was mostly
> waiting to see what would come out of these discussions since they
obviously
> have a good deal to do with the coding...
>
> So, I think the question here is if you actually think my proposed system
> simply won't cut it, or if we are just coming to an impasse on design
ideas.

I'm coming round .... but still need a little more flesh on the idea ...
sorry:
 - what would this subsidiary serialstockmoves table look like?
 - how would the system deal with lots and batches?
 - would there be any control on quantities in batches/lots/bundles or could
these be any amount?
 - Don't understand you templatey idea?
 - how do we display all the serial numbers of items on hand at any one time
?
 - how do we display on invoices the serial numbers of items sold - if we
wanted to ?

I'm a pedantic b/std! Please bear with me I'll get there!

> If it's the later, I will try to get around to mocking up some tables,
> adding lots & lots of records to them, and running exisiting
queries/reports
> against them to see how they will perform.
>
> Thank you for having these discussions. I really am not trying to irritate
> you and am only being this persistant b/c I like the package you've put
> together and would like to help make it better - to that end, obviously I
> think the method I proposed will scale better than the way you'd
planned...
> again, though, please show me if you see holes in it so I can rethink my
> position.
>

Thank you for lending your knowledge to the greater good!
Certainly not irritated - enjoying the dialogue :-) .... its the only reason
I do it!
I think the discussion at this stage is great to ensure we get it right.

Phil

RE: [Web-erp-developers] Serialised stock - how to code this up

[jesse <je...@st...>]

more comments below, I think we're close...

>
> The stock master contains info about the item ....
>
> We already have a field to define if we want to reference
> items individually
> (controlled) or not.
agreed & agreed.

> However we also need to define the level of control. If it is
> controlled -
> then how many items per bundle, lot or serial number. Lets
> call this field
> QtyControl
Umm... yes and no. It may be a good idea to limit the number of items that
can be associated with, say, a sales order - that goes back around to my
suggestion of a product catalog that templates the available options (like
max qty) ... kind of like a BOM allows, but different ... and refers just to
what you is selected on a SalesOrder. That would also lend well to adding
services later that may not be able to be put into the stock structure. It's
a lot of work, but I don't think it needs to be done yet....

> If it is only one item allowed per lot then this is serial
> number control -
> one reference per item.
> In some businesses it could also be that up to 1000 kgs are
> allowed in a
> batch of cheese production then a food manufacturer would be
> able to trace
> where the batch was received and sold to for quality control purposes.
> If up to 150 metres of cloth could be on a roll - a clothing
> manufacturer
> would want to trace where each roll of cloth was used.
StockMaster:
StockID=ROLL_RED_CLOTH
Desc=Roll of Red Cloth
Units=metres
Controlled=yes
Serialised=no

[several stock moves shown...]
StockMoves:
(1 - from PO)
StockId=ROLL_RED_CLOTH
Bundle=1234321   (some or any control number used by vendor, made up,
defaulted to 1, etc...added upon receipt of goods from PO)
Qty=150
Type=25  (po delivery)
NewQOH=150  (pretend it's the 1st order for simplicity)
(2 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00001   (my Control # attached when I move the item anywhere. Could
be printed label attached when cloth is cut, rerolled, and shipped to
customer, warehouse, etc.)
Qty=10
Type=10 (sales invoice)
NewQOH=140
(3 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00002  (same as #2)
Qty=50
Type=10 (sales invoice)
NewQOH=90
(4 - to SalesOrder)
StockId=ROLL_RED_CLOTH
Bundle=A00003    (same as #2)
Qty=5
Type=10 (sales invoice)
NewQOH=85

and LocStock would work as is with no changes...

> So you see the two fields Controlled and QtyControl allow the
> item to be
> serialised - ie a reference for every unit of the item, or a
> reference that
> can cover up to 999999999 units of the item - or whatever the
> limit of an
> int data type  is.
I'm still sticking with Controlled & Serialised...

> Also, while we are adding fields to the stock master - the
> number of decimal
> places to display would be a cool addition to the feature set.
This should be a User Interface change, not database change. A Standard
Weights & Measures library to handle conversions. Maybe to keep from needing
a product catalog (as referenced above), the field added in StockMaster is
SoldUnits (or the such) and Units will be PurchasedUnits (without changing
field name), and of course checking on StockMaster entries/updates that both
Units agree with each other (ie, someone hasn't entered units=kgs and
soldunits=feet  (thought units=kilograms and soldunits=ounces would be ok)

> When items are received ..... the stock master is examined to
> see if the
> item is controlled or not ... if its controlled then we need
> to use the new
> script to put the stock in and enter the reference for the
> bundle lot or
> serial number - with reference to the stock master QtyControl
> - if its 1
> (serialised stock) then the quantity would not need to be
> displayed it would
> be defaulted to 1 for each reference (serial number) entered.
> If its more
> than 1 then input of the quantity in the lot would be
> required too. Perhaps
> this screen should show a table of  reference text inputs and
> quantities.
Yes... kind of. I still have bad,bad feelings about recording every serial
number in StockMoves.


> Need to check for not pre-existing references and quantities
> less than or
> equal to QtyControl.
Um... just to be certain, I see the Qty being worked with coming from either
the Qty in GoodsReceived or ConfirmDispatch. Any script adding serialised
items would have to be very strict about requiring the number of items
input/selected to exactly match the Qty received or sold/moved.

> When items are (purchase) ordered - then there is no need for
> lot control
> (is there?) since you don't know the lot until the order
> turns up at the
> warehouse gates.
Yes, exactly. That functionality is just in GoodsReceived.

> When items are sold - at the Confirm_DispatchInvoice.php stage the
> quantities and lot numbers would need to be noted by the
> storeman/picker and
> would need to entered in this script for controlled items -
> with quantities
> (if not serialised ie QtyControl ==1). When the invoice is created it
> creates a stockmove for each lot with the quantities entered
> - we already
> have the bundle field to record the lot (or bundle or serial
> number) of the
> item sold.
Agreed... my view on how the data would be laid out is above and reflected
in the tables I've sent. What you mention here is 'controlled' - serialised
would add the same single stock move and add(on received) or update(on
moved/sold) each item's stockmove number, and for sold/moved, add tracking
records for previous stockmoves the item was involved in.


> The updates to the LocStock would need modification since
> LocStock table now
> needs a Bundle field.
> the LocStock table contains information about quantities at each stock
> location - and it should also show the quantities in each bundle - for
> serialised items each quantity would be 1 - and the LocStock
> record for the
> bundle would be deleted when the item/serial number is invoiced or
> transferred to another location.
LocStock should contain counts of StockMaster types for a Location... it
doesn't seem right to extend that to counts of sets of StockMaster types
since they are not different StockMaster types, just sets of the same
StockMaster type.

Consider this. Under the method I've proposed, you can easily break down
LocStock to get at what bundles are in a location by linking back to
StockMaster and looking at Qty's. The big difference is that your way is
adding lots of records to tables whose performance is critical and that are
oft referenced by other scripts. Mine allows those same tables to stay small
enough to gaurantee performace will stay ok and relagates actual
serialization to another table that you will rarely be running large queries
against.


> If controlled==1 then the StockStatus inquiry needs to show
> the bundles of
> stock held arriving at a total at each location. Or at least
> an option to
> show the bundles.
>
> Since the stockmoves are created for every serialised item
> then the stock
> movement inquiry will show the full detail for each bundle -
> we could even
> show just the movements relating to a specific bundle -
> coming in and going
> out. The invoices and credit notes would also display - the movements
> relating to the invoice - serial number by serial number.
Yes, all of that is good... again, though,  under my scheme, all of that is
possible, you don't have to roll up (say count(*) ) bundles, and when you
want full detail on a bundle, the query to pull it simply links against the
Items table and is a 'link' away (ie, serialised items would link to a
detail page, non-serialised would just have no link). Once again, think
about even an an invoice with 1,000 items on it. Do you really want an
Invoice that big?? I would think you would want to summarize it with a
single line and then provide details if needed. It's all tracked, so you can
get it anytime it is needed.

Also, what are your thoughts on getting 1,000 items into and out of
inventory? Someone entering each serial number? An import file works there,
but what about 10,000? 100,000? I'm really not kidding when I say that today
I will sell that many items at one time to a single customer and then have
to be able to take a serial number and tell which Customer received it.


> So I am proposing ....
>
<proposed changes removed for brevity...>
Again, I agree on the functionality, just not the implementation. If there
is something wrong with what I've proposed before, please just give me a run
down of what will not work and a sample so I can consider it and see what
you would think about how I would make that scenario work.
One note was your mention of disallowing BOMs to contain Serialised items. I
disagree... I also think what I've laid out alleviates your concerns with
this. That, and I already have BOMs setup in situations where I definitely
want one to contain both non-controlled,non-serialised items and
controlled,serialised items.
That's a great list though! All the scripts I 'inventoried' as needing
changes and I think a couple more. Some, though, wouldn't be necessary as I
don't require changes to the way StockMoves or LocStock functions today, so
many reporting functions don't break.

> I am not sure about any issues in having many millions of
> stockmoves -  SCSI
> disk size/speed/RAID and plenty of additional RAM to deal
> with the data. The
> script limits the data returned by date for stockmoves
> inquiries by date -
> retrieving moves for invoices and credits might slow from
> less than a milli
> second to a tenth or something. It would need some trial -
> with hopefully
> minimal error. The performance of the system is really quite
> good. Having
> additional tables with links etc would probably be slower
> than one biggie.
> Indexing is also a critical one to watch.

Single selects out of several million items would be quick, but something
like grouping on a stockid, location, etc. and counting would not be that
quick
I certainley will throw whatever hardware at it as necessary - that wasn't
really in question. I just think that optimizing the data layout and what is
being accessed often from the start will save on hardware necessities in the
future... and 'big hardware' doesn't necessarily fix slow running software.
Indexes are certainley important - remember, eventually even an index can
get out of hand, though. Look at StockMoves - that's not only the table
data, but indexes on 12 fields that have to be created, updated, etc when
changes are made.
And, in my mind, the data you would add to StockMoves for each serialised
item in large moves would just duplicate most of the data for the other
items involved... things not duplicated are simply numbers (qty, newqoh). So
why not just keep 1 copy of that data, and move the 1 thing (serial num)
that really needs to change, but rarely needs to be directly referenced
elsewhere and get it out of the way?

> Sorry this is a biggie - this is how I did serial number control in a
> previous client server SQL Server system - it worked well.
Did it actually handle the numbers being thrown around here? As I said,
desigining it this way makes me nervous... I'm seriously not kidding when I
say


> Variable batch
> control is a substantial undertaking and will trash the
> system throughout
> until we get it stable. It does add a fair bit of complexity
> to scripts and
> is not a project for the feint of heart!
> Are you going to take it on ?

Yes, it was my intention to start coding this... soon in fact. Keep in mind,
my original draw to this project was that you have an enormous amount of
work put forth in areas that I had determined as necessary - before I found
it, I would have ended up writing the whole thing (ouch). I was mostly
waiting to see what would come out of these discussions since they obviously
have a good deal to do with the coding...

So, I think the question here is if you actually think my proposed system
simply won't cut it, or if we are just coming to an impasse on design ideas.
If it's the later, I will try to get around to mocking up some tables,
adding lots & lots of records to them, and running exisiting queries/reports
against them to see how they will perform.

Thank you for having these discussions. I really am not trying to irritate
you and am only being this persistant b/c I like the package you've put
together and would like to help make it better - to that end, obviously I
think the method I proposed will scale better than the way you'd planned...
again, though, please show me if you see holes in it so I can rethink my
position.


jesse