Skip to Content
avatar image
Former Member

Is it possible to have DocEntry Duplicates?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

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

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 29, 2017 at 07:54 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 29, 2017 at 03:39 PM

    Hi

    you do not need INV1 table in this query.

    Try the query again without this table.

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded