Validate BOM

Validate BOM Item Code,Project and Allow Procurement  for Sales Order

 IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'17')  
 BEGIN  
 set @entry= (select distinct DOCENTRY from RDR1 where DocEntry=@list_of_cols_val_tab_del )  
 IF EXISTS (SELECT DISTINCT T1.ITEMCODE FROM [DBO].RDR1 T1 WHERE T1.LinePoPrss='Y' )  
 BEGIN  
  IF NOT Exists (SELECT DISTINCT T1.ItemCode FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE where T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Item Code BOM is missing !'  
 END  
 ELSE IF NOT EXISTS( SELECT DISTINCT T1.Project FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE AND T1.PROJECT IS not NULL WHERE  T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Project Code is missing !'  
 END  
 ELSE IF NOT EXISTS( SELECT DISTINCT T1.LinePoPrss FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE AND  T1.LinePoPrss='Y'  WHERE  T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Allow Procurement is not checked !'  
 END  
 END  
 END  

Convert Date From SAP

Convert Date From SAP

  Private Function ConvertDateFromSAP(ByVal strDate As String) As String  
       Return Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4)  
     End Function  

Get Remarks of Item Code using SAP Business one Studio Suite

Get Remarks of Item Code using SAP Business one Studio Suite

  Private Sub GetRemarksofItemcode()  
       Dim oRsOITM As SAPbobsCOM.Recordset = Nothing  
       Dim strQuery As String = String.Empty  
       Dim strItemCode As String = String.Empty  
       Dim oCell As SAPbouiCOM.Cell  
       Dim oEditText As SAPbouiCOM.EditText  
       Dim oCol As SAPbouiCOM.Column  
       oRsOITM = oApplication.Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)  
       For index As Integer = 1 To Matrix0.VisualRowCount  
         oCol = Matrix0.Columns.Item("7")  
         oCell = oCol.Cells.Item(index)  
         oEditText = DirectCast(oCell.Specific, SAPbouiCOM.EditText)  
         'strItemCode = DirectCast(Matrix0.Columns.Item("7").Cells.Item(index).Specific, SAPbouiCOM.EditText).Value.Trim()  
         strItemCode = oEditText.String  
         strQuery = "Select ValidComm from OITM where Itemcode='" & strItemCode & "'"  
         oRsOITM.DoQuery(strQuery)  
         If oRsOITM.RecordCount > 0 Then  
           SetMatrixValues(Matrix0, "u_ea_Remarks", index, oRsOITM.Fields.Item("ValidComm").Value)  
         Else  
           SetMatrixValues(Matrix0, "u_ea_Remarks", index, String.Empty)  
         End If  
   
       Next  
     End Sub  

Display Supplier Catelog Number in BOM Item Code

Display Supplier Catelog Number in BOM Item Code

 SELECT Distinct SuppCatNum FROM OITM  Where Itemcode= $[$3.1.0]  

Query for PO Draft

Query for PO Draft 

 IF @transaction_type In ( 'A' ,'U') AND @object_type = '112'  
 BEGIN  
 if exists(select * from ODRF t1 inner join DRF1 t2 on t1.DocEntry =t2.DocEntry where  
  t1.DoCENTRY =@list_of_cols_val_tab_del and t1.ObjType = 22 AND isnull(T2.Project,'') = '')  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Project Code is missing !'  
 END  
 END  

TDS Report Section Wise

TDS Report Section Wise

 SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate],T0.[BaseAmnt], T0.[WTSum] as TDSAmount, T0.[DocTotal] FROM [dbo].[OPCH] T0 INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry   

Validate Project Code in Receipt From Production

Validate Project Code in Receipt From Production

 IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'59')  
 BEGIN  
 Declare @entry int  
 declare @receveqty float  
 declare @receveqty1 float  
 set @entry= (select distinct BaseEntry from IGN1 where DocEntry=@list_of_cols_val_tab_del and BaseType=202)  
 if Exists  
 (SELECT T0.Project from IGN1 T0   
 where  
 T0.DocEntry = @entry)  
  BEGIN  
 SELECT @Error = 5, @error_message = 'Project Code is missing !'  
 END  
 END   

Query for PO

Query for PO

 IF @transaction_type In ( 'A' ,'U') AND @object_type = '22'  
 BEGIN  
  IF EXISTS( select c.DocEntry  
     from OPOR c INNER JOIN  POR1 d on c.DocEntry = d.docentry   
     where c.DocEntry = @list_of_cols_val_tab_del  
       and isnull(d.Project,'') = '')   
 BEGIN  
 SELECT @Error = 5, @error_message = 'Project Code is missing !'  
 END  
 END  

Get Text value from Matrix using SAP Business One Studio Suite

Code for Get Text value from Matrix using SAP Business One Studio Suite

  Private Function GetTextValue(ByVal Matrix As SAPbouiCOM.Matrix, ByVal ColId As String, ByVal row As Integer) As String  
       Return TryCast(Matrix.GetCellSpecific(ColId, row), SAPbouiCOM.EditText).Value  
     End Function  

Add Folder in System Form using SAP business one Studio Suite

 Today , we will see how to add Folder in System Form Using SAP Business one Studio Suite

  Call System Form in VS 2010 .
   Go To System Form Code section.

Declare Control for Form :

  Private WithEvents oFolder As SAPbouiCOM.Folder  
  Private WithEvents oFolder1 As SAPbouiCOM.Folder  

Code for Multiple Folder:

  Private Sub AddMultipleFolders()  
       Try  
         Dim oItem As SAPbouiCOM.Item = Nothing  
         Dim oItemRef As SAPbouiCOM.Item = Nothing  
         oItemRef = Me.UIAPIRawForm.Items.Item("1320002137")  
         '' Earning Folder  
         oItem = Me.UIAPIRawForm.Items.Add("MyFld", BoFormItemTypes.it_FOLDER)  
         oItem.Top = oItemRef.Top  
         oItem.Height = oItemRef.Height  
         oItem.Left = oItemRef.Left + oItemRef.Width  
         oItem.Width = oItemRef.Width  
         oItem.Visible = True  
         oFolder = oItem.Specific  
         oFolder.Caption = "Earnings"  
         oFolder.GroupWith(oItemRef.UniqueID)  
         oFolder.Pane = 25  
         'Create a matrix on the folder   
         oItem = Me.UIAPIRawForm.Items.Add("MyMtx", BoFormItemTypes.it_MATRIX)  
         oItem.FromPane = 25  
         oItem.ToPane = 25  
         oItemRef = Me.UIAPIRawForm.Items.Item("45")  
         oItem.Top = oItemRef.Top - 50  
         oItem.Left = 10  
         oItem.Width = Me.UIAPIRawForm.Width - 200  
         oItem.Height = 200  
         '' Deduction Folders  
         oItemRef = Me.UIAPIRawForm.Items.Item("MyFld")  
         oItem = Me.UIAPIRawForm.Items.Add("MyFld1", BoFormItemTypes.it_FOLDER)  
         oItem.Top = oItemRef.Top  
         oItem.Height = oItemRef.Height  
         oItem.Left = oItemRef.Left + oItemRef.Width  
         oItem.Width = oItemRef.Width  
         oItem.Visible = True  
         oFolder1 = oItem.Specific  
         oFolder1.Caption = "Deductions"  
         oFolder1.Pane = 26  
         oFolder.Item.LinkTo = "1320002137"  
         oFolder1.GroupWith("MyFld")  
         AddHandler oFolder.ClickBefore, AddressOf Me.oFolder_ClickBefore  
         AddHandler oFolder1.ClickBefore, AddressOf Me.oFolder1_ClickBefore   
       Catch ex As Exception  
   
       End Try          
     End Sub  

Add Event:

  Private Sub Form_LoadAfter(pVal As SAPbouiCOM.SBOItemEventArg)  
       'Throw New System.NotImplementedException()  
       AddMultipleFolders()
   
     End Sub  
     Private Sub oFolder_ClickBefore(ByVal sboObject As Object, ByVal pVal As SBOItemEventArg, ByRef BubbleEvent As Boolean) Handles oFolder.ClickBefore  
       If pVal.ItemUID = "MyFld" Then  
         Me.UIAPIRawForm.PaneLevel = 25  
       End If  
     End Sub  
     Private Sub oFolder1_ClickBefore(ByVal sboObject As Object, ByVal pVal As SBOItemEventArg, ByRef BubbleEvent As Boolean) Handles oFolder1.ClickBefore  
       If pVal.ItemUID = "MyFld1" Then  
         Me.UIAPIRawForm.PaneLevel = 26  
   
       End If  
   

Set Matrix Value using SAP Business One Studio Suite

Code for Set Matrix Value 

  Private Sub SetMatrixValues(ByVal aMatrix As SAPbouiCOM.Matrix, ByVal coluid As String, ByVal intRow As Integer, ByVal strvalue As String)  
       Try  
         aMatrix.Columns.Item(coluid).Cells.Item(intRow).Specific.value = strvalue  
       Catch ex As Exception  
         Message(ex.Message, SAPbouiCOM.BoStatusBarMessageType.smt_Error)  
       End Try  
   
     End Sub  

Add Column in Grid SAP Business one

     Today , we will see How to Add Column Name for Grid in SAP B1 

 oGrid.Columns.Item("ColName").TitleObject.Caption = "Col Caption Name"  
 oGrid.Columns.Item("ColName").Visible = True