Solved

Joining GLTran to INTran

  • 11 July 2023
  • 8 replies
  • 210 views
Michaelh
Rank
Userlevel 5
Badge

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!

icon

Best answer by Michaelh 10 August 2023, 00:05

View original
Permanent Newb, don't let the posts fool you.

8 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

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.

Naveen B
Michaelh
Rank
Userlevel 5
Badge

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.

Permanent Newb, don't let the posts fool you.
Laura02
Rank
Badge +18

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

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
Michaelh
Rank
Userlevel 5
Badge

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!

Permanent Newb, don't let the posts fool you.
K
Rank
Userlevel 5
Badge

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. 

Michaelh
Rank
Userlevel 5
Badge

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

Permanent Newb, don't let the posts fool you.
Chris Hackett
Rank
Userlevel 7
Badge

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

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
Michaelh
Rank
Userlevel 5
Badge

I ended up making two inquiries to solve the problem.

Permanent Newb, don't let the posts fool you.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved