Tim Richardson

Melbourne, Australia

  • Increase font size
  • Default font size
  • Decrease font size

Accpac SQL query scrapbook

E-mail Print PDF

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

Tags:

Add this to your website
Last Updated on Friday, 19 November 2010 09:06