How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)

(1) Share

Report

Posted on by b_radlyjames

3,155

I have been working on creating an AOT query (For a Tile) to show a count of 'Expired' party relationships. (DirPartyRelationship)

All of my attempts, however, seem to have been thwarted by the ValidTimeState setting of the DIrPartyRelationship table which forces some ranges on the 'ValidTo' and 'ValidFrom' fields.

Is there a way to using an AOT query to get only Expired relationships?

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (0)

Report

HI, You can write range in init method of query. Check 'ReturnTableListPage_Expired' AOT range.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Also, you can try adding range on ValidTo field is less than DateTimeUtil::getToday(DateTimeUtil::getUserPreferredTimeZone()).

I think no need to validate ValidFrom as ValidFrom cannot be greater than ValidTo, so if ValidTo is less than today then its a expired record.

Was this reply helpful? Yes No
Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

Hi radlyjames,

You can add a "ValidTo" field as range and in the value, property set the below one.

(lessThanUtcNow()).

It will work.

Thanks,

Girish S.

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

GirishS,

Sadly, you can simply add a range on 'ValidTo'. The query automatically places a range on 'ValidTo' and 'ValidFrom' (Due to the ValidTimeState on the table) that can't be modified or removed in any way (That I could find).

Therefore, it doesn't matter what range you attempt, it will always fail as it can't work right with the validTimeState range.

Mohit,

I will check the 'ReturnTableListPage_Expired'. Thanks!

UPDATE:
To give you an idea, if you get the SQL statement from the Query object, you will see the following:
(T3.VALIDTO<?)) AND ((T3.VALIDFROM<=?) AND (T3.VALIDTO>=?)))
The first one there is from my range on the AOT Query. The second bit is from the valid time state setting. It's forcing in that range. So if you attempt any other ranges it will simply break the query.

Brad

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

You can refer to the HcmWorkerListPage_PastEmployees query where the below said is implemented.

This query will return all the workers who's valid to field of HcmEmployment table is lesser then today, which is expired record.
Query will have PastEmployment datasource which will have range added to ValidTo which will return expired records and then it will have ActiveFutureEmployment datasoruce which will have range added to ValidTo which will return active records.
Both will be joined with not exists join.

Thanks,

Girish S.

Was this reply helpful? Yes No
b_radlyjames 3,155 on at

Like (0)

Report

Girish,

Thank you! I will look at that.

Brad

Was this reply helpful? Yes No
Verified answer

b_radlyjames 3,155 on at

Like (0)

Report

After some trial and error I found how to do this:

1. You CANNOT use composite queries. Not sure why but it doesn't work.

2. In the 'Init' method of the 'Simple Query' put this: this.query().validTimeStateDateTimeRange(DateTimeUtil::minValue(), DateTimeUtil::maxValue());

3. This will free up the 'Valid From' and 'Valid To' ranges for you to use the query date range functions such as '(lessThanUtcNow())'.

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Yes, you can refer to the query I send previously. They are setting the min and max date range to validTimeStateRange on the init method of the query.

Thanks,

Girish S.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

How to create an AOT query to show only 'Expired' Party Relationships (DirPartyRelationship)

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates