VIEW: APPS.PA_REP_PROJ_REQHRS_WKD_V

Source

View Text - Preformatted

SELECT pfi.item_quantity, proj.name, pfi.project_id, play.person_id, ppf.full_name, proj.carrying_out_organization_id, to_number(to_char(next_day(pfi.item_date, to_char(sysdate + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - to_number(to_char(sysdate,'d')),'DAY'))-1,'YYYY')), to_number(to_char(next_day(pfi.item_date, to_char(sysdate + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - to_number(to_char(sysdate,'d')),'DAY'))-1,'MM')), to_number(to_char(next_day(pfi.item_date, to_char(sysdate + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - to_number(to_char(sysdate,'d')),'DAY'))-1,'DAY'))-1, pfi.item_date, asgn.assignment_name, asgn.min_resource_job_level, asgn.max_resource_job_level, asgn.start_date, asgn.end_date, NVL(ppm.probability_percentage, 100) from pa_forecast_items pfi, pa_projects_all proj, pa_probability_members ppm, pa_project_players play, pa_project_assignments asgn, pa_project_statuses ps, per_people_x ppf WHERE pfi.forecast_item_type = 'R' and pfi.delete_flag = 'N' and pfi.assignment_id = asgn.assignment_id and pfi.project_id = proj.project_id and asgn.assignment_type = 'OPEN_ASSIGNMENT' and asgn.status_code (+) = ps.project_status_code and ps.project_system_status_code (+) = 'OPEN_ASGMT' and asgn.project_id = proj.project_id and play.project_id = proj.project_id and play.project_role_type = 'PROJECT MANAGER' and TRUNC(sysdate) between TRUNC(play.start_date_active) and TRUNC(nvl(play.end_date_active, sysdate)) and play.person_id = ppf.person_id and ppm.probability_member_id (+) = proj.probability_member_id

View Text - HTML Formatted

SELECT PFI.ITEM_QUANTITY
, PROJ.NAME
, PFI.PROJECT_ID
, PLAY.PERSON_ID
, PPF.FULL_NAME
, PROJ.CARRYING_OUT_ORGANIZATION_ID
, TO_NUMBER(TO_CHAR(NEXT_DAY(PFI.ITEM_DATE
, TO_CHAR(SYSDATE + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - TO_NUMBER(TO_CHAR(SYSDATE
, 'D'))
, 'DAY'))-1
, 'YYYY'))
, TO_NUMBER(TO_CHAR(NEXT_DAY(PFI.ITEM_DATE
, TO_CHAR(SYSDATE + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - TO_NUMBER(TO_CHAR(SYSDATE
, 'D'))
, 'DAY'))-1
, 'MM'))
, TO_NUMBER(TO_CHAR(NEXT_DAY(PFI.ITEM_DATE
, TO_CHAR(SYSDATE + FND_PROFILE.VALUE('PA_GLOBAL_WEEK_START_DAY') - TO_NUMBER(TO_CHAR(SYSDATE
, 'D'))
, 'DAY'))-1
, 'DAY'))-1
, PFI.ITEM_DATE
, ASGN.ASSIGNMENT_NAME
, ASGN.MIN_RESOURCE_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL
, ASGN.START_DATE
, ASGN.END_DATE
, NVL(PPM.PROBABILITY_PERCENTAGE
, 100)
FROM PA_FORECAST_ITEMS PFI
, PA_PROJECTS_ALL PROJ
, PA_PROBABILITY_MEMBERS PPM
, PA_PROJECT_PLAYERS PLAY
, PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECT_STATUSES PS
, PER_PEOPLE_X PPF
WHERE PFI.FORECAST_ITEM_TYPE = 'R'
AND PFI.DELETE_FLAG = 'N'
AND PFI.ASSIGNMENT_ID = ASGN.ASSIGNMENT_ID
AND PFI.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT'
AND ASGN.STATUS_CODE (+) = PS.PROJECT_STATUS_CODE
AND PS.PROJECT_SYSTEM_STATUS_CODE (+) = 'OPEN_ASGMT'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND PLAY.PROJECT_ID = PROJ.PROJECT_ID
AND PLAY.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN TRUNC(PLAY.START_DATE_ACTIVE)
AND TRUNC(NVL(PLAY.END_DATE_ACTIVE
, SYSDATE))
AND PLAY.PERSON_ID = PPF.PERSON_ID
AND PPM.PROBABILITY_MEMBER_ID (+) = PROJ.PROBABILITY_MEMBER_ID