eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJECT_ASSIGNMENTS_V

Source

View Text - Preformatted


SELECT asgn.rowid ,asgn.assignment_id ,asgn.assignment_name ,asgn.assignment_type ,asgn.multiple_status_flag ,asgn.record_version_number ,asgn.apprvl_status_code ,ps1.project_status_name apprvl_status_name ,asgn.status_code status_code ,decode (asgn.multiple_status_flag,'Y', pl.meaning, ps.project_status_name) status_name ,asgn.project_id ,proj.carrying_out_organization_id project_organization_id ,pa_resource_utils.get_organization_name(proj.carrying_out_organization_id) project_organization_name ,asgn.project_role_id ,prt.meaning project_role_name ,asgn.resource_id ,decode(res.resource_name, null, decode(asgn.assignment_type, 'OPEN_ASSIGNMENT', res.resource_name, pa_resource_utils.get_resource_name(asgn.resource_id)), res.resource_name) resource_name ,res.resource_organization_id resource_organization_id ,pa_resource_utils.get_organization_name(res.resource_organization_id) resource_organization ,res2.resource_organization_id resource_cur_organization_id ,pa_resource_utils.get_organization_name(res2.resource_organization_id) resource_cur_organization ,res2.manager_id manager_id ,res2.manager_name manager_name ,asgn.project_party_id ,psp.project_subteam_id ,pst.name project_sub_team_name ,psp.project_subteam_party_id ,asgn.start_date ,asgn.end_date ,proj.name ,proj.segment1 ,(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ) assignment_duration ,asgn.assignment_effort ,asgn.extension_possible ,asgn.min_resource_job_level ,asgn.max_resource_job_level ,asgn.assignment_number ,asgn.additional_information ,asgn.location_id ,loc.city ,loc.region ,loc.country_code ,ftt.territory_short_name ,asgn.description ,asgn.note_to_approver ,asgn.calendar_id ,caltl.calendar_name ,asgn.calendar_type ,asgn.resource_calendar_percent ,asgn.work_type_id ,wt.name ,asgn.expense_owner ,pl1.meaning ,asgn.expense_limit ,asgn.expense_limit_currency_code ,asgn.fcst_job_id ,pj.name ,asgn.fcst_job_group_id ,pjg.displayed_name ,asgn.expenditure_org_id ,pa_expenditures_utils.GetOrgTlName(asgn.expenditure_org_id) ,asgn.expenditure_organization_id ,pa_expenditures_utils.GetOrgTlName(asgn.expenditure_organization_id) ,asgn.expenditure_type_class ,asgn.expenditure_type ,asgn.fcst_tp_amount_type ,pl2.meaning ,asgn.staffing_priority_code ,pl3.meaning ,asgn.staffing_owner_person_id ,PA_RESOURCE_UTILS.get_person_name_no_date(asgn.staffing_owner_person_id) ,proj.project_type ,asgn.revenue_bill_rate ,asgn.revenue_currency_code ,asgn.markup_percent ,ps.project_system_status_code ,nvl(asgn.no_of_active_candidates, 0) ,asgn.source_assignment_id ,asgn.assignment_template_id ,asgn.mass_wf_in_progress_flag ,asgn.competence_match_weighting ,asgn.availability_match_weighting ,asgn.job_level_match_weighting ,asgn.search_min_availability ,ftt2.territory_short_name ,asgn.search_country_code ,pos.name ,asgn.search_exp_org_struct_ver_id ,pa_expenditures_utils.GetOrgTlName(asgn.search_exp_start_org_id) ,asgn.search_exp_start_org_id ,asgn.search_min_candidate_score ,asgn.enable_auto_cand_nom_flag ,asgn.last_auto_search_date ,proj.enable_automated_search ,asgn.pending_approval_flag ,asgn.bill_rate_override ,asgn.bill_rate_curr_override ,asgn.markup_percent_override ,asgn.discount_percentage ,asgn.rate_disc_reason_code ,asgn.tp_rate_override ,asgn.tp_currency_override ,asgn.tp_calc_base_code_override ,asgn.tp_percent_applied_override ,proj.multi_currency_billing_flag ,asgn.creation_date ,PA_GENERATE_FORECAST_PUB.get_forecast_gen_date(asgn.project_id) ,asgn.transfer_price_rate ,asgn.transfer_pr_rate_curr ,asgn.resource_list_member_id ,pa_planning_resource_utils.get_plan_res_combination(asgn.resource_list_member_id) ,asgn.attribute_category ,asgn.attribute1 ,asgn.attribute2 ,asgn.attribute3 ,asgn.attribute4 ,asgn.attribute5 ,asgn.attribute6 ,asgn.attribute7 ,asgn.attribute8 ,asgn.attribute9 ,asgn.attribute10 ,asgn.attribute11 ,asgn.attribute12 ,asgn.attribute13 ,asgn.attribute14 ,asgn.attribute15 ,rlm.alias Planning_Resource_Name ,ps.project_status_name ,flv.meaning ,per.email_address ,asgn.last_update_date FROM pa_project_assignments asgn ,pa_resources_denorm res ,pa_resources_denorm res2 ,pa_project_role_types prt ,pa_project_statuses ps ,pa_project_statuses ps1 ,pa_locations loc ,fnd_territories_tl ftt ,fnd_territories_tl ftt2 ,jtf_calendars_tl caltl ,pa_work_types_v wt ,pa_projects_all proj ,pa_lookups pl ,pa_lookups pl1 ,pa_lookups pl2 ,pa_lookups pl3 ,pa_project_subteams pst ,pa_project_subteam_parties psp ,per_jobs pj ,per_job_groups pjg ,per_organization_structures pos ,per_org_structure_versions posv ,pa_resource_list_members rlm ,per_people_f per ,fnd_lookup_values flv WHERE asgn.resource_id = res.resource_id(+) AND asgn.start_date BETWEEN res.resource_effective_start_date(+) AND res.resource_effective_end_date(+) AND res.schedulable_flag (+) = 'Y' AND asgn.resource_id = res2.resource_id(+) AND sysdate BETWEEN res2.resource_effective_start_date(+) AND res2.resource_effective_end_date(+) AND res2.schedulable_flag (+) = 'Y' AND asgn.project_role_id = prt.project_role_id AND asgn.status_code = ps.project_status_code (+) AND asgn.apprvl_status_code = ps1.project_status_code (+) AND asgn.location_id = loc.location_id (+) AND loc.country_code = ftt.TERRITORY_CODE (+) AND ftt.language(+) = userenv('LANG') AND asgn.calendar_id = caltl.calendar_id(+) AND caltl.language(+) = userenv('LANG') AND asgn.work_type_id = wt.work_type_id AND asgn.project_id = proj.project_id AND pl.lookup_type = 'MULTIPLE_STATUS_TEXT' AND pl.lookup_code = 'MULTIPLE' AND asgn.assignment_id = psp.object_id (+) AND psp.object_type(+) = 'PA_PROJECT_ASSIGNMENTS' AND psp.primary_subteam_flag(+) = 'Y' AND psp.project_subteam_id = pst.project_subteam_id(+) AND asgn.fcst_job_id = pj.job_id (+) AND asgn.fcst_job_group_id = pjg.job_group_id (+) AND pl1.lookup_type(+) = 'EXPENSE_OWNER_TYPE' AND asgn.expense_owner = pl1.lookup_code (+) AND pl2.lookup_type(+) = 'TP_AMOUNT_TYPE' AND asgn.fcst_tp_amount_type = pl2.lookup_code(+) AND pl3.lookup_type(+) = 'STAFFING_PRIORITY_CODE' AND asgn.staffing_priority_code = pl3.lookup_code(+) AND asgn.search_exp_org_struct_ver_id = posv.org_structure_version_id(+) AND posv.organization_structure_id = pos.organization_structure_id(+) AND asgn.search_country_code = ftt2.TERRITORY_CODE (+) AND ftt2.language(+) = userenv('LANG') AND asgn.resource_list_member_id = rlm.resource_list_member_id (+) AND res.person_id = per.person_id(+) AND sysdate between per.effective_start_date(+) and per.effective_end_date(+) AND flv.view_application_id=3 AND flv.lookup_type = 'PERSON_TYPE' AND flv.language = USERENV('LANG') AND flv.lookup_code=decode(per.current_employee_flag,'Y','EMP','CWK')

View Text - HTML Formatted

SELECT ASGN.ROWID
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.MULTIPLE_STATUS_FLAG
, ASGN.RECORD_VERSION_NUMBER
, ASGN.APPRVL_STATUS_CODE
, PS1.PROJECT_STATUS_NAME APPRVL_STATUS_NAME
, ASGN.STATUS_CODE STATUS_CODE
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL.MEANING
, PS.PROJECT_STATUS_NAME) STATUS_NAME
, ASGN.PROJECT_ID
, PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, ASGN.PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.RESOURCE_ID
, DECODE(RES.RESOURCE_NAME
, NULL
, DECODE(ASGN.ASSIGNMENT_TYPE
, 'OPEN_ASSIGNMENT'
, RES.RESOURCE_NAME
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(ASGN.RESOURCE_ID))
, RES.RESOURCE_NAME) RESOURCE_NAME
, RES.RESOURCE_ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(RES.RESOURCE_ORGANIZATION_ID) RESOURCE_ORGANIZATION
, RES2.RESOURCE_ORGANIZATION_ID RESOURCE_CUR_ORGANIZATION_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(RES2.RESOURCE_ORGANIZATION_ID) RESOURCE_CUR_ORGANIZATION
, RES2.MANAGER_ID MANAGER_ID
, RES2.MANAGER_NAME MANAGER_NAME
, ASGN.PROJECT_PARTY_ID
, PSP.PROJECT_SUBTEAM_ID
, PST.NAME PROJECT_SUB_TEAM_NAME
, PSP.PROJECT_SUBTEAM_PARTY_ID
, ASGN.START_DATE
, ASGN.END_DATE
, PROJ.NAME
, PROJ.SEGMENT1
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, ASGN.ASSIGNMENT_EFFORT
, ASGN.EXTENSION_POSSIBLE
, ASGN.MIN_RESOURCE_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL
, ASGN.ASSIGNMENT_NUMBER
, ASGN.ADDITIONAL_INFORMATION
, ASGN.LOCATION_ID
, LOC.CITY
, LOC.REGION
, LOC.COUNTRY_CODE
, FTT.TERRITORY_SHORT_NAME
, ASGN.DESCRIPTION
, ASGN.NOTE_TO_APPROVER
, ASGN.CALENDAR_ID
, CALTL.CALENDAR_NAME
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.WORK_TYPE_ID
, WT.NAME
, ASGN.EXPENSE_OWNER
, PL1.MEANING
, ASGN.EXPENSE_LIMIT
, ASGN.EXPENSE_LIMIT_CURRENCY_CODE
, ASGN.FCST_JOB_ID
, PJ.NAME
, ASGN.FCST_JOB_GROUP_ID
, PJG.DISPLAYED_NAME
, ASGN.EXPENDITURE_ORG_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORG_ID)
, ASGN.EXPENDITURE_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORGANIZATION_ID)
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.EXPENDITURE_TYPE
, ASGN.FCST_TP_AMOUNT_TYPE
, PL2.MEANING
, ASGN.STAFFING_PRIORITY_CODE
, PL3.MEANING
, ASGN.STAFFING_OWNER_PERSON_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(ASGN.STAFFING_OWNER_PERSON_ID)
, PROJ.PROJECT_TYPE
, ASGN.REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE
, ASGN.MARKUP_PERCENT
, PS.PROJECT_SYSTEM_STATUS_CODE
, NVL(ASGN.NO_OF_ACTIVE_CANDIDATES
, 0)
, ASGN.SOURCE_ASSIGNMENT_ID
, ASGN.ASSIGNMENT_TEMPLATE_ID
, ASGN.MASS_WF_IN_PROGRESS_FLAG
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, ASGN.SEARCH_MIN_AVAILABILITY
, FTT2.TERRITORY_SHORT_NAME
, ASGN.SEARCH_COUNTRY_CODE
, POS.NAME
, ASGN.SEARCH_EXP_ORG_STRUCT_VER_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.SEARCH_EXP_START_ORG_ID)
, ASGN.SEARCH_EXP_START_ORG_ID
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.ENABLE_AUTO_CAND_NOM_FLAG
, ASGN.LAST_AUTO_SEARCH_DATE
, PROJ.ENABLE_AUTOMATED_SEARCH
, ASGN.PENDING_APPROVAL_FLAG
, ASGN.BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT_OVERRIDE
, ASGN.DISCOUNT_PERCENTAGE
, ASGN.RATE_DISC_REASON_CODE
, ASGN.TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE
, PROJ.MULTI_CURRENCY_BILLING_FLAG
, ASGN.CREATION_DATE
, PA_GENERATE_FORECAST_PUB.GET_FORECAST_GEN_DATE(ASGN.PROJECT_ID)
, ASGN.TRANSFER_PRICE_RATE
, ASGN.TRANSFER_PR_RATE_CURR
, ASGN.RESOURCE_LIST_MEMBER_ID
, PA_PLANNING_RESOURCE_UTILS.GET_PLAN_RES_COMBINATION(ASGN.RESOURCE_LIST_MEMBER_ID)
, ASGN.ATTRIBUTE_CATEGORY
, ASGN.ATTRIBUTE1
, ASGN.ATTRIBUTE2
, ASGN.ATTRIBUTE3
, ASGN.ATTRIBUTE4
, ASGN.ATTRIBUTE5
, ASGN.ATTRIBUTE6
, ASGN.ATTRIBUTE7
, ASGN.ATTRIBUTE8
, ASGN.ATTRIBUTE9
, ASGN.ATTRIBUTE10
, ASGN.ATTRIBUTE11
, ASGN.ATTRIBUTE12
, ASGN.ATTRIBUTE13
, ASGN.ATTRIBUTE14
, ASGN.ATTRIBUTE15
, RLM.ALIAS PLANNING_RESOURCE_NAME
, PS.PROJECT_STATUS_NAME
, FLV.MEANING
, PER.EMAIL_ADDRESS
, ASGN.LAST_UPDATE_DATE
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_RESOURCES_DENORM RES
, PA_RESOURCES_DENORM RES2
, PA_PROJECT_ROLE_TYPES PRT
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS1
, PA_LOCATIONS LOC
, FND_TERRITORIES_TL FTT
, FND_TERRITORIES_TL FTT2
, JTF_CALENDARS_TL CALTL
, PA_WORK_TYPES_V WT
, PA_PROJECTS_ALL PROJ
, PA_LOOKUPS PL
, PA_LOOKUPS PL1
, PA_LOOKUPS PL2
, PA_LOOKUPS PL3
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PER_JOBS PJ
, PER_JOB_GROUPS PJG
, PER_ORGANIZATION_STRUCTURES POS
, PER_ORG_STRUCTURE_VERSIONS POSV
, PA_RESOURCE_LIST_MEMBERS RLM
, PER_PEOPLE_F PER
, FND_LOOKUP_VALUES FLV
WHERE ASGN.RESOURCE_ID = RES.RESOURCE_ID(+)
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES.RESOURCE_EFFECTIVE_END_DATE(+)
AND RES.SCHEDULABLE_FLAG (+) = 'Y'
AND ASGN.RESOURCE_ID = RES2.RESOURCE_ID(+)
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES2.RESOURCE_EFFECTIVE_END_DATE(+)
AND RES2.SCHEDULABLE_FLAG (+) = 'Y'
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.APPRVL_STATUS_CODE = PS1.PROJECT_STATUS_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND ASGN.CALENDAR_ID = CALTL.CALENDAR_ID(+)
AND CALTL.LANGUAGE(+) = USERENV('LANG')
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND PL.LOOKUP_TYPE = 'MULTIPLE_STATUS_TEXT'
AND PL.LOOKUP_CODE = 'MULTIPLE'
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND PL1.LOOKUP_TYPE(+) = 'EXPENSE_OWNER_TYPE'
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND PL2.LOOKUP_TYPE(+) = 'TP_AMOUNT_TYPE'
AND ASGN.FCST_TP_AMOUNT_TYPE = PL2.LOOKUP_CODE(+)
AND PL3.LOOKUP_TYPE(+) = 'STAFFING_PRIORITY_CODE'
AND ASGN.STAFFING_PRIORITY_CODE = PL3.LOOKUP_CODE(+)
AND ASGN.SEARCH_EXP_ORG_STRUCT_VER_ID = POSV.ORG_STRUCTURE_VERSION_ID(+)
AND POSV.ORGANIZATION_STRUCTURE_ID = POS.ORGANIZATION_STRUCTURE_ID(+)
AND ASGN.SEARCH_COUNTRY_CODE = FTT2.TERRITORY_CODE (+)
AND FTT2.LANGUAGE(+) = USERENV('LANG')
AND ASGN.RESOURCE_LIST_MEMBER_ID = RLM.RESOURCE_LIST_MEMBER_ID (+)
AND RES.PERSON_ID = PER.PERSON_ID(+)
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE(+)
AND PER.EFFECTIVE_END_DATE(+)
AND FLV.VIEW_APPLICATION_ID=3
AND FLV.LOOKUP_TYPE = 'PERSON_TYPE'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE=DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK')