DEVELOPING MOCA COMMANDS AND COMPONENTS
Developing MOCA Commands
• At a high level, developing a MOCA command involves these steps.
• Create the MOCA command as a .mcmd file or .mtrg file for
triggers.
• The MOCA file can be created with any text editor.
• There is also a screen called Server Command Maintenance that
will allow you to create MOCA commands in the WMS GUI.
Developing MOCA Commands continued…
• Local syntax and Groovy MOCA commands are contained entirely
in the .mcmd or .mtrg file.
• For MOCA commands that invoke Java and C code, we must create
a .mcmd file that defines the MOCA command as invoking a Java or
C component.
• Then we have to develop and compile the Java or C component.
• Once the MOCA command file is complete and the associated C or
Java module is compiled, we compile the MOCA command.
• Variables in MOCA
• Usually start with a '@'
• @ type variables are placed with argument values or values on the
context stack if passed with an equal sign.
• @ type variables are replaced with null if NOT passed in or not in
the context stack.
list orders where wh_id = 'WMD1'
…….
[select * from ord
where wh_id = @wh_id ( replaced with 'WMD1' )
and ordnum = @ordnum] ( replaced with null )
• @+ Variables
• Replaced with argument values or values on the context stack.
• Can change the sign if passed with < or >.
• Replaced with 1=1 if NOT passed as an argument or NOT on the
current context stack.
• Very useful for commands that perform select queries to pull lists to
display on reports, screens, etc.
list orders where wh_id > 'WMD1'
…….
[select * from ord
where @+wh_id ( replaced with wh_id > 'WMD1' )
and @+ordnum] ( replaced with 1 = 1 )
@% Variables
• Replaced with argument values or values on the context stack.
• Works like @+ with one exception.
• If '%' is passed in as part of the argument, then a like statement is
used.
list orders where wh_id = '%WMD1%'
…….
[select * from ord
where @%wh_id ( replaced with wh_id like '%WMD1%' )
and @%ordnum] ( replaced with 1 = 1 )
@* Variables
• This is a "no name" wildcard type of variable
• Powerful, but also dangerous.
• Replaces every argument passed in, but bad argument may cause
an error.
list orders where wh_id = 'WMD1' and ordnum = 'TEST'
…….
[select * from ord
where @*]
becomes
[select * from ord
where wh_id = 'WMD1'
and ordnum = 'TEST']
@@ Variables
• This variable is replaced by a matching value as set in the
environment
• Commonly used are @@wh_id, @@usr_id, and @@locale_id
list orders where ordnum = 'TEST'
…….
[select * from ord
where wh_id = @@wh_id ( becomes 'WMD1' if set in environment )
and ordnum = 'TEST'
and modusr = @@usr_id]
@? Variable
• Used to hold the value of the last return code.
• Used with the catch statement
[select * from ord
where wh_id = @wh_id
and ordnum = @ordnum] catch ( @?)
|
If ( @? = 0 ) { some more code }
• Catch will catch the return code and the value is now in @?.
• We can evaluate @? or publish it to context.
@! Variable
• Used to hold the value of the last return code message or error
message.
• Used with the catch statement
[select * from ord
where wh_id = @wh_id
and ordnum = @ordnum] catch ( @?)
|
publish data where error_msg = @!
• @! Will be "Success" in this case.
Local Syntax structure – SQL Statements
• All SQL is surrounded by [ ]
• Not surrounding SQL by brackets will result in a parse error or
invalid SQL statement error.
• The MOCA Server will parse the SQL, perform variable replacement
and binding and execute the statement on the Database Server.
[select * from ord
where wh_id = @wh_id
and ordnum = @ordnum] catch ( @?)
• Local Syntax structure – pipe
• The | or pipe is used to pass the results of a SQL statement or a
MOCA command call to the next SQL statement or MOCA
command.
• In this example, wh_id, ordnum, and client_id from the SQL select
are all passed to the next command.
[select ordnum, client_id, wh_id from ord
where wh_id = @wh_id
and client_id = @client_id]
|
cancel order where wh_id = @wh_id
and ordnum = @ordnum
and client_id = @client_id
The | will cause commands down stream to run for each row in the
result of a query or command.
• In the previous example if the query returns 5 rows.
• Then the command down stream will run 5 times.
[select ordnum, client_id, wh_id from ord
where wh_id = @wh_id
and client_id = @client_id]
|
cancel order where wh_id = @wh_id
and ordnum = @ordnum
and client_id = @client_id
Local Syntax structure – semi-colon
• The semi-colon is used when you want to run commands separately
without passing the results.
• In this example, we are running the purge commands but we do not
care about the query results.
• The semi-colon will do a rollback of all commands if any of the
commands fail.
• Even if the query returns 5 rows, the 2 purge commands will only
run 1 time.
[select * from ord where wh_id = 'WMD1'
;
purge daily activity table where dayold = 30
;
purge daily transaction table where dayold = 30
Local Syntax structure – ampersand
• If we want to combine the results of two commands, then we use
the apersand
• In this first example, we are running two warehouse list commands.
Each returns a row, but we will get two rows published.
• In this second example, we are running two different list commands.
We get a result set containing the first commands rows and the
second commands rows.
• Note that data rows are NOT combined – rather one result set will
appear after the other will ALL column names.
list warehouses where wh_id = 'WMD1' & list warehouses where wh_id =
'WMD2'
list users & list roles
Local Syntax structure – Packing result sets
• Result sets are published to the MOCA context stack.
• BUT, we can pack them into a variable if we need to.
• rowcount() is a function that gives us the rows in a result set.
list warehouses >> res1
|
if ( rowcount ( @res1 ) < 2 )
{
publish data where text_msg = "Missing warehouse from system"
}
else
{
publish data where text_msg = "All warehouses in system"
}
Local Syntax commands - braces.
• Braces are used to group statements together.
• The results of the last command executed in braces are published
to the MOCA context stack.
• In this example, only the result set from command 2 will be on the
MOCA stack.
{
Command 1
|
Command 2
}
Local Syntax commands - if statements.
• If statements are used extensively in Local Syntax.
• if, else, and if else are supported
[select * from ord
where wh_id = @wh_id] catch @
|
if ( @? = 0 )
{ … }
else if ( @? = -1403 )
{ … }
else
{ … }
Local Syntax commands – catch statement first style.
• The catch statement has already been introduced.
• The catch immediately follows the MOCA command or SQL
statement.
• All return codes can be caught OR a specific return code or codes
can be caught.
• An if statement following the catch can check @? for the value of
the return code
[select * from ord
where wh_id = @wh_id] catch @?
OR
[select * from ord
where wh_id = @wh_id] catch -1403 OR catch ( -1403, 510 )
Local Syntax commands – catch statement second
style.
• This catch statement mirrors C++ and Java.
• A try block surrounds the command followed by 1 to many catch
statements blocks.
• A finally block can be coded as well.
try { create distro }
catch (10849)
{ … }
catch (11527, 10841 )
{ … }
finally { … }
Local Syntax commands – catching return codes.
• Any return code <> 0 that is NOT caught will stop the current
command from running and do a rollback of the current command.
• Catching a return code will allow execution to continue.
• Its very important to test your commands with no data found
conditions and catch those conditions when they are not critical
errors.
• Otherwise, all SQL in the command back to the last commit will be
rolled back and you may get unintended results.
Local Syntax commands – Wrappers.
• Review - we can have custom MOCA commands at the custom
%LESDIR/src/cmdsrc/varint or usrint level with the same name as
MOCA commands in the base product.
• If we want to call the next level of MOCA command down, we can
do so with the ^.
• This is called a wrapper.
• The code below could be put in a command "list warehouses" at the
%LESDIR%/src/cmdsrc/varint level:
if ( @custom_code_flg = 1 )
{ ( custom logic goes here ) }
else
{ ^list warehouses }
Local Syntax - Remote server execution.
• Commands can be run on another RedPrairie software product
when a MOCA connection is configured.
• When you code this, is usually best to have the hostname:port
passed in as a variable, hard coding is not recommended.
remote (hostname:port#timeout) command
Local Syntax - Remote server execution in parallel.
• System is specified using hostname:port#timeout.
• Rollbacks are performed on the systems where the command fails.
• Commits are performed on the systems where the command
succeeds.
parallel (system1, system2) command
Remote server execution in parallel.
• System is specified using hostname:port#timeout.
• This form of the parallel command rollback the command on ALL
systems if any one of the commands fails.
• Commits are performed on ALL the systems when all the
commands succeed.
inparallel (system1, system2) command
Local Syntax – publish data and context.
• The results of each command OR SQL statement are published to
context.
• Variables can be replaced in context when the same name occurs.
• The last SQL statement or command in a MOCA command is what
the command will return.
• Sometimes we use publish data to manipulate the context and
make sure we return or keep on context what we need.
• Sometimes we use publish data to make sure we return to the caller
the exact values we want to return.
n this example, we do not want to return all rows, but just one field
with concatenated values.
[select * from adrmst]
|
publish data where last_adr_line = @adrcty || ' , ' || @adrstc || ', ' ||
@adrpsz
Local Syntax – type casting "raw".
• MOCA local syntax supports type casting that we sometimes use in
SQL.
• If we want to keep the "raw" format of a variable with adding quotes
– otherwise strings are quoted in SQL.
• In this example, we want to pass in a string that contains a list and
we do not want quotes around it.
publish data where shpsts_list = " ('I','R','S') " and wh_id = 'WMD1'
|
[select *
from shipment
where wh_id = @wh_id
and shpsts in @shpsts_list:raw]
Local Syntax – type casting "date".
• The :date type cast is used in MOCA to do a date conversion on a
variable.
• If the variable is in local syntax as @variable:date, a to_date
function is called to convert the string to a date type.
• The variable must contain a string in the format:
'YYYYMMDDHH24MISS'
publish data where moddte1 = '20010622000000' and moddte2 = '20010622235959'
|
[select * from bomhdr where moddte > @moddte1:date and moddte < @moddte2:date]
The first step MOCA takes is to parse the @variable:date and
convert it into a Local Syntax to_date function call.
[select * from bomhdr
where moddte > TO_DATE('2001-06-22 00:00:00', 'YYYYMMDDHH24MISS')
and moddte < TO_DATE('2001-06-22 23:59:59', 'YYYYMMDDHH24MISS')]
In the next step, the conversion to database specific SQL occurs.
• Below we see the original SQL with @variable:date becoming the
SQL Server Convert Function call:
publish data where moddte1 = '20010622000000' and moddte2 = '20010622235959'
|
select * from bomhdr where moddte > CONVERT(DATETIME, '2001-06-22 00:00:00',
20) and moddte < CONVERT(DATETIME, '2001-06-22 23:59:59', 20)
Comments
Post a Comment