Sage 300 Queries

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

Owners:

Single Customer queries:

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'

select  *

from "OEORDH" with (index(KEY_1))  

where  CUSTOMER='1200'   

select *

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)