Re: Joining PCP62 and INP95

Chris,

I don't have access to System21 data anymore, but I am sure the works order
number and operation sequence are stored on one field in INP95 (either the
Reference or the To/From field) - It is in the Style version anyway.

I've used this in my attempts at reconciling transactions between Inventory
and Production.

Hope this helps.

Roshan.

----- Original Message ----- 
From: "Chris Payne" <CPayne@xxxxxxxxxxxxxxx>
To: "'System 21 Users'" <system21@xxxxxxxxxxxx>
Sent: Thursday, August 12, 2004 6:05 AM
Subject: RE: [SYSTEM21] Joining PCP62 and INP95


> Joining on those three fields does not get me unique records, because
people
> make entries for the same part in the same company on the same work order.
>
> The query I have so far is as follows.
>
> SELECT
> cono95,
> pnum95,
> dtmv95,
> mqty95,
> nshf62
> FROM
>   OSLD1F3.INP95 T01,
>   OSLPDF3.PCP62 T02
> WHERE
>   CONO95 = CONO62  AND
>   PNUM95 = COMP62  AND
>   REFN95 = WORD62 AND
>   3600*hour(tmmv95)+60*minute(tmmv95)+second(tmmv95) >=
> 3600*hour(reft62)+60*minute(reft62)+second(reft62)-8  AND
>   3600*hour(tmmv95)+60*minute(tmmv95)+second(tmmv95) <=
> 3600*hour(reft62)+60*minute(reft62)+second(reft62)  AND
>
>   REFD95 = REFD62  AND
>
>   PNUM95 LIKE '* LABOR%' and
>   mqty95 = -1 * aval62
>
> and it has three problems. If a single work order takes longer then eight
> seconds to process, it will not show up. If someone makes 2 issues on the
> work order within 8 seconds of each other, and the issues are for the same
> amount, I pull duplicate records. Finally if someone makes an entry close
> enough to midnight (less then 8 seconds), and the system takes long enough
> to process (long enough to take it into the next day), the record will not
> show up.
>
> Than being said, this is a very lame hack, and if anyone has a
> better idea I would love to hear it.
>
> Chris
>
>
>
> -----Original Message-----
> From: Johnson, Duane [mailto:djohnson@xxxxxxxxxxxxx]
> Sent: Wednesday, August 11, 2004 3:06 PM
> To: 'System 21 Users'
> Subject: RE: [SYSTEM21] Joining PCP62 and INP95
>
> Why, yes, I may have noticed something to the effect that she may be on
> vacation.  From the 9th through the 13th.  Of August.
>
> By the way, you should link on at least three fields.
> CONO95 = CONO62
> REFN95 = WORD62
> PNUM95 = COMP62
>
> That should come close to getting you unique items.
>
> Duane Johnson
> Information Technology Services
> Programming Manager
> Coleman Powermate Inc.
> 4970 Airport Rd.
> Kearney, NE 68847
> djohnson@xxxxxxxxxxxxx
>
> -----Original Message-----
> From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]
> On Behalf Of Richard_Caldicott@xxxxxxxxxxxxxxx
> Sent: Wednesday, August 11, 2004 1:39 PM
> To: System 21 Users
> Subject: Re: [SYSTEM21] Joining PCP62 and INP95
>
>
> Does anyone know if Diana Larsson is on vacation?
>
>
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/system21.
>
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/system21.
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/system21.
>
>