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
