The question is about SQL fiddling in Dynamics Nav tables, especially the table [$Vendor Ledger Entry]. How to link each payment to appropriate invoices.
The aim is to get [External Document No_] and [Amount] of the invoice. One payment can be matched with many invoices (happens) or one invoice with many payments (rare case).
My desperate approach is:
;with cte as
(
select
*
,[Row_max]=max([Row]) OVER(PARTITION BY CBEN,[Document Type])
from (
select
*
,[Row]=ROW_NUMBER() OVER(PARTITION BY CBEN,[Document Type] ORDER BY [Entry No_] asc)
from (
select
*
,CBEN=case when [Closed by Entry No_]=0 then [Entry No_] else [Closed by Entry No_] end
from [CompanyName$Vendor Ledger Entry] --here put correct table name
) tb1
) tb2
)
select
a.[Entry No_] [Entry No_payment] -- letter t means transfers
,b.[Entry No_] [Entry No_invoice] -- letter p means payables
--,a.CBEN as CBEN_t
--,b.CBEN as CBEN_p
--,a.[Row_max] as Row_max_t
--,b.[Row_max] as Row_max_p
--,a.[Row] as Row_t
--,b.[Row] as Row_p
,a.[Open]
,b.[External Document No_] [External Document No_invoice]
,a.[Document No_] [Document No_payment]
,b.[Document No_] [Document No_invoice]
,b.[Document Date]
,b.[Posting Date]
,b.[Due Date]
,Amount_p=b.[Closed by Amount]
--,a.[Company]
from cte a
left join cte b
on
(
a.CBEN=b.CBEN and -- join using Closed By Entry No
(a.[Row]=b.[Row] and a.[Row_max]=b.[Row_max] or
a.Row_max=1 and not (a.[Row_max]>1 and b.[Row_max]>1) or
b.Row_max=1 and not (a.[Row_max]>1 and b.[Row_max]>1)
) and
a.[Document Type] in (0,1) and
b.[Document Type]=2
and a.[Open]=0
)
or
(
a.CBEN = b.[Entry No_] and -- or join by Closed By Entry No to EntryNo
(a.[Row]=b.[Row] and a.[Row_max]=b.[Row_max] or
a.Row_max=1 and not (a.[Row_max]>1 and b.[Row_max]>1) or
b.Row_max=1 and not (a.[Row_max]>1 and b.[Row_max]>1)
) and
a.[Document Type] in (0,1) and
b.[Document Type]=2
and a.[Open]=0
)
where
a.[Open]=0 and a.[Document Type] in (0,1) and b.[Entry No_] is not null -- show payments with matched invoices
or
a.[Open]=1 and a.[Document Type] in (0,1) and b.[Entry No_] is null -- or payments with open status
order by a.CBEN,1,2,a.[Document No_]
I am at a loss to join Invoice [Amount] and I do not know in which Dynamics NAV table it might be.
