Lawson Admin Scripts

Foreground Compile Script

Save the following script into a standard scripts directory that is shared by all technical users. Call it komp:

    #!/bin/sh 

    if [ $# != 1 ] 
    then 
      echo " " 
      echo "   Usage: $0 program_name" 
      echo " " 
      exit 100 
    fi 

    pwd > /tmp/kompfile.txt 
    field1=`cut -d/ -f3 /tmp/kompfile.txt` 
    field2=`cut -d/ -f4 /tmp/kompfile.txt` 
    field3=`echo ${field2} | cut -c1-2` 
    rm /tmp/kompfile.txt 
    echo "Compiling Program "$1 
    sleep 1 

    loccmp -c $field1 $field3 $1 
    acct=`echo $1 | tr '[a-z]' '[A-Z]' ` 
    if [ -f $acct.err ] 
       then more $acct.err 
       else 
       echo "Compile Complete with no errors for Program "$1 
       exit 1 
    fi 

To run the script, and compile an application in the foreground, cd to where the application resides and then run:

komp ap160

When the session returns from the compile, it will display either error messages or a message indicating that program compiled with no errors (prevents having to continually check with qstatus and then go searching for a file holding errors).

Finding programs affected by a PDLIB

This dbdump command produces a listing, by product line, of all programs and the libraries contained within them. First, pipe this into a file as follows (this takes a few seconds to run and can create a file 1 or 2 megs in size):

dbdump gen pgmlib > gendump

Once you have created that file, you can grep subprograms out of it (first, limit it to a single product line, then go after the PDLIB):

grep TEST gendump | grep PRUEPAY | lashow

The results look like this:

Productline	System	Program	Screen Nbr	Libname	Filler
LIVE	BN	BN150	0	PRUEPAY	0
LIVE	BN	BN66	1	PRUEPAY	0
LIVE	PR	PR128	0	PRUEPAY	0
LIVE	PR	PR131	0	PRUEPAY	0
LIVE	PR	PR132	0	PRUEPAY	0
LIVE	PR	PR134	0	PRUEPAY	0
LIVE	PR	PR136	0	PRUEPAY	0
LIVE	PR	PR137	0	PRUEPAY	0
LIVE	PR	PR139	0	PRUEPAY	0
LIVE	PR	PR140	0	PRUEPAY	0

Here is a variation (display all programs that use by a library routine):

rngdbdump gen PGMLIB -k prodline syscode | grep library program

rngdbdump gen PGMLIB -k sup70 pr | grep PRCHKPD

Amazing Oracle SQL command - MINUS

How often do you want to see what changed in a table, or which records are missing from a table? The MINUS command will determine the differences between columns in two tables.

This query finds orphan AP Distribution records (those that are missing a parent invoice record):

select invoice from apdistrib
minus
select invoice from apinvoice ;

This query will find records in the GL Master table that were never used by existing transactions:

select company, acct_unit, account, sub_account from glmaster minus select company, acct_unit, account, sub_account from gltran ;

Easy file backup prior to making changes (Unix)

This script creates a backup copy of a Unix file with the date and time as part of the backup file name.

#! /bin/sh
# This script will creates a backup copy of any unix
# file, and adds a .datetime extension to the copy.
# To execute you only have to type the command cpy filename
# and the script will do the rest.
# The original save date and time are preserved.
DATE=`date '+%Y%m%d%H%M'`
newname=`echo $1.$DATE`
cp -p $1 $newname
echo " "
echo " $1 was copied to:"
echo " $newname"
echo " "

Here's what you get when you run this script:

$ cpy prodline.counts

The backup file prodline.counts.200007122138 was created

Monitor the progress of an Oracle import

This checks strategically placed tables in the HR/Payroll system to determine how far an Oracle import has progressed at any time during the import. It does this by displaying non-zero counts from all tables that have been successfully loaded at the time this "Progress" script is run.

sqlplus oracleid/password <grep -v 'SQL>'

set pagesize 300
set linesize 300
spool progress

select count(*) as agcy from agcy;
select count(*) as benefit from benefit;
select count(*) as employee from employee;
select count(*) as jobcode from jobcode;
select count(*) as objid from objid;
select count(*) as prbatch from prbatch;
select count(*) as prtime from prtime;
select count(*) as prsystem from prsystem;
select count(*) as xrefstep from xrefstep;
select count(*) as Indexes from user_indexes;
select count(*) as Tables from user_tables;
quit
DELIM

Summing a column of numbers on Unix

Here is a handy script for getting the total from a column of numbers (saves having to upload it to Excel and parse it out). It uses a short awk script to do the summing. The example takes the values in positions 72 to 80 of the file and accumulates them.

# This script adds the total dollar amount on the TSA file and displays it.

cut -c72-80 TSA > tsa.temp
cat tsa.temp | awk '{sum += $1 } END {printf("\nTotal Amount: %d\n\n", sum)}'
rm tsa.temp

Macro - Formatting Lawson reports in Word

The following macro will reformat a Lawson report so it breaks pages well and doesn't wrap when using them in Word. It sets the margins and font size so that reports can be viewed, printed, annotated, e-mailed, etc. in Micorsoft Word documents:

    Sub Format_Lawson_Report() 
    ' 
    ' Lawson Macro 
    ' Macro recorded 04/29/99 by David C. Amen 
    ' 
        Selection.WholeStory 
        Selection.Font.Name = "Courier New" 
        Selection.Font.Size = 8 
        With ActiveDocument.PageSetup 
            .LineNumbering.Active = False 
            .Orientation = wdOrientLandscape 
            .TopMargin = InchesToPoints(0.3) 
            .BottomMargin = InchesToPoints(0.2) 
            .LeftMargin = InchesToPoints(1) 
            .RightMargin = InchesToPoints(1) 
            .Gutter = InchesToPoints(0) 
            .HeaderDistance = InchesToPoints(0.5) 
            .FooterDistance = InchesToPoints(0.5) 
            .PageWidth = InchesToPoints(11) 
            .PageHeight = InchesToPoints(8.5) 
            .FirstPageTray = wdPrinterDefaultBin 
            .OtherPagesTray = wdPrinterDefaultBin 
            .SectionStart = wdSectionNewPage 
            .OddAndEvenPagesHeaderFooter = False 
            .DifferentFirstPageHeaderFooter = False 
            .VerticalAlignment = wdAlignVerticalTop 
            .SuppressEndnotes = False 
            .MirrorMargins = False 
        End With 
    End Sub 

Back    Home