× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MAPICS-L
  3. March 2005

RE:Updating CMAT costing (Mike Ellis/ISI)

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 thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.