The complexity comes in with Posting Types 82 and 236. For these posting types, I want to display the related PO lines, including the item ID, item name, quantity, and line amount. Although I can join the PO line tables to my current query, this causes duplication because the join occurs at the MainAccount level. For example, voucher APO00900 has two voucher transaction lines, both with Posting Type 236. One line is posted to MainAccount 12350, and the other is posted to MainAccount 67890. Both lines reference the same PurchId (PO-09090).When reviewing the purchase order, it contains three lines. Two of those PO lines roll up to the total posted to MainAccount 12350, while the remaining PO line matches the transaction amount posted to MainAccount 67890.If I add the PO lines directly to my current query, each PO line ends up matching both main accounts. This results in six records being returned instead of the expected three. I have been trying to determine whether there is another entity or relationship that can be used to correctly associate the voucher transaction, the main account, and the corresponding PO line(s) but found none.
Please let me know if I can provide any additional information. I would really appreciate any guidance!
[I created a view to make things cleaner for the GL Data:
SELECT GJ.LEDGERACCOUNT, SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0)) AS MAINACCOUNTID,GJ.TEXT AS DESCRIPTION, GJ.TRANSACTIONCURRENCYAMOUNT AS TAMOUNT,
CASE WHEN GJ.ISCREDIT = 1 THEN GJ.TRANSACTIONCURRENCYAMOUNT END AS CREDIT,
CASE WHEN GJ.ISCREDIT = 0 THEN GJ.TRANSACTIONCURRENCYAMOUNT END AS DEBIT,
GJ.POSTINGTYPE AS TYPE, GJ.POSTINGTYPE_$LABEL AS POSTINGTYPE,
G.ACCOUNTINGDATE AS DATE, G.JOURNALCATEGORY, G.DOCUMENTNUMBER, G.JOURNALNUMBER, G.SUBLEDGERVOUCHER AS VOUCHER, UPPER(G.SUBLEDGERVOUCHERDATAAREAID) AS LEDGERNAME, G.JOURNALCATEGORY_$LABEL, G.RECID,
MA.NAME
FROM GENERALJOURNALACCOUNTENTRY GJ
JOIN GENERALJOURNALENTRY G ON G.RECID = GJ.GENERALJOURNALENTRY
JOIN MAINACCOUNT MA ON MA.MAINACCOUNTID = SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0))
WHERE SUBSTRING(GJ.LEDGERACCOUNT,0,CHARINDEX('-',GJ.LEDGERACCOUNT,0)) > 'x9999' ]
