Skip to main content

I’m trying to get all inventory movements through a purchases accrual account in an inquiry. I can get the APTran joined nicely. I have about HALF of my INTran records showing as expected.

The issue I have is that I “think” I need to use Line Number, but the Line Number on INTran is 1, but in the GLTran it’s 2 for a record (Purchases Accrual comes in as the 2nd line, the 1st line is the offsetting account). Without the Line Number, I’m concernced that a transaction could have two rows with the same Item and Account ID, thus creating duplicate entries. The DAC Browser doesn’t have anything regarding these two tables that I can find (hopefully those get more flushed out as we go along).

 

Joins between GLTran and INTran

Thanks for looking/helping everyone!

Hi @Michael Hansen  Below are the join conditions for the table GLTRAN and INTRAN.

 

Select * from INTran I inner join GLTran G on I.CompanyID= G.CompanyID and I.TranType=G.TranType and I.RefNbr = G.RefNbr
Where I.RefNbr='002051' and I.DocType='R'

If you could not able to figure it out, just share your GI, so that I will make the correction and send it back to you.

I don’t think that would account for an INTran line with two items, or even two identical items would it? 

I don’t know how the INTran module works persay, but if an INtran /GLTran Document had:

 

INTran:
Item A qty 5

Item A qty 10

GLTran:

Item A qty 5

Item A qty 10

Wouldn’t I get four results with the joins you mentioned above? Nothing here is choosing the item by the ID or by the line number. Again, I’m not sure how the GLTran works, maybe it forces only one item per GLTran line, but I know INTran can have MANY items on it, thus my intention to use the LineNbr field to isolate that item uniquely.

Hello Michael,

In addition to joining on appropriate Key Fields from both GL Tran and IN Tran,

and solving whether Line order of Inventory batch is the same as line order of the GL Batch,

please consider whether to use Debit Amount vs. Credit Amount from GL Tran.  Inventory can be a Debit or a  Credit in GL depending on the Transaction Type in Inventory, Receipt versus Return.

GLTran key fields:  Company ID, Module, Batch Number, Linenbr

INTran key fields: Company ID, DocType, Reference nbr, LineNbr.

To find out how inventory items work in GLTran, I suggest you create inventory batches and then study the results in the GL batches, making sure “Post Summary on Updating GL” is NOT selected in IN Preferences screen.

 

Laura

I have a sample set for validating. Here is my issue with the joins we’ve posted.

 

I want all GLTran lines for a specific account (Purchase Accruals). I have it filtered to only show those rows. This is my starting set, I now have all the dollars moving through the account. Now I want to tie it back to the PO who created it, so I need to bind to INTran.

 

INTran has ONE line in it, but GLTran has TWO lines in it. The TranLineNbr for GLTran is “2” (line 1 being the offsetting GL entry), but since the INTran only has one line, it has LineNbr “1”. My join fails here. I need a way to get from the second GLTran Line (which holds my purchases accrual account data) back to INTran, who only has a single line.

 

My concern now, is that if I drop the TranLine identifier, and my transaction has multiple items with the same ID, I will get duplicate entries. 

Also, thank you everyone for helping me dig into this, it is GREATLY appreciated!

There’s some unintuitive behavior with GLTran that makes it hard to do cross-table inquiry, at least under our setup:

  • INTran’s LineNumber goes 1>3>5>7
  • GLTran’s TranLineNbr for the AP leg (RefNbr = AP) goes 1>2>3>4 - which may or may not be the same as your Receipt lines depending on the bill.
  • GLTran’s TranLineNbr straight up doesnt show in the IN leg (RefNbr = INTran Receipt’s RefNbr)
  • GLTran’s LineNbr has offsetting accounts - hard to trace back since can have other offsetting accounts like tax

If I were facing your issue, I would ignore GLTran and use INTran as the base, you already have AcctID as the account and ExtCost as the dollar amount. By filtering out only PO accrual accounts and only Released transactions, you could (theoretically) tie back to GL, barring any reclassifications of course. 

Unfortunately, I still need to bind APTran as well. I’m not sure I could get from INTran → GLTran and still have APTran records for my account without making duplicate entries.

This is why I started in GLTran (Both APTran and INTran make GLTran records).

I can make two inquries to get the answer for now. Just feels like a miss for linking the tables forwards and backwards. You would think that there’d HAVE to be a 1:1 relationship here… At least I’m not the only nerd struggling with these line numbers :D

Hi @Michael Hansen were you able to find a solution? Thank you!

I ended up making two inquiries to solve the problem.

I’ve come across this same issue and can only join GLTran to INTran to a certain extent, but not 100% unique 1 to 1.

Therefore my report duplicates lines when the IN record has lines with the same Inventory ID, Qty, Value etc.

Having GLTran.TranLineNbr populate with the INTran.LineNbr (as it does with AP and AR LineNbr’s) would give a unique 1 to 1 join and resolve this. I’ve created an idea for this:

 

 

Reply


Terms & ConditionsCookie settings