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');
}