Wednesday, November 26, 2008

Configure ODBC to access iSeries(AS/400) system

IBM provides software like WRKQRY, STRSQL on the iSeries (AS/400) system to access and query the database DB2 UDB and needs green screen terminal emulation to work with it Another way to access the DB2 database is to use iSeries navigator that provides option to run SQL statements.

The iSeries database can also be accessed using the ODBC and OLE DB drivers that can be installed on the PC and configured in the ODBC data source administration. Once the drivers are installed and configured you can use SQL to access and query the DB2 database using Excel or Access or any third party database management tool like WinSQL (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp).

The OLE DB drivers allows developer to write application using OLE DB connection in VB or VB.NET/C# using Microsoft Visual studio etc.

To install ODBC or OLE DB driver, you will require the installation media iSeries Access for Window from IBM and is normally provided as a part of standard softwares when iSeries system are purchased from IBM. If you do not have the installation media contact the vendor or IBM for more information and cost involved in purchasing it.

During the installation process of iSeries Access for Window software, select the custom setup that allows to select the required program features you want to install on the PC. Below is the screenshot when performing custom setup and make sure you have selected the ODBC and OLE DB provider as required for installation.
Once the installation is complete, the drivers will be available for use for your tools or development.












To configure ODBC to access perform the following step (I am using Window XP):
Click on Start->Control Panel->Administrative Tools that will bring the Window to setup a Data Source on your PC.















Open the Data Source (ODBC) by double click on the shortcut to bring up the ODBC Data Source Administrator.
Select the System DSN and Click on the Add button to create a new Data source.















Select Client Access ODBC Driver (32-bit) and click on the Finish button to continue.

Enter a name in the Data source name field and the IP address in the System field.
















Click on the Server tab and enter the SQL default library.
















Click OK button to complete the process.

For additional references to the other options and tabs refer the documentation or user guide of the iSeries Access to Windows.

Once the setup is complete, the ODBC data source is available to access from other tools.
In next time post I will write on tools to access the ODBC data source.

Monday, November 24, 2008

Open Customer Order and Delivery Information in Lawson M3 (Movex)

The Customer order Management (COM) and Warehouse (SCE) users requests a quick data extract for Open Customer orders and delivery information from M3 (Movex).

The delivery data are stored the delivery header table MHDISH and delivery line table MHDISL. The customer order lines are stored in the table OOLINE and Customer master table is OCUSMA.

The SQL below will list all the open customer order and delivery information

SELECT OBWHLO as Warehouse,
OQCONN as ShipmentNumber,
OQDLIX as DeliveryNumber,
OBORNO as OrderNumber ,
OBPONR as Line ,
OBCUNO as CustomerNo,
OKCUNM as CusotmerName,
OBADID as AddressID,
OBCUOR as CustomerPO,
OQTEDF as DeliveryTerm,
OQMODF as DeliveryMethod,
OQPGRS as DeliveryStatus,
OQDSDT as DepartureDate,
OQDSHM as DepartureTime,
OBITNO as ItemNumber,
OBITDS as ItemDescription,
URTRQT as DeliveryTransactionQty,
URGRWE as GrossWeight,
OBORQT as OrderQty,
OBALQT as AllocatedQty,
OBPLQT as PickQty,
OBORST as Status
FROM MHDISH
LEFT JOIN MHDISL ON OQCONO=URCONO AND OQDLIX=URDLIX
JOIN OOLINE ON URCONO=OBCONO AND URRIDN=OBORNO AND URRIDL = OBPONR*100
JOIN OCUSMA ON OBCONO=OKCONO AND OBCUNO=OKCUNO
WHERE OQCONO=1 AND OQRORC='3' AND
OQWHLO = 'WH1' AND OQPGRS < '90'

The field OQRORC (Reference order category) = 3 will list only customer orders and the status field OQPGRS < ‘90’ will list deliveries that are not fully reported.

Substitute Company and Warehouse value with the required value for your company and warehouse.

The SQL is applicable to Movex version 12 or later. Depending up on the version the field listed in the SQL may require be adding or removing from the SQL Statement.

Thursday, November 20, 2008

Open outstanding AR from Lawson M3 (Movex) ERP system

One of the repeated request received from the finance users (specially AR manager or Supervisor) a list of outstanding AR Invoices.

The sql statement will query the M3 (Movex) table FSLEDG to provide AR invoices that are not paid:

SELECT ESDIVI, ESCUNO, ESINYR,ESCINO, ESDUDT, MAX(ESDUDT) AS DUEDATE, MAX(ESIVDT) AS INVOICEDATE, SUM (ESCUAM) AS OUTAMT
FROM FSLEDG WHERE ESCONO=1 AND ESDIVI = '100' GROUP BY ESDIVI, ESCUNO, ESINYR, ESCINO, ESDUDT HAVING SUM (ESCUAM) <> 0

Substitute the ESCONO and ESDIVI value with the required Company number and Division.