How can I get a field from Jobs into a BAQ about GLJournals?

[Briser (Brian Sandison)]

I was asked to create a BAQ showing GL Journals that are related to Trailer sales with various fields which I have managed to do with one exception.

They want a date the Job was signed off as Quality Inspection completed so basically I need to join a custom table to my BAQ which I am having a lot of difficulty with.

So far I have tried joining JobHead to GLJrnDtl using SegValue6 (Serial Number) against JobNum (which are the same number) but I realise this was not going to work.

Has anyone managed to join General Ledger to Production Job tables?

The attached is my starting point.

GA_PostMonthYearEndSales.baq (11.8 KB)

[dcamlin (David Camlin)]

The GLJrnDtl’s “Description” column seems to include job numbers. You could try to parse that out to capture the JobNum and then join based on that.

Example Comment: Labor Emp:693 Job:060003-1-1/0/20

SUBSTRING(
    GLJrnDtl.Description,
    CHARINDEX('Job:', GLJrnDtl.Description) + 4,
    CHARINDEX('/', GLJrnDtl.Description) 
      - (CHARINDEX('Job:', GLJrnDtl.Description) + 4)
)

I was able to use the above and feed it a JobNum and get the Closed Date off of the JobHead table.

Not sure what GL transactions you’re trying to use to marry up to jobs. Could be other ways to do this.

[Briser (Brian Sandison)]

Thanks David, I will give this a go today.

How do I achieve this using Kinetic? Is it with a calculated field?

I don’t know if this makes a difference but our GLJrnDtl.Description field is in the form
AR Inv: 13622 Company name Ltd

[Chad_Smith]

Have you met TranGLC?

I would try David’s approach first for the sake simplicity (and maybe your sanity), but TranGLC is the path between most business objects and all things debity/credity. You’d need the secret decoder ring to use it. Maybe someone has a link handy. I have one somewhere but seem to have misplaced it.

[hkeric.wci (Haso Keric)]

If you have alot of TranGLCs then go after the Journal Description and extract. Because we do Backflush my query would always timeout on TranGLC.

We ended up Extracting the Descriptions as well for everything from the PO, the Job among other things. Creating Calculated Fields.

[Briser (Brian Sandison)]

I have managed to get the number from Description but it is in fact an AR Invoice Number rather than a Job Number:

SUBSTRING(

GLJrnDtl.Description,

CHARINDEX(':', GLJrnDtl.Description) + 2,

CHARINDEX(' ', GLJrnDtl.Description, CHARINDEX(':', GLJrnDtl.Description) + 2)

    - (CHARINDEX(':', GLJrnDtl.Description) + 2)

)

I have not heard of TranGLC or it’s secret decoder ring before.

[jkane (John Kane)]

The ABTUID is the link between all things GL. It is a 1 to 1 link like SysRowID.

[hkeric.wci (Haso Keric)]

JobNum:

CASE 
  WHEN CHARINDEX('Job:', GLJrnDtl.Description) > 0 
     AND CHARINDEX('/', GLJrnDtl.Description, CHARINDEX('Job:', GLJrnDtl.Description)) > 0 
  THEN SUBSTRING(
     GLJrnDtl.Description, 
     CHARINDEX('Job:', GLJrnDtl.Description) + 4, 
     CHARINDEX('/', GLJrnDtl.Description, CHARINDEX('Job:', GLJrnDtl.Description)) - 
     CHARINDEX('Job:', GLJrnDtl.Description) - 4
     )
  ELSE NULL 
END

PartNum:

CASE 
  WHEN CHARINDEX('Part:', GLJrnDtl.Description) > 0 
  THEN RTRIM(
    LTRIM(
      SUBSTRING(
        GLJrnDtl.Description, 
        CHARINDEX('Part:', GLJrnDtl.Description) + 5, -- Start after "Part:"
        CASE
          -- Find the nearest of the next known fields (Job, PO, Ext.Cost, Order, Pack, Cust, PS, DS)
          WHEN CHARINDEX('Job:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('Job:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('Job:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5
          
          WHEN CHARINDEX('PO:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('PO:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('PO:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5

          WHEN CHARINDEX('Ext.Cost:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('Ext.Cost:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('Ext.Cost:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5

          WHEN CHARINDEX('Order:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('Order:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('Order:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5
          
          WHEN CHARINDEX('Pack:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('Pack:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('Pack:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5

          WHEN CHARINDEX('Cust:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('Cust:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('Cust:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5

          WHEN CHARINDEX('PS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('PS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('PS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5
          
          WHEN CHARINDEX('DS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('DS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('DS:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5
          
          WHEN CHARINDEX('IV:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > 0 
              AND CHARINDEX('IV:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) > CHARINDEX('Part:', GLJrnDtl.Description) 
          THEN CHARINDEX('IV:', GLJrnDtl.Description, CHARINDEX('Part:', GLJrnDtl.Description) + 5) - CHARINDEX('Part:', GLJrnDtl.Description) - 5
          
          -- If no other field exists, take the entire remaining string
          ELSE LEN(GLJrnDtl.Description) - CHARINDEX('Part:', GLJrnDtl.Description) + 1
        END
      )
    )
  )
  ELSE NULL 
END

[dcamlin (David Camlin)]

@dcamlin

Not sure what GL transactions you’re trying to use to marry up to jobs.

Yeah, and that’s why I mentioned the above. You may already be filtering out the “job” related transactions and your query is only looking for AR invoices.

You may need a subquery where you can query your journal detail again.

If these are based on customer shipments, you can look for MFG-CUS or STK-CUS transactions in your query, those include the PartNum, SalesOrder, PackNum, and CustNum in the description… then you’d have to bounce that through JobProd, for example, to get the JobNum so you could link JobHead.

Example Description: MFG-CUS Part:070-06119 Order:53796/2/1 Pack:19/1 Cust:14810

or… STK-CUS Part:028-01510 Order:60039/1/1 Pack:1/1 Cust:63540

[jkane (John Kane)]

@hkeric.wci , try using the ABTUID. Will save you tons of time.

[hkeric.wci (Haso Keric)]

Never looked into the ABTUID I’ll check that out!

[jkane (John Kane)]

It will tie directly to a Journal.

[dcamlin (David Camlin)]

Agree with @hkeric.wci … never even heard of that before.

[dcamlin (David Camlin)]

What table?

[jkane (John Kane)]

Any thing that interacts with the GL

select top 100 p.*, t.*
from erp.parttran p
left outer join erp.tranglc t
on p.company = t.company and p.abtuid = t.abtuid

You will further want to filter based off of what you are doing, but that is the just of it.

[dcamlin (David Camlin)]

OH… I see ABTUID is on the TranGLC table. Gotcha.

[jkane (John Kane)]

It is all over the place.

select top 100 abtuid
from erp.LaborDtl

[hkeric.wci (Haso Keric)]

For me the issue was that our TranGLC somehow has a billion rows and I needed to remain on the JrnlDtl level for me not to timeout when summing… Maybe the ABTUID is indexed better. Then you also have Multi-Books etc…

[jkane (John Kane)]

I would hope so, it is the GUID that runs the GL.