Thursday, May 30, 2019

Customer Address book relation for channel Database D365FO


Customer Address book relation for channel Database D365FO



Scenario:

Most of our D365FO Fellow are working with Retail where we sync customer with our channel database, and sometime we need to update the customer from POS as well, Sometime channel DB throw exception when while updating the customer information if AddressBook of the customer  customer is not properly set.

Its happens when we create customer using AX client or import them via DMF and didn't set the address book value.


So no need to worry using below code & query you can update the customers address book in bulk.

Image:




Code:

public void AddrssBookRelation()
    {
        DirAddressBookParty       dirAddressBookParty;
        DirAddressBook                   dirAddressBook;
        CustTable                              custTable;


        select RecId,Party from custTable
            where custTable.AccountNum==this.CustomerAccount;

        if(custTable)
        {
            select RecId from dirAddressBook
            where dirAddressBook.Name==this.CountryCode;

            if(dirAddressBook)
            {
                select RecId from dirAddressBookParty
                    where dirAddressBookParty.Party==custTable.Party && dirAddressBookParty.AddressBook==dirAddressBook.RecId;

                if(dirAddressBookParty.RecId==0)
                {
                    dirAddressBookParty.clear();
                    dirAddressBookParty.Party=custTable.Party;
                    dirAddressBookParty.AddressBook=dirAddressBook.RecId;
                    dirAddressBookParty.insert();
                }

            }
        }

    }



SQL Query:

INSERT INTO DirAddressBookParty (PARTY,ADDRESSBOOK,PARTITION)
select DISTINCT DirPartyLocation.PARTY,DirAddressBook.RECID,5637144576 from LogisticsPostalAddress
join DirPartyLocation on DirPartyLocation.LOCATION=LogisticsPostalAddress.LOCATION
Join LOGISTICSLOCATION on LOGISTICSLOCATION.RECID=LogisticsPostalAddress.LOCATION
join DirAddressBook on DirAddressBook.NAME=LogisticsPostalAddress.COUNTRYREGIONID
where DirPartyLocation.ISPRIMARY=1 and party in (select party from CUSTTABLE)
and LOGISTICSLOCATION.ISPOSTALADDRESS=1 and DirPartyLocation.ISPOSTALADDRESS=1 and DirPartyLocation.POSTALADDRESSROLES='Business'
and not exists (select 1 from DirAddressBookParty where DirAddressBookParty.PARTY=DirPartyLocation.PARTY and DirAddressBookParty.ADDRESSBOOK=DirAddressBook.RECID)
 and LogisticsPostalAddress.VALIDTO >=GETDate()

Friday, March 29, 2019

Customer Cancel Payment Transaction



Customer Cancel Payment Transaction 


Using following code you can cancel the customer Invoice payment


class TMV_CustomerPaymentCancel
{

   

    public static void main(Args _args)
    {
        LedgerJournalTrans _ledgerJournalTrans;
        TMV_CustomerPaymentCancel objCancelPayment=new TMV_CustomerPaymentCancel();
        objCancelPayment.cancelpaymentExecution('R00007404',_ledgerJournalTrans);
    }

    public void cancelpaymentExecution(InvoiceId _invoiceid,LedgerJournalTrans _ledgerJournalTrans)
    {

        CustTrans custTransOffset;
       
        ReasonTable reasonTable;
        ReasonCode reasonCode;
        ReasonRefRecID reasonRefRecID;
     
        Args args;
 
        ;
 

        custTransOffset= this.findPaymentReference(_invoiceid);
 
   
        if(custTransOffset.RecId>0)
        {
            this.CancelPayment(custTransOffset, _ledgerJournalTrans);
        }

    }

    public CustTrans findPaymentReference(InvoiceId _invoiceid)
    {
        CustTrans custTrans,custTransOffset;

        custTrans = CustTrans::findFromInvoice(_invoiceid);
        select custTransOffset
            where custTransOffset.OffsetRecid==custTrans.RecId
            && custTransOffset.TransType==LedgerTransType::Payment;
        return custTransOffset;
    }

    public void CancelPayment(CustTrans custTransOffset,LedgerJournalTrans _ledgerJournalTrans)
    {
        CustVendPDCManager  custVendPDCManager;
        BankPaymCancel bankPaymCancel;
        Args    localArgs;
        if(custTransOffset.RecId)
        {
 
            infolog.clear();
            try
            {
                localArgs = new Args();

                bankPaymCancel = BankPaymCancel::newBankPaymCancel(custTransOffset);

                //  bankPaymCancel.parmReason(reasonTable.Reason);
                //   bankPaymCancel.parmReasonComment(reasonTable.Description);
                bankPaymCancel.parmTransDate(systemDateGet());
       
                localArgs.caller(bankPaymCancel);
                localArgs.record(custTransOffset);
                BankPaymCancel::serverRun(localArgs);
 
            }
            catch
            {
               


            }
           
        }
    }



    public  str getErrorStr()
    {
        SysInfologEnumerator enumerator;
        SysInfologMessageStruct msgStruct;
        Exception exception;
        str error;
        enumerator = SysInfologEnumerator::newData(infolog.cut());
        while (enumerator.moveNext())
        {
            msgStruct = new SysInfologMessageStruct(enumerator.currentMessage());
            exception = enumerator.currentException();
            error = strfmt("%1 %2", error, msgStruct.message());
        }
        return error;
    }

}

Monday, March 11, 2019

Reverse customer Posted Invoice


Reverse customer Posted Invoice 

Using below code you can reverse the posted invoice of the customer.


class DemoTransationReversal extends TransactionReversal_Cust
{
    public static DemoTransationReversal construct()
    {
        return new DemoTransationReversal();
    }

    public boolean showDialog()
    {
        return false;
    }

    public static void main(Args _args)
    {
        CustTrans custTrans;
        DemoTransationReversal demoTransationReversal;
        ReasonTable reasonTable;
        ReasonCode reasonCode;
        ReasonRefRecID reasonRefRecID;
        InvoiceId invoiceId;
        Args args;
        ;

        invoiceId = "0099";
        reasonCode = "DEMO Purpose";       
        reasonTable = ReasonTable::find(reasonCode);
        reasonRefRecID = ReasonTableRef::createReasonTableRef(
            reasonTable.Reason, reasonTable.Description);

        custTrans = CustTrans::findFromInvoice(invoiceId);
           
        if (custTrans.RecId && !custTrans.LastSettleVoucher)
        {
            args = new Args();
            args.record(custTrans);

            demoTransationReversal = DemoTransationReversal::construct();
            demoTransationReversal.parmReversalDate(systemDateGet());
            demoTransationReversal.parmReasonRefRecId(reasonRefRecID);
            demoTransationReversal.reversal(args);
           
            info(strFmt("%1 %2 %3 %4 reversed.",
                custTrans.Voucher,
                custTrans.TransDate,
                custTrans.Invoice,
                custTrans.Txt));
        }       
    }
}

Wednesday, February 20, 2019

SQL In Operator in D365FO


SQL In Operator in D365FO


Microsoft Introduce In Operator in X++ Syntax, but it will work with Enums  only.

Following are the example how you can use this


SalesTable  salesTable;
container   con = [SalesType::Sales, SalesType::ReturnItem, SalesType::Subscription];
while select SalesId from salesTable
where salesTable.SalesType in con
{
Info(salesTable.SalesId);
}

Wednesday, December 19, 2018

Project Item Journal D365FO


Project Item Journal D365FO

Using below code you can create the project item journal.



public void createProjectInventJournal(ProjectJournalContract    _projectJournal)
    {
        InventDim                            inventDim;
        InventJournalName                    InventJournalName;
        InventJournalTable                   inventJournalTable;
        InventJournalTableData               journalTableData = JournalTableData::newTable(inventJournalTable);
        InventTable                          inventTable;
        ProjTable                               projTable;
        AWCFieldServIntegrtionMainAccount     ServiceOffsetAccount=this.findServiceMainAccount(AWCFieldServiceIntegrationType::ProjectItem);
       
         ProjectJournalTransContract  journalTransContract;

        if(_projectJournal.getProjectJournalTransCount()==0)
        {
            return;
        }
       

        journalTransContract = _projectJournal.getProjectJournalTrans(0);

        select projTable
            where projTable.ProjId==journalTransContract.ProjectId;

        select inventTable
            where inventTable.itemId==journalTransContract.ItemId;

        select InventJournalName
            where InventJournalName.JournalType==InventJournalType::project
        && InventJournalName.isFSJournal==NoYes::Yes;

        inventJournalTable.JournalId = journalTableData.nextJournalId();
        inventJournalTable.JournalType=InventJournalType::project;
      
        journalTableData.initFromJournalName(InventJournalName);
     
        inventJournalTable.insert();

        InventJournalTrans          inventJournalTrans;
        InventJournalTransData      journalTransData = journalTableData.journalStatic().newJournalTransData(inventJournalTrans, journalTableData);
        journalTransData.initFromJournalTable();

        inventJournalTrans.TransDate    = today();

       

        inventJournalTrans.ProjId               =journalTransContract.ProjectId;
       inventJournalTrans.ProjCategoryId       = journalTransContract.ProjectCateId;
        inventJournalTrans.initFromProjTable(projTable);

        inventJournalTrans.initFromInventTable(inventTable);
       
        inventJournalTrans.Qty              = journalTransContract.QtyUsed;
        inventJournalTrans.PriceUnit        = journalTransContract.CostPrice;
     
        inventJournalTrans.ProjUnitID       = journalTransContract.Unit;
        inventJournalTrans.Worker           = HcmWorkerLookup::currentWorker();
        inventJournalTrans.LedgerDimension  = LedgerDefaultAccountHelper::getDefaultAccountFromMainAccountRecId(MainAccount::findByMainAccountId(ServiceOffsetAccount.MainAccountId).RecId);
        inventJournalTrans.activityNumberModified();
        inventDim.clear();
        inventDim.InventSiteId  = journalTransContract.Site;
        inventDim.InventLocationId =journalTransContract.Location;
        inventDim.wMSLocationId = journalTransContract.Warehouse;

       
        inventJournalTrans.InventDimId = inventDim::findOrCreate(inventDim).inventDimId;

        //other fields
        journalTransData.create();
    }

Wednesday, December 5, 2018

Currency Conversion X++ AX7, D365FO and AX-2012


Currency Conversion X++ AX7, D365FO and AX-2012


Convert currency to currency.

Static void main(Args args)
{
    CurrencyExchangeHelper currencyExchangeHelper;
    AmountMst amountMST;
    CurrencyCode toCurrency =  ‘AED’;
    CurrencyCode FromCurrency =  ‘USD’;

    AmountCur amountCur = 5000;



        currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());
        amountMST =  currencyExchangeHelper.calculateCurrencyToCurrency(toCurrency, fromCurrency,amountCur,true);

  
info(strFmt(“%1”, amountMST))


}



Convert Transaction currency to company ledger currency.
Method will convert the transaction currency in to accounting currency defined in ledger.

static void Main(Args _args)
{
    CurrencyExchangeHelper currencyExchangeHelper;
    CurrencyCode transCurrency = ‘EUR’;
    AmountCur amountCur = 5500.00;
    AmountMst amountMST;
   
    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());
    amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);
    info(strFmt(‘%1’,amountMST));
}





Convert Company currency to transaction currency.
Method calculates the transaction currency amount from an accounting currency given.

static void  main(Args _args)
{
    CurrencyExchangeHelper currencyExchangeHelper;
    CurrencyCode transCurrency = ‘AED’;
    AmountCur amountCur;
    AmountMst amountMST = 5500.00;
   
    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::primaryLedger(CompanyInfo::findDataArea("DUM").RecId), systemDateGet());
    amountCur = currencyExchangeHelper.calculateAccountingToTransaction(transCurrency, amountMST ,true);
    info(strFmt(‘%1’,amountcur));
}

Tuesday, December 4, 2018

Project-Hour Journal D365FO


Project-Hour Journal D365FO



using following code you can create Project-Hour Journal




  public void createProjectHourJournal(ProjectJournalContract    _projectJournal) // project hour journal
    {
        ProjJournalTableData    JournalTableData;
        ProjJournalTransData    journalTransData;
        ProjJournalTable        journalTable, journalTableUpdate;
        ProjJournalTrans        journalTrans;
        ProjTable               projTable;
        ProjInvoiceTable        projInvoiceTable;
        NumberSeq               numberSeq;
        ProjJournalCheckPost    jourPost;
        ProjQtyEmpl             qty;
        JournalNumOfLines       numOfLines;
        ProjCategory            projCategory;
        LedgerJournalName       ledgerJournalName;
        ProjectJournalTransContract     projecJournalTrans;
       // ProjLinePropertySetup       projLinePropertySetup;
        ProjJournalName         projJournalName;

        if(_projectJournal!=null)
        {

            select projJournalName
                where projJournalName.isFSJournal==NoYes::Yes;

            projecJournalTrans=_projectJournal.getProjectJournalTrans(0);
           
            ttsBegin;
            journalTableData =          JournalTableData::newTable(journalTable);


            journalTransData =            journalTableData.journalStatic().newJournalTransData(journalTrans,  journalTableData);


            //select projLinePropertySetup
            //    where projLinePropertySetup.ProjCode==TableGroupAll::All
            //    && projLinePropertySetup.ProjRelation==projTable.ProjId;

            projTable           = ProjTable::find(projecJournalTrans.ProjectId);
            // Init        JournalTable
            projCategory=ProjCategory::find(projecJournalTrans.ProjectCateId);
      
            journalTable.JournalId      = journalTableData.nextJournalId();

            journalTable.JournalType    = ProjJournalType::Hour;

            journalTable.ProjId=projecJournalTrans.ProjectId;
            journalTable.ProjQty=projecJournalTrans.QtyUsed;
            journalTable.CategoryId=projecJournalTrans.ProjectCateId;
            journalTable.ProjTransDate=systemDateGet();
            //journalTable.LinePropertyId
            journalTable.initFromProjJournalName(projJournalName);
            journalTable.insert();

          
            journalTableData.initFromJournalName(journalTableData.journalStatic().findJournalName(journalTable.JournalNameId));

            journalTrans.clear();
            journalTransData.initFromJournalTable();
    
            projInvoiceTable    = projTable.projInvoice();
           
          

            journalTrans.setTransDate();
          //  journalTrans.TransDate      = systemDateGet();
            journalTrans.ProjTransDate  = systemDateGet();
            journalTrans.ProjId         = projTable.ProjId;
            journalTrans.Qty            = projecJournalTrans.QtyUsed;
            journalTrans.CategoryId     = projCategory.CategoryId;
         //   journalTrans.LinePropertyId = projLinePropertySetup.LinePropertyId;
            journalTrans.Worker         = HcmWorkerLookup::currentWorker();
            journalTrans.ActivityNumber = projecJournalTrans.ActivityId;
            journalTrans.Txt            = _projectJournal.FreeTxt;

           
           

            if(projInvoiceTable.CurrencyId)
            {
                journalTrans.CurrencyId =            projInvoiceTable.CurrencyId;
            }

            else
            {
                journalTrans.CurrencyId =           Ledger::accountingCurrency(CompanyInfo::current());

            }


            journalTrans.DefaultDimension   = projTable.DefaultDimension;
            journalTrans.TaxGroupId         =           ProjParameters::taxGroupInvoice(projTable.ProjId);

            if  (journalTrans.Worker)
            {
                journalTrans.setHourPrices();
                journalTrans.setPeriodDate();
            }

            numberSeq =            NumberSeq::newGetVoucherFromId(journalTable.VoucherNumberSequenceTable, false);
            journalTrans.Voucher        = numberSeq.voucher();
            journalTransData.create();

            ttsCommit;
       

        }

    }