How to change the Quality Status by scripting

This note describes how to change the QS of a stock based on a location

In case you want to change the QS of a stock on a certain location, then you can do it by a script. 

You can automate this task with the following script configured for Produmex Robot tool.
In this example I am going to change the QS of a batch managed item to QUARANTI on location "01-B-01-02".

Creating SQL VIEW as an input for the script

First, create an SQL view that contains all source data:

create VIEW "PMX_WMS_CHANGE_QS" AS
select 
"PMX_INVT"."ItemCode", 
"PMX_INVT"."StorLocCode", 
"PMX_INVT"."Quantity", 
"PMX_INVT"."QuantityUom2", 
"PMX_INVT"."LogUnitIdentKey", 
"PMX_INVT"."QualityStatusCode",
"PMX_INVT"."ItemTransactionalInfoKey"
from 
"PMX_INVT" 
left join "PMX_ITRI" on "PMX_INVT"."ItemTransactionalInfoKey" = "PMX_ITRI"."InternalKey"
left join (
select 
"OIBT"."ItemCode", 
"OIBT"."BatchNum",
"OIBT"."WhsCode",
"OIBT"."Quantity"
from 
"OIBT" 
left join "OBTN" on "OIBT"."ItemCode" = "OBTN"."ItemCode" AND "OIBT"."SysNumber" = "OBTN"."SysNumber"
WHERE 
"OIBT"."Quantity" > 0
) as "SBO_STOCK" on "SBO_STOCK"."ItemCode" = "PMX_INVT"."ItemCode" AND "SBO_STOCK"."BatchNum" = "PMX_ITRI"."BatchNumber"
where 
"SBO_STOCK"."ItemCode" is not null
AND "PMX_INVT"."QualityStatusCode" <> 'QUARANTI'

Creating the script

Next, save the following script as ChangeQS.cs into folder C:\Program Files\Produmex\Produmex Tools\CsScript\

I added a filtering by the "storage location code" inside the script, when I defined the query

using System;
using System.Diagnostics;
using System.Globalization;
using System.Reflection;
using System.Text;
using System.Transactions;
using Produmex.Foundation.Data.Sbo;
using Produmex.Foundation.Data.Sbo.Attributes;
using Produmex.Foundation.Data.Sbo.BusinessObjects;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Convertors;
using Produmex.Foundation.Data.Sbo.BusinessObjects.Definitions.Tables;
using Produmex.Foundation.Data.Sbo.Providers;
using Produmex.Foundation.Data.Sbo.Utilities;
using Produmex.Foundation.Data.SqlClient;
using Produmex.Foundation.Diagnostics;
using Produmex.Foundation.Reflection;
using Produmex.Foundation.Resources;
using Produmex.Foundation.SboGui;
using Produmex.Sbo.Logex.Data.BusinessObjects;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions;
using Produmex.Sbo.Logex.Data.BusinessObjects.Definitions.Tables;
using Produmex.Sbo.Logex.Data.Providers;

namespace AddUpdateData
{
class Program
{
private static string CONNECTION_STRING = "ADDYOURCONNECTIONSTRINGHERE";
private static readonly ILog s_log = LogProvider.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);


static void Main(string[] args)
{
try
{
DoSomething();
}
catch (Exception ex)
{
s_log.Error("", ex);
Console.WriteLine(ex);
Console.ReadLine();
}
}


public static void DoSomething()
{
// creating a new transaction
using (TransactionScope scope = PmxDbConnection.GetNewTransactionScope())
{
// create the connection to the database
using (PmxDbConnectionDirect conn = PmxDbConnectionMgr.GetDirectConnection(SboConnectionString.ParseStringToObject(CONNECTION_STRING)))
{
//open the connection
conn.Open();
// creating a new PmxMoveProvider object
PmxMoveProvider moveProv = new PmxMoveProvider(conn);
// defining the query that is used as an input for the 
string query = @"select * from PMX_WMS_CHANGE_QS where StorLocCode like '%01-B-01-02%'";

// create a recordset from the result of the query
using (ISboRecordset rs = SboRecordsetHelper.RunQuery(s_log, query, conn))
{
// create a new PMXMove object
using (PmxMove move = moveProv.GetNewBO())
{
// read the rows from the recordset from the result of the query
while (!rs.EoF)
{
// create a new PmxMoveLine object
PmxMoveLine moveLine = moveProv.GetNewAddedLine(move);
// set the parametersof the PmxMoveLine object from the 
moveLine.ItemCode = rs.GetTypedValue<string>("ItemCode");
moveLine.Quantity = rs.GetTypedValue<double>("Quantity");
moveLine.SourceQualityStatusCode = rs.GetTypedValue<string>("QualityStatusCode");
moveLine.DestinationQualityStatusCode = "QUARANTI";
moveLine.SourceStorageLocationCode = rs.GetTypedValue<string>("StorLocCode");
moveLine.DestinationStorageLocationCode = rs.GetTypedValue<string>("StorLocCode");
if (rs.GetTypedValue<int>("ItemTransactionalInfoKey") > 0)
{
moveLine.ItemTransactionalInfoKey = rs.GetTypedValue<int>("ItemTransactionalInfoKey");
}
if (rs.GetTypedValue<double>("QuantityUom2") > 0)
{
moveLine.QuantityUom2 = rs.GetTypedValue<double>("QuantityUom2");
}
if (rs.GetTypedValue<int>("LogunitIdentkey") > 0)
{
moveLine.SourceLogisticUnitIdentKey = rs.GetTypedValue<int?>("LogunitIdentkey");
moveLine.DestinationLogisticUnitIdentKey = rs.GetTypedValue<int?>("LogunitIdentkey");
}
// read the next row from the recordset
rs.MoveNext();
}
// add the move to Produmex WMS, this step will generate the move action
moveProv.AddBO(move, true);
}
}
//Complete transaction
scope.Complete();
}
}
}
}
}

"C:\Program Files\Produmex\Produmex Tools\Produmex.Sbo.Logex.Tools.Robot.exe" /t:csscript /a1:"C:\Program Files\Produmex\Produmex Tools\CsScript\ChangeQS.cs" /cs:yourconnectionstringhere
Pause