Get Current User running report list in Ax 2012 from SQL DB

--Query

SELECT UserInfo.[Name],
ElementName AS [Report Name],
UserInfo.[Enable] AS [Active Employee]
INTO #temp
FROM SysLastValue WITH(NOLOCK)
INNER JOIN UserInfo WITH(NOLOCK)
                ON UserInfo.ID = SysLastValue.UserID
WHERE UserID NOT IN ('Admin', '')
     AND RecordType = 18 /* Report */



SELECT t.[Report Name],
(SELECT COUNT(DISTINCT [Name])
   FROM #temp sub
WHERE sub.[Active Employee] = 1
AND sub.[End User] = 1
AND sub.[Report Name] = t.[Report Name]) AS [# of Active Employees Who Ran The Report]
FROM #temp t
GROUP BY t.[Report Name]
ORDER BY t.[Report Name]

DROP TABLE #temp

Change default Source and Target layers when comparing code in Ax 2012

Change default layers when comparing code

Classes- --- SysCompare.initContext() method

    if (comboBox1.getText(comboBox1.selection()) == comboBox2.getText(comboBox2.selection()) && comboBox2.items() > comboBox2.selection() + 1 )
    {
        comboBox2.selection(comboBox2.selection()+1);

       
        if (comboBox1.items() == comboBox2.items() - 1)
        {
            if (comboBox1.items() > 2 && Global::strEndsWith(comboBox2.getText(comboBox2.items() - 1), ' (SYS)'))
            {
                comboBox1.selection(comboBox1.items() - 2);
                comboBox2.selection(comboBox2.items() - 2);
            }
        }
    }
 
    else if (comboBox1.items() > 1 && comboBox2.items() == 1 && Global::strEndsWith(comboBox2.getText(1 - 1), ' (xpo)'))
    {
        // Compare last layer to the imported XPO
        comboBox1.selection(comboBox1.items() - 1); // Set first drop down to last possible option
    }

Gets the AX table ID for the table name in SQL Function

ALTER FUNCTION  fnGetAXTableID
(
    @tableName nvarchar(100)
)
RETURNS int
AS
BEGIN
    DECLARE @tableNum int
 
    SELECT @tableNum = TableID
    FROM SQLDictionary
    WHERE [Name] = @tableName
        AND FieldID = 0
        AND Array = 0

    RETURN @tableNum

END

//Call Scalar Function in SQL Query

select  dbo.fnGetAXTableID('MyTable') as [TableId]

Get the next unique file name

//Get the next unique file name

Input:
fileOriginalsPath (path folder)
fileName

fileNameTemp = Global::fileNameNext(fileOriginalsPath + fileName);

Modify the permissions of the Admin group , After creation of new security Key

static void San_GrantAccessAdminGroup(Args _args)
{
    #Admin
    SecurityKeySet  securitySet;
    ;
    setPrefix(funcName());

    securitySet = new SecurityKeySet();
    securitySet.loadGroupRights(#AdminUserGroup, '');

    securitySet.access(securitykeynum("SecurityKey"), AccessType::Delete);

    xAccessRightsList::saveSecurityRights(securitySet.pack(), #AdminUserGroup, '');
}

Print Management Tool for doing testing (Testing and Development Env) in AX 2012

//Contract Class
[
    DataContractAttribute,
    SysOperationGroupAttribute('Queryfilter', 'Select', '1'),
    SysOperationGroupAttribute('PrintMgmtSettings', 'Print management settings', '2')
]
class San_PrintManagementDataContract
{
    Email                   email;
    PrintMgmtNodeType       modType;
    PrintMgmtDocumentType   docType;
}

[
    DataMemberAttribute,
    SysOperationGroupMemberAttribute('Queryfilter'),
    SysOperationDisplayOrderAttribute('2')
]
public PrintMgmtDocumentType parmDocType(PrintMgmtDocumentType _docType = docType)
{
    docType = _docType;

    return docType;
}

[
    DataMemberAttribute,
    SysOperationGroupMemberAttribute('PrintMgmtSettings'),
    SysOperationDisplayOrderAttribute('1')
]
public Email parmEmail(Email _email = email)
{
    email = _email;

    return email;
}

[
    DataMemberAttribute,
    SysOperationGroupMemberAttribute('Queryfilter'),
    SysOperationDisplayOrderAttribute('1')
]
public PrintMgmtNodeType parmModuleType(PrintMgmtNodeType _modType = modType)
{
    modType = _modType;

    return modType;
}

//Create Batch class

class San_PrintManagementBatch extends SysOperationServiceController
{
    San_PrintManagementDataContract dataParms;
}

protected ClassDescription defaultCaption()
{
    return 'Print management settings update';
}

public void new(IdentifierName _className = '', IdentifierName _methodName = '', SysOperationExecutionMode _executionMode = 0)
{
    super();

    this.parmClassName(_className);
    this.parmMethodName(_methodName);
    this.parmExecutionMode(_executionMode);
}

public static San_PrintManagementBatch construct()
{
    San_PrintManagementBatch printMgmtBatch;

    printMgmtBatch = new San_PrintManagementBatch (classStr(San_PrintManagementBatch ), methodStr(San_PrintManagementBatch , runProcess));

    return printMgmtBatch;
}

public static void main(Args _args)
{
    San_PrintManagementBatch printMgmtBatch;
    ;

    printMgmtBatch = San_PrintManagementBatch ::construct();
    printMgmtBatch.startOperation();
}

public void runProcess(San_PrintManagementDataContract  _data)
{
    Query                           q = new Query();
    QueryRun                        qr;
    QueryBuildDataSource            qbdsPrintMgmtSetting;
    QueryBuildDataSource            qbdsPrintMgmtDoc;

    PrintMgmtSettings               printMgmtSettings;

    // set up query
    qbdsPrintMgmtDoc = q.addDataSource(tableNum(PrintMgmtDocInstance));

    qbdsPrintMgmtSetting = qbdsPrintMgmtDoc.addDataSource(tableNum(PrintMgmtSettings));
    qbdsPrintMgmtSetting.joinMode(JoinMode::InnerJoin);
    qbdsPrintMgmtSetting.relations(false);
    qbdsPrintMgmtSetting.addLink(fieldNum(PrintMgmtDocInstance, RecId), fieldNum(PrintMgmtSettings, ParentId));

    // if a module is selected, filter on that
    if (_data.parmModuleType())
    {
        qbdsPrintMgmtDoc.addRange(fieldNum(PrintMgmtDocInstance, NodeType)).value(queryValue(_data.parmModuleType()));
    }

    // if a document type is selected, filter on that
    if (_data.parmDocType())
    {
        qbdsPrintMgmtDoc.addRange(fieldNum(PrintMgmtDocInstance, DocumentType)).value(queryValue(_data.parmDocType()));
    }

    qr = new QueryRun(q);

    // loop through the query
    while (qr.next())
    {
        // Get the table buffer
        printMgmtSettings = qr.get(tableNum(PrintMgmtSettings));

        // update the settings
        this.updatePrintSettings(printMgmtSettings, _data);
    }
}

private void updatePrintSettings(PrintMgmtSettings _printMgmtSettings, San_PrintManagementDataContract  _dataContract)
{
    PrintMgmtSettings               printMgmtSettingsUpdate;
    SRSPrintDestinationSettings     printDestSettings;

    // if printer settings were found
    if (_printMgmtSettings.PrintJobSettings)
    {
        ttsBegin;
        select forUpdate printMgmtSettingsUpdate where printMgmtSettingsUpdate.RecId == _printMgmtSettings.RecId;

        printDestSettings =  new SRSPrintDestinationSettings(_printMgmtSettings.PrintJobSettings);

        // if an email was set and an updated email was given
        if (printDestSettings.emailTo() && _dataContract.parmEmail())
        {
            printDestSettings.emailTo(_dataContract.parmEmail());
        }

        // update the settings on the record
        printMgmtSettingsUpdate.PrintJobSettings = printDestSettings.pack();
        printMgmtSettingsUpdate.update();
        ttsCommit;
    }
}

//Create Action Menu Item ("San_PrintManagementTool"), Label "Print management tool"

Moving security roles from one layer to another layer while keeping users assigned to their roles in AX 2012

//Create New Table

TABLE #ST_SecurityRolesToUpdate

FIELD #IsUpdated
FIELD #NewID
FIELD #OldId
FIELD #RoleName

Index- Idx - Property - Allow Duplicate to No
NewId
OldId
RoleName


//Job to Get Current Security Role list to migrate new layer

static void ST_GetCurSecurityRoleIds(Args _args)
{
    ST_SecurityRolesToUpdate       securityRolesToUpdate;
    SecurityUserRole                securityUserRole; //Use only if wants to take against User in AX
    SecurityRole                    securityRole;
    ;
    while select securityRole
    {
        securityRolesToUpdate.OldId = securityRole.RecId;
        securityRolesToUpdate.RoleName = securityRole.AotName;
        securityRolesToUpdate.insert();
    }
}

Note:
--> In your target layer, right-click on the role and choose Duplicate. The newly created duplicate will have a name like CopyOfXYZ.
--> Go into the layer of the object - where it originally existed and delete the object
--> Go back to the target  layer and remove the CopyOf from the name of the duplicate object created   This will ensure that the name of the new object will be the same as the old object name.
--> Then run new Job

//Update security Role to new

static void ST_UserSecurityRoleUpdate(Args _args)
{
    ST_SecurityRolesToUpdate       securityRolesToUpdate;
    SecurityUserRole                securityUserRole, newUserRole;
    SecurityRole                    securityRole;
    OMUserRoleOrganization          omUserRoleOrg, newUserRoleOrg;
    CompanyInfo                     companyInfo;
    int64                           oldId;

    while select forUpdate securityRolesToUpdate join securityRole
        where securityRole.aotname == securityRolesToUpdate.RoleName
    {
        ttsBegin;
        securityRolesToUpdate.Newid = securityRole.RecId;
        securityRolesToUpdate.update();
        ttsCommit;
    }

    while select forupdate securityRolesToUpdate
        where securityRolesToUpdate.oldId != securityRolesToUpdate.newid &&
        securityRolesToUpdate.IsUpdated == NoYes::No &&
        securityRolesToUpdate.NewID != 0 && securityRolesToUpdate.OldId != 0
    {
        while select securityUserRole
            join securityRole
            where securityUserRole.SecurityRole == securityRolesToUpdate.OldId &&
                securityrole.AotName == securityRolesToUpdate.RoleName
        {
            oldId = securityUserRole.SecurityRole;
            newUserRole.User = securityUserRole.User;
            newUserRole.SecurityRole = securityRolesToUpdate.NewId;
            newUserRole.AssignmentMode = securityUserRole.AssignmentMode;
            newUserRole.AssignmentStatus = securityUserRole.AssignmentStatus;
            SecuritySegregationOfDuties::assignUserToRole(newUserRole);
        }

        ttsBegin;
        securityRolesToUpdate.IsUpdated = NoYes::Yes;
        securityRolesToUpdate.update();
        ttsCommit;

        while select omUserRoleOrg
            join companyInfo
            where omUserRoleOrg.OMInternalOrganization == companyInfo.RecId &&
            omUserRoleOrg.SecurityRole == oldId
        {
            ttsBegin;

            newUserRoleOrg.User = omUserRoleOrg.User;
            newUserRoleOrg.SecurityRole = securityRolesToUpdate.NewId;
            newUserRoleOrg.OMInternalOrganization = omUserRoleOrg.OMInternalOrganization;
            newUserRoleOrg.SecurityRoleAssignmentRule = omUserRoleOrg.SecurityRoleAssignmentRule;
            newUserRoleOrg.OMHierarchyType = 0;

            EePersonalDataAccessLogging::logUserRoleChange(newUserRoleOrg.SecurityRole,
                newUserRoleOrg.omInternalOrganization,
                newUserRoleOrg.User,
                AddRemove::Add);

            newUserRoleOrg.insert();

            ttsCommit;
        }

    }

    info("Done");
}

To AOT Objects and their elements with Layer information

Table : UtilIdElements (System Table)

//From above Table - we can get specified only layer changes Elements

Labels informative finder in AX

static void San_findLabelTextbyID(Args _args)
{
    define.language('en_us')
    define.module('SYS')
    define.Id(451)

    define.labelId('@SYS451')
    define.sysLBlLanguage('en-us')
 
    SysModelElementLabel       sysModelElementLabel ;
    str                         labelText; 

    while select sysModelElementLabel
        where sysModelElementLabel.id == #Id
        && sysModelElementLabel.language == #language
        && sysModelElementLabel.Module == #module
    {
        info(strFmt('Label file:%1, Description: %2',sysModelElementLabel.Module,sysModelElementLabel.Text));
    }

    labelText= (SysLabel::labelId2String(literalStr(#labelId),#sysLBLanguage));

    info(strFmt('Label value: %1, label text: %2',literalStr(#labelId),labelText));

}

Calculating Customer Aging Balances through X++

private void CalcCustAging(AccountNum _invoiceAccount) { CustVendTable custVendTable; CustVendAgingStatistics custVendAgingStatistics; TmpAccountSum tmpAccountSum;

; custVendTable = CustTable::find(_invoiceAccount); custVendAgingStatistics = CustVendAgingStatistics::construct(custVendTable,

#agingFormat, DateTransactionDuedate::DueDate,True); custVendAgingStatistics.calcStatistic(); tmpAccountSum.setTmpData(custVendAgingStatistics.tmpAccountsum()); cachedaccountSummary = new Map(Types::Integer,Types::Real); cachedaccountSummaryTxt = new Map(Types::Integer,Types::String); while select tmpAccountSumLocal {

info(strfmt("%1,%2",tmpAccountSumLocal.Txt,tmpAccountSumLocal.Balance03)); } }