Update UDF in a Document

[former_member193355]

Dear Experts,

Could somebody help me how to update an user  field in production order ?

Example U_ActualCap in the OWOR. I used this following code to get the value or amount the udf U_ActualCap in the production order

I made this following code but failed.

Private Sub ProductionOrder_InsertValue(ByVal DocDate As Date, ByVal oProductionOrderDocEntry As String, ByVal oProductionOrderDocNum As String, ByVal oProductOrder As String, ByRef IsError As Boolean, ByVal SBO_Application As SAPbouiCOM.Application, ByVal oCompany As SAPbobsCOM.Company)

        Dim oRec As SAPbobsCOM.Recordset

        oRec = CType(oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset), SAPbobsCOM.Recordset)

        Dim lRetCode As Integer = 0

        Dim lErrCode As Integer = 0

        Dim sErrMsg As String = ""

        Dim MainProductCode As String = ""

        Dim oResource As Decimal = 0

        oRec.DoQuery("select ItemCode from OWOR where DocEntry= '" & oProductionOrderDocEntry & "'")

        MainProductCode = oRec.Fields.Item("ItemCode").Value.ToString

        oRec.DoQuery("exec GetValueResourceActual """ + MainProductCode + """,""" + oProductionOrderDocNum + """")

        oResource = oRec.Fields.Item("ResourceAmount").Value.ToString

        '-Production Order

        Dim oProdOrder As SAPbobsCOM.ProductionOrders

        Dim isLineExist As Boolean = False

        oProdOrder = CType(oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders), SAPbobsCOM.ProductionOrders)

       If Round(oResource, 2) <> 0 Then

            oProdOrder.UserFields.Fields.Item("U_ActualCap").Value = oResource

            isLineExist = True

        End If

        If isLineExist Then

            lRetCode = oProdOrder.Update()

            If lRetCode <> 0 Then

                IsError = True

                oCompany.GetLastError(lErrCode, sErrMsg)

                SBO_Application.MessageBox(lErrCode.ToString & " : " & sErrMsg)

            Else

                IsError = False

                If oProdOrder.GetByKey(CInt(oCompany.GetNewObjectKey)) Then

                    oProductOrder = oProdOrder.DocNum.ToString

                End If

            End If

        End If

        oProdOrder = Nothing

    End Sub

How can I use the above code to insert its result  in the UDF by using the SAP SDK?

Pls help to solve this issue.

THank you

Regards,

Steve

[edy_simon · Accepted solution]

Hi Steve,

After you have initialized the Production order,

You have not get the Record that you want to update.

Use

   oProdOrder = CType(oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders), SAPbobsCOM.ProductionOrders)
oProdOrder.GetByKey(TheDocEntryToUpdate)     '<---- Add this line

Peace be with you,

Edy

[former_member193355 · Accepted solution]

Dear Edy,

Thanks for your answer.

There is an error message:

Illegal value entered and the addd-on didn't work as  expected.

I change to the following code:

If Round(sAccount, 2) <> 0 Then

            oItemReval.UserFields.Fields.Item("U_ACTUALCAP").Value = sAccount.ToString

but failed.

Pls help

Thanks

Regards,

Steve

[edy_simon · Accepted solution]

Hi Steve,

Ilegal Value entered, most probably, means the UDF data type and the value you are trying to insert into it is not compatible.

Can you check the data type of your UDF ?

Regards

Edy

[former_member193355 · Accepted solution]

Dear Edy,

Thank you for your answer. I have checked it.

The udf data type is unit and total. The structure is Amount. So, it will be Numeric (19,6)

In the vb.net, I set as decimal as follows:

Dim oResource As Decimal = 0

After I change to the data type unit and total with structure amount, the new error message appears as follows:

Pls help to solve this issue. Thank you

Rgds,

Steve

[former_member193355 · Accepted solution]

Dear Edy,

I've tried to remove the Private Sub ProductionOrder_InsertValue to avoid the above error.

So, I am using this following

Public Sub FormDataEvent_Handler_ProductionOrder(ByVal BusinessObjectInfo As SAPbouiCOM.BusinessObjectInfo, ByRef BubbleEvent As Boolean, ByVal SBO_Application As SAPbouiCOM.Application, ByVal oCompany As SAPbobsCOM.Company)

        If BusinessObjectInfo.EventType = SAPbouiCOM.BoEventTypes.et_FORM_DATA_LOAD Then

            If BusinessObjectInfo.BeforeAction = False Then

                DisableEnable(BusinessObjectInfo.FormUID, SBO_Application, oCompany)

            End If

        End If

        If BusinessObjectInfo.EventType = SAPbouiCOM.BoEventTypes.et_FORM_DATA_UPDATE Then

            Dim oForm As SAPbouiCOM.Form = SBO_Application.Forms.Item(BusinessObjectInfo.FormUID)

            ' If BusinessObjectInfo.BeforeAction = False And BusinessObjectInfo.ActionSuccess = True Then

            If BusinessObjectInfo.BeforeAction = True Then

                Try

                    If oForm.DataSources.DBDataSources.Item("OWOR").GetValue("U_PRODUCT", 0) = "Y" And oForm.DataSources.DBDataSources.Item("OWOR").GetValue("Status", 0) = "L" Then

                        Dim oProductionOrderDocEntry As String = oForm.DataSources.DBDataSources.Item("OWOR").GetValue("DocEntry", 0)

                        Dim ProductionOrderDocNum As String = oForm.DataSources.DBDataSources.Item("OWOR").GetValue("DocNum", 0)

                        Dim ItemCode As String = oForm.DataSources.DBDataSources.Item("OWOR").GetValue("ItemCode", 0)

                        Dim oUDFForm As SAPbouiCOM.Form = SBO_Application.Forms.Item(oForm.UDFFormUID)

                        Dim oRec As SAPbobsCOM.Recordset

                        Dim Resource As String

                        oRec = CType(oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset), SAPbobsCOM.Recordset)

                        oRec.DoQuery("exec GetValueResourceAct """ + ItemCode + """,""" + ProductionOrderDocNum + """")

                        Resource= oRec.Fields.Item("ResourceAct").Value.ToString

                        oUDFForm.Items.Item("U_ACTUALCAP").Specific.String = Resource

                    End If

                Catch ex As Exception

                    SBO_Application.MessageBox(ex.Message)

                End Try

            End If

            If BusinessObjectInfo.BeforeAction = False And BusinessObjectInfo.ActionSuccess = True Then

                Try

                    If oForm.DataSources.DBDataSources.Item("OWOR").GetValue("U_PRODUCT", 0) = "Y" And oForm.DataSources.DBDataSources.Item("OWOR").GetValue("Status", 0) = "L" Then

                        Dim oProductionOrderDocEntry As Integer = oForm.DataSources.DBDataSources.Item("OWOR").GetValue("DocEntry", 0)

                        RunActionProductionOrder(BusinessObjectInfo.FormUID, SBO_Application, oCompany)

                    End If

                Catch ex As Exception

                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)

                    SBO_Application.MessageBox(ex.Message)

                End Try

            End If

        End If

    End Sub

There is no more error but the UDF U_ACTUALCAP only filled with value 0.00. Meanwhile if I execute the SP in the SQL, there is a result.

Pls kindly help to solve this issue. Thank you

Rgds,

Steve

[edy_simon · Accepted solution]

Hi Steve,

Put a break on this line

oUDFForm.Items.Item("U_ACTUALCAP").Specific.String = Resource

What is the Resource value  on your debug ?

Regards

Edy

[former_member193355 · Accepted solution]

Hi Edy,

Thank you for your answer.

Here is the result:

Pls kindly help to provide this issue

Rgds,

Steve

[edy_simon · Accepted solution]

Hi Steve,

From your debug screen,

the value of resource you get from

oRec.DoQuery("exec GetValueResourceAct """ + ItemCode + """,""" + ProductionOrderDocNum + """")

                        Resource= oRec.Fields.Item("ResourceAct").Value.ToString

returns you a 0.00 value.

And you put this value into your U_ACTUALCAP.
There is nothing wrong with the Code.

If you feel that the Resource should not 0.00, then you need to check your SP.

Try this

Dim sSQL as string = "exec GetValueResourceAct """ + ItemCode + """,""" + ProductionOrderDocNum + """"

Debug this line, copy the value of sSQL into your SQL Management Studio and run it.

Regards
Edy

[former_member193355 · Accepted solution]

Dear Edy,

Yes, exactly. The debug screen have different result with the executed SP query result. In the SQL M. studio, the query gives the correct result. It is not 0.00.

I will try your advice and will let you know the result soon.

Rgds,

Steve

[former_member193355 · Accepted solution]

Dear Edy,

I have tested your solution but it gave error as seen in  the print screen belows:

I made some changes in code

Dim oRec As SAPbobsCOM.Recordset = CType(oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset), SAPbobsCOM.Recordset)

Dim sSQL as string = "exec GetValueResourceAct """ + ItemCode + """,""" + ProductionOrderDocNum + """"

Ssql = String.Format(SSql, ItemCode, ProductionOrderDocNum)

oRec.DoQuery(sSQL)

Dim Resource As String = oRec.Fields.Item("Resource").Value

If sSQL.ToString <> 0 Then

'not found

oUDFForm.Fill("U_ACTUALCAP")

But it is failed and throwing an error message like the print screen. Pls kindly solve the problem.

Rgds,

Steve

[edy_simon · Accepted solution]

Hi Steve,

Why this line

     Resource= oRec.Fields.Item("ResourceAct").Value.ToString

become this line  ?

     Dim Resource As String = oRec.Fields.Item("Resource").Value

What does your SP returns ? ResourceAct ? or Resource ? column.

And Also

     Dim sSQL as string = "exec GetValueResourceAct """ + ItemCode + """,""" + ProductionOrderDocNum + """"

     Ssql = String.Format(SSql, ItemCode, ProductionOrderDocNum)

Why the need of the String.Format ? remove that line.

put a break on this line

     oRec.DoQuery(sSQL)

Debug and see the value of sSQL, copy it and run it under your SQL management studio.

What result do you see under the SQL management studio ?

Regards
Edy