Skip to Content
avatar image
Former Member

How to link Inventory transfer and A/R or A/P Invoice using query in sap b1

We wanted to know number of stock transfers open from one location to another location with detailed information.

How i will link Inventory transfer OWTR & WTR1 with OINV & INV1

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • avatar image
    Former Member
    Mar 22, 2018 at 03:16 PM

    this should put the new publication up. This is a multi-version, I have tried all kinds of stuff, just put an article, just one item without batch, several items, several items without batch, many times overwritten the code just as I remembered.
    I tried to vary a document type, I tried to vary item processing, etc. when an error comes back that "you can not add rows without the full selection of the item" you should normally not come back. (Ret = 0)

    SAP B1 9.2

    qry = "select H.U_U_from, --0




    C.ZipCode, --4




    H.Phone1, --8




    A.Comments, --12



    H.CardCode, --15






    From ORDR A

    join RDR1 B on A.DocEntry=B.DocEntry

    join Ocrd H on H.CardCode=A.CardCode

    join Crd1 C On C.Address = COALESCE(A.ShipToCode, A.PayToCode) AND C.CardCode = H.CardCode

    Where A.DocEntry =" & .OrderNumber & ""



    order.CardCode = recordsett.Fields.Item(2).Value.ToString

    order.CardName = recordsett.Fields.Item(3).Value.ToString

    order.Address = recordsett.Fields.Item(19).Value.ToString

    order.Address2 = recordsett.Fields.Item(20).Value.ToString

    order.AgentCode = recordsett.Fields.Item(17).Value.ToString

    order.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Items

    order.DocumentSubType = SAPbobsCOM.BoDocumentSubType.bod_None

    order.HandWritten = SAPbobsCOM.BoYesNoEnum.tNO

    'order.SalesPersonCode = recordsett.Fields.Item(18).Value.ToString

    'Dim datte As String = .TranDate.ToString("yyyy/MM/dd HH:mm:ss")

    'Dim dattte As Date

    'Date.TryParseExact(datte, "yyyy/MM/dd HH:mm:ss", CultureInfo.CurrentCulture, DateTimeStyles.AssumeLocal, dattte)

    order.DocDueDate = .TranDate 'dattte

    order.StartDeliveryDate = .TranDate 'DateTime.ParseExact(.TranDate, "MM/dd/yyyy hh:mm:ss tt", Nothing)

    Catch ex As Exception

    End Try

    End With


    Dim counterr As Integer = 0

    Dim lineadded As Boolean = False

    For Each ii As WebInterfacesCore.BaseClasses.ShapeClassesResponseMessageItem In cc.WebItems

    With ii

    m += "--Item--" & vbCr &

    "Article Number: " & .Articlenumber.ToString & vbCr &

    "Batch: " & .Batch.ToString & vbCr &

    "BB Date: " & .BBDate.ToString & vbCr &

    "Error Code: " & .ErrorCode.ToString & vbCr &

    "Executed Quantity: " & .ExecutedQuantity.ToString & vbCr &

    "Item Line Id: " & .ItemLineId.ToString & vbCr &

    "Ordered Quantity: " & .OrderedQuantity.ToString & vbCr &

    "Refused Quantity: " & .RefusedQuantity.ToString & vbCr &

    "Sscc: " & .Sscc.ToString & vbCr &

    "Status: " & .Status.ToString & vbCr &

    "Unit: " & .Unit.ToString & vbCr &

    "--/Item--" & vbCr

    If elozoitemcode Is Nothing Then

    elozoitemcode = .Articlenumber

    eddigiqty += .ExecutedQuantity

    elozobbdatum = .BBDate

    elozobatch = .Batch

    recordsett.DoQuery("select top 1 * from oibt A

    join obtn B on B.DistNumber = A.BatchNum and A.usersign = B.usersign and A.itemcode=B.itemcode

    where A.itemcode = '" & .Articlenumber & "'

    order by B.SysNumber desc


    ElseIf .Articlenumber = elozoitemcode And elozobatch = .Batch And counterr < cc.WebItems.Count Then

    eddigiqty += .ExecutedQuantity


    recordsett.DoQuery("Select top 1 batchnum, A.expdate, A.BaseLinNum, A.InDate from oibt A

    Join obtn B on B.DistNumber = A.BatchNum And A.usersign = B.usersign And A.itemcode=B.itemcode

    where A.itemcode = '" & elozoitemcode & "'

    order by B.SysNumber desc")

    ' order.Lines.BaseType = SAPbobsCOM.BoAPARDocumentTypes.bodt_DeliveryNote

    order.Lines.Quantity = eddigiqty

    order.Lines.ItemCode = elozoitemcode

    If recordsett.Fields.Item(0).Value.ToString = String.Empty Then

    order.Lines.BatchNumbers.BatchNumber = Nothing


    order.Lines.BatchNumbers.BatchNumber = recordsett.Fields.Item(0).Value.ToString

    order.Lines.BatchNumbers.ExpiryDate = CDate(recordsett.Fields.Item(1).Value.ToString)

    order.Lines.BatchNumbers.Quantity = eddigiqty

    order.Lines.BatchNumbers.BaseLineNumber = CInt(recordsett.Fields.Item(2).Value.ToString)

    order.Lines.BatchNumbers.AddmisionDate = CDate(recordsett.Fields.Item(3).Value.ToString)

    End If


    If lineadded Then



    lineadded = True

    End If

    Catch ex As Exception


    End Try

    eddigiqty = 0

    eddigiqty += .ExecutedQuantity

    elozoitemcode = .Articlenumber

    elozobbdatum = .BBDate

    End If

    ' order.Lines.Add()

    End With

    counterr += 1


    order.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Items

    Dim ret As Integer = order.Add()

    If ret <> 0 Then

    Dim errorcode As Long

    Dim errormsg As String

    company.GetLastError(errorcode, errormsg)

    End If

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 23, 2018 at 06:24 AM


    When you create an invoice (OINV & INV1) the system does not create records in the warehouse transfer tables (OWTR & WTR1). When you create a warehouse transfer (OWTR & WTR1), you cannot invoice (OINV & INV1) that transaction.

    Can you please describe the business process that links these two types of transactions in your scenario?



    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 23, 2018 at 04:15 AM

    Hello Gabor,

    I am just asking the query to link OWTR & WTR1 with OINV & INV1.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 26, 2018 at 04:23 AM

    See int his relationship map Inventory transfer is linked with A/R invoice. We are using Indian localization. So how i will get the data linked with transfer and invoice.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 26, 2018 at 06:19 AM


    Could you please open the invoice from your screenshot, select one of the rows that is in the inventory transfer, and click the Base document... button?

    Does that open the inventory transfer or another document?



    Add comment
    10|10000 characters needed characters exceeded

    • That is because when an invoice is not based on a base document, this field is NULL.

      Please try this:

      FROM INV1 ir
           INNER JOIN OWTR th ON ISNULL(ir.BaseEntry, -1) = th.DocEntry
                          AND ISNULL(ir.BaseType, -1) = ??? /* <-- you need to find
                                                                   out the base type
                                                                   number for stock
                                                                   transfers */
           INNER JOIN WTR1 tr ON ISNULL(ir.BaseLine, -1) = tr.LineNum AND th.DocEntry = tr.DocEntry



  • avatar image
    Former Member
    Apr 17, 2018 at 04:08 AM

    Dear Aarti,

    In OINV table there are two fields BaseEntry & BaseType which links with OWTR table.

    Itemcode field is common between WTR1 & INV1 tables.

    Hope this will help you.


    Pradnya S

    Add comment
    10|10000 characters needed characters exceeded