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.
>
>