News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics AX (Archived) How to forcefully upda...

Microsoft Dynamics AX (Archived)

How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

(0) Share

Report

Report

Posted on by Pranav Gupta

145

Hi,

One of my customer is asking for this change for multiple records (Kind of dataFix).

The issue is When I update these two table and checked the Invoice of the salesOrder, all the Voucher and transaction details are missing, so I again changed these date back to previous and I can see the Voucher and transaction details again.

I need to know, how many more table I need to update with these two tables (CustInvoiceJour and CustInvoiceTrans) to make the data correct.

Best Regards

Pranav

*This post is locked for comments

All responses Img

All responses (8)

Answers Img

Answers (1)

Pranav Gupta 145 on at

Like (1)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Thanks Rustem,

It's working now.

Verified answer

Rustem Galiamov

8,072 on at

Like (0)

Report

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Sorry, i'm forgot SubledgerVoucherGeneralJournalEntry and CustTransOpen.

static void garu_updateCustInvoiceJourDate(Args _args)
{
    CustInvoiceJour                     custInvoiceJour;
    CustInvoiceTrans                    custInvoiceTrans;
    CustTrans                           custTrans;
    CustTransOpen                       custTransOpen;
    TaxTrans                            taxTrans;
    GeneralJournalAccountEntry          generalJournalAccountEntry;
    GeneralJournalEntry                 generalJournalEntry;
    SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry;
    InventTrans                         inventTrans;
    InventTransOrigin                   inventTransOrigin;
    InventTransPosting                  inventTransPosting;
    CustInvoiceSalesLink                custInvocieSaleslink;
    InventReportDimHistory              inventReportDimHistory;
    Voucher                             voucher = "UNC-10041407";
    TransDate                           transDate = 06\08\2018;
    transDate                           newInvoiceDate = 08\08\2018;
    boolean                             error;

    ttsBegin;

    select forUpdate custInvoiceJour
        where custInvoiceJour.LedgerVoucher == voucher
           && custInvoiceJour.InvoiceDate   == TransDate;

    if(custInvoiceJour)
    {
        while select forUpdate custInvoiceTrans
            where custInvoiceTrans.InvoiceId            == custInvoiceJour.InvoiceId
                && custInvoiceTrans.InvoiceDate         == custInvoiceJour.InvoiceDate
                && custInvoiceTrans.SalesId             == custInvoiceJour.SalesId
                && custInvoiceTrans.numberSequenceGroup == custInvoiceJour.numberSequenceGroup
        {
            select forUpdate inventTransOrigin
                where inventTransOrigin.InventTransId == custInvoiceTrans.InventTransId;

            if(inventTransOrigin)
            {
                info(strFmt("inventTransOrigin   %1", inventTransOrigin.InventTransId));

                while select forUpdate inventTrans
                    where inventTrans.InventTransOrigin == inventTransOrigin.RecId
                        && inventTrans.StatusIssue       == StatusIssue::Sold
                        && inventTrans.Voucher           == custInvoiceJour.LedgerVoucher
                        && inventTrans.InvoiceId         == custInvoiceJour.InvoiceId
                        && inventTrans.DateFinancial     == custInvoiceJour.InvoiceDate
                {
                    info(strFmt("update inventTrans   %1", inventTrans.Voucher));
                    inventTrans.DateFinancial       = newInvoiceDate;
                    inventTrans.doUpdate();
                }

                custInvoiceTrans.InvoiceDate = newInvoiceDate;
                custInvoiceTrans.doUpdate();
                info(strFmt("update custInvoiceTrans   %1", inventTrans.Voucher));
            }

        }

        while select forUpdate taxTrans
            where taxTrans.Voucher   == custInvoiceJour.LedgerVoucher
                && taxTrans.TransDate == custInvoiceJour.InvoiceDate
        {
            info(strFmt("update taxTrans   %1", taxTrans.Voucher));
            taxTrans.TransDate = newInvoiceDate;
            taxTrans.doUpdate();
        }

        while select forUpdate generalJournalEntry
            where generalJournalEntry.SubledgerVoucher == custInvoiceJour.LedgerVoucher
        {
            while select forupdate subledgerVoucherGeneralJournalEntry
                where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId
            {
                info(strFmt("update subledgerVoucherGeneralJournalEntry   %1", subledgerVoucherGeneralJournalEntry.Voucher));
                subledgerVoucherGeneralJournalEntry.AccountingDate = newInvoiceDate;
                subledgerVoucherGeneralJournalEntry.doUpdate();
            }
            
            info(strFmt("update generalJournalEntry   %1", generalJournalEntry.SubledgerVoucher));
            generalJournalEntry.AccountingDate      = newInvoiceDate;
            generalJournalEntry.AcknowledgementDate = newInvoiceDate;
            generalJournalEntry.DocumentDate        = newInvoiceDate;
            generalJournalEntry.doUpdate();
        }

        select forUpdate custTrans
            where custTrans.Voucher    == custInvoiceJour.LedgerVoucher
                && custTrans.TransDate == custInvoiceJour.InvoiceDate;

        if(custTrans)
        {
            while select forUpdate custTransOpen
                where custTransOpen.AccountNum == custTrans.AccountNum
                    && custTransOpen.RefRecId   == custTrans.RecId
            {
                info(strFmt("update custTransOpen   %1", custTransOpen.AccountNum));
                custTransOpen.TransDate = newInvoiceDate;
                custTransOpen.doUpdate();
            }
                
            info(strFmt("update custTrans   %1", custTrans.Voucher));
            custTrans.TransDate = newInvoiceDate;
            custTrans.doUpdate();
        }
            
            
        select forUpdate custInvocieSaleslink
            where custInvocieSaleslink.salesId == custInvoiceJour.SalesId
                && custInvocieSaleslink.invoiceId == custInvoiceJour.InvoiceId
                && custInvocieSaleslink.invoiceDate == custInvoiceJour.InvoiceDate;

        if(custInvocieSaleslink)
        {
            info(strFmt("update custInvocieSaleslink   %1", custInvocieSaleslink.InvoiceId));
            custInvocieSaleslink.invoiceDate = newInvoiceDate;
            custInvocieSaleslink.doUpdate();
        }

        info(strFmt("update custInvoiceJour   %1", custInvocieSaleslink.InvoiceId));
        custInvoiceJour.InvoiceDate = newInvoiceDate;
        custInvoiceJour.doUpdate();
    }
    
    ttsCommit;

    info('Done');
}

Brandon Wiese 17,786 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Do not forget table CUSTTRANSOPEN.
Pranav Gupta 145 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Hi Rustem,

I have change the dates for 1 record for testing in these tables, but the vouchers are not visible.

Do I have to update in more tables?

below is the job I am using, please suggest.

static void Job11(Args _args)

{

CustInvoiceJour custInvoiceJour;

CustInvoiceTrans custInvoiceTrans;

CustInvoiceSalesLink custInvoiceSalesLink;

CustTrans custTrans;

GeneralJournalEntry generalJournalEntry;

TaxTrans taxTrans;

InventTrans inventTrans;

ttsBegin;

select forupdate custInvoiceJour

where custInvoiceJour.InvoiceId == "SI107348" &&

custInvoiceJour.SalesId == "SO191450" &&

custInvoiceJour.InvoiceDate == str2Date("01/05/2018", 123);

custInvoiceJour.InvoiceDate = str2Date("23/08/2018", 123);

custInvoiceJour.DueDate = str2Date("07/10/2018", 123);

custInvoiceJour.update();

select forUpdate custInvoiceTrans

where custInvoiceTrans.InvoiceId == "SI107348" &&

custInvoiceTrans.SalesId == "SO191450" &&

custInvoiceTrans.InvoiceDate == str2Date("01/05/2018", 123);

custInvoiceTrans.InvoiceDate = str2Date("23/08/2018", 123);

custInvoiceTrans.update();

select forUpdate custInvoiceSalesLink

where custInvoiceSalesLink.invoiceId == "SI107348" &&

custInvoiceSalesLink.origSalesId == "SO191450" &&

custInvoiceSalesLink.InvoiceDate == str2Date("01/05/2018", 123);

custInvoiceSalesLink.InvoiceDate = str2Date("23/08/2018", 123);

custInvoiceSalesLink.update();

select forUpdate custTrans

where custTrans.Invoice == "SI107348" &&

custTrans.accountNum == "1160" &&

custTrans.TransDate == str2Date("01/05/2018", 123);

custTrans.TransDate = str2Date("23/08/2018", 123);

custTrans.DueDate = str2Date("07/10/2018", 123);

custTrans.update();

select forUpdate generalJournalEntry

where generalJournalEntry.SubledgerVoucher == "INV10107347";

generalJournalEntry.AccountingDate = str2Date("23/08/2018", 123);

generalJournalEntry.AcknowledgementDate = str2Date("23/08/2018", 123);

generalJournalEntry.DocumentDate = str2Date("23/08/2018", 123);

generalJournalEntry.update();

while select forUpdate taxTrans

where taxTrans.Voucher == "INV10107347" &&

taxTrans.TransDate == str2Date("01/05/2018", 123)

{

taxTrans.RealizedDate = str2Date("23/08/2018", 123);

taxTrans.TransDate = str2Date("23/08/2018", 123);

taxTrans.update();

}

while select forUpdate inventTrans

where inventTrans.Voucher == "INV10107347" &&

inventTrans.InvoiceId == "SI107348"

//inventTrans.DateFinancial == str2Date("01/05/2018", 123)

{

inventTrans.DateFinancial = str2Date("23/08/2018", 123);

// inventTrans.TransDate = str2Date("23/08/2018", 123);

inventTrans.update();

}

ttsCommit;

//while select custInvoiceJour

//join custInvoiceTrans

//where custInvoiceTrans.InvoiceId == custInvoiceJour.InvoiceId &&

//custInvoiceTrans.SalesId == custInvoiceJour.SalesId &&

////custInvoiceTrans.InvoiceDate == custInvoiceJour.InvoiceDate &&

//custInvoiceJour.InvoiceId >= "SI107344" &&

//custInvoiceJour.InvoiceId <= "SI107351"

////custInvoiceJour.InvoiceDate == str2Date("01/05/2018", 123)

//{

//info(strFmt("CustInvoiceJour.InvoiceID = %1, CustInvoiceJour.InvoiceDate = %2, CustInvoiceJour.DueDate = %3",custInvoiceJour.InvoiceId, custInvoiceJour.InvoiceDate, custInvoiceJour.DueDate));

//info(strFmt("CustInvoiceTrans.InvoiceID = %1, CustInvoiceTrans.InvoiceDate = %2",custInvoiceTrans.InvoiceId, custInvoiceTrans.InvoiceDate));

//}

}
Suggested answer

Pranav Gupta 145 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Hi Rustem,

Thanks for the detail of the tables, I will proceed with this information.

It will be great if you can provide that job to me.
Pranav Gupta 145 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Thanks André for replying, our consultant have already discussed this with client, they are aware of the risk.
Rustem Galiamov 8,072 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Hi Pranav!

In a few project we did this by writing a job's. But, as André Arnaud de Calavon mentioned, it is not allowed to adjust posted transaction.

BTW, the tables in which we updated the dates are:

CustInvoiceJour

CustInvoiceTrans

CustInvocieSaleslink

CustTrans

GeneralJournalEntry

TaxTrans

InventTrans

And keep in mind that inventory and financial period must be open.
André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at

Like (0)

Report

RE: How to forcefully update the invoiceDate and dueDate in CustInvoiceJour and invoiceDate in CustInvoiceTrans Table?

Hi Pranav,

Be aware that in most countries it is not allowed to adjust posted transaction; for sure not invoices. Make sure you will not be held liable/responsible for any issues related to a possible data fix. Try to get your customer to get an approval of e.g. external auditors.

The voucher and transaction/invoice date are the key to find related ledger transactions and more. So, also tables where the accounting entries, tax transactions, item transactions are stored needs to be updated. There can be more tables, but that needs some more investigation which you have to do yourself or there might be another forum member with more information.

It is also possible to correct is with an additional credit note and new invoice.

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,214 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans