This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Just curious if anyone has created a view of the non-persistant table PERSONAVAIL or has any tips on how to generate a report to show a person's true availability?
When you're in the Person app or Assignment Manager, there is an option to modify a person's availability - e.g. add 4h of work time to their schedule for volunteer OT. Eventually you see a chart for the next 7d with the date and hours the person is available. We want to be able to print out that chart for a person's availability. The problem is that chart is based on non-persistent data created from several MBO's.
I did some more digging into the class files that creates the non-persistent table PERSONAVAIL and came up with the following:
SELECT person.personid, workperiod.workdate, workperiod.workhours, personcal.isprimary, person.locationsite,
(case when
(modavail.workdate is null and modavail.workhours is null) then workperiod.workhours
else modavail.workhours
end) as availworkhours
FROM person INNER JOIN personcal ON (person.personid=personcal.personid) AND (person.locationorg=personcal.orgid) INNER JOIN workperiod ON ((personcal.calnum=workperiod.calnum) AND (personcal.orgid=workperiod.orgid)) AND (personcal.shiftnum=workperiod.shiftnum)
left outer join modavail on (person.personid=modavail.personid) and (workperiod.workdate=modavail.workdate)
WHERE personcal.isprimary=1 AND (workperiod.workdate>=dateadd(day, 0, convert(varchar, getdate(), 101)) AND workperiod.workdate<dateadd(day, 7, convert(varchar, getdate(), 101)))
Testing shows this works so far and I have a draft BIRT report that works as well.
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
> Just curious if anyone has created a view of the non-persistant table PERSONAVAIL or has any tips on how to generate a report to show a person's true availability?
>
> When you're in the Person app or Assignment Manager, there is an option to modify a person's availability - e.g. add 4h of work time to their schedule for volunteer OT. Eventually you see a chart for the next 7d with the date and hours the person is available. We want to be able to print out that chart for a person's availability. The problem is that chart is based on non-persistent data created from several MBO's.
>
One quick follow-up on a question I got from a couple of people. The SQL statement I used hardcoded the date range to be for the next 7d. When I looked at the java classes used to construct PERSONAVAIL table, the classes used a maximo variable, MAXVAR = MODAVAIL. That variable represented the number of days out that the class should use to create the PERSONAVAIL data, which by default happnes to be 7.
As I explained, yes I could have referenced the variable, but since we're a smaller shop and I can control the change to that variable, it was easier to hard code it in the query.
--- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@...> wrote:
>
> I did some more digging into the class files that creates the non-persistent table PERSONAVAIL and came up with the following:
>
> SELECT person.personid, workperiod.workdate, workperiod.workhours, personcal.isprimary, person.locationsite,
> (case when
> (modavail.workdate is null and modavail.workhours is null) then workperiod.workhours
> else modavail.workhours
> end) as availworkhours
> FROM person INNER JOIN personcal ON (person.personid=personcal.personid) AND (person.locationorg=personcal.orgid) INNER JOIN workperiod ON ((personcal.calnum=workperiod.calnum) AND (personcal.orgid=workperiod.orgid)) AND (personcal.shiftnum=workperiod.shiftnum)
> left outer join modavail on (person.personid=modavail.personid) and (workperiod.workdate=modavail.workdate)
> WHERE personcal.isprimary=1 AND (workperiod.workdate>=dateadd(day, 0, convert(varchar, getdate(), 101)) AND workperiod.workdate<dateadd(day, 7, convert(varchar, getdate(), 101)))
>
> Testing shows this works so far and I have a draft BIRT report that works as well.
>
>
> --- In MAXIMO@yahoogroups.com, "Jason Verly" <jason.verly@> wrote:
> >
> > Just curious if anyone has created a view of the non-persistant table PERSONAVAIL or has any tips on how to generate a report to show a person's true availability?
> >
> > When you're in the Person app or Assignment Manager, there is an option to modify a person's availability - e.g. add 4h of work time to their schedule for volunteer OT. Eventually you see a chart for the next 7d with the date and hours the person is available. We want to be able to print out that chart for a person's availability. The problem is that chart is based on non-persistent data created from several MBO's.
> >
>