[Fixed 4.1.57] DB Table [PeriodicConsumptionItems] - 2 columns are misnamed (reversed)

Version 4 V4 Issue
1

@emre, been scratching my head about this for about an hour wondering why my query was pulling a bunch of NULLs, then I realized there are misnamed columns. As shown below, the column names outlined in RED have their names reversed in table [PeriodicConsumptionItems]:

af2520caff8e8ea3.png1001×379 21.3 KB

I realize fixing this is going to break some Custom Reports for some people (yes, I’m thinking about you @gerlandog), but is there a chance this could be corrected sooner rather than later?

3 Likes
2

When I did the reports I noticed something strange but did not pay much attention… the database fields did not sound entirely correct, but again… was more interested in having it working than analyzing, LOL!!!

GOOD!!!

G.

1 Like
3

Wow awesome catch. As it is an issue with FK creation it was a little tricky but I solved it :slight_smile: Will work fine after next update.

2 Likes
4

@emre, It appears the following table has the same problem:

[CostItems]
5

Yeah. I’ve fixed CostItems too. Thanks.

1 Like
6

I want to save this query here for updating later when the fixes are in place. This is a properly constructed Stock Level Report (originally based on query from @gerlandog).

use [SambaPOS4]

SELECT
  wp.[Id] as [WPID]
--, wp.[StartDate] as [WPBeg]
--, wp.[EndDate] as [WPEnd]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, pci.[PeriodicConsumptionId] [pcIDbad]
--, wc.[Id] as [wcID]
--, pci.[WarehouseConsumptionId] as [wcIDbad]
--, wc.[WarehouseId] as [whID]
, wh.[Name] as [Warehouse]
, ii.[GroupCode] as [ItemType]
, ii.[Name] as [Item]
, ii.[TransactionUnit]
, CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [QLarge]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[BaseUnit]
, CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as [QSmall]

FROM [PeriodicConsumptionItems] pci
--left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
--above line will work for next version (4.1.56), until upgrade, must use following line (4.1.55)
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[WarehouseConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]

WHERE 1=1
-- ensure we are pulling data for latest CLOSED WorkPeriod
 AND wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
-- AND  pci.[WarehouseConsumptionId]=wc.[Id]
-- above line will work for next version (4.1.56), until upgrade, must use following line (4.1.55)
 AND pci.[PeriodicConsumptionId]=wc.[Id]
-- filter by Warehouse Type... comment line to show all Warehouse Types
 AND wh.[WarehouseTypeId]=(select [Id] from [WarehouseTypes] where [Name]='Local Warehouses'
-- filter by Warehouse Name... comment line to show all Warehouses
 AND wh.[Name]='SHOP'
)

ORDER BY [Warehouse], [ItemType], [Item]

Result set examples:

Warehouse SHOP:

Warehouse BODEGA:

2 Likes
7

Yay! As of v4.1.57, the column names are correct, so the following query works. @gerlandog, you will need to update your SQL…

use [SambaPOS4]

    SELECT
      wp.[Id] as [WPID]
    --, wp.[StartDate] as [WPBeg]
    --, wp.[EndDate] as [WPEnd]
    --, pci.[Id] as [pciID]
    --, pc.[Id] as [pcID]
    --, pci.[PeriodicConsumptionId] [pcIDbad]
    --, wc.[Id] as [wcID]
    --, pci.[WarehouseConsumptionId] as [wcIDbad]
    --, wc.[WarehouseId] as [whID]
    , wh.[Name] as [Warehouse]
    , ii.[GroupCode] as [ItemType]
    , ii.[Name] as [Item]
    , ii.[TransactionUnit]
    , CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [QLarge]
    , pci.[UnitMultiplier] as [Multiplier]
    , ii.[BaseUnit]
    , CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as [QSmall]

    FROM [PeriodicConsumptionItems] pci
    left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
    left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
    left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
    left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
    left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]

    WHERE 1=1
    -- ensure we are pulling data for latest CLOSED WorkPeriod
     AND wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
     AND  pci.[WarehouseConsumptionId]=wc.[Id]
    -- filter by Warehouse Type... comment line to show all Warehouse Types
     AND wh.[WarehouseTypeId]=(select [Id] from [WarehouseTypes] where [Name]='Local Warehouses'
    -- filter by Warehouse Name... comment line to show all Warehouses
     AND wh.[Name]='SHOP'
    )

    ORDER BY [Warehouse], [ItemType], [Item]
1 Like