VIEW: APPS.PA_TEMPLATE_REQUIREMENTS_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.status_code status_code ,ps.project_status_name status_name ,asgn.assignment_template_id ,tt.team_template_name ,asgn.project_role_id ,prt.meaning project_role_name ,psp.project_subteam_id ,pst.name project_sub_team_name ,psp.project_subteam_party_id ,asgn.start_date ,asgn.end_date ,(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 ,pl.meaning ,asgn.staffing_priority_code ,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 FROM pa_team_templates tt ,pa_project_assignments asgn ,pa_project_role_types prt ,pa_project_statuses ps ,pa_locations loc ,fnd_territories_tl ftt ,jtf_calendars_tl caltl ,pa_work_types_v wt ,pa_lookups pl ,pa_project_subteams pst ,pa_project_subteam_parties psp WHERE asgn.assignment_template_id = tt.team_template_id AND asgn.project_role_id = prt.project_role_id AND asgn.status_code = ps.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 pl.lookup_type(+) = 'STAFFING_PRIORITY_CODE' AND asgn.staffing_priority_code = pl.lookup_code(+) 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.template_flag='Y'

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.STATUS_CODE STATUS_CODE
, PS.PROJECT_STATUS_NAME STATUS_NAME
, ASGN.ASSIGNMENT_TEMPLATE_ID
, TT.TEAM_TEMPLATE_NAME
, ASGN.PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, PSP.PROJECT_SUBTEAM_ID
, PST.NAME PROJECT_SUB_TEAM_NAME
, PSP.PROJECT_SUBTEAM_PARTY_ID
, ASGN.START_DATE
, ASGN.END_DATE
, (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
, PL.MEANING
, ASGN.STAFFING_PRIORITY_CODE
, 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
FROM PA_TEAM_TEMPLATES TT
, PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECT_ROLE_TYPES PRT
, PA_PROJECT_STATUSES PS
, PA_LOCATIONS LOC
, FND_TERRITORIES_TL FTT
, JTF_CALENDARS_TL CALTL
, PA_WORK_TYPES_V WT
, PA_LOOKUPS PL
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
WHERE ASGN.ASSIGNMENT_TEMPLATE_ID = TT.TEAM_TEMPLATE_ID
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STATUS_CODE = PS.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 PL.LOOKUP_TYPE(+) = 'STAFFING_PRIORITY_CODE'
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
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.TEMPLATE_FLAG='Y'