|
midrange.com
Add to the suggestions that you use the SQLs you're creating to build an
offline load file to make changes to ITEMASB. MUCH MUCH MUCH SAFER. And
these days, a little almost free safety seems to make sense.
Michael G. Ellis
Information Systems International, Inc.
A Global ERP Consulting Firm
815-398-1670 x23
Choose ISI consultants to ensure consistently top quality implementation
services at all levels of your organization.
"Look at a day when you are supremely satisfied at the end;
it is not a day when you lounge around doing nothing,
it's a day when you've had everything to do and you've
done it!"
- Margaret Thatcher
mapics-l-request@xxxxxxxxxxxx
Sent by: mapics-l-bounces@xxxxxxxxxxxx
03/09/2005 12:00 PM
Please respond to mapics-l
To: mapics-l@xxxxxxxxxxxx
cc:
Subject: MAPICS-L Digest, Vol 3, Issue 63
Send MAPICS-L mailing list submissions to
mapics-l@xxxxxxxxxxxx
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/mapics-l
or, via email, send a message with subject or body 'help' to
mapics-l-request@xxxxxxxxxxxx
You can reach the person managing the list at
mapics-l-owner@xxxxxxxxxxxx
When replying, please edit your Subject line so it is more specific
than "Re: Contents of MAPICS-L digest..."
Today's Topics:
1. RE:Updating CMAT costing (James Staber II)
2. Re: RE:Updating CMAT costing (Kris.devlieger@xxxxxxxxxx)
3. RE: Updating CMAT costing (Ann Neal)
----------------------------------------------------------------------
message: 1
date: Tue, 8 Mar 2005 13:57:58 -0500
from: "James Staber II" <jstaber@xxxxxxxxxx>
subject: RE:Updating CMAT costing
-----Original Message-----
Ann,
Thanks for the quick response
Here's the command that I wrote. I think the intent is correct but my
methodology is flawed:
The following command errors out when trying to update CMAT with the NULL
value in table 2:
update amflibx/itemasb f1
set cmat = (select lcost from amflibx/itembl f2
where f1.itnbr = f2.itnbr and f2.house = '1')
where f1.itnbr in (select itnbr from amflibx/itemasa f3
where f3.ittyp <> 'K')
message: 4
date: Tue, 8 Mar 2005 09:10:11 -0600
from: "Ann Neal" <aneal@xxxxxxxxxxxxxxxxxx>
subject: RE: Updating CMAT costing
Jim,
Could you modify your SQL to exclude ITTYP <> 'K' type parts? Can you
paste
your old SQL in your response?
Ann
-----Original Message-----
From: mapics-l-bounce
On Behalf Of James Staber II
Sent: Tuesday, March 08, 2005 7:03 AM
To: MAPICS-L
Subject: Updating CMAT costing
Does anyone have an automated way of updating the CMAT field in ITEMASB
with
the data from the LCOST field in ITEMBL?
We had a SQL command that we would run as required that worked fine until
we
added a 'kit' item which has a NULL LCOST value which CMAT doesn't like.
Any suggestions would be greatly appreciated.
Thanks,
Jim
------------------------------
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) digest list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.
End of MAPICS-L Digest, Vol 3, Issue 62
***************************************
------------------------------
message: 2
date: Tue, 8 Mar 2005 20:17:08 +0100
from: <Kris.devlieger@xxxxxxxxxx>
subject: Re: RE:Updating CMAT costing
Well, for one you forgot to include the:
and itnbr in (select itnbr from amflibx/itembl where house='1')
and then I think you can leave out the item type K selection. Your
problem
are those items not having an ITEMBL record in warehouse 1. But I suggest
to change the ittyp <>'K' into in ('3','4','9') because you don't need to
do
this for mfg parts.
Also:
Please update the maintain date (that what you did: maintain) and the cost
status code (so it'll be recosted during a selective cost roll up)
Once this is done you're where you want to be I think
Good Luck,
----- Original Message -----
From: "James Staber II" <jstaber@xxxxxxxxxx>
To: <mapics-l@xxxxxxxxxxxx>
Sent: Tuesday, March 08, 2005 7:57 PM
Subject: RE:Updating CMAT costing
>
>
> -----Original Message-----
> Ann,
>
> Thanks for the quick response
>
> Here's the command that I wrote. I think the intent is correct but my
methodology is flawed:
>
> The following command errors out when trying to update CMAT with the
NULL
value in table 2:
>
> update amflibx/itemasb f1
> set cmat = (select lcost from amflibx/itembl f2
> where f1.itnbr = f2.itnbr and f2.house = '1')
> where f1.itnbr in (select itnbr from amflibx/itemasa f3
> where f3.ittyp <> 'K')
>
> message: 4
> date: Tue, 8 Mar 2005 09:10:11 -0600
> from: "Ann Neal" <aneal@xxxxxxxxxxxxxxxxxx>
> subject: RE: Updating CMAT costing
>
> Jim,
>
> Could you modify your SQL to exclude ITTYP <> 'K' type parts? Can you
paste
> your old SQL in your response?
>
> Ann
>
> -----Original Message-----
> From: mapics-l-bounce
> On Behalf Of James Staber II
> Sent: Tuesday, March 08, 2005 7:03 AM
> To: MAPICS-L
> Subject: Updating CMAT costing
>
> Does anyone have an automated way of updating the CMAT field in ITEMASB
with
> the data from the LCOST field in ITEMBL?
>
> We had a SQL command that we would run as required that worked fine
until
we
> added a 'kit' item which has a NULL LCOST value which CMAT doesn't like.
>
> Any suggestions would be greatly appreciated.
>
> Thanks,
>
> Jim
>
>
>
> ------------------------------
>
> _______________________________________________
> This is the MAPICS ERP System Discussion (MAPICS-L) digest list
> To post a message email: MAPICS-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l
> or email: MAPICS-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/mapics-l.
>
>
>
> End of MAPICS-L Digest, Vol 3, Issue 62
> ***************************************
>
> _______________________________________________
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
> To post a message email: MAPICS-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l
> or email: MAPICS-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/mapics-l.
>
>
------------------------------
message: 3
date: Tue, 8 Mar 2005 15:07:13 -0600
from: "Ann Neal" <aneal@xxxxxxxxxxxxxxxxxx>
subject: RE: Updating CMAT costing
Try this:
UPDATE AMFLIBK/ITEMASB A SET A.CMAT = (SELECT B.LCOST FROM AMFLIBK/ITEMBL
B
WHERE A.ITNBR = B.ITNBR AND B.LCOST IS NOT NULL AND B.HOUSE = '1') WHERE
EXISTS (SELECT *FROM AMFLIBK/ITEMBL C WHERE A.ITNBR = C.ITNBR AND C.HOUSE
=
'1')
My warehouse was actually in a test environment and it is 'MEX'. Make
sure
your warehouse is '1 ' or ' 1' and not just '1' as the field is 3 alpha.
Ann
-----Original Message-----
From: mapics-l-bounces@xxxxxxxxxxxx
[mailto:mapics-l-bounces@xxxxxxxxxxxx]On Behalf Of James Staber II
Sent: Tuesday, March 08, 2005 12:58 PM
To: mapics-l@xxxxxxxxxxxx
Subject: RE:Updating CMAT costing
-----Original Message-----
Ann,
Thanks for the quick response
Here's the command that I wrote. I think the intent is correct but my
methodology is flawed:
The following command errors out when trying to update CMAT with the NULL
value in table 2:
update amflibx/itemasb f1
set cmat = (select lcost from amflibx/itembl f2
where f1.itnbr = f2.itnbr and f2.house = '1')
where f1.itnbr in (select itnbr from amflibx/itemasa f3
where f3.ittyp <> 'K')
message: 4
date: Tue, 8 Mar 2005 09:10:11 -0600
from: "Ann Neal" <aneal@xxxxxxxxxxxxxxxxxx>
subject: RE: Updating CMAT costing
Jim,
Could you modify your SQL to exclude ITTYP <> 'K' type parts? Can you
paste
your old SQL in your response?
Ann
-----Original Message-----
From: mapics-l-bounce
On Behalf Of James Staber II
Sent: Tuesday, March 08, 2005 7:03 AM
To: MAPICS-L
Subject: Updating CMAT costing
Does anyone have an automated way of updating the CMAT field in ITEMASB
with
the data from the LCOST field in ITEMBL?
We had a SQL command that we would run as required that worked fine until
we
added a 'kit' item which has a NULL LCOST value which CMAT doesn't like.
Any suggestions would be greatly appreciated.
Thanks,
Jim
------------------------------
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) digest list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.
End of MAPICS-L Digest, Vol 3, Issue 62
***************************************
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.
------------------------------
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) digest list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.
End of MAPICS-L Digest, Vol 3, Issue 63
***************************************
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
