AnsweredAssumed Answered

Open Timesheets query

Question asked by Rajani on Aug 20, 2010
Latest reply on Aug 20, 2010 by Rajani
Hello,

I am building a porltet to show all timesheet details. When I join timeperiods and srm_resources tables with prtimesheet, it is brining only timesheets that are touched. How do I get open timesheets?

I came up with the following query to get open (untouched) timesheets, but it is not giving me all open timesheets. I may be missing a condition in joining the tables (prtimeperiods, prtimesheet, srm_resources and prj_resources).

I need to get done with this very soon.

Please let me know your suggestions

Also, any tips on improving query performance? Any one used SQL LAB?


SELECT R.ID RESID,
R.UNIQUE_NAME EID,
R.FULL_NAME FULLNAME,
R.EMAIL EMAIL,
R1.ID MANAGER_ID,
R1.FULL_NAME MANAGER,
R1.EMAIL MANAGER_EMAIL,
TP.PRSTART TIMEPERIODSTART,
TP.PRFINISH TIMEPERIODEND,
TP.PRID TPID,
0 TSID,
0 TSSTATUS,
'OPEN' TSSTATUS_NAME,
O.PARTITION_CODE PARTITION,
R.PERSON_TYPE PERSON_TYPE,
v1.name PERSON_TYPE_NAME,
PR.PRISOPEN,
0 AS pending_actuals,
0 AS posted_actuals

FROM
prtimeperiod tp,
srm_resources r,
srm_resources r1,
odf_ca_Resource o,
prj_resources pr,
cmn_lookups_v v1
WHERE
r.DATE_OF_HIRE < TP.PRFINISH AND (R.DATE_OF_TERMINATION > TP.PRFINISH OR R.DATE_OF_TERMINATION IS NULL) and
tp.prisopen =1 and r.IS_ACTIVE = 1 and
r.id = pr.prid and PR.PRISOPEN = 1 and
tp.prid not in (select prtimeperiodid from prtimesheet) and
r.MANAGER_ID = r1.USER_ID and
r.id=o.id and
r.id = pr.prid and
tp.prisopen=1 and
pr.prisopen =1 and
r.is_active=1 and
r.person_type = v1.id(+) and
v1.lookup_type(+) = 'SRM_RESOURCE_TYPE' and
v1.language_code(+) = 'en' and

Thank you,
Rajani.

Outcomes