Can a payment be applied to specific invoice lines? Can you query for that if it is possible?

AMarasco

I'm using Navision 2009 R2 and querying in SQL Server, can I find if a payment was applied to specific invoice lines (assuming that's possible)?

Here's what I'm currently querying:

SELECT
	pmt.[Entry No_]
	, inv.[Document No_]
	, d_inv.Amount
	, sil.[Line No_]
FROM dbo.[<Company>$Cust_ Ledger Entry] pmt -- payment entries
INNER JOIN dbo.[<Company>$Detailed Cust_ Ledg_ Entry] d_inv ON d_inv.[Applied Cust_ Ledger Entry No_] = pmt.[Entry No_] -- invoice detail lines where payment is applied to invoice
INNER JOIN dbo.[<Company>$Cust_ Ledger Entry] inv ON inv.[Entry No_] = d_inv.[Cust_ Ledger Entry No_] -- invoice entries
INNER JOIN dbo.[<Company>$Sales Invoice Line] sil ON sil.[Document No_] = inv.[Document No_]
WHERE
	pmt.[Document Type] = 1-- payment doc type
	AND inv.[Document Type] = 2 -- invoice doc type
	AND d_inv.[Unapplied] = 0 -- not unapplied

Obviously this is going to return all the sales invoice lines for a sales invoice document, but I'm trying to figure out if there's a way to check if a payment was applied to only specific lines in the sales invoice. Can that be queried?

bbrown

The short answer is no. Payments are applied to customer ledger entries. A posted invoice only creates a single customer ledger entry. Regardless of the number of document lines.