Microsoft Dynamics AX (Archived)

SQL to AOT Query : Purch Req Approvers

(0) Share

Report

Posted on by Community Member

Hi Folks, hope ye are well.

I'm looking to achieve a result in an AOT Query similar to the following SQL code.

I've started in the AOT with PurchReqTable->PurchReqLine->WorkflowWorkitemTable

maybe its easier to reverse it. Will be working this later today, so just looking for feedback prior.

Also, i cant seem to make the Union option display in the properties.

Thanks as always.

select  core.SUBMITTEDBY,core.SUBMITTEDDATETIME,core.PURCHREQID,core.PURCHREQNAME,core.requisitionstatus,core.numlines,core.amount 
,wf.datasourcename,bj.description,wf.userid,wf.status,null as LineNum,null as pendamount,null as name
from (
select pr.SUBMITTEDBY,pr.SUBMITTEDDATETIME,pr.PURCHREQID,pr.PURCHREQNAME,pr.requisitionstatus
,sum(pl.lineamount) amount, count(linenum) numlines,pr.recid
from purchreqtable pr
join purchreqline pl on pr.RECID = pl.PURCHREQTABLE
where pr.requisitionstatus = 10
--and pr.purchreqid = 
group by pr.SUBMITTEDBY,pr.SUBMITTEDDATETIME,pr.PURCHREQID,pr.PURCHREQNAME,pr.requisitionstatus,pr.recid
) core
join purchreqtable pr1 on core.RECID = pr1.RECID
join purchreqbusinessjustificationcodes bj on bj.recid = pr1.BUSINESSJUSTIFICATION
join workflowworkitemtable wf on core.recid = wf.refrecid and wf.status = 0 and DATASOURCENAME = 'PurchReqTable'
union
select  core.SUBMITTEDBY,core.SUBMITTEDDATETIME,core.PURCHREQID,core.PURCHREQNAME,core.requisitionstatus,core.numlines,core.amount 
,wf.datasourcename,bj.description,wf.userid,wf.status,pl1.LINENUM,pl1.LINEAMOUNT,pl1.ITEMIDNONCATALOG
from (
select pr.SUBMITTEDBY,pr.SUBMITTEDDATETIME,pr.PURCHREQID,pr.PURCHREQNAME,pr.requisitionstatus
,sum(pl.lineamount) amount, count(linenum) numlines,pr.recid
from purchreqtable pr
join purchreqline pl on pr.RECID = pl.PURCHREQTABLE
where pr.requisitionstatus = 10
--and pr.purchreqid = 
group by pr.SUBMITTEDBY,pr.SUBMITTEDDATETIME,pr.PURCHREQID,pr.PURCHREQNAME,pr.requisitionstatus,pr.recid
) core
join purchreqtable pr1 on core.RECID = pr1.RECID
join purchreqline pl1 on pr1.recid = pl1.purchreqtable 
join purchreqbusinessjustificationcodes bj on bj.recid = pl1.BUSINESSJUSTIFICATION
join workflowworkitemtable wf on pl1.recid = wf.refrecid and wf.status = 0 and DATASOURCENAME = 'PurchReqLine'
order by core.purchreqid, linenum

*This post is locked for comments

I have the same question (0)

All responses (22)

Answers (2)

Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

Do you have any question about it?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

just looking for advise on best approach, AOT query structure. Am sure I'll have more solid questions in 2 hours. thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

How can I structure my 'CORE' sql block in the AOT Query and then start joining to the result?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

am thinking i should use a view here

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report

Hi KTJBurbz,

Using multiply view could be good approach here. You can build view on a query and than use this view to build another query and so on.

Another option is to create class that will populate temporary table and use that table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

thanks ievgen, yea, going down the View route at the moment, it makes sense, just starting to get my head around the AOT views/queries. a little long winded, but getting there, first time always the pain. cheers

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

a little strange: when I add a query with 2 levels of datasource (purchreqtable/purchreqline) to a view, i cannot add the fields from purchreqline to the view fields. however i can add the second level fields if i create the two levels in the view (rather than using a query)... why is this? i notice dynamic fields property is greyed to yes when not using the query in the view also. i cant seem to select my aggregates either... #noteasy

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

I don't really know what are you doing but it works for me :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

found my issue (the join was set to existsjoin (accidently)) so I can now add second level _ds fields to the view fields. however, second level aggregates cannot be added. additionally, without using a query in the view, and building from datasource i can only seem to add 1 second level datasource. anyways, i'm progressing, be it slowly.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

actually, you just cant drag and drop the aggregated fields to the view fields, you need to manually add them.

This is what i'm trying to achieve,

this configuration returns no results ( range purchid = valid id, status = in review)

am guessing its to do with the additional levels (hcmworker/person) after the aggregation..? will try a smaller build. and a second embedded view.

Was this reply helpful? Yes No