Skip to Content

Query to find Unlinked Invoices/Delivery in SAP Business One

Hi Team,

I need a query to find unlinked invoices and unlinked deliveries. Unlinked as in Document is standalone, there is not other document associated with this in relationship Map.

Regards,

Krishnam

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 14 at 09:48 AM

    Hello Krishnam...there is a field on the Line Tables titled BaseRef. When a Delivery or AR Invoice is "standalone", then that field is not filled out. I was not sure what data you want on the list, so I am writing this as if you just want the Document Number of a Delivery or AR Invoice. Also added AR Credit Memos (RIN1/ORIN), in case you wanted to see those also. Many Customers want to see the AR Credit Memo included, so just added a new UNION ALL. If not needed, you can easily remove it. Myabe you want to include Returns also (RDN1 and ORDN)???

    You might want to check these blogs on Relationships and LineNum:

    Recreate the Relationship Map

    LineNum or Visorder

    It might help explain the connections a little bit better.

    Here is some code to test it out. Please let us know if this SQL works for you. If you want more information besides Document Number, please ensure you put the new field (i.e., DocDate, etc.) in all three sections, since a UNION ALL requires all sections to have the same number of columns.

    Many thanks and regards, Zal

    PS - if you want to see all lines or want to include the Item Code, you can remove the T0.LineNum = 0 and add the addtional information from the line tables.

    SELECT DISTINCT
    'AR Delivery' AS 'Document',
    T1.DocNum
    
    FROM DLN1 T0
    LEFT OUTER JOIN ODLN T1 
    ON T0.DocEntry = T1.DocEntry
    
    WHERE 
    T0.LineNum = 0
    AND (T0.BaseRef IS NULL OR T0.BaseRef = ' ')
    
    UNION ALL
    
    SELECT DISTINCT
    'AR Invoice' AS 'Document',
    T1.DocNum
    
    FROM INV1 T0
    LEFT OUTER JOIN OINV T1 
    ON T0.DocEntry = T1.DocEntry
    
    WHERE 
    T0.LineNum = 0
    AND (T0.BaseRef IS NULL OR T0.BaseRef = ' ')
    
    UNION ALL
    
    SELECT DISTINCT
    'AR Credit Memo' AS 'Document',
    T1.DocNum
    
    FROM RIN1 T0
    LEFT OUTER JOIN ORIN T1 
    ON T0.DocEntry = T1.DocEntry
    
    WHERE 
    T0.LineNum = 0
    AND (T0.BaseRef IS NULL OR T0.BaseRef = ' ')
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11 at 07:44 AM

    Hi Krishnam,

    Here below the requested query for the A/P invoice

    SELECT t1.Docnum,t1.Docdate,t1.CardCode,t1.CardName,t1.Doctotal,t1.JrnlMemo 
    FROM OPCH t1 inner Join PCH1 t2 on t1.Docentry=t2.Docentry 
     WHERE t2.BaseEntry is null and t2.TrgetEntry is null and t2.Flags=0 and t2.VisOrder=0
    

    Thank you,

    Aziz El Mir

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12 at 10:35 AM

    Hi,

    Try this query,

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[ItemCode], T1.[Quantity], T3.[DocNum], T3.[DocDate], T2.[ItemCode], T2.[Quantity] FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T1.[LineNum] LEFT JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry]

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded