This article demonstrates an example of a Custom Stock Movement Detailed by Shift Report. Disclaimer: This report may need modifications or alterations base on each system setup and configuration.
This article demonstrates an example of a Custom Stock Movement Detailed by Shift Report.
Disclaimer:
This report may need modifications or alterations base on each system setup and configuration.
Report Query:
<?xml version="1.0" encoding="utf-8"?>
<cViews xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<iListType>25</iListType>
<dtDateUpdated>2014-07-18T13:39:12</dtDateUpdated>
<iOperatorID>0</iOperatorID>
<sName>StockMovementDetailedByShift</sName>
<sViewXML><?xml version="1.0" encoding="utf-16"?><cCustomReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><sReportName>Stock Movement Detailed Shift</sReportName><sSQL>SELECT
(SELECT Product.Name FROM Product WHERE ProductID = (SELECT ParentId FROM Product WHERE ProductID = StkLine.ProductID)) AS GroupName,
(SELECT Product.Name From Product WHERE ProductID = StkLine.ProductID) AS ProductName,
(SUM(QtyOnHandStart)) - (SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType Between 1 AND 4 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Qty_Start,
(SUM(StkLine.CostExStart)) - (SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType Between 1 AND 4 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Cost_Start_Ex,
(SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 1 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Qty_Recieved,
(SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 1 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Cost_Recieved_Ex,
(SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 4 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Qty_Adjust,
(SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 4 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Cost_Adjust_Ex,
(SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 3 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Qty_Sold,
(SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 3 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Cost_Sold_Ex,
SUM(QtyOnHandStart) Qty_End,
SUM(StkLine.CostexStart) Cost_End,
SUM(QtyOnHandEnd) As Stock_Counted,
(SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Qty_StokeTake_Var,
CASE WHEN (SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5) = 0 THEN 0 ELSE (SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5)/(SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5) END AS Unit_Cost_StokeTake_Var_Ex,
(SELECT SUM(CostExAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5 AND ShiftID between {ShiftFrom} and {ShiftTo}) AS Total_Cost_StokeTake_Var_Ex,
CASE WHEN SUM(QtyOnHandStart) = 0 THEN 0 ELSE (SELECT SUM(QtyOHAdjust) FROM StockVar WHERE ProductID = StkLine.ProductID AND AdjType = 5 AND ShiftID between {ShiftFrom} and {ShiftTo}) / SUM(QtyOnHandStart) * 10000 END AS Variance_Per
FROM StkLine, StkHead
WHERE StkLine.StkHeadID = StkHead.StkHeadID AND StkLine.ProductID IN (SELECT ProductID FROM Product WHERE NonStocked = 0 AND IsParent = 0) AND StkHead.ShiftID between {ShiftFrom} and {ShiftTo}
GROUP BY StkLine.ProductID
</sSQL><oColumnDefs><dictionary><item><key>0</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;0&lt;/sKey&gt;&lt;sText&gt;Group Name&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>1</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;1&lt;/sKey&gt;&lt;sText&gt;Product Name&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Text&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>2</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;2&lt;/sKey&gt;&lt;sText&gt;Qty Start&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>3</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;3&lt;/sKey&gt;&lt;sText&gt;Cost Start Ex&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>4</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;4&lt;/sKey&gt;&lt;sText&gt;Qty Recieved&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>5</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;5&lt;/sKey&gt;&lt;sText&gt;Cost Recieved Ex&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>6</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;6&lt;/sKey&gt;&lt;sText&gt;Qty Adjusted&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>7</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;7&lt;/sKey&gt;&lt;sText&gt;Cost Adjusted Ex&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>8</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;8&lt;/sKey&gt;&lt;sText&gt;Qty Sold&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>9</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;9&lt;/sKey&gt;&lt;sText&gt;Cost Sold Ex&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>10</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;10&lt;/sKey&gt;&lt;sText&gt;Qty End&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>11</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;11&lt;/sKey&gt;&lt;sText&gt;Cost Ex End&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>12</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;12&lt;/sKey&gt;&lt;sText&gt;Stock Counted Cost&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>13</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;13&lt;/sKey&gt;&lt;sText&gt;Stock Counted&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>14</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;14&lt;/sKey&gt;&lt;sText&gt;Qty Stocktake Var&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Dec2Dbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>15</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;15&lt;/sKey&gt;&lt;sText&gt;Total Cost Stocktake Var Ex&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;Yes&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;CurrDbl&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item><item><key>16</key><type>Xfer.cColInfo</type><value>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;cColInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;&lt;sKey&gt;16&lt;/sKey&gt;&lt;sText&gt;Variance %&lt;/sText&gt;&lt;Hidden&gt;No&lt;/Hidden&gt;&lt;Read_only&gt;Yes&lt;/Read_only&gt;&lt;Total&gt;No&lt;/Total&gt;&lt;FixedColumn&gt;No&lt;/FixedColumn&gt;&lt;ParentCol&gt;No&lt;/ParentCol&gt;&lt;eFormat&gt;Perc2Int&lt;/eFormat&gt;&lt;eList&gt;None&lt;/eList&gt;&lt;/cColInfo&gt;</value></item></dictionary></oColumnDefs></cCustomReport></sViewXML>
</cViews>