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_PROJ_ASSIGNMENTS_AMG_V

Source

View Text - Preformatted


SELECT proj.project_id ,proj.name ,proj.segment1 ,proj.carrying_out_organization_id ,pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id) ,pa_resource_pvt.get_manager_id(proj.project_id) ,pa_resource_pvt.get_manager_name(proj.project_id) ,asgn.assignment_id ,asgn.assignment_name ,asgn.assignment_type ,asgn.assignment_number ,asgn.description ,asgn.start_date ,asgn.end_date ,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) ,res.resource_organization_id ,pa_expenditures_utils.GetOrgTlName(res.resource_organization_id) ,res.manager_id ,res.manager_name ,(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ) ,asgn.assignment_effort ,asgn.apprvl_status_code ,ps1.project_status_name apprvl_status_name ,asgn.note_to_approver ,asgn.resource_list_member_id ,rlm.alias ,pa_planning_resource_utils.get_plan_res_combination(asgn.resource_list_member_id) ,asgn.additional_information ,asgn.record_version_number ,nvl(ps.project_status_name, pl.meaning) ,asgn.status_code ,asgn.multiple_status_flag ,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.location_id ,loc.city ,loc.region ,loc.country_code ,ftt.territory_short_name ,asgn.staffing_priority_code ,pl3.meaning ,asgn.calendar_id ,caltl.calendar_name ,asgn.calendar_type ,asgn.resource_calendar_percent ,asgn.project_role_id ,prt.meaning project_role_name ,asgn.work_type_id ,wt.name ,psp.project_subteam_id ,pst.name ,asgn.extension_possible ,asgn.expense_owner ,asgn.expense_limit ,asgn.expenditure_type_class ,asgn.expenditure_type ,asgn.rate_disc_reason_code ,asgn.discount_percentage ,asgn.bill_rate_override ,asgn.bill_rate_curr_override ,asgn.markup_percent_override ,asgn.tp_rate_override ,asgn.tp_currency_override ,asgn.tp_calc_base_code_override ,asgn.tp_percent_applied_override ,asgn.staffing_owner_person_id ,PA_RESOURCE_UTILS.get_person_name_no_date(asgn.staffing_owner_person_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 ,asgn.orig_system_code ,asgn.orig_system_reference ,asgn.creation_date ,asgn.last_update_date FROM pa_project_assignments asgn ,pa_resources_denorm res ,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 WHERE asgn.assignment_type in ('STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT') AND asgn.resource_id = res.resource_id AND sysdate BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date 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 (+)

View Text - HTML Formatted

SELECT PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.CARRYING_OUT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID)
, PA_RESOURCE_PVT.GET_MANAGER_ID(PROJ.PROJECT_ID)
, PA_RESOURCE_PVT.GET_MANAGER_NAME(PROJ.PROJECT_ID)
, ASGN.ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_TYPE
, ASGN.ASSIGNMENT_NUMBER
, ASGN.DESCRIPTION
, ASGN.START_DATE
, ASGN.END_DATE
, 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)
, RES.RESOURCE_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES.RESOURCE_ORGANIZATION_ID)
, RES.MANAGER_ID
, RES.MANAGER_NAME
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 )
, ASGN.ASSIGNMENT_EFFORT
, ASGN.APPRVL_STATUS_CODE
, PS1.PROJECT_STATUS_NAME APPRVL_STATUS_NAME
, ASGN.NOTE_TO_APPROVER
, ASGN.RESOURCE_LIST_MEMBER_ID
, RLM.ALIAS
, PA_PLANNING_RESOURCE_UTILS.GET_PLAN_RES_COMBINATION(ASGN.RESOURCE_LIST_MEMBER_ID)
, ASGN.ADDITIONAL_INFORMATION
, ASGN.RECORD_VERSION_NUMBER
, NVL(PS.PROJECT_STATUS_NAME
, PL.MEANING)
, ASGN.STATUS_CODE
, ASGN.MULTIPLE_STATUS_FLAG
, 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.LOCATION_ID
, LOC.CITY
, LOC.REGION
, LOC.COUNTRY_CODE
, FTT.TERRITORY_SHORT_NAME
, ASGN.STAFFING_PRIORITY_CODE
, PL3.MEANING
, ASGN.CALENDAR_ID
, CALTL.CALENDAR_NAME
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.WORK_TYPE_ID
, WT.NAME
, PSP.PROJECT_SUBTEAM_ID
, PST.NAME
, ASGN.EXTENSION_POSSIBLE
, ASGN.EXPENSE_OWNER
, ASGN.EXPENSE_LIMIT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.EXPENDITURE_TYPE
, ASGN.RATE_DISC_REASON_CODE
, ASGN.DISCOUNT_PERCENTAGE
, ASGN.BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE
, ASGN.STAFFING_OWNER_PERSON_ID
, PA_RESOURCE_UTILS.GET_PERSON_NAME_NO_DATE(ASGN.STAFFING_OWNER_PERSON_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
, ASGN.ORIG_SYSTEM_CODE
, ASGN.ORIG_SYSTEM_REFERENCE
, ASGN.CREATION_DATE
, ASGN.LAST_UPDATE_DATE
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_RESOURCES_DENORM RES
, 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
WHERE ASGN.ASSIGNMENT_TYPE IN ('STAFFED_ASSIGNMENT'
, 'STAFFED_ADMIN_ASSIGNMENT')
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND SYSDATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
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 (+)