You may wish to join up to tciTranTypeCompany and/or tsmLocalString.

I don't believe any of the tables will give you the full text of 'Invoice'. But tcuTranTypeCompany should hold values like the following in the TranTypeID field:

IN

CM

DM

FC

CS

SO

RM

If those are sufficient, you can simply use a variation of this query:

Select T.TranTypeID, *

FROM tarcustdoctrnsmit D (NOLOCK)

JOIN tarcustomer C (NOLOCK) ON D.CustKey = C.CustKey

JOIN tciTranTypeCompany T (NOLOCK) on D.TranType = T.TranType and C.CompanyID = T.CompanyID

If you need the full text you can include a CASE statement like this:

Select D.*,

CASE

WHEN T.TranTypeID = 'IN' THEN 'Invoice'

WHEN T.TranTypeID = 'CM' THEN 'Credit Memo'

WHEN T.TranTypeID = 'DM' THEN 'Debit Memo'

WHEN T.TranTypeID = 'FC' THEN 'Finance Charge'

WHEN T.TranTypeID = 'CS' THEN 'Customer Statement'

WHEN T.TranTypeID = 'SO' THEN 'Sales Order'

WHEN T.TranTypeID = 'RM' THEN 'Returned Mdse. Auth.'

END AS TranTypeAsText

FROM tarcustdoctrnsmit D (NOLOCK)

JOIN tarcustomer C (NOLOCK) ON D.CustKey = C.CustKey

JOIN tciTranTypeCompany T (NOLOCK) on D.TranType = T.TranType and C.CompanyID = T.CompanyID

Invoice as a string 'Invoice' is stored in tsmLocalString.LocalText column. Another way to accomplish the same would be the join:

tciTranType LEFT OUTER JOIN tsmLocalString ON tciTranType.TranDescStrNo = tsmLocalString.StringNo

Thanks Zenon, you're absolutely right. So I would change the final query to this:

Select S.LocalText as 'TranTypeAsText', D.*

FROM tarcustdoctrnsmit D (NOLOCK)

JOIN tarcustomer C (NOLOCK) ON D.CustKey = C.CustKey

JOIN tciTranType T (NOLOCK) on D.TranType = T.TranType --and C.CompanyID = T.CompanyID

LEFT OUTER JOIN tsmLocalString S (NOLOCK) ON T.TranDescStrNo = S.StringNo

If you are going to use a TranTypeId in what you are doing, you should get it from tciTranTypeCompany.  Each company can override the values for the TranTypeID.  While it will be the same if the company in play did not do this (on AR Options under the transaction types button), they may have.  So to use it, the proper thing to use is the version found in tciTranTypeCompany so that if any of the companies in your database ever change the values, the object your are creating will pick this up.  If you are not going to use the TranTypeID, then ignore this message.

Thank you all for your help, it was a great learning experience.  I used the most recent select statement that D Hart posted and it worked!