How to SQL join payments with invoices in NAV Dynamics (former Navision)

Question

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.

Answer 1

the table is

[Detailed Vendor Ledg. Entry]

with [Entry Type] = Application