AccPac report

AccPac report can be downloaded from BE Invoice. Select a few invoices; then choose Export dropdown, choose AccPac.

Terms: PBI: Pay By Invoice. For AccPac, PBI orders are exported differently than non-PBI orders.

Code: ConvertToAccpac.php

Steps to build AccPac report:

First; grab all the headers (orders)
Query: 

$orderHeaders = $connection->fetchAll($queryGetHeaders);

Order headers are grouped by OrdUniq. OrdUniq are either Accounting Label ID (or sales_payment's po_number; in case the order is PBI). Note that AccPac report is supposed to be exported only once per day; therefore the OrdUniq should always be unique. Look into the raw SQL query GROUP BY to know more.

Second; grab all the details from `sales_invoice_item` :

$orderSkuRows = $connection->fetchAll($queryGetSkuRows);

$orderSkuRows are grouped by OrdUniq (see above); and then grouped by AccPac SKU. 

With the $orderHeaders and $orderSkuRows, we are ready to build the report. 

Example built report look like this:

For a PBI order:

RECTYPE,ORDUNIQ,ORDNUMBER,CUSTOMER,SHPNAME,SHPADDR1,SHPADDR2,SHPADDR3,SHPADDR4,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,SHPFAX,SHPCONTACT,PONUMBER,TYPE,ORDDATE,EXPDATE,LOCATION,TAMOUNT1,AUTOTAXCAL
RECTYPE,ORDUNIQ,LINENUM,LINETYPE,ITEM,MISCCHARGE,CATEGORY,LOCATION,QTYORDERED,PRIUNTPRC,EXTINVMISC,INVDISC,TAMOUNT1,COMMINST
RECTYPE,ORDUNIQ,PAYMENT
RECTYPE,ORDUNIQ,UNIQUIFIER,DETAILNUM,COINTYPE,COIN
RECTYPE,ORDUNIQ,UNIQUIFIER
RECTYPE,ORDUNIQ,OPTFIELD
RECTYPE,ORDUNIQ,LINENUM,OPTFIELD
1,239143,239143-081224,239143,"","","","","","","","","",,,"","this is where po number should be",1,20240812,20240812,03MAIL,0.0000,0.0000
2,239143,220300,1,220525,,STDCAT,03MAIL,1,48.000000000000,48.0000,7.2000,0.0000,0
2,239143,220301,1,220520,,STDCAT,03MAIL,1,48.000000000000,48.0000,7.2000,0.0000,0
2,239143,50,2,,MOFRT,,03MAIL,0,0,16.7500,0,,0
2,239143,51,2,,SLSTAX,,03MAIL,0,0,0.0000,0,,0

 

For a non-PBI order: