I'm going to store here a scrapbook or cookbook for Accpac SQL Queries which may be useful.
See here about Accpac SQL queries and Excel
Tax transactions
SELECT TXAUDH.AUTHORITY, TXAUDH.FISCYEAR, TXAUDH.FISCPERIOD, TXAUDH.DOCDATE, TXAUDH.SRCEAPP, TXAUDH.DOCTYPE, TXAUDH.DOCNUMBER,CASE [TXAUDH].[TYPE] WHEN 1 THEN 'SALES' ELSE 'PURCHASES' END AS 'Type', TXAUDH.CUSTVEND, TXAUDH.RATERECOV, TXAUTH.[DESC],
TXAUDH.RECOVERABL, TXAUDH.EXPSEPARTE, TXAUDH.SCURN, TXAUDH.SRECOVRAMT, TXAUDH.SDECIMAL, TXAUDH.SINVAMT, TXAUDD.SBASEAMT,
TXAUDD.SCURNTAX, { fn RTRIM({ fn CONVERT(TXAUDH.SEQUENCE, SQL_CHAR) }) } AS 'TXAUDH.SEQUENCE'
FROM TXAUDH AS TXAUDH INNER JOIN
TXAUDD AS TXAUDD ON TXAUDH.SEQUENCE = TXAUDD.SEQUENCE INNER JOIN
TXAUTH AS TXAUTH ON TXAUDH.AUTHORITY = TXAUTH.AUTHORITY
WHERE (TXAUDH.AUTHORITY = 'ATO') AND (TXAUDH.FISCYEAR = '2011') AND (TXAUDH.FISCPERIOD = 2)
ORDER BY TXAUDH.AUTHORITY, TXAUDH.TYPE, TXAUDH.FISCYEAR, TXAUDH.FISCPERIOD, TXAUDH.DOCDATE
Customer receipts
SELECT ARTCR.IDCUST, ARTCR.DATERMIT, ARTCR.AMTRMITHC, ARCUS.IDGRP, ARTCR.FISCPER, ARTCR.FISCYR
FROM SAUDAT.dbo.ARCUS ARCUS, SAUDAT.dbo.ARTCR ARTCR
WHERE ARCUS.IDCUST = ARTCR.IDCUST
Payments by account (for analysis of purchases)
SELECT dbo.APPJH.TRANSTYPE, dbo.APPJH.TRXTYPE, dbo.APPJH.FISCPER, dbo.APPJH.FISCYR, dbo.APPJH.TYPEBTCH, dbo.APPJH.POSTSEQNCE, APPJH.CNTBTCH,
APPJH.CNTITEM, dbo.APPJD.IDACCT, dbo.GLAMF.ACCTDESC, dbo.glamf.ACCTGRPCOD, apven.CURNCODE, dbo.APPJD.IDINVC, dbo.APPJD.AMTEXTNDHC, dbo.APPJD.AMTEXTNDTC,
APPJH.GLBATCH, dbo.APPJH.GLENTRY, dbo.APVEN.VENDORID, dbo.APVEN.VENDNAME, dbo.APPJD.DATEBUS,dbo.appjh.dateinvc,DBO.APVEN.IDGRP,DBO.APVEN.CODETAXGRP
FROM dbo.APPJH INNER JOIN
dbo.APPJD ON dbo.APPJH.TYPEBTCH = dbo.APPJD.TYPEBTCH AND dbo.APPJH.POSTSEQNCE = dbo.APPJD.POSTSEQNCE AND
dbo.APPJH.CNTBTCH = dbo.APPJD.CNTBTCH AND dbo.APPJH.CNTITEM = dbo.APPJD.CNTITEM INNER JOIN
dbo.GLAMF ON dbo.APPJD.IDACCT = dbo.GLAMF.ACCTID LEFT OUTER JOIN
dbo.APVEN ON dbo.APPJH.IDVEND = dbo.APVEN.VENDORID
WHERE (dbo.APPJD.IDACCT >= '60000') AND (dbo.APPJH.FISCYR >= '2010') AND (dbo.APPJH.TYPEBTCH <> 'PY') AND (DBO.APVEN.CODETAXGRP = 'HKTAX')
Link GL postings back to AR entries to trace sales transactions hitting GL accounts
This query finds sales accounts by assuming they all belong to an account group '08'SELECT GLJED.ACCTID, GLJEH.BATCHID, GLJEH.BTCHENTRY, GLJEH.FSCSYR, GLJEH.FSCSPERD, CONVERT(varchar(10), GLJED.TRANSDATE, 103) AS Date1,
GLJEH.SRCELEDGER, GLJEH.SRCETYPE, GLJED.TRANSAMT, ARPJH.IDCUST, ARCUS.CCTYPE, ARCUS.IDGRP, ARIBC.BTCHTYPE, ARPJH.IDINVC,GLPOST.POSTINGSEQ, GLPOST.TRANSNBR
FROM GLJEH AS GLJEH INNER JOIN
GLJED AS GLJED ON GLJEH.BATCHID = GLJED.BATCHNBR AND GLJEH.BTCHENTRY = GLJED.JOURNALID INNER JOIN
GLAMF AS GLAMF ON GLJED.ACCTID = GLAMF.ACCTID INNER JOIN
GLPOST ON GLJED.BATCHNBR = GLPOST.BATCHNBR AND GLJED.JOURNALID = GLPOST.ENTRYNBR AND
GLJED.TRANSNBR = GLPOST.TRANSNBR LEFT OUTER JOIN
ARCUS INNER JOIN
ARPJH ON ARCUS.IDCUST = ARPJH.IDCUST LEFT OUTER JOIN
ARIBC ON ARPJH.CNTBTCH = ARIBC.CNTBTCH ON GLJEH.BATCHID = ARPJH.GLBATCH AND GLJEH.BTCHENTRY = ARPJH.GLENTRY
WHERE (GLJEH.FSCSYR = '2011') AND (GLAMF.ACCTGRPCOD = '08')
Open Items (AR) THe correct table is AROBL
ARIBH doesn't show the amount open after payments have been made.this is an example
SELECT IDCUST, /*AROBL.IDINVC , TRXTYPETXT,*/ sum(AMTDUEhC) FROM AROBL where TRXTYPETXT in (1,2,3) /* and IDCUST = 'FMIIJO' */ group BY IDCUST order by idcust
| < Prev | Next > |
|---|
