Custom Reports
      Back to home
      1. INTERNAL - Bepoz Help Guides
      2. End-User | Reporting, Data Analysis & Security
      3. Custom Reports

      4 | Accounts Payable with Invoice Subtotals (Custom Report)

      Some Venues may want the Stock Journal report filtered for purchases. The report is a continuous list of received records. They want a total inserted for each invoice for reconciliation purposes. Report Break Down StockVar Table Fields Used Report Fields Name Description TransactionID Used as the purchase ID in the StockVar table.

      Report Break Down

       

      StockVar Table Fields Used

      Report Fields
      Name Description
         
      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