Insert , Update and Delete Query in Customization - Epicor

// Insert Query 

UD02Adapter UD02connect_1  = new UD02Adapter(this.oTrans);

UD02connect_1.BOConnect();

UD02connect_1.GetaNewUD02();

DataRow edvnew02 = UD02connect_1.UD02Data.UD02.Rows[0];

edvnew02.BeginEdit();

edvnew02["Key1"]= cmbBankGrp.Value;

edvnew02["Key2"]= cmbBank.Value;

edvnew02["Key3"]= cmbFiscalYr.Value;

edvnew02["Key4"]= cmbPeriod.Value;

edvnew02["Key5"] = "WPS";

edvnew02["Number01"]= 0;

edvnew02.EndEdit();

//oTrans.Update();

UD02connect_1.Update();

UD02connect_1.Dispose();

EpiMessageBox.Show ("Created Successfully");

//Update Query

UD02Adapter UD02connect_1  = new UD02Adapter(this.oTrans);

UD02connect_1.BOConnect();

if(UD02connect_1.GetByID(grdBankTransferSummary.ActiveRow.Cells["UD021_Key1"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key2"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key3"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key4"].Value.ToString(), "WPS"))

{

UD02connect_1.UD02Data.UD02[0]. Rows[0]["Checkbox_c"] = cbxTest.Checked;

            UD02connect_1.Update();

}

UD02connect_1.Dispose();

//Delete Query

UD02Adapter UD02connect_1  = new UD02Adapter(this.oTrans);

UD02connect_1.BOConnect();

UD02connect_1.GetByID(grdBankTransferSummary.ActiveRow.Cells["UD021_Key1"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key2"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key3"].Value.ToString(), grdBankTransferSummary.ActiveRow.Cells["UD021_Key4"].Value.ToString(), "WPS");

UD02connect_1.UD02Data.UD02[0].Delete();

UD02connect_1.Update();

Insert a new Row in table using BPM

for each ttUD101A:
if not can-find(UD18 where UD18.Company = ttUD101A.Company and UD18.Key1 = ttUD101A.Key1 and UD18.Key2 = ttUD101A.ChildKey1 and UD18.Key3 = ttUD101A.ChildKey2 and 
UD18.Key4 = ttUD101A.ChildKey3 and UD18.Key5 = ttUD101A.ChildKey5) then do:
create UD18.
assign UD18.Company = ttUD101A.Company.
assign UD18.Key1 = ttUD101A.Key1.
assign UD18.Key2 = ttUD101A.ChildKey1.
assign UD18.Key4 = ttUD101A.ChildKey3.
assign UD18.Key5 = ttUD101A.ChildKey4.
assign UD18.Character01 = ttUD101A.Character01.
end.
end.

Check Duplicate PO in Sales Order using BPM in Epicor

Open the Method Directives application (in the standard Epicor menu — Main Menu >> System Management >> Business Process Management >> Setup >> Method Directives).



Click the “Method Code” button to open the search window, and search for the Method Name starting at “Update” and the Business Object “SalesOrder.”  Select the “Update” method.



From the menu or toolbar, select File >> New >> New Pre-Processing.



Give your directive the name “Check for Duplicate PO,” and check the “Enabled” and “Prevent Endless Loops” check boxes.



Click the “Conditions” button and then the “Add Line” button.



Under “User Text” select “number of rows in the designed query…”.



Click the “designed query” hyperlink and then give the query a name — “DupPO”, type in the query “for each ttOrderHed no-lock  ,  each OrderHed no-lock  where (OrderHed.CustNum = ttOrderHed.CustNum and OrderHed.PONum = ttOrderHed.PONum and OrderHed.OrderNum <> ttOrderHed.OrderNum )” and then click “OK.”



Leave the “is not less than 1″ portion of the condition as is, and click “OK.”



Click the “Actions” button and then the “Add Line” button.



Select the “show informational message based on the designed template” option.



Click the “designed” hyperlink, and enter the name, severity, and message text.



If you want to show the PO number the user entered, click “Insert” and enter the name “PONum”, select the proper table “ttOrderHed”, check all the boxes, and check the “PONum” field name.  Click OK.



As soon as you save your work, the Method Directive will be called anytime and anywhere the SalesOrder.Update method is called.

Add New Button in Epicor Action menu using VB.net

Private sub AddTools()
Dim custButton As New Infragistics.Win.UltraWinToolbars.ButtonTool("TestTool")
Dim pop As Infragistics.Win.UltraWinToolbars.PopupMenuTool = Nothing
custButton.SharedProps.Caption = "Print Receipt"
If baseToolbarsManager.Tools.Exists("TestTool") = False Then
      baseToolbarsManager.Tools.Add(custButton)
      baseToolbarsManager.Toolbars("Standard Tools").Tools.Add(custButton)
      baseToolbarsManager.Tools("ActionsMenu").SharedProps.Enabled = True
      pop = DirectCast(baseToolbarsManager.Tools("ActionsMenu"),                          

      Infragistics.Win.UltraWinToolbars.PopupMenuTool)

      pop.Tools.AddRange(New Infragistics.Win.UltraWinToolbars.ToolBase() {custButton})
      Dim intFuncImage As System.Drawing.Image = EpiUIImages.GetImage("InternalFunction")
      Dim app As New Infragistics.Win.Appearance()
      app.Image = intFuncImage            
      baseToolbarsManager.Tools("TestTool").SharedProps.AppearancesLarge.Appearance = app
      baseToolbarsManager.Tools("TestTool").SharedProps.AppearancesSmall.Appearance = app
End If

End sub

Private Sub QuoteForm_Load(ByVal sender As Object, ByVal args As EventArgs)
      AddTools()
End Sub

Private Sub baseToolbarsManager_ToolClick(ByVal sender As Object, ByVal args As Infragistics.Win.UltraWinToolbars.ToolClickEventArgs)
If args.Tool.Key.tostring() = "TestTool" then
     Dim QView As EpiDataView = CType(oTrans.EpiDataViews("QuoteHed"), EpiDataView)
     Dim QuoteStr As Int32 = QView.dataView(QView.Row)("QuoteNum")
     Process.Start("Crystal.exe",QuoteStr )
End if
End Sub

Part Number Auto generate using BPM in Epicor

Find ttPart where ttPart.RowMod = "A" no-error.
If Available ttPart then do :
For each Part where Part.Company = ttPart.Company and part.Shortchar01= ttPart.ShortChar01 and Part.ProdCode = ttPart.ProdCode :
if Part.Number01 = 0 then
ttPart.Number01 = 100015.
end.
end.
ttPart.PartNum = ttPart.ShortChar01 + ttPart.ProdCode + String(ttPart.Number01).


Find ttPart where ttPart.RowMod = "A" no-error.
If Available ttPart then do :
For each Part where Part.Company = ttPart.Company and part.Shortchar01= ttPart.ShortChar01 and Part.ProdCode = ttPart.ProdCode :
if Part.Number01 > 0 then
ttPart.Number01 = Part.Number01 + 1.
end.
end.
ttPart.PartNum = ttPart.ShortChar01 + ttPart.ProdCode + String(ttPart.Number01).

Query to set Single user to Multi user in Sql server

exec sp_dboption 'epicor905', 'single user', 'FALSE'

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'epicor905'

kill spid

ALTER DATABASE epicor905 SET MULTI_USER

Query to find last restore date and time in Sql server

Declare @DB sysname = 'EpicorPilot905'
Select * from msdb.dbo.restorehistroy where destination_database_name = @DB;