tdietrich
01/06/2022, 8:22 PMSELECT
SalesOrdered.Transaction,
Transaction.TranID,
BUILTIN.DF( Transaction.Entity ) AS EntityName,
-- BUILTIN.DF( TransactionAccountingLine.AccountingBook ) AS AccountingBookName,
Account.AcctNumber,
Account.Fullname AS AccountName,
TransactionAccountingLine.Debit,
TransactionAccountingLine.Credit,
TransactionAccountingLine.Posting,
TransactionLine.Memo
FROM
SalesOrdered
INNER JOIN Transaction ON
( Transaction.ID = SalesOrdered.transaction )
INNER JOIN TransactionAccountingLine ON
( TransactionAccountingLine.Transaction = Transaction.ID )
LEFT OUTER JOIN TransactionLine ON
( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine )
LEFT OUTER JOIN Account ON
( Account.ID = TransactionAccountingLine.Account )
WHERE
( SalesOrdered.Transaction = 460691 )
AND ( TransactionAccountingLine.Account IS NOT NULL )
AND ( TransactionAccountingLine.NetAmount <> 0 )
-- AND ( TransactionAccountingLine.Posting = 'T' )
ORDER BY
TransactionAccountingLine.TransactionLine
Note that the SalesOrdered table is really just a view on the Transaction table. You could clean this up a bit and eliminate the join to the Transaction table, but I'd leave it as is.