Where does Time Phase OH Qty come from?

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>
/* <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> */
/* Delete PlantWhse If WarehouseCode Does Not Exist In Warehse */
-- ---------------------------------------------------------------------------------------------------------
	 @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
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
    select PlantWhse.Company, PlantWhse.PartNum, PlantWhse.Plant, PlantWhse.WarehouseCode, 
     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
        If NOT Exists (select 1
         from Erp.Warehse Warehse
         where (Warehse.Company = @PlantWhse_Company and Warehse.WarehouseCode = @PlantWhse_WarehouseCode and 
            Warehse.Plant = @PlantWhse_Plant))
            insert @updinfo (PlantWhse_Company, PlantWhse_Plant, PlantWhse_WarehouseCode, 
                PlantWhse_PartNum, sysrowid)
            select @PlantWhse_Company, @PlantWhse_Plant, @PlantWhse_WarehouseCode, 
                @PlantWhse_PartNum, @PlantWhse_sysrow

        FETCH NEXT FROM PlantWhse_cursor into @PlantWhse_Company, @PlantWhse_PartNum, 
        @PlantWhse_Plant, @PlantWhse_WarehouseCode, @PlantWhse_sysrow
    CLOSE PlantWhse_cursor;
    DEALLOCATE PlantWhse_cursor;

FETCH NEXT FROM companyP_cursor into @companyP_company, @companyP_sysrow
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
    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
        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

It doesn’t really harm anything to have a bogus extra row in time phase, but it does bother me.

Hey Jason, iirc it should be the PartDtl table.

Sorry, but I don’t think so. Most of the rows are from there, but OH qty is not.

I just did a quick BAQ to confirm. Only source types (for me on this part today) are JM, PO and TO. There’s no spot for on-hand.

The top rows of time phase come from other places; sometimes there are parts pending PO inspection approval that show on separate rows as well. See pic below.

So I know it’s a union of several tables/queries. And I thought OH was a mixture of PlantWhse and maybe PartQty or PartWhse.


Have you tried running the clean up process for that part?


Well, I’ll never use “refresh partbin.” I have a long-standing opposition to that as it’s terribly inaccurate and detrimental. Also, our OH quantities are fine.

The other one, no, not recently. Worth a try. Definitely filter on that one part as you say. I tried it wide open a few weeks ago and after like 9 hours it got nowhere.

1 Like

@JasonMcD I use PartWhse for my TSQL TimePhase query. It’s working well for us. I’d be happy to share my query if you want to see it.

I use PartBin to measure any inventory levels because we had an issue where PartWhse wasnt lining up with PartBin sum. Epicor support told me there is an issue (we were on 10.2.200 at the time) where PartWhse sometimes doesnt update when a transaction takes place. He is the one that told me to use those Refresh processes to fix it for the parts we come across.

@MikeGross Thank you, but I actually have a BAQ of my own that we use for TimePhase and it works well.

My problem isn’t getting the right info. My problem is, Why does Epicor use the wrong info, and where is it coming from?

1 Like

And I can completely agree and see where the “Refresh… Allocations” process would work perfectly for that. I do plan to try it again.

But “Refresh PartBin” is bad because, for one example, if a part goes from qty-bearing to non-qty-bearing and back again, the process doesn’t know how to ignore that part since, for example, STK-MTL has no indication of qty-bearing at the time of the transaction. This refresh process treats them all as if they were QB, and so you get inflated OH numbers.

1 Like

Hi Jason,

When you look at the tree structure in Part Maintenance, any chance when you expand the tree you have a “bad part warehouse” on there? Or perhaps a “bad part site”?



Huh. Sure does.


But riddle me this. EpiBinding is supposed to be PartPlant, yet I only have two entries in PartPlant for this part number.


Nah, I definitely had Part Warehouse record due to bad PlantWarehouse records, not in PartPlant. I suspect PlantWhse, PartBin, or PartWhse has your culprit data. Any blank warehouses in there for this part number? Only other thought would be an old bad PartTran hanging out there. While I think the PartTran causes the bad record (i.e., in PlantWhse, PartBin, or PartWhse) at the time it occurs, I doubt that the outstanding bad PartTran continues to cause bad site or bad warehouse to show in Part tree structure, once the bad record was deleted…

This just gets better.

So, I did a trace of Part Maintenance after trying to modify my phantom part-site record.

It references a SysRowID in PartPlant that does not exist! (But FYI, the SysRowID of the other two real records do match from trace to the DB.)

Now I’m in left field, but is this where a SQL View comes into play? I am so lost right now.

Basically yes, is the answer.

Since I have added UD fields to PartPlant, there is a PartPlant_UD table that also has the bogus row with a blank site.

So, scrolling way over to the right in the PartPlant View, I found the ForeignSysRowID of the PartPlant_UD record and, ahem, made it go away.

The view still has this bogus row, but Time Phase is corrected now. So I’m happy about that, but will the view ever get corrected? SSMS doesn’t seem to let me modify the view row…