Tuesday, September 25, 2018

TempDB on Forms

Spent about 2 hours trying to recall how to make tmpDB tables work with forms and sorted it out, thanks to this link http://alexvoy.blogspot.com/2018/03/tempdb-table-on-form-with-multiple.html

Just a post for reminding myself for next time

Wednesday, September 19, 2018

Find AX code snippets from SQL end like a cross reference on AX but from SQL side

I recently learned a way to find code snippets quickly using sql. This is very helpful in scenarios when cross reference is turned off and you need to find places where a particular class or function is being called from.

On the model database on sql you can write the below query and you should be able to see all the methods where that code is called from.


SELECT RootHandle.Name                                              RootElementName,
    ElementHandle.Name                                               ElementName,
    ElementTypes.ElementTypeName                              Type,
    CAST(Sources.SourceText AS nvarchar(MAX))                 SourceText
FROM Sources Sources (nolock)
    JOIN ModelElement ElementHandle (nolock)
              ON Sources.SourceHandle        = ElementHandle.ElementHandle
    JOIN ModelElement RootHandle (nolock)
              ON RootHandle.ElementHandle = ElementHandle.RootHandle
    JOIN ElementTypes ElementTypes (nolock)
              ON ElementTypes.ElementType = ElementHandle.ElementType
WHERE CAST(Sources.SourceText AS nvarchar(MAX)) LIKE '%code to search for%'
OPTION(MAXDOP 0)

Wednesday, May 23, 2018

A SQL query to find all wmsLocations that have zero stock in AX 2012 R3

I struggled with this for more than 2 days as it was not as straightforward as I thought. Because we cannot use "having" in a simple AX SQL statement, I had to rely on AOT Query objects to get this. I hope my solutions is not very performance intensive but I dont know enough about AOT Query objects to confirm. It does look like it is a lot better than doing a while select and using INVENTSUM class to calculate so I went with this.

Here is the solution

I ended up fixing it by creating an AOT Query object With wmsLocations where I am grouping by location ID, inner join with InventDim but InventDim has an outer Join with InventSum and having a view on the query where syscomputed column on it to find physical Stock for that group of wmsLocations. Here is an image trying to explain the query design



The code in the physical stock is copied below.

public static server str physicalStock()

 {

 //  Description : taken from InventSum.physicalInventCalculated

  return SysComputedColumn::add(SysComputedColumn::add(

           SysComputedColumn::add(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, PostedQty))),

                 SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Received)))),

           SysComputedColumn::add(SysComputedColumn::negative(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Deducted)))),

                 SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Registered))))),

           SysComputedColumn::negative(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Picked)))));


  }
Then, I used this view and added it to the query where I use the having clause to take the sum into account. The value in the properties is said to be '<=0'. Which sums up the total quantity for a particular inventdim and filters out anything that is greater than 0. This can also be tested by putting in a view and looking and wmslocationID and calculatedPhysStock.


This view gives all the locations where the physical stock in zero but does not give any location where an inventory dimension might not have been generated yet. So, it will miss any new location created or if for some reason all related dimensions of a particular location was deleted, it will miss those too.

To add that, I created another query where I do WMSLocationTable NOTEXISTS InventDIM. By the current table design, this query will always return all locations where there is no physical stock as you cannot have a physical stock without inventDIM record.
Combining both queries as a union gives all the locations that do not have a stock.

I think this is an overkill for something that should be simpler so in case someone has a shorter solution, please let me know in the comments.

Sunday, February 11, 2018

Add custom fields in email to print mgmt settings PrintMgmtPrintDestinationTokens

To add custom fields like the image below, get to the PrintMgmtPrintDestinationTokens class and make the following modifications


PrintMgmtPrintDestinationTokens classDeclaration - add the following code (in red) to ClassDeclaration
[SrsPrintDestinationTokensExtAttribute('PrintMgmt')]
class PrintMgmtPrintDestinationTokens extends SrsPrintDestinationTokens
{
    Common jour;
    PrintMgmtDocType docType;
    container validTokens;

    SysLookupMultiSelectCtrl printMgmtMultiSelect;
    FormStringControl printMgmtCtl;
    FormCheckBoxControl printMgmtCheckBox;

    // Custom token for line manager
    FormCheckBoxControl lineMgrCheckBox;
    #define.LineMgrToken('@LineMgr@')
    #define.LineMgrTokenName('LineMgr')
}

PrintMgmtPrintDestinationTokens.addUICtls - add the following code (in red) to
protected void addUICtls(FormGroupControl _group)
{
    .
    .
    .
    printMgmtCheckBox = _group.addControl(FormControlType::CheckBox, 'PrintMgmtPrintDestinationPrimary');
    printMgmtCheckBox.widthMode(FormWidth::ColumnWidth);
    printMgmtCheckBox.label(partyType == PrintMgmtPrintDestinationPartyType::Unknown ?
            "@SYS316632" :
            strFmt("@SYS4004929", partyType));

    if (partyType == PrintMgmtPrintDestinationPartyType::Worker)//the condition for the new field based on party type or could even be document type
    {
        lineMgrCheckBox = _group.addControl(FormControlType::CheckBox, 'PrintMgmtPrintDestinationLineMgr');
        lineMgrCheckBox.widthMode(FormWidth::ColumnWidth);
        lineMgrCheckBox.label(strFmt("@SYS4004929", "@LABEL"));
    }
    .
    .
}


PrintMgmtPrintDestinationTokens.expandEmailToken- add the following code (in red) to expandEmailToken (screenshot here to keep things clear)

As you can see, a new method getHCMWorkerLineMgrEmail is defined here, this will let print mgmt settings know which email address to pick, so lets define that
protected str getHcmWorkerLineMgrEmail(Common _jour)
{
    PrintMgmtPrintDestinationPartyType  partyType;
    CustVendAC                          ac;
    str                                 addresses;
    HcmWorker                           hcmWorker, hcmWorkerMgr;
    HcmPositionRecId                    workerPositionRecId;

    [partyType, ac] = docType.getDestinationPartyTypeAndIdExt(_jour);//to get the party type in this class
    if (partyType == PrintMgmtPrintDestinationPartyType::Worker)
    {
        hcmWorker = HcmWorker::findByPersonnelNumber(ac);//to get the personnel number ac is defined above
        if (hcmWorker)
        {
            // Get worker's line manager
            workerPositionRecId = HcmWorker::getPrimaryPosition(hcmWorker.RecId);

            hcmWorkerMgr = ConcurExpWorkerFileWriter::findWorkerLineManager(workerPositionRecId);
            if (hcmWorkerMgr)
            {
                addresses = this.getEmailAddressForParty(partyType, hcmWorkerMgr.Person, '');
            }
        }
    }

    return addresses;
}
PrintMgmtPrintDestinationTokens.getUIAddresses- add the following code (in red)
public str getUIAddresses()
{
    str addresses = this.makeAddressTokens(printMgmtCtl.text());

    if(printMgmtCheckBox.checked())
    {
        addresses = this.appendAddresses(addresses, this.emptyToken());
    }

    if (lineMgrCheckBox)
    {
        if(lineMgrCheckBox.checked())
        {
            addresses = this.appendAddresses(addresses, #LineMgrToken);
        }
    }

    return this.appendAddresses(addresses, nextTokens ? nextTokens.getUIAddresses() : '');
}

Override the PrintMgmtPrintDestinationTokens.parseAddress as that is picked up from the parent class SrsPrintDestinationTokens and make sure it accommodates for our new tokens. This was completely non intuitive and most things had to be specified only here which do not exist in the parent class.
// Parse custom line manager token, to split from standard otherAddress (3rd element) to customToken (4rd element)
protected container parseAddresses(str _addresses, container _availableTokens)
{
    container       availableAddresses;
    str             otherAddresses;
    List            otherAddressesTokens;
    ListEnumerator  listEnum;
    List            customTokenList = new List(Types::String);
    List            otherAddressesList = new List(Types::String);
    str             sep = this.parmAddressSeparator();

    availableAddresses = super(_addresses, _availableTokens);

    otherAddresses = conPeek(availableAddresses, 3);
    otherAddressesTokens = strSplit(otherAddresses, this.parmAddressSeparator());
    listEnum = otherAddressesTokens.getEnumerator();

    while (listEnum.moveNext())
    {
        if (listEnum.current() == #LineMgrToken)
        {
            customTokenList.addEnd(listEnum.current());
        }
        else
        {
            otherAddressesList.addEnd(listEnum.current());
        }
    }

    return [conPeek(availableAddresses, 1), conPeek(availableAddresses, 2), SrsPrintDestinationTokens::list2Str(otherAddressesList, sep), SrsPrintDestinationTokens::list2Str(customTokenList, sep)];
}

The last method you need to modify is setUIAddresses. Just added the lines in red in the method
protected void setUIAddresses(str _addresses)
{
    container availableAddresses = this.parseAddresses(_addresses, conPeek(validTokens, 2));

    // We handle @token@ and @@ so pass the rest to super for further processing
    if(nextTokens)
    {
        nextTokens.setUIAddresses(conPeek(availableAddresses, 3));
    }

    printMgmtMultiSelect.set(this.selectTokens(conPeek(availableAddresses, 1), validTokens));
    printMgmtCtl.text(conPeek(availableAddresses, 1));
    printMgmtCheckBox.value(conPeek(availableAddresses, 2) != '');

    if (lineMgrCheckBox)
    {
        lineMgrCheckBox.value(conPeek(availableAddresses, 4) != '');
    }
}

These are the only changes I made to display the businesslineMgr in the window and picking up the manager's email whenever an email is sent to a subordinate.

So, the following methods were modified (and added) to make it work. I am also attaching the whole file here for easy reference.






Finding print destination email tokens

I had a requirement to look for print mgmt tokens for email to and log it. The following statement lets you do that

PrintMgmtSettings  printMgmtSettings;
    PrintMgmtReportFormat printMgmtReportFormat;
    PrintMgmtDocInstance printMgmtDocInstance;
   

 while select printMgmtSettings
    exists join printMgmtReportFormat
            where printMgmtReportFormat.Name==ssrsReportStr(ReportFORWHICHTHE PRINTMGMTSETTINGSAREDEFINED)
               && printMgmtReportFormat.RecId == printMgmtSettings.ReportFormat
    exists join printMgmtDocInstance
            where printMgmtDocInstance.DocumentType==PrintMgmtDocumentType::CORRECTNODETYPE
               && printMgmtDocInstance.PrintType==PrintMgmtDocInstanceType::Original
               && printMgmtDocInstance.RecId == printMgmtSettings.ParentId
    {
        srsPrintDestinationSettings = new srsPrintDestinationSettings(printMgmtSettings.PrintJobSettings);
        info(srsPrintDestinationSettings.emailTo());      //this prints@HOME@ and @PVTContact@ 
    }


Finding Conditions defined for print mgmt using X++

I had a requirement where I needed to look for print management conditions and then log it.

    PrintMgmtNodeInstance PrintMgmtNodeInstance;
    PrintMgmtSetupDoc PrintMgmtSetupDoc;
    PAECommunications PAECommunications;
    Query             query;
    int i;

 PrintMgmtNodeInstance = new PrintMgmtNodeInstance();
    PrintMgmtNodeInstance.parmNodeDefinition(new PrintMgmtNode_HRM());//or whatever your node is, sales/HCMWorker/depending on your situation this can vary.

    PrintMgmtNodeInstance.parmReferencedTableBuffer(tableBufferForPrintMGMTQuery);

    PrintMgmtSetupDoc = PrintMgmtSetupDoc::construct(PrintMgmtNodeInstance, PrintMgmtDocumentType::YOURPRINTMGMTDocType, CompanyInfo::languageId());
 
 i= PrintMgmtSetupDoc.getInstanceByPos(1).numConditionalSettings(); //the instance is always 1 so this is safe
   
    for (i=1;i<PrintMgmtSetupDoc.getInstanceByPos(1).numConditionalSettings();i++)
    {
        query = PrintMgmtSetupDoc.getInstanceByPos(1).getConditionalSettingByPos(1).parmCondition();//this gives the query
    }

So, the above code for a setup like below gives me the query defined in both conditions


Thursday, January 18, 2018

research refresh reread

Best blog to find out what method to call http://kashperuk.blogspot.co.uk/2010/03/tutorial-reread-refresh-research.html