Fetch Data from direct SQL Queries in ax 2012 d365fo

 Fetch Data from direct SQL Queries

        Connection  connection;

        Statement   statement;

        str         sql;

        ResultSet   resultSet;

        SqlStatementExecutePermission   perm;

        //sql =  "SELECT COUNT(*) as [NumOfCombinations] FROM " + "( " + "select MyName, MyValue " + "from tab1 " + strFmt("where DATAAREAID = '%1' ",curext()) + "group by MyName, MyValue" + ") a";

        sql =  strFmt("select * from taxtrans where TRANSDATE between CONVERT(date, '%1') and CONVERT(date, '%2')", datetime2Str(DateTimeUtil::newDateTime(fromDateValue,0)), datetime2Str(DateTimeUtil::newDateTime(toDateValue,0)));

        connection = new connection();

        perm = new SqlStatementExecutePermission(sql);

        perm.assert();

        statement = connection.createStatement();

        resultSet = statement.executeQuery(sql);

        while (resultSet.next())

Record Insert List in D365 FO in AX 2012

Record Insert List to insert data in one SQL Call.

Helps in improve performance

RecordInsertList tmpValueReporting = new RecordInsertList(tableNum(VendProvisionalBalanceTmpValue), false, false, false, false, true, vendProvisionalBalanceTmpValue);

while select crosscompany AccountNum, ADLegalEntity, MainAccountId
from vendProvisionalBalanceTmpProcessing
group by AccountNum, MainAccountId, ADLegalEntity
join PostingProfile, IsSummaryAccount, sum(AmountAccounting), sum(AmountReporting), ADLegalEntity
from vendProvisionalBalanceTransTmp
group by AccountNum, PostingProfile, IsSummaryAccount, ADLegalEntity
where vendProvisionalBalanceTransTmp.AccountNum == vendProvisionalBalanceTmpProcessing.AccountNum
//&& vendProvisionalBalanceTransTmp.ADLegalEntity == vendProvisionalBalanceTmpProcessing.ADLegalEntity
&& vendProvisionalBalanceTransTmp.PostingProfile == vendProvisionalBalanceTmpProcessing.PostingProfile
&& vendProvisionalBalanceTransTmp.IsSummaryAccount == vendProvisionalBalanceTmpProcessing.IsSummaryAccount
&& vendProvisionalBalanceTransTmp.ADLegalEntity == vendProvisionalBalanceTmpProcessing.ADLegalEntity
&& vendProvisionalBalanceTransTmp.TransDate < _fromDate
{
vendProvisionalBalanceTmpValue.AccountNum = vendProvisionalBalanceTmpProcessing.AccountNum;
vendProvisionalBalanceTmpValue.ADLegalEntity = vendProvisionalBalanceTmpProcessing.ADLegalEntity;
vendProvisionalBalanceTmpValue.MainAccountId = vendProvisionalBalanceTmpProcessing.MainAccountId;
vendProvisionalBalanceTmpValue.PostingProfile = vendProvisionalBalanceTransTmp.PostingProfile;
vendProvisionalBalanceTmpValue.IsSummaryAccount = vendProvisionalBalanceTransTmp.IsSummaryAccount;
vendProvisionalBalanceTmpValue.Amount = vendProvisionalBalanceTransTmp.AmountAccounting;
vendProvisionalBalanceTmpValue.ReportingCurrencyAmount = vendProvisionalBalanceTransTmp.AmountReporting;
//vendProvisionalBalanceTmpValue.insert();
tmpValueReporting.add(vendProvisionalBalanceTmpValue);
}
tmpValueReporting.insertDatabase();

 

SSRS Report Deployment failed - The number of defined parameter is not equal to the number of cell definitions in the parameter panel. in d365fo

 

Report deployment parameter Error

Error: - The number of defined parameter is not equal to the number of cell definitions in the parameter panel.

solution - check the old/ extra parameter in parameter node

delete ReportParameterLayout tag of extra parameter from XML File to remove parameter issue

AX2012 - How to fix ID Conflict issue

 We can Get ID Conflict issue

while importing Model Store or XPO etc.

I was able to fix somehow by using below link: -

https://github.com/DAXRunBase/AX-2012-R3/tree/master/Fix%20Table%20and%20Field%20ID

http://dev.goshoom.net/2011/11/id-change/

Workflow approver with submitted date and approved date in d365fo

 

public void init()
{
AFZ_PurchaseRequisitionSummaryReportTableTmp prTmp;
PurchReqTable purchReqTable;
PurchReqLine purchReqLine;
WorkflowTrackingStatusTable workflowTrackingStatus, workflowTrackingStatusTable, workflowTrackingStatusTmp;

WorkflowTrackingTable workflowTrackingTable, workflowTrackingTableTmp;
WorkflowTrackingCommentTable workflowTrackingCommentTable;
UserInfo userInfo;
RecId _recId, workflowElementTableRecId;
Name _name, actionTakenByName;
HcmWorkerRecId workerRecId;
HcmWorker hcmWorker;
int i = 1;

super();

delete_from prTmp;

while select purchReqTable
{
prTmp.PurchReqId = purchReqTable.PurchReqId;
prTmp.PurchReqName = purchReqTable.PurchReqName;
prTmp.PRPreparerName = purchReqTable.originatorName();
prTmp.RequisitionStatus = purchReqTable.RequisitionStatus;
prTmp.PRPreparedDate = purchReqTable.CreatedDateTime;
prTmp.SubmittedDateTime = purchReqTable.SubmittedDateTime;
prTmp.AFZPRBudgetId = purchReqTable.AFZPRBudgetId;
prTmp.AFZProjId = purchReqTable.AFZProjId;
prTmp.AFZPRCategoryId = purchReqTable.AFZPRCategoryId;

PurchReqBusJustification PurchReqBusJustification;
select * from PurchReqBusJustification where PurchReqBusJustification.RefRecId == purchReqTable.RecId
&& PurchReqBusJustification.RefTableId == purchReqTable.TableId;

prTmp.Details = PurchReqBusJustification.BusinessJustification;

//prTmp.BusinessJustification = purchReqTable.BusinessJustification;
prTmp.AFZPRNotes = purchReqTable.AFZPRNotes;
prTmp.AFZPurchPoolId = purchReqTable.AFZPurchPoolId;

prTmp.POApproverLevel1 = '';
prTmp.POApproverReceiveDateL1 = utcDateTimeNull();
prTmp.POApproverApproveDateL1 = utcDateTimeNull();

prTmp.POApproverLevel2 = '';
prTmp.POApproverReceiveDateL2 = utcDateTimeNull();
prTmp.POApproverApproveDateL2 = utcDateTimeNull();

prTmp.POApproverLevel3 = '';
prTmp.POApproverReceiveDateL3 = utcDateTimeNull();
prTmp.POApproverApproveDateL3 = utcDateTimeNull();

prTmp.POApproverLevel4 = '';
prTmp.POApproverReceiveDateL4 = utcDateTimeNull();
prTmp.POApproverApproveDateL4 = utcDateTimeNull();

prTmp.POApproverLevel5 = '';
prTmp.POApproverReceiveDateL5 = utcDateTimeNull();
prTmp.POApproverApproveDateL5 = utcDateTimeNull();

prTmp.POApproverLevel6 = '';
prTmp.POApproverReceiveDateL6 = utcDateTimeNull();
prTmp.POApproverApproveDateL6 = utcDateTimeNull();

_recId = purchReqTable.RecId;

while select workflowTrackingStatus order by CreatedDateTime asc
where workflowTrackingStatus.ContextTableId == tableNum(PurchReqTable)
&& workflowTrackingStatus.ContextRecId == _recId
&& workflowTrackingStatus.TrackingStatus == WorkflowTrackingStatus::Completed
join workflowTrackingTable order by CreatedDateTime asc
where workflowTrackingTable.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowtrackingtable.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval
join userInfo
where userInfo.id == workflowTrackingTable.User
{

select workflowTrackingStatusTmp order by RecId desc
where workflowTrackingStatusTmp.ContextTableId == tableNum(PurchReqTable)
&& workflowTrackingStatusTmp.ContextRecId == _recId
&& workflowTrackingStatusTmp.TrackingStatus == WorkflowTrackingStatus::Completed;

if(workflowTrackingStatusTmp.RecId == workflowTrackingStatus.RecId)
{

prTmp.FinalApprovedDate = workflowTrackingTable.CreatedDateTime;

if(prTmp.POApproverLevel1 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel1 = userInfo.name;
else
prTmp.POApproverLevel1 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId,CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL1 = workflowTrackingTableTmp.CreatedDateTime;

prTmp.POApproverApproveDateL1 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel2 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel2 = userInfo.name;
else
prTmp.POApproverLevel2 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL2 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL2 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel3 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel3 = userInfo.name;
else
prTmp.POApproverLevel3 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL3 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL3 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel4 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel4 = userInfo.name;
else
prTmp.POApproverLevel4 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL4 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL4 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel5 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel5 = userInfo.name;
else
prTmp.POApproverLevel5 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;

prTmp.POApproverReceiveDateL5 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL5 = workflowTrackingTable.CreatedDateTime;
}
else if(prTmp.POApproverLevel6 == '' && workflowTrackingTable.User && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval)
{
if(userInfo.name)
prTmp.POApproverLevel6 = userInfo.name;
else
prTmp.POApproverLevel6 = workflowTrackingTable.User;

workflowTrackingTableTmp.clear();
select RecId, WorkflowTrackingStatusTable, CreatedDateTime from workflowTrackingTableTmp order by RecId, CreatedDateTime desc
where workflowTrackingTableTmp.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId
&& workflowTrackingTableTmp.CreatedDateTime < workflowTrackingTable.CreatedDateTime
&& workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Creation
|| workflowTrackingTableTmp.TrackingType == WorkflowTrackingType::Delegation
&& workflowTrackingTableTmp.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowTrackingTableTmp.WorkflowElementTable == workflowTrackingTable.WorkflowElementTable;


prTmp.POApproverReceiveDateL6 = workflowTrackingTableTmp.CreatedDateTime;
prTmp.POApproverApproveDateL6 = workflowTrackingTable.CreatedDateTime;
}
}

}
prTmp.insert();
}

}

}

 

To find out which table access is missing - event create rule permission in d365fo

 class EventSecurity, method isFilterAccessible.

To find out which table access is missing you can change this method in a TEST environment to:

public boolean isFilterAccessible(Query   query)
{
    QueryBuildDataSource    qbds;
    Counter                 i;

    for (i=1;i<=query.dataSourceCount();i++)
    {
        qbds=query.dataSourceNo(i);
        if (! this.isTableAccessible(qbds.table()))
        {
            info(tableId2name(qbds.table()));
            //return false;
        }
    }

    return true;
}

get last workflow approver in d365fo

  while select workflowTrackingTable  order by CreatedDateTime asc 

                                            where workflowTrackingTable.CreatedDateTime >= fromDateTimeValue

                                              &&  workflowTrackingTable.CreatedDateTime <= toDateTimeValue                             

        {

            actionTakenByName = DirPersonUser::userId2Name(workflowTrackingTable.User);//xUserInfo::find(true,workflowTrackingTable.User).name;

            select workflowTrackingStatusTable where workflowTrackingStatusTable.RecId == workflowTrackingTable.WorkflowTrackingStatusTable;

            select workflowCommentTable where workflowCommentTable.WorkflowTrackingTable == workflowTrackingTable.RecId;

            select workflowWorkItemTable where workflowWorkItemTable.CorrelationId == workflowTrackingStatusTable.CorrelationId;

            if(workflowTrackingStatusTable)

            {

                ttsbegin;

                afzWorkflowInquiryTable.DateTime            = workflowTrackingTable.CreatedDateTime;

                afzWorkflowInquiryTable.DocumentType        = workflowTrackingStatusTable.DocumentType;

                afzWorkflowInquiryTable.WorkflowInstance    = workflowTrackingStatusTable.InstanceNumber;

                afzWorkflowInquiryTable.WorkflowStatus      = workflowTrackingStatusTable.TrackingStatus;

                afzWorkflowInquiryTable.WorkflowStatusString = enum2Str(workflowTrackingStatusTable.TrackingStatus);

                afzWorkflowInquiryTable.SubmittedBy         = workflowTrackingStatusTable.getSubmitterUserName();

                afzWorkflowInquiryTable.Document            = workflowTrackingStatusTable.Document;

                if(actionTakenByName)

                    afzWorkflowInquiryTable.ActionTakenBy   = actionTakenByName;

                else

                    afzWorkflowInquiryTable.ActionTakenBy   = workflowTrackingTable.User;

                afzWorkflowInquiryTable.TrackingType        = workflowTrackingTable.TrackingType;

                afzWorkflowInquiryTable.Context             = workflowTrackingTable.TrackingContext;

                afzWorkflowInquiryTable.CompanyName         = workflowTrackingStatusTable.ContextCompanyId;

                afzWorkflowInquiryTable.Comment             = workflowCommentTable.Comment;

                afzWorkflowInquiryTable.TrackingMessage     = workflowCommentTable.TrackingMessage;

                afzWorkflowInquiryTable.Name                = workflowTrackingTable.Name;

                afzWorkflowInquiryTable.DocumentRecId       = workflowTrackingStatusTable.ContextRecId;

                afzWorkflowInquiryTable.insert();

                ttscommit;

            }

                        

        }

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Get only last approver detail: - 

 select workflowTrackingStatus order by CreatedDateTime desc

            where workflowTrackingStatus.ContextTableId == tableNum(purchtable)

            && workflowTrackingStatus.ContextRecId == purchtable.recid

            && workflowTrackingStatus.TrackingStatus == WorkflowTrackingStatus::Completed

            join workflowTrackingTable order by CreatedDateTime desc

            where workflowTrackingTable.WorkflowTrackingStatusTable == workflowTrackingStatus.RecId

            && workflowtrackingtable.TrackingContext == workflowtrackingcontext::WorkItem

            && workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval

            join userInfo

            where userInfo.id == workflowTrackingTable.User;

        //_purchaseOrderHeader.User =   HcmWorker::findByPerson(DirPersonuser::find(currentUserId).PersonParty).name();

        _purchaseOrderHeader.User =   UserInfoHelp::userName(workflowTrackingTable.User);

        _purchaseOrderHeader.UserPrintedBy =  UserInfoHelp::userName(curUserId());

        _purchaseOrderHeader.UserOriginator =  UserInfoHelp::userName(purchtable.CreatedBy);

        

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SQL Workflow Approval code

select * from workflowTrackingStatusTable a -- order by createdDateTime Asc

inner join WorkflowTrackingTable b

on a.RecId = b.WorkflowTrackingStatusTable

and a.TrackingStatus = 1

and a.ContextRecId = 5637145328 --(PurchTable/ PurchReqTable RecID)

and a.ContextTableId = 20050 --(PurchTable/ PurchReqTable TableId)

and b.TrackingType = 4

and b.trackingContext = 5

order by a.createdDateTime, b.createdDateTime Asc

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SQL Get Table ID

select * from SysTableIdView where Name = 'PURCHTABLE'

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////