Sage 100

Thank you - there was an issue with a VI job and I definitely could see these files being all forked over right now

Thank you - there was an issue with a VI job and I definitely could see these files being all forked over right now

I have a client who used to get this type of error quite a bit. I created a simple Crystal Report that showed all unposted serial numbers that were in invoice data entry. This cut down on some of the issues.

As noted, 99.9% of the time the rebuild sorts for inventory management should clear any stray committed values

Rebuild Sort Files clears up false committed flags (in IM_ItemCost).

Legit committed values can be cleared up by normal data entry activities.

Orphaned rows can exist in the tier distribution tables too, where you need to clean them up via DFDM... here is a query we use to help find orphans.

select * from BM_ProductionTierDistribution where ProductionNo not in (select ProductionNo from BM_ProductionHeader);
select * from BM_DisassemblyTierDistribution where DisassemblyNo not in (select DisassemblyNo from BM_DisassemblyHeader);
select * from IM_TransactionTierDist where EntryNo not in (select EntryNo from IM_TransactionHeader);
select * from SO_InvoiceTierDistribution where InvoiceNo not in (select InvoiceNo from SO_InvoiceHeader);
select * from SO_SalesOrderTierDistribution where SalesOrderNo not in (select SalesOrderNo from SO_SalesOrderHeader);
select * from PO_ReceiptTierDistribution where ReceiptNo not in (select ReceiptNo from PO_ReceiptTierDistribution);
select * from PO_ReturnTierDistribution where ReturnNo not in (select ReturnNo from PO_ReturnHeader);
select * from PO_MaterialReqTierDistribution where IssueNo not in (select IssueNo from PO_MaterialReqHeader);

I have a client who used to get this type of error quite a bit. I created a simple Crystal Report that showed all unposted serial numbers that were in invoice data entry. This cut down on some of the issues.

As noted, 99.9% of the time the rebuild sorts for inventory management should clear any stray committed values

Thanks for the info Kevin.  I didn't know rebuild sort files cleared up incorrect committed quanities in IM_ItemCost.  I just set one to 0 yesterday for a FIFO item.  if there are only a few it probably makes sense to use DFDM so you don;t have to kick all of the users out. 

Rebuild Sort Files clears up false committed flags (in IM_ItemCost).

Legit committed values can be cleared up by normal data entry activities.

Orphaned rows can exist in the tier distribution tables too, where you need to clean them up via DFDM... here is a query we use to help find orphans.

select * from BM_ProductionTierDistribution where ProductionNo not in (select ProductionNo from BM_ProductionHeader);
select * from BM_DisassemblyTierDistribution where DisassemblyNo not in (select DisassemblyNo from BM_DisassemblyHeader);
select * from IM_TransactionTierDist where EntryNo not in (select EntryNo from IM_TransactionHeader);
select * from SO_InvoiceTierDistribution where InvoiceNo not in (select InvoiceNo from SO_InvoiceHeader);
select * from SO_SalesOrderTierDistribution where SalesOrderNo not in (select SalesOrderNo from SO_SalesOrderHeader);
select * from PO_ReceiptTierDistribution where ReceiptNo not in (select ReceiptNo from PO_ReceiptTierDistribution);
select * from PO_ReturnTierDistribution where ReturnNo not in (select ReturnNo from PO_ReturnHeader);
select * from PO_MaterialReqTierDistribution where IssueNo not in (select IssueNo from PO_MaterialReqHeader);

I have a client who used to get this type of error quite a bit. I created a simple Crystal Report that showed all unposted serial numbers that were in invoice data entry. This cut down on some of the issues.

As noted, 99.9% of the time the rebuild sorts for inventory management should clear any stray committed values

For FIFO you probably need to check IM_DataEntryCostCalcCommit, which is the table that locks tier quantities during the posting process (after printing the journal, before the update is completed). 

I was not aware IM_ItemCost rows were ever marked as committed for FIFO tiers.

Thanks for the info Kevin.  I didn't know rebuild sort files cleared up incorrect committed quanities in IM_ItemCost.  I just set one to 0 yesterday for a FIFO item.  if there are only a few it probably makes sense to use DFDM so you don;t have to kick all of the users out. 

Rebuild Sort Files clears up false committed flags (in IM_ItemCost).

Legit committed values can be cleared up by normal data entry activities.

Orphaned rows can exist in the tier distribution tables too, where you need to clean them up via DFDM... here is a query we use to help find orphans.

select * from BM_ProductionTierDistribution where ProductionNo not in (select ProductionNo from BM_ProductionHeader);
select * from BM_DisassemblyTierDistribution where DisassemblyNo not in (select DisassemblyNo from BM_DisassemblyHeader);
select * from IM_TransactionTierDist where EntryNo not in (select EntryNo from IM_TransactionHeader);
select * from SO_InvoiceTierDistribution where InvoiceNo not in (select InvoiceNo from SO_InvoiceHeader);
select * from SO_SalesOrderTierDistribution where SalesOrderNo not in (select SalesOrderNo from SO_SalesOrderHeader);
select * from PO_ReceiptTierDistribution where ReceiptNo not in (select ReceiptNo from PO_ReceiptTierDistribution);
select * from PO_ReturnTierDistribution where ReturnNo not in (select ReturnNo from PO_ReturnHeader);
select * from PO_MaterialReqTierDistribution where IssueNo not in (select IssueNo from PO_MaterialReqHeader);

I have a client who used to get this type of error quite a bit. I created a simple Crystal Report that showed all unposted serial numbers that were in invoice data entry. This cut down on some of the issues.

As noted, 99.9% of the time the rebuild sorts for inventory management should clear any stray committed values

Yes.  That solved the issue.  They were trying to make a -1 inventory adjustment and when trying to distribute the quantity to a tier the quantity to distribute was not available even though there was 1 on hand.  i changed the committed field in IM_ItemCost to 0 and the quantity was then available to distribute.  Similar to a serial item.

For FIFO you probably need to check IM_DataEntryCostCalcCommit, which is the table that locks tier quantities during the posting process (after printing the journal, before the update is completed). 

I was not aware IM_ItemCost rows were ever marked as committed for FIFO tiers.

Thanks for the info Kevin.  I didn't know rebuild sort files cleared up incorrect committed quanities in IM_ItemCost.  I just set one to 0 yesterday for a FIFO item.  if there are only a few it probably makes sense to use DFDM so you don;t have to kick all of the users out. 

Rebuild Sort Files clears up false committed flags (in IM_ItemCost).

Legit committed values can be cleared up by normal data entry activities.

Orphaned rows can exist in the tier distribution tables too, where you need to clean them up via DFDM... here is a query we use to help find orphans.

select * from BM_ProductionTierDistribution where ProductionNo not in (select ProductionNo from BM_ProductionHeader);
select * from BM_DisassemblyTierDistribution where DisassemblyNo not in (select DisassemblyNo from BM_DisassemblyHeader);
select * from IM_TransactionTierDist where EntryNo not in (select EntryNo from IM_TransactionHeader);
select * from SO_InvoiceTierDistribution where InvoiceNo not in (select InvoiceNo from SO_InvoiceHeader);
select * from SO_SalesOrderTierDistribution where SalesOrderNo not in (select SalesOrderNo from SO_SalesOrderHeader);
select * from PO_ReceiptTierDistribution where ReceiptNo not in (select ReceiptNo from PO_ReceiptTierDistribution);
select * from PO_ReturnTierDistribution where ReturnNo not in (select ReturnNo from PO_ReturnHeader);
select * from PO_MaterialReqTierDistribution where IssueNo not in (select IssueNo from PO_MaterialReqHeader);

I have a client who used to get this type of error quite a bit. I created a simple Crystal Report that showed all unposted serial numbers that were in invoice data entry. This cut down on some of the issues.

As noted, 99.9% of the time the rebuild sorts for inventory management should clear any stray committed values