Customization and Integration

[trodman]

Does anyone know which tables the values in this field correspond to?

[Delamater]

Hi trodman,

Just a quick response for you. For some reason, I remembered that being driven back to BIE. At any rate, these two queries show the same results. It seems that at the very least, it relates to these tables:

tPACostTrxnRptWrk
tPATrxDetInqWrk
tPA00100

Queries that might help:

SELECT OBJECT_NAME([object_id]), name FROM sys.columns WHERE [name] LIKE '%linktable%'
SELECT * FROM tsmdatadictcol WHERE columnName LIKE '%linktable%'

At any rate, i hope that helps get you closer to the right answer.

[trodman]

Thanks, but I tried that all ready.  Each value is an integer.  Normally you could query the tsmdatadictcoldesc table and see what each value means.  However, not for this field for some reason.

[LouDavis]

Hey Tim,

 Using Redgates' SQL Search utility it looks like two other tables have this same column.  They are tPAtrxDetlnqWrk and tPACostTrxnRptWrk.  The schema browser does have the definitions for what each value means under the definition for tpa00100..

[trodman]

I know that one of them is the timInvtTran table because I can match tPA00100.intRelatedKey to the record in timInvtTran. I'm just not sure what the other tables are.

[LouDavis]

RedGates ' SQL Search is a free utility..http://www.red-gate.com/products/sql-development/sql-search/  If you get that and enter the table names, or the column name you will get a list of every DB object that it was found in.  That might help you understand what it is and where it is used.

[trodman]

Thanks Lou. I'll check it out.

[trodman · Accepted solution]

My bad, I was looking at the tsm tables instead of simply looking at the schema browser.  It's very clear in the schema browser.

[HeadHoncho]

Be careful with the receipt of goods.  You would think that this would indicate a reference to tpoRcvrLine, but it is not.  It is a pointer to tpoPOLine, and the intRelatedKey is a POLineKey.

[trodman]

Thanks. I think I have a case open to fix this because it causes the purchase price variance calculation for non-inventory PO items tagged to projects to look at the difference between invoice and PO rather than invoice and receipt to determine what amount to record as a purchase price variance.

The result is that, when the PO cost is changed between receipt and invoice, the purchase price variance adjustment is incorrect, leaving a balance in the purchase price variance account after the invoice is posted.