Skip to Content

SAP B1 - Build query from line items in INV1 with common UDF

Hi there,

I am trying to build a query containing line items from the INV1 table in combination with some items of the OINV table. I managed to get a simple extract of the information I am looking for but my SQL skills are limited at best.

To simplify what I am looking for, I attached a small visual from Excel. Though I do not need the total sum on the query but can calculate the sum manually later on. Its just meant for illustration purpose. The line item prices are more relevant.

In essence, I am looking for a query, which returns all line items within the same batch number, if that batch contains Product A only. The INV1 might have other batches with other products, but the main functionality is to filter selective products and their relating customisations from an invoice. The result in the example above would return rows 1-3 and 7-8 with the relevant quantities and prices.

Thanks everybody in advance and I hope I am not too cryptic.


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jul 10, 2020 at 01:39 PM


    Try this query,

    SELECT T2.[CardCode], T2.[CardName], T2.[Address], T0.[DocNum],T4.[BaseNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T5.[DistNumber] BATCHNO,T5.[ExpDate], T1.[Quantity], T1.[PriceBefDi], T1.[DiscPrcnt], T1.[Price], T1.[LineTotal],T3.[WhsName] FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [dbo].[OCRD] T2 ON T0.[CardCode] = T2.[CardCode] LEFT JOIN [dbo].[OWHS] T3 ON T1.[WhsCode]= T3.[WhsCode] LEFT JOIN [dbo].[IBT1] T4 ON T0.[DocNum] =T4.[BaseNum] LEFT JOIN [dbo].[OBTN] T5 ON T4.[BatchNum]=T5.[DistNumber] WHERE T4.[BatchNum] = [%0]



    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 09, 2020 at 04:04 PM

    OINV table is the header table for INV1, so I don't think the question makes sense? OINV has no items.

    If you mean you want some fields from the OINV table joined to the INV1 table, they join together on DocEntry.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 09, 2020 at 06:34 PM

    Hi Charles,

    thanks for your response. I am aware of the OINV/INV1 structure and maybe I have been too cryptic in explaining my attempt.

    In short I am looking for a possibility extract information from the INV1 table. I would like to return all associated customisations to a particular ItemCode, e.g. 0001. When I run the query it should not only return the product line, but also the associated customisations which can be identified via the identical batch number. So if ItemCode if 0001, lookup Batch and return row 1, 2, 3 and 7,8,9

    I will add the OINV details, such as DocNum, etc later myself.

    Does this help to clarify ?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.