What tables to delete to clear customers transaction

selece28
selece28 Member Posts: 316
edited 2007-05-03 in NAV/Navision Classic Client
Hi All
My customer want me to delete all transaction in their outlet by the end of the day because the data is imported to their headquaters. First i think i just need to delete from this tables:
- Sales Header and Line
- Sales Shipment Header and Line
- Transfer Header and Line
- Transfer Shipment Header and Line
- Transfer Receipt and Line
The outlet can only post Ship in Sales Order and can only sell Item.

My question is what other tables to delete if i want the outlet database to be cleanup. They want to make the customer credit limit back to 0 like the customer have not done any transaction. But i still keep the item ledger entry to keep the item inventory amount.
Please tell me if u know

Thanks in advance,
______________

Regards,
Steven

Comments

  • Mindie
    Mindie Member Posts: 124
    You are in for a huge mess if you attempt to do this. I would recommend posting the appropriate transactions to bring the balance to 0 but leave the transactions where they are.

    I know this isn't what the client wants to hear but sometimes it's best not to give the client what they ask for.
  • selece28
    selece28 Member Posts: 316
    I think we still can do this
    My Client don't need their outlet data anymore so they want to clean it.
    The outlet is only used for Selling only (Sales Order, posting Ship) thats it.
    By the end of the day all Sales Order data from Outlet is send to the Headquarter. The rest of the process is done in their Headquarter (Post Invoice, etc).
    So they want the database to be cleaned on the next day, but the stock movement they still keep to track the stock in Outlet and Headquarter.
    I create the object for them to run to delete the data, but it didn't reset the customer credit limit back to 0, Because i only delete the tables above. I think i need to delete some data from the accounting tables, i didn't understand much about accounting tables.
    I only want to delete all transaction data, so what table i need to delete?

    I already explain them by deleting the data they must take the risk of their own if they loose some data.

    Thanks
    ______________

    Regards,
    Steven
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Believe me :
    selece28 wrote:
    I think we still can do this ...

    and
    selece28 wrote:
    ... i didn't understand much about accounting tables. ...

    Do not belong in the same post.
    David Singleton
  • David_Singleton
    David_Singleton Member Posts: 5,479
    edited 2007-04-29
    selece28 wrote:
    ...
    I already explain them by deleting the data they must take the risk of their own if they loose some data.
    ...

    Oh and good luck on that one. When it all goes South, that will be very conveniently forgotten.

    Seriously though, you need to really understand the impact of any change you make to a customers system, BEFORE you start it. They are paying you and relying on your expert skills, to not let them shoot themselves in the foot.
    David Singleton
  • DenSter
    DenSter Member Posts: 8,307
    Once a transaction is posted in a financial system, it is a matter of history, and in most countries it is illegal to remove them. If there's a transaction in there that should not have been there, or that needs to be reversed, you need to actually post a reversal.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • eugenious
    eugenious Member Posts: 7
    if you do not need this information - why you post those invoices? Make additional field "Invoice Status", where users modify record to status "Ready to export to HQ", but not posting. End then after you transfer entries to HQ delete them. But on your place I would better move those entries to some "History Sales Header + Line" table.
  • selece28
    selece28 Member Posts: 316
    Oh and good luck on that one. When it all goes South, that will be very conveniently forgotten.
    Yes i know that David, but my client still insist me to do that.
    They said they just want the Outlet like a "dumb terminal", used only to input Sales Order. I already suggest they go online but they refuse. They still want to go offline and only keep the 2 or 3 days previous transaction records.

    Is there any other suggestion?

    Thanks in advance
    ______________

    Regards,
    Steven
  • David_Singleton
    David_Singleton Member Posts: 5,479
    selece28 wrote:
    ...

    Is there any other suggestion?

    ...

    I'll let Daniel answer that, he has a prepared answer that he can cut and paste :mrgreen:
    David Singleton
  • DenSter
    DenSter Member Posts: 8,307
    Run Steven, Run!!! :mrgreen:
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • diptish.naskar
    diptish.naskar Member Posts: 360
    Hi Steven,

    Sometimes you do face the requirements like this and you have to do it irrespective of the choice. The entire scenario depends if you want to delete just the transaction data(which have not been posted) or you need to delete the both the posted and the transactional data. If you want to make nthe credit limit 0 of a customer based on certain criteria then you can do the same by using a process only report.
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/
  • Waldo
    Waldo Member Posts: 3,412
    DenSter wrote:
    Run Steven, Run!!! :mrgreen:

    :lol:
    Life is like a box of chocolats
    Once you've eaten one, the only way to take it back is to "}@#^" :|
    Once you've posted the document, deleting the transaction should make you "}@#^" :mrgreen:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • selece28
    selece28 Member Posts: 316
    Yes diptish.naskar
    Thats what i mean
    i already create Process only report for my client to delete their old Sales Order Data, but now they want me to help them to delete the transactional data to make the customer credit limit back to 0. So what table should i delete? Is it only from Customer ledger entry? Is there anything that i forget?

    Thanks
    ______________

    Regards,
    Steven
  • diptish.naskar
    diptish.naskar Member Posts: 360
    Hi Selec:

    See the credit limit is actually a normal field in Customer table which you can always modify from a procesing report. But if you delete the CLE...then there are many things that you need to keep in mind as you will be removing the entire audit trail of the entries passed. Just removing the CLE will not help you....but rather put you in a mess. However you can consider the following tables...

    1. Detailed Customer ledger entries
    2. Sales Invoice Header
    3. Sales Invoice Line
    4. G/L Entry
    5. Customer ledger entry

    Well all the tables together have a tight integration, but its an advice ....try not to acheive the same via a processing report...and even if want to do this.....then....be very very careful...because one single mistake and u will be doing...... ](*,) :cry:
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/
  • Waldo
    Waldo Member Posts: 3,412
    - Dimension tables
    - VAT tables
    - ...

    [-X

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • diptish.naskar
    diptish.naskar Member Posts: 360
    Oh Yes!!!!!!!!!! =D> Waldo is right...

    you have to get hold of the Ledger entry dimension table, VAT, Posted document dimensions,Service Tax Entry and posted structure details.

    Hope this will do.... :lol:
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/
  • Waldo
    Waldo Member Posts: 3,412
    Using code coverage and navigate should get you going ... .

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • selece28
    selece28 Member Posts: 316
    Ow man, so many tables affected
    ](*,)

    I think i'll try and see if i can find another way

    Thanks
    ______________

    Regards,
    Steven

Categories