Kiwiplan

• Kiwiplan - VUE with systemd
• Kiwiplan - Business Rules Book
• Kiwiplan - KDW Data Warehouse
• Kiwiplan - Upgrade Shortcut Switches
• Kiwiplan - Bag of Tricks for ESP and MAP/MES
• Kiwiplan - Supplying Product Designs
• Kiwiplan - Multi-Plant Harmonization
• Kiwiplan - ULT Interface to Warehouse and Carriers
• Kiwiplan - ESP Stock Transfer
• Linux Server - Install RedHat 9.4
• Linux Server - Install RedHat 8.6
• Linux Server - Install Rocky 9.4
• Kiwiplan - Classic Purge Parameter Defaults
• Kiwiplan - Costing
• Kiwiplan - Farmout Routes in ESP
• Script - Import Data Script for Internal Customer Servers
• Kiwiplan - Box Plant Classic/Refresh To VUE Master Parameters
• Kiwiplan - Sheet Plant VUE Master Parameters
• SFL - Post-Site Audit Installation
• Kiwiplan - Conversion Tables for VUE (Microns)
• Kiwiplan - JAVA openJDK Install for Oracle Licensing Issues
• Script - Data Dump Script for Internal Customer Servers
• Script - Test Server .bash_profile
• Kiwiplan - ULT/VUE Setup a New Machine for Outside Farmout
• Kiwiplan - ESP Consignment Store
• Kiwiplan - ESP Crystal Reports Standards and Help Hints
• Kiwiplan - MAP Variables
• Kiwiplan - Add Printer for Classic and Refresh Sites
• Kiwiplan - MySQL log is fill or filled the drive
• Kiwiplan - Install Web Launcher
• Script - Deleting a KDE server from the Database
• Kiwiplan - KIDSENV Settings
• Kiwiplan - Server Load Monitor
• MySQL - Parameters Explained
• Dataset - Restrict Date in MAP MES VUE ESP
• Kiwiplan - Setup ULT for ESP

Kiwiplan - VUE with systemd

Introduction

This document it to setup auto restart of the VUE services after a server restart.

Create The Following Files

kiwiplan@.service

sudo vi /etc/systemd/system/kiwiplan@.service

Place the following in the file

[Unit]
Description=Kiwiplan services for %I
After=network-online.target mariadb.target

[Service]
Type=simple
User=remuser
Group=kiwiplan
ExecStart=/KIWI/services/servers.sh start %i
ExecStop=/KIWI/services/servers.sh stop %i
RemainAfterExit=true
PrivateTmp=false
LimitNOFILE=32768
LimitNPROC=32768
Environment=TERM=vt100

[Install]
WantedBy=multi-user.target

Reload the changes

sudo systemctl daemon-reload

servers.sh

vi /KIWI/services/servers.sh

Place the following in the file

#!/bin/bash
if [ $# -gt 1 ]; then
  export TERM=vt100
  export LOG=/tmp/kiwiplan.$2.log
  echo $(date +'%Y-%m-%d %H:%M:%S') - $1 >> $LOG
  case "$1" in
    start)
       /KIWI/services/sites/$2/current/bin/startservers.sh >> $LOG 2>&1
       ;;
     stop)
       /KIWI/services/sites/$2/current/bin/stopservers.sh >> $LOG 2>&1
       ;;
     *)
       exit 1
       ;;
  esac
  exit 0
else
  echo "No Site passed"
  exit 1
fi

Make the file executable

chmod +x /opt/kiwi/services/servers.sh

Run Configurations

For the VUE site named 'vue' use the following:

To have it start on boot run :

systemctl enable kiwiplan@vue.service 

To stop it from starting at boot time :

systemctl disable kiwiplan@vue.service

To start :

systemctl start kiwiplan@vue.service

 To stop :

systemctl stop kiwiplan@vue.service

 To view status :

systemctl -l status kiwiplan@vue.service

Kiwiplan - Business Rules Book

Business Rules

The business rule engine is custom main but it uses part of the following logic:

 Ternary Conditional Operand/Operator

Kiwiplan Document Generator

The KDG software back end is built using the following engine:

Stimulsoft

Unit Load (StimulSoft/KDG)

Reconstructing the SSCC barcode_key of the unit (barcode_key) is not imported into ESP with the unit. Will use this when creating auto interplant units in Plant ULT, so that the producing plants barcode can be scanned when unit arrives in .

"[$s1]:=""0019419839""
[$s2]:=format([uniqueid],""000000000"")
[$barcode]:=[$s1]&[$s2]
[$d12]:=val(substring([$s2],2,1))
[$d13]:=val(substring([$s2],3,1))*3
[$d14]:=val(substring([$s2],4,1))
[$d15]:=val(substring([$s2],5,1))*3
[$d16]:=val(substring([$s2],6,1))
[$d17]:=val(substring([$s2],7,1))*3
[$d18]:=val(substring([$s2],8,1))
[$d19]:=val(substring([$s2],9,1))*3
[$chkdigit]:=[$d12] + [$d13]+ [$d14]+ [$d15] + [$d16]+[$d17]+ [$d18] + [$d19] +78
[$d20]:=(roundup([$chkdigit]/10)*10)-[$chkdigit]
[Userfieldul2]:=[$barcode]&[$d20]"

Kiwiplan - KDW Data Warehouse

Introduction

The document assumes that you have the server build with a Kiwiplan environment.

These are things that need to be done on after the installation of KDW.

These instructions may not follow the same folder setup our was installed using the “KDW” site name.

Environment

Add these to the environment to make it easier to navigate to the folders

export KIWIBASE=/KIWI
export KIWI=${KIWI:-/KIWI/site_$PLANTID}
export KBIN=${KBIN:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/bin}
export KLOG=${KLOG:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/log}
export KCONF=${KCONF:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/conf}

Logout and log back in to get the variables and be able to use them

Admin Console prep

You need to add the dashboard to the the pam authentication

cd $KCONF
sudo cp kdw-adminconsole_eample /etc/pam.d/kdw-adminconsole

Database Readiness

Run this to make sure there are no table lock.

USE master;
GO
ALTER DATABASE [kdw_master_datawarehouse]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [kdw_master_datawarehouse] SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE [kdw_master_datawarehouse] 
SET MULTI_USER;
GOUSE master;
GO
ALTER DATABASE [kdw_working_datawarehouse]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [kdw_working_datawarehouse] SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE [kdw_working_datawarehouse] 
SET MULTI_USER;
GO

MySQL Connector

https://dev.mysql.com/downloads/connector/j/

For 9.80.x revision you must use 5.41 as version 8.x.x do not work.

Extract the file and then copy the .jar to the DIST folder.

[/KIWI/services/etl/kdw/current/kiwiplan/dist]$
mysql-connector-java-5.1.44-bin.jar

Setting Console

cd /opt/kiwi/services/etl/kdw/current/kiwiplan/conf
cp kdw-adminconsole_example /etc/pam.d/kdw-adminconsole
chmod 777 /etc/pam.d/kdw-adminconsole

Extra Executors

To setup extra executors to reduce the time of execution for each system.

You will need to copy the executor properties files to another by adding a numeric value to the name

[/KIWI/services/etl/kdw/current/kiwiplan/conf]$ 
cp application_executor.properties application_executor1.properties

The new file needs to be edited and this value changed

## KDW executer name
kdw.executor-name=Main_Executor

To the following value

kdw.executor-name=Main_Executor1

Revision 9.8.x

Make sure you un the following.

[/KIWI/services/etl/kdw/current/kiwiplan/bin]$
dwdate_initialization.sh

Restart Script

You can create a restart.sh script with the following

[/KIWI/services/etl/kdw/current/kiwiplan/bin]$ 
vi restart.sh

Then add this in the file

:
./stopExecutors.sh
./stopSchedulers.sh
./startSchedulers.sh
sleep 15
./startExecutors.sh

Make the file executable for the system

[/KIWI/services/etl/kdw/current/kiwiplan/bin]$
chmod 777 restart.sh

Kiwiplan - Upgrade Shortcut Switches

These are placed on the shortcut of the upgrade.exe.

Upgrade Options Only (depreciated 7.80)

Evaluator:

Option: 1 Will show you the available revisions to upgrade from for the revision you are trying to upgrade to if it fails, it returns database versions though, not msi versions. 

(Depricated) Espdatapurge:

This is now on a service setup.

Purge data in ESP before upgrading, it uses different datetime fields depending on the record, and depends on the statuses of some objects to determine if the transactional record is purged

NOTE: If a docket is older than your purge time, if it is in printed status, it will not purge 

Upgrade Options Only

Applyscriptfolder:

This is to point the upgrade to look at another script folder but will bypass version checking so you can break things. C:\Kiwiplan\EspServ\Upgrade.exe applyscriptfolder

Importactivedirectory:

This is to import and sync the Active Directory after the upgrade completes. C:\Kiwiplan\EspServ\Upgrade.exe importactivedirectory

C:\Kiwiplan\EspServ\Upgrade.exe espdatapurge

C:\Kiwiplan\EspServ\Upgrade.exe evaluator:1

 

 

Kiwiplan - Bag of Tricks for ESP and MAP/MES

Agents

Agents

ESP Agent Retry Errors Setup

 

Agent can be set up to retry errors so that the system reprocesses failed requests.

 

InfClient/Section/Agent General

Name                                                                                      Scope  Value

Restart Error Text                                                                   whole  Automation error

Maximum Automatic Retry Count For Failed Requests         whole  3

 

InfClient/Section/Agent Errors To Retry

Name                                                              Scope  Value

Agent exited without completing request      whole  Agent exited

Cannot find matching dockets                        whole  Cannot find matching dockets

changed by another user                                whole  changed by another user

Clipboard                                                        whole  Clipboard

Deadlock                                                         whole  deadlock

Execution canceled                                         whole  Execution canceled

Operation Cancelled                                       whole  Operation canceled

PalletMovement                                             whole  PalletMovement

Updated by another user                               whole  Updated by another user      

 

Application Shortcut Line Options

The 1 equals true and the 0 equals false. You can also use True or False those will also turn these options on or off.

All of these options have the semicolon to separate the option with the True or False

Ex: PrintToFile:1 or PrintToFile:True

 

KiwiXplor & Agent Options

DBServer:

This is the hostname of the SQL server.

DBName:

This is the DataBase name to use on the SQL server.

DBPassword:

This is to specify a SQL DataBase password to log into SQL server.

DBLogin:

This is to specify a NT/Network SQL DataBase Login to log into SQL server.

NTLogin:

Replaces the Domain user name to the one specified.

Plant:

Is the plant code (from MAP) in GEN/PL

SQLLog:

This enables logging of calls to the SQL DataBase

Width:

Is the width of the ESP window that opens by default

Height:

Is the length of the ESP window that opens by default

Object Log:

Logs object usage in ESP, an interval defined in seconds

HostDebugLevel:

Is the Tcp connection between ESP and the Unix system (aka KIDS) debugging level 0 - 5 (5 = most detailed)

KiwiXplor Options Only

HostName:

This is to change the Linux server

HostDataSet:

This is to access the MAP DataSet that is specified in the KIDSENV file. For more info on this parameter look at the “First Time Installation Guide”.

HostUser:

This is to specify the Unix server login name to log into the MAP DataSet.

HostPassword:

This is to specify the Unix server login password to log into the MAP DataSet.

DBTimeOut:

Is a setting for the amount of time in seconds before the database should give up waiting for a reply from the SQL DataBase.

Agent Options Only (depreciated after 7.80)

AgentNumber:

This is used when more than one Agent has been setup in ESP to handle different types of requests

PrintToFile:

This is used if printing documents to a file instead of physically printing (good for testing).

ShowWord:

This is used if Word is to display the populating of templates as Word documents are printing

Freeze a ESP dataset in time

Freezes your dataset in time. To use this you will have to create a file in the ESPServ folder and call it “profile.ini” and then add the following within the file

[SheetPlant]
StartDateTime=10/30/2014 16:25:25
TimeZone=Eastern Standard Time
LocaleID=1033

[CHH]
StartDateTime=02/10/03 14:45:00
TimeZone=New Zealand Standard Time
LocaleID=5129

Using this option on the properties command line you have to use it like this

profilesection:CHH

Reference https://kall.kiwiplan.co.nz/kall/kiwiplan/issueViewer.do?action=viewIssue&searchId=7&issueId=72725

Example of KiwiXplor Options

KiwiXplor Target Line

 Example of a target:

“K:\Kiwiplan\Kiwiplan\KiwiXplor.exe” HOSTUSER:john HOSTPASSWORD:doe HOSTNAME:192.168.242.129 HOSTDATASET:Cinncinati DBSERVER:172.16.6.16 DBNAME:espbox

 Note: Make sure that there is a space between each of the options e.g.: HOSTUSER:john(space)

Example of Agent Options

Agent Target Line

 Example of a target:     

K:\Kiwiplan\Kiwiplan\EspAgent.exe AgentNumber:3

Tricks & Shortcuts

Main Keys

F4 is a shortcut key used to display a combo field box (drop down box).

Simply click into the field and hit F4.

F5 is a shortcut key used to bring up another window from within the current one you are in.

Simply click into the field (even if it is grayed out) and hit F5

E.g.:     If you are in the Product Design window you can click on the company field, press F5, and another window will open, displaying the company.

F8 is a shortcut key used to bring up special instructions in any Comment or Special Instruction field. These special instructions are stored in the Macro section of the relevant application.

Simply click into the field and hit F8

 

Alternative Manual Invoice/Credit Parameter

Several of the external Word docs in ESP are designed to allow for multiple templates of the same document. For example, it’s possible to have many different invoices. The standard invoice template is used unless a different template file is specified on the Documentation tab on the Company form for a particular company. This is the same for order confirmations, price lists, dockets etc. 

The manual invoice and credit are not set up this way. You cannot attach a different manual invoice template or manual credit template to a customer. However, there are two parameters that allow for the use of alternative template files for manual invoices and credits:

EspClient/Invoicing/ Alternative Manual Invoice Template Formula
EspClient/Invoicing/ Alternative Manual Credit Template Formula 

These parameters specify the template to be used for the manual invoice and manual credit using a business rule in the Value field.

Example:

If the parameter has the value:

iif([company.region]="Export", "ExportInvoice", iif([productcode]="Tool", "ServiceInvoice", "ManualInvoice"))

Then:

• If the region for the company is set to Export, then the ExportInvoice template is used.

• If the region is not set to Export and the Product code is Tool then the ServiceInvoice template is used.

• In all other cases, the ManualInvoice template is used.

 

Kiwiplan - Supplying Product Designs

This section describes how to set up one product design to be supplied by another. This scenario is needed when goods are produced for stock, but must be erected/finished at a future unknown date. This means we need a means of tracking stock of the unfinished product and finished product.

 ESP and MAP provide a solution for this using two stock PD’s. The first one (unfinished) is converted and booked into store. When the last conversion process is required, a topup of the second (finished) PD is placed. The feedback of this topup order will automatically decrease the stock of the first PD.

Parameters, Store and Machine Setup

Machine Setup

For this to work there are two machines that must be set up correctly. There must be a Supply From Stock machine. This machine should have the Supply operation 17, and be set to auto-feedback, triggered by feedback of the next step.

The second machine is the conversion machine that finishes the product. This machine should have standard feedback, the operations that are required to finish the product, and be attached to the store and location where the unfinished product will be stored.

Store Setup

The store location where the unfinished product is stored must have ‘Units from WIP label to be Available/Stock’ set to Y.

Parameters

Two parameters need to be set in MAP: 

• PCS FB 57 “Auto Board Xfers for Hort Jobs” = Y

• GEN PL 33 “FGS Supply Machine for Hort.” = machine number for Supply From Stock machine

Product Design Setup

Unfinished Product Design

This PD must have a stock line for the store that was specified on the finishing machine described previously in this section. 

The machine route should be set up as normal, without the finishing step. 

Finished Product Design

The finished PD must also have a stock line.

The machine route should be the Supply from Stock machine, the finishing machine and the strapper, so the operations on the PD need to be Supply board, whatever finishing operations are required, and strapping.

The machine route should have the unfinished PD number specified in the ‘supplying design number’ field.

Business Rule for Special Instruction 

There is currently nothing on the order for the finished PD to indicate that it is being supplied by another PD. However this can be easily remedied by writing a business rule to the Order business class that adds the supplying PD number to the special instructions.

Expression:

[Specialinstructions]:=[Specialinstructions] & iif([Ordertype]<>"calloff" AND isobjectvalid([Route.SupplyingProductDesign])=true AND findcount("to supply this order",[specialinstructions])=0,"  !!!!! Please use PD" & [Route.SupplyingProductDesign.Designnumber] & " to supply this order !!!!!","")

 

This rule appends a message to the existing special instructions, but only if the following conditions are true: 

• the order is not a calloff

• the route attached to the order has a supplying PD number

• the message isn’t already in the special instructions

 

The message that is added to the special instructions: 

!!!!! Please use PD**** to supply this order !!!!! 

where **** is the supplying PD number from the machine route.

Ordering, Feedback and Delivery 

Here is the sequence of events for this scenario: 

• topup is placed for the unfinished PD to increase the stock. It is converted and booked into store

• topup is placed for the finished PD

• the finishing step is fed back

• this automatically feeds back the Supply from Stock step, drops the unfinished stock levels and increases the finished stock levels

• calloff order is placed for delivery of finished product

Kiwiplan - Multi-Plant Harmonization

What fields and areas that need to be standardize in ESP and MAP. 

This section was put together from the aspect if we were going forward either with one MAP database or Multi-MAP databases scenario. 

All plants in one ESP database

Here is a list of fields/lookups that need to be standardized in the ESP database

1. Destination Codes

1. Styles for Descriptions and print outs

1. Stacking Patterns shared with MAP parameters 

All countries/states/provinces in a separate MAP Database

Here is a list of fields/lookups that need to be standardized all across the MAP databases

1. Colors

1. Pallet Types

1. Top Board Codes

1. Closure Codes

1. Strapping Codes

1. Stacking Patterns shared with ESP

1. Tab Types

1. Score Types

1. Ink Types

10)  Tools Types

11)  Tool Names when the tool is shared

12)  Machine Operations

13)  Unit Descriptions

14)  Basic Board Codes

15)  Downtime Codes

16)  Waste Codes

17)  Machine numbers

18)  BRD-ST, Material Status

19)  CORR-FL, Corrugator flutes     

20)  CSC-BS, Board structure

21)  CSC-CC, Coating codes  

22)  CSC-OS, Order status codes and desc    

23)  CSC-PC, Paper class    

24)  GEN-FB, Default waste codes    

25)  GEN-LT, Label types    

26)  GEN-QC, Down time categories  

27)  GEN-UM, Units of measure       

28)  GEN-WC, Waste code classes     

29)  PCS-QE, PCS - machine groups   

30)  PCS-QO, PCS - order status     

31)  PCS-QR, PCS - run rate units 

32)  Label Formats

33)  Board Code 

Board Codes

The image below shows how we would setup board codes to be used in MAP. The ESP sales board code would only be seen in ESP for costing and sale purposes. The plant board code would only be seen on the production side and used to produce the item. Depending on the local plant’s available papers the combination would change at every plant. The producing plant’s combination would be setup in the CSC system to have an upgrade downgrade path to the top board code from sale board code. 

NOTE: There is currently a limit to 16 upgrade and downgrade paths.

Kiwiplan - ULT Interface to Warehouse and Carriers

This is an interface that can be used to import loaded trucks at a remote location and also be able to ship them in ULT. 

Reference

https://kall.kiwiplan.co.nz/kall/kiwiplan/issueViewer.do?id=5447259

Programs

imptruck

This program imports the jobs and units that would be on the remote load 

shptruck

This program ships the truck in ULT. 

NOTE: You can also unship and undesptch via the interface to undo loads. 

Example of an interface running in Canada

 

Kiwiplan - ESP Stock Transfer

This is to be able to print out a stock transfer docket. 

ULT Setup

Set the following MAP parameters to enable export of transfer data:

GEN/EE/Layout Version for ULTDLD (ULT Export) to 2

pcsmenu:AP                   MAINTAIN PARAMETERS                    13/Sun 13:48
================================================================================

System  Description                    V/M
 GEN    General for many systems
Prefix  Description                    View_Mnt Default_key Many_records_allowed
  EE    Electronic Data Export             M                         Y
Number  Parameter                      Value
 1      Data Type                      ULTDLD
 2      Description                    Docket Export
 3      Directory Name                 /app01/kiwi/site_0068/work/ultdld/
 4      Last File Number Used          3
 5      Script name for File Export    EspRenameDkt
 6      Fixed record length 0=variable 0
 7      File Name (optional)           
 8      Layout Version                 2  

INV/DL/Allow File Export (Y/N) to Y

kwutils:C                    MAINTAIN PARAMETERS                    13/Sun 13:50
================================================================================

System  Description                    V/M
 INV    Inventory Management/Tracking
Prefix  Description                    View_Mnt Default_key Many_records_allowed
  DL    Despatch Load Parameters           M      PARAMS             N
Number  Parameter                      Value
 1      Key                            PARAMS
6	     Allow File Export (Y/N)        Y

INV/DL/Include in export transfer to Y

kwutils:C                    MAINTAIN PARAMETERS                    13/Sun 13:51
================================================================================

System  Description                    V/M
 INV    Inventory Management/Tracking
Prefix  Description                    View_Mnt Default_key Many_records_allowed
  DL    Despatch Load Parameters           M      PARAMS             N
Number  Parameter                      Value
35      Include in export transfer     Y

INV/DL/When set Load to "Shipped" Generate GLBOL export to Y

kwutils:C                    MAINTAIN PARAMETERS                    13/Sun 13:51
================================================================================

System  Description                    V/M
 INV    Inventory Management/Tracking
Prefix  Description                    View_Mnt Default_key Many_records_allowed
  DL    Despatch Load Parameters           M      PARAMS             N
Number  Parameter                      Value
33      When set Load to "Shipped"
34	      Generate DLBOL export        Y

ESP Setup

Ensure your FGS/Stores parameters in ESP are set correctly, in particular FGS/Stores/Store x - FGS stk store xfer machine number for each store you are transferring to and from.

Verify you store locations and added highlighted (FGS Stk Store, In Machine & Out Machine)

invmenu:C                       Select Store                        13/Sun 14:18
================================================================================
                                                             Total Stores =   14
Idx Store Description               1_L Plant Items Off Address Line 1
  5    80 WINSTON-SALEM MAIN FG STO   N     1       0   8080 N. Point Blvd
  6    81 Westpoint Warehouse         N     1       0   3946 Westpoint Blvd
  7   124 Reynolds Packaging          N     1       0   2249 Virginia Ave
  8   125 Richmond Corrugated         N     1       0   5301 Corrugated Rd
  9   126 Carolina Container          N     1       0   5701 Quality Way
 10   127 South Atlantic              N     1       0   3932 Westpoint Blvd
 11   128 Phoenix Packaging           N     1       0   125 E. 9th St.
 12   129 Driscoll Group              N     1       0   1084 W. 4th St.
 13   130 RKT Latta                   N     1       0   4461 Hwy 301 S
 14   143 Freeman Container           N     1       0   121 Freeman Park Drive

You must add the FROM and TO store number of the stores that will be used in the transfers in the despatchmode lookup table.

Once the store numbers are in the despatchmode lookup table then you must go to the relative address. To do this SEARCHàAddressàStore Number and enter the store number for the FROM and also on the TO and set the drop down for the despatchmode.

FROM

TO

Linux Server - Install RedHat 9.4

Summary

Instructions on the install and configuration of a Linux server for Kiwiplan.

Details

Prerequisites

VMware, KVM, Hyper-V machine to host the installation

You will need to use the following command when you vi your files as you cut and paste:

Once in vi you have to hit the colon and then type “set paste”

:set paste

This will not mess with the paste you put in.

Instructions 

Preparing the OS

This is to install the OS on a VmWare

VMWare Setup using GUI

Boot ISO

Select “Install RedHat Linux”

Select Language

Then Select “Network & Hostname” from install menu.

Set hostname and hit apply

Click Configure and set the IP

Click on IPv6 Settings and disable it

Select “Installation Destination”

Make sure you select custom to configure the drives and click done

Click on the blue link to auto configure them

Make sure the /home drive is highlighted and click the “-“ minus sign to remove that drive

Add the pink value of the space available after the delete of the home folder.

Enter “350 GiB” (total of drive space you created for the VM), this will recalculate the size of the drive and then hit “Done”.

Hit “Accept Changes”

Click on the “Root Password” and click “Allow root SSH login with password” and then click “Done”

Click “Connect to Redhat”

Enter you Account details unless you are building a VM template, if not then select the purpose of the server then click on “Register”

Click on “Done”

Click “Software Selection” and select “Minimal Install”

Click “Begin Installation”

When finished click on “Reboot System”

Network Text GUI

Login with root

NOTE: Run # nmtui Only if you did not do the GUI version

Select Edit connection

Select interface and hit enter

Use arrows to navigate

IPV4 change to manual and select show, fill in the information

IPV6 Change to disabled, press OK

Highlight Automatically connect and press the space bar

press OK

Start setup Instructions for RedHat Linux

Connect to the Server

Use a SSH connection to the server the remaining of the commands in order to cut and pates them in instead of using the VM interface.

Commands to run

This will show all command that need to be ran to update within the OS to start the configuration.

All the command are within each box

Update the System

After install update system packages for the needed configuration.

dnf upgrade -y

Disable SeLinux

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

 Disable firewall

systemctl disable firewalld.service

Epel Release

subscription-manager repos --enable codeready-builder-for-rhel-9-$(arch)-rpms
dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm -y

After Epel installation rerun the upgrade to update if any are needed

dnf upgrade -y

Install core software

Install Packages

Add the needed packages

Install required system packages

dnf install bind-utils bzip2 cups cifs-utils enscript ftp gdb ghostscript java-1.8.0-openjdk-headless java-11-openjdk-headless krb5-workstation ksh lftp lrzsz lsof libnsl lzop mlocate mutt ncompress net-tools net-snmp net-snmp-utils net-tools nfs-utils nmap nvme-cli openldap-clients openssh-clients psmisc realmd rsync samba-client strace sysstat tcpdump telnet telnet-server tmux unix2dos vim vim-enhanced vsftpd wget xfsdump vsftpd htop mc rsyslog rsyslog-doc postfix dbus-daemon s-nail dovecot cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain tree figlet toilet coreutils -y

If you are running on a virtual machine run the following

Install VM Tools

dnf install open-vm-tools -y

Swap Settings

sysctl vm.swappiness=10

Install vim color for scripting

dnf install git -y
git clone https://github.com/flazz/vim-colorschemes ~/.vim/
cp ~/.vim/colors/desert.vim /etc/vimrc.local

Configure Installed Packages

Configure the packages

Enable Telnet run the following to enable and start the packages

systemctl enable telnet.socket
systemctl restart telnet.socket

ONLY CHANGE FOR OLDER SSH ALGORYTHM IF NEEDED FOR LEGACY DEVICES

Legacy ssh connections

CONTINUE HERE IF SSH LEGACY IS NOT NEEDED

Enable Time Synchronization run the the following and add your domain time server

vi /etc/chrony.conf

Add your server below the following and make sure you change the domain name from sflservicesllc.com

# These servers were defined in the installation:
#server _gateway iburst
server domain.sflserviesllc.com iburst prefer

Enable Time Synchronization

systemctl enable --now chronyd

Note: Time-zone changes are made with the command

timedatectl

Make the following changes within the file

sudo sed -i 's/^anonymous_enable=.*/anonymous_enable=NO/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^local_umask=.*/local_umask=002/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^#ascii_upload_enable=.*/ascii_upload_enable=YES/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^#ascii_download_enable=.*/ascii_download_enable=YES/' /etc/vsftpd/vsftpd.conf

Start and Enable FTP for transfer in and out for us or other vendors

systemctl enable --now vsftpd.service

ONLY CHANGE THE FOLLOWING IF YOU HAVE REMOTE LOGGING

Remote Logging

Then add the following at the end of the file but make sure you update the IP address and replace the XXX's

sudo tee -a /etc/rsyslog.conf > /dev/null << 'EOF'

# Forwarding Rules
*.* @192.168.XXX.XXX:514   # use @ for UDP Protocol
*.* @@192.168.XXX.XXX:514   # use @@ for TCP Protocol
auth.* @192.168.XXX.XXX:514 # only for authentication based records
EOF

CONTINUE HERE IF NOT REMOTE LOGGING

Enable Rsyslog to enable logging locally and remotely

systemctl enable --now rsyslog

Emailing backup the original file

cp /etc/postfix/main.cf /etc/postfix/main.cf.bak

Update the following with a remote or local email server and make sure you change the domain name from sflservicesllc.com

# Set host, domain, and relayhost
sudo postconf -e "myhostname = sflservicesllc.com"
sudo postconf -e "mydomain = sflservicesllc.com"
sudo postconf -e "relayhost = mail.sflservicesllc.com"

# Check for syntax/configuration errors
sudo postfix check

# Reload Postfix to apply the configuration changes live
sudo postfix reload

# Check the specific parameters
postconf myhostname mydomain relayhost

Optional: Update the following for no email server and make sure you change the domain name from sflservicesllc.com and only allow certain networks

# Set mynetworks
sudo postconf -e "mynetworks = 127.0.0.0/8, 168.100.189.0/28"

# Verify the updated parameter
postconf mynetworks

# Check syntax and reload Postfix
sudo postfix check
sudo postfix reload

Enable Postfix

systemctl enable --now postfix

Test your config this way

echo "Install of Linux Rocks $HOSTNAME" | sendmail steve.ling@sflservicesllc.com

Locale add-ons to make sure that ANSI applications display correctly so edit the following and replace all of the values with the following

Edit the following:

sudo cat > /etc/locale.conf << EOF
LANG="en_US"
SUPPORTED="en_GB:en_GB-.UTF-8:en_US:en_US.UTF-8:de_DE:de_DE.UTF-8"
SYSFONT="latarcyrheb-sun16"
EOF

ONCE ADDED THE ABOVE CONFIGURATION THEN EXIT AND LOG BACK IN

Bash colors and Prompt

Bash for root for color edit the following:

Dependent on

dnf install figlet toilet -y

Creating Aliases

sudo cat > /etc/profile.d/custom-aliases.sh << EOF
# Common interactive aliases for all users

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias vi='vim'
alias tailf='tail -f'
EOF

Server Warning

sudo tee /etc/profile.d/custom-prod-warning.sh > /dev/null << 'EOF'
# /etc/profile.d/custom-prod-warning.sh
# Production warning banner – only for real interactive logins

# 1. Exit immediately if this is not an interactive shell
[[ $- != *i* ]] && return

# 2. Exit if we don't have a real terminal
[[ -t 0 && -t 1 ]] || return

# 3. Exit if SSH is running a forced command (rsync, scp, ansible, etc.)
#    OpenSSH sets SSH_ORIGINAL_COMMAND in these cases
[[ -n "${SSH_ORIGINAL_COMMAND:-}" ]] && return

# --- Safe to show the banner from here ---

RED='\033[1;31m'
YELLOW='\033[1;33m'
NC='\033[0m'

echo
if command -v figlet >/dev/null 2>&1; then
    figlet -f big "PRODUCTION" | while IFS= read -r line; do
        printf '%b%s%b\n' "$RED" "$line" "$NC"
    done
    figlet -f big "SERVER" | while IFS= read -r line; do
        printf '%b%s%b\n' "$RED" "$line" "$NC"
    done
else
    echo -e "${RED}***** PRODUCTION SERVER!! *****${NC}"
fi

echo
echo -e "${YELLOW} *** EXTREME CAUTION REQUIRED ***${NC}"
echo -e "${RED}This is a LIVE PRODUCTION system.${NC}"
echo -e "${RED}Unauthorized access is strictly prohibited.${NC}"
echo -e "${RED}All activity is logged and monitored.${NC}"
echo -e "${YELLOW}Think twice before running commands!${NC}"
echo
EOF

Creating Colorful Prompts

sudo tee /etc/profile.d/custom-prompt-and-colors.sh > /dev/null << 'EOF'
# Custom LS_COLORS and improved multi-line colored prompt

# Vibrant LS_COLORS (directories bold blue on gray, etc.)
LS_COLORS='rs=0:di=01;44:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:'
export LS_COLORS

# Only set up the fancy colored prompt in real interactive terminals
if [[ $- == *i* ]] && [[ -t 0 && -t 1 ]] && [[ -z "${SSH_ORIGINAL_COMMAND:-}" ]]; then

    # Color and style variables
    RESET=$(tput sgr0)
    BOLD=$(tput bold)
    BG_GRAY=$(tput setab 8)
    FG_RED=$(tput setaf 1)
    FG_MAGENTA=$(tput setaf 5)
    FG_GREEN=$(tput setaf 2)
    FG_CYAN=$(tput setaf 6)

    # Multi-line prompt – all non-printing sequences wrapped in \[ \]
    PS1="\n"
    PS1+="\[${BOLD}${BG_GRAY}${FG_RED}\]\u"
    PS1+="\[${FG_MAGENTA}\]@"
    PS1+="\[${FG_RED}\]\`hostname\`"
    PS1+="\[${FG_GREEN}\] : \`uname\` : "
    PS1+="\[${FG_CYAN}\]\d \t : \${PLANTID:+\$PLANTID }"
    PS1+="\[${RESET}\]\n"
    PS1+="\[${FG_CYAN}\][\w]"
    PS1+="\[${RESET}${BOLD}\] \$ \[${RESET}\]"

    export PS1
fi
EOF

Database Install and Configuration

Configure MariaDB

OPTIONAL ADD THE MARIADB REPO TO GET THE LATES REVISION

MARIADB REPORTED A PROBLEM WITH 10.5, YOU SHOULD CONSIDER UPGRADING TO 10.11

Install the new Repo for MariaDB

curl -sLS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

This is the location of

vi /etc/yum.repos.d/mariadb.repo

You have to change the yellow below to get the desired version

10.11 Revision

[mariadb-main] name = MariaDB Server baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.11/yum/rhel/9/x86_64 gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY gpgcheck = 1 enabled = 1 module_hotfixes = 1 [mariadb-maxscale] # To use the latest stable release of MaxScale, use "latest" as the version # To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version name = MariaDB MaxScale baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/9/x86_64 gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY gpgcheck = 1 enabled = 1

You will have to remove the current one installed if it exists and then install the new one

dnf remove mariadb-server -y
dnf install mariadb-server -y

Run the following to edit the MariaDB service file

# Edit the file
sudo tee /etc/systemd/system/mariadb.service.d/override.conf > /dev/null << 'EOF'
[Service]
LimitNOFILE=32768
EOF

# 1. Tell systemd to scan for unit updates
sudo systemctl daemon-reload

# 2. Restart MariaDB to apply the new open file limits
sudo systemctl restart mariadb

# 3. Verify the applied LimitNOFILE setting for the running process
sudo systemctl show mariadb -p LimitNOFILE

Run the following to add the client socket to the [client] section

sudo awk '
  /^\[client\]/ { print; print "socket=/var/lib/mysql/mysql.sock"; added=1; next }
  { print }
  END { if (!added) print "\n[client]\nsocket=/var/lib/mysql/mysql.sock" }
' /etc/my.cnf.d/client.cnf | sudo tee /etc/my.cnf.d/client.cnf.tmp > /dev/null \
&& sudo mv /etc/my.cnf.d/client.cnf.tmp /etc/my.cnf.d/client.cnf

Verify the following

cat /etc/my.cnf.d/client.cnf

Run the following

vi /etc/my.cnf.d/server.cnf

Add the following below the [mysqld] section and you will have to use the :set paste command or it will not paste correctly

BELOW IS FOR SERVER 8CPU AND 64GIG MEMORY

#Custom
performance_schema = ON
tmpdir = /run/mariadb
thread_cache_size = 4
table_open_cache = 16384
table_definition_cache = 8384
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

query_cache_type = 0
query_cache_size = 0
query_cache_limit = 128M
query_cache_strip_comments = 1

tmp_table_size = 512M
max_heap_table_size = 512M

max_connections = 750
max_allowed_packet = 24M
sort_buffer_size = 24M
join_buffer_size = 48M

innodb_buffer_pool_size = 48G           # 65-75% of RAM for InnoDB cache --> Keeping some for VUE
innodb_buffer_pool_instances = 6        # One instance per CPU core --> Keeping some for VUE 2 out of 8
innodb_flush_method = O_DIRECT          # Reduce I/O overhead
innodb_flush_log_at_trx_commit = 2      # Balance performance and durability
innodb_log_buffer_size = 16M            # Buffer for transaction logs
innodb_thread_concurrency = 0           # Let MySQL manage threads (0 = unlimited)
innodb_io_capacity = 2000               # Adjust based on storage IOPS
innodb_io_capacity_max = 4000           # Max IOPS for bursts
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 2      # Balance performance and durability --> Changed from 0
innodb_file_per_table
innodb_log_file_size = 2G               # Larger logs for better write performance --> Changed from 512

#Optional configuration for transaction logging Change for mysql or mariadb
#log_bin = /var/log/mariadb/mariadb.log
#expire_logs_days = 2

Reload the changes

systemctl daemon-reload

Enable and start MySQL/MariaDB on boot

systemctl enable --now mariadb.service

Run the following

mysql

Then execute the following statements also make sure to (change passwords as needed XXXXXX but NOT the users names)

DROP USER 'kiwisql'@'%';
FLUSH PRIVILEGES;
CREATE USER 'kiwisql'@'%' IDENTIFIED BY 'XXXXXX';
GRANT all ON *.* TO 'kiwisql'@'%' WITH GRANT OPTION;

DROP USER 'kiwisql'@'localhost';
FLUSH PRIVILEGES;
CREATE USER 'kiwisql'@'localhost' IDENTIFIED BY 'XXXXXX';
GRANT all ON *.* TO 'kiwisql'@'localhost' WITH GRANT OPTION;

DROP USER 'kiwilive'@'localhost';
FLUSH PRIVILEGES;
CREATE USER 'kiwilive'@'localhost' IDENTIFIED BY 'XXXXXX';
GRANT all ON *.* TO 'kiwilive'@'localhost';

DROP USER 'remuser'@'%';
FLUSH PRIVILEGES;
CREATE USER 'remuser'@'%' IDENTIFIED BY 'XXXXXX';
GRANT all ON *.* TO 'remuser'@'%' WITH GRANT OPTION;

DROP USER 'remuser'@'localhost';
FLUSH PRIVILEGES;
CREATE USER 'remuser'@'localhost' IDENTIFIED BY 'XXXXXX';
GRANT all ON *.* TO 'remuser'@'localhost' WITH GRANT OPTION;

DROP USER 'reports'@'%';
FLUSH PRIVILEGES;
CREATE USER 'reports'@'%' IDENTIFIED BY 'readonly';
GRANT SHOW DATABASES, SELECT, EXECUTE, PROCESS, SHOW VIEW  ON *.* TO 'reports'@'%';
FLUSH PRIVILEGES;
quit

Build skeleton for the Kiwiplan Environment

Command to Run

Setup the user environment

Remember to change the remuser & esp password

ESP username password is the global password store in ESP to let it connect and is unfortunately visible to an admin user but please make sure to (change passwords as needed XXXXXX but NOT the users names)

groupadd kiwiplan
mkdir /opt/kiwi
ln -s /opt/kiwi/ /KIWI
mkdir /opt/kiwi/{home,rev}
mkdir /opt/kiwi/rev/{map,mes}
useradd -g kiwiplan -G wheel -d /KIWI/home/remuser/ remuser
echo XXXXXXXXXX | passwd --stdin remuser
useradd -g kiwiplan -d /KIWI/home/esp/ esp
echo XXXXXXXXXX | passwd --stdin esp
chown -R remuser:kiwiplan /opt/kiwi
mkdir /etc/kiwiplan
chown remuser:kiwiplan /etc/kiwiplan

Systemd Socket Implementation For KIDD

Configure kidd.stocket

Remember your :set paste command

Create a socket file and the following

vi /lib/systemd/system/kidd.socket

Then add the following

[Unit]
Description=KIDD
[Socket]
ListenStream=2326
Accept=yes

[Install]
WantedBy=sockets.target

Create a service file by editing the following

vi /lib/systemd/system/kidd@.service

Then add the following

[Unit]
Description=Kidd Server
[Service]
ExecStart=/KIWI/rev/current/progs/kidd -a -p -F 1
User=root
Group=root
StandardInput=socket
StandardOutput=socket

# Resource configuration
LimitNOFILE=8192
LimitNPROC=4096

# MaxConnections=nnn - The maximum number of connections to simultaneously run services instances for
#  when Accept=true is set. nnn should be sized accordingly for the implementation.
MaxConnections=200

# The maximum number of connections for a service per source IP address. 
# This is very similar to the MaxConnections= directive above. Disabled by default.
MaxConnectionsPerSource=80

Start the service and enable on reboot

systemctl start kidd.socket
systemctl enable kidd.socket

This will only work once you have the Kiwiplan environment configured/installed

VUE Installation Configurations

Adjust system limits for VUE products

Edit the following

sudo tee -a /etc/security/limits.d/20-nproc.conf > /dev/null << 'EOF'

# Soft process limit for kiwiplan group
@kiwiplan soft nproc 32768
EOF

Verify the edit 

cat /etc/security/limits.d/20-nproc.conf

Edit the following

sudo tee -a /etc/security/limits.conf > /dev/null << 'EOF'

# Open file limits for kiwiplan group
@kiwiplan - nofile 32768
EOF

Verify the edit

cat /etc/security/limits.conf

Installing MsSQL Tool

Configure MsSQL tools

Install the repo for the MsSQL service/connection

curl https://packages.microsoft.com/config/rhel/9/prod.repo > /etc/yum.repos.d/msprod.repo

This will only work once you have the Kiwiplan environment configured/installed

Install the packages make sure you answer/type YES to all

dnf remove mssql-tools unixODBC-utf16-devel
dnf install mssql-tools unixODBC-devel -y

STOP HERE FOR YOUR TEMPLATE BUILD

RESTART FROM HERE AFTER TEMPLATE BUILD

Linux RedHat Registration

Re-Register the server with RedHat if you entered initial credentials to add the server

You need to run the nmtui command to bring up the menu from above in this documentation and then simply change the IP and also the  hostname

Run the following and this will change the subscription details

subscription-manager register --force
subscription-manager attach --auto

Activate Directory Setup

Using SSSD

Install the following packages

yum install sssd sssd-tools realmd samba-common-tools oddjob oddjob-mkhomedir adcli samba -y

Run this just in-case your domain is still using RC4

update-crypto-policies --set DEFAULT:AD-SUPPORT

Configure KRB5

Edit the following file and make sure the following is added/changed

vi /etc/krb5.conf

Add or changed the following below make sure yo change the ONLING.COM to your domain name

# To opt out of the system crypto-policies configuration of krb5, remove the # symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated. includedir /etc/krb5.conf.d/ [logging]     default = FILE:/var/log/krb5libs.log     kdc = FILE:/var/log/krb5kdc.log     admin_server = FILE:/var/log/kadmind.log [libdefaults]     dns_lookup_realm = true     ticket_lifetime = 24h     renew_lifetime = 7d     forwardable = true     rdns = true     pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt     spake_preauth_groups = edwards25519   dns_canonicalize_hostname = fallback   qualify_shortname = ""     default_realm = ONLING.COM     default_ccache_name = KEYRING:persistent:%{uid}     udp_preference_limit = 0   [realms]  ONLING.COM = {      kdc = domain_controller..onling.com      admin_server = domain_controller..onling.com  } [domain_realm]  .onling.com = ONLING.COM  onling.com = ONLING.COM

Join the server to the domain

Configurating the SSSD (in yellow update to site specific) (in green needs to be capitalized)

You must have the access to add to the AD server or the server has to be add first before you run the following command.

For the join you may need to specify the -OU's for the specific location

# realm discover onling.com # realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com # authselect select sssd with-mkhomedir  # authselect apply-changes

Copy from below and make changes

realm discover onling.com
realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com
authselect select sssd with-mkhomedir
authselect apply-changes

Update SSSD config

Once joined then you can change or add the following parameters

vi /etc/sssd/sssd.conf

[sssd] domains = onling.com config_file_version = 2 services = nss, pam   [domain/onling.com] default_shell = /bin/bash ad_server = sfl-dom-001.onling.com krb5_store_password_if_offline = True cache_credentials = True krb5_realm = ONLING.COM realmd_tags = manages-system joined-with-adcli id_provider = ad fallback_homedir = /home/%d/%u ad_domain = onling.com use_fully_qualified_names = false ldap_id_mapping = True access_provider = ad override_gid = 1000 ad_gpo_ignore_unreadable = true ad_gpo_access_control = disabled timeout = 300

Restarting SSSD

systemctl stop sssd; sss_cache -E; systemctl start sssd

Stop Here

Optional SSSD configurations

Defaults to no Shell Access, allows short usernames

Optional (Group allow) additional parameters and changes

simple_allow_groups = sssd-users OR simple_allow_groups =  gg-C3193???-KiwiTS_access

Add users to SSSD

Optional grant shell access and force primary group to kiwiplan, change john.doe to the username

sss_override user-add john.doe -g $(getent group kiwiplan | cut -d: -f3) -s /bin/bash -h /KIWI/home/john.doe

Linux Server - Install RedHat 8.6

Summary

Instructions on the install and configuration of a Linux server for Kiwiplan.

Details

Prerequisites

VMware, KVM, Hyper-V machine to host the installation

You will need to use the following command when you vi your files as you cut and paste:

Once in vi you have to hit the colon and then type “set paste”

:set paste

This will not mess with the paste you put in.

Instructions 

Preparing the OS

This is to install the OS on a VmWare

VMWare Setup using GUI

Boot ISO

Select “Install RedHat Linux”

Select Language

Then Select “Network & Hostname” from install menu.

Set hostname and hit apply

Click Configure and set the IP

Click on IPv6 Settings and disable it

Register Redhat

Software Selection

Select Minimal install with nothing else checked

Select “Installation Destination

Make sure you select custom to configure the drives and click done

Click on the Hyper Link

Click on the /home folder and then the minus sign

Enter the total original in the Desired Capacity

Click on any of the drives and the screen will recalculate the space to the “/” folder which is what we need.

Accept Changes

Root Password

Set “root” Password 

Click “Begin Installation”

When finished click on “Reboot System”

Connect to the Server

Use a SSH connection to the server the remaining of the commands in order to cut and pates them in instead of using the VM interface.

Commands to run

This will show all command that need to be ran to update within the OS to start the configuration.

All the command are within each box

Update the System

After install update system packages for the needed configuration.

dnf upgrade -y

Disable SeLinux

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

Optional: Manually adjust the file which is located here if you wish to edit manually

vi /etc/sysconfig/selinux

 Disable firewall

systemctl disable firewalld.service

Epel Release

subscription-manager repos --enable codeready-builder-for-rhel-8-$(arch)-rpms
dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

After Epel installation rerun the upgrade to update if any are needed

dnf upgrade -y

Install core software

Install Packages

Add the needed packages

Install required system packages

dnf install bind-utils bzip2 cups cifs-utils enscript ftp gdb ghostscript java-1.8.0-openjdk-headless java-11-openjdk-headless krb5-workstation ksh lftp lrzsz lsof libnsl lzop mariadb-server mlocate mutt ncompress net-tools net-snmp net-snmp-utils net-tools nfs-utils nmap nvme-cli openldap-clients openssh-clients psmisc realmd rsync samba-client strace sysstat tcpdump telnet telnet-server tmux unix2dos vim vim-enhanced vsftpd wget xfsdump vsftpd htop mc rsyslog rsyslog-doc postfix dbus-daemon s-nail dovecot cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain tree figlet toilet coreutils -y

If you are running on a virtual machine run the following

dnf install open-vm-tools -y
sysctl vm.swappiness=10

Install vim color for scripting

dnf install git -y
git clone https://github.com/flazz/vim-colorschemes ~/.vim/
cp ~/.vim/colors/desert.vim /etc/vimrc.local

Configure Installed Packages

Configure the packages

Enable Telnet run the following to enable and start the packages

systemctl enable telnet.socket
systemctl restart telnet.socket

Enable Time Synchronization run the the following and add your domain time server

vi /etc/chrony.conf

Add your server below the following and make sure you change the domain name from sflservicesllc.com

# These servers were defined in the installation:
#server _gateway iburst
server domain.sflserviesllc.com iburst

Enable Time Synchronization

systemctl enable --now chronyd

Note: Time-zone changes are made with the command

timedatectl

Enable FTP run the following 

vi /etc/vsftpd/vsftpd.conf

Make the following changes within the file

anonymous_enable=NO
local_umask=002
ascii_upload_enable=YES
ascii_download_enable=YES

Start and Enable FTP for transfer in and out for us or other vendors

systemctl enable --now vsftpd.service

If you have a remote logging server setup then edit the following command

vi /etc/rsyslog.conf

Then add the following at the end of the file but make sure you update the IP address and replace the XXX's

*.* @192.168.XXX.XXX:514   # use @ for UDP Protocal
*.* @@192.168.XXX.XXX:514   # use @@ for TCP Protocal

Enable Rsyslog to enable logging locally and remotely

systemctl enable --now rsyslog

Configure Postfix for Email run the following

vi /etc/postfix/main.cf

Update the following with your email server

relayhost = [ENTER IP or SMTP SERVER] or [sflservicesllc-com.mail.protection.outlook.com]:25

Optional if you want to hardcode a domain name run the following

vi /etc/postfix/main.cf

Update the following with a remote or local email server and make sure you change the domain name from sflservicesllc.com

myhostname = sflservicesllc.com

mydomain = sflservicesllc.com

mydestination =

relayhost = [ENTER IP or SMTP SERVER] or [sflservicesllc-com.mail.protection.outlook.com]:25

 Optional f you do not have a email remote/local server

vi /etc/postfix/main.cf

Update the following for no email server and make sure you change the domain name from sflservicesllc.com

myhostname = sflservicesllc.com

mydomain = sflservicesllc.com

mail_spool_directory = /var/mail

mynetworks = 127.0.0.0/8, 168.100.189.0/28

Enable Postfix

systemctl enable --now postfix

Test your config this way

echo "Install of Linux Rocks $HOSTNAME" | sendmail steve.ling@sflservicesllc.com

Locale add-ons to make sure that ANSI applications display correctly so edit the following and replace all of the values with the following

Edit the following:

vi /etc/locale.conf

Change the following:

sudo cat > /etc/locale.conf << EOF
LANG="en_US"
SUPPORTED="en_GB:en_GB-.UTF-8:en_US:en_US.UTF-8:de_DE:de_DE.UTF-8"
SYSFONT="latarcyrheb-sun16"
EOF

Once added the locals exit and telnet back into the server to take the update

Bash colors and Prompt

Bash for root for color edit the following

Dependent on

dnf install figlet toilet -y

Creating Aliases

sudo cat > /etc/profile.d/custom-aliases.sh << EOF
# Common interactive aliases for all users

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias vi='vim'
alias tailf='tail -f'
EOF

Server Warning

sudo tee /etc/profile.d/custom-prod-warning.sh > /dev/null << 'EOF'
# Colorful PRODUCTION warning banner for interactive logins

if [[ -n "$PS1" && -t 0 && -t 1 ]]; then  # Interactive terminal with stdin/stdout
    RED='\033[1;31m'   # Bold red
    YELLOW='\033[1;33m' # Bold yellow
    NC='\033[0m'       # No color

    echo
    if command -v figlet >/dev/null 2>&1; then
        figlet -f big "PRODUCTION" | while IFS= read -r line; do
            printf '%b%s%b\n' "$RED" "$line" "$NC"
        done
        figlet -f big "SERVER" | while IFS= read -r line; do
            printf '%b%s%b\n' "$RED" "$line" "$NC"
        done
    else
        echo -e "${RED}***** PRODUCTION SERVER!! *****${NC}"
    fi

    echo
    echo -e "${YELLOW} *** EXTREME CAUTION REQUIRED ***${NC}"
    echo -e "${RED}This is a LIVE PRODUCTION system.${NC}"
    echo -e "${RED}Unauthorized access is strictly prohibited.${NC}"
    echo -e "${RED}All activity is logged and monitored.${NC}"
    echo -e "${YELLOW}Think twice before running commands!${NC}"
    echo

    # Pause briefly to ensure user sees the warning
    read -t 5 -p "Press Enter to continue (auto-continue in 5 seconds)... " || true
    echo
fi
EOF

Creating Colorful Prompts

sudo tee /etc/profile.d/custom-prompt-and-colors.sh > /dev/null << 'EOF'
# Custom LS_COLORS and improved multi-line colored prompt

# Vibrant LS_COLORS (directories bold blue on gray, etc.)
LS_COLORS='rs=0:di=01;44:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:'
export LS_COLORS

# Color and style variables (cached tput calls for speed)
RESET=$(tput sgr0)
BOLD=$(tput bold)
BG_GRAY=$(tput setab 8)
FG_RED=$(tput setaf 1)
FG_MAGENTA=$(tput setaf 5)
FG_GREEN=$(tput setaf 2)
FG_CYAN=$(tput setaf 6)

# Multi-line prompt
PS1="\n${BOLD}${BG_GRAY}${FG_RED}\\u${FG_MAGENTA}@${FG_RED}\`hostname\`${FG_GREEN} : \`uname\` : ${FG_CYAN}\\d \\t : \${PLANTID:+\$PLANTID }\n${FG_CYAN}[\\w]${RESET}${BOLD} \\\$ ${RESET}"
export PS1
EOF

Database Install and Configuration

Configure MariaDB

Optional: To add the MariadB repo to get the latest revision other then the one that is included in the RedHat

MySQL/MariaDB reported a problem with 10.5, you should consider upgrading to 10.6

Install the new Repo for MariaDB

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

This is the location of

vi /etc/yum.repos.d/MariaDB.repo

You have to change the yellow below to get the desired version

[mariadb] name = MariaDB baseurl = https://rpm.mariadb.org/10.6/rhel/$releasever/$basearch gpgkey= https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

Done with Optional

Run the following

systemctl edit mariadb.service

Then add the following to the file in between these lines or if blank just add and save

### Editing /etc/systemd/system/mariadb.service.d/override.conf
### Anything between here and the comment below will become the new contents of the file

[Service]
LimitNOFILE=32768

### Lines below this comment will be discarded

Run the following

vi /etc/my.cnf.d/client.cnf

Add the following below the [client] section

socket=/var/lib/mysql/mysql.sock

Run the following

vi /etc/my.cnf.d/mariadb-server.cnf

Add the following below the [mysqld] section and you will have to use the :set paste command or it will not paste correctly

#Custom
performance_schema = ON
tmpdir = /run/mariadb
thread_cache_size = 4
table_open_cache = 16384
table_definition_cache = 8384
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

query_cache_type = 0
query_cache_size = 0
query_cache_limit = 128M
query_cache_strip_comments = 1

tmp_table_size = 512M
max_heap_table_size = 512M

max_connections = 512
max_allowed_packet = 24M
sort_buffer_size = 24M
join_buffer_size = 48M

innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table
innodb_log_file_size = 512M

#Optional configuration for transaction loggin
log_bin = /var/log/mariadb/mariadb.log
expire_logs_days = 2

Reload the changes

systemctl daemon-reload

Enable and start MySQL/MariaDB on boot

systemctl enable --now mariadb.service

Run the following

mysql

Then execute the following statements also make sure to (change passwords as needed XXXXXXXXXX but NOT the users names)

CREATE USER 'kiwisql'@'%' IDENTIFIED BY 'XXXXXXXXXX';
GRANT all ON *.* TO 'kiwisql'@'%' WITH GRANT OPTION;
CREATE USER 'kiwisql'@'localhost' IDENTIFIED BY 'XXXXXXXXXX';
GRANT all ON *.* TO 'kiwisql'@'localhost' WITH GRANT OPTION;
CREATE USER 'kiwilive'@'localhost' IDENTIFIED BY 'XXXXXXXXXX';
GRANT all ON *.* TO 'kiwilive'@'localhost';
CREATE USER 'remuser'@'localhost' IDENTIFIED BY 'XXXXXXXXXX';
GRANT all ON *.* TO 'remuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'reports'@'%' IDENTIFIED BY 'XXXXXXXXXX';
GRANT SHOW DATABASES, SELECT, EXECUTE, PROCESS, SHOW VIEW  ON *.* TO 'reports'@'%';
FLUSH PRIVILEGES;
Quit

Build skeleton for the Kiwiplan Environment

Command to Run

Setup the user environment

Remember to change the remuser & esp password

ESP username password is the global password store in ESP to let it connect and is unfortunately visible to an admin user but please make sure to (change passwords as needed XXXXXXXXXX but NOT the users names)

groupadd kiwiplan
mkdir /opt/kiwi
ln -s /opt/kiwi/ /KIWI
mkdir /opt/kiwi/{home,rev}
mkdir /opt/kiwi/rev/{map,mes}
useradd -g kiwiplan -G wheel -d /KIWI/home/remuser/ remuser
echo XXXXXXXXXX | passwd --stdin remuser
useradd -g kiwiplan -d /KIWI/home/esp/ esp
echo XXXXXXXXXX | passwd --stdin esp
chown -R remuser:kiwiplan /opt/kiwi
mkdir /etc/kiwiplan
chown remuser:kiwiplan /etc/kiwiplan

Configure kidds

Remember your :set paste command

Edit xinetd.conf:

vi /etc/xinetd.conf

Add the following ad the end of the # Define access restriction defaults section

instances       = 200
per_source      = 80

Should look like this:

# # This is the master xinetd configuration file. Settings in the # default section will be inherited by all service configurations # unless explicitly overridden in the service configuration. See # xinetd.conf in the man pages for a more detailed explanation of # these attributes. defaults { # The next two items are intended to be a quick access place to # temporarily enable or disable services. # #       enabled         = #       disabled        = # Define general logging characteristics.         log_type        = SYSLOG daemon info         log_on_failure  = HOST         log_on_success  = PID HOST DURATION EXIT # Define access restriction defaults # #       no_access       = #       only_from       = #       max_load        = 0         cps             = 50 10         instances       = 200         per_source      = 80 # Address and networking defaults # #       bind            = #       mdns            = yes         v6only          = no # setup environmental attributes # #       passenv         =         groups          = yes         umask           = 002 # Generally, banners are not used. This sets up their global defaults # #       banner          = #       banner_fail     = #       banner_success  = } includedir /etc/xinetd.d

Append to end of the services:

kidd            2326/tcp                # Kiwiplan Interface Daemon

Run the following to add the kidds service:

sudo tee /etc/xinetd.d/kidd <<EOF
# default: on
# description: The kidd server serves kidd sessions; it uses \
# unencrypted username/password pairs for authentication.
service kidd
{
        disable = no
        flags           = REUSE
        socket_type     = stream
        wait            = no
        user            = root
        server          = /KIWI/rev/current/progs/kidd
        server_args     = -a -p -F 1
        log_on_failure  += USERID
        env             = HOME=/tmp
}
EOF

Start the service:

systemctl start kidd.socket
systemctl enable kidd.socket

The above will only work once you have the Kiwiplan environment configured/installed

VUE Installation Configurations

Adjust system limits for VUE products

Edit the following

vi /etc/security/limits.d/20-nproc.conf

Add the following 

@kiwiplan  soft     nproc     32768

Edit the following

vi /etc/security/limits.conf

Then add the following before the #End of file

@kiwiplan     -     nofile     32768

Installing MsSQL Tool

Configure MsSQL tools

Install the repo for the MsSQL service/connection

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo

This will only work once you have the Kiwiplan environment configured/installed

Install the packages make sure you answer/type YES to all

dnf remove mssql-tools unixODBC-utf16-devel
dnf install mssql-tools unixODBC-devel -y

STOP HERE FOR YOUR TEMPLATE BUILD

RESTART FROM HERE AFTER TEMPLATE BUILD

Linux RedHat Registration

Re-Register the server with RedHat if you entered initial credentials to add the server

You need to run the nmtui command to bring up the menu from above in this documentation and then simply change the IP and also the  hostname

Run the following and this will change the subscription details

subscription-manager register --force
subscription-manager attach --auto

Activate Directory Setup

Using SSSD

Install the following packages

yum install sssd sssd-tools realmd samba-common-tools oddjob oddjob-mkhomedir adcli samba -y

Run this just in-case your domain is still using RC4

update-crypto-policies --set DEFAULT:AD-SUPPORT

Configure KRB5

Edit the following file and make sure the following is added/changed

vi /etc/krb5.conf

Add or changed the following below

# To opt out of the system crypto-policies configuration of krb5, remove the # symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated. includedir /etc/krb5.conf.d/ [logging]     default = FILE:/var/log/krb5libs.log     kdc = FILE:/var/log/krb5kdc.log     admin_server = FILE:/var/log/kadmind.log [libdefaults]     dns_lookup_realm = true     ticket_lifetime = 24h     renew_lifetime = 7d     forwardable = true     rdns = true     pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt     spake_preauth_groups = edwards25519   dns_canonicalize_hostname = fallback   qualify_shortname = ""     default_realm = ONLING.COM     default_ccache_name = KEYRING:persistent:%{uid}     udp_preference_limit = 0   [realms]  ONLING.COM = {      kdc = domain_controller..onling.com      admin_server = domain_controller..onling.com  } [domain_realm]  .onling.com = ONLING.COM  onling.com = ONLING.COM

Join the server to the domain

Configurating the SSSD (in yellow update to site specific) (in green needs to be capitalized)

You must have the access to add to the AD server or the server has to be add first before you run the following command.

For the join you may need to specify the -OU's for the specific location

# realm discover onling.com # realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com # authselect select sssd with-mkhomedir  # authselect apply-changes

Copy from below and make changes

realm discover onling.com
realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com
authselect select sssd with-mkhomedir
authselect apply-changes

Update SSSD config

Once joined then you can change or add the following parameters

vi /etc/sssd/sssd.conf

[sssd] domains = onling.com config_file_version = 2 services = nss, pam   [domain/onling.com] default_shell = /bin/bash ad_server = sfl-dom-001.onling.com krb5_store_password_if_offline = True cache_credentials = True krb5_realm = ONLING.COM realmd_tags = manages-system joined-with-adcli id_provider = ad fallback_homedir = /home/%d/%u ad_domain = onling.com use_fully_qualified_names = false ldap_id_mapping = True access_provider = ad override_gid = 1000 ad_gpo_ignore_unreadable = true ad_gpo_access_control = disabled timeout = 300

Restarting SSSD

systemctl stop sssd; sss_cache -E; systemctl start sssd

Optional SSSD configurations

Defaults to no Shell Access, allows short usernames

Optional (Group allow) additional parameters and changes

simple_allow_groups = sssd-users OR simple_allow_groups =  gg-C3193???-KiwiTS_access

Add users to SSSD

Optional grant shell access and force primary group to kiwiplan, change john.doe to the username

sss_override user-add john.doe -g $(getent group kiwiplan | cut -d: -f3) -s /bin/bash -h /KIWI/home/john.doe

Linux Server - Install Rocky 9.4

Summary

Instructions on the install and configuration of a Linux server for Kiwiplan.

Details

Prerequisites

VMware, KVM, Hyper-V machine to host the installation

You will need to use the following command when you vi your files as you cut and paste:

Once in vi you have to hit the colon and then type “set paste”

:set paste

This will not mess with the paste you put in.

Instructions 

Preparing the OS

This is to install the OS on a VmWare

VMWare Setup using GUI

Boot ISO

Select “Install RedHat Linux”

Select Language

Then Select “Network & Hostname” from install menu.

Set hostname and hit apply

Click Configure and set the IP

Click on IPv6 Settings and disable it

Select “Installation Destination”

Make sure you select custom to configure the drives and click done

Click on the blue link to auto configure them

Make sure the /home drive is highlighted and click the “-“ minus sign to remove that drive

Add the pink value of the space available after the delete of the home folder.

Enter “350 GiB” (total of drive space you created for the VM), this will recalculate the size of the drive and then hit “Done”.

Hit “Accept Changes”

Click on the “Root Password” and click “Allow root SSH login with password” and then click “Done”

Click “Software Selection” and select “Minimal Install”

Click “Begin Installation”

When finished click on “Reboot System”

Network Text GUI

Login with root

NOTE: Run # nmtui Only if you did not do the GUI version

Select Edit connection

Select interface and hit enter

Use arrows to navigate

IPV4 change to manual and select show, fill in the information

IPV6 Change to disabled, press OK

Start setup Instructions for RedHat Linux

Connect to the Server

Use a SSH connection to the server the remaining of the commands in order to cut and pates them in instead of using the VM interface.

Commands to run

This will show all command that need to be ran to update within the OS to start the configuration.

All the command are within each box

Update the System

After install update system packages for the needed configuration.

dnf upgrade -y

Disable SeLinux

setenforce 0
sed -i 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

File is located here if you wish to edit manually

vi /etc/sysconfig/selinux

 Disable firewall

systemctl disable firewalld.service

Epel Release

dnf install epel-release -y

After Epel installation rerun the upgrade to update if any are needed

dnf upgrade -y

Install core software

Install Packages

Add the needed packages

Install required system packages

dnf install bind-utils bzip2 cups cifs-utils enscript ftp gdb ghostscript java-1.8.0-openjdk-headless java-11-openjdk-headless krb5-workstation ksh lftp lrzsz lsof libnsl lzop mariadb-server mlocate mutt ncompress net-tools net-snmp net-snmp-utils net-tools nfs-utils nmap nvme-cli openldap-clients openssh-clients psmisc realmd rsync samba-client strace sysstat tcpdump telnet telnet-server tmux unix2dos vim vim-enhanced vsftpd wget xfsdump vsftpd htop mc rsyslog rsyslog-doc postfix dbus-daemon s-nail dovecot cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain tree figlet toilet coreutils -y

If you are running on a virtual machine run the following

dnf install open-vm-tools -y
sysctl vm.swappiness=10

Install vim color for scripting

dnf install git -y
git clone https://github.com/flazz/vim-colorschemes ~/.vim/
cp ~/.vim/colors/desert.vim /etc/vimrc.local

Configure Installed Packages

Configure the packages

Enable Telnet run the following to enable and start the packages

systemctl enable restart telnet.socket
systemctl enable telnet.socket

Enable Time Synchronization run the the following and add your domain time server

vi /etc/chrony.conf

Add your server below the following and make sure you change the domain name from sflservicesllc.com

# These servers were defined in the installation:
#server _gateway iburst
server domain.sflserviesllc.com iburst

Enable Time Synchronization

systemctl enable --now chronyd

Note: Time-zone changes are made with the command

timedatectl

Enable FTP run the following 

vi /etc/vsftpd/vsftpd.conf

Make the following changes within the file

sudo sed -i 's/^anonymous_enable=.*/anonymous_enable=NO/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^local_umask=.*/local_umask=002/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^#ascii_upload_enable=.*/ascii_upload_enable=YES/' /etc/vsftpd/vsftpd.conf
sudo sed -i 's/^#ascii_download_enable=.*/ascii_download_enable=YES/' /etc/vsftpd/vsftpd.conf

Start and Enable FTP for transfer in and out for us or other vendors

systemctl enable --now vsftpd.service

If you have a remote logging server setup then edit the following command

vi /etc/rsyslog.conf

Then add the following at the end of the file but make sure you update the IP address and replace the XXX's

*.* @192.168.XXX.XXX:514   # use @ for UDP Protocal
*.* @@192.168.XXX.XXX:514   # use @@ for TCP Protocal

Enable Rsyslog to enable logging locally and remotely

systemctl enable --now rsyslog

Configure Postfix for Email run the following

vi /etc/postfix/main.cf

Update the following with your email server

relayhost = [ENTER IP or SMTP SERVER] or [sflservicesllc-com.mail.protection.outlook.com]:25

Optional if you want to hardcode a domain name run the following

vi /etc/postfix/main.cf

Update the following with a remote or local email server and make sure you change the domain name from sflservicesllc.com

myhostname = sflservicesllc.com

mydomain = sflservicesllc.com

mydestination =

relayhost = [ENTER IP or SMTP SERVER] or [sflservicesllc-com.mail.protection.outlook.com]:25

 Optional f you do not have a email remote/local server

vi /etc/postfix/main.cf

Update the following for no email server and make sure you change the domain name from sflservicesllc.com

myhostname = sflservicesllc.com

mydomain = sflservicesllc.com

mail_spool_directory = /var/mail

mynetworks = 127.0.0.0/8, 168.100.189.0/28

Enable Postfix

systemctl enable --now postfix

Test your config this way

echo "Install of Linux Rocks $HOSTNAME" | sendmail steve.ling@sflservicesllc.com

Locale add-ons to make sure that ANSI applications display correctly so edit the following and replace all of the values with the following

Edit the following:

vi /etc/locale.conf

Change the following:

LANG="en_US"
SUPPORTED="en_GB:en_GB-.UTF-8:en_US:en_US.UTF-8:de_DE:de_DE.UTF-8"
SYSFONT="latarcyrheb-sun16"

Once added the locals exit and telnet back into the server to take the update

Bash colors and Prompt

Bash for root for color edit the following

Dependent on

dnf install figlet toilet -y

Creating Aliases

sudo cat > /etc/profile.d/custom-aliases.sh << EOF
# Common interactive aliases for all users

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias vi='vim'
alias tailf='tail -f'
EOF

Server Warning

sudo tee /etc/profile.d/custom-prod-warning.sh > /dev/null << 'EOF'
# Colorful PRODUCTION warning banner for interactive logins

if [[ -n "$PS1" && -t 0 && -t 1 ]]; then  # Interactive terminal with stdin/stdout
    RED='\033[1;31m'   # Bold red
    YELLOW='\033[1;33m' # Bold yellow
    NC='\033[0m'       # No color

    echo
    if command -v figlet >/dev/null 2>&1; then
        figlet -f big "PRODUCTION" | while IFS= read -r line; do
            printf '%b%s%b\n' "$RED" "$line" "$NC"
        done
        figlet -f big "SERVER" | while IFS= read -r line; do
            printf '%b%s%b\n' "$RED" "$line" "$NC"
        done
    else
        echo -e "${RED}***** PRODUCTION SERVER!! *****${NC}"
    fi

    echo
    echo -e "${YELLOW} *** EXTREME CAUTION REQUIRED ***${NC}"
    echo -e "${RED}This is a LIVE PRODUCTION system.${NC}"
    echo -e "${RED}Unauthorized access is strictly prohibited.${NC}"
    echo -e "${RED}All activity is logged and monitored.${NC}"
    echo -e "${YELLOW}Think twice before running commands!${NC}"
    echo

    # Pause briefly to ensure user sees the warning
    read -t 5 -p "Press Enter to continue (auto-continue in 5 seconds)... " || true
    echo
fi
EOF

Creating Colorful Prompts

sudo tee /etc/profile.d/custom-prompt-and-colors.sh > /dev/null << 'EOF'
# Custom LS_COLORS and improved multi-line colored prompt

# Vibrant LS_COLORS (directories bold blue on gray, etc.)
LS_COLORS='rs=0:di=01;44:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:'
export LS_COLORS

# Color and style variables (cached tput calls for speed)
RESET=$(tput sgr0)
BOLD=$(tput bold)
BG_GRAY=$(tput setab 8)
FG_RED=$(tput setaf 1)
FG_MAGENTA=$(tput setaf 5)
FG_GREEN=$(tput setaf 2)
FG_CYAN=$(tput setaf 6)

# Multi-line prompt
PS1="\n${BOLD}${BG_GRAY}${FG_RED}\\u${FG_MAGENTA}@${FG_RED}\`hostname\`${FG_GREEN} : \`uname\` : ${FG_CYAN}\\d \\t : \${PLANTID:+\$PLANTID }\n${FG_CYAN}[\\w]${RESET}${BOLD} \\\$ ${RESET}"
export PS1
EOF

Database Install and Configuration

Configure MariaDB

Optional: To add the MariadB repo to get the latest revision other then the one that is included in the RedHat

Install the new Repo for MariaDB

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

This is the location of

vi /etc/yum.repos.d/MariaDB.repo

You have to change the yellow below to get the desired version

[mariadb] name = MariaDB baseurl = https://rpm.mariadb.org/10.6/rhel/$releasever/$basearch gpgkey= https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

Done with Optional

Run the following

systemctl edit mariadb.service

Then add the following to the file in between these lines

### Editing /etc/systemd/system/mariadb.service.d/override.conf
### Anything between here and the comment below will become the new contents of the file

[Service]
LimitNOFILE=32768

### Lines below this comment will be discarded

Run the following

vi /etc/my.cnf.d/client.cnf

Add the following below the [client] section

socket=/var/lib/mysql/mysql.sock

Run the following

vi /etc/my.cnf.d/mariadb-server.cnf

If you used the optional above use the following link

vi /etc/my.cnf.d/server.cnf

Done with Optional

Add the following below the [mysqld] section and you will have to use the :set paste command or it will not paste correctly

#Custom
performance_schema = ON
tmpdir = /run/mariadb
thread_cache_size = 4
table_open_cache = 16384
table_definition_cache = 8384
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

query_cache_type = 0
query_cache_size = 0
query_cache_limit = 128M
query_cache_strip_comments = 1

tmp_table_size = 512M
max_heap_table_size = 512M

max_connections = 512
max_allowed_packet = 24M
sort_buffer_size = 24M
join_buffer_size = 48M

innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table
innodb_log_file_size = 512M

#Optional configuration for transaction loggin
log_bin = /var/log/mariadb/mariadb.log
expire_logs_days = 2

Reload the changes

systemctl daemon-reload

Enable and start MySQL/MariaDB on boot

systemctl enable --now mariadb.service
echo > /etc/logrotate.d/mariadb

Run the following

mysql

Then execute the following statements also make sure to (change passwords as needed XXXXXXXXXX but NOT the users names)

CREATE USER 'kiwisql'@'%' IDENTIFIED BY '800486kiwi';
GRANT all ON *.* TO 'kiwisql'@'%' WITH GRANT OPTION;
CREATE USER 'kiwisql'@'localhost' IDENTIFIED BY '800486kiwi';
GRANT all ON *.* TO 'kiwisql'@'localhost' WITH GRANT OPTION;
CREATE USER 'kiwilive'@'localhost' IDENTIFIED BY '5541500kiwi';
GRANT all ON *.* TO 'kiwilive'@'localhost';
CREATE USER 'remuser'@'localhost' IDENTIFIED BY '5541500kiwi';
GRANT all ON *.* TO 'remuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'reports'@'%' IDENTIFIED BY 'readonly';
GRANT SHOW DATABASES, SELECT, EXECUTE, PROCESS, SHOW VIEW  ON *.* TO 'reports'@'%';
FLUSH PRIVILEGES;
Quit

Build skeleton for the Kiwiplan Environment

Command to Run

Setup the user environment

Remember to change the remuser & esp password

ESP username password is the global password store in ESP to let it connect and is unfortunately visible to an admin user but please make sure to (change passwords as needed XXXXXXXXXX but NOT the users names)

groupadd kiwiplan
mkdir /opt/kiwi
ln -s /opt/kiwi/ /KIWI
mkdir /opt/kiwi/{home,rev}
mkdir /opt/kiwi/rev/{map,mes}
useradd -g kiwiplan -G wheel -d /KIWI/home/remuser/ remuser
echo NewDssP@$$RemRhel193 | passwd --stdin remuser
useradd -g kiwiplan -d /KIWI/home/esp/ esp
echo esp2map | passwd --stdin esp
chown -R remuser:kiwiplan /opt/kiwi
mkdir /etc/kiwiplan
chown remuser:kiwiplan /etc/kiwiplan

Systemd Socket Implementation For KIDD

Configure kidd.stocket

Remember your :set paste command

Create a socket file and the following

vi /lib/systemd/system/kidd.socket

Then add the following

[Unit]
Description=KIDD
[Socket]
ListenStream=2326
Accept=yes

[Install]
WantedBy=sockets.target

Create a service file by editing the following

vi /lib/systemd/system/kidd@.service

Then add the following

[Unit]
Description=Kidd Server
[Service]
ExecStart=/KIWI/rev/current/progs/kidd -a -p -F 1
User=root
Group=root
StandardInput=socket
StandardOutput=socket

# Resource configuration
LimitNOFILE=8192
LimitNPROC=4096

# MaxConnections=nnn - The maximum number of connections to simultaneously run services instances for
#  when Accept=true is set. nnn should be sized accordingly for the implementation.
MaxConnections=200

# The maximum number of connections for a service per source IP address. 
# This is very similar to the MaxConnections= directive above. Disabled by default.
MaxConnectionsPerSource=80

Start the service and enable on reboot

systemctl start kidd.socket
systemctl enable kidd.socket

This will only work once you have the Kiwiplan environment configured/installed

VUE Installation Configurations

Adjust system limits for VUE products

Edit the following

vi /etc/security/limits.d/20-nproc.conf

Add the following 

@kiwiplan soft nproc 32768

Edit the following

vi /etc/security/limits.conf

Then add the following before the #End of file

@kiwiplan - nofile 32768

Installing MsSQL Tool

Configure MsSQL tools

Install the repo for the MsSQL service/connection

curl https://packages.microsoft.com/config/rhel/9/prod.repo > /etc/yum.repos.d/msprod.repo

This will only work once you have the Kiwiplan environment configured/installed

Install the packages make sure you answer/type YES to all

dnf remove mssql-tools unixODBC-utf16-devel
dnf install mssql-tools unixODBC-devel -y

Activate Directory Setup

Using SSSD

Install the following packages

yum install sssd sssd-tools realmd samba-common-tools oddjob oddjob-mkhomedir adcli samba -y

Run this just in-case your domain is still using RC4

update-crypto-policies --set DEFAULT:AD-SUPPORT

Configure KRB5

Edit the following file and make sure the following is added/changed

vi /etc/krb5.conf

Add or changed the following below

# To opt out of the system crypto-policies configuration of krb5, remove the # symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated. includedir /etc/krb5.conf.d/ [logging]     default = FILE:/var/log/krb5libs.log     kdc = FILE:/var/log/krb5kdc.log     admin_server = FILE:/var/log/kadmind.log [libdefaults]     dns_lookup_realm = true     ticket_lifetime = 24h     renew_lifetime = 7d     forwardable = true     rdns = true     pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt     spake_preauth_groups = edwards25519   dns_canonicalize_hostname = fallback   qualify_shortname = ""     default_realm = ONLING.COM     default_ccache_name = KEYRING:persistent:%{uid}     udp_preference_limit = 0   [realms]  ONLING.COM = {      kdc = domain_controller.onling.com      admin_server = domain_controller.onling.com  } [domain_realm]  .onling.com = ONLING.COM  onling.com = ONLING.COM

Join the server to the domain

Configurating the SSSD (in yellow update to site specific) (in green needs to be capitalized)

You must have the access to add to the AD server or the server has to be add first before you run the following command.

For the join you may need to specify the -OU's for the specific location

# realm discover onling.com # realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com # authselect select sssd with-mkhomedir  # authselect apply-changes

Copy from below and make changes

realm discover onling.com
realm join -U administrator@ONLING.COM --verbose domain_controller.onling.com
authselect select sssd with-mkhomedir
authselect apply-changes

Update SSSD config

Once joined then you can change or add the following parameters

vi /etc/sssd/sssd.conf

[sssd] domains = onling.com config_file_version = 2 services = nss, pam   [domain/onling.com] default_shell = /bin/bash ad_server = sfl-dom-001.onling.com krb5_store_password_if_offline = True cache_credentials = True krb5_realm = ONLING.COM realmd_tags = manages-system joined-with-adcli id_provider = ad fallback_homedir = /home/%d/%u ad_domain = onling.com use_fully_qualified_names = false ldap_id_mapping = True access_provider = ad override_gid = 1000 ad_gpo_ignore_unreadable = true ad_gpo_access_control = disabled timeout = 300

Restarting SSSD

systemctl stop sssd; sss_cache -E; systemctl start sssd

Optional SSSD configurations

Defaults to no Shell Access, allows short usernames

Optional (Group allow) additional parameters and changes

simple_allow_groups = sssd-users OR simple_allow_groups =  gg-C3193???-KiwiTS_access

Add users to SSSD

Optional grant shell access and force primary group to kiwiplan, change john.doe to the username

sss_override user-add john.doe -g $(getent group kiwiplan | cut -d: -f3) -s /bin/bash -h /KIWI/home/john.doe

Kiwiplan - Classic Purge Parameter Defaults

This is to give your plant(s) default set values for these parameters.

Legend:

• PARAMS - Default for all
• CSC - Corrugator Scheduling Center
• RSS - Roll Stock System
• ULT - Unit Load Tracking
• WIP - ULT Work In Progress

PARAMS is the default setting for all modals if it is the only one that exists

System  Description                   View_or_Maintain  GEN    General for many systems         Maintain Prefix  Description                   View_or_Maintain  Default_key  Many_records_allowed   PP    Purge Control Parameters         Maintain          PARAMS              Y         Description                      Contents         -----------                      --------         Key                              PARAMS         Produce filefull before purge    Y         Purge orders daily (Y/N)         Y         Purge orders weekly (0-7) 0=No   7         Number of days orders to keep    800         Repair file if errors found      Y         Purge details daily (Y/N)        Y         Purge details weekly (0-7)0=No   7         Number of days details to keep   800         Repair file if errors found      Y         Purge history daily (Y/N)        Y         Purge history weekly (0-7)0=No   7         Number of days history to keep   800         Repair file if errors found      Y         Purge specifications daily Y/N   Y         Purge specs weekly (0-7) 0=No    7         No.days specifications to keep   800         Repair file if errors found      Y

CSC Parameters

System  Description                   View_or_Maintain  GEN    General for many systems         Maintain Prefix  Description                   View_or_Maintain  Default_key  Many_records_allowed   PP    Purge Control Parameters         Maintain          PARAMS              Y         Description                      Contents         -----------                      --------         Key                              CSC         Produce filefull before purge    Y         Purge orders daily (Y/N)         Y         Purge orders weekly (0-7) 0=No   0         Number of days orders to keep    800         Repair file if errors found      Y         Purge details daily (Y/N)        Y         Purge details weekly (0-7)0=No   0         Number of days details to keep   800         Repair file if errors found      Y         Purge history daily (Y/N)        Y         Purge history weekly (0-7)0=No   0         Number of days history to keep   800         Repair file if errors found      Y         Purge specifications daily Y/N   Y         Purge specs weekly (0-7) 0=No    0         No.days specifications to keep   800         Repair file if errors found      Y

RSS Parameters

System  Description                   View_or_Maintain  GEN    General for many systems         Maintain Prefix  Description                   View_or_Maintain  Default_key  Many_records_allowed   PP    Purge Control Parameters         Maintain          PARAMS              Y         Description                      Contents         -----------                      --------         Key                              RSS         Produce filefull before purge    Y         Purge orders daily (Y/N)         Y         Purge orders weekly (0-7) 0=No   0         Number of days orders to keep    400         Repair file if errors found      Y         Purge details daily (Y/N)        Y         Purge details weekly (0-7)0=No   0         Number of days details to keep   400         Repair file if errors found      Y         Purge history daily (Y/N)        Y         Purge history weekly (0-7)0=No   0         Number of days history to keep   400         Repair file if errors found      Y         Purge specifications daily Y/N   Y         Purge specs weekly (0-7) 0=No    0         No.days specifications to keep   400         Repair file if errors found      Y

ULT Parameters

System  Description                   View_or_Maintain  GEN    General for many systems         Maintain Prefix  Description                   View_or_Maintain  Default_key  Many_records_allowed   PP    Purge Control Parameters         Maintain          PARAMS              Y         Description                      Contents         -----------                      --------         Key                              ULT         Produce filefull before purge    Y         Purge orders daily (Y/N)         Y         Purge orders weekly (0-7) 0=No   0         Number of days orders to keep    400         Repair file if errors found      Y         Purge details daily (Y/N)        Y         Purge details weekly (0-7)0=No   0         Number of days details to keep   200         Repair file if errors found      Y         Purge history daily (Y/N)        Y         Purge history weekly (0-7)0=No   0         Number of days history to keep   400         Repair file if errors found      Y         Purge specifications daily Y/N   Y         Purge specs weekly (0-7) 0=No    0         No.days specifications to keep   800         Repair file if errors found      Y

WIP ULT Parameters

System  Description                   View_or_Maintain  GEN    General for many systems         Maintain Prefix  Description                   View_or_Maintain  Default_key  Many_records_allowed   PP    Purge Control Parameters         Maintain          PARAMS              Y         Description                      Contents         -----------                      --------         Key                              WIP         Produce filefull before purge    N         Purge orders daily (Y/N)         N         Purge orders weekly (0-7) 0=No   0         Number of days orders to keep    60         Repair file if errors found      Y         Purge details daily (Y/N)        N         Purge details weekly (0-7)0=No   0         Number of days details to keep   60         Repair file if errors found      Y         Purge history daily (Y/N)        Y         Purge history weekly (0-7)0=No   0         Number of days history to keep   120         Repair file if errors found      Y         Purge specifications daily Y/N         Purge specs weekly (0-7) 0=No    0         No.days specifications to keep   100         Repair file if errors found      Y

Kiwiplan - Costing

SFL Services LLC can help you revamp or configure your cot model

Contact us to get timing and cost

Contents

Enterprise Cost Model 4

0010 Paper/Board. 4

SFLS-Board. 4

0010 Paper/Board. 6

SFLS-Board SP. 6

0010 Paper/Board. 7

SFLS-Litho/Top Sheet 7

0020 Board Waste on the Corrugator 7

SFLS-Paper Waste Corr 7

0021 Trim on the Corrugator 8

SFLS-Trim Waste Corr 8

0022 Board Waste on Converting. 9

SFLS-Paper Waste Conv. 9

0023 Trim Waste on Converting. 9

SFLS-Trim Waste Conv. 9

0024 Board Setup Waste. 9

SFLS-Setup Waste Conv. 9

0050 Starch Costs on the Corrugator 9

SFLS-Starch. 9

0055 Ink Costs. 10

SFLS-Standard Ink. 10

SFLS-Custom Ink. 11

0059 Pallet Costs. 12

SFLS-Pallets. 12

0060 Transport Boxes. 13

SFLS-Shippers. 13

0063 Coating. 14

SFLS-Coating. 14

0080 Purchased Finished Goods. 14

SFLS-Purchased Finished Goods. 14

0081 Purchased Finished Goods. 15

SLFS-3^rd Pary Costs PPC. 15

0081 Purchased Goods for Converting. 15

SFLS-Purch Gds for Converting. 15

0111 Set up Costs on Converting - (Fixed Cost) 15

SFLS-Fix Set up Costs Conv. 15

0111 Set up Costs on Converting - (Variable Cost) 15

SFLS-Var Set up Costs Conv. 15

0140 Running Costs on Corrugator - (Fixed Cost) 16

SFLS-Fix Running Costs Corr 16

0140 Running Costs on Corrugator – (Variable Cost) 16

SFLS-Var Run Costs on Corr 16

0160 Running Costs on Converting - (Fixed Cost) 16

SFLS-Fix Run up Costs Conv. 16

0160 Running Costs on Converting – (Variable Cost) 17

SFLS-Var Run Costs on Conv. 17

0161 Hand Labor 17

SFLS-Assembly Costs. 17

0164 Printing Tool Cost 17

SFLS-Printing Tool Cost 17

0165 Cutting Tool Cost 17

SFLS-Cutting Tool Cost 17

0167 Internal WIP Transport – (Fixed Cost) 17

SFLS-Fix Int WIP Trans Costs. 17

0167 Internal WIP Transport – (Variable Cost) 18

SFLS-Var Int WIP Trans Costs. 18

0185 Outside Service. 18

SFLS-Outside Service. 18

0201 Palletizer – (Fixed Cost) 18

SFLS-Fix Palletizer 18

0201 Palletizer – (Variable Cost) 18

SFLS-Var Palletizer 18

0202 Strapping Costs. 19

SFLS-Strapping. 19

0202 Stretch Wrap Costs. 19

SFLS-Stretch wrap. 19

0250 Financial Cost of Debtors. 19

SFLS-Fin Wrk Cap Recovery. 19

0230 Transport Costs. 20

SFLS-Ded Transport Costs. 20

0230 Costs of Storage. 21

SFLS-Storage Costs (FG) 21

0231 Dedicated Transport Costs. 22

0232 – Over The Road Transport Quoted Cost 25

SFLS-OTR Transport (Quoted) 25

0233 – Over The Road Transport Estimated Cost 27

SFLS-OTR Transport (Estimated) 27

0234 Additional Transport Costs (need to chage to new ESP functionality) 29

SFLS-Drop Trailer Costs. 29

0235 Post Cost Freight 29

SFLS-MBOL. 29

0237 Freight Override Cost 29

SFLS-Freight Override. 29

0301 Order Costs. 29

SFLS-Order Cost Recovery. 29

0302 Sales Costs. 29

SFLS-Sales Cost Recovery. 29

0303-5 General & Administration Costs SFLS-Gen Admin Conv Set Up SFLS-Gen Admin Conv Costs SFLS-Gen Admin Corr Costs. 30

0820 Payment Terms Recovery. 30

SFLS-Payment Terms Recovery. 30

0825 Financial Cost of Inventory. 31

SFLS- Fin Cost of Inventory. 31

0850 Rebate Recovery. 32

SFLS-Rebate Recov - Company. 32

0851 Rebate Recovery. 32

SFLS-Rebate Recovery - PD. 32

0855 Board. 32

SFLS-Packaging Delta. 32

Degrade Rules. 33

Lookup Table. 35

Kiwiplan - Farmout Routes in ESP

Farm-Out Routes in ESP

Warehoused Product Designs

These 5 Farm Out route scenarios have at least these operations

Below - PCS Classic

Below - PCS VUE

Above - PCS Classic

Above - PCS VUE

How To and Explanation:

If no route appears after saving PD

1. Drag & drop machine into route.
   • Drop once on Green
2. Right click in Blue, Assign Operations
3. In Misc Details (2) Tab, ensure all operations correlate with their respective bubble.

If a route appears after saving PD

1. Make appropriate changes to machines to how your item is being produced.
   • Delete & Add Machines
   • Choose Machines from Machine list
2. Right click in Blue, Assign Operations
3. In Misc Details (2) Tab, ensure all operations correlate with their respective bubble.

Script - Import Data Script for Internal Customer Servers

This is a script to import databases, logs and preference bundles from a backup file generated from a datadump script.

Name:

importdata

Script Located here:

https://github.com/SFLServicesLLC/Scripts/blob/main/Linux/importdata

Kiwiplan - Box Plant Classic/Refresh To VUE Master Parameters

Master Data Parameters for a Classic Refresh Plant

This is used to know where the parameter take affect in either VUE or Classic

ReFresh
Setup In
Classic	VUE	Option	Vue Script	Comments
	Yes	Material > Material Group > Board Group
	Yes	Configuration > Organization > Score Types		Make sure default in VUE is set but also set CORR-CP & PCS-QB
Yes	Yes	Configuration > Organization > Score Types & PCS-QB & CORR-CP		Make sure that classic has the default converting score set
	Yes	Materials > Flute Types
	Yes	Materials > Material Group > Board Group
	Yes	Materials > Material Groups > Paper Classes
Yes	Yes	CSC00 > P > CSC > UD
	Yes	Configuration > Downtime > Downtime Classes
Yes	Yes	Materials > Materials > Shippers & GEN-SN		VUE only needed for COMMS. Classic for everything else
Yes		PCS00 > P > GEN > TB
Yes		PCS00 > P > GEN > TU
Yes	Yes	Configuration > Parameters > Manufacturing > COMMS & GEN-UM
Yes		GEN-UP		Not used in VUE but stored on the unitising table
Yes		PCS00 > P > GEN > WC
Yes		INV-SM		Not used in VUE but stored on the shipmethod and job table
Yes		PCS00 > P > PCS > CC	Yes
Yes		PCS00 > P > PCS > CE	Yes
Yes		PCS-CN, GEN-CM, GEN-CN
	Yes	Configuration > Parameters > PCS > Feedback
Yes		PCS00 > P > PCS > IC	Yes
Yes		Configuration > Tool > Tool Types & PCS-PD		VUE needs these for tooling but ESP reads classic for type
Yes		PCS00 > P > PCS > QD
Yes		PCS00 > P > PCS > QS
Yes		Configuration > Organization > Machine Groups & PCS-QE		Classic & ESP reads the group description from classic
Yes		Configuration > Machine > Machine Operations & PCS-QK
Yes		Configuration > Order > Hold Statuses
Yes		Configuration > Order > Hold Statuses
Yes		PCS00 > P > PCS > QP
Yes		Configuration > Machine > Slot WidthCodes
Data Fields
	Yes	Materials > Materials > Papers
	Yes	Materials > Materials > Starch
	Yes	Materials > Materials > Corrugated Boards
	Yes	Materials > Materials > Corrugator Coatings
Yes		PCS00 > L > D
		Materials > Material Group > Ink Groups
Yes		Configuration > Organization > Plant > Stores and invmenu		Only machine in and out feed locations need to be in VUE
Yes		Configuration > Organization > Plant > Stores and invmenu		Only machine in and out feed locations need to be in VUE
Yes	Yes	Configuration > KDG Templates & lbs00:A		Just code needs to exist in classic, label defined in VUE
	Yes	Configuration > Organization > Plant > Machines
		Calendars > Shift Names
		Calendars > Calendars
		PICS and ESP		Must enter in ESP and PICS and must match

Kiwiplan - Sheet Plant VUE Master Parameters

Master Data Parameters for a VUE Sheet Plant

This is used to know where the parameter take affect in either VUE or Classic

Sheet Plants / Box Plant Full VUE
		Setup In
Parameter	Description	Classic	VUE	Option	Comments
CORR-BB	Board Codes		Yes	Material > Material Group > Board Group
CORR-CT	Score Codes		Yes	Configuration > Organization > Score Types	Make sure default in VUE is set but also set CORR-CP & PCS-QB
CORR-CP & PCS-QB	Default Score Code	Yes	Yes	Configuration > Organization > Score Types & PCS-QB & CORR-CP	Make sure that classic has the default converting score set
CORR-FL	Flutes		Yes	Materials > Flute Types
CSC-BS	Board Structure		Yes	Materials > Material Group > Board Group
CSC-PC	Paper Classes			Not required for sheetplants
CSC-UD	Unit Descriptions		Yes	Configuration > Order > Groups
GEN-QC	Downtime Classes		Yes	Configuration > Downtime > Downtime Classes
GEN-SN	Shipper types	Yes	Yes	Materials > Materials > Shippers & GEN-SN	VUE only needed for COMMS. Classic for everything else
GEN-TB	Top Boards		Yes	Materials > Materials > Top Board
GEN-TU	Pallets		Yes	Materials > Materials > Pallets Materials > Unit Load Types < -- After version 10 Box Plant
GEN-UM	Units of Measure	Yes	Yes	Configuration > Parameters > Manufacturing > COMMS & GEN-UM
GEN-UP	Label/Sample Placement	Yes		GEN-UP	Not used in VUE but stored on the unitising table
GEN-WC	Waste Classes		Yes	Configuration > Waste - Categories
INV-SM	Shipment Method	Yes		INV-SM	Not used in VUE but stored on the shipmethod and job table
PCS-CC	Closure Codes		Yes	Configuration > Machine > Closure Codes
PCS-CE	Flap Types		Yes	Configuration > Machine > Closure Type
PCS/GEN-CM/CN	Communications	Yes		PCS-CN, GEN-CM, GEN-CN
PCS-FB	Feedback		Yes	Configuration > Parameters > PCS > Feedback
PCS-IC	Inks & Coatings		Yes	Configuration > Material Group > Ink types
PCS-PD	Tooling Definitions	Yes	Yes	Configuration > Tool > Tool Types & PCS-PD	VUE needs these for tooling but ESP reads classic for type
PCS-QD	Tooling Die Status		Yes	Configuration > Tool > Tool Statuses
PCS-QS	Tooling Print Status		Yes	Configuration > Tool > Tool Statuses
PCS-QE	Machine Groups	Yes	Yes	Configuration > Organization > Machine Groups & PCS-QE	Classic & ESP reads the group description from classic
PCS-QK	Machine Operations	Yes	Yes	Configuration > Machine > Machine Operations & PCS-QK
PCS-QO	Order Status		Yes	Configuration > Order > Hold Statuses
CSC-OS	Order Status		Yes	Configuration > Order > Hold Statuses
PCS-QP	PCS-QP			Not required for sheetplants
PCS-SW	Slot Widths		Yes	Configuration > Machine > Slot WidthCodes
Data Fields
Papers				Not required for sheetplants
Starch				Not required for sheetplants
Board Grades			Yes	Materials > Materials > Corrugated Boards
Coatings			Yes	Materials > Materials > Coatings
Inks			Yes	Materials > Materials > Inks
Ink Groups			Yes	Materials > Material Group > Ink Groups
Locations		Yes	Yes	Configuration > Organization > Plant > Stores and invmenu	Only machine in and out feed locations need to be in VUE
Stores		Yes	Yes	Configuration > Organization > Plant > Stores and invmenu	Only machine in and out feed locations need to be in VUE
Label Formats		Yes	Yes	Configuration > KDG Templates & lbs00:A	Just code needs to exist in classic, label defined in VUE
Machines			Yes	Configuration > Organization > Plant > Machines
Shift Names			Yes	Calendars > Shift Names
Calendars			Yes	Calendars > Calendars
Purchase Category			Yes	PICS and ESP	Must enter in ESP and PICS and must match

SFL - Post-Site Audit Installation

Post-Site Audit Installation

This document is used to go through each department on a plant visit or for the plant to fill conduct themselves prior a visit.

Please take time to talk to each team and document their concerns as most of the time their concerns will have or are part of cascading effects from or to other departments. 

Document Instruction

You will have to download this document and fill out ALL sections and we will re-upload it if we are taking on your project which we will add to our project planning area HERE.

Current SOPs (Standard Operating Procedures)

The big question in most cases, does the plant have either past system SOP or any current SOP's?

Depending on you answer are you look for us to help you create them for the newly installed system?

Introduction

This section is to learn more about what was installed at the plant when the systems were introduced:

System aka's	Name	Yes/No
CSC	Corrugator Control Scheduling
SCS	Supply Chain Simulator
PCS	Production Control System
ESP	Enterprise Sales Processing
ULT	Unit Load Tracking
VUE PCS	Visual GUI version of Production Control System
VUE CSC	Visual GUI version of Corrugator Control Scheduling
VUE ULT	Visual GUI version of Unit Load Tracking
KDW	Kiwiplan Data Warehouse
TSS	Truck Scheduling System
QMS	Quality Management System
MMS	Material Management System

Fact Finding Mission

Overview

The mission of the document is to make sure that the processes that are used at the plant are adaptable to the Kiwiplan system suit of the MAP/MES and ESP systems. 

Overview Questions:

What system did the plant use before the installation
Was there use of spreadsheet before the installation
What was the driver to change the system for the plant

Business Flow Charts

Do you have any workflow to provide us that are like this from your old or new processes:

Estimating

Ordering

Other Core Systems

This section is for any other systems that complement the Kiwiplan systems.

Do you have a unitizing system for optimizing stacking
Do you have a CAD system for creating products
What is your accounting software

Specific Workflows

The section is if you have any workflows that complement the corrugated processes within your plan.

Product Design / Estimating / Costing Processes

This section is to explain the process from the point of creating a product specification, its routing, its costing and finally its pricing.

We need a detailed process explanation or flow chart to tell us how this works at the plant. 

You can have a look at our costing model that we usually configure at our customer plants/corporation:

Click HERE

Creating Product

What is the process used to create your product and which department is creating.

Setting Degrade Rules

This is crucial to have implemented degrade rules from a costing leveling perspective to the routing perspective. This allows you to consistently cost at the same level but be able to send more accurate run speeds to production to keep a good production level. 

Machine#	Detailed Degrade Rules Set Y/N (Y for detailed N for Standards Only)

Creating Routing

What is the process used to create your routing for the product and which department is creating.

Creating Estimating

What is the process used to create your cost estimate based on the routing and the product and which department is creating.

Creating Pricing

What is the process used to create your pricing based on the costing and product information and which department is creating.

Roll Stock

This section is to explain the process from the ordering of the rolls to receiving them into your own warehouse. 

Please fill out the table below to know how your plant currently functions with your warehouses.

Owned/Leased	Name	Consignment Y/N	EDI Y/N

Production

This section is to explain the process of placing the order in the ESP system and getting the BOB (Book on Blue) date with the use of loadings and degrade rules if any are set.

Please specify if you send some work out to get farm out and what that process looks like.

Please specify if you are in the horticulture segment of the industry and what that process looks like. 

Printing Plates

This section is to explain if you create, purchase or destroy your tooling and what is its process on a daily bases.

How do you manage your Printing Plates:

Purchasing
Rotations
Discarding

Die Cut Dies

This section is to explain if you create, purchase or destroy your tooling and what is its process on a daily bases.

How do you manage your Cutting Dies:

Purchasing
Rotations
Discarding

Purchasing Materials

What are your current process on purchasing finished products or sheets from a external source?

 

Customer Service Department

This section is to explain the processes withing the customer service area of the plant. Please elaborate on the departments process on booking orders to delivery. These processes should also include what and how the team handles order requests or inquiries during the day. Please include all interactions that the team can think of between them and the plant depatments.

Finance Department

This section is to explain the processes within the finance area of the plant.

Does the invoicing happen the day of or the next day of the delivery?

Do you ship across boarders?

Do you have an interface build to send the transactional data to from ESP to the accounting software?

 

Logistic Department

This section is to explain the processes used in the shipping departments at the plant. Does the plant use the production schedule to plant for trucks and where they are going by a certain criteria? For example regions, zones or etc.

If you purchased the TSS system how are you using it to determine when to deliver the goods and when?

 

Quality Department

This section is to explain the process within the quality department. Please explain you past or current process on how your plant processes are dealing with current runs or past runs of products.

Did you purchase the QMS?

 

Integrations (Interfaces)

This section is to explain if you have any integrations with any core systems used at your location or locations to feed data to other host systems?

System	Integration

 

Custom Scripts or Applications

This section is to explain if you have any kind of special system scripts or application in between the systems to the Kiwiplan system and that includes any SQL agent scripts or SSIS processes?

System	Integration

Summary Notes

This section is to explain if you can think of any other system touch points that were not covered above?

 

Document Revision History

Date	Version	Document	Created / Modified	Comments
08.11.2018	1.0	Kiwiplan Site Audit	Steve Ling	Creation
02.07.2025	1.1	Added to the docs site for ease of sharing	Steve Ling

Kiwiplan - Conversion Tables for VUE (Microns)

Conversion Table

This is a table to help you if building reports from the Kiwiplan VUE systems

Value	Into	Factor	Feet	Inches	16ths	Formula
17729644500	Lineal Feet	3048000	5817			ROUND([value]/3048000)
27178000	Roll Width	25400		66.8750	1070	ROUND([value]/25400)/16

Kiwiplan - JAVA openJDK Install for Oracle Licensing Issues

Microsoft OpenJDK

These two version are required for some of the Kiwiplan suit installations

Java Main

Here is the link to the main page

https://learn.microsoft.com/en-us/java/openjdk/download

Java 8

Here is the link for the 8 version 

https://learn.microsoft.com/en-us/java/openjdk/download#openjdk-8

Java 11

Here is the link for the 11 version

https://learn.microsoft.com/en-us/java/openjdk/download#openjdk-11

 

Script - Data Dump Script for Internal Customer Servers

This script is to get a backup of all databases to either send them to a test server, just a backup or send them for support.

Name:

datadump

Script Located here:

https://github.com/SFLServicesLLC/Scripts/blob/main/Linux/datadump

Script - Test Server .bash_profile

This script needs to be modified for the test server login

vi /etc/skel/.bash_profile

Add or change the following to this

PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
/KIWI/corp/bin/bash_profile
#Uncomment if you want the shell to exit when user exists
exit

Script to be added/created to the $CORPBIN of the server

Edit the following script

vi /KIWI/corp/bin/bash_profile

Add the following

#!/bin/bash
#Type:Utility
#########################################################
# Script:
#
# This is used for logging into a test server
# 
# Created
# Steve Ling
#########################################################
# Revision
#
# 2025-01-26 SFL Initial revision
#
#########################################################
#
# Environment:
#
umask 002
#trap 'echo "Caught Ctrl+C"; exit 0' SIGINT
export KIWIBASE=/KIWI
DBHOST=localhost
MYSQLUSER=kiwisql
MYSQLPASS=800486kiwi
#
# Variables
REVS=/KIWI/rev/map
MREVS=/KIWI/rev/mes
DATASETS=/KIWI/backups
#
# Define colors
RED='\033[1;31m'
GREEN='\033[1;32m'
YELLOW='\033[1;33m'
BLUE='\033[1;36m'
NC='\033[0m' # No Color
BOLD='\033[1m'
FLASH='\033[5m'
#
# Define log file
LOG_FILE="Log$(date +"%Y%m%d_%H%M%S").log"
#
# Logging function
log() {
  local message="$1"
  local level="$2" # Optional: INFO, WARNING, ERROR
  local color="$3"

  # Timestamp
  timestamp=$(date +"%Y-%m-%d %H:%M:%S")

  # Log message with level and timestamp
  echo -e "${color}${message}${NC}" | tee -a "$LOG_FILE"
  #echo -e "${color}${message}${NC}" >> "$LOG_FILE"
  echo "[${timestamp}] [${level}] ${message}" | logger
}
#
# Display banner
banner(){
clear
log "=========================================" "INFO" "${GREEN}${BOLD}"
log "|  Welcome to the Test/Dev Environment  |" "INFO" "${GREEN}${BOLD}"
log "|                                       |" "INFO" "${GREEN}${BOLD}"
log "| You are in the server:                |" "INFO" "${GREEN}${BOLD}"
log "  `hostname` " "INFO" "${YELLOW}${BOLD}"
log "=========================================" "INFO" "${GREEN}${BOLD}"
echo ""
}
#
checkVariables() {
if [ ! -d $REVS ];then
        log "$REVS does not exist" "ERROR" "${RED}${BOLD}${FLASH}"
        return 1
fi
#
if [ ! -d $MREVS ];then
        log "$MREVS does not exist" "ERROR" "${RED}${BOLD}${FLASH}"
        return 1
fi
#
if [ ! -d $DATASETS ];then
        log "$DATASETS does not exist" "ERROR" "${RED}${BOLD}${FLASH}"
        return 1
fi
}
#
main_menu(){
# Main Menu
  banner
  echo ""
  log "A) Add Site" "INFO" ${GREEN}${BOLD}
  log "D) Delete Site." "INFO" ${GREEN}${BOLD}
  log "R) Restore Data" "INFO" ${GREEN}${BOLD}
  log "S) Select Site" "INFO" ${GREEN}${BOLD}
  echo ""
  log "X) Exit" "INFO" ${BLUE}${BOLD}
  echo ""
  echo -en ${YELLOW}${BOLD} "Default [S] : "
  read -t 10 choice
  log choice
  case "$choice" in
    "A"|"a")
            log "Chose option A" "INFO" ${YELLOW}${BOLD}
            site_add
    ;;
    "D"|"d")
            log "Chose option D" "INFO" ${YELLOW}${BOLD}
            site_delete
    ;;
    "R"|"r")
            log "Chose option R" "INFO" ${YELLOW}${BOLD}
            data_restore
    ;;
    "S"|"s")
            log "Chose option S" "INFO" ${YELLOW}${BOLD}
            site_select
    ;;
    "X"|"x")
            log "Exiting..." "INFO" ${YELLOW}${BOLD}
            return 0
    ;;
    *)
      site_select
      return 0
    ;;
  esac

}

site_add(){
  echo -en ${GREEN}${BOLD} "Enter 4 character site name : "
  read NEW_SITE
  if [ -z "$NEW_SITE" ]
  then
    exit 1
    main_menu
  else
    REVS=$(find /KIWI/rev/map/* -maxdepth 0 -type d| sort)
    PS3="Select MAP revision for $NEW_SITE : "
    select REV in $REVS
    do
      if [ ! -d "/KIWI/rev/site_$NEW_SITE" ]; then
        ln -s $REV $KIWIBASE/rev/site_$NEW_SITE
      fi
      mkdir -p $KIWIBASE/site_$NEW_SITE
      ln -s $KIWIBASE/rev/site_$NEW_SITE/bin $KIWIBASE/site_$NEW_SITE/bin
      ln -s $KIWIBASE/rev/site_$NEW_SITE/progs $KIWIBASE/site_$NEW_SITE/progs
      ln -s $KIWIBASE/rev/site_$NEW_SITE/scp $KIWIBASE/site_$NEW_SITE/scp
      ln -s $KIWIBASE/rev/site_$NEW_SITE/sql $KIWIBASE/site_$NEW_SITE/sql
      break
    done
  install_mysql new
  gen_kidds
  mkdir -p $KIWIBASE/site_$NEW_SITE/site/bin
  mkdir -p $KIWIBASE/site_$NEW_SITE/site/dat
  if [ ! -d $KIWIBASE/services  ];then
     mkdir -p $KIWIBASE/services/
  fi
  cd $KIWIBASE
  if [ ! -d $KIWIBASE/java  ];then
     ln -s $KIWIBASE/services java
  fi
  mkdir -p $KIWIBASE/services/sites/$NEW_SITE/
  MREVS=$(find $KIWIBASE/rev/mes/* -maxdepth 0 -type d| sort)
  PS3="Select MES revision for $NEW_SITE : "
  select MREV in $MREVS
  do
    cd /$KIWIBASE/services/sites/$NEW_SITE/
    mkdir -p `basename $MREV`
    ln -s `pwd`/`basename $MREV` current 
    mkdir -p $KIWIBASE/services/sites/$NEW_SITE/current/conf
    mkdir -p $KIWIBASE/services/sites/$NEW_SITE/current/conf/roadgrids
    mkdir -p $KIWIBASE/services/sites/$NEW_SITE/current/conf/kiwiplan/roadgrids
    ln -s $KIWIBASE/services/maps/roadgrids/osm-gh $KIWIBASE/services/sites/$NEW_SITE/current/conf/roadgrids/osm-gh
    ln -s $KIWIBASE/services/maps/roadgrids/osm-gh $KIWIBASE/services/sites/$NEW_SITE/current/conf/kiwiplan/roadgrids/osm-gh
    mkdir -p $KIWIBASE/services/sites/$NEW_SITE/current/logs
    break
  done
  fi
  log "Added $NEW_SITE to the environment" "INFO" ${BLUE}${BOLD}
  log "Setting the $NEW_SITE environment" "INFO" ${YELLOW}${BOLD}
  export KIWI=${KIWI:-$KIWIBASE/site_$NEW_SITE}
  export CORPBIN=${CORPBIN:-${KIWIBASE}/corp/bin}
  export CORPDAT=${CORPDAT:-${KIWIBASE}/corp/dat}
  export SITEBIN=${SITEBIN:-${KIWI}/site/bin}
  export SITEDAT=${SITEDAT:-${KIWI}/site/dat}
  export BIN=${BIN:-${KIWI}/bin}
  export WORK=${WORK:-${KIWI}/work/${LOGNAME}}
  export DATA=${DATA:-${KIWI}/data_$NEW_SITE}
  export PROGS=${PROGS:-${KIWI}/progs}
  export SCP=${SCP:-${KIWI}/scp}
  export SQL=${SQL:-${KIWI}/sql}
  export UFD=${UFD:-${KIWI}/ufd}
  export KIWISEA=".:$WORK:$HOME:$DATA:$SITEDAT:$SITEBIN:$CORPDAT:$CORPBIN:$BIN:$PROGS:$SCP:$SQL"
  export PATH=".:$WORK:$HOME:$SITEBIN:$CORPBIN:$BIN:$PROGS:/bin:/usr/bin:/usr/sbin"
  export JAVA_HOME=/usr/lib/jvm/jre-11
  log "Now will add the MES environment for $NEW_SITE" "INFO" ${BLUE}${BOLD}
  cd $MREV
  REVBASE=`basename $MREV`
  log "This will take some time to decompress and will start the install" "INFO" ${BLUE}${BOLD}${FLASH}
  tar zxvf mes_$REVBASE.tar.gz
  ./mes-$REVBASE.sh
  log "Running the importdata script for the $NEW_SITE environment" "INFO" ${GREEN}${BOLD}
  $KIWIBASE/corp/bin/importdata
  main_menu
}

site_delete(){
  if [ ! -d $KIWIBASE/site_*  ];then
     log "Sites do not exists"
     sleep 2
     main_menu
  fi
  PS3="Choose site : "
  SITES=`ls -d $KIWIBASE/site_* | cut -b 12-`
  select SITE in $SITES
  do
    echo -n "Are you sure you want to delete /KIWI/site_$SITE (Y/[N])?"
    read choice
      case "$choice" in
      "Y"|"y")
        echo "Deleting the Classic Site Folder"
        find $KIWIBASE/site_$SITE/ -name kwsql -exec mysql -e "drop database $SITE_classic" \;;
        echo "Deleting the Kiwi Site Folder"
        rm -rf $KIWIBASE/site_$SITE
        echo "Deleting the Rev Site Folder"
        rm -f $KIWIBASE/rev/site_$SITE
        echo "Deleting the Jave Site Folder"
        rm -rf $KIWIBASE/services/sites/$SITE
        echo "Deleting the Web Folder"
        rm -rf $KIWIBASE/services/web/$SITE
      ;;
      *)
        main_menu
      ;;
      esac
    break
  done
  main_menu
}

data_restore() {
  if [ ! -d $KIWIBASE/site_*  ];then
     log "Sites do not exists"
     sleep 2
     main_menu
  fi 
  PS3="Choose site : "
  SITES=`ls -d $KIWIBASE/site_* | cut -b 12-`
  select NEW_SITE in $SITES
  do
    echo -n "Are you sure you want to overwrite data for /KIWI/site_$NEW_SITE (Y/[N])?"
    read choice
      case "$choice" in
      "Y"|"y")
        rm -rf $KIWIBASE/site_$SITE/data_$NEW_SITE/*
      
        echo "Select data source : "
        echo ""
        echo "I) ISAM"
        echo "M) MySQL"
        echo ""
        echo -n "Default [I] : "
        read choice
        case "$choice" in
        "M"|"m")
          install_mysql restore
        ;;
        *)
          install_isam restore
        ;;
       esac
       ;;
       *)
         main_menu
       ;;
      esac
    break
  done
  main_menu
}

install_isam() {
  if [ ! -f $KIWIBASE/backups/*.tar.gz ];then
     log "ISAM Backup files do not exist"
     sleep 2
     return
  fi
  DATASETS=`find $KIWIBASE/backups/*.tar.gz`
  PS3="Select data for $NEW_SITE : "
  select DATASET in $DATASETS
    do
      if [ $1 == "new" ]      
        then
        tar -zxvf $DATASET -C $KIWIBASE/site_$NEW_SITE/
        mv -vf `find $KIWIBASE/site_$NEW_SITE/* -type d -maxdepth 0` $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE
      fi
      if [ $1 == "restore" ]
        then
        find $KIWIBASE/site_$NEW_SITE/ -name kwsql -exec rm {} \;;
        tar -zxvf $DATASET -C $KIWIBASE/site_$NEW_SITE/
        SCR_DIR=`basename \`tar -ztf $DATASET | head -n 1\``
        mv -vf $KIWIBASE/site_$NEW_SITE/$SCR_DIR $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE
      fi
      break
    done  
}

install_mysql() {
if [ -f $KIWIBASE/backups/*.sql.gz ];then
   DATASETS=`find $KIWIBASE/backups/*.sql.gz`
   PS3="Select data for $NEW_SITE : "
   select DATASET in $DATASETS
   do 
      if [ $1 == "restore" ];then
          log "Checking to ensure the database is not in use" "INFO" ${YELLOW}${BOLD}
          sleep 2
          mysqladmin processlist | grep -v _master | egrep -q $DATASET
          if [ $? -eq 1 ] ; then
            mysql -e "drop database `$NEW_SITE_classic`"
          logo "Dropping old databases""INFO" ${YELLOW}${BOLD}
          sleep 2
          else
            echo "Destination database is in use. Please try again later."
            read error
          fi
      else
        mkdir -p $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE
      fi  
      logo "Creating new database." "INFO" ${YELLOW}${BOLD}
      sleep 2
      mysql -e "create database $NEW_SITE_classic"
      log "Restoring database now." "INFO" ${YELLOW}${BOLD}
      sleep 2
      zcat $DATASET | grep -v "CREATE DATABASE" | mysql $NEW_SITE_classic
      mysql -e "grant all on $NEW_SITE.* to 'kiwisql'@'localhost' identified by '800486kiwi'"
     break
   done
else
   log "SQL Backup files do not exist" "INFO" ${YELLOW}${BOLD}
fi
   if [ ! -d $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE ]; then
        echo "Creating the site Data Folder"
        mkdir -p $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE
   fi
   touch $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "DATA="${NEW_SITE}"_classic" > $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "HOST=localhost" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "INTERFACE=sql" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "USER=kiwisql" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "PASSWORD=800486kiwi" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql 
#   echo "PASSWORDX=2o5tP2P8uX4WMp4xsjVnK8DGX5uYrwBz" >> /KIWI/site_$NEW_SITE/data_$NEW_SITE/kwsql   
   echo "LOG=error" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
   echo "LOGPID=1" >> $KIWIBASE/site_$NEW_SITE/data_$NEW_SITE/kwsql
}

gen_kidds() {
  rm -f $KIWIBASE/site_$NEW_SITE/KIDSENV
  touch $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "SHELL=/bin/sh" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "TERM=vt100" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIDSDEBUG=4" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIDSLOGLEVEL=4" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIWISITE=$NEW_SITE" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIWI=/KIWI/site_$NEW_SITE" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "DATA=\$KIWI/data_$NEW_SITE" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "WORK=\$KIWI/work/$LOGNAME" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "BIN=\$KIWI/bin/" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "PROGS=\$KIWI/progs/" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "SCP=\$KIWI/scp/" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "SITEBIN=\$KIWI/site/bin" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "SITEDAT=\$KIWI/site/dat" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "SQL=\$KIWI/sql" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIDSLOGDIR=\$KIWI/work/$LOGNAME" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KWSQL_COMMONDIR=\$KIWI/data_$NEW_SITE" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "LD_LIBRARY_PATH=\$PROGS" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KWSQL_USER=kiwisql" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KWSQL_PASS=800486kiwi" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "KIWISEA=.:\$DATA:\$SITEDAT:\$SITEBIN:\$PROGS:\$BIN:\$SCP:\$SQL"  >> $KIWIBASE/site_$NEW_SITE/KIDSENV
  echo "PATH=\$PATH:.:\$SITEDAT:\$SITEBIN:\$CORPBIN:\$PROGS:\$BIN:\$DAT" >> $KIWIBASE/site_$NEW_SITE/KIDSENV
}

setup_counter () {
cd $KIWIBASE
rm $KIWIBASE/counter
ln -s $KIWIBASE/site_$NEW_SITE/work/mdcxmt counter
}

site_select(){
  if [ ! -d $KIWIBASE/site_*  ];then
     log "Sites do not exists" "ERROR" ${RED}${BOLD}${FLASH}
     sleep 2
     main_menu
  fi
    banner
    PS3="Choose site : "
    SITES=`ls -d $KIWIBASE/site_* | cut -b 12-`
    log "Sites that are currently running:" "INFO" ${YELLOW}${BOLD}
    for j in $SITES
        do
                RUNNING=`ps -ef | grep tomcat | grep -i $j | cut -b 133-136`
                if [ ! "$RUNNING" = "" ];then
                        log "${GREEN}${BOLD}Site ${BLUE}${BOLD}$j ${GREEN}${BOLD}is already running" "INFO" ${BLUE}${BOLD}
                fi
        done
    echo ""
    log "Site Ports" "INFO" ${YELLOW}${BOLD}
    for s in $SITES
        do
                if [ -e $KIWIBASE/services/sites/$s/current/conf/recentparametervalues.properties ];then
                        PORTS=`cat $KIWIBASE/services/sites/$s/current/conf/recentparametervalues.properties|grep OFFSET|cut -d"=" -f2`
                        PORTEXT=`expr 8080 + $PORTS`
                        if [ -n $PORTS ];then
                                log "${GREEN}${BOLD}Site ${BLUE}${BOLD}$s ${GREEN}${BOLD}has a port of ${BLUE}${BOLD}$PORTEXT" "INFO" ${BLUE}${BOLD}
                        fi
                fi
        done
    echo ""
    log "Sites that are available:" "INFO" ${YELLOW}${BOLD}
    select SITE in $SITES
    do
      if [ -z "$SITE" ];then
         SITE=$SITES
      fi

      export PLANTID=$SITE
      export KIWI=$KIWIBASE/site_$PLANTID
      export EXEC="kiwimenu menu=support"

      # MySQL query
      QUERY="SELECT SUBSTRING(xl_body,30,39) FROM XLATEP WHERE xl_system='GEN' AND xl_prefix='EE' OR xl_prefix='EI'"

      # Execute query and store results
      results=$(mysql -h "$DBHOST" -u "$MYSQLUSER" -p"$MYSQLPASS" -D "${SITE}_classic" -e "$QUERY" --batch --silent)

      # Check if query was successful
      if [ $? -ne 0 ]; then
         log "Error executing MySQL query" "ERROR" ${RED}${BOLD}${FLASH}
         sleep 2
         main_menu
      fi

      # Read results line by line
      while IFS=$'\t' read -r -a columns; do

         mkdir -p ${columns[0]}
         log "Created the following folder : ${columns[0]}" "INFO" ${BLUE}${BOLD}

      done <<< "$results"

#      setup_counter
      . $KIWIBASE/corp/bin/stdprofile
      break
    done
}
#
# Execute
checkVariables
main_menu
#
#if [ -f ~/.bashrc ]; then
#        . ~/.bashrc
#fi
#
#THIS MUST BE AT THE END OF THE FILE FOR SDKMAN TO WORK!!!
#export SDKMAN_DIR="/KIWI/home/remuser//.sdkman"
#[[ -s "/KIWI/home/remuser//.sdkman/bin/sdkman-init.sh" ]] && source "/KIWI/home/remuser//.sdkman/bin/sdkman-init.sh"
#
#exit 0

Script to be added/created to the $CORPBIN of the server

Edit the following script

vi /KIWI/corp/bin/stdprofile

Add the following

#!/bin/bash
umask 002

if [ -r $KIWI/site/KIWIENV ]; then
   COMM_DIR=`cat /KIWI/site_$PLANTID/site/KIWIENV | grep "^COMMSDIR:" | cut -d":" -f2`
   AB_DIR=`cat /KIWI/site_$PLANTID/site/KIWIENV | grep "^HOMEAB:" | cut -d":" -f2`
fi

export KIWIBASE=/KIWI
export KIWI=${KIWI:-/KIWI/site_$PLANTID}
export BACKUPS=${BACKUPS:-${KIWIBASE}/backups}
export BIN=${BIN:-${KIWI}/bin}

# COMMS
export COMMSDIR=${COMMSDIR:-${KIWI}/work/cscomm}
export COMM_DIR=${COMM_DIR:-COMMSDIR}
export COMMS_PRINTER=98

export CORPBIN=${CORPBIN:-${KIWIBASE}/corp/bin}
export CORPDAT=${CORPDAT:-${KIWIBASE}/corp/dat}
export DATA=${DATA:-${KIWI}/data_$PLANTID}
export HOMEAB=${HOMEAB:-${KIWI}/work/mdcxmt}
export HOMECO=${HOMECO:-${KIWI}/work/corrop}
export HOMECONV=${HOMECONV:-${KIWI}/work/ducker}
export PROGS=${PROGS:-${KIWI}/progs}
export SCP=${SCP:-${KIWI}/scp}
export SQL=${SQL:-${KIWI}/sql}
export UFD=${UFD:-${KIWI}/ufd}
export SITEBIN=${SITEBIN:-${KIWI}/site/bin}
export SITEDAT=${SITEDAT:-${KIWI}/site/dat}
export WORK=${WORK:-${KIWI}/work/${LOGNAME}}
export ftpdir_ord=/mnt/ducker/ordinfo
export ftpdir_seq=/mnt/ducker/corrseq
export ftpdir_ols=/mnt/ducker/ol
export ftpdir_stk=/mnt/ducker/pidnumber
export ftpdir_load=/mnt/ducker/cvm_enter
export DCV_VERSION=3.01
export USESAMBA=1


# VUE Directories
export VUE=${VUE:-${KIWIBASE}/services/sites/$PLANTID/current}
export JCSC=${VUE:-${KIWIBASE}/services/sites/$PLANTID/current}
export VBIN=${VBIN:-${KIWIBASE}/services/sites/$PLANTID/current/bin}
export VLOG=${VLOG:-${KIWIBASE}/services/sites/$PLANTID/current/logs}
export VCONF=${VCONF:-${KIWIBASE}/services/sites/$PLANTID/current/conf}
export KBIN=${KBIN:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/bin}
export KLOG=${KLOG:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/logs}
export KCONF=${KCONF:-${KIWIBASE}/services/etl/kdw/current/kiwiplan/conf}

export PS1="\\[$(tput bold)\\]\\[$(tput setaf 1)\\]\\[$(tput setab 8)\\]
"\\u\\[$(tput setaf 5)\\]@\\[$(tput setaf 1)\\]`hostname`"\\[$(tput setaf 2)\\] : ""`uname`"" : \\[$(tput setaf 6)\\]""\d \t : ""`echo $PLANTID`""
"[\\w]'\$\[$(tput sgr0)\] '
export KWSQL_COMMONDIR=${KWSQL_COMMONDIR:-${DATA}}
#export LANGUAGE=${LANGUAGE:-{KIWI_LANG}}
export KWSQL_USER=${KWSQL_USER:-kiwisql}
export MYSQL_PWD=${MYSQL_PWD:-800486kiwi}
export EXEC=${EXEC:-""}

# Has to come after the $PROGS definition
export LD_LIBRARY_PATH=${PROGS}

#export HOMELD=${HOMELD:-${KIWI}/work/autotags}
#export HOMECONVM=${HOMECONVM:-${KIWI}/work/minda}
#export HOMECONVS=${HOMECONVS:-${KIWI}/work/mindas}

export KIWISEA=".:$WORK:$HOME:$DATA:$SITEDAT:$SITEBIN:$CORPDAT:$CORPBIN:$BIN:$PROGS:$SCP:$SQL"
export PATH=".:$WORK:$HOME:$SITEBIN:$CORPBIN:$BIN:$PROGS:/bin:/usr/bin:/usr/sbin"
export JAVA_HOME=/usr/lib/jvm/jre-11

#if [ -z "${WORK}" ]; then 
#  echo "Variable WORK does not exist. WORK: ${WORK}"
#  sleep 2
#  exit
#fi

if [ ! -d ${WORK} ]; then
  mkdir -p ${WORK}
  if [ ! -d ${WORK} ]; then
    clear
    echo -e "ERROR: Cannot create work directory : ${WORK}"
    echo -e "Press <Enter> to continue ... \c"
    read key
    exit 1
  fi
fi

cd $WORK

if [ -z "${EXEC}" ]; then
  exit
else
  ${EXEC}
fi

Kiwiplan - ULT/VUE Setup a New Machine for Outside Farmout

Kiwiplan - ESP Consignment Store

How to set up Consignment Store

MAP & PCS/Planner

1. Create Stores in MAP & PCS/Planner
   1. Name
   2. Full Address
2. Create Locations
   1. Default (DEF)
   2. Fact Call Off Doors (FC1, FC2, etc.)
   3. Quarantine (QUAR)
   4. Receive (RECEIV)

ESP

1. Create Address in ESP
2. Setup Store Parameters
   1. Configuration > Application > FGSClient > Sections > Stores
   2. 
3. Setup Store to Allow Fast Call-Off
   1. 

      

4. When creating a Stock Line for a Product Design. “Allows Fast Call-Offs” must be checked.

Kiwiplan - ESP Crystal Reports Standards and Help Hints

Customised Reports:

In order to make sure that customised reports, and the SQL views or stored procedures behind them, do not get overwritten during ESP upgrades the following points should be observed:

1. A site specific reports directory should be set up and this must be the first directory on the path listed under the section Directories/Reports in ESP.
2. All customised reports (“.rpt” files) and “.ini” files should be saved in the site specific reports directory. To avoid confusion ONLY customised reports and ini files should be saved to this directory.
3. Any customised report should use a customised source for its data. If the report has been customised but uses a generic SQL view or stored procedure then the view or stored procedure should be copied and renamed. For example the stored procedure for the generic Cost Estimates Report is called “irsCostEstimateDetails”, at one site the stored procedure is called “irsACostEstimateDetails”, at another the procedure is called “irsWCostEstimateDetails”.
4. As a naming convention it is strongly suggested that, in cases where a customised view or stored procedure is derived from a generic one the customised view/stored procedure is given the same base name with a site specific suffix eg for a site called “AnyCartons” a customised version of the view “irsListofInvoices” could be named “irsListofInvoices_AnyCartons”.
5. Once the copy of the view or stored procedure has been made Crystal reports will allow you to “alias” the report fields to the new View/Stored Procedure with no changes required to the fields on the report. This is done through the “Database/Set Location” menu option in the Crystal reports designer.(NOT THE Database/Set Alias” menu option)..
6. Once the alias is set using the “Set Location” option it will be necessary in some case to perform a final step. This is done through the “Show SQL Query” option, also on the Database Menu. If the report is being driven from a View there will be a “FROM viewname” clause. If this has not been changed by step 4) above the following will need to be done:

• If the existing view is called “irsOldView” the new customised view is called “irsNewViw”
• If the from clause was “from irsOldView” this will need to be changed to “from irsNewView irsOldView”
• If the from clause reads “from irsOldView irsOldView” or “from irsOldView as irsOldView” then this needs to be changed to “from irsNewView irsOldView”. The “as” in the second example is optional

These points should minimise the chances of an ESP upgrade causing problems with customised views and reports.

As another safeguard any changes made to existing Crystal reports or ini files should have copies sent to Kiwiplan NZ. Any SQL views or stored procedures should be saved as sql scripts using the naming convention:

Yyymmdd_Updirsxxxxx.sql – where sxxxxxis is an existing view/stored procedure name

or

Yyymmdd_Addirsxxxxx.sql – where sxxxxxis is a new view/stored procedure name

And then a copy of the script sent to Kiwiplan NZ.

If small changes are required to a standard report it is worthwhile discussing the changes with Kiwiplan NZ before deciding to create a custom report. Sometimes small changes can be added to the existing standard report and made available to all customers.

SQL Naming and Coding Conventions

Traditionally all views and stored procedures that are created to be used with reports are named with the prefix “irs”

All scripts that create or modify views, stored procedures or SQL functions should follow the format:

If SQL object exists

Drop SQL object

GO

Create [dbo].[SQL Object]

<SQL to create the object>

GO

Grant permissions on SQL Object

GO

For a view the Grant statement should be:

GRANT SELECT on [dbo].[<view name>] to [PUBLIC]

For a stored procedure (or SQL function) the statement should read:

GRANT EXECUTE ON [dbo].[<stored procedure name>|<SQL function name>] TO [public]

Make sure that all objects are created with the “dbo” owner qualification to the name.

All character variables within stored procedures and functions should be defined as “nvarchar” to cater for Unicode based sites.

If creating temporary tables all character columns should be defined using the “COLLATE DATABASE_DEFAULT” option to cater for sites where multiple collations could be in use. This is shown below:

declare @table table

(

atextfield nvarchar(50) COLLATE DATABASE_DEFAULT null,

....more fields

)

This allows sorts and equality testing to happen without an error being raised.

A couple of points about performance:

• Use table variables for temporary tables rather than the traditional “#” temp tables
• Avoid cursors if possible
• Think in terms of set operations rather than individual row operations. That is if a query can be written to perform bulk operations then use this in preference to traversing a result set and working on individual rows
• Use Case statements in queries to cater for multiple options rather than repeating the query.
• Try to think of the way the indexes on the tables involved in a query are structured when building a “WHERE” clause, the closer the “WHERE” clause matches underlying indexes the more chance there is that the query optimizer will utilise the index.

Standard ESP SQL Functions

The following functions are part of the ESP database.

Note: For the date and financial period functions “zero hour” is “00:00:00” hours on the day, “midnight hour” is 23:59:59.99” on the day

Function name	Parameter(s)	Returns	Description
Date Functions -Calendar dates
dbo.fn_getstartday	datetime	datetime	Given a datetime value, returns the “zero hour” value for that date
dbo.fn_getendday	datetime	datetime	Given a datetime returns the “midnight hour” value for that day
dbo.fn_getstartmonth	datetime	datetime	Given a datetime returns the “zero hour” value for the first of the month
dbo.fn_getendmonth	datetime	datetime	Given a datetime returns the “midnight hour” value for the last day of the month
dbo.fn_getstartyear	datetime	datetime	Given a datetime returns the “zero hour” value for the first of January for that year
dbo.fn_getendyear	datetime	datetime	Given a datetime returns the “midnight hour” value for the 31st of December for that year
Date Functions - Financial dates			These need the financial year and financial periods to be set up in ESP to work, otherwise they fall back to calendar months and years
dbo.fn_getstartfinperiod	datetime	datetime	Returns the “zero hour” on the first date of the financial period that the given a date is contained in
dbo.fn_getendfinperiod	datetime	datetime	Returns the “midnight hour” on the last date of the financial period that the given a date is contained in
dbo.fn_getstartfinyear	datetime	datetime	Returns the “zero hour” on the first date of the financial year that the given a date is contained in
dbo.fn_getendfinyear	datetime	datetime	Returns the “midnight hour” on the last date of the financial year that the given a date is contained in
General Utility Functions
dbo.fn_IsInList	@strlist nvarchar(1024) @strsearch nvarchar(100) @joinchar nvarchar(1)	smallint	Given a string containing a list of values (@strlist) separated by a single character(@joinchar) and a value to search for(@strsearch) returns 0 if the exact value is not found or 1 if it is.
dbo.fn_parsecriteria	@sValue nvarchar(200) @fieldname nvarchar(100) @datatype nvarchar(50)	nvarchar(500)	Given a string containing a value to include in a WHERE clause, the name of the field to search on and the data type of the field will return a string that can be concatenated to a SQL statement
dbo.fn_as16s	Int	nvarchar(30)	Given an integer value returns the value formatted as 16ths ie Given 21 will return “1.05”, Given 30 returns “1.14” (Used on sites that use imperial units of measure)
dbo.fn_fixx	Float	Int	Given a number returns the largest integer less than or equal to the given number Given 1.5 returns 1 Given -2.4 returns -3
dbo.fn_MaskValue	@mask nvarchar(500) @pval int	nvarchar(500)	Returns the given number masked with the given mask
dbo.fn_StripMask	@mask nvarchar(500) @pval vnarchar(500)	Int	Returns the numeric value of a masked string
dbo.fnGetFKColumns	@constraintID int, @keyID int, @rf nchar(1)	varchar(2126)	This , and the next function are used to retrieve details on foreign key constraints and indexes. There were written to be used in the stored procedure “dbo.inf_ResetCollation” (see Utility Stored Procedures section below)which resets and rebuilds all constraints and indexes in a given database to a given collation.
dbo.fnGetIndexColumns	@objname sysname @objid int @indid int	varchar(2126)
ESP Specific Function
dbo.fn_numworkdays	@firstdate datetime @lastdate datetime @id int @idtype nvarchar(1)	int	Return the number of working days between a given start and end date for either a specific address or a plant if the @idtytpe is “P”(uses the plant base address). This uses the “opendays” field from the address.
dbo.fn_getcoatings	@pdid int @coattype varchar(2)	varchar(500)	Returns a comma separated list that contains, for each colour/coating, the sequence, code and coverage. The parameter @coattype can be one of the following: IC – Inside colour OC – Outside colour II - Inside coating OI – Outside coating
dbo.fn_getPDCoating	(@pdid int @isColourorCoating int @inside smallint @coatorcover int	varchar(500)	Returns a comma separated list of either the coating/colour codes or the coverage percent. Data is selected from the table ebxproductdesigncoating based on the parameters: @inside equals the column inside ( can be -1 or 0 ) @isColourOrCoating equals the column @isColourorCoating
dbo.fn_ShipPiecesPerUnit	@pd int @route int	int	For a given route for a product design returns the quantityperunit field from th euntizing data for the last step on the route.
dbo.fn_unitcost	@ceid int @ppid int	float	Returns the “per unit” full cost value from either a cost estimate or product price record.
dbo.fn_UnitSummaryQty	@unitsummary varchar(1000)	int	Returns the total quantity of items as held in the unit summary field
dbo.fnGetSetTotalPrice	@CostEstimateID int @CalculationQuantity int, @currdate datetime	money	Returns the total “freight inclusive” price from the product price table for all components (if any) that are part of the set defined by having the supplied cost estimate as their “master” cost estimate. They must also have the same calculation quantity and be active at the given time.
Functions to access MAP/ULT data		These functions have been re-written as stored procedures so that they would not be restricted to a “hard coded” linked server. The sored procedures are named under the function names and both take the linked server name as a parameter. Output remains a table/dataset with the same column names.
dbo.fn_GENPL() (Stored procedure dbo.irsGENPL)		plantno varchar(8) plantname varchar(30) defaultCorrugatorMachineNo int defaultDespatchMachineNo int boardTransferMachineNo int pickupGoodsMachineNumber int quarantineMachineNumber int fgsReworkStoreMachineNo int sbsFromStockMachineNumber int sheetBoardStrapperNumber int sheetBoardSlitterMachineNo int supplyFromStockMachineNo int defaultRssReceiptingStore int stockDespatchMachineNo int despatchNotificationMachineNo int defaultBoardReceivingmchNo int defaultOrderingMachineNo int	Returns a table giving, for each plant in the MAP dataset, the default machines as set up in the GEN/PL XLATEP parameter in MAP
dbo.fn_GENTU() (Stored procedure dbo.irsGENTU)		pallettype varchar(8) pallet varchar(30) width int null length int null height int null weight int null	Returns a table giving, for each pallet type, the width, length, height and weight. These are taken from the GEN/TU XLATEP parameter in MAP

Utility SQL Stored Procedures

The following table lists stored procedures that provide functionality that is outside specific report requirements.

Stored Procedure	Input Parameters	Output	Description
Procedures to access MAP/ULT data		These procedures assume a valid “linked server” to a MySQL database is set up. The syntax of the SQL is MySQL specific.
dbo.irsGENPL	nvarchar(500)	See entry above for dbo.fn_GENPL()	Given a linked server name returns the table described above in the Functions section
dbo.fn_GENTU	nvarchar(500)	See entry above for dbo.fn_GENTU()	Given a linked server name returns the table described above in the Functions section
dbo.irsCORRCP	nvarchar(500)	plant_number int, corrugatornumber int, corrugatorname nvarchar(30) machinenumber int	Given a linked server name, returns a list of all corrugators with information taken from the CORR/CP parameter in the XLATEP table and the CSCNTR table.
dbo.irsmachines	nvarchar(500)	Machineno interactionmachinename nvarchar(40) machinegroup int	Given a linked server name, returns a list of all machines held in the CSCNTR table.

Crystal 10 Database Connections

Select “Create New Connection”

Select “ODBC (RDO)”

Select “Enter Connection String”

Enter the string “DRIVER=SQL Server”

Fill in the details:

1)Server is the database server

2)User ID and Password are the values held in the ESP Parameters under

Configuration/Application/InfClient(or Infrastructure)/Section/Security/Report Login Name

Configuration/Application/InfClient(or Infrastructure)/Section/Security/Report Login Password

3)Database is the ESP database name

DO NOT check the “Trusted Connection” check box

Click on “Finish” and if all details have been entered correctly you should be able to select tables, views or stored procedures from the database.

Crystal Reports and ESP

This document provides an outline of the interaction between ESP and Crystal Reports and covers the points to be aware of if you are planning on creating Crystal reports to be used from within ESP.

Overview

There are two major components involved in the integration of Crystal Reports with ESP.

• Ini files – these provide a specification for the construction of the report options form that allows the user to make criteria selections
• Rpt files – these are the actual Crystal reports files

These files MUST have the same base name for example if you have saved your report as

“My Report.rpt”

the ini file must be called

“My Report.ini”

Both files must exist and must reside in a directory that is specified in ESP in the “INFClient/Sections/Directories/Reports” parameter.

Sections below cover the following points in more detail:

• An overview of the processes involved when running a report
• Ini Files
• Points to take into consideration when writing Crystal reports
• Adding reports to ESP
• Report Data Sources

Running a Report

Once a report is selected from the reports menu, or one of the sub menus, the following process takes place:

• ESP looks for the ini file with the same name as the report. It searches for this file in the directories specified by the “INFClient /Sections/Directories/Reports” parameter, in the order that the directories are listed.
• Once found the ini file is read and the report options form is created with the controls specified by the various sections in the file (see Ini Files below). Default values are placed in fields where specified.
• If translation is turned on then all labels and constant text values are translated, this includes all hard coded lists (combo boxes of “listAbsValue” type or default text)
• The options form is displayed and the user makes their selection of criteria/parameters/sorting fields.
• The user also has the option of either exporting or viewing the generated report.

Once the user has made their selections and clicked the “OK” button (or pressed enter) ESP performs the following actions:

• ESP looks for the rpt file in the directories named in the “INFClient /Sections/Directories/Reports” parameter. NOTE: The rpt file does NOT have to be in the same directory as the ini file, as long as it can be found in one of the directories specified in the parameter.
• If translation is turned on for the site then the report is translated (see Crystal Reports – Translation below)
• Values selected or entered in the various parameter and criteria fields on the report options form are processed and the Crystal reports’ SQL is updated (if required) and report parameters are set.
• The reports data source is set to be the current database that ESP is running against unless the section “[DBConnection]” has been set in the Ini file. If it has the connection will either be set to the defined connection or left as is.
• If the “Export Report” option has been selected the Crystal Reports “Export” dialogue box where the type and location of the export can be chosen. The report is then generated and output to the specified location.
• If the “View Report” option has been selected the report is generated and displayed in the Crystal reports viewer window.

Ini Files

Ini files provide information to ESP to allow it to construct a report options form that acts as the interface between the end user and the Crystal report being run. They follow the standard Windows ini file format with section headers, indicated by a keyword enclosed in square brackets alone on a line:

[Criteria]

and section bodies, generally a keyword followed by an equal sign followed by various values separated by semi-colons:

Report Date=date;{irsRepView.datefield}

Sections recognised by ESP are:

• [General] – This must always be the first section and always consists of the one line body:

Form=frmReportOptions

• [Criteria] – This section specifies values which will be used to build a, or add to an existing, “where clause”. This section is NOT used if the data for the report is supplied by a stored procedure.
• [Default Criteria] – This section provides default values for items in the “Criteria” section. Labels must match exactly with a Criteria Label in the Criteria section.
• [Parameters] – This section is used to supply values to Crystal report “Parameter” fields. There are two types of parameter, displayed or hidden. Either type of parameter can be either used internally in the report of passed through as input parameters to stored procedures.
• [Default Parameters] – Values in this section provide default values to items in the “Parameters” section. Labels must match exactly with a Parameter Label in the Parameters section.
• [DBConnection] – Specifies an alternate data source to be used for the current report.
• [Sortable Fields] – Any items in this section are used to structure the sort order of the report. If a report has pre-defined grouping the sorting will be defined at the detail level, within the lowest level of grouping.
• [Default Sorting] – Values in this section allow the default sort options to be offered, including setting the sort direction.
• [Formulas] – Formulas and Formula Fields allow for a degree of end user selection of fields to appear on the report. These fields can be calculated, concatenations of multiple fields or can be individual fields. This section defines the labels and available formulae.
• [Formula Fields] – This section lists the formula field names that are available on the report and defines the link between the formula field used to display a selected formula value and the formula field which will display the formula label.
• [Default Formulas] – This section provides default formula selections.
• [Show Form] – This section is used to tell ESP that the report options form should not be displayed
• [Default Mode] – This section is used to set the default mode for processing the report (View or Export)

The following is the contents of an example ini file. Most ini files will not be anywhere near as complicated as this. For the purposes of documentation every possible section and option has been used. Sections may be left blank, for example a report that relies on a stored procedure for its data will have no values in either the Criteria or Default Criteria sections and many reports have no Sort or Formula sections defined. Following the sample is an explanation of the various sections and detail lines of the file.

[General]

FormName=frmReportOptions

[Criteria]

Plant=Long;{espInvoice.plantID};listQryID=plant,,name,ID;

Customer=String;{orgcompany.name};listqryvalue=company,id in(select id from orgcompany where iscustomer = -1),name

Invoice Number=String;{espInvoice.InvoiceNumber};

Transaction Date=Date;{espInvoice.TransactionDate};

OrderType=String;{espOrder.ordertype};listAbsID=0,Make and Deliver,1,Call Off,2,Top Up

(NP)Status=String;{irsInvoice.InvoiceStatus}

Product Design Number=String;{ebxproductdesign.designnumber}

[Parameters]

Application Name=Application Name

Application Version=Application Version

Username=User

Copyright=Copyright

Criteria=Criteria

(NP)ReportType=String;

ReportCustomer=Customer

Invoice Type=String;;listAbsValue=Product,Non-Product

Report Date Range=Date;

[DBConnection]

Server=Access1

DB=db11

Login=mylogin

Password=no password

[Sortable Fields]

Invoice Number={espInvoice.InvoiceNumber}

Transaction Date={espInvoice.TransactionDate}

Customer Name={orgCompany.Name}

[Formulas]

Description LxW=ToText({espOrder.width}) + " X " + ToText({espOrder.Length})

Status={espOrder.OrderStatus}

Contact Name={orgContact.FirstName} + " " + {orgContact.LastName}

Special Instructions={espOrder.SpecialInstructions}

[Formula Fields]

extraField1=extraField1Label

extraField2=extraField2Label

extraField3=extraField3Label

[Show Form]

[Default Mode]

Export

[Default Criteria]

(NP)Status=<>Cancelled

Transaction Date=[mtd]

Product Design Number=[class#productdesign.designnumber]

[Default Parameters]

(NP)ReportType=Detail

Invoice Type=Product

[Default Sorting]

Invoice Number=ascending

[Default Formulas]

extraField1=Description LxW

All the first two lines in ini files used for reports must start with:

[General]

FormName=frmReportOptions

The other sections can appear in any order, the only requirement is that the section header appear as the only value on a line, it is not necessary to have blank line between the last item in a section and the next section header, however it does make the ini file easier to read.

The usage of each section is detailed below. The section name is given, followed by a line showing the syntax of the detail items, under this is an example line from the sample ini file, then a description of the parts of the line.

[Criteria]

Field Label=Data Type;{Data Field};Combo Type(optional);Required?(optional)

Plant=Long;{espInvoice.plantID};listQryID=plant,,name,ID;Yes

• Field Label / Plant -The value to the left of the equal sign is the name that will appear on the options form as the label for the field. If a value is selected in the field the label is also formatted into the “Criteria” field value displayed in report headers. If the Field Label is prefaced with the string “(NP)” then the criteria will not be displayed on the options form although if a default value for it is specified in the “[Default Criteria]” section the value will be used in building the SQL where clause.
• Data Type / Long – The value immediately to the right of the equal sign indicates the data type of the field. This can be one of the following:
  • Long – The data entry will be a text box unless a Combo Type is specified
  • String – The data entry will be a text box unless a Combo Type is specified
  • Date – The data entry will be a text box unless a Combo Type is specified
  • Boolean – Data entry will be via a check box
• {Data Field} / {espInvoice.PlantID} – This is the field against which the entered criteria is applied in the where clause. It must be enclosed in curly braces “{}” and must match exactly the name of a field that is available to the query in the report. This can be either a table column or a field from a SQL view (if that is the data source for the report. In this example if the user had entered the value 5 in this field on the options form, then ESP would format this as:

(espinvoice.plantid = 5)

If the query already had a where clause would append this as

and (espinvoice.plantid = 5)

If the query did not have a where clause this would be added

where (espinvoice.plantid = 5)

• Combo Type / listQryID=plant,,name,ID – The “Combo Type” part of the section detail item is optional and is used when a drop-down combo box is to be displayed. There are several different combo box types available depending on the source of the data.
  • ListQryId – Data is supplied by a query and the “ID” field from the selected row is used.
  • ListQryValue – Data is supplied by a query and the value of the selected row is used
  • ListAbsId – Data is supplied by a hard coded list and the “ID” value is used
  • ListAbsValue – Data is supplied by a hard coded list and the value is used.

The syntax of the two “ListQry…” combos is

Table Name,Filter Clause(optional),Display Value,ID(not used if the combo type is ListQryValue)

Where

• • Table Name is the business class name (the database table name without its 3 letter prefix)
  • Filter Clause is a filtering condition and should take the form of

Field=condition

For example, in the example being used, the customer combo will only display names from the orgcompany table where the “IsCustomer” flag is true (-1),

id in(select id from orgcompany where iscustomer = -1)

If no filter condition is used then the space must be delimited with a trailing comma (as in the sample ini file)

• • Display Value is the name of the field to display in the list, in the case of a “ListQryValue” this is the field used to build the where clause.
  • ID is specified when the combo type is “ListQryID”

The syntax of the combo type “ListQryID” is as follows

listAbsID=ID1,Value1,ID2, Value2,….

listAbsID=0,Make and Deliver,1,Call Off,2,Top Up

In other words, it consists of a list of pairs of values, a numeric “ID” value followed by a string to be displayed, all values separated by commas.

The syntax of the “ListQryValue” combo consists of a simple list of values separated by commas.

• Required?(optional) - This is an optional value, that, if set to one of the following values will cause the field background to be displayed with the colour defined in the ESP parameter “InfClient/FORMS/Required controls colour”. It will also cause ESP to check that a value has been entered in the field when the user clicks the “OK” button. If there is no value in the field then the user is warned and asked to enter a value.
  • 1
  • -1
  • true
  • required
  • mandatory
  • yes
    

[Default Criteria]

Criteria Label=Default Value

(NP)Status=<>Cancelled

There are three types of default values. In the example above the value is literal, in that it will be displayed exactly as it is (<>Cancelled) against the specified Criteria. The second type is a keyword enclosed in square brackets as in the line below.

Criteria Label=[Keyword]

Transaction Date=[mtd]

The possible keywords are explained in the following list

• [now] – The current date and time formatted as a "General Date"
• [datenow] – The current date formatted as a “Short Date”
• [date] – The current date formatted as a “Long Date”
• [time] – The current date formatted as a “Long Time”
• [mtd] – A date range with the first of the current month as the first value and “today” as the second value, both formatted as “Short Date”
• [ytd] – A date range with the first of the current year as the first value and “today” as the second value, both formatted as “Short Date”
• [yesterday] – The day before the current date formatted as a “Short Date”
• [lastmonth] – A date range with the first of the previous month as the first value and the last of the previous month as the second value. Both formatted as “Short Date”
• [lastyear]– A date range with the first of January of the previous year as the first value and the thirty first of December of the previous year as the second value. Both formatted as “Short Date”

The third type of default value is also enclosed in square brackets and has the form:

Criteria Label=[Default Value]

Product Design Number=[class#productdesign.designnumber]

In this type of default value the keyword class# indicated that we want ESP to use the current context of the user and to

use as a default the specified property of the class. In this example, if the user had the Product design form open then the current Product designs design number would be placed as a default value against the Criteria “Product design Number”

[Parameters]

There are two major types of Parameters, displayed and hidden. In both types the Parameter Label that appears to the left of the equal sign must exactly match a Parameter field defined in the Crystal report. If the parameter does not exist (or is misspelled) then any value derived from the Parameter from the ini file is ignored.

Displayed Parameters

Displayed parameters have a similar syntax and usage to Criteria.

Parameter Label=Data Type;;Combo Type(optional);Required?

Invoice Type=String;;listAbsValue=Product,Non-Product;Mandatory

The major differences between Displayed Parameters and Criteria are:

• The “Data Field” that appears on the Criteria is missing from the Parameter line.
• If no “Combo Type” is specified then the Data Type must be terminated with a semi-colon.
• No “where clause” is built using any entered value. Apart from date parameters all values are passed through to the equivalent report parameter
• Date values can be either a single date or a date range (two dates separated by two dots). When ESP comes to process the value in a Date parameter it performs the following actions:
  • If the value is a single date, say 21/06/04, then two values are created, one that is for the start of the day, formatted as “yyyymmdd hh:mm:ss”, (“20040621 00:00:00”) and one for the end of the day (“20040621 23:59:59”).
  • If a range has been entered, say 20/06/04..30/06/04, then the first value is formatted as the start of that day (“20040620 00:00:00”) and the second as the end of that day (“20040630 23:59:59”)
  • It looks for a parameter in the Crystal report with a name that matches the parameter label but with the word “start” added to the end. In the sample ini file the parameter from the ini file is “Report Date Range” and the expected parameter from the report would be “Report Date Rangestart”. The first date value is assigned to this parameter.
  • Next ESP looks for a report parameter with the same base name but ending with the word “end” (“Report Date Rangeend”) and assigns the second value to this parameter if it is found.
  • If no report parameter ending with the word “start” is found ESP will look for a report parameter that exactly matches the Parameter Label and if found will assign the literal value of the field to it.
• Ranges (two values separated by .. (two dots) are only supported for date parameters. With Criteria ranges are supported for String and Numeric data types as well.
• The label shown as black text on a white background (when using the standard Windows colour scheme). Criteria labels are black text on a background the same colour as the form background.

Hidden Parameters

Hidden parameters fall into three categories, Keyword, Criteria Value and Hidden Value

Keyword Parameters

Keyword=Keyword

Application Name=Application Name

There are eight possible keyword parameters. They follow the syntax of two instances of the keyword, separated by an equals sign. The possible keywords are:

• Application Name – This is the application that the report is listed as a parameter under. In the standard reports this will be “InfClient”
• Application Version – This is the current major version of ESP e.g. 4.028 or 4.036
• Username – The logged on user running the report
• Copyright – Kiwiplan Copyright
• Criteria – This is a “readable” version of the where clause built from selected Criteria values. It follows the format of :

(Criteria Label = Selected Value) And (Criteria Label 2 = Selected Value 2)

• Now – This is the current date and time that the user presses “OK” on the options form
• Date – The current date at the time that the user presses “OK” on the options form
• Time – The current time that the user presses “OK” on the options form

Criteria Value parameters

Parameter Name=Criteria Label

ReportCustomer=Customer

These are Parameters that are not displayed but which are loaded with the value selected by the user for the Criteria. This is useful as the individual Criteria values are not available within the body of the report.

Hidden Value Parameters

(NP)Parameter Label=DataType;

(NP)ReportType=String;

Hidden Value parameters have their Parameter Label prefixed by the string “(NP)”. As with all parameters the name of the Crystal Report parameter must match exactly (including the “(NP)”) the label in the ini file. The parameter line in the ini file must be terminated with a semi-colon. This type of parameter would be used to pass a default value through to the report that the report designer did not want the user to see, possibly to set site specific values in a report that was otherwise generic.

[Default Parameters]

Default Parameters follow the same rules and have the same processing as Default Criteria. However they are only applied to Displayed Parameters and Hidden Value Parameters. Keyword Parameters are already their own default and to apply a default to a Criteria Value Parameter you would apply it to the Criteria that the parameter was pointing to.

[DBConnection]

Connection Parameter Name=Value

Server=Access1

DB=db11

Login=mylogin

Password=no password

This section is used by ESP to set the connection details for a report if it has been defined to use a different data source than the ESP database. There are two ways of using this section,

• The first as shown in the sample ini file, specifies the values required to set the database connection. The example is for a system ODBC DSN.
  • Server=Access1 – In the case of an ODBC connection this is the DSN name, if the report was being pointed at a SQL Server database (using the Crystal native SQL driver) then the server value would be the name of the SQL Server.
  • DB=db11 – This is the database that holds the tables that the report is looking for..
  • Login=mylogin – this is the username to use to login on to the database. If this option is not specified or is blank the default “report user” as defined for ESP will be used.
  • Password=no password – This indicates that no password is to be used on this connection. If this option is not specified or left blank the default “report user password” as defined for ESP will be used.
• The second way is to specify a single line:
  • UpdateTables=no – This tells ESP that you want it to use the connection details from the report itself. However there is a limitation here in that the user and password on the report connection MUST be the same as the default defined as the ESP “report user”.

[Sortable Fields]

Sort Field Label={Sort Data Field}

Invoice Number={espInvoice.InvoiceNumber}

The Sort Field Label is displayed in the list box of available sort fields. Selecting any of the sort fields will cause the report to be sorted on the Sort Data Field assigned the Sort Field Label Name.

[Default Sorting]

Sort Field Label=Sort Direction

Invoice Number=ascending

Any entries must exist as Sort Field Labels in the Sortable Fields section, and will be automatically selected when the form is displayed. The value to the left of the equals sign is optional and can be either “ascending” or “descending”. If omitted the equals sign must be used and the default sort direction will be descending. Multiple default sort fields can be specified below each other in this section. The order that they are placed in determines the precedence of sorting on the report.

[Formulas]

Formula Label=Formula

Description LxW=ToText({espOrder.width}) + " X " + ToText({espOrder.Length})

The Formula Label is what is displayed in the drop down list of available formulae.. It is also the value that is assigned to the Label Formula Field. The Formula is assigned to an empty formula field on the Crystal report.

The syntax of the Formula must be acceptable to Crystal Reports. (Default syntax used in the KiwiPlan generic reports is “Crystal” not “Basic”)

[Formula Fields]

Formula Field Name=Label Formula Field Name

extraField1=extraField1Label

This section holds a list of pairs of empty formula fields from the Crystal report. The Formula Field Name is the formula to which a selected formula is assigned. The Label Formula Field Name has the Formula Label assigned to it.

[Default Formulas]

Formula Label=Formula Field Name

Description LxW=extraField1

The Default Formulas section allows the assignment of formula to specific formula fields.

[Show Form]

This section is optional if used and the [Show Form] section header is followed on the next line by the word “No” the report options form will not be displayed. This option can be used where a report can have the various criteria and /or parameters set with default values that should not be altered. For example a sales report that is always run using the last calendar month as the date range.

[Default Mode]

Export

This section is optional. It is used to specify that the “Export Report” option is selected by default on the report options form. If omitted or any other value than “Export” is placed under the heading the default mode will be set to “View Report”

Crystal Reports

This section outlines the points that need to be taken into consideration when either writing new reports or modifying existing reports for use from ESP. Most of these have to do with making sure that reports are handled correctly when language translation is turned on for Crystal reports from within ESP. There are three conditions that must be met to enable language translation of Crystal reports when they are run from ESP.

• The Crystal runtime dlls, CRAXDRT.dll (version 8.5.0.674) and CRPE32.dll (version 8.5.3.979), must exist and be registered. Both of these ship with ESP. DLL
• The parameter INFClient/Section/Language Translation/Enable Translation must be set to “True”
• The parameter INFClient/Section/Language Translation/Translate Crystal Reports must be set to “True”

If these three conditions are met then ESP will translate the report using the process outlined below.

In order for any values to be translated there must be an entry in the “InfClient” language table for the value and there must be a “Site Text” value for that entry. If there is no entry in the language table, or if the “Site Text” field is empty for the value then it will be displayed on the report “as is”.

Overview of the Translation process

Translation of a report is done in several steps.

• A copy of the report is made
• All formula field in the report are processed
• All formulae associated with groups and sections (conditional formula) are processed
• All text objects on the report are translated
• All conditional formula attached to formulae, fields and text objects are translated.

Formula Field Translation

• Each formula field on the report is examined and any values enclosed in double quotes are translated.
• If the string “//func” is encountered then all text from that point to the end of the formula is not processed
• If a sting beginning with a double at sign “@@” is encountered it is assumed to be a placeholder for one of the ESP Crystal functions as in the following list:

Placeholder
@@smallareafull()	Returns the full text of the small area unit from the Unit table
@@smallarea()	Returns the abbreviation of the small area unit from the Unit table
@@largeareafull()	Returns the full text of the large area unit from the Unit table
@@largearea()	Returns the abbreviation of the large area unit from the Unit table
@@smallweightfull()	Returns the full text of the small weight unit from the Unit table
@@smallweight()	Returns the abbreviation of the small weight unit from the Unit table
@@largeweightfull()	Returns the full text of the large weight unit from the Unit table
@@largeweight()	Returns the abbreviation of the large weight unit from the Unit table
@@smallvolumefull()	Returns the full text of the small volume unit from the Unit table
@@smallvolume()	Returns the abbreviation of the small volume unit from the Unit table
@@largevolumefull()	Returns the full text of the large volume unit from the Unit table
@@largevolume()	Returns the abbreviation of the large volume unit from the Unit table
@@smalllinearfull()	Returns the full text of the small linear unit from the Unit table
@@smalllinear()	Returns the abbreviation of the small linear unit from the Unit table
@@largelinearfull()	Returns the full text of the large linear unit from the Unit table
@@largelinear()	Returns the abbreviation of the large linear unit from the Unit table
@@currencysymbol()	Returns a dollar sign. To enable translation of this into the local symbol the “$” symbol will need to be defined and have a “Site Text” value in the translation table

In this case the returned value from the function will replace the placeholder in the string.

if {?UseTranslation} then

"Avg area per order (@@smallarea())"

//func

else

"Avg area per order " + EspUOMgetUnitInfo("abbreviation","area","small")

If the site in question had “sq m” as their small area abbreviation and translation was turned on the above would result in the following string being passed through to be translated:

"Avg area per order (sq m)"

And the translated value of the string would be displayed on the report. The line “//func” would cause the translation process to ignore the remainder of the formula.

Note that if translation is turned off then the first part of the formula would be ignored and the “else” portion would be executed while the report was being generated.

Group Name Translation

The process used here is identical to that followed by ESP when translating Formula Fields.

Text Object Translation

• All text objects in the report are translated
• It is not possible, with a text object, to conditionally translate part of the text. If there is text that is required to be displayed un-translated then one of the following methods can be used:
  • Do not have a “Site Text” value for the text in the language table”
  • Place the text in a formula field and place the string “//func” on the first line of the field
• DO NOT use Text objects with fields embedded in them. Due to limitations in the 8.5 version of Crystal, ESP will strip the embedded fields out and replace them with square brackets.

Conditional Formula Translation

The process used here is identical to that followed by ESP when translating Formula Fields.

Adding Reports to ESP

Reports are made available to users in two steps

• Placing the report and ini file in the appropriate directory. These directories are specified in the parameters section of ESP under “InfClient/Section/Directories/Reports”. The directories are searched in the order that they are listed in this parameter, this allows for site specific reports (or ini files) to be placed in a folder that will not be overwritten during an upgrade process. The report and ini files do not have to both be in the same directory if, for example, a site had customised defaults on a standard report they could place the customised ini file in the site specific directory (normally first in the parameter) and leave the report in the standard ESP reports directory.
• Making a parameter entry in the InfClient/Section/Reports area of Configuration. This can be done either manually or through running the SQL outlined below.

if (select count(id) from infparameter where value like 'report=Chep Pallets%') <= 0

BEGIN

EXECUTE infHelperInsParameter 'InfClient',

'Reports',

'all=Chep Pallets',

'report=Chep Pallets|app=INF|group=Pallet Reports|caption=Chep Pallets', 0,

NULL

END

go

This SQL checks to see if the report already exists, if not it calls the stored procedure “infHelperInsParameter”, passing the following parameters:

• ‘InfClient’ – This is the Application under which the report is to be listed, all ESP reports should use ‘InfClient’
• ‘Reports’ – This is the parameter branch that reports are stored on
• ‘all=Chep Pallets’ – this is the “name” field value and indicates that the report is available whatever the current users context in ESP.
• ‘Report=Chep Pallets|App=INF|group=Pallet Reports|caption=Chep Pallets’ – this parameter consists of label=value pairs separated by “|” symbols
  • Report=Chep Pallets – the value to the right of the equal sign is the name of the rpt and ini files without their extensions
  • App=INF – the application group, should always be ‘INF’
  • Group=Pallet Reports – this is an optional value, if included it indicates that the report should be placed on a sub-menu called (in this example) “Pallet reports”
  • Caption=Chep Pallets – this is the value which will be displayed on the menu, it may differ from the true report name.
• ‘0’ - this is scope of the report parameter, 0 indicating a report that is globally visible.
• ‘NULL’ – This parameter is not used and should be left set as ‘NULL’

Support Scenarios:

Check that report and ini are in one of these folders

Problem:Can't find rpt or ini:

Solution

Check that the report and ini files are in one of the folders listed under

Configuration/Application/InfClient/Section/Directories/Report Directory

The rpt and ini do NOT have to be in the same folder, but files are used based on the ordering of the

folders.

Problem Login failed:

Solution: This is caused by the report user and/or password set in ESP not having correct permissions in SQL Server to access the ESP database. Ensure that the values set in the parameters

Configuration/Application/InfClient/Section/Security/Report Login Name

Configuration/Application/InfClient/Section/Security/Report Login Password

match a valid user in the ESP database.

Ensure that this is a valid user in the ESP database and the correct password for that user

Problem: Unable to see labels on report options form Labels and background both set to the same colour

Solution:

This is caused by the colour settings in the Display Properties reached by right clicking on the desktop and selecting the “Appearance” tab. The sfest option is the “Windows Classic” or “Windows Standard” style, however other styles can be fine, it just may take some experimentation to find the correct one.

Problem: Unable to connect to database

s

Solution: This is caused by an incorrect datasource used when creating the report. The following procedure should be followed to ensure that ESP can correctly set the datasource on the report at runtime.

In Crystal Reports open the Database/Set Database Location menu option

Select “Create New Connection”

Select “ODBC (RDO)”

Select “Enter Connection String”

Enter the string “DRIVER=SQL Server”

Fill in the details:

1)Server is the database server

2)User ID and Password are the values held in the ESP Parameters under

Configuration/Application/InfClient(or Infrastructure)/Section/Security/Report Login Name

Configuration/Application/InfClient(or Infrastructure)/Section/Security/Report Login Password

3)Database is the ESP database name

DO NOT check the “Trusted Connection” check box

Click on “Finish” and if all details have been entered correctly you should be able to select tables, views or stored procedures from the database.

Linked Server

The target audience for this document is technical and support personnel.

Manual Setup

ODBC DSN

On the SQL Server machine

1) Install the MySQL ODBC Driver

2) Set up a System DSN

Name of MySQL server

Name of database to link to

Name of user, must have privileges on the database being linked to

Leave this

3)Set options:

These are defaults-leave them

Set this

Once these details click on “Test data Source” if the test is successful click on OK if not there is a chance that the user has not had access granted on the MySQL database.

Linked Server Setup in SQL Server

After this is done the Linked Server needs to be setup. There are two ways of doing this, both must be carried out on the SQL server machine.

Manual Setup

Expand the Security branch of the Explorer tree in Enterprise Manager, then right click on the “Linked Servers” node. Select “New Linked Server” and the following dialog will be displayed:

This can be anything

Select from drop down list

This can be anything

Name of System DSN set up above

Then open the “Security” tab and select the “Be made using this security context” option.

Enter the name of the user defined in the DSN as the “Remote login" as well as any password defined in the DSN.

Click on OK and that should be it.

Setup Using Scripts

ODBC DSN

It is possible to create a registry script that will create a system DSN, however it is not recommended as

the possibility of corrupting the registry exists.

Linked Server Setup

The linked server in SQL Server can be created using the system stored procedure “sp_addlinkedserver”. The following SQL commands will accomplish this. See notes after the SQL for more explanation.

/*

Script to add linked server, requires that a ODBC System DSN be set up first on the actual SQL Server

machine with the name "MAPMYSQL"

*/

EXEC sp_addlinkedserver 'MAPMYSQL', 'MySQL', 'MSDASQL', Null, Null,

'Driver={MySQL ODBC 3.51 Driver};DB=mysqldb;

SERVER=mysqlserver;option=512;uid=mysqluser;pwd=mysqlpassword;'

EXEC sp_addlinkedsrvlogin 'MAPMYSQL', 'false', NULL, 'mysqluser', 'mysqlpassword'

EXEC sp_serveroption 'MAPMYSQL', 'data access', 'true'

EXEC sp_serveroption 'MAPMYSQL', 'use remote collation', 'true'

EXEC sp_serveroption 'MAPMYSQL', 'connect timeout', 0

EXEC sp_serveroption 'MAPMYSQL', 'query timeout', 0

/*

End script

*/

NOTES:

1. The value (MySQL ODBC 3.51 Driver) will need to be changed if a later version of the MySQL ODBC driver is used.
2. The values for DB, SERVER, uid, pwd in the sp_addlinkedserver line will need to be modified for each site. These should match the values used to set up the ODBC System DSN.
3. The values 'mysqluser' and 'mysqlpassword' in the sp_addlinkedsrvlogin will need to be edited to

match the values used for the 'uid' and 'pwd' parameters in the previous line

Once this is done data is accessed through the SQL Server “OpenQuery()” function. In theory linked servers support the 4 part naming convention for sql , however it appears that the 3.511 version of the MySQL ODBC driver does not. So the “OpenQuery” function is the only way of accessing the MySQL data from within SQL server.

The “OpenQuery” function can be used to create views so that the fact that the data is from a linked server is transparent to the users.

Create view MAP_ULOADC as

select u.* from openquery(MAPMYSQL, 'select * from ULOADC') as u

go

Be aware that in the MAP dataset the following tables have blob or tinyblob fields and views for them should be created by explicitly selecting each column and casting the blob column as char

Table blob column

COMTRN body

LABELS body

LBMISC misc_data

SCHCOM body

XLATEP xl_body

XLDEFN body

The example below shows how this is done with the XLATEP table.

create view dbo.MAP_XLATEP as

select x.* from openquery(MAPMYSQL,

'SELECT xl_prefix, xl_key,

cast(xl_body as char) as xl_body,

xl_system, xl_group

FROM XLATEP') as x

Linked Server Name Parameter

A parameter called “Linked Server Name” has been added to the section “Report Parameters” under the Inf Client application in ESP. This is to allow individual sites the freedom to name their linked servers however they wish.

The code below shows how the linked server name can be retreived from the parameter table then used to build an openquery sql statement.

declare @linkedservername nvarchar(50)

--get the linked server name

select @linkedservername = isnull(value, 'MAPMYSQL') from infparameter where name = 'Linked Server Name' and sectionid in (select id from infsection where name = 'Report Parameters')

--build the sql statement using the retrieved linked server name

set @sql = 'declare qcurs cursor for select isnull(qty,0) from openquery(' + @linkedservername +

',''select sum(quantity_good_out) as qty from FACTRY where job_number = ''''' + @job +

''''' and machine_number in (1180,2180,3180)

and finish_datetime < ''''' + convert(nvarchar(30),@dte,120) +

''''' group by job_number'')'

-- now exec the statement

exec(@sql)

This functionality can be used in stored procedures but not in SQL functions or Views.

Kiwiplan - MAP Variables

Kiwiplan software uses several environmental variables within the Unix structure. 

Knowing these variables will provide a better understanding of the directory structure during setup of Kiwiplan software.

These variable settings are located in the files:

$HOME/.profile à /KIWI/site/bin/profile.?????

/KIWI/site/bin/stdprofile

$DATA/PROFILE or $WORK/PROFILE

Environment variables point to individual directories and give a shortcut to the Kiwiplan software.

Below is a listing of all directory variables.

{DRIVE}             = any location                :folder or drive location

$KIWI                 = /{drive}/kiwi               :contains the kiwi environment

$REV                   = /KIWI/revisions

$COMMSDIR     = /KIWI/work/cscomm  :contains comms logs & audit trails

$PLANTBIN       = /KIWI/site/00xx/bin   :contains plant specific menu scripts

$SITEBIN            = /KIWI/site/bin             :contains site specific menu scripts

$PLANTDAT       = /KIWI/site/dat             :contains plant .MNU, .TOG files

$SITEDAT           = /KIWI/site/dat             :contains .MNU, .TOG files

$DATA      ISAM= /KIWI/data_a              :contains revision data files & indexes

$DATA      SQL  = /KIWI/site_sql             :contains revision data files

$WORK              = /KIWI/usr/work          :contains all user .TM and .LS files

$SCP                    = /KIWI/kiwi/scp           :contains revision specific scripts

$PROGS              = /KIWI/kiwi/progs       :contains all program files for revision

$BIN                    = /KIWI/kiwi/bin           :contains unix specific scripts

$UFD                    = /KIWI/kiwi/ufd           :contains data file definitions

$TOOLS             = /KIWI/site/tools          :contains admin tools & menus

$SCRIPTS           = /KIWI/site/SCRIPTS :contains homemade scripts

 

NOTE:  Some sites use a different base directory for the Kiwiplan software.

Examples: $KIWI may be /usr/kiwi or /kiwi/kiwi.

 

Environmental variables are used to set up the user’s environment, meaning which data, menu, etc… they access when logging in.  Below is a list of basic environment variables.

EXEC                  = executes a program or script, which in turn will call a specific .MNU file.

                             Example:  ”kiwimenu menu=csc00”

 

DATA                  = which data the user will access when logging in.

                             Example:  data_a or site_sql

 

 

TERM                 = what terminal type the user will default to when logging in.

                             Example:  vt220, vt100, & ansi

 

KIWISEA            = the search path for Kiwiplan environment.

 

UNIX PATH        = the UNIX executing search path.

 

To view what each variable is set to, use the echo command.  Example= echo $TERM will tell what terminal emulation is being used.  Below is a directory tree layout to better show the variable locations and directory contents.

Kiwiplan - Add Printer for Classic and Refresh Sites

This is how to add a printer in Kiwiplan for KDG to host the printer and for classic label.

Step1:

Add the printer to the server were KDG is running under Control Panel\Hardware\Devices and Printers

Step2:

Verify in KDG top make sure it shows up

Step3:

Once the printer is available you have to add a printer to the PRINTERS file on the Linux server 

You can use the locate command to find out where the file is located

locate PRINTERS

This is what the file would/should look like

#Other ##Nul Printer LPT90:TEMPLATE:LEGAL LPT90:SCRIPT:print_kdg   ##Non-Physical Printers LPT93:SCRIPT:print_email LPT97:SCRIPT:pgprint LPT98:SCRIPT:saveprint LPT99:SCRIPT:noprint   LPT100:SCRIPT:print_email LPT100:PRINTER:sales@sflservicesllc.com

To add in the new HP_Office printer we just added, in the example we will add the printer 1 ( 1 will be used in the classic environment ) but in PCS Vue the name of the printer will show.

The options are LETTER and LEGAL for the template

The option for SCRIPT the following options are available

print_kdg means it will send the request via KDG

print_email means it will email you the classic report and as text and a PDF

pgprint means it will display the result on screen and you will be able to page through them

saveprint means it will save the file to drive and keep it as a SAVE_?????.LS file

noprint means it will just not print all all

#Office Printers
LPT1:TEMPLATE:LETTER
LPT1:SCRIPT:print_kdg

#Other
##Nul Printer
LPT90:TEMPLATE:LEGAL
LPT90:SCRIPT:print_kdg

##Non-Physical Printers
LPT93:SCRIPT:print_email

LPT97:SCRIPT:pgprint
LPT98:SCRIPT:saveprint
LPT99:SCRIPT:noprint

LPT100:SCRIPT:print_email
LPT100:PRINTER:sales@sflservicesllc.com

Step4:

Still on the Linux side you will need to associate the printer to number you just created to the KDG side you will need to run the following command

kdgprint

Or you can also do this via the parameters section call SYS:KP, but I recommend using the above command.

Once you run the command you can use a filter if you have man printers or just leave it blank

kdgprint                Assign KDG Printers utility                 26/Thu 11:17 ================================================================================   Apply filter to server list: ..............................

kdgprint                Assign KDG Printers utility                 26/Thu 11:18 ================================================================================ Total Printers on server:4                                        Page 1   /1 Indx KDG_Printer_name                   Print_Tray              LegacyPrinters    1 HP_Office    2 Microsoft Print to PDF                                             3 Microsoft XPS Document Writer                                             4 nul                                          (C)hange, (F)ind, (E)xit, or select Index ? E....

As you enter through the Print_Tray and enter 1 for the Legacy printer you may get an error if an old printer was delete and the parameters section still exists

? Printer number already assigned :Office_CustomerService

You will need to go to the paramters sectip of SYS:KP and delete the old printer

If not you can add the 1 to the LegacyPrinters, also this will allow you to add more then one printer number to the same printer which is useful if a printer goes down.

kdgprint                Assign KDG Printers utility                 26/Thu 11:22 ================================================================================ Total Printers on server:4                                        Page 1   /1 Indx KDG_Printer_name                   Print_Tray              LegacyPrinters    1 HP_Office                                                                                         1        2 Microsoft Print to PDF                                             3 Microsoft XPS Document Writer                                             4 nul                                       (C)hange, (F)ind, (E)xit, or select Index ? E....

Kiwiplan - MySQL log is fill or filled the drive

This document is to explain the procedure on how to get the plant operational again.

Couple of things to think about:

1. Is the drive full
2. Do you see many log files for mariadB or MySQL
   1. If so then proceed on delete the oldest to the newest date and the onse with a .gz extention
      1. Then investigate the growing log to figure out what is adding to the logs
   2. If not then go to step one below

Steps:

1. Stop all xmgenmenu links
2. Stop any other services running on the linux server
   1. For example PrePrint service (kprt-service.sh -stop)
   2. Check to see all have been stopped by running "ps -ef | grep xmgen"
   3. If any are still running use "kill -9 (id)"
3. Turn off Java Services in $VBIN
   1. ./stopservers.sh
4. Go to ESP and turn off agents for that plant
5. Once everything is down, then get into root (sudo su)
   1. "systemctl stop mariadb"
   2. cd /var/log/mariadb/
   3. Verify the mariadb.log file is no longer growing (couple ls -lart)
   4. rm mariadb.log … "yes"
   5. "systemctl restart mariadb"
   6. Then turn on all xmgenmenu links
      1. followed by any plant specific service like PrePrint (kprt-service.sh -start)
6. Turn on Java services
7. Go back into ESP and start Agents for that plant

Kiwiplan - Install Web Launcher

This requires Java to be installed first

 
 

Script - Deleting a KDE server from the Database

DECLARE @ServerID INT, @UPD INT

SET @ServerID	= 1 --Server ID To Delete
SET @UPD		= 0 --0 to Select 1 to Delete

-- DOT NOT MODIFY BELOW --
IF @UPD != 1
BEGIN
	SELECT * FROM [KDG].[dbo].[kdgDelivery] where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].[kdgDeliveryResource] where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgDocument where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgEmailDelivery where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgEmailRecipient where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgPrintDelivery where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgRequest where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgSaveToDiskDelivery where serverID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgServer where ID = @ServerID
	SELECT * FROM [KDG].[dbo].kdgServerConfig where serverID = @ServerID
END

IF @UPD = 1
BEGIN
	DELETE FROM [KDG].[dbo].[kdgDelivery] where serverID = @ServerID
	DELETE FROM [KDG].[dbo].[kdgDeliveryResource] where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgDocument where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgEmailDelivery where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgEmailRecipient where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgPrintDelivery where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgRequest where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgSaveToDiskDelivery where serverID = @ServerID
	DELETE FROM [KDG].[dbo].kdgServer where ID = @ServerID
	DELETE FROM [KDG].[dbo].kdgServerConfig where serverID = @ServerID
END

Kiwiplan - KIDSENV Settings

MAXPROC=999

TERM=vt100

KIDSLOGDIR=$PLANT/tmp
KIDSLOGMAX=1000000
KIDSLOGLEVEL=-4

The KWSQL_LOG variable is a system-level setting in Kiwiplan that controls logging options for the SQL interface, although ISAM can also utilize these logs. It allows you to specify the log level, which can be one of the following:

• all: Logs everything.
• error: Logs only errors.
• warn: Logs warnings and errors.
• query: Logs queries, warnings, and errors.
• info: Logs informational messages.
• none: Disables logging entirely.

For example, setting the log level to error will only display errors, while setting it to query will display queries, warnings, and errors
KWSQL_LOG=query

The KWLOCK_TIME_LOG variable is a system-level diagnostic setting in Kiwiplan. It causes the application to abort if a lock remains active for a specified number of seconds
KWLOCK_TIME_LOG=15

The KWLOCK_TIME_COUNT variable is a system-level diagnostic setting in Kiwiplan. It causes the application to abort if a lock remains active for a specified number of iteration counts
KWLOCK_TIME_COUNT=15

The KIWI_NO_CATCH_HUP variable is a system-level setting in Kiwiplan that turns off the catching of HUP (hangup) signals. This means the application will not handle or respond to HUP signals when this variable is specified. This one if for MDC that  keeps shutting down
export KIWI_NO_CATCH_HUP=1

The KIWI_LOCK_NOPID variable is a system-level setting in Kiwiplan that forces the process ID to be written to the lock file when a process locks a file. However, it comes with a warning that setting this variable can affect performance. This one is for the lock files
export KIWI_LOCK_NOPID=1
GEN/IN use lock monitor N

Kiwiplan - Server Load Monitor

This script is based on this initial loadmon script

This is to monitor specific objects at a Advantive/Kiwiplan sites

It will send something like this for PCS

#!/bin/bash
############################################
#
# Author: Steve Ling 5/2/25
# Modified:
#    Added PCS Scheduler looping monitoring 8/14/25
#    Added PCS table record counts 8/14/25
#
# Purpose: Monitor system metrics including:
# - Average Load
# - CPU Usage
# - Disk Space
# - Memory Usage
# - MySQL Table Record Counts (multiple tables with individual thresholds)
#
# Installation:
# 1. Copy to /opt/scripts/
# 2. Set permissions: chmod 755 /opt/scripts/loadmon.sh
# 3. Set ownership: chown remuser:kiwiplan /opt/scripts/loadmon.sh
# 4. Add cron job as root to run every 5 minutes:
#   To supply parameters or not:
#       */5 * * * * /opt/scripts/loadmon.sh >/dev/null 2>&1 | logger
#       */5 * * * * root MYSQL_USER="your_user" MYSQL_PASSWORD="your_password" MYSQL_DATABASE="your_db" MYSQL_TABLES_AND_THRESHOLDS="users:50000,orders:100000" /opt/scripts/loadmon.sh >/dev/null 2>&1 | logger
#       */5 * * * * root MYSQL_USER="your_user" MYSQL_PASSWORD="your_password" /opt/scripts/loadmon.sh >/dev/null 2>&1 | logger
#
# MySQL Configuration:
# Set the following environment variables:
# - MYSQL_HOST: MySQL host (default: localhost)
# - MYSQL_USER: MySQL username
# - MYSQL_PASSWORD: MySQL password
# - MYSQL_PCS_DATABASE: PCS Database name
# - MYSQL_TABLES_AND_THRESHOLDS: Comma-separated list of table:threshold pairs
#   (e.g., "table1:50000,table2:100000,table3"). If threshold is omitted, uses MYSQL_RECORD_THRESHOLD.
# - MYSQL_PCS_RECORD_THRESHOLD: Default record count threshold
#
# Improvements:
# - Added support for multiple MySQL tables with individual thresholds
# - Sends individual alerts for each table exceeding its threshold
# - Added error handling for MySQL commands
# - Improved logging with timestamps
# - Made thresholds configurable via environment variables
# - Added hostname to alerts for clarity
# - Replaced mutt with mail (more common)
# - Optimized command execution
# - Added input validation
#
############################################

# Uncomment to "Exit" the script on any error
#set -e

# Configuration (can be overridden via environment variables)
: "${LOAD_THRESHOLD:=10.00}"       # Load average threshold
: "${DISK_THRESHOLD:=85}"          # Disk usage threshold (%)
: "${CPU_THRESHOLD:=65}"           # CPU usage threshold (%)
: "${MEM_THRESHOLD:=85}"           # Memory usage threshold (%)
: "${MYSQL_RECORD_THRESHOLD:=100000}" # Default MySQL table record count threshold
: "${MYSQL_PCS_RECORD_THRESHOLD:=100000}" # Default PCS Scheduler table record count threshold
: "${RECIPIENTS:=steve.ling@sflservicesllc.com}" # Space-separated email addresses
: "${HOSTNAME:=$(hostname -s)}"    # Short hostname for alerts
: "${LOG_FILE:=/var/log/loadmon.log}" # Log file location
: "${MYSQL_HOST:=localhost}"        # MySQL host
: "${MYSQL_USER:=}"                # MySQL username
: "${MYSQL_PASSWORD:=}"            # MySQL password
: "${MYSQL_PCS_DATABASE:=ccc_pcs}"     # MySQL database prefix
: "${MYSQL_TABLES_AND_THRESHOLDS:=schedulemachineavailability:1000000}" # Comma-separated table:threshold pairs

# Ensure required commands are available
for cmd in awk df top free mail logger mysql sed tr bc; do
    if ! command -v "$cmd" &>/dev/null; then
        echo "Error: Required command '$cmd' not found" | logger -t loadmon
        exit 1
    fi
done

# Function to log messages with timestamp
log_message() {
    local message="$1"
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $message" | logger -t loadmon
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $message" >> "$LOG_FILE"
}

# Function to send email alerts
send_alert() {
    local subject="$1"
    local body="$2"
    if echo -e "$body" | mail -s "$subject" $RECIPIENTS 2>/dev/null; then
        log_message "Alert sent: $subject"
    else
        log_message "Error: Failed to send alert: $subject"
    fi
}

# Collect system metrics
load=$(awk '{print $1}' /proc/loadavg 2>/dev/null || log_message "Error: Failed to read load average")
disk_usage=$(df / | awk 'NR==2 {print $5}' | sed 's/%//' 2>/dev/null || log_message "Error: Failed to read disk usage")
cpu_usage=$(top -bn1 | grep "Cpu(s)" | awk '{print 100 - $8}' | cut -d. -f1 2>/dev/null || log_message "Error: Failed to read CPU usage")
mem_usage=$(free | awk '/Mem:/ {printf "%.0f", $3/$2 * 100}' 2>/dev/null || log_message "Error: Failed to read memory usage")

# Validate collected system metrics
if ! [[ "$load" =~ ^[0-9]+([.][0-9]+)?$ ]]; then
    log_message "Error: Invalid load value: $load"
    exit 1
fi
if ! [[ "$disk_usage" =~ ^[0-9]+$ ]]; then
    log_message "Error: Invalid disk usage value: $disk_usage"
    exit 1
fi
if ! [[ "$cpu_usage" =~ ^[0-9]+$ ]]; then
    log_message "Error: Invalid CPU usage value: $cpu_usage"
    exit 1
fi
if ! [[ "$mem_usage" =~ ^[0-9]+$ ]]; then
    log_message "Error: Invalid memory usage value: $mem_usage"
    exit 1
fi

# Collect MySQL table record counts (if configured)
mysql_records_summary=""
if [ -n "$MYSQL_USER" ] && [ -n "$MYSQL_PCS_DATABASE" ] && [ -n "$MYSQL_TABLES_AND_THRESHOLDS" ]; then
    IFS=',' read -r -a table_pairs <<< "$MYSQL_TABLES_AND_THRESHOLDS"
    for pair in "${table_pairs[@]}"; do
        pair=$(echo "$pair" | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')  # Trim whitespace
        table="${pair%%:*}"  # Extract table name
        threshold="${pair#*:}"  # Extract threshold (if provided)
        
        # Use default threshold if not specified or invalid
        if [ -z "$threshold" ] || ! [[ "$threshold" =~ ^[0-9]+$ ]]; then
            threshold="$MYSQL_PCS_RECORD_THRESHOLD"
            log_message "Warning: Using default threshold ($threshold) for table $table"
        fi

        # Validate table name (basic check for non-empty and no special characters)
        if [ -z "$table" ] || [[ "$table" =~ [^a-zA-Z0-9_] ]]; then
            log_message "Error: Invalid table name: $table"
            continue
        fi

        # Query record count
        record_count=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -D "$MYSQL_PCS_DATABASE" -N -e "SELECT COUNT(*) FROM \`$table\`" 2>/dev/null) || {
            log_message "Error: Failed to query MySQL table $MYSQL_PCS_DATABASE.$table"
            record_count=0
        }

        # Validate record count
        if ! [[ "$record_count" =~ ^[0-9]+$ ]]; then
            log_message "Error: Invalid record count for table $table: $record_count"
            record_count=0
        fi

        # Append to summary
        mysql_records_summary="$mysql_records_summary $table:$record_count"

        # Check threshold and send alert if exceeded
        if [ "$record_count" -gt "$threshold" ]; then
            body="Record count in $MYSQL_PCS_DATABASE.$table: $record_count\nThreshold: $threshold"
            send_alert "High MySQL record count on $HOSTNAME - $table [ $record_count ]" "$body"
        fi
    done
    mysql_records_summary=$(echo "$mysql_records_summary" | sed 's/^ //')  # Trim leading space
else
    log_message "Warning: MySQL configuration incomplete (USER, DATABASE, or TABLES_AND_THRESHOLDS missing)"
    mysql_records_summary="N/A"
fi

# Collect PCS Scheduler record count
mysql_pcs_record_count=0
if [ -n "$MYSQL_USER" ] && [ -n "$MYSQL_PCS_DATABASE" ]; then
    mysql_pcs_record_count=$(mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -D "$MYSQL_PCS_DATABASE" -N -e "SELECT COUNT(*) FROM schedulemachineavailability WHERE pcsSchedule NOT IN (SELECT pcsSchedule FROM schedulerun)" 2>/dev/null) || {
    log_message "Error: Failed to query PCS Scheduler table $MYSQL_PCS_DATABASE"
    mysql_pcs_record_count=0
    }
else
    log_message "Warning: PCS Scheduler configuration incomplete (USER or DATABASE missing)"
fi

# Validate collected system metric
if ! [[ "$mysql_pcs_record_count" =~ ^[0-9]+$ ]]; then
    log_message "Error: Invalid PCS Scheduler record count: $mysql_pcs_record_count"
    mysql_pcs_record_count=0
fi

# Check thresholds and send alerts for system metrics
if (( $(echo "$load > $LOAD_THRESHOLD" | bc -l) )); then
    body=$(sar -q 2>/dev/null || echo "Error collecting sar data")
    send_alert "High load on $HOSTNAME - [ $load ]" "$body"
fi

if (( disk_usage > DISK_THRESHOLD )); then
    body=$(df -h / 2>/dev/null || echo "Error collecting df data")
    send_alert "High disk usage on $HOSTNAME - [ ${disk_usage}% ]" "$body"
fi

if (( cpu_usage > CPU_THRESHOLD )); then
    body=$(top -bn1 | head -n 12 2>/dev/null || echo "Error collecting top data")
    send_alert "High CPU usage on $HOSTNAME - [ ${cpu_usage}% ]" "$body"
fi

if (( mem_usage > MEM_THRESHOLD )); then
    body=$(free -h 2>/dev/null || echo "Error collecting free data")
    send_alert "High memory usage on $HOSTNAME - [ ${mem_usage}% ]" "$body"
fi

if [ "$mysql_pcs_record_count" -gt "$MYSQL_PCS_RECORD_THRESHOLD" ]; then
    body="PCS VUE Scheduler has an issue\nTable schedulemachineavailability has $mysql_pcs_record_count\n\nWhich is more records then the number of ran schedules in the schedulerun table"
    send_alert "PCS VUE Scheduler record count on $HOSTNAME - [ $mysql_pcs_record_count ]" "$body"
fi

log_message "Monitoring completed: Load=$load, Disk=${disk_usage}%, CPU=${cpu_usage}%, Mem=${mem_usage}%, PCS Scheduler Count=${mysql_pcs_record_count}, MySQL Records=[$mysql_records_summary]"

MySQL - Parameters Explained

performance_schema = ON
tmpdir = /run/mariadb
thread_cache_size = 4
table_open_cache = 16384
table_definition_cache = 8384
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 128M
query_cache_strip_comments = 1
 
tmp_table_size = 512M
max_heap_table_size = 512M
 
max_connections = 750
max_allowed_packet = 24M
sort_buffer_size = 24M
join_buffer_size = 48M

innodb_buffer_pool_size = 65G		# 65-75% of RAM for InnoDB cache --> Keeping some for VUE
innodb_buffer_pool_instances = 10	# One instance per CPU core --> Keeping some for VUE
innodb_flush_method = O_DIRECT		# Reduce I/O overhead
innodb_flush_log_at_trx_commit = 2	# Balance performance and durability
innodb_log_buffer_size = 16M		# Buffer for transaction logs
innodb_thread_concurrency = 0		# Let MySQL manage threads (0 = unlimited)
innodb_io_capacity = 2000		# Adjust based on storage IOPS
innodb_io_capacity_max = 4000		# Max IOPS for bursts
innodb_use_native_aio = 1
innodb_flush_log_at_trx_commit = 2	# Balance performance and durability --> Changed from 0
innodb_file_per_table
innodb_log_file_size = 2G		# Larger logs for better write performance --> Changed from 512

 

Dataset - Restrict Date in MAP MES VUE ESP

This is to freeze a datasets for different Kiwiplan modules.

MAP / CLASSIC / FFF

Create a file named [PROFILE] in the KIWISEA path. The best place to put it is in the $DATA folder. The DATA and TIME are the settings you wish to freeze the data at.

RESTRICT:
DATE:160725
TIME:0700

VUE

There are a couple of places you will need to have a file named [time.properties] and you will need to create and save a file in each folder with the following entry:

time=16/07/2025 07:00:00

Linux Server

/KIWI/services/sites/<sitename>/current/conf/kiwiplan
/KIWI/services/web/<sitename>/current/conf/kiwiplan

Windows Server (Client Install)

C:\Kiwiplan\<install folder>\conf\kiwiplan

ESP

Create a file named [PROFILE.INI] to hold the database you wish to freeze in time with the following entry:

[SFL]
StartDateTime=07/16/2025 07:00:00
TimeZone=US Eastern Standard Time
LocaleID=1033

[DBLABELMAPPING]
ESP_LIVE=SFL

The sections are set like the folowing:

[DBLABELMAPPING] example
DBNAME=SECTION_NAME

[TimeZone] examples
US Eastern Standard Time
Central Standard Time
GMT Standard Time
New Zealand Standard Time

[LocaleID] code page example
1033
1062
5129

Windows Server (ESP Installation)

C:\Kiwiplan\EspServ\

 

Kiwiplan - Setup ULT for ESP

Parameter settings

SYS MO

Load Docket Program Allowed=Y
Allow FG tracking=1 
Allow WP tracking=1

GEN ED

#2=*
#3=16 (site dependent)
#4= #

GEN EE

ULTDLD
 1      Data Type                      ULTDLD
 2      Description                    Docket Export
 3      Directory Name                 ./  or path to /ultdld
 4      Last File Number Used          1
 5      Script name for File Export    EspRenameDkt
 6      Fixed record length 0=variable 0
 7      File Name (optional)           
 8      Layout Version  <

ULTUNIT
1      Data Type                      ULTUNIT
 2      Description                    ULT Unit movement for ESP
 3      Directory Name                 ./  or path to ultunit
 4      Last File Number Used          1
 5      Script name for File Export    
 6      Fixed record length 0=variable  0
 7      File Name (optional)           unitmvmt.edi
 8      Layout Version                 S3

GEN PP
ULT
 1      Key                            ULT
 2      Produce filefull before purge  N
 3      Purge orders daily (Y/N)       Y
 4      Purge orders weekly (0-7) 0=No 0
 5      Number of days orders to keep  120
11      Purge history daily (Y/N)      Y
12      Purge history weekly (0-7)0=No 0
13      Number of days history to keep 120

WIP
 1      Key                            WIP
 2      Produce filefull before purge  N
 3      Purge orders daily (Y/N)       Y
 4      Purge orders weekly (0-7) 0=No 0
 5      Number of days orders to keep  120
11      Purge history daily (Y/N)      Y
12      Purge history weekly (0-7)0=No 0
13      Number of days history to keep  120

GEN PQ
ULT
 1      System Name (Key)              ULT
 2      Name of purge program to run   ultprg
11-14     Ords-WARN no purge in N days   10   (all 3 params)

WIP
 1      System Name (Key)              WIP
 2      Name of purge program to run   ultprg

GEN PL
 Example machines
 4      Default Corrugator Machine No. 1,011
 5      Default Despatch Machine No.   8,000
 6      Board Transfer Machine Number  9,999
 8      Pick-Up Goods Machine Number   9,997
 9      Quarantine Machine Number      9,998
10      FGS Rework Store Machine No.   8,007
11      Strapper Triggering Notifictn  7,999
12      Printer No for Notifications   99
13      Notification Label Code
16      Sheetboard strapper number     7,998
18      Supply From Stock Machine No   1,000
21      Into WIP Default Label Format  0A
22      Into FGS Default Label Format  01
27      Stock Despatch Machine No      8,008
29      Default Board Receiving Mch No 1,001
37      Surplus Sheetboard Machine No. 8,006

GEN TU
Set up all pallet types (must match ESP)
1      Unit Load Type                 01
 2      Description                    40X48, 
 3      Width                             40.00 
 4      Length                            48.00 
 5      Default load height      52.00 
 7      Absolute Length Overhang       8.00  
 8      Absolute Width Overhang        8.00 
12      Thickness                      5.00  
28      Weight of Pallet               40

INV DL
  Maximum value for load Id      64,000
 Maximum Unit Loads per truck=500
                         Allow File Export=Y 
                         One file per Despatch Load = N
                     BOL Sequence = 0
                         Qty subtotals in BOL report = N
                         BOL totals only = N
                         Job pages in BOL report=N
                     BOL last number  = 1 ( as a starting point)
                     BOL maximum value 99,999,,999
                         Ignore Plant check as planning=Y
                         Ignore mach. check as planning=Y
                         Prompt for ship method = Y
                         Don’t prompt to enter Truck Id =N
                         When set Load to “Despatch”
                            Check Label-Only Units = N
                         Generate BOL printout=N
                         BOL printout only if Transf.=N (use ESP transfer type docket)
                         Number of copies of BOL printout = 0
                         Generate BOL export (Billing) = Y
                       Accept before Export/Ship    Y
                       Enter Despatch Comment       1     (added for seal numbers/dock time appts)     
 When set Load to Shipped” 
         Generate DLBOL  export = Y
                         Include in export transfer = Y (use ESP ‘transfer’ type docket)
                         Always transfer jobs of type 3 = Y
                         Update PCS when shipping loads = Y  
                         Update PCS with Xfered Units = Y
                         Skip Board Transfers in ULT= N
                         Split BOL per Address No. = Y
                         Allow entry of BOLJob Weight = N
                         Show Label units in FastTrack = N
                         Exclude Special Inst. From BOL=Y
                        Status Partial if more series  Y
         No new series for Call-Off Rtn N
          Fast-Track: Auto assign L-Bay  N  (Yes would use any free Dock Door)
   Fast-Track: Select Unit by:
         0=Min Splits, 1=FIFO          1
     Allow loading from diff. store N  (allows load, unload & relocate to a different store)
   No upper casing customer names Y  (fixes customer search in BDR report)
   Select CallOff UL on Qty Split Y  (helps use the FIFO selection process on fasttrack)
    Auto select up to Overrun Qty  N ( For type 0 orders only, Yes where it is not OK to over deliver, else all units including overrun are selected)
Allow Misc Items on ULT Loads = N
Prefix for Misc. Items       =   MISC
Assign Stop No in Reverse Seqn =  N
Freight Code for RollStock = 
Flag full load at X units  =     0
Flag full load at X weight   =   0
Single Ship Method for Load  =    N  (consolidates to first orders ship method if different)
Allow to correct shipped loads N  (new to prevent unshipping when ESP has status ??)
Correct Shipped loads if:
Job Status in Load is <=     1  (0 No invoice created, 1 Inv cancelled, 2 Inv created, 3 Inv printed, 4 Inv posted, 5 error, 6 unknown)
Invoice Check:Default ShipCode =  ( used for generic ship methods, ie. CC,  when user should have changed ship method before exporting load details to ESP)

INV UT
        Unit Load Delimiter =  -
          Unit Load Step Delimiter = W
          Unit Load Qty. Delimiter = (blank)
          Unit Load String Delimiter = (blank)
          Default Pallet Type   (must match a valid GEN TU or blank is fine)         
          Xfer store name as customer  =   Y  
           New unit barcode field format
             0=Plant Code/Unique#, 1=SSCC  =  0
           Allow 0 Pallets on a Unit Load  =  Y 
           Auto Move WIP Units to InFeed = Y
           Auto Consume Entry WIP Units =  Y
           No check Unprocessed Exit Uls = Y
           Allow Add for jobs not in PCS =  N
           Method to calc unit height = (blank)
           Don't export WIP unit changes  = Y
           Search only for a selected job= N
Skip Purge of Into Store Mchs = N
Board Xfer integrate with ULT  = Y if on Rev 8.10 April 2015 or newer

PCS FB
                   Default WIP Label Format (site specific)  Ex: WW
                    Default FGS Label Format (site specific) Ex: WF
                    Print WIP labels from Feedback = Y
                    Print from (H) Mach Lineup = Y
                        Print from (F) Mach Lineup = Y
                        Estimated WIP Labels 1 less = Y
                 Prevent updating of non-contiguous feedback = Y                   
                   Only 1 In-Process job allowed = Y
                    Qty/Unit to keep default value = Y

PCS RP
                    Standard Truck Volume = 1,750  (metric 70)
                       Default Hours Until Ready = 24
                       Blank line on Sort Key change = 1
                       Full Order Status from DPR = Y
                       Chg. Load Status when Add Load = Y

PCS QO
Order Status
 2      Keys = 1, 3, 14,  + any soft Hold Keys
                             Despatch Plan Report Selected =  Y

PCS XF
FG transfers
1      Shipping Machine for customer  7995 op 28 machine
 2      Destination ULT Transfer Store 11
 
 3      Customer Name                 WH11 Name
 4      Address Number                 0
 5      Delivery Destination Code      WH11
 6      Ship Method                        (blank is fine on all)
 7      Customer Key                      (blank is fine on all)
 8      Carrier                                  (blank is fine on all)
 Next Example:
 1      Shipping Machine for customer  7995 op 28 machine
 2      Destination ULT Transfer Store 12
 
 3      Customer Name                WH12 Name
 4      Address Number                 0
 5      Delivery Destination Code      WH12

Etc.

WP transfers
1      Shipping Machine for customer  7995  op 28 machine
 2      Destination ULT Transfer Store 101
 
 3      Customer Name                  OP101 Name
 4      Address Number                 0
 5      Delivery Destination Code      W101
 Next example:
 1      Shipping Machine for customer   7995  op 28 machine
 2      Destination ULT Transfer Store 102
                                       
 3      Customer Name                  OP102 Name
 4      Address Number                 0
 5      Delivery Destination Code      W102

Etc.

Possible order routes
 
Sheets
Stp          Machine
1.    Corrugator
2.    Strapper
3.    Shipping

Farm-out
    Stp          Machine
1.    Corrugator
2.    Diecutter
3.    Transfer out
4.    Outside process
5.    Transfer in  (optional)
6.    Strapper
7.    Shipping

Purchase sheets (farm-in)
  Stp          Machine
1.    Purchase Product (sheets)  (op 17)
2.    Diecutter
3.    Strapper
4.    Shipping

Purchase Finished product 
Stp          Machine
1.    Purchase Product (Finished product)  (op 17)
2.    Transfer In  (op 29)
3.    Warehouse  (op 20/30)

Returns
    Stp          Machine
1.    Return-Pickup Goods  (op 20)
2.    Quarantine/Rework  (op 20 & 30)

Into Warehouse on site
    Stp          Machine
1.    Corrugator
2.    Diecutter
3.    Strapper
4.    Warehouse Stock (op 20 & 30)

Into Warehouse off site
    Stp          Machine
1.    Corrugator
2.    Diecutter
3.    Strapper
4.    Transfer Out  (op 28)
5.    Warehouse Stock (op 20 & 30)

Warehouse Release (Calloffs)
    Stp          Machine
1.    Warehouse Release From Stock  (no ops)
2.    Shipping (op 20)

Stock Transfers
    Stp          Machine
1.    Warehouse Release from stock (no ops)  (FG store= blank)
2.    Transfer Out (op 28)
3.    Warehouse Stock (op 20 & 30)

Stores and Locations

Main Store

Store              10          
( 2) Description       MAIN FINISHED GOODS STORE 
 If one ESP database and more than one MAP database where plants are using same warehouse or cross warehousing between plants,  then put plant names in description.  ESP drop down will see description like:
Cor Main FG Store (10)       and Paso Main FG Store (20)
CorAlliWHSE (151)               and PasoAlliWHSE (151)
CorPasoWHSE (20)              and PasoCor WHSE (10)
 ( 3) Plant Code        1     
( 4) Allowed Types     FG WP               
(15) One Location      N                   
(16) Def Recv Locatn   STRAP
(17) Off-line                 0
(19) Show only at fixed store N

Location types

ENTRY    
( 1) Description    Default Receiving 
( 2) Default Type  FG WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

ST001       
( 1) Description    Storage 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

QUAR      
( 1) Description    Quarantine
( 2) Default Type  FG  WP
( 3) Quarantine location                                 Y
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

REWORK        
( 1) Description    Rework
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

DD01        
( 1) Description    Loading Dock Door 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        2
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

M####      
( 1) Description    Individual Machines 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        0
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

STRAP
( 1) Description    Default Warehouse Receiving
( 2) Default Type  FG WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        0
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

Finished Good Warehouses

Store              11          
( 2) Description       Warehouse 11
( 3) Plant Code        1     
( 4) Allowed Types     FG WP               
(15) One Location      N                   
(16) Def Recv Locatn   INTRAN
(17) Off-line                 0
(19) Show only at fixed store N

Location types

INTRAN    
( 1) Description    Default Receiving 
( 2) Default Type  FG WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        0
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

ST001       
( 1) Description    Storage 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
 ( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

QUAR      
( 1) Description    Quarantine
( 2) Default Type  FG  WP
( 3) Quarantine location                                 Y
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

DD01   - With Scanning
 ( 1) Description    Loading Dock Door 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        2
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

DD01  - Without scanning
 ( 1) Description    Loading Dock Door 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        2
( 6) Fast Track Loading Bay                              Y
( 7) Units from WIP label to be Available/Stock          N

Outside Farmout Warehouses

Store              101          
( 2) Description       Warehouse 101
( 3) Plant Code        1     
( 4) Allowed Types     FG WP               
(15) One Location      N                   
(16) Def Recv Locatn   INTRAN
(17) Off-line                 0
(19) Show only at fixed store N

Location types

INTRAN    
( 1) Description    Default Receiving 
( 2) Default Type  FG WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        0
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

ST001       
( 1) Description    Storage 
( 2) Default Type  FG  WP
( 3) Quarantine location                                 N
( 5) 0-Enter,1-Store,2-Exit Unit Loads,3-Pre-Load        1
( 6) Fast Track Loading Bay                              N
( 7) Units from WIP label to be Available/Stock          N

Consignments
Location types
Not Required
KDG Label Formats

Create label formats for Kiwi programs CORR (C=Type), PCS (W=Type) & ULT (F=type)
Specific customer FG formats passed from Host Systems should also be created.
Finished Goods Unit
                          Type            Fields
01    STD LOGO (CONV/PCS/FFF)        W - Work in Progress     2502 & 1047 = Y
11    STD LOGO (CORR)                         C - Corrugator                 2502 & 1047 = Y
21    STD LOGO (FGS/ULT)                    F - Finished Goods         2502 & 1047 =N
Work in Process Unit
                          Type               Fields
0A    WIP TO WIP (CONV/PCS/FFF)   W - Work in Progress   2503 & 1047 = Y
1A    WIP TO WIP (CORR)                   C  – Corrugator               2503 & 1047 = Y
2A    WIP TO WIP (FGS/ULT)              F - Finished Goods         2503 & 1047 = N
  
KDG Parameters 

SYS  -   KG    Kiwiplan Document Generator        
 1      KEY                            PARAMS
 2      KDGServer    fill in the server name
 3      Port                           50,123
 4      User Name Login
 5      User Password
 6      Legacy KDG Folder              legacy

SYS  -    KP    KDG PrinterQueue   
1      Printer number                  23
2      KDG PrinterQueue               PR######
3      KDG PrinterTray  

Dispatch Planning Report

PCS TD 
Regular delivery loads
Several can be setup in the PCS/TD parameters.  
Example of Dispatch Shipping Report:
pcsmenu:AME    Despatch Planning Report Menu         Despatch Mch No or ALL         
                      (A)  ALL DISPATCH PLANNING REPORT    ALL
                      (B)  MAIN FG STORE DPR             8580     7995     8000     8001
                    (C)  WAREHOUSE A DPR            8581
                    (D)  WAREHOUSE B DPR            8582

Fast-track loads
1.    Fast Tracking can be setup on each parameter PCS/TD  or ULT option BDA when warehouse is not using RF/Scanning.  
2.    Set to level  4 or more, will allow manipulation of unit qty, selection of units, pallet types on the units, and total weight.
3.    Shipping user can see the type 0 Make/Deliver or type 1 Calloff orders for the next few days in the PCS system,  they can plan trucks.
4.     A pick list (printed DPR or load list) of orders, quantity and items can be printed for fork lift driver to load trucks.
5.    Selection can be unit by unit traceable, FIFO, or minimum partial pallet selection.
6.    A truck ID can be added for the docket
7.    Qty can be changed on a unit to match what was loaded, or units split up.
8.    Returns for Calloff jobs can open series on Calloff or not.  Add option has the unit finding most recent top up to assign unit against.
9.    When using Qty selection you would have no idea what inventory is truly left in house for a Quality concern.  Tally sheet selection provides unit traceability.

Fasttrack can be used from ULT BDA option.
ult00 menusep=. opt=b.d.a fasttrack=6 chgpal=1 fixstore=## 

Here are some of the command line arguments associated with fasttrack:
chgpal=
chgqty=
fasttrack=

Levels of fasttrack:
1= Planning status
2= Issue status
3= Loading status and Completed question
4= Completed status
5= Despatch status – appears you can use Accept command here before load is Billing.
6= Shipped status

Possible ‘fasttrack’ script placed on a ULT menu
clear
echo "Fasttrack ULT B.D.A  for store number"
echo
echo "Enter store number  \c"
read st
echo
ult00 menusep=. opt=b.d.a fasttrack=6 chgpal=1 fixstore=$st
if [ "$st" = "201" ];then
ult00 menusep=. opt=b.d.a fasttrack=6 chgpal=1 fixstore=201
fi
if [ "$st" = "251" ];then
ult00 menusep=. opt=b.d.a fasttrack=6 chgpal=1 fixstore=251
fi

Fast Calloff loads

ESP Fast Calloffs would only work for ‘Calloffs’, as it excludes the make/deliver shipments; no unit by unit inventory accuracy.  The correct amount would be peeled off and shipped using the fasttrack routine. Customers call at one of your remote stores and order products for immediate collection. In this case, orders are supplied from miscellaneous stock on hand.

1.    Units allocated in ULT may not match the actual units supplied to the customer.
2.    Returns for Calloff jobs can open series on Calloff or not.  Add option has the unit finding most recent top up to assign unit against.
3.    Fast Calloff uses the fasttrack routine to select units for each job
The oldest units are automatically picked. Parameter FIFO or least amount of split units.
􀂃 The last unit is split, if necessary, to make the exact quantity shipped for each job.
Where a unit is split the remaining split unit retains the oldest creation date and time
so that it will be the first unit picked next time
4.     Sufficient Qty needs to be in Store prior to processing the Calloff
5.     No truck ID on docket
6.    Quality concerns regarding production job – no idea what inventory is truly left in house for that production job, or where the rest of production has been shipped.
Electronic data transfers

GEN EE
Locate data transfers in MAP kiwi directory
Sent to ESP

*ULT*DLBOL* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*450*MASON FOA*0000*j*150123*112842* * * * *DC*14440*2685*3* * *10*L56*405*3*remuser*test*remuser*150123*112847*1632600*
*ULT*DLJOB* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*MASON FOA*0000*1694*2*360*2*8320*5776*1090*1745*037605-00*80.06*28.12*120611*120521*1*CB 35-26-30-26-33*1.48x1.64x1.48*450*3*0*0*
*ULT*JUNIT* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*1694*27*180*02*1*6*2*15*2888*545*1694*150123*112839*19372*437519372*0* *0*0* * * *130115*140508*
*ULT*JUNIT* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*1694*29*180*02*1*6*2*15*2888*545*1694*150123*112839*19374*437519374*0* *0*0* * * *130115*140609*
*ULT*DLJOB* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*MASON FOA*0000*1692*2*360*2*8320*5776*1050*1745*037603-00*80.06*28.12*120607*120521*2*CB 35-26-30-26-33*1.48x1.64x1.48*450*3*0*0*
*ULT*JUNIT* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*1692*22*180*02*1*6*2*15*2888*505*1692*150123*112839*12226*437512226*0* *0*0* * * *120607*153334*
*ULT*JUNIT* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*1694*31*180*02*1*6*2*15*2888*545*1692*150123*112839*19376*437519376*0* *0*0* * * *130115*140719*
*ULT*DLJOB* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*MASON FOA*0000*1693*1*180*1*8320*2888*545*1745*037604-00*80.06*28.12*120608*120521*1*CB 35-26-30-26-33*1.48x1.64x1.48*450*3*0*0*
*ULT*JUNIT* * *150123*112906*EST*1*4375*KIWIPLAN - CINCINNATI, OH*1694*45*180*02*1*6*2*15*2888*545*1693*150123*112839*19390*437519390*0* *0*0* * * *150123*103706*

 
ESP Dockets

ESP Delivery Dockets
 Design KDG template for all customer specific and out of country documentation.  

Upon ULT Despatch, user must approve or change the Complete/Partial Flag.

ESP Transfer Dockets

Warehouse Transfers – Design KDG template for transfer type documentation.

Upon ULT Despatch, user cannot change Complete/ Partial Flag.

Manual Dockets
User should log who, what, when, where, and manual docket number.  Then update KIWI system at a later time to get ESP docket number.  Then update log with ESP docket number.

Failed Dockets
1.    User should search ESP for docket using ULT load number.  If found,  right click and email current user.  Then open PDF file and print thru Windows.
2.    If no docket in ESP, user should check Agent.   

Create Invoices
ESP tools drop down – create invoices.  Select shipped bill of ladings on left,  or move them to the right to be held.

Post Invoices
ESP tools drop down – post invoices.  Select invoices on the left,  or move them to the right to be held.
  
Returns

ESP adds Customer Issue. 
 If check box ‘awaiting goods return’ is checked,  then ADRTN records are sent to MAP.

Shipping can plan for return monitoring the PCS Pick up goods machine
Stp Machine            
1     9997 Pick up Goods 
2     9998 Returned/Quarantine 

ULT return of units

Select job to return

ult01:BR                       Returned Goods                       18/Wed 14:46
================================================================================
Page 1/1                                                           Toggle 1 / 2
Idx Job#           S Qty_Ex  Qty_Rtn Str  Reason
  1 1042           P  31782    28800  10  Faulty Goods for Return

(M)ore, (P)rev, (B)eg, (T)og, (Q)uery, (F)inish, (L)ist, (V)iewLoc,  > 1....

Return St/Loc:  10 FGS...

Start typing (job-unit number(s))

ult01:BR                        Return Units                        18/Wed 14:48
================================================================================
Page 1/1            RtnId 1000002     Cur_St/Loc 10/FGS
Job# 1042                               Spec# 1655          QtyRqd 2982
Idx Job_Unit       Unit     Qty Sta Customer     Pal/Load     CumQty PO spec

(A)dd, (E)xit, UL# or Idx ? 1042-52................

Verify qty on each unit
Qty  600....

After all unloaded, use R for Return, enter location, this updates Pick Up goods machine feedback with quantity returned.

ult01:BR                        Return Units                        18/Wed 14:50
================================================================================
Page 1/1            RtnId 1000002     Cur_St/Loc 10/FGS
Job# 1042                               Spec# 1655          QtyRqd 2982
Idx Job_Unit       Unit     Qty Sta Customer     Pal/Load     CumQty PO spec
  1 1042-52          52     600 A   MJK Manage          1        600 1655
  2 1042-53          53     582 A   MJK Manage          1       1182 1655

(A)dd, (C)hg, (U)ndo, (R)eturn, (M)ore, (P)rev,  > R..

After all completely Returned for this return ID,  and no more coming back,  use F to finish the return.  This should update ESP awaiting goods return check box.
ult01:BR                       Returned Goods                       18/Wed 14:53
================================================================================
Page 1/1                                                           Toggle 1 / 2
Idx Job#           S Qty_Ex  Qty_Rtn Str  Reason
  1 1042           P  31782    29982  10  Faulty Goods for Return

(M)ore, (P)rev, (B)eg, (T)og, (Q)uery, (F)inish, (L)ist, (V)iewLoc,  > F...

PCS feedback and re-processing units

Order Status shows customer pickup step is done and 9998 Returned/Quarantine machine outstanding.  

Now you need to use PCS lineup option H to feedback the quarantine/return goods machine:

pcsmenu:AH              Job Running Order and Feedback              18/Wed 15:25
================================================================================
Lineup for    9998 Returned/Quarantine
                                                  Toggle  1/8         Pg   1/1
 Idx Job_NumbSr Cust Item Desc       PN DueDt La  Hrs  QtyIn B P D Locati T M  F
   1 1042    /6 MJK  7 1/4" Iceless  5> 03/18 -0   -2   29982   x x *Ready 0    C
 
(F)eedback, (N)ext, (P)rev, (T)oggle, (R)efresh, (M)enu, (C)orr,  > F

Enter Idx of Job for Processing   1..

pcsmenu:AH              Job Running Order and Feedback              18/Wed 15:26
================================================================================
Lineup for    9998 Returned/Quarantine
                                                  Toggle  1/8         Pg   1/1

   1 1042    /6 MJK  7 1/4" Iceless  5> 03/18 -0   -2   1182   x x *Ready 0    C

Total Sheets to be processed      1182

Transfer to In-Stock              1182
To be Reworked                       0
Scrap as Waste                       0
                              ========
Remaining in Quarantine              0

Proceed (Y/N) ?   Y

Order Status shows 9997 customer pickup step is fedback and 9998 Returned/Quarantine machine has changed to 8007 FGS Overrun machine outstanding.  

Stp Machine            Date_Time    Qty_In No_Up Qty_Good No_Up Wst_Qty Sta  U/L
 1  9997 Customer Pick 03/18 15:08   63762   1/1    63702   1/1      40  **   53
 2  8007 FGS Overrun   03/18 15:28   31782   1/1    31782   1/1       0  *    53

Dispatch plan for re-shipment

Units still show as Avail to ship against job 1042,  but they are on Overrun machine.
 If plant is going to re-ship, then move them to 8000 or 8001 CPU machines.  Else treat them as excess overrun and change ULT unit status to S for stock.

pcsmenu:ABA                       Job Summary                       18/Wed 15:30
================================================================================
Page 1/1                                                            Toggle  1/2
Idx Job     Custom Label Avail  Avail   Stock  Stock Spec      Q_Prd    Job_Desc
                   Units Units    Qty     Qty  Units
  1 1024    SFL Ma     0     0      0     600      1 1655      31866 7 1/4" Icel
  2 1042    SFL Ma     0    53  31782       0      0 1655      31787 7 1/4" Icel

Month End Reports

ULT BB

Create several reports in ULT BB option by sorting the selection many different ways.
ult21LL is St/Loc/Cust/job sort 3/7
ult21LJ is sort 3 jobs
ult21LS is st/loc/spec/job sort 7
ult21JJ  sort 1 jobs
ult21WL units
ult21CC cust/loc sort 8/9
ult21CB Cust / loc Jobs listing sort 9
ult21CA loc/cust sort 8 jobs
ult21D cust/loc 8 and units
ult21  units
ult21L  sort 3 units
ult21E  sort 8,9 units
ult21CJ sort 2 Cust job
ult21CS  sort 6 cust spec job
ult21JS sort 4 Job Store Loc
ult21SJ sort 5 spec Job
ult25 history of units