BAQ - Labor detail as posted to the GL

Kinetic ERP
1

Surely this has come up before, but this seems to be one of those things that’s a combination of several common search terms for other things that swamp out the search results I’m actually looking for.

What I’d like to do is query labor detail transactions and get their detail as posted to the GL. GLJrnDtl doesn’t appear to return labor transactions. LaborDtl doesn’t return fiscal year, fiscal period, or a journal number. I’d really like to close the loop between the job operation labor tracking details and journal entries. What am I missing?

2

Try looking in the TranGLC table, that is where the LaborDtl / GL records are.
In that table the RelatedToFile column represents the table the record is from.

3

That does look like the other half of what I was looking for.

There doesn’t appear to be an available reference linking job to journal though. TranGLC.LaborHedSeq and TranGLC.LaborDtlSeq are always 0 and I’m not seeing anything like a job number in text fields, so no way to join on LaborDtl. LaborDtl.JournalNum is always 0, so no join paths going the other way.

4

Yea, I don’t really know why they did it this way but the actual fields like labordtlseq and others are never there.
You can join to labordtl though, key1 is empid and key2 is dtlseq if i remember correctly. They are strings though so you have to convert in the join or a subquery.
All the tables linked to that table have to join that way.

1 Like
5

Aaaah that’s it. Effectively:

join Erp.LaborDtl on 
	LaborDtl.Company = TranGLC.Company
	and LaborDtl.LaborHedSeq =  cast(TranGLC.Key1 as int)
	and LaborDtl.LaborDtlSeq =  cast(TranGLC.Key2 as int)
	and TranGLC.RelatedToFile = 'LaborDtl'

Thanks Joseph!