Skip to Content
0

Is it possible to have DocEntry Duplicates?

Jun 29, 2017 at 07:38 AM

58

avatar image
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 !!! 

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
avatar image
Former Member Jun 29, 2017 at 05:05 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Jun 29, 2017 at 07:54 AM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you Johan,

I already checked on the B1 Client and is not the case :/

0
Agustin Marcos Cividanes Jun 29, 2017 at 03:39 PM
1

Hi

you do not need INV1 table in this query.

Try the query again without this table.

Kind regards

Agustín Marcos Cividanes

Share
10 |10000 characters needed characters left characters exceeded