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

[aarati_kollur2]

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

[narayanis]

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

[Johan_H]

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

[aarati_kollur2]

Hi,

In invoice i selected one row and clicked on base document then inventory transfer documnet is opened.

[Johan_H]

Ok, that means that in a query you can join INV1 to OWTR and WTR1.

Something like this:

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

Regards,

Johan

[aarati_kollur2]

That's my question in inv1 table i am getting null in baseentry so i am not able to connect this both tables.

[Johan_H]

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

[aarati_kollur2]

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.

[Johan_H]

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

[aarati_kollur2]

Hello Gabor,

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

[Former Member]

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

[Johan_H]

Hi,

This answer is probably supposed to go to a different question?

Regards,

Johan