Approved and unapproved time

J

JungleBoy

Hi ,

is there a way to see time which has been approved by the project manager
and time which was actually logged by a resource. I created a pivot table
with Actual Work (hourses closked by resource) and Actual Work protected
(approved hours), but it gives the same figure and I can se ethat there is
still time which is unapproved.

Is these any other field or way I can find this out.

Thanks
 
J

James Fraser

Hi ,

is there a way to see time which has been approved by the project manager
and time which was actually logged by a resource. I created a pivot table
with Actual Work (hourses closked by resource) and Actual Work protected
(approved hours), but it gives the same figure and I can se ethat there is
still time which is unapproved.

Is these any other field or way I can find this out.
Click to expand...

Presuming you are talking about Project Server 2003:
This information is stored in the database, but there is no interface
to this data in Project Server out of the box. Using SQL queries you
can get at the information; our company has written reports of this
data for clients.

Some of the states that time can be in:
1. Time is entered and saved by the task owner
2. Time is submitted by the task owner but not accepted by the PM.
3. Time is accepted but not in the project plan yet.
4. Time is accepted and in the project's Actual Work Protected.
5. Time is rejected.


James Fraser
 
E

Ernie Longino

JungleBoy said:
Hi ,

is there a way to see time which has been approved by the project manager
and time which was actually logged by a resource. I created a pivot table
with Actual Work (hourses closked by resource) and Actual Work protected
(approved hours), but it gives the same figure and I can se ethat there is
still time which is unapproved.

Is these any other field or way I can find this out.

Thanks
Click to expand...

We had the same need ... I worked with our SQL DBA, who wrote the following
queries for Project Server 2003:

/*****************************************************/
/** List all Submitted but pending tasks by manager **/
/** for ONLY active resources. **/
/*****************************************************/

Select b.RES_NAME as MANAGER,
d.PROJ_NAME,
c.RES_NAME as RESOURCE,
a.TASK_NAME,
a.WASSN_SEND_UPDATE_DATE
from dbo.MSP_WEB_ASSIGNMENTS as a
inner join dbo.MSP_WEB_RESOURCES as b
on a.WRES_ID_MGR = b.WRES_ID
inner join dbo.MSP_WEB_RESOURCES as c
on a.WRES_ID = c.WRES_ID
inner join dbo.MSP_WEB_PROJECTS as d
on a.WPROJ_ID = d.WPROJ_ID
Where a.WASSN_SEND_UPDATE_NEEDED = 1
and a.WASSN_ACTUALS_PENDING = 1
and a.WASSN_UPDATE_STATUS = 1
and a.WASSN_SEND_UPDATE_DATE IS NOT NULL
and c.WRES_IS_ENABLED = 1 -- Active Flag
order by b.RES_NAME, d.PROJ_NAME, c.RES_NAME


/*****************************************************/
/** List all Submitted but pending tasks by manager **/
/** for ONLY inactive resources. **/
/*****************************************************/

Select b.RES_NAME as MANAGER,
d.PROJ_NAME,
c.RES_NAME as RESOURCE,
a.TASK_NAME,
a.WASSN_SEND_UPDATE_DATE
from dbo.MSP_WEB_ASSIGNMENTS as a
inner join dbo.MSP_WEB_RESOURCES as b
on a.WRES_ID_MGR = b.WRES_ID
inner join dbo.MSP_WEB_RESOURCES as c
on a.WRES_ID = c.WRES_ID
inner join dbo.MSP_WEB_PROJECTS as d
on a.WPROJ_ID = d.WPROJ_ID
Where a.WASSN_SEND_UPDATE_NEEDED = 1
and a.WASSN_ACTUALS_PENDING = 1
and a.WASSN_UPDATE_STATUS = 1
and a.WASSN_SEND_UPDATE_DATE IS NOT NULL
and c.WRES_IS_ENABLED = 0 -- Inactive Flag
order by b.RES_NAME, d.PROJ_NAME, c.RES_NAME


/*****************************************************/
/** List all Submitted but pending tasks by manager **/
/** for ONLY active Managers and active Resources **/
/*****************************************************/

Select b.RES_NAME as MANAGER,
d.PROJ_NAME,
c.RES_NAME as RESOURCE,
a.TASK_NAME,
a.WASSN_SEND_UPDATE_DATE
from dbo.MSP_WEB_ASSIGNMENTS as a
inner join dbo.MSP_WEB_RESOURCES as b
on a.WRES_ID_MGR = b.WRES_ID
inner join dbo.MSP_WEB_RESOURCES as c
on a.WRES_ID = c.WRES_ID
inner join dbo.MSP_WEB_PROJECTS as d
on a.WPROJ_ID = d.WPROJ_ID
Where a.WASSN_SEND_UPDATE_NEEDED = 1
and a.WASSN_ACTUALS_PENDING = 1
and a.WASSN_UPDATE_STATUS = 1
and a.WASSN_SEND_UPDATE_DATE IS NOT NULL
and b.WRES_IS_ENABLED = 1 -- Active Manager
and c.WRES_IS_ENABLED = 1 -- Active Resource
order by b.RES_NAME, d.PROJ_NAME, c.RES_NAME


/*****************************************************/
/** List all Submitted but pending tasks by manager **/
/** for ONLY INactive Managers and active Resources **/
/*****************************************************/

Select b.RES_NAME as MANAGER,
d.PROJ_NAME,
c.RES_NAME as RESOURCE,
a.TASK_NAME,
a.WASSN_SEND_UPDATE_DATE
from dbo.MSP_WEB_ASSIGNMENTS as a
inner join dbo.MSP_WEB_RESOURCES as b
on a.WRES_ID_MGR = b.WRES_ID
inner join dbo.MSP_WEB_RESOURCES as c
on a.WRES_ID = c.WRES_ID
inner join dbo.MSP_WEB_PROJECTS as d
on a.WPROJ_ID = d.WPROJ_ID
Where a.WASSN_SEND_UPDATE_NEEDED = 1
and a.WASSN_ACTUALS_PENDING = 1
and a.WASSN_UPDATE_STATUS = 1
and a.WASSN_SEND_UPDATE_DATE IS NOT NULL
and b.WRES_IS_ENABLED = 0 -- INActive Manager
and c.WRES_IS_ENABLED = 1 -- Active Resource
order by b.RES_NAME, d.PROJ_NAME, c.RES_NAME




/*****************************************************/
/** List all Submitted but pending tasks by manager **/
/** for ONLY active Managers and INactive Resources **/
/*****************************************************/

Select b.RES_NAME as MANAGER,
d.PROJ_NAME,
c.RES_NAME as RESOURCE,
a.TASK_NAME,
a.WASSN_SEND_UPDATE_DATE
from dbo.MSP_WEB_ASSIGNMENTS as a
inner join dbo.MSP_WEB_RESOURCES as b
on a.WRES_ID_MGR = b.WRES_ID
inner join dbo.MSP_WEB_RESOURCES as c
on a.WRES_ID = c.WRES_ID
inner join dbo.MSP_WEB_PROJECTS as d
on a.WPROJ_ID = d.WPROJ_ID
Where a.WASSN_SEND_UPDATE_NEEDED = 1
and a.WASSN_ACTUALS_PENDING = 1
and a.WASSN_UPDATE_STATUS = 1
and a.WASSN_SEND_UPDATE_DATE IS NOT NULL
and b.WRES_IS_ENABLED = 1 -- Active Manager
and c.WRES_IS_ENABLED = 0 -- INActive Resource
order by b.RES_NAME, d.PROJ_NAME, c.RES_NAME
 
M

Maurício Kobren

Ernie,
Your queries are very helpfull. Now it's possible to see the pending tasks
submitted by the resources.
But this solve part of my problems. I also need to know the hours submitted
by the resources in the pending tasks, day by day.
Do you kwon if there is a way to do that?

Att,

Maurício Kobren
 
E

Ernie Longino

Maurício,

We have not been able to identify where the hours are stored in the
database. I would be very interested if anyone has been successful doing
this.

Ernie
 
M

Maurício Kobren

Ernie,

I posted the question in a new thread and I discovered that the hours are
stored in the MSP_WEB_WORK table.

This select returns the data that I need:

SELECT
a.WPROJ_ID,
r.WRES_ID,
r.RES_NAME,
a.TASK_NAME,
'Plan',
(w.WWORK_VALUE/480000) AS 'HDs Real',
Convert(Varchar,Convert(DateTime,w.WWORK_START,121),103) AS 'Inicio',
Convert(Varchar,Convert(DateTime,w.WWORK_FINISH,121),103) AS 'Fim'

FROM
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_RESOURCES r,
MSP_WEB_WORK w

WHERE
r.WRES_ID = w.WRES_ID AND
a.WASSN_ID = w.WASSN_ID AND
a.WRES_ID = r.WRES_ID AND
((w.WWORK_START Between '2007-05-28 00:00:00' And '2007-06-01 00:00:00') AND
(w.WWORK_APPROVAL_STATUS=1) AND
(w.WWORK_UPDATE_STATUS=1))

ORDER BY
Convert(Varchar,Convert(DateTime,w.WWORK_START,121),103)



Att,

Maurício.

==============================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Approved abd unapproved time 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Approved Versus Unapproved Hours in PS2007 1
Actual hours disappear after submitting and approved 0
How can I determine if there is submitted but not approved time - 2
Admin Time handling and Reporting 1
Work Budget Salary vs Real Time Salary 0
Actual work protected and actual work - PS2003 1

Top