RE: Sales invoice line and GL Entry
I am not aware of a link between the Sales Invoice Line table and the G/L Entry table. This is one reason why unique document numbers across all posting, journals, document types, etc. are important. Many areas of the system rely on unique document numbers. Such as the "Find Entries" or the old "Navigate" function. You'll see this function works by Document Numbers and Date. If your Purchase Invoices and Sales Invoices use a generic XXXXX numeric number you will have duplicates across every posting. It's best to include PSI (Posted Sales Invoice), PPI (Posted Purchase Invoice), etc. in the document number. Also, use a number series for general journals.
In regards to linking these transactions, there are some links between transactions created upon posting. The G/L Register page will show you these. Each time you post something in the system a G/L Register is created.
The link between the G/L Entries created and the Customer Ledger Entry created is the Transaction No.
The link between the G/L Entries created and Item Ledger & Value Entries is the G/L - Item Ledger Relation table. This table also stores the G/L Register number.
Unfortunately these linking fields do not exist on posted document header or line tables. However from the posted document header/lines table you can get to the Customer Ledger Entry or Vendor Ledger Entry table where you can find the Transaction No. With this Transaction No. you can find all the G/L Entries that were created when this document was posted. With those G/L Entry numbers you can find the G/L Register that was created at the time of posting. With that G/L Register number you can find all the entries in the G/L - Item Ledger Relation table which will allow you to get all the Item Ledger and Value entries.
So you'll definitely have to join in a few tables.
The other thing to consider when trying to incorporate Value Entries is that additional Value Entries can get created over time due to cost adjustments and/or Item Charges. These new Value Entries will have been posted in a new G/L Register and will therefor have a new Transaction No.
Utilizing the above method will only find Value Entries that were created during the original posting. You'll need to incorporate into your report code to account for newly created Value Entries if you plan on reporting on cost.
Although with strictly unique document numbers you can reliably use document numbers.
Another thing: I just noticed that you want to relate each individual Sales Invoice Line to its corresponding GL Entries. This won't always be possible as the system will post single entries into certain accounts for different reasons. You will probably only get one Accounts Receivable entry even if you have 10 lines on the invoice for example.
