Reason I am asking is that I have a quantity showing in site [blank] which happens to be the total of the other two combined:
I did have a entry in PlantWhse for this part with a blank site but I, um, made that go away*, and this still persists.
*I don’t want to say what I did ahem but it’s the same thing that this fix from Epicor support did, which gets rid of bogus PlantWhse records:
SQL code from support
-- ---------------------------------------------------------------------------------------------------------
-- <GEN DATE>04/18/2019 11:29:59</GEN DATE>
-- <MODIFIED>NO </MODIFIED>
/* <KNOWLEDGE> */
/* <ShortDescr>
Delete PlantWhse if its Plant and WarehouseCode cannot be found in Warehse
</ShortDescr> */
/* <meta>
Part Tracker, PlantWhse, 17079ESC
</meta> */
/* <Issue>
Duplicated records found under On Hand -> Bins tab in Part Tracker
</Issue> */
/* <WhatSelected>
PlantWhse record if its Plant and WarehouseCode cannot be found in Warehse
</WhatSelected> */
/* <WhatProgramDoes>
Delete PlantWhse for the selected records
</WhatProgramDoes> */
/* </KNOWLEDGE> */
/* <FIX DESCRIPTION> */
/* Delete PlantWhse If WarehouseCode Does Not Exist In Warehse */
/* </FIX DESCRIPTION> */
-- ---------------------------------------------------------------------------------------------------------
DECLARE
@Company nvarchar(max) = '<ALL>' --** allok
, @Plant nvarchar(max) = '<ALL>' --** allok (PlantWhse.Plant)
, @WarehouseCode nvarchar(max) = '<ALL>' --** allok (PlantWhse.WarehouseCode)
, @updind bit = 1 -- 0 = Display Only/ 1 = Display then Update
DECLARE @sysrowid uniqueidentifier
declare @updinfo table (
PlantWhse_Company nvarchar(8),
PlantWhse_Plant nvarchar(8),
PlantWhse_WarehouseCode nvarchar(8),
PlantWhse_PartNum nvarchar(50),
sysrowid uniqueidentifier,
rowid_112233 int identity(1,1))
DECLARE @companyP_company nvarchar(8), @companyP_sysrow uniqueidentifier
DECLARE @PlantWhse_Company nvarchar(8), @PlantWhse_PartNum nvarchar(50), @PlantWhse_Plant
nvarchar(8), @PlantWhse_WarehouseCode nvarchar(8), @PlantWhse_sysrow uniqueidentifier
DECLARE @Warehse_sysrow uniqueidentifier
DECLARE companyP_cursor cursor LOCAL STATIC FORWARD_ONLY READ_ONLY for
select companyP.company, companyP.sysrowid
from Erp.company companyP
join ice.SysCompany s on s.company = companyp.Company -- ds generated Install Link
where (companyP.company = case when @Company = '<ALL>' then companyP.company else @Company end)
order by companyP.company
OPEN companyP_cursor
FETCH NEXT FROM companyP_cursor into @companyP_company, @companyP_sysrow
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE PlantWhse_cursor cursor LOCAL STATIC FORWARD_ONLY READ_ONLY for
select PlantWhse.Company, PlantWhse.PartNum, PlantWhse.Plant, PlantWhse.WarehouseCode,
PlantWhse.sysrowid
from Erp.PlantWhse PlantWhse
where PlantWhse.company = @companyP_company and (PlantWhse.Plant = case when @Plant = '<ALL>' then PlantWhse.Plant
else @Plant end) and (PlantWhse.WarehouseCode = case when @WarehouseCode = '<ALL>' then PlantWhse.WarehouseCode
else @WarehouseCode end)
OPEN PlantWhse_cursor
FETCH NEXT FROM PlantWhse_cursor into @PlantWhse_Company, @PlantWhse_PartNum, @PlantWhse_Plant,
@PlantWhse_WarehouseCode, @PlantWhse_sysrow
WHILE @@FETCH_STATUS = 0
BEGIN
If NOT Exists (select 1
from Erp.Warehse Warehse
where (Warehse.Company = @PlantWhse_Company and Warehse.WarehouseCode = @PlantWhse_WarehouseCode and
Warehse.Plant = @PlantWhse_Plant))
BEGIN
insert @updinfo (PlantWhse_Company, PlantWhse_Plant, PlantWhse_WarehouseCode,
PlantWhse_PartNum, sysrowid)
select @PlantWhse_Company, @PlantWhse_Plant, @PlantWhse_WarehouseCode,
@PlantWhse_PartNum, @PlantWhse_sysrow
END
FETCH NEXT FROM PlantWhse_cursor into @PlantWhse_Company, @PlantWhse_PartNum,
@PlantWhse_Plant, @PlantWhse_WarehouseCode, @PlantWhse_sysrow
END
CLOSE PlantWhse_cursor;
DEALLOCATE PlantWhse_cursor;
FETCH NEXT FROM companyP_cursor into @companyP_company, @companyP_sysrow
END
CLOSE companyP_cursor;
DEALLOCATE companyP_cursor;
select PlantWhse_Company, PlantWhse_Plant, PlantWhse_WarehouseCode, PlantWhse_PartNum
from @updinfo
order by rowid_112233
-- ---------------------------------------------------------------------------------------------------------
-- Data Update Section - Do Not Remove
-- ---------------------------------------------------------------------------------------------------------
if @updind = 1
begin
DECLARE Upd_cursor cursor LOCAL STATIC FORWARD_ONLY READ_ONLY for
select PlantWhse_Company, PlantWhse_Plant, PlantWhse_WarehouseCode, PlantWhse_PartNum, sysrowid
from @updinfo
order by rowid_112233
OPEN Upd_cursor
FETCH NEXT FROM Upd_cursor into @PlantWhse_Company, @PlantWhse_Plant, @PlantWhse_WarehouseCode,
@PlantWhse_PartNum, @PlantWhse_sysrow
WHILE @@FETCH_STATUS = 0
begin
set @sysrowid = @PlantWhse_sysrow
begin tran
delete from Erp.PlantWhse
where sysrowid = @sysrowid
commit tran
FETCH NEXT FROM Upd_cursor into @PlantWhse_Company, @PlantWhse_Plant,
@PlantWhse_WarehouseCode, @PlantWhse_PartNum, @PlantWhse_sysrow
end
end
It doesn’t really harm anything to have a bogus extra row in time phase, but it does bother me.
