Integration with Computers Unlimited 3.0

In Access, you’ll need to create links to the following tables in CU’s database: LINPRM—permanent archived invoices LINHST—“invoiced” invoices from the current calendar month ORDHDR—orderline header info CUSMAS—customer master INVMAS—inventory master CY_BF—cylinder balances ORDLGAS—gas orderline ORDLIN—orderline

You’ll also need to create a VB module with the following: Visual Basic Function to Left pad a number with zeros, to match the format of CU’s scannable invoice numbers. '*********************************************************************

'Declarations section of the module.

'*********************************************************************

Option Explicit Dim x As Integer Dim PadLength As Integer

'=====================================================================

'The following function will left pad a string with a specified 'character. It accepts a base string which is to be left padded with 'characters, a character to be used as the pad character, and a 'length which specifies the total length of the padded result.

'=====================================================================

Function lpad(MyValue$, MyPadCharacter$, MyPaddedLength%)

     PadLength = MyPaddedLength - Len(MyValue)
     Dim PadString As String
     For x = 1 To PadLength
        PadString = PadString & MyPadCharacter
     Next
     lpad = PadString + MyValue

End Function

In all of the queries below, you’ll need to change the “JWE” to the three letter prefix for your client.

Historical invoice Query: for all invoices not in CURRENT CALENDAR MONTH

SELECT jwe_LINPRM.CUSNO, jwe_CUSMAS.LAST_NAME, lpad(jwe_LINPRM.ORDNO,0,8) & lpad(jwe_LINPRM.BAKNO,0,2) AS Expr1, jwe_LINPRM.LINHST_DATE, jwe_LINPRM.LINPRM_DATE, jwe_LINPRM.SHIP_DATE, jwe_LINPRM.PART, Replace([jwe_INVMAS].[DESCR1]+[jwe_INVMAS].[DESCR2],',',' ') AS Expr2, jwe_LINPRM.CYLSHIP, jwe_LINPRM.CYLRET, jwe_LINPRM.PONO FROM jwe_LINPRM, jwe_CUSMAS, jwe_INVMAS WHERE (((jwe_LINPRM.CUSNO)=[jwe_CUSMAS].[CUSNO]) AND ((jwe_LINPRM.LINPRM_DATE)>=20071001 And (jwe_LINPRM.LINPRM_DATE)<=20071015) AND ((jwe_LINPRM.PART)=[jwe_INVMAS].[PART]) AND ((jwe_LINPRM.GASFLG)=1) AND ((jwe_LINPRM.SUP)=[jwe_INVMAS].[SUP]));

Query for "invoiced" invoices in the current calendar month:

SELECT jwe_LINHST.CUSNO, jwe_CUSMAS.LAST_NAME, lpad(jwe_LINHST.ORDNO,0,8) & lpad(jwe_LINHST.BAKNO,0,2) AS Ordno, jwe_LINHST.INVDTE, jwe_LINHST. INVDTE, jwe_LINHST. INVDTE, jwe_LINHST.PART AS Expr3, Replace(jwe_INVMAS.DESCR1+jwe_INVMAS.DESCR2,',',' ') AS Expr4, jwe_LINHST.CYLSHIP, jwe_LINHST.CYLRET, jwe_LINHST.PONO FROM jwe_LINHST, jwe_CUSMAS, jwe_INVMAS WHERE (((jwe_LINHST.CUSNO)=jwe_CUSMAS.CUSNO) And ((jwe_LINHST.GASFLG)=1) And ((jwe_LINHST.SUP)=jwe_INVMAS.SUP) And ((jwe_LINHST.PART)=jwe_INVMAS.PART)) And jwe_LINHST. INVDTE >=20071218 And jwe_LINHST. INVDTE <=20071219 ORDER BY ordno;

Query for "Selected Invoices" (closed invoices that will go to "invoiced" status overnight, but that can still be changed):

SELECT jwe_ORDHDR.OCUSNO, jwe_ORDHDR.OCUSNM, lpad(jwe_ORDHDR.OORDNO,0,8) & lpad(jwe_ORDHDR.OBAKNO,0,2) AS Expr1, jwe_ORDHDR.OORDDT, jwe_ORDHDR.OORDDT, jwe_ORDHDR.OORDDT, jwe_ORDLIN.LITMNO_ITEM, jwe_ORDLIN.LDESCR1, jwe_ORDLGAS.LSHIP, jwe_ORDLGAS.LRET, jwe_ORDHDR.OPONO FROM jwe_ORDHDR, jwe_ORDLGAS, jwe_ORDLIN WHERE (((jwe_ORDHDR.OORDNO)=[jwe_ORDLGAS].[LGAS_ORDNO] And (jwe_ORDHDR.OORDNO)=[jwe_ORDLIN].[LORDNO]) AND ((jwe_ORDHDR.OBAKNO)=[jwe_ORDLGAS].[LGAS_SEQNO] And (jwe_ORDHDR.OBAKNO)=[jwe_ORDLIN].[LSEQNO]) AND ((jwe_ORDLIN.LITMNO_SUP)='GAS') AND ((jwe_ORDLIN.LINENUMA)=[jwe_ORDLGAS].[LGAS_LINENUMA])) and jwe_ORDHDR.OFLAG=1;

Query for Customer Balances:

SELECT [jwe_CYO_BF].[CUSNO], [jwe_CYO_BF].[SUP]+[jwe_CYO_BF].[PART], [QTY], [jwe_CUSMAS].[LAST_NAME] FROM jwe_CYO_BF, jwe_CUSMAS WHERE [jwe_CYO_BF].[CUSNO]=[jwe_CUSMAS].[CUSNO];

Query for List of customers with customer numbers:

SELECT [jwe_CUSMAS].[CUSNO], [jwe_CUSMAS].[LAST_NAME] FROM jwe_CUSMAS;

CCDMAS Credit Card