Reading the SYSTEM Variable in User Data Source string

There are many ways to read the system variable in SAP SDK. But Simplest way is to read directly from data source. based on the Variable ID

 strTotal = PForm.DataSources.UserDataSources.Item("SYS_85").ValueEx;

Remote System Login through VB Code

You can do a remote login using VB or C# code. Only few things is required; Like IP, User Name and Password

Remote Login and Copy File

'''''''''''''''Login To Remote system Share Folder '''''''''''''''''''''''

   Open_Remote_Connection(ServerIP, ServerUser, ServerPass)

                    '''''''''''''''Copy File Share Folder '''''''''''''''''''''''

''' To Copy a file please create a shared folder in the destination system with full access.   

System.IO.File.Copy(strFullFile, ServerPath + strDocentry + "_" + strCardCode.Trim() + ".xls")

Private Sub Open_Remote_Connection(ByVal strComputer As String, ByVal strUserName As String, ByVal strPassword As String)

        Try

            Dim ProcessStartInfo As New System.Diagnostics.ProcessStartInfo

            ProcessStartInfo.FileName = "net"

            ProcessStartInfo.Arguments = "use \\" & strComputer & "\c$ /USER:" & strUserName & " " & strPassword

            ProcessStartInfo.WindowStyle = ProcessWindowStyle.Hidden

            System.Diagnostics.Process.Start(ProcessStartInfo)

            System.Threading.Thread.Sleep(2000)

        Catch ex As Exception

        End Try

    End Sub

Check Tables in View \ Procedure \ SQL Job

Most of the cases, it will difficult to find where the table or column or procedure is used any where in the Procedures in the database; Below quires will help to search through the data base and find the table or procedure the keywords are used

Below query will help to check the table is used any where in View or Procedure

SELECT j.Name, s.Database_name, s.Command
FROM msdb.dbo.sysjobsteps s
INNER JOIN msdb.dbo.sysjobs j
ON s.job_id = j.job_id
WHERE s.command LIKE '%TableName%'
AND s.database_name LIKE '%DBName%'


Below query will help to check the table is used any where in SQL Job

SELECT DISTINCT so.Name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%OBTN%' 

Print Layout for the User Defined Form - SAP 8.82 or Above only

The below explained method can be used only in SAP B1 8.82 and above. There is a limitation in B1 8.81 version: Only UDO objects whose id is numeric are supported. If the UDO code is alphanumeric such as CR_8110, it will not work. In B1 8.81 we won’t able to create the UDO with a numeric value.

Please follow the steps in the attached document. If you cannot set the ID of report after loading, you have to dynamically query the ID from the table to access the report. Details are in given the document.

Advertisement

Attachment : Detail Document with Image

Step 1 : Create a crystal report for the Printlayout of the user defined form.The parameter of the crystal
report must be DocKey@ with type number. Ref the Image below
Step 2 : Upload this report to SAP B1.

Step 3 : Have to add the Event trigger in the InitialSettings.cs.

Advertisement

Add the below codes in the
InitialSettings()
<Code>
Global.SapApplication.LayoutKeyEvent += new
SAPbouiCOM._IApplicationEvents_LayoutKeyEventEventHandler(SapApplication_LayoutKeyEvent);
<Code>
Add the event function to trigger the event function of “VPurchaseRequest.cs”
<code>
private void SapApplication_LayoutKeyEvent(ref SAPbouiCOM.LayoutKeyInfo eventInfo, out bool
BubbleEvent)
{
Global.bubblevalue = true;
if (eventInfo.ReportCode == "RCRI" & eventInfo.ReportTemplate == "RCRI0012")
{
VPurchaseRequest.Instance.SapApplication_LayoutKeyEvent(ref eventInfo, out
BubbleEvent);
}
BubbleEvent = Global.bubblevalue;
} 

<code>
RCRI – Is the type code of Crsyatl report

Chart of Accounts Report

Dear Experts,
i need to create either a Query or a crystal report to Display chart of accounts same as it display on char of account screen ( Draws, Levels and accounts hierarchy wise).
regards

 Answer 

You have to update the query based on number of level you have in COA


select TBL1.[GROUP],(TBL1.[AcctCodeLevel-1] + ' - ' + TBL1.[Acct Name Level-1]) [Level-1],
(TBL1.[AcctCodeLevel-2] + ' - ' + TBL1.[Acct Name Level-2]) [Level-2],(TBL1.[AcctCodeLevel-3] + ' - ' + TBL1.[Acct Name Level-3]) [Level-3] ,
(TBL1.[AcctCodeLevel-4]  + ' - ' + TBL1.[Acct Name Level-4]) [Level-4],
(TBL1.[AcctCodeLevel-5] + ' - ' + TBL1.[Acct Name Level-5]) [Level-5]

from (
     SELECT t0.groupmask, t0.grpline, t0.levels, T0.[AcctCode]  [AcctCodeLevel-1],
T0.[AcctName] [Acct Name Level-1],'-' [AcctCodeLevel-2] ,'-' [Acct Name Level-2],'-' [AcctCodeLevel-3],'-' [Acct Name Level-3]
,
'-' [AcctCodeLevel-4],'-' [Acct Name Level-4],'-' [AcctCodeLevel-5],'-' [Acct Name Level-5],

(case T0.[GroupMask] when 1 then 'ASSET' WHEN 2 THEN 'LIABILITIES' WHEN 3 THEN 'EQUITY'
WHEN 4 THEN 'REVENUE' WHEN 5 THEN 'EXPENSES' END) [GROUP]
  FROM OACT T0 WHERE t0.Levels = '1'
  union al

Logged on User in SAP B1

In addon using C#.net SDK coding method you can use the below code to access the current user logged in SAP.  

Global.SapCompany.UserSignature.ToString() 

In formatted search use the below format.

SELECT $[USER] 
  
In transaction notification use 

SET @USERID = (SELECT ORDR.UserSign FROM ORDR WHERE ORDR.DocEntry = @list_of_cols_val_tab_del )
  
If you are trying to find who is updating the document in transaction notification use the  'usersign2' filed in the table.

Advertisement

SAP Business One Menu ID List

Objects ID List
 
The following table lists the business objects that are exposed through the DI API.
Object Name Object ID Description

ACT 1 Chart of Accounts
CRD 2 Business Partner Cards
ITM 4 Items
PLN 6 Price list names
SPP 7 Special prices
CPR 11 Contact employees
USR 12 Users
INV 13 Invoices
RIN 14 Credit notes
DLN 15 Delivery notes
RDN 16 Revert delivery notes
RDR 17 Orders
PCH 18 Purchases
RPC 19 Revert purchases
PDN 20 Purchase delivery notes
RPD 21 Revert purchase delivery notes
POR 22 Purchase orders
QUT 23 Quotations
RCT 24 Receipts incoming payments
DPS 25 Bill of Exchange Deposits
BTD 28 Journal vouchers
JDT 30 Journal entries
ITW 31 Item warehouse
CLG 33 Contact activities
CRN 37 Currency codes
CTG 40 Payment terms types
BNK 42 Bank pages
VPM 46 Payments to vendors
ITB 52 Item groups
CHO 57 Checks for payment
IGN 59 Inventory general entry
IGE 60 Inventory general exit
WHS 64 Warehouses codes and names
ITT 66 Product trees