When Crystal Reports isn't quite good enough.

Queries were written for Sage AccPac 5.5A for a third party audit of HST recovery.
The first query, APVNDLT1, extracted a list of vendors. The second query, apchkreg, extracted a listing of cheque made to those vendors along with which invoice(s) applied to each cheque. The remainder of the queries were various iterations of extracting data from the posting journal. Starting off with just the posting journal, then adding accounts from the g/l.

These were written/modified 12/2018 with the last query modified 01/2019 to distinguish between credits and invoices. There were reports available from within accpac to get most of the below data, but the reports "Excel" export of the reports end up as a heavily formatted spreadsheet that is supposed to look like the printed report making it very difficult to do any practical analysis of the actual data. In addition, the reports took quite a long time to run and would fail if the dataset was large. The end report required data spanning four years.

-- APVNDLT1

SELECT "APVEN"."VENDORID", "APVEN"."VENDNAME", "APVEN"."TEXTSTRE1", "APVEN"."TEXTSTRE2", "APVEN"."TEXTSTRE3", "APVEN"."TEXTSTRE4", "APVEN"."NAMECITY", "APVEN"."CODESTTE", "APVEN"."DATELASTMN", "APVEN"."DATEINAC", "APVEN"."SWHOLD", "APVEN"."SHORTNAME", "APVEN"."NAMECTAC", "APVEN"."TEXTPHON1", "APVEN"."TEXTPHON2", "APVEN"."IDGRP", "APVEN"."DATESTART", "APVEN"."CURNCODE", "APVEN"."AMTCRLIMT", "APVEN"."DUPAMTCODE", "APVEN"."CODECHECK", "APVEN"."TAXRPTSW", "APVEN"."TAXNBR", "APVEN"."TAXIDTYPE", "APVEN"."CLASID", "APVEN"."IDACCTSET", "APVEN"."SWDISTBY", "APVEN"."TERMSCODE", "APVEN"."PRIMRMIT", "APVEN"."BANKID", "APVEN"."CODETAXGRP", "APVEN"."CODEPSTL", "APVEN"."CODECTRY", "APVEN"."TAXCLASS1", "APVEN"."TAXCLASS2", "APVEN"."TAXCLASS3", "APVEN"."TAXCLASS4", "APVEN"."TAXCLASS5", "TXGRP"."AUTHORITY2", "TXGRP"."AUTHORITY3", "TXGRP"."AUTHORITY4", "TXGRP"."AUTHORITY5", "TXGRP"."AUTHORITY1", "APVEN"."IDTAXREGI1", "APVEN"."IDTAXREGI2", "APVEN"."IDTAXREGI3", "APVEN"."IDTAXREGI4", "APVEN"."IDTAXREGI5", "APVEN"."SWTXINC1", "APVEN"."SWTXINC2", "APVEN"."SWTXINC3", "APVEN"."SWTXINC4", "APVEN"."SWTXINC5", "APVEN"."SWACTV", "APVEN"."DUPDATECD", "APVEN"."RATETYPE", "APVEN"."PRTSEPCHKS", "APVCM"."VENDORID", "APVEN"."EMAIL2", "APVEN"."EMAIL1", "APVEN"."WEBSITE", "CSCCD"."DECIMALS", "APVEN"."CTACPHONE", "APVEN"."CTACFAX", "APVEN"."DELMETHOD", "CSCOM"."HOMECUR", "APVEN"."DISTSETID", "APVEN"."DISTCODE", "APVEN"."GLACCNT", "APVEN"."RTGPERCENT", "APVEN"."RTGDAYS", "APVEN"."RTGTERMS", "APP03"."SWRTG", "APVEN"."VALUES", "APSLVEN"."SELSEQ", "TXGRP"."TTYPE", "APVEN"."PAYMCODE", "APSLVEN"."RECORDNO"
FROM  ((((("APSLVEN" "APSLVEN" INNER JOIN "APVEN" "APVEN" ON "APSLVEN"."IDVEND"="APVEN"."VENDORID") INNER JOIN "TXGRP" "TXGRP" ON "APVEN"."CODETAXGRP"="TXGRP"."GROUPID") INNER JOIN "CSCOM" "CSCOM" ON "APVEN"."AUDTORG"="CSCOM"."ORGID") INNER JOIN "APP03" "APP03" ON "APVEN"."AUDTORG"="APP03"."AUDTORG") LEFT OUTER JOIN "APVCM" "APVCM" ON "APVEN"."VENDORID"="APVCM"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APVEN"."CURNCODE"="CSCCD"."CURID"
WHERE "TXGRP"."TTYPE"=2

The second query, apchkreg, extracted a listing of cheque made to those vendors along with which invoice(s) applied to each cheque.

-- apchkreg

SELECT "APVEN"."VENDNAME", "CSCCD"."DECIMALS", "APPYM"."IDBANK", "APPYM"."CODECURN", "APPYM"."AMTPAYM", "CSCCD_APPYM"."DECIMALS", "APPYM"."IDRMIT", "APPJH"."POSTSEQNCE", "APPJH"."SWNONRCVBL", "APPJH"."MISCRMITTO", "APPJD"."IDINVC", "APPJD"."CNTPAYM", "APPJD"."AMTDSCTCUR", "APPJD"."AMTADJTCUR", "APPJD"."AMTEXTNDTC", "APPJD"."AMTDSCHCUR", "APPJD"."AMTADJHCUR", "APPJD"."AMTEXTNDHC", "APPJH"."SWSTATUS", "APPJD"."CODECURN", "APPJH"."TYPEBTCH", "APPJD"."ACCTTYPE", "APPJD"."IDTRANS", "APPJH"."IDVEND", "APPJD"."TRANSTYPE", "APPJD"."DATEDISC", "APPJD"."DATEDUE", "APPJH"."IDRMIT", "APPJH"."LONGSERIAL", "APPJH"."DATEINVC", "APPJH"."PAYMTYPE"
FROM  (((("APPJH" "APPJH" INNER JOIN "APPJD" "APPJD" ON ((("APPJH"."TYPEBTCH"="APPJD"."TYPEBTCH") AND ("APPJH"."POSTSEQNCE"="APPJD"."POSTSEQNCE")) AND ("APPJH"."CNTBTCH"="APPJD"."CNTBTCH")) AND ("APPJH"."CNTITEM"="APPJD"."CNTITEM")) INNER JOIN "APPYM" "APPYM" ON ((("APPJH"."IDBANK"="APPYM"."IDBANK") AND ("APPJH"."IDVEND"="APPYM"."IDVEND")) AND ("APPJH"."IDRMIT"="APPYM"."IDRMIT")) AND ("APPJH"."LONGSERIAL"="APPYM"."LONGSERIAL")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "CSCCD" "CSCCD_APPYM" ON "APPYM"."CODECURN"="CSCCD_APPYM"."CURID"
WHERE  "APPJH"."TYPEBTCH"='PY' AND "APPJH"."PAYMTYPE"=2 AND ("APPJH"."POSTSEQNCE">=10435 AND "APPJH"."POSTSEQNCE"<=10520) AND "APPJD"."ACCTTYPE"=1 AND ("APPJD"."IDTRANS"=51 OR "APPJD"."IDTRANS"=57)
ORDER BY IDVEND ASC

This is where things begin to get interesting. First is the posting journal

-- APIPJ01
SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH", "APPJH"."POSTSEQNCE", "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS"
FROM (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350)
ORDER BY "APPJS"."POSTSEQNCE"

Then we had to add information from the GL. Start with the posting journal query and the GL query.

-- gl detail and apipj01

 ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC"
 FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD"
 WHERE  "APPJD"."TYPEBTCH"='IN' AND "APPJD"."POSTSEQNCE"=6317 AND "APPJD"."CNTBTCH"=6337 AND "APPJD"."CNTITEM"=1 ) as GLDTL

 ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH", "APPJH"."POSTSEQNCE", "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS"
 FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
 WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01

Remove unnessasary fields and combine the two queries.

-- Fields removed from below SELECT - , "GLDTL"."DECIMALS", "GLDTL"."TRANSTYPE", "GLDTL"."TYPEBTCH", "GLDTL"."CNTITEM", "GLDTL"."CNTSEQENCE", "GLDTL"."OPTFIELD", "GLDTL"."VALUES", "GLDTL"."VALUE", "GLDTL"."TYPE", "GLDTL"."GLREF", "GLDTL"."GLDESC"
SELECT "APIPJ01"."DATEBUS", "APIPJ01"."IDVEND", "APIPJ01"."POSTSEQNCE", "APIPJ01"."VENDNAME", "APIPJ01"."CNTBTCH", "APIPJ01"."CNTITEM", "APIPJ01"."DATEINVC", "APIPJ01"."FISCYR", "APIPJ01"."FISCPER", "APIPJ01"."IDINVC", "APIPJ01"."TRANSTYPE", "APIPJ01"."PGMVER", "APIPJ01"."CODETAXGRP", "APIPJ01"."CODECURNTC", "APIPJ01"."CODETERM", "APIPJ01"."RATEEXCHTC", "APIPJ01"."DATEDUE", "APIPJ01"."RATEDATETC", "APIPJ01"."RTGTERMS", "APIPJ01"."SWRTG", "APIPJ01"."SWJOB", "APIPJ01"."SWRTGRATE", "APIPJ01"."AMTINVCTC", "APIPJ01"."RTGAMTTC", "APIPJ01"."RTGAPPLYTO", "APIPJ01"."IDINVCAPPL", "APIPJ01"."IDBANK", "APIPJ01"."IDRMIT", "APIPJ01"."TYPEBTCH", "APIPJ01"."DECIMALS", "APIPJ01"."RTGDATEDUE", "APIPJ01"."VALUES", "APIPJ01"."SWPRINTED", "APIPJ01"."DESC", "APIPJ01"."RATETYPETC", "APIPJ01"."IDACCTSET", "APIPJ01"."APPJH_DATEBUS", "GLDTL"."IDDIST", "GLDTL"."IDACCT", "GLDTL"."ACCTDESC", "GLDTL"."AMTEXTNDTC", "GLDTL"."AMTTAXTC", "GLDTL"."AMTEXTNDHC", "GLDTL"."AMTTAXHC"
FROM
  ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH" AS APPJH_TYPEBTCH, "APPJH"."POSTSEQNCE" AS APPJH_POSTSEQNCE, "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS" AS APPJH_DATEBUS
    FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
    WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01
  LEFT JOIN
  ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC"
    FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD") as GLDTL
  ON "APIPJ01"."TYPEBTCH" = "GLDTL"."TYPEBTCH" AND "APIPJ01"."POSTSEQNCE" = "GLDTL"."POSTSEQNCE" AND "APIPJ01"."CNTBTCH" = "GLDTL"."CNTBTCH" AND "APIPJ01"."CNTITEM" = "GLDTL"."CNTITEM"
ORDER BY "APIPJ01"."POSTSEQNCE", "APIPJ01"."IDVEND"

Now we have all of the records. However, there is no distinction between invoices and credits. Added one field to tie everything together.

-- Fields removed from below SELECT - , "GLDTL"."DECIMALS", "GLDTL"."TRANSTYPE", "GLDTL"."TYPEBTCH", "GLDTL"."CNTITEM", "GLDTL"."CNTSEQENCE", "GLDTL"."OPTFIELD", "GLDTL"."VALUES", "GLDTL"."VALUE", "GLDTL"."TYPE", "GLDTL"."GLREF", "GLDTL"."GLDESC"
-- Added srcetype appjd.srcetype - gldtl.srcetype
SELECT "APIPJ01"."DATEBUS", "APIPJ01"."IDVEND", "APIPJ01"."POSTSEQNCE", "APIPJ01"."VENDNAME", "APIPJ01"."CNTBTCH", "APIPJ01"."CNTITEM", "APIPJ01"."DATEINVC", "APIPJ01"."FISCYR", "APIPJ01"."FISCPER", "APIPJ01"."IDINVC", "APIPJ01"."TRANSTYPE", "APIPJ01"."PGMVER", "APIPJ01"."CODETAXGRP", "APIPJ01"."CODECURNTC", "APIPJ01"."CODETERM", "APIPJ01"."RATEEXCHTC", "APIPJ01"."DATEDUE", "APIPJ01"."RATEDATETC", "APIPJ01"."RTGTERMS", "APIPJ01"."SWRTG", "APIPJ01"."SWJOB", "APIPJ01"."SWRTGRATE", "APIPJ01"."AMTINVCTC", "APIPJ01"."RTGAMTTC", "APIPJ01"."RTGAPPLYTO", "APIPJ01"."IDINVCAPPL", "APIPJ01"."IDBANK", "APIPJ01"."IDRMIT", "APIPJ01"."TYPEBTCH", "APIPJ01"."DECIMALS", "APIPJ01"."RTGDATEDUE", "APIPJ01"."VALUES", "APIPJ01"."SWPRINTED", "APIPJ01"."DESC", "APIPJ01"."RATETYPETC", "APIPJ01"."IDACCTSET", "APIPJ01"."APPJH_DATEBUS", "GLDTL"."IDDIST", "GLDTL"."IDACCT", "GLDTL"."ACCTDESC", "GLDTL"."AMTEXTNDTC", "GLDTL"."AMTTAXTC", "GLDTL"."AMTEXTNDHC", "GLDTL"."AMTTAXHC", "GLDTL"."SRCETYPE"
FROM
  ( SELECT "APPJS"."POSTSEQNCE", "APPJS"."DATEBUS", "APPJH"."IDVEND", "APVEN"."VENDNAME", "APPJH"."CNTBTCH", "APPJH"."CNTITEM", "APPJH"."DATEINVC", "APPJH"."FISCYR", "APPJH"."FISCPER", "APPJH"."IDINVC", "APPJH"."TRANSTYPE", "APPJS"."PGMVER", "APPJH"."CODETAXGRP", "APPJH"."CODECURNTC", "APPJH"."CODETERM", "APPJH"."RATEEXCHTC", "APPJH"."DATEDUE", "APPJH"."RATEDATETC", "APPJH"."RTGTERMS", "APPJH"."SWRTG", "APPJH"."SWJOB", "APPJH"."SWRTGRATE", "APPJH"."AMTINVCTC", "APPJH"."RTGAMTTC", "APPJH"."RTGAPPLYTO", "APPJH"."IDINVCAPPL", "APPJH"."IDBANK", "APPJH"."IDRMIT", "APPJH"."TYPEBTCH" AS APPJH_TYPEBTCH, "APPJH"."POSTSEQNCE" AS APPJH_POSTSEQNCE, "CSCCD"."DECIMALS", "APPJH"."RTGDATEDUE", "APPJH"."VALUES", "APPJS"."TYPEBTCH", "APPJS"."SWPRINTED", "APPJH"."DESC", "APPJH"."RATETYPETC", "APPJH"."IDACCTSET", "APPJH"."DATEBUS" AS APPJH_DATEBUS
   FROM   (("APPJS" "APPJS" INNER JOIN "APPJH" "APPJH" ON ("APPJS"."TYPEBTCH"="APPJH"."TYPEBTCH") AND ("APPJS"."POSTSEQNCE"="APPJH"."POSTSEQNCE")) LEFT OUTER JOIN "APVEN" "APVEN" ON "APPJH"."IDVEND"="APVEN"."VENDORID") LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJH"."CODECURNTC"="CSCCD"."CURID"
   WHERE  "APPJS"."TYPEBTCH"='IN' AND ("APPJS"."POSTSEQNCE">=6317 AND "APPJS"."POSTSEQNCE"<=6350) ) as APIPJ01
 LEFT JOIN
  ( SELECT "APPJD"."POSTSEQNCE", "APPJD"."IDDIST", "APPJD"."IDACCT", "GLAMF"."ACCTDESC", "APPJD"."AMTEXTNDTC", "APPJD"."AMTTAXTC", "APPJD"."AMTEXTNDHC", "APPJD"."AMTTAXHC", "CSCCD"."DECIMALS", "APPJD"."TRANSTYPE", "APPJD"."TYPEBTCH", "APPJD"."CNTBTCH", "APPJD"."CNTITEM", "APPJD"."CNTSEQENCE", "APPJDO"."OPTFIELD", "APPJD"."VALUES", "APPJDO"."VALUE", "APPJDO"."TYPE", "APPJD"."GLREF", "APPJD"."GLDESC", "APPJD"."SRCETYPE"
   FROM   (("APPJD" "APPJD" LEFT OUTER JOIN "CSCCD" "CSCCD" ON "APPJD"."CODECURN"="CSCCD"."CURID") LEFT OUTER JOIN "APPJDO" "APPJDO" ON (((("APPJD"."TYPEBTCH"="APPJDO"."TYPEBTCH") AND ("APPJD"."POSTSEQNCE"="APPJDO"."POSTSEQNCE")) AND ("APPJD"."CNTBTCH"="APPJDO"."CNTBTCH")) AND ("APPJD"."CNTITEM"="APPJDO"."CNTITEM")) AND ("APPJD"."CNTSEQENCE"="APPJDO"."CNTSEQENCE")) LEFT OUTER JOIN "GLAMF" "GLAMF" ON "APPJD"."IDACCT"="GLAMF"."ACCTFMTTD") as GLDTL
 ON "APIPJ01"."TYPEBTCH" = "GLDTL"."TYPEBTCH" AND "APIPJ01"."POSTSEQNCE" = "GLDTL"."POSTSEQNCE" AND "APIPJ01"."CNTBTCH" = "GLDTL"."CNTBTCH" AND "APIPJ01"."CNTITEM" = "GLDTL"."CNTITEM"
ORDER BY "APIPJ01"."POSTSEQNCE", "APIPJ01"."IDVEND"

I made a couple of attempts extracting and exporting the data using Sql Server Management Studio Express on the server hosting they MSSQL backend for AccPac. There were several issues with getting the data in a usable form. I ended up connecting to the server in Excel 2016 and using the queries as part of the setup to extract the data directly into an xlsx spreadsheet.