Skip to Content
-1

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

Mar 22 at 10:43 AM

77

avatar image

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

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Gábor Bánhidai 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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

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

Regards,

Johan

0
Johan Hakkesteegt Mar 23 at 06:24 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Aarati Kollur Mar 23 at 04:15 AM
0

Hello Gabor,

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

Share
10 |10000 characters needed characters left characters exceeded
Aarati Kollur Mar 26 at 04:23 AM
0

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.


Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Mar 26 at 06:19 AM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi,

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

0

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

0
Johan Hakkesteegt

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

0

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

0
Pradnya S 6 days ago
0

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

Share
10 |10000 characters needed characters left characters exceeded