Is it possible to have DocEntry Duplicates?

[Former Member]

The Goal is to link Invoices with payments, but when I do it some of the Invoices are link to the same payment number.  

This is the code I´m using for this query 

SELECT DISTINCT   T2.[DocNum]  ,T2.[DocDate],
T0.[CardCode], T0.[CardName], T2.[DocTotal] ,
(T2.[DocTotal]-T2.[PaidToDate]) ,T0.[DocNum],
T0.[DocDate] , T0.[DocTotal] , T2.DocStatus, 
DateDiff(dd,T2.[DocDate],T0.[DocDate]) 
FROM ORCT T0  

INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum 
INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry 
INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry 

WHERE ( T0.[DocDate] between '2017-01-01' and '2017-06-26') AND  
T0.[Canceled] ='N' AND T0.PayNoDoc='N' 


I tried this Script for testing one of the Invoice with duplicate payment 
number 

SELECT T2.DocNum ,T0.DocNum AS DET0 ,T1.DocNum AS DNT1,T1.DocEntry as DET1 ,T2.DocEntry AS  DET2, T3.DocEntry AS DET3
FROM ORCT T0 
INNER JOIN RCT2 T1 ON T0.DocEntry= T1.DocNum 
LEFT JOIN  OINV T2 ON T1.DocEntry=T2.DocEntry
LEFT JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry  
WHERE T2.DocNum='22959'


 I get this results: 

DocNum      DET0        DNT1        DET1        DET2        DET3
----------- ----------- ----------- ----------- ----------- -----------
22959       4243        4243        6686        6686        6686
22959       24140       24140       6686        6686        6686

Can anybody help understand why this is happening and how can I solve this ? 

Thank you !!! 

[Former Member · Accepted solution]

Thank you for your anwsers, but I was able to solve the problem by joining ORCT and OINV like this:

FROM ORCT T0

INNER JOIN OINV T2 ON

T0.DocEntry = T2.ReceiptNum

[agustin_marcoscividanes]

Hi

you do not need INV1 table in this query.

Try the query again without this table.

Kind regards

Agustín Marcos Cividanes

[Johan_H]

Hi Daniel,

the system allows for a customer to make a single payment to cover multiple invoices. Therefore you may get multiple invoices for a single payment. So please first check the payment through the B1 client, and see if this is the case.

Regards,

Johan

[Former Member]

Thank you Johan,

I already checked on the B1 Client and is not the case 😕