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.

No comments: