Tuesday, February 20, 2024

Project intercompany invoice sales tax calculation bug in 10.0.37

 While Microsoft working on this issue, let me show you how you can fix it in the current code running on 10.0.37 or lower.

Scenario

When using the project intercompany customer invoice form in Project management and accounting module , you can create a customer invoice to bill to an intercompany customer. As the customer is intercompany, there is a legal entity (LE) associated with that customer. When posting the intercompany customer invoice, a pending vendor invoice is created in the customer’s LE with the same lines from the customer invoice.





You can see calculated Sales taxes via Sales tax button













Issue

If you try to add more lines, it will be added with line number 1, and sales taxes won't be added with this newly added line. Note: If you delete any line here and then you add more, it works fine.

This bug leads to missed taxes transactions in all TaxUncommitted, TmpTaxWorkTrans, and eventually in TaxTrans tables

Fix

Create an extension class with the following code



[ExtensionOf(classStr(ProjIntercompanyCustomerInvoiceCreator))]
final class myProjIntercompanyCustomerInvoiceCreator_Extension
{
    
    public CustInvoiceTable createInvoice()
    {
        // we need to increase the next line number to avoid duplicates in case when new lines added to initially created ones
        lineNum             = this.myGetNextLineNum();

        custInvoiceTable    = next createInvoice();

        if(origTransList.elements())
        {
            TaxUncommitted::deleteForDocumentHeader(tableNum(CustInvoiceTable), custInvoiceTable.RecId);
        }
        
        return custInvoiceTable;
    }


    public LineNum myGetNextLineNum()
    {
        CustInvoiceLine custInvoiceLine;
        
        select maxof(lineNum) from custInvoiceLine
            where custInvoiceLine.ParentRecId == custInvoiceTable.RecId;

        return custInvoiceLine.LineNum + 1;
    }

}

Friday, January 5, 2024

Check your data via SQL

 Sometimes we need to check or validate some data in a dev box. The fastest way to do that is to run a query directly in MS SQL management studio.

For example, I have hundreds legal entities and want to know in which of them I have some Purchase orders. Voila:




select
dataareaid as Company, count(RECID) as 'Number of PO'
from PURCHTABLE
group by DATAAREAID
having count(RECID) > 1

Tuesday, September 19, 2023

How to create a new custom financial dimension value with description and other parameters via X++

User can add values manually to Custom dimension attribute type only. For all other backing entities it should go via standard table creation, say, new CustTable record, etc.



The easiest way to create a new Custom list dimension value is to use the standard service DimensionValueService as follows. Say, you need to create a new value by using _newProjCategory record fields.

DimensionValueService dimensionValueService = new DimensionValueService();
DimensionValueContract dimensionValueContract = new DimensionValueContract();
dimensionValueContract.parmValue(_newProjCategory.Id);
dimensionValueContract.parmDimensionAttribute(myDimHelper::getProjCategoryAttribute);
dimensionValueContract.parmDescription(_newProjCategory.Name);

dimensionValueService.createDimensionValue(dimensionValueContract);

It creates the display value as its description.





 

Saturday, September 2, 2023

Ledger dimension vs Default dimension

 In short Ledger dimension it is Main account + default dimension.

So, if you need to replace any attribute value in a given Ledger dimension, do not forget to get a Default dimension first.

LedgerDimensionFacade::getDefaultDimensionFromLedgerDimension(ledgerjournalTrans.LedgerDimension);

Then you can use this old good way.

DimensionDefault newDim = DimensionHelper::setValueToDefaultDimension(hcmEmployment.DefaultDimension, DimensionAttribute::findByName(_dimensionName).RecId, _dimensionValue);
        

BTW, the opposite thing works like that:

LedgerDimensionFacade::serviceCreateLedgerDimension(ledgerDimensionMainAccount, inventTrans.defaultDimension);
Supporting method
public static DimensionDefault setValueToDefaultDimension(DimensionDefault _dimensionDefault, RefRecId _dimensionAttributeRecId, DimensionValue  _newDimensionValue)
    {
        DimensionAttributeValueSetStorage   dimStorage;
        DimensionDefault                    newDimensionDefault = _dimensionDefault;
        DimensionAttributeValue             dimensionAttributeValue;
        if (_dimensionAttributeRecId)
        {
            dimStorage = DimensionAttributeValueSetStorage::find(_dimensionDefault);
            if (_newDimensionValue)
            {
                dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::find(_dimensionAttributeRecId), _newDimensionValue, false, true);
                dimStorage.addItem(dimensionAttributeValue);
            }
            else
            {
                dimStorage.removeDimensionAttribute(_dimensionAttributeRecId);
            }
            newDimensionDefault = dimStorage.save();
        }
        return newDimensionDefault;
    }

Bigger usage example

        // replace TransactionType to the one from parameters
        // first we need to get main account and default dimension from the originl transaction ledger dimension 
        mainAccountRecId = LedgerDimensionFacade::getMainAccountRecIdFromLedgerDimension(_ledgerJournalTransOrig.LedgerDimension);
        dimensionDefault = LedgerDimensionFacade::getDefaultDimensionFromLedgerDimension(_ledgerJournalTransOrig.LedgerDimension);
        // then replace this attribute with a new value
        dimensionDefault = myDimValueHelper::setDefaultDimensionValue(dimensionDefault, myDimensionConstants::TransactionType, LedgerParameters::find().myGLDimInvTransSale);
        // get default dimension for main account
        ledgerDimensionMainAccount = LedgerDefaultAccountHelper::getDefaultAccountFromMainAccountRecId(mainAccountRecId);
        // finally combine it with original main account to get a new ledger dimension
        ledgerJournalTrans.OffsetLedgerDimension  = LedgerDimensionFacade::serviceCreateLedgerDimension(ledgerDimensionMainAccount, dimensionDefault);
        ledgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans, OffsetLedgerDimension));

 https://alexvoy.blogspot.com/2015/10/how-to-lookup-and-set-new-value-for.html

Thanks Sasha Nazarov and Ievgen Miroshnikov and Denis Trunin

Friday, August 4, 2023

How to select\unselect all records in a form grid

 While adding standard command button you can opt for SelectAll to mark all records in a form grid. However, there is no such a command for the opposite - unselect all records.


You can easily achieve it by using the following method and two usual button form controls.

[Form]
public class myForm extends FormRun
{
    public void selectAll(boolean _select)
    {
        VendPaymFormat_DS.markAllLoadedRecords(_select);
    }

    [Control("Button")]
    class FormButtonControlSelectAll
    {
        public void clicked()
        {
            element.selectAll(true);
            super();
        }
    }

    [Control("Button")]
    class FormButtonControlUnSelectAll
    {
        public void clicked()
        {
            element.selectAll(false);
            super();
        }
    }
}






How to get rid of a stuck report design in SSRS

It is difficult to say if your recent changes to an SSRS report design are really deployed. I suggest that any textbox be colored to visualize it (you can revert it in the end).

However, sometimes previous design is stuck on the server, and you still see no changes deployed, nevertheless, you already restarted Reporting services. Fortunately, there is a direct way to delete such a stubborn report from the server.

Open Report Server configuration manager as administrator and apply Portal URL setting if it is not done yet.



Then go for your report and delete it.

Next deployment should be OK.


Friday, July 28, 2023

XDS in action: How to restraint access to data based on the current user employee

Extensible Data Security (XDS) policies allow restraint access to D365FO data in a very flexible way.

Business case: a user can see those purchase orders and their related confirmations only if he or she is Requester. The whole project can basically contain three objects: role, query, and policy.



After creating a specific security role, we need to create a query with HCMWorker table so that it is filtered for the current user.





All constrained tables should be added to the policy by referencing a particular relation (PurchTable, for example, has two relations with HCMWorker table; thus, we need to pick up the required one related to Requester field)







Note: we can create a join expression if a required relation does not exist for a given table.

Once the project built and synchronized, we can assign this new role along with some standard ones to a user. For example sake, I added Ada to my user.