New St Proc WarehouseSOH Summary
Posted:
Tue Oct 28, 2014 2:46 pm
by 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
Re: New St Proc WarehouseSOH Summary
Posted:
Tue Oct 28, 2014 4:25 pm
by Mike.Sheen
2can2 wrote:"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.
Re: New St Proc WarehouseSOH Summary
Posted:
Tue Oct 28, 2014 4:54 pm
by 2can2
Excellent, thanks for the rapid reply Mike. So simple!
Re: New St Proc WarehouseSOH Summary
Posted:
Tue Oct 28, 2014 6:34 pm
by Mike.Sheen
2can2 wrote: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
Re: New St Proc WarehouseSOH Summary
Posted:
Fri Nov 07, 2014 11:27 am
by 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.
Re: New St Proc WarehouseSOH Summary
Posted:
Fri Nov 07, 2014 5:16 pm
by Mike.Sheen
2can2 wrote:Great Mike. Exactly what I wanted!
Excellent - glad to be of help!
2can2 wrote: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.