x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 17 replies
  • Subscribers 104 subscribers
  • Views 7111 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
964
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
SageAcolyte

Clear out an Inventory Glitch! ...Shows in COST detail - can I manually delete the SQL record in IM_ItemCost?

Posted By

Hello there, I apologize if I am a bit confusing in regards to asking this question.  I need to clear out erroneous records related to inventory.  I am not sure how to do it.

The users noticed an incorrect entry here it is the first record shown, and they tried to delete it but could not.  Then they tried to adjust it with a PHY CNT type of record and that did not 'fix' the first record and now that adjustment record is stuck as well.  

At this point, they emailed me as asked me: Lot #6L1121 should be zero quantity. Can you remove the 824 items?

After hours, when the CI/inventory module was not in use, I tried but Sage/MAS says:

This item is associated with other transactions and cannot be deleted using item maintenance.  use delete and change items...

OK, so I use delete and change items but it says: 

"You cannot proceed with unposted data in Physical Count entry" but there is nothing there.  We can post daily etc w/o problems.  OK so I did a SQL trace and found the data.  Please see the next image...

ok great... so I see the matching records there.  I can try deleting these in a VM copy but I do not know what repercussions it may cause.  What can I do to properly get rid of these records?  Thanks so much!!!!

::UPDATE:: I did manually delete the SQL record (the top one listed in the SQL pane) in the VMWare copy... and this *seemed* to fix the problem but I dont know if this will cause ANY issues down the line.....

  • 0

    sorry forgot to mention, I have 'sanitized' the two images to not show our exact live data... so it might look funny!  Also I noticed that the erroneous record has a blank TierType...not sure if that matters... Thanks again!!!

  • 0 in reply to SageAcolyte
    Do not delete data through the back end, ever.
    Post a return of goods equal to the bad receipt, for the lot #, and that should back the quantity out of inventory properly.
  • 0 in reply to Kevin M

    Thank you for the response. If I am understanding correctly, what you suggest was not working. The record in question was not fully a correct record 'half in and half out' the CSR's explain it to me. It was not adjustable. They are not able to correct it, nor am I able to understand/use the Sage tools to clear it out. Could you please tell me if I am misunderstanding or is there another way? Thanks again

    update

    Kevin M said:
    Do not delete data through the back end, ever. 
    Post a return of goods equal to the bad receipt, for the lot #, and that should back the quantity out of inventory properly.

    Sage themselves directed me to manually delete this corrupted record through the back end (as long as it is missing TierType, they said)

  • 0 in reply to SageAcolyte
    There is a difference between corrupted data and a properly executed transaction that was done in error. My assumption is that you are asking about the second possibility.
    In which case, a proper logically opposite transaction is the correct course of action to fix the data. The transaction trail will still exist, properly, but the "current" on hand values will then be accurate.
    You didn't describe attempting a return. Your notes involved a physical count, which is something _completely_ different.
    You absolutely do not want to do anything with the "Delete and Change Items" utility to correct QoH issues.
    The only idea I have for what you mean by "half in half out" would be a receipt without invoice. My previous suggestion to process a return transaction applies to that scenario. If you don't have the PO module that could be an issue, or a negative quantity receipt. (Process first in a test company to see what works for you).
    Perhaps you might want to contact your Sage Partner / re-seller to assist, hands-on.
  • 0 in reply to Kevin M
    Thanks. OK I see what you are saying now and I showed your responses to our lead CSRs and managers. This is a situation where the data is corrupted / or glitched... the suggested process for backing out of a mistake will not work.
  • 0 in reply to SageAcolyte
    In a test company, edit the table in the bottom screen shot and put in a value of 3 for the tier type. That should allow a proper transaction to correct.
  • 0 in reply to Kevin M

    Thanks Kevin, I had thought that Tier type might be involved... however because of how Sage/MAS often create their unique records with multiple columns (not a single INT as identity for example) I was not able to affect the change... because that PHY CNT record the CSR's had made to try to fix the problem is considered the same record ... if TierType of 3 is used on the glitched/corrupted row (please see screen shot)

    This could be a matter of me not understanding what tier type is.. could I change tier type of that PHY CNT record manually to something else?  Again it was only used to try to fix the glitch record

  • 0 in reply to SageAcolyte

    The top four fields are primary keys - of the table IM_ItemCost ... they cannot all be the same on two rows (two records)

    What is safe to change: for example could I change the WarehouseCode of that PHY CNT record without any trouble to Sage/MAS?  We have warehouse 000,001,002

  • 0 in reply to SageAcolyte
    TierType is tied to valuation method, which enables the different ways tiers can be used. When blank, it basically becomes invisible to parts of the Sage 100 program. Editing the data without a full understanding of all the related tables is not advised.
    You're beyond what I can help with through forum posts. Contact your Sage Partner or Sage support for assistance.
  • 0 in reply to Kevin M

    I just found that I can use the built in tool: "Remove Zero Quantity Costing Tiers" to clear out that PHY CNT record...

    After this is done... I can go forward with your suggestion to enter "3" into the corrupted row.  Its looking good.  ASAP I will tap CSR/Data Entry to execute "a proper transaction to correct." since I do not actually use Sage/MAS I do not know how to do this.

    I will report on the outcome.  Many thinks to Kevin M!!

*Community Hub is the new name for Sage City