NAV 2009: Open Invoices

[Jason]

I am trying to write a sql query that will return a list of open sales
invoices and include what the invoice amount is and how much is remaining. I
see the table [Sales Invoice Header] but it doesn't seem to show what the
current amount due is on an invoice. What table do I need to join to? Is
there a document that talks about how the tables all link?

[Luc Van Dyck]

"Jason" wrote:

> I am trying to write a sql query that will return a list of open sales
> invoices and include what the invoice amount is and how much is remaining. I

You should use the table "Customer Ledger Entry" instead. This table has a
field "Remaining Amount" which tells you the amount which needs to be payed.
A posted Sales Invoice results in one record in the Customer Ledger Entry
table.

Use the field "Document Type" to filter out only Invoices.

Luc Van Dyck
webmaster http://mibuso.com
"Your favourite knowledge base"

[Volker Strähle]

Hi Jason,

why don't You use report 106?

Volker

[Jason]

"Luc Van Dyck" wrote:

> "Jason" wrote:
>
> > I am trying to write a sql query that will return a list of open sales
> > invoices and include what the invoice amount is and how much is remaining. I
>
> You should use the table "Customer Ledger Entry" instead. This table has a
> field "Remaining Amount" which tells you the amount which needs to be payed.
> A posted Sales Invoice results in one record in the Customer Ledger Entry
> table.
>
> Use the field "Document Type" to filter out only Invoices.
>

Thanks. Just what I was looking for.

[Volker Strähle]

Hi Jason,

in this case You could also trace the SQL-queries when running a report from
NAV.

Volker

[lalv...@gmail.com]

DECLARE @AsofDate AS datetime

SET @AsofDate = '02/28/13'

select
-- L.[Entry No_],

CASE L.[Document Type]
WHEN '1' THEN ' Payment'
WHEN '2' THEN 'Invoice'
WHEN '3' THEN 'Credit'
WHEN '4' THEN 'Finance'
WHEN '5' THEN 'Reminder'
WHEN '6' THEN 'Refund'
ELSE ''
END AS DocType,
L.[Document No_],
CASE when L.[Open] = 1 then 'Y' else 'N' end as 'Open',
L.[Cust_ Summary Invoice No_],
P.[Ending Date] as PeriodEndDate,
C.[Salesperson Code] as 'Slsm',
SP.Name as 'SlsmName',
L.[Customer No_],
L.[Posting Date],
L.[Document Date],
L.[Due Date],
L.[Closed at Date] AS ClosedDate ,
case when L.[Open] = 0 THEN
DATEDIFF (DAY,L.[Document Date],L.[Closed at Date]) else
''
end as 'DaysToClose',
case when L.[Open] = 1 THEN
DATEDIFF (DAY,L.[Document Date],@AsofDate) else
''
end as 'DaysOpen',
OA.OrigAmt,
DL.RemainingAmt

from
dbo.[xx$Cust_ Ledger Entry] L
left join
(select
D.[Cust_ Ledger Entry No_],
SUM(D.Amount) as RemainingAmt
FROM
dbo.[xx$Detailed Cust_ Ledg_ Entry] D with(nolock)
-- where
-- D.[Posting Date] <= @AsofDate
GROUP BY
D.[Cust_ Ledger Entry No_]
) DL
ON L.[Entry No_] = DL.[Cust_ Ledger Entry No_]
left join
(select
D.[Cust_ Ledger Entry No_],
D.Amount AS OrigAmt
FROM
dbo.[xx$Detailed Cust_ Ledg_ Entry] D with(nolock)
where
D.[Entry Type] = 1
-- AND D.[Posting Date] <= @AsofDate
) OA
ON L.[Entry No_] = OA.[Cust_ Ledger Entry No_]
left join dbo.[Hi Touch$Customer] C
on C.No_ = L.[Customer No_]
left join
dbo.[Hi Touch$Salesperson_Purchaser] SP with(nolock)
on C.[Salesperson Code] = SP.[Code]
LEFT JOIN dbo.[Hi Touch$Customer Accounting Period] P with(nolock)
ON P.[Cust_ Summary Invoice No_] = L.[Cust_ Summary Invoice No_] and L.[Cust_ Summary Invoice No_] <>''

where
L.[Open] = 1

order by
C.[Salesperson Code],
C.No_,
L.[Document Date]