How to Delete Company Transactions in AX 2012 including Trial balance?

To delete Transactions data inside any company in ax, you can do by using the class SysDatabaseTransDelete under AOT.
But you may facing an issue since the Trial Balance will not be deleted so, to delete GL Trans by deleting the following tables:
GeneralJournalAccountEntry
GeneralJournalEntry
LedgerEntryJournal
LedgerEntry
Ledgerjournaltrans
Ledgerjournaltable
To remove the Trial Balance you have to follow the steps below:


1-) Modify 'handleTable()' method by adding 2 new cases
Case TableGroup::TransactionHeader:
Case TableGroup::TransactionLine:
void handleTable(SysDictTable sysDictTable)
{
    TableGroup      tableGroup;

    if (tableSet.in(sysDictTable.id()))
        return;

    tableSet.add(sysDictTable.id()); 

    if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())
    {
        tableGroup = sysDictTable.tableGroup();
        // Handle company specific tables to be deleted
        if (sysDictTable.dataPrCompany())
        {
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                    this.handleTransTable(sysDictTable);
                    break;
                default:
                    this.handleNonTransTable(sysDictTable);
                    break;
            }
        }
        else
        {
            // Handle global tables to be deleted
            switch(tableGroup)
            {
                case TableGroup::Transaction:
                case TableGroup::TransactionHeader:
                case TableGroup::TransactionLine:
                case TableGroup::TransactionHeader :
                case TableGroup::WorksheetHeader:
                case TableGroup::WorksheetLine:
                    this.handleGlobalTransTable(sysDictTable);
                    break;
                default:
                    break;
            }
        }
    }
}

2) Add a new method to handle LEDGERJOURNALTABLE:
private void deleteLedgerJournalTables()
{
    GeneralJournalEntry         GJEntry;
    GeneralJournalAccountEntry  GJAEntry;
    LedgerJournalTable          ledgerjournalTable;
    LedgerEntryJournal          ledgerEntryJournal;

    ttsBegin;
    while select forupdate LedgerJournalTable
    {
        while select forUpdate ledgerEntryJournal
            where ledgerEntryJournal.JournalNumber == ledgerjournalTable.JournalNum
            //&&    ledgerEntryJournal.dataAreaId == ledgerjournalTable.dataAreaId
        {
            while select forUpdate GJEntry
                where GJEntry.LedgerEntryJournal == ledgerEntryJournal.RecId
            {
                  delete_from GJAEntry  where GJAEntry.GeneralJournalEntry == GJEntry.RecId;

                GJEntry.delete();
            }
            ledgerEntryJournal.delete();
        }
        LedgerJournalTable.delete();
    }
    ttsCommit;
}


3) Modify the 'handleTransTable()' method to call the above method
void handleTransTable(SysDictTable sysDictTable)
{
    switch(sysDictTable.id())
    {
        case tablenum(CustCollectionLetterLine):
        case tablenum(InventDim):
        case tablenum(DocuRef):
        case tablenum(DirPartyRelationship) :

            break;
        case tablenum(LedgerJournalTable) :
            this.deleteLedgerJournalTables();
            break;

        default:
            this.deleteTable(sysDictTable);
            break;
    }
}

4) You may have to modify the 'deleteVoucher()' method in the 'LedgerJournalTrans' table to skip over releasing non-existing voucher numbers
public server void deleteVoucher(Voucher _voucher = this.Voucher)
{
    LedgerJournalTable  ledgerJournalTable = LedgerJournalTable::find(this.JournalNum);

    if (! ledgerJournalTable.Posted && !this.Transferred)
    {
        if (_voucher && ! LedgerJournalTrans::existTransMinusThis(this.JournalNum, _voucher, this.RecId))
        {
            if (this.checkVoucherNotUsed(ledgerJournalTable, _voucher))
            {
                if (this.checkVoucherNotUsedDataSource(_voucher))
                {
                    // replace the voucher number so it can be re-used
                    if (ledgerJournalTable.NumberSequenceTable) /* 28Nov12-Admin */
                        NumberSeq::releaseNumber(ledgerJournalTable.NumberSequenceTable, _voucher);

                    if (this.Voucher == _voucher)
                    {
                        // delete voucher template record if exists and the voucher on the line is not being changed
                        LedgerJournalTransVoucherTemplate::deleteForJournalOrVoucher(this.JournalNum, _voucher);
                    }
                }
            }
        }
    }
}
5) After running 'SysDatabaseTransDelete', rebuild balances for the financial dimension sets (General Ledger\Setup\Financial Dimensions\Financial dimension sets)

If you still have non-zero amounts in the Trial balance then you must manually remove the 'left-over' rows in the shared tables (results of your previous executions of the 'SysDatabaseTransDelete'). Identify these entries in the 'LedgerEntryJournal' table then use the following job to clear them:
static void tg_deleteTables(Args _args)
{
    GeneralJournalEntry         GJEntry;
    GeneralJournalAccountEntry  GJAEntry;
    LedgerJournalTable          ledgerjournalTable;
    LedgerEntryJournal          ledgerEntryJournal;

    ttsBegin;
        while select forUpdate ledgerEntryJournal
            where ledgerEntryJournal.JournalNumber like 'clau*'  
        {
            while select forUpdate GJEntry
                where GJEntry.LedgerEntryJournal == ledgerEntryJournal.RecId
            {
                  delete_from GJAEntry  where GJAEntry.GeneralJournalEntry == GJEntry.RecId;

                GJEntry.delete();
            }
            ledgerEntryJournal.delete();
        }

    ttsCommit;
    info('completed');
}

No comments:

Post a Comment