Microsoft Dynamics AX (Archived)

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

(0) Share

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

I have the same question (0)

All responses (8)

Answers (1)

André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pranav Gupta 145 on at

Like (0)

Report

Thanks André for replying, our consultant have already discussed this with client, they are aware of the risk.

Was this reply helpful? Yes No
Suggested answer

Pranav Gupta 145 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pranav Gupta 145 on at

Like (0)

Report

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

//}

}

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (0)

Report

Do not forget table CUSTTRANSOPEN.

Was this reply helpful? Yes No

Verified answer

Rustem Galiamov

8,072 on at

Like (0)

Report

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