If you even need to find out all store procedures that contain certain phrase, such as “PurchaseOrders”, the following query will help.

select so.name, sc.text from syscomments sc inner join sysobjects so on sc.id = so.id
where so.xtype = ‘P’ and sc.text like ‘%PurchaseOrders%’

This can be extended to other object type, for example ‘TR’ for trigger.   Here is a list of object type.

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

In Syteline, there are many ways to default field value.  The most simple way is to use the default value property of the field component.  But if you need to set the default value based on some other field value, that simple property value would not cut it.

Syteline Application Case

We want to default ship via code in Customer Ship To form based on customer type.

Syteline Technical Components

Event Handler, Inline Script, SQL Store Procedure.

Customization Solution

Since the customer type is in Customer form, but not in Ship To form, we need to get that in.

1) Create a simple SP: nGetCustType

ALTER PROCEDURE [dbo].[navGetCustType]
(@CustNum [CustNumType],
@CustType [CustTypeType] output)

as

select @CustType = cust_type
from customer
where cust_num = @CustNum and cust_seq = 0

2) Add a new event handler to event：StdObjectNewCompleted, to call the following Inline Script：

Option Explicit On
Option Strict On

Imports System
Imports Microsoft.VisualBasic
Imports Mongoose.IDO.Protocol
Imports Mongoose.Scripting

Namespace SyteLine.GlobalScripts
Public Class EvHandler_StdObjectNewCompleted_5
Inherits GlobalScript

Sub Main()

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “nGetCustType”

nRequest.Parameters.Add(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“CustNum”))
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.Variables(“vCustType”).Value = nRetVal.Parameters(1).ToString

if ThisForm.Variables(“vCustType”).Value = “AAA” or _
ThisForm.Variables(“vCustType”).Value = “BBB”  then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UPRP”)
else
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh _
(“ShipCode”, “UP7A”)
end if
ReturnValue = “0”
End Sub
End Class
End Namespace

Within this script, we call the SP, get the Customer Type, then based on the customer type to set the ShipCode value.

We will use a sample to run through the process of adding a CLM into IDO. 

Part of making the Customer Document Profile function available to RMA verification report, we need to create a new CLM to IDO SLRmas.  The CLM is based on SP  navProfileRMAVerificationSp. 

create PROCEDURE [dbo].[navProfileRMAVerificationSp]
(
   @RMAStatusOpen                   ListYesNoType       = NULL,
   @RMAStatusClosed                 ListYesNoType       = NULL,
   @RMAStatusStopped                ListYesNoType       = NULL,
   @RMAStatusHistory                ListYesNoType       = NULL,
   @RMAStarting                     RmaNumType          = NULL,
   @RMAEnding                       RmaNumType          = NULL,
   @RMADateStarting                    DateType            = NULL,
   @RMADateEnding                    DateType            = NULL,
   @WhseStarting                    WhseType            = NULL,
   @WhseEnding                        WhseType            = NULL,
   @CustomerStarting                CustNumType          = NULL,
   @CustomerEnding                  CustNumType          = NULL
) AS

DECLARE @UserParmSite SiteType,
        @RMAStatus nvarchar(4)

if @RMAStatusOpen = 1 set @RMAStatus = @RMAStatus + ‘O’
if @RMAStatusClosed = 1 set @RMAStatus = @RMAStatus + ‘C’
if @RMAStatusStopped = 1 set @RMAStatus = @RMAStatus + ‘S’
if @RMAStatusHistory = 1 set @RMAStatus = @RMAStatus + ‘H’

SELECT @UserParmSite = parms.site FROM parms
SET @WhseStarting    = ISNULL(@WhseStarting, dbo.LowCharacter())
SET @WhseEnding      = ISNULL(@WhseEnding, dbo.HighCharacter())
SET @RMAStarting          = CASE WHEN @RMAStarting IS NULL THEN dbo.LowCharacter()  ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAStarting) END
SET @RMAEnding            = CASE WHEN @RMAEnding   IS NULL THEN dbo.HighCharacter() ELSE dbo.ExpandKyByType(‘RmaNumType’,@RMAEnding)   END
SET @CustomerStarting = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerStarting), dbo.LowCharacter())
SET @CustomerEnding = ISNULL(dbo.ExpandKyByType(‘CustNumType’, @CustomerEnding), dbo.HighCharacter())

   SELECT
     rma.rma_num
   , rma.cust_num
   , rma.cust_seq
   , CASE WHEN DocProfileCustomer.RowPointer IS NULL THEN 0 ELSE 1 END
   , DocProfileCustomer.Method
   , DocProfileCustomer.Destination
   , DocProfileCustomer.NumCopies
   , ISNULL(Customer.lang_code,
    (SELECT lang_code FROM Customer WHERE Customer.cust_num = rma.cust_num
    AND Customer.cust_seq =0)) as LangCode
   , DocProfileCustomer.CoverSheetCompany
   , DocProfileCustomer.CoverSheetContact
   FROM rma
   LEFT OUTER JOIN Customer
     ON rma.Cust_Num = Customer.cust_num
     AND rma.Cust_Seq = Customer.cust_seq
   LEFT OUTER JOIN DocProfileCustomer
      ON DocProfileCustomer.CustNum = rma.cust_num
     AND DocProfileCustomer.CustSeq = rma.cust_seq
     AND DocProfileCustomer.RptName = ‘RMA Verification Report’
     AND DocProfileCustomer.active = 1
   WHERE  CHARINDEX(rma.stat, @RMAStatus) <> 0
     AND rma.rma_num BETWEEN @RMAStarting AND @RMAEnding
     AND rma.whse BETWEEN @WhseStarting AND @WhseEnding
     AND rma.cust_num BETWEEN @CustomerStarting AND @CustomerEnding
     AND (@RMADateStarting is null or rma.rma_date >= @RMADateStarting)
     AND (@RMADateEnding is null or rma.rma_date <= @RMADateStarting)

Create new IDO as extension of existing IDO

Use the new IDO Wizard to create a new IDO, which is extend of a existing Syteline IDO

Add Table

Once the IDO created, we need to add table DocProfileCustomer to the IDO

The links for the table should be CustNum and CustSeq，link to rma.cust_num and rma.cust_seq.

Add New Property

Every output field from the SP navProfileRMAVerificationSp need to be mapped to a IDO property.  So we need to create those property upfront. 

Bound property

Field like rma.rma_num, rma.cust_num and rma.cust_seq are bound to table fields

Derrvied property

For fields that no directly link to table fields

Add Method

Finally, we will add the CLM (Custom Load Method)

Then map the output fields of store procedure to IDO properties

Now this new CLM should be ready to use in our form. 

SQL server provides way to connect to remote database (SQL, Oracle and many others).  Within your store procedure, you can dynamically connect to the remote DB, fetch data, process them into Syteline DB. 

Business Case:

Let’s say you have a web store database, in a remote database server.  You want to replicate the orders into Syteline

Syteline Technical Component:

SQL Store Procedure

Solution:

In your store procedure, you will need to first connect to the remote DB server, and login to the database.  Here is a example with server name DBSRVR.

if not exists(select 1 from sys.servers where name = ‘DBSRVR’)
    EXEC sp_addlinkedserver
        ‘DBSRVR’,
        N’SQL Server’

exec sp_addlinkedsrvlogin ‘DBSRVR’
                         ,’false’
                         ,null
                         ,’sa’
                         ,’sapassword’

After getting the connection, you can use the select statement to fetch data into a temp table for further process.

Insert @Orders (
            [OrderID]
           ,[OrderDate]
           ……
           )
    Select
            [OrderID]
           ,[OrderDate]

           ……

        from [DBSRVR].[WEBORDER].dbo.orders

Once you done with all the process, you may want to drop off the connection

— Drop off from DBSRVR 
if exists(select 1 from sys.servers where name = ‘DBSRVR’)
Exec sp_dropserver ‘DBSRVR’, ‘droplogins’

To check what Database Servers are currently link to your DB Server, just query the sys.servers table. 

Select * from sys.servers

Here is a basic way to call a SQL Store Procedure (SP) within a script, either Form Script or Inline Script.

The sample we use below is calling a SP: GetCustType and return the CustType value to a form variable.

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “GetCustType”

nRequest.Parameters.Add(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“CustNum”))
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.Variables(“vCustType”).Value = nRetVal.Parameters(1).ToString

And here is the SP:

PROCEDURE [dbo].[navGetCustType]
(@CustNum [CustNumType],
@CustType [CustTypeType] output)

as

select @CustType = cust_type
from customer
where cust_num = @CustNum and cust_seq = 0

Here is a multiple parameters  sample:

Sub nGetUnitDetails()
Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “nGetUnitDetails”
nRequest.Parameters.Add(ThisForm.Components(“editUnit”).Text)
nRequest.Parameters.Add(CDate(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“RmahdrRmaDate”)))
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Warranty”, nRetVal.Parameters(2).ToString)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Item”, nRetVal.Parameters(3).ToString)
ThisForm.GenerateEvent(“ItemDataChange”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoNum”, nRetVal.Parameters(4).ToString)
ThisForm.GenerateEvent(“OrderNumLoseFocus”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoLine”, nRetVal.Parameters(5).ToString)
ThisForm.GenerateEvent(“DisplayRMAWarningMsgSp”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoRelease”, nRetVal.Parameters(6).ToString)
ThisForm.GenerateEvent(“CoLineChanged”)
‘New for Parag
‘ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“UnitCreditConv”, nRetVal.Parameters(7).ToString)
‘ThisForm.GenerateEvent(“RecalcTotals”)

If Not nRetVal.Parameters(3).ToString = “” Then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“QtyToReturnConv”, “1”)
End If

End Sub