4 | Accounts Payable with Invoice Subtotals (Custom Report)

Report Break Down

StockVar Table Fields Used

Name	Description
Report Fields

TransactionID	Used as the purchase ID in the StockVar table. Used in the query to select only purchase records, to retrieve those records grouped by purchase, and to add in the Totals line
DateTimeAdj	Time stamp of the purchase
StoreID	Used to retrieve Store Name from Store table
ProductID	Used to retrieve Product Name and Number from Product table
AdjType	Stock adjustment type 1 = Purchase 2 = Unknown 3 = Sales 4 = Manual Adjust 5 = Stock Take 6 Re-Initialize 7 = Transfer In 8 = Transfer Out 9 = Loss 10 = Unknown 11 = Cost Correction 12 = Unknown
QtyOHAdj	Purchased quantity
CostExAdjust	Cost of purchase Ex Tax
OperatorID	Used to retrieve Operator Name from the Operator table
Descript	Invoice description
SupplierID	Used to retrieve Supplier Name from the Supplier table

Custom Formulas

SELECT DISTINCT INTO creates a temporary table with a list of unique purchase only transactionIDs within the selected ranges

CREATE TABLE Result creates a temporary table to hold the modified data

SELECT @Count = COUNT(*) FROM TransList Initializes the WHILE loop. Will be decremented each pass

BEGIN the WHILE loop

SELECT TOP 1 @LoopCounter = Trans FROM TransList Gets the top transactionID from the TransList table

Collect all the data from StockVar for the transaction, add the totals and put them in to the result table

Delete the used TransactionID from TransList and reset the Count.

END

JOIN the external tables for Names and run the report

DROP the 2 temporary tables

Purchases with invoice totals Collapse source

SELECT DISTINCT TransactionID AS Trans

INTO Translist

FROM dbo.StockVar

WHERE AdjType = 1 AND (DateTimeAdj >= {DATEFROM} AND DateTimeAdj <= {DATETO})

AND (StoreID LIKE '{STOREID}') AND (VenueID LIKE '{VENUEID}' )

AND SupplierID LIKE '{SUPPLIERID}'

CREATE TABLE Result

(DTAdj DATETIME,

StoreID INT,

ProductID INT,

AdjType INT,

QtyAdj FLOAT,

CExAdj FLOAT,

OperatorID INT,

Descript VARCHAR(40),

TransID INT,

SuppID INT

)

DECLARE @LoopCounter INT , @Count INT,

@Date DATETIME, @Store INT, @Prod INT, @Type INT, @Qty FLOAT,

@CEx FLOAT, @Op INT, @Script VARCHAR(40), @TID INT, @Supp INT

SELECT @Count = COUNT(*) FROM TransList

WHILE (@Count > 0)

BEGIN

SELECT TOP 1 @LoopCounter = Trans FROM TransList

SELECT

@Date = DateTimeadj,

@Store = StoreID,

@Prod = ProductID,

@Type =AdjType,

@Qty = QtyOHAdjust,

@CEx = CostExAdjust,

@Op = OperatorID,

@Script = Description,

@TID = TransactionID,

@Supp = SupplierID

FROM dbo.StockVar as v WHERE TransactionID = @LoopCounter

INSERT INTO Result (DTAdj, StoreID, ProductID, AdjType, QtyAdj, CExAdj, OperatorID, Descript, TransID, SuppID)

SELECT

DateTimeadj,

StoreID,

ProductID,

AdjType,

QtyOHAdjust,

CostExAdjust,

OperatorID,

Description,

TransactionID,

SupplierID

FROM dbo.StockVar as v WHERE TransactionID = @LoopCounter AND v.AdjType <> 11

INSERT INTO Result (DTAdj, StoreID, QtyAdj, CExAdj, Descript, TransID)

VALUES

(@Date + '00:00:01',

@Store,

(SELECT SUM(QtyOHAdjust) FROM dbo.Stockvar WHERE TransactionID = @LoopCounter),

(SELECT SUM(CostExAdjust) FROM dbo.Stockvar WHERE TransactionID = @LoopCounter),

'TOTAL',

@TID)

DELETE FROM Translist WHERE Trans = @Loopcounter

SELECT @Count = COUNT(*) FROM TransList

END

Select

v.DTAdj as [DateTime Adjustment],

r.Name as [Store Name],

p.ProdNumber as [Product Number],

p.Name as [Product Name],

CASE

WHEN v.AdjType = 1 THEN 'Purchase'

WHEN v.AdjType = 2 THEN 'Unknown Type 2'

WHEN v.AdjType = 3 THEN 'Sales'

WHEN v.AdjType = 4 THEN 'Manual Adjust'

WHEN v.AdjType = 5 THEN 'Stock Take'

WHEN v.AdjType = 6 THEN 'Re-Initialize'

WHEN v.AdjType = 7 THEN 'Transfer In'

WHEN v.AdjType = 8 THEN 'Transfer Out'

WHEN v.AdjType = 9 THEN 'Loss'

WHEN v.AdjType = 10 THEN 'Unknown Type 10'

WHEN v.AdjType = 11 THEN 'Cost Correction'

WHEN v.AdjType = 12 THEN 'Unknown Type 12'

END AS [Adjust Type],

v.QtyAdj as [Qty Adjust],

v.CExAdj as [CostEx Adjust],

o.FirstName + ' ' + o.Lastname as [Operator Name],

v.Descript as [Description],

v.TransID as [Transaction ID],

s.Name as [Supplier Name]

From Result as v

LEFT JOIN dbo.Store as r ON v.StoreID = r.StoreID

LEFT JOIN dbo.Product as p ON p.ProductID = v.ProductID

LEFT JOIN dbo.Supplier as s ON v.SuppID = s.SupplierID

LEFT JOIN dbo.Operator as o ON v.OperatorID = o.OperatorID

--WHERE v.DTAdj > '2018-10-15 16:00:00' AND v.AdjType = 1

ORDER BY v.TransID, v.DTAdj

DROP TABLE Translist

DROP TABLE Result