Set Document Series SAP B1

Dear All,
   We will see how to set Document Series in ComboBox using SAP Business one SDK.

   '***************************************************************

    '' Purpose : To Display Document Series in the Form

    '' Created Date : 24-11-2016; Author : LAKSHMI NARAYANAN.S

    '***************************************************************

    Friend Sub AddDocSeries(ByVal oForm As SAPbouiCOM.Form, ByVal oComboUID As Object)

        Try

            Dim oComboBox As SAPbouiCOM.ComboBox = Nothing

            oComboBox = oForm.Items.Item(oComboUID).Specific

            If oComboBox.ValidValues.Count > 0 Then

                While oComboBox.ValidValues.Count > 0

                    oComboBox.ValidValues.Remove(0, SAPbouiCOM.BoSearchKey.psk_Index)

                End While

            End If

            oComboBox.ValidValues.Add("5", "Primary")

            oComboBox.ValidValues.Add("-1", "Manual")

            oComboBox.ValidValues.Add("", "")

            oComboBox.Item.DisplayDesc = True

            oComboBox.ExpandType = SAPbouiCOM.BoExpandType.et_DescriptionOnly



        Catch ex As Exception



        Finally



        End Try

    End Sub

Get Document Ownership Name SAP B1

   
Dear All,
   Today ,we will see how to Get Login User Name from SAP B1 throuh SDK

    ''' <summary>

    ''' Get Login Employee Name

    ''' </summary>

    ''' <param name="strLogin">Pass Login Name</param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Private Function GetOnwerDetails(ByVal strLogin As String) As String()

        Dim ORs As SAPbobsCOM.Recordset = Nothing

        Dim strSelQuery As String = String.Empty

        Dim strEmpName As String = String.Empty

        Dim strempID As String = String.Empty

        ORs = oApplication.SapCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

        Try

            If Not String.IsNullOrEmpty(strLogin) Then

                strSelQuery = "SELECT T2.[empID] , ISNULL(T2.[firstName],'') + ' ' + ISNULL(T2.[middleName],'') + ' '+  ISNULL(T2.[lastName],'') as 'EmpName' FROM [dbo].[OUSR]  T1 INNER JOIN OHEM T2 ON T1.[USERID] = T2.[userId] AND  T1.[USERID]='" & strLogin & "'"

                ORs.DoQuery(strSelQuery)

                If ORs.RecordCount > 0 Then

                    strempID = ORs.Fields.Item(0).Value

                    strEmpName = ORs.Fields.Item("EmpName").Value

                Else

                    strempID = String.Empty

                    strEmpName = String.Empty

                End If

            End If

        Catch ex As Exception

        Finally

            System.Runtime.InteropServices.Marshal.ReleaseComObject(ORs)

        End Try

        Return New String() {strempID, strEmpName}

    End Function

Display Project Details in CFL

Dear All,
   Today, we will discuss about below points
1) Display Active Project
2) Display Project based on Posting Date.

Code Here,

  '*************************************************************************************************************

    'Type               : Friend  Sub

    'Name               : CardDetailsCondition

    'Parameter          :

    'Return Value       :

    'Author             : Lakshmi Narayanan

    'Created Dt         : 26-11-2016

    'Last Modified By   :                       

    'Modified Dt        :

    'Purpose            : This Method is used for Project Details CFL Condition

    '*************************************************************************************************************

    Friend Sub ProjectDetailsCondition(ByVal SBO_Application As SAPbouiCOM.Application, ByVal oForm As SAPbouiCOM.Form, ByVal cflIndex As Object, ByVal strPostingDate As Object)

        Dim ocfl As SAPbouiCOM.ChooseFromList = Nothing

        'Dim oCFLcollection As SAPbouiCOM.ChooseFromListCollection

        Dim ocondition As SAPbouiCOM.Condition = Nothing

        Dim oconditions As SAPbouiCOM.Conditions = Nothing

        'oCFLcollection = oForm.ChooseFromLists

        ocfl = SBO_Application.Forms.ActiveForm.ChooseFromLists.Item(cflIndex)

        Try

            oconditions = ocfl.GetConditions

            If 0 = oconditions.Count Then

                ocondition = oconditions.Add

                ocondition.Alias = "Active"

                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

                ocondition.CondVal = "Y"

                ocondition.Alias = "ValidTo"

                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_GRATER_THAN

                ocondition.CondVal = strPostingDate



                ocfl.SetConditions(oconditions)

            End If

        Catch ex As Exception



        Finally

            ocfl = Nothing

            ocondition = Nothing

            oconditions = Nothing



        End Try

       

    End Sub

SAP B1 Question Bank -1

What is Docentry?
"Docentry" is unique number , this will generate automatically for all the individual documents. this starts from 1 will increase one by one for each new entry.

What is BaseEntry?
 Its a Docentry of the base document.

What is LineNum?
 Its a Serial number for "Row table" .

What is ObjType?
Its a door number of the each document. When ever we write a address we will write a door number same like you have to mention the Objtype.

Create Calendar for Between Dates

Create Calendar for Between Dates

 CREATE TABLE [dbo].[EA_CALENDAR](  
      [ID] [int] IDENTITY(1,1) NOT NULL,  
      [DATE] [date] NOT NULL,  
      [Idx] [int] NULL,  
      [YEAR] AS (datepart(year,[DATE])) PERSISTED,  
      [SEMESTER] AS (case when datepart(month,[DATE])<(7) then '1' else '2' end) PERSISTED NOT NULL,  
      [TRIMESTER] AS (case when datepart(month,[DATE])<(4) then '1' else case when datepart(month,[DATE])<(7) then '2' else case when datepart(month,[DATE])<(10) then '3' else '4' end end end) PERSISTED NOT NULL,  
      [MONTH] AS (case when len(CONVERT([varchar](2),datepart(month,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(month,[DATE])) else CONVERT([varchar](2),datepart(month,[DATE])) end) PERSISTED,  
      [WEEK] AS (case when len(CONVERT([varchar](2),datepart(week,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(week,[DATE])) else CONVERT([varchar](2),datepart(week,[DATE])) end),  
      [DAY] AS (case when len(CONVERT([varchar](2),datepart(day,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(day,[DATE])) else CONVERT([varchar](2),datepart(day,[DATE])) end) PERSISTED,  
 PRIMARY KEY CLUSTERED   
 (  
      [ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  

Run the Below Script

 CREATE PROCEDURE [dbo].[EA_AddData]  
 @START_DATE DATETIME,  
 @ENDDATE DATETIME  
 AS  
 WITH CTE_DATES AS  
 (  
 SELECT  
   @START_DATE DateValue UNION ALL SELECT  
   DateValue + 1  
 FROM CTE_DATES  
 WHERE DateValue + 1 <= @ENDDATE)  
 INSERT INTO EA_CALENDAR ([DATE],[idx])  
   SELECT  
    CAST(DateValue AS date),DENSE_RANK() OVER (ORDER BY DATEValue)  
   FROM CTE_DATES  
   OPTION (MAXRECURSION 0)  
 GO  

 EXEC [dbo].[EA_AddData] '',''