New St Proc WarehouseSOH Summary

[2can2]

Hi, I have an issue with the new V70082 st proc Jiwa_Warehouse_SOH_Summary. I had a modified version previously running on V70079 but have now put those changes into the latest standard version with a schema but still have an issue : "Error : Column name or number of supplied values does not match table definition. Module : On Error"
I have just added one extra field "TotalWeight" defined as follows :
1. In the Schema -
-- define Total Weight
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
SELECT 'TotalWeight', 'Total KG', 1, 6, 0, NULL, NULL, NULL, 'QuantityDecimalPlaces'
2. At the end of the Table definition -
TotalForecastWorkOrderOutputWIP DECIMAL(19,6),
TotalWeight DECIMAL(19,6)
)
3. In the Logic -
-- PM Update Total Weight
UPDATE #tmpWarehouseSOH SET TotalWeight = ISNULL((
SELECT SUM(dbo.LON_IN_SOHCustomValues.WeightLeft)
FROM dbo.IN_SOH INNER JOIN
dbo.LON_IN_SOHCustomValues ON dbo.IN_SOH.LinkID = dbo.LON_IN_SOHCustomValues.IN_SOHID
WHERE IN_SOH.InventoryID = @InventoryID AND IN_SOH.IN_LogicalID = #tmpWarehouseSOH.INLogicalID
),0) + 0.01
4. At the end in the table output -
TotalForecastWorkOrderOutputWIP,
TotalWeight,
@DecimalPlaces [QuantityDecimalPlaces]
FROM #tmpWarehouseSOH
GO

Only one field added, same code as used previously so not sure where to now? I have attached full stored proc.
Thanks for the help in advance.
Cheers

[Mike.Sheen]

"Error : Column name or number of supplied values does not match table definition. Module : On Error"

You need to modify the insert of the temporary column you added the new column to to include an initial value.

I.E.: change

Code: Select all

INSERT INTO #tmpWarehouseSOH
SELECT IN_Logical.IN_LogicalID, IN_Physical.IN_PhysicalID, IN_Logical.Description + ' / ' + IN_Physical.Description, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM IN_Logical JOIN IN_Physical ON (IN_Logical.IN_PhysicalID = IN_Physical.IN_PhysicalID)
ORDER BY IN_Physical.Description, IN_Logical.Description

To

Code: Select all

INSERT INTO #tmpWarehouseSOH
SELECT IN_Logical.IN_LogicalID, IN_Physical.IN_PhysicalID, IN_Logical.Description + ' / ' + IN_Physical.Description, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM IN_Logical JOIN IN_Physical ON (IN_Logical.IN_PhysicalID = IN_Physical.IN_PhysicalID)
ORDER BY IN_Physical.Description, IN_Logical.Description, 0

Note the only difference is I added the ,0 to the end of the query.

[2can2]

Excellent, thanks for the rapid reply Mike. So simple!

[Mike.Sheen]

Excellent, thanks for the rapid reply Mike. So simple!

You're welcome

I note that you're using the "QuantityDecimalPlaces" column as the source of precision for the new column you added, TotalWeight - but that "QuantityDecimalPlaces" column is set to be the inventory decimal places. I think you may want to change it so it uses a fixed value, ie: 2 or something - otherwise your weight will be displayed always to the precision of the inventory item quantity decimals - which probably isn't what you want - weights are normally a fixed level of precision, unlike quantities.

So, what I would do is:

1. Add a weight decimal places column to the schema:

Code: Select all

INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'WeightDecimalPlaces', 'WeightDecimalPlaces', 0, 0, 0, NULL, NULL, NULL, NULL

2. Modify your "TotalWeight" schema insert to refer to the new "WeightDecimalPlaces" column for its precision:

Code: Select all

INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'TotalWeight', 'Total KG', 1, 6, 0, NULL, NULL, NULL, 'WeightDecimalPlaces'

3. Modify your final SELECT to use a literal for the "WeightDecimalPlaces" column output:

Code: Select all

SELECT INLogicalID,
   INPhysicalID,
   WarehouseDescription,
   'JiwaFinancials.Jiwa.JiwaWarehousesUI.frmMain' PhysicalWarehouseDrillDownClassName,
   TotalSOH,
    TotalBackOrder,
   TotalManualBackOrder,
   TotalAutoBackOrder,
   TotalShipComplete,
   TotalWarehouseTransfer,
   TotalUnprocessed,
   TotalForwardRequirements,
   TotalWorkOrderComponentWIP,
   TotalForecastWorkOrderComponentWIP,
   TotalWorkOrderOutputWIP,
   TotalForecastWorkOrderOutputWIP,
   TotalWeight,
   @DecimalPlaces [QuantityDecimalPlaces],
   2 [WeightDecimalPlaces]
FROM #tmpWarehouseSOH

And then your total weight column should always to be to 2 decimals of precision (assuming you wanted that). Note: I've not tested this - there could be errors in my work

Mike

[2can2]

Great Mike. Exactly what I wanted!
I guess this new schema design may have been prompted by my and others wanting a different no of decimals displayed for one field as opposed to the standard master.
Thanks again.

[Mike.Sheen]

Great Mike. Exactly what I wanted!

Excellent - glad to be of help!

I guess this new schema design may have been prompted by my and others wanting a different no of decimals displayed for one field as opposed to the standard master.

Yes, that and the fact I hated using hard-to-read XML to populate grids from stored procedures - that made it hard to troubleshoot when things weren't working as expected.