Query in Analytics for tracking Physical Available For Sale from Books

Query in Analytics for tracking Physical Available For Sale from Books

Need: a query in Analytics on which we can build reports related to Physical Stock from Books items. 

In Books, each item has an Accounting Stock and a Physical Stock. In the settings you can toggle the 'Mode of Stock tracking' option and we have selected Physical Stock. 

However, there is a note that says "Irrespective of your preferred mode of stock tracking in Zoho Inventory, the integrated Zoho Books organization will always track stock based on Bills and Invoices." 

In Zoho Analytics, it is relatively easy to build reports based on the Accounting stock. Because no matter which 'Mode' you choose for tracking, the report structure is based on Accounting stock. 

We have a need to track by Physical stock. We have developed a query that works 90% or more of the time. We have contacted Zoho about this and requested them to either review our query and provide solution, or to just start over and provide a new query that they build. We have been waiting so far 30 days for a meaningful response or delivery of a query, including the past 6 days with no response at all. 

One challenge is the Stock In Flow table has 4 Quantity fields and the Stock Out Flow table has 2 Quantity fields. It's not clear how these fields work, when they are used or not used, how the interact with each other. We have not been able to find any documentation explaining it. A simple pivot view shows there is some logic to how these fields are used, but it is not consistent enough for us to understand without any other information. For example, what is the different between 'Quantity In' and 'Quantity Physically Tracked'? Why is one used sometimes and not other times? Why is one used for a certain type of entry, but not all entries of the same type? 

I have included the queries that we have created below. A specific case where this query failed was due to one item which had the quantity tracked in the 'Quantity In' column instead of 'Quantity Physically Tracked'. This entry was also of the type 'Credit Note'. However not all entries of 'Credit Note' used 'Quantity In' some of them used it and some used 'Quantity Physically Tracked'. 

The first query is a summary query which is used to build reports on. The second query is the detail query which underlies the summary query. This is the one that actually merges all the different tables and attempts to create a current snapshot of the Physically Available stock for each item. 

Any help is greatly appreciated!

--Summary Query--
SELECT
StockLevelDetail."Product ID" AS 'Product ID',
Items."Item Name" AS 'Item Name',
StockLevelDetail."Quantity" AS 'Stock on Hand',
StockLevelDetail."Direction" AS 'Direction',
'Item' AS 'SourceEntity'
FROM  "Stock Level For Estimates Detail Query" AS  StockLevelDetail
LEFT JOIN "Items" AS  Items ON Items."Item ID"  = StockLevelDetail."Product ID"  
WHERE StockLevelDetail."Product ID"  NOT IN
(
  SELECT CompositeItem."Item ID"
FROM  "Composite Item" AS  CompositeItem 
)
 AND LOWER(Items."Item Name")  NOT LIKE '%digital%'
 AND LOWER(Items."Item Name")  NOT LIKE '%online%'


--Detail Query--
 SELECT
  'In Stock' AS "Direction",
  'StockInFlowTable' AS 'Table',
  StockInFlowTable."Stock In Flow ID" AS "Key",
  StockInFlowTable."Product ID" AS "Product ID",
  StockInFlowTable."Transaction Date" AS "Transaction Date",
  StockInFlowTable."Quantity Physically Tracked" AS "Quantity",
  /*IF(StockInFlowTable."Quantity Physically Tracked">0,StockInFlowTable."Quantity Physically Tracked",StockInFlowTable."Quantity In") AS "Quantity",*/ StockInFlowTable."Total (BCY)" AS "Total"
 FROM  "Stock In Flow Table" AS  StockInFlowTable 
 UNION ALL
  SELECT
  'Out Stock' AS "Direction",
  'StockOutFlowTable' AS 'Table',
  StockOutFlowTable."Stock Out Flow ID",
  StockOutFlowTable."Product ID",
  StockOutFlowTable."Transaction Date",
  (-1 * StockOutFlowTable."Quantity Physically Tracked") AS "Quantity",
  (-1 * SUM(FIFOMappingTable."Total (BCY)"))
 FROM  "Stock Out Flow Table" AS  StockOutFlowTable
 LEFT JOIN "FIFO Mapping Table" AS  FIFOMappingTable ON FIFOMappingTable."Stock Out Flow ID"  = StockOutFlowTable."Stock Out Flow ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
 UNION ALL
  SELECT
  'In PO' AS "Direction",
  'PurchaseOrderItems' AS 'Table',
  PurchaseOrderItems."Item ID",
  PurchaseOrderItems."Product ID",
  PurchaseOrders."Date",
  (PurchaseOrderItems."Quantity Received") AS 'Quantity',
  SUM(PurchaseOrderItems."Total (BCY)")
 FROM  "Purchase Order Items" AS  PurchaseOrderItems
 LEFT JOIN "Purchase Orders" AS  PurchaseOrders ON PurchaseOrders."Purchase Order ID"  = PurchaseOrderItems."Purchase Order ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
 UNION ALL
  SELECT
  'Out SO' as "Direction",
  'SalesOrderItems' AS 'Table',
  SalesOrderItems."Item ID",
  SalesOrderItems."Product ID",
  SalesOrders."Order Date",
  (-1 * SalesOrderItems."Quantity"),
  SUM(SalesOrderItems."Total (BCY)")
 FROM  "Sales Order Items" AS  SalesOrderItems
 LEFT JOIN "Sales Orders" AS  SalesOrders ON SalesOrders."Sales order ID"  = SalesOrderItems."Sales order ID"  
 GROUP BY 1,
  2,
  3,
  4,
  5,
    6 
  
  
  

    Zoho Pagesense Resources

        Zoho SalesIQ Resources



              Zoho TeamInbox Resources

                Zoho DataPrep Resources


                    Zoho CRM Plus Resources

                      Zoho Books Resources


                        Zoho Subscriptions Resources

                          Zoho Desk Resources

                            Zoho Projects Resources


                              Zoho Sprints Resources


                                Qntrl Resources


                                  Zoho Creator Resources


                                    Zoho WorkDrive Resources




                                        Zoho Campaigns Resources

                                          Zoho CRM Resources

                                                      Design. Discuss. Deliver.

                                                      Create visually engaging stories with Zoho Show.

                                                      Get Started Now