Can you join PRDISTRIB to PRTIME?

Is there a question in there ? :)=

Yes, how do you join the PRDISTRIB  to PRTIME? 

It's not a one-to-one join. You have to join PRDISTRIB (details) to PAYMASTR (header) back down to PRTIME (details) -- or vice versa. You can then use the GLT-OBJ-ID in both the PRDISTRIB and PRTIME tables to match the corresponding GL transactions. This isn't a simple report (you probably won't be able to use Crystal connected to the tables but will likely need to do some fancy SQL).

Hi Gail,

Not sure of the details of your report requirements or your PR setup, but you might try this, especially if you are looking for certain types of hours:

Join PRDISTRIB to PRTIME on COMPANY, EMPLOYEE, and CHECKID, and join PRDISTRIB.PCD_SEQ_NBR to PRTIME.PAY_SUM_GRP through the PRPAYCODE table. This is fast because you are using the clustered indexes, and you don't need to bring the PAYMASTR into the mix.

Try a query similar to this, to see if you can get what you are looking for using this way:

SELECT A.EMPLOYEE

,A.PCD_SEQ_NBR

,B.PAY_SUM_GRP

,A.DST_ACCOUNT

,B.DST_ACCOUNT

,B.HOURS FROM PRDISTRIB A, PRTIME B, PRPAYCODE C

WHERE A.EMPLOYEE =

AND A.CHECK_ID =

AND A.COMPANY = B.COMPANY

AND A.EMPLOYEE = B.EMPLOYEE

AND A.CHECK_ID = B.CHECK_ID

AND A.PCD_SEQ_NBR = C.SEQ_NBR

AND B.PAY_SUM_GRP = C.PAY_SUM_GRP

Caveat: this assumes one line per paycode per check, so if you enter daily or weekly time I will be problematic. =

i do it in crystal and sql. we use this PRTIME sql expression to PRDISTRIB. sql is the same idea. you should test if hooking tr_date or per_end_date will work for you. in this case we use labor dist templates in gm10 to distribute labor in PR197, we use tr_date.

(select sum(r_hours)
from lawson.prtime
where company="PRDISTRIB"."COMPANY"
and employee="PRDISTRIB"."EMPLOYEE"
and check_id="PRDISTRIB"."CHECK_ID"
and pcd_seq_nbr="PRDISTRIB"."PCD_SEQ_NBR"
and tr_date="PRDISTRIB"."TR_DATE")