Sage 300 Queries: Difference between revisions

From Data Islands
No edit summary
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 4: Line 4:




Owners:


Accounts Payable
<u>'''Owners:'''</u>
 
 
'''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
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:
<u>'''Single Customer queries for a customer data region:'''</u>


CUSTOMER DETAILS
'''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'
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
'''CUSTOMER ORDERS'''
 
<u>--get all customer orders</u>


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 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 itemsselect 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)
<u>--get all customer orders items</u>
 
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)
<u>--customer order totals (change the -2 etc for the give year.....set to 0 for the current year)</u>


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   
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   

Latest revision as of 22:53, 4 September 2022


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