Sage 300 Queries

Contained here are some recommended queries for Sage 300 to get only the data needed for Owners and customers.

Owners:

Customers

select IDCUST as idfield, AUDTORG,TEXTSNAM,  NAMECUST as 'Customer', TEXTSTRE1 as 'Address 1',TEXTSTRE2,TEXTSTRE3,TEXTSTRE4,NAMECITY as 'City',CODESTTE,CODEPSTL,CODECTRY,NAMECTAC,TEXTPHON1,TEXTPHON2,CODETERR,IDACCTSET,AMTCRLIMT AS 'Credit Limit',AMTBALDUET,DAYSTOPAY,EMAIL1 from ARCUS

Accounts Payable

SELECT L.IDVEND ,V.VENDNAME, L.CODECURN, SUM(AMTINVCHC) PAYABLEFUNCT, SUM(AMTINVCTC) PAYABLESRC FROM APOBL L LEFT JOIN APVEN V ON L.IDVEND=V.VENDORID WHERE (((L.SWPAID = 0 OR L.SWPAID = 1) )) GROUP BY L.IDVEND,V.VENDNAME, L.CODECURN

Top sales people

select CODESLSP, sum(AMTINVC) as 'AMTINVC' from ARSPS where CNTYR='2020' group by CODESLSP

OE Stats data

select YR,PERIOD,CURRENCY,QTYSOLD,SALESAMTF,INVAMTF, COGSF, INVCOUNT,LARGSTINF,LARGSTINS,(SELECT NAMECUST FROM ARCUS WHERE IDCUST=LINVCUST) AS 'LARGEST INVOICE CUSTOMER',(SELECT NAMECUST FROM ARCUS WHERE IDCUST=SINVCUST) AS 'SMALLEST INVOICE CUSTOMER'from OESTATS order by YR,PERIOD

---

Single Customer queries for a customer data region:

CUSTOMER DETAILS

select AUDTORG,TEXTSNAM,  NAMECUST as 'Customer', TEXTSTRE1 as 'Address 1',TEXTSTRE2,TEXTSTRE3,TEXTSTRE4,NAMECITY as 'City',CODESTTE,CODEPSTL,CODECTRY,NAMECTAC,TEXTPHON1,TEXTPHON2,CODETERR,IDACCTSET,AMTCRLIMT AS 'Credit Limit',AMTBALDUET,DAYSTOPAY,EMAIL1 from ARCUS where IDCUST='1200'

CUSTOMER ORDERS

--get all customer orders

select ORDUNIQ, ORDNUMBER,CUSTOMER, BILNAME,BILADDR1,BILCITY,BILSTATE,BILZIP,BILCOUNTRY,BILPHONE,BILCONTACT,SHIPTO,SHPNAME,SHPADDR1,SHPADDR2,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,TERMTTLDUE,    REFERENCE,    ORDDATE,EXPDATE,ORDFISCYR,    INVSUBTOT,INVNET,INVETAXTOT,INVNETWTX,INVAMTDUE from OEORDH with (index(KEY_1))  where  CUSTOMER='1200'

--get all customer orders items

select ORDUNIQ,LINENUM, LINETYPE,ITEM,[DESC],QTYORDERED,QTYSHIPPED,    QTYBACKORD, ORDUNIT,UNITPRICE, TAMOUNT1,TAMOUNT2,TRATE1,TRATE2 from OEORDD where ORDUNIQ in ( select  ORDUNIQ   from OEORDH with (index(KEY_1))   where  CUSTOMER='1200'   ) AND ((LINENUM >= 0)) order by ORDUNIQ,LINENUM option(fast 10)

--customer order totals (change the -2 etc for the give year.....set to 0 for the current year)

select sum(TERMTTLDUE) as amt,DATEPART(m, convert(datetime,convert(varchar(10),INVDATE,120))) as mth, DATEPART(yyyy, convert(datetime,convert(varchar(10),INVDATE,120))) as yr from OEINVH where CUSTOMER='1200'  and ( DATEPART(yyyy, convert(datetime,convert(varchar(10),INVDATE,120) )) = DATEPART(yyyy,  DATEADD(yyyy, -2, getdate())) )  group by DATEPART(m, convert(datetime,convert(varchar(10),INVDATE,120))),  DATEPART(yyyy, convert(datetime,convert(varchar(10),INVDATE,120))) order by yr, mth