Zoals gevraagd, het gehele script: (ik heb de Select waarop de pointer staat bij de foutmelding onderstreept)
-- BASELINE COUNT OF ACTIVE RESOURCES
SELECT COUNT(B.RN) FROM AV_USERS_VIEWS40 B WHERE TSUSER = 1
-- QUERY 1 (COLUMNS A - FIN AREA / B - MANAGER / C - AREA / D - NAME / E - ID / F - COMPANY / G - BILLABLE / H - RATE / I - CREDIT CORP / J - CREDIT DEPT / K - EXPECTED HOURS)
SELECT DISTINCT
a.rc01 AS "Area", c.resp1 AS "Manager",b.ds AS "Name", a.rn AS "ID",
b.payroll AS "Company", a.rc02 AS "Rate",
d.credit_corp AS "Credit Corp",
d.credit_dept AS "Credit Dept",
e.expweek AS "Expected hours per week",
b.cal AS "Personal Calendar #",
c.status AS "Financial Area"
FROM av_resource_views40 a,
av_users_views40 b,
av_element_views40 c,
gl_resource_credit d,
av_tvusers_views40 e
WHERE b.tsuser = 1
AND a.rn = b.rn
AND a.rn = d.resource_id(+)
AND a.rn = e.rn
AND c.CATEGORY = 'SSC IT OBS'
AND c.ELEMENT = a.rc01
ORDER BY a.rc01, c.resp1, b.ds
-- Q/A FOR EXCEPTIONS
SELECT DISTINCT B.RN FROM AV_USERS_VIEWS40 B WHERE TSUSER = 1
MINUS
SELECT DISTINCT B.RN
FROM AV_RESOURCE_VIEWS40 A,AV_USERS_VIEWS40 B,AV_ELEMENT_VIEWS40 C,GL_RESOURCE_CREDIT D,AV_TVUSERS_VIEWS40 E
WHERE
B.TSUSER = 1 AND
A.RN = B.RN AND
A.RN = D.RESOURCE_ID(+) AND
A.RN = E.RN AND
C.CATEGORY = 'SSC IT OBS' AND C.ELEMENT = A.RC01
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 2 (COLUMN 10) PLANNED HOURS LAST WEEK
-- PRECONDITION:
-- TRACKVIEW USER MUST UPDATE PERSONAL CALENDAR WITH EXPECTED TIME OFF
-- EXPECTED HOURS MUST BE SET FOR THE TRACKVIEW USER
-- VARIABLES:
-- MON_OF_PREV_WEEK ENTER DATE FOR MONDAY FOR THE LAST WEEK
-- WE_PREV_WEEK ENTER LAST WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- LIST RESOURCE EXCEPTIONS TO THE EXPECTED WEEK TOTAL FOR THE PREVIOUS WEEK
SELECT DISTINCT b.rn, COUNT (f.hol) AS "DAYS OFF",
e.expweek AS "NORMAL HOURS",
e.expweek
- (8 * COUNT (f.hol)) AS "&MON_OF_PREV_WEEK - &WE_PREV_WEEK" -- EXPECTED HOURS FOR THE WEEK
FROM av_users_views40 b, av_tvusers_views40 e,
av_holiday_views40 f
WHERE b.tsuser = 1
AND b.cal = f.cal
AND b.rn = e.rn
AND hol >= TO_DATE ('&MON_OF_PREV_WEEK', 'MM/DD/YYYY')
AND hol <= TO_DATE ('&WE_PREV_WEEK', 'MM/DD/YYYY')
GROUP BY b.rn, e.expweek
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 3 (COLUMN X - ACTUAL HOURS LAST WEEK)
-- PRECONDITION:
-- TRACKVIEW USER ENTERS AND (SAVES (AND/OR) APPROVES) TIMESHEET IN THE GIVEN WEEK
-- VARIABLES:
-- MON_OF_PREV_WEEK ENTER DATE FOR MONDAY FOR THE LAST WEEK
-- WE_PREV_WEEK ENTER LAST WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- RETURNS A RECORD FOR ANY RESOURCE THAT SAVED HOURS IN TRACKVIEW FOR THE GIVEN WEEK (NOT NECESSARILY APPROVED)
SELECT B.RN,SUM(G.TOTAL) AS "ACTUAL HOURS LW"
FROM
AV_USERS_VIEWS40 B,AV_TVPV_TIMESHEET_VIEWS40 G
WHERE
B.TSUSER = 1 AND
B.RN = G.RN AND
G.WEEK >= TO_DATE('&MON_OF_PREV_WEEK','MM/DD/YYYY') AND
G.WEEK <= TO_DATE('&WE_PREV_WEEK','MM/DD/YYYY')
GROUP BY B.RN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 4 (COLUMN 12) PLANNED HOURS THIS WEEK
-- VARIABLES:
-- MON_OF_THIS_WEEK ENTER DATE FOR MONDAY FOR THE CURRENT WEEK
-- WE_THIS_WEEK ENTER CURRENT WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- LIST RESOURCE EXCEPTIONS TO THE EXPECTED WEEK TOTAL FOR THE CURRENT WEEK
-- ????????????????
SELECT DISTINCT b.rn, COUNT (f.hol), e.expweek AS "EXPECTED HOURS",
e.expweek
- (8 * COUNT (f.hol)) AS "&MON_OF_THIS_WEEK - &WE_THIS_WEEK"
FROM av_users_views40 b, av_tvusers_views40 e,
av_holiday_views40 f
WHERE b.tsuser = 1
AND b.cal = f.cal
AND b.rn = e.rn
AND hol >= TO_DATE ('&MON_OF_THIS_WEEK', 'MM/DD/YYYY')
AND hol <= TO_DATE ('&WE_THIS_WEEK', 'MM/DD/YYYY')
GROUP BY b.rn, e.expweek
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 5 (COLUMN Y - ACTUAL (PROJECT) HOURS LAST WEEK
-- VARIABLES:
-- MON_OF_PREV_WEEK ENTER DATE FOR MONDAY FOR THE LAST WEEK
-- WE_PREV_WEEK ENTER LAST WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- RETURNS A RECORD FOR ANY RESOURCE THAT SAVED HOURS AGAINST A NON-SUPPORT PROJECT OF
-- BILLABLE STATUS N80 OR Y IN TRACKVIEW FOR THE GIVEN WEEK (NOT NECESSARILY APPROVED)
SELECT b.rn, SUM (g.total) AS "PROJECT HOURS LW"
FROM av_users_views40 b, av_tvpv_timesheet_views40 g,
av_project_views40 h
WHERE b.tsuser = 1
AND b.rn = g.rn
AND g.proj = h.proj
AND g.proj NOT LIKE 'A0_-00%'
AND h.pc08 NOT IN ('APPLICATION SUPPORT', 'ENVIRONMENT SUPPORT')
AND h.pc02 IN ('Y', 'N80')
AND g.week >= TO_DATE ('&MON_OF_PREV_WEEK', 'MM/DD/YYYY')
AND g.week <= TO_DATE ('&WE_PREV_WEEK', 'MM/DD/YYYY')
GROUP BY b.rn
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 6 (COLUMN Z - ACTUAL (SUPPORT) HOURS LAST WEEK)
-- VARIABLES:
-- MON_OF_PREV_WEEK ENTER DATE FOR MONDAY FOR THE LAST WEEK
-- WE_PREV_WEEK ENTER LAST WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- RETURNS A RECORD FOR ANY RESOURCE THAT SAVED HOURS AGAINST A SUPPORT PROJECT IN TRACKVIEW FOR THE GIVEN WEEK (NOT NECESSARILY APPROVED)
SELECT b.rn, SUM (g.total) AS "SUPPORT HOURS LW"
FROM av_users_views40 b, av_tvpv_timesheet_views40 g,
av_project_views40 h
WHERE b.tsuser = 1
AND b.rn = g.rn
AND g.proj = h.proj
AND ( g.proj LIKE 'A0_-00%'
OR h.pc08 IN ('APPLICATION SUPPORT', 'ENVIRONMENT SUPPORT')
)
AND g.week >= TO_DATE ('&MON_OF_PREV_WEEK', 'MM/DD/YYYY')
AND g.week <= TO_DATE ('&WE_PREV_WEEK', 'MM/DD/YYYY')
GROUP BY b.rn
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 7 (COLUMN AA - ACTUAL (ADMIN) HOURS LAST WEEK)
-- VARIABLES:
-- MON_OF_PREV_WEEK ENTER DATE FOR MONDAY FOR THE LAST WEEK
-- WE_PREV_WEEK ENTER LAST WEEK'S WEEK-ENDING DATE
-- QUERY OUTPUT:
-- RETURNS A RECORD FOR ANY RESOURCE THAT SAVED HOURS AGAINST AN ADMIN PROJECT OF BILLABLE STATUS 'N' IN TRACKVIEW FOR THE GIVEN WEEK (NOT NECESSARILY APPROVED)
SELECT b.rn, SUM (g.total) AS "ADMIN HOURS LW"
FROM av_users_views40 b, av_tvpv_timesheet_views40 g,
av_project_views40 h
WHERE b.tsuser = 1
AND b.rn = g.rn
AND g.proj = h.proj
AND h.pc02 = 'N'
AND g.week >= TO_DATE ('&MON_OF_PREV_WEEK', 'MM/DD/YYYY')
AND g.week <= TO_DATE ('&WE_PREV_WEEK', 'MM/DD/YYYY')
GROUP BY b.rn
------------------------------------------
-- IT WEEKLY RESOURCE ACTIVITIES REPORT --
------------------------------------------
-- BASELINE COUNT OF ACTIVITIES TRACKED TO BY RESOURCES OF PROJECTS THAT HAVEN'T BEEN COMPLETED
SELECT COUNT (b.rn)
FROM av_users_views40 b,
av_element_views40 c,
av_project_views40 h,
av_resreq_views40 i
WHERE b.tsuser = 1
AND c.CATEGORY = 'SSC IT OBS'
AND c.resp1 = h.pc13
AND h.pc11 IN ('ACTIVE')
AND h.proj = i.proj
AND (h.proj LIKE 'A0_-%' OR h.pc02 = 'N')
AND i.ver = 0
AND b.rn = i.rn
AND c.status = 'A' AND h.pc15 <> 'REPORT EXEMPT'
SELECT * FROM AV_ELEMENT_VIEWS40 WHERE CATEGORY = 'SSC IT OBS'
-- QUERY 8 (COLUMN A - AREA / B - MANAGER / C - NAME / D - ID / E - TYPE / F - PROJECT NUMBER / G - PROJECT DESCRIPTION / H - PROJECT LEADER /
-- I - STATUS / J - ACTIVITY NUMBER - K - ACTIVITY DESCRIPTION / L - BILLABLE / M - FISCAL BILLING MONTH / N - FINANCIAL AREA)
SELECT DISTINCT a.rc01 AS "AREA", c.resp1 AS "MANAGER", b.ds AS "NAME",
b.rn AS "ID", h.pc08 AS "TYPE", h.proj AS "PROJECT #",
h.ds AS "PROJECT DESCRIPTION", i.act AS "ACTIVITY #",
j.ds AS "ACTIVITY DESCRIPTION",
h.pc13 AS "PROJECT LEADER",
h.pc15 AS "STATUS",
h.pc02 AS "BILLABLE",
k.month AS "FISCAL BILLING MONTH",
c.status AS "FINANCIAL AREA"
FROM av_resource_views40 a,
av_users_views40 b,
av_element_views40 c,
av_project_views40 h,
av_resreq_views40 i,
av_activity_views40 j,
av_fiscal_yr_info k
WHERE b.tsuser = 1
AND h.proj = i.proj
AND h.pc11 = 'ACTIVE'
AND i.ver = 0
AND b.rn = i.rn
AND a.rn = b.rn
AND h.proj = j.proj
AND i.act = j.act
AND j.ver = 0
AND a.rc01 = c.ELEMENT
AND c.CATEGORY = 'SSC IT OBS'
AND (select sysdate from dual) between k.start_dt and k.end_dt
AND c.status = 'A' AND h.pc15 <> 'REPORT EXEMPT'
ORDER BY b.rn
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 9 (COLUMN O - PLANNED START / Q - PLANNED FINISH / S - PLANNED HOURS)
-- VARIABLES:
-- NONE
-- QUERY OUTPUT:
-- RETURNS RECORDS FOR ALL ACTIVITIES ON ACTIVE PROJECTS THAT HAVE BEEN ASSIGNED TO A RESOURCE IN THE APPS AREA
SELECT DISTINCT h.proj AS "PROJECT #", i.act AS "ACTIVITY #", b.rn AS "ID",
j.es AS "PLANNED START", j.ef AS "PLANNED FINISH",
SUM (i.ra) AS "PLANNED HOURS"
FROM av_resource_views40 a,
av_users_views40 b,
av_element_views40 c,
av_project_views40 h,
av_resreq_views40 i,
av_activity_views40 j
WHERE b.tsuser = 1
AND h.proj = i.proj
AND h.pc11 = 'ACTIVE'
AND i.ver = 99
AND b.rn = i.rn
AND a.rn = b.rn
AND h.proj = j.proj
AND i.act = j.act
AND j.ver = 99
AND a.rc01 = c.ELEMENT
AND c.CATEGORY = 'SSC IT OBS'
AND c.status = 'A' and h.pc15 <> 'REPORT EXEMPT'
GROUP BY h.proj, i.act, b.rn, j.es, j.ef
---------------------------------------------------------------------------------------------------------------------------------------------------------------
-- QUERY 10 (COLUMNS P - ACTUAL START / R - ACTUAL FINISH / T - ACTUAL FINISH)
-- VARIABLES:
-- NONE
-- QUERY OUTPUT:
-- RETURNS RECORDS FOR ALL ACTIVITIES ON ACTIVE PROJECTS THAT HAVE BEEN ASSIGNED TO A RESOURCE IN THE APPS AREA
SELECT DISTINCT h.proj AS "PROJECT #", f.act AS "ACTIVITY #", b.rn AS "ID",
f.sd AS "ACTUAL START", f.fd AS "ACTUAL FINISH",
SUM (f.ra) AS "ACTUAL HOURS"
FROM av_resource_views40 a,
av_users_views40 b,
av_element_views40 c,
av_resactual_views40 f,
av_project_views40 h,
av_activity_views40 j
WHERE b.tsuser = 1
AND h.proj = f.proj
AND h.pc11 = 'ACTIVE'
AND f.ver = 0
AND b.rn = f.rn
AND a.rn = b.rn
AND h.proj = j.proj
AND f.act = j.act
AND j.ver = 99
AND a.rc01 = c.ELEMENT
AND c.CATEGORY = 'SSC IT OBS'
AND c.status = 'A' and h.pc15 <> 'REPORT EXEMPT'
GROUP BY h.proj, f.act, b.rn, f.sd, f.fd
-- QUERY 11 (COLUMNS V - ETC HOURS / W - ACTUAL HOURS LW / X - REMARKS)
-- VARIABLES:
-- WE_PREV_WEEK - THE WEEKENDING DATE OF THE LAST WEEK
-- QUERY OUTPUT:
-- RETURNS RECORDS FOR ALL ACTIVITIES ON ACTIVE PROJECTS THAT HAVE BEEN TRACKED TO BY RESOURCES IN THE APPS AREA
SELECT DISTINCT h.proj AS "PROJECT #", g.act AS "ACTIVITY #", b.rn AS "ID",
g.etc AS "ETC HOURS", SUM (g.total) AS "ACTUAL HOURS LW", g.remarks AS "REMARKS"
FROM av_resource_views40 a,
av_users_views40 b,
av_element_views40 c,
av_tvpv_timesheet_views40 g,
av_project_views40 h,
av_activity_views40 j
WHERE b.tsuser = 1
AND h.proj = g.proj
AND h.pc11 = 'ACTIVE'
AND b.rn = g.rn
AND a.rn = b.rn
AND h.proj = j.proj
AND g.act = j.act
AND j.ver = 99
AND a.rc01 = c.ELEMENT
AND c.CATEGORY = 'SSC IT OBS'
AND week = TO_DATE ('&WE_PREV_WEEK', 'MM/DD/YYYY')
AND c.status = 'A' AND h.pc15 <> 'REPORT EXEMPT'
GROUP BY h.proj, g.act, b.rn, g.etc, g.remarks
:strip_exif()/u/21606/walkingicon.gif?f=community)
