Skip to Content
avatar image
-1
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 at 03:16 PM
    -2

    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

    H.U_U_end,

    A.CardCode,

    A.CardName,

    C.ZipCode, --4

    C.City,

    C.Street,

    A.CntctCode,

    H.Phone1, --8

    H.Fax,

    A.ShipToCode,

    A.TrnspCode,

    A.Comments, --12

    A.U_EKAER,

    A.NumAtCard,

    H.CardCode, --15

    H.CardName,--16

    A.AgentCode,

    A.SlpCode,

    A.[address],

    A.address2--20

    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 & ""

    Try

    recordsett.DoQuery(qry)

    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

    Try

    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

    Else

    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

    Else

    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

    Try

    If lineadded Then

    order.Lines.Add()

    Else

    lineadded = True

    End If

    Catch ex As Exception

    SBO_Application.SetStatusBarMessage(ex.Message.ToString)

    End Try

    eddigiqty = 0

    eddigiqty += .ExecutedQuantity

    elozoitemcode = .Articlenumber

    elozobbdatum = .BBDate

    End If

    ' order.Lines.Add()

    End With

    counterr += 1

    Next

    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 at 06:24 AM

    Hi,

    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?

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 23 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 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 at 06:19 AM

    Hi,

    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?

    Regards,

    Johan

    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

      Regards,

      Johan

  • avatar image
    Former Member
    Apr 17 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.

    Regards

    Pradnya S

    Add comment
    10|10000 characters needed characters exceeded