VIEW: APPS.PA_REP_ASMT_DTS_V

Source

View Text - Preformatted

SELECT asmt_dts.project_organization_id ,asmt_dts.project_organization_name ,asmt_dts.project_id ,asmt_dts.project_name ,asmt_dts.project_number ,asmt_dts.project_name_number ,asmt_dts.assignment_id ,asmt_dts.assignment_name ,asmt_dts.assignment_number ,asmt_dts.project_role_id ,asmt_dts.project_role_name ,asmt_dts.resource_id ,asmt_dts.resource_name ,asmt_dts.resource_job_level ,asmt_dts.resource_capacity_hours ,asmt_dts.resource_capacity_hours * imp.fte_day resource_capacity_days ,asmt_dts.resource_capacity_hours * imp.fte_week resource_capacity_weeks ,asmt_dts.resource_job_group ,asmt_dts.resource_job ,asmt_dts.resource_organization ,asmt_dts.resource_cur_organization ,asmt_dts.asmt_start_date ,asmt_dts.asmt_end_date ,asmt_dts.provisional_hours ,asmt_dts.provisional_hours * imp.fte_day provisional_days ,asmt_dts.provisional_hours * imp.fte_week provisional_weeks ,asmt_dts.confirmed_hours ,asmt_dts.confirmed_hours * imp.fte_day confirmed_days ,asmt_dts.confirmed_hours * imp.fte_week confirmed_weeks ,asmt_dts.effort_hours ,asmt_dts.effort_hours * imp.fte_day effort_days ,asmt_dts.effort_hours * imp.fte_week effort_weeks ,asmt_dts.bill_rate_currency ,asmt_dts.revenue_bill_rate ,asmt_dts.revenue_currency_code ,asmt_dts.work_type_name ,asmt_dts.staffing_priority ,asmt_dts.project_subteam_name ,asmt_dts.country ,asmt_dts.region ,asmt_dts.city ,asmt_dts.location ,asmt_dts.extention_possible ,asmt_dts.expense_owner_name ,asmt_dts.expense_limit ,asmt_dts.assignment_duration ,asmt_dts.approval_status ,asmt_dts.schedule_status ,asmt_dts.expenditure_type ,asmt_dts.bill_rate_override_curr ,asmt_dts.bill_rate_override ,asmt_dts.bill_rate_curr_override ,asmt_dts.markup_percent ,asmt_dts.markup_percent_override ,asmt_dts.tp_rate_override_curr ,asmt_dts.tp_rate_override ,asmt_dts.tp_currency_override ,asmt_dts.tp_basis_override ,asmt_dts.tp_percent_override ,asmt_dts.description ,asmt_dts.additional_information ,asmt_dts.calendar_type ,asmt_dts.resource_calendar_percent ,asmt_dts.expenditure_type_class ,asmt_dts.note_to_approver ,asmt_dts.search_min_availability ,asmt_dts.search_country_code ,asmt_dts.search_min_candidate_score ,asmt_dts.last_auto_search_date ,asmt_dts.competence_match_weighting ,asmt_dts.availability_match_weighting ,asmt_dts.job_level_match_weighting ,asmt_dts.attribute_category ,asmt_dts.attribute1 ,asmt_dts.attribute2 ,asmt_dts.attribute3 ,asmt_dts.attribute4 ,asmt_dts.attribute5 ,asmt_dts.attribute6 ,asmt_dts.attribute7 ,asmt_dts.attribute8 ,asmt_dts.attribute9 ,asmt_dts.attribute10 ,asmt_dts.attribute11 ,asmt_dts.attribute12 ,asmt_dts.attribute13 ,asmt_dts.attribute14 ,asmt_dts.attribute15 FROM (SELECT proj.carrying_out_organization_id project_organization_id ,pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id) project_organization_name ,proj.project_id ,proj.name project_name ,proj.segment1 project_number ,proj.name || '(' || proj.segment1 || ')' project_name_number ,asgn.assignment_id assignment_id ,asgn.assignment_name assignment_name ,asgn.assignment_number assignment_number ,asgn.project_role_id project_role_id ,prt.meaning project_role_name ,asgn.resource_id ,decode(res.resource_name, null, pa_resource_utils.get_resource_name(asgn.resource_id), res.resource_name) resource_name ,res.resource_job_level resource_job_level ,pa_assignment_utils.Get_Assignment_Measures(asgn.assignment_id, asgn.resource_id, asgn.assignment_effort, asgn.start_date, asgn.end_date, asgn.multiple_status_flag) resource_capacity_hours ,pjg.displayed_name resource_job_group ,pj.name resource_job ,pa_expenditures_utils.GetOrgTlName(res.resource_organization_id) resource_organization ,pa_expenditures_utils.GetOrgTlName(res2.resource_organization_id) resource_cur_organization ,asgn.start_date asmt_start_date ,asgn.end_date asmt_end_date ,pa_assignment_utils.Get_Asgn_Provisional_Hours() provisional_hours ,pa_assignment_utils.Get_Asgn_Confirmed_Hours() confirmed_hours ,asgn.assignment_effort effort_hours ,asgn.revenue_bill_rate || ' ' || asgn.revenue_currency_code bill_rate_currency ,asgn.revenue_bill_rate revenue_bill_rate ,asgn.revenue_currency_code revenue_currency_code ,wt.name work_type_name ,pl.meaning staffing_priority ,pst.name project_subteam_name ,ftt.territory_short_name country ,loc.region region ,loc.city city ,decode(loc.location_id, null, null, ftt.territory_short_name || '.' || loc.region || '.' || loc.city) location ,asgn.extension_possible extention_possible ,pl1.meaning expense_owner_name ,asgn.expense_limit expense_limit ,(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ) assignment_duration ,ps1.project_status_name approval_status ,decode (asgn.multiple_status_flag,'Y', pl2.meaning, ps.project_status_name) schedule_status ,asgn.expenditure_type ,asgn.bill_rate_override || ' ' || asgn.bill_rate_curr_override bill_rate_override_curr ,asgn.bill_rate_override bill_rate_override ,asgn.bill_rate_curr_override bill_rate_curr_override ,asgn.markup_percent markup_percent ,asgn.markup_percent_override markup_percent_override ,asgn.tp_rate_override || ' ' || asgn.tp_currency_override tp_rate_override_curr ,asgn.tp_rate_override tp_rate_override ,asgn.tp_currency_override tp_currency_override ,asgn.tp_calc_base_code_override tp_basis_override ,asgn.tp_percent_applied_override tp_percent_override ,asgn.expenditure_org_id expenditure_org_id ,asgn.description ,asgn.additional_information ,asgn.calendar_type ,asgn.resource_calendar_percent ,asgn.expenditure_type_class ,asgn.note_to_approver ,asgn.search_min_availability ,asgn.search_country_code ,asgn.search_min_candidate_score ,asgn.last_auto_search_date ,asgn.competence_match_weighting ,asgn.availability_match_weighting ,asgn.job_level_match_weighting ,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_project_assignments asgn ,pa_projects_all proj ,pa_resources_denorm res ,pa_resources_denorm res2 ,pa_project_role_types prt ,(select lookup_code, meaning from pa_lookups where lookup_type='STAFFING_PRIORITY_CODE') pl ,(select lookup_code, meaning from pa_lookups where lookup_type='EXPENSE_OWNER_TYPE') pl1 ,(select meaning from pa_lookups where lookup_type='MULTIPLE_STATUS_TEXT' and lookup_code='MULTIPLE') pl2 ,pa_locations loc ,pa_project_subteams pst ,pa_project_subteam_parties psp ,pa_project_statuses ps ,pa_project_statuses ps1 ,pa_work_types_v wt ,per_jobs pj ,per_job_groups pjg ,fnd_territories_tl ftt ,(SELECT instance_pk1_value FROM fnd_grants fg ,fnd_user fu ,fnd_objects fob ,(select nvl(pa_security_pvt.get_menu_id('PA_PRM_PROJ_AUTH'),-1) menu_id ,nvl(pa_security_pvt.get_grantee_key,-1) grantee_key from dual) prj_auth_menu WHERE fg.instance_type = 'INSTANCE' AND fg.grantee_type = 'USER' AND fg.object_id = fob.object_id AND fob.obj_name = 'ORGANIZATION' AND fg.menu_id = prj_auth_menu.menu_id AND fg.grantee_key = prj_auth_menu.grantee_key AND fu.user_id = fnd_global.user_id AND trunc(SYSDATE) BETWEEN trunc(fg.start_date) and trunc(NVL(fg.end_date, SYSDATE+1))) auth_org ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof WHERE prof.val = 'N' AND asgn.project_id = proj.project_id AND asgn.resource_id = res.resource_id AND asgn.start_date BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date AND asgn.resource_id = res.resource_id AND asgn.resource_id = res2.resource_id(+) AND sysdate BETWEEN res2.resource_effective_start_date(+) AND res2.resource_effective_end_date(+) AND asgn.project_role_id = prt.project_role_id AND asgn.staffing_priority_code = pl.lookup_code(+) AND asgn.expense_owner = pl1.lookup_code (+) AND asgn.location_id = loc.location_id (+) AND asgn.assignment_id = psp.object_id (+) AND asgn.status_code = ps.project_status_code (+) AND asgn.apprvl_status_code = ps1.project_status_code (+) AND asgn.work_type_id = wt.work_type_id AND res.job_id = pj.job_id (+) AND pj.job_group_id = pjg.job_group_id (+) AND loc.country_code = ftt.TERRITORY_CODE (+) AND ftt.language(+) = userenv('LANG') AND psp.object_type(+) = 'PA_PROJECT_ASSIGNMENTS' AND psp.primary_subteam_flag(+) = 'Y' AND psp.project_subteam_id = pst.project_subteam_id(+) AND proj.carrying_out_organization_id in auth_org.instance_pk1_value UNION SELECT proj.carrying_out_organization_id project_organization_id ,pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id) project_organization_name ,proj.project_id ,proj.name project_name ,proj.segment1 project_number ,proj.name || '(' || proj.segment1 || ')' project_name_number ,asgn.assignment_id assignment_id ,asgn.assignment_name assignment_name ,asgn.assignment_number assignment_number ,asgn.project_role_id project_role_id ,prt.meaning project_role_name ,asgn.resource_id ,decode(res.resource_name, null, pa_resource_utils.get_resource_name(asgn.resource_id), res.resource_name) resource_name ,res.resource_job_level resource_job_level ,pa_assignment_utils.Get_Assignment_Measures(asgn.assignment_id, asgn.resource_id, asgn.assignment_effort, asgn.start_date, asgn.end_date, asgn.multiple_status_flag) resource_capacity_hours ,pjg.displayed_name resource_job_group ,pj.name resource_job ,pa_expenditures_utils.GetOrgTlName(res.resource_organization_id) resource_organization ,pa_expenditures_utils.GetOrgTlName(res2.resource_organization_id) resource_cur_organization ,asgn.start_date asmt_start_date ,asgn.end_date asmt_end_date ,pa_assignment_utils.Get_Asgn_Provisional_Hours() provisional_hours ,pa_assignment_utils.Get_Asgn_Confirmed_Hours() confirmed_hours ,asgn.assignment_effort effort_hours ,asgn.revenue_bill_rate || ' ' || asgn.revenue_currency_code bill_rate_currency ,asgn.revenue_bill_rate revenue_bill_rate ,asgn.revenue_currency_code revenue_currency_code ,wt.name work_type_name ,pl.meaning staffing_priority ,pst.name project_subteam_name ,ftt.territory_short_name country ,loc.region region ,loc.city city ,decode(loc.location_id, null, null, ftt.territory_short_name || '.' || loc.region || '.' || loc.city) location ,asgn.extension_possible extention_possible ,pl1.meaning expense_owner_name ,asgn.expense_limit expense_limit ,(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ) assignment_duration ,ps1.project_status_name approval_status ,decode (asgn.multiple_status_flag,'Y', pl2.meaning, ps.project_status_name) schedule_status ,asgn.expenditure_type ,asgn.bill_rate_override || ' ' || asgn.bill_rate_curr_override bill_rate_override_curr ,asgn.bill_rate_override bill_rate_override ,asgn.bill_rate_curr_override bill_rate_curr_override ,asgn.markup_percent markup_percent ,asgn.markup_percent_override markup_percent_override ,asgn.tp_rate_override || ' ' || asgn.tp_currency_override tp_rate_override_curr ,asgn.tp_rate_override tp_rate_override ,asgn.tp_currency_override tp_currency_override ,asgn.tp_calc_base_code_override tp_basis_override ,asgn.tp_percent_applied_override tp_percent_override ,asgn.expenditure_org_id expenditure_org_id ,asgn.description ,asgn.additional_information ,asgn.calendar_type ,asgn.resource_calendar_percent ,asgn.expenditure_type_class ,asgn.note_to_approver ,asgn.search_min_availability ,asgn.search_country_code ,asgn.search_min_candidate_score ,asgn.last_auto_search_date ,asgn.competence_match_weighting ,asgn.availability_match_weighting ,asgn.job_level_match_weighting ,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_project_assignments asgn ,pa_projects_all proj ,pa_resources_denorm res ,pa_resources_denorm res2 ,pa_project_role_types prt ,(select lookup_code, meaning from pa_lookups where lookup_type='STAFFING_PRIORITY_CODE') pl ,(select lookup_code, meaning from pa_lookups where lookup_type='EXPENSE_OWNER_TYPE') pl1 ,(select meaning from pa_lookups where lookup_type='MULTIPLE_STATUS_TEXT' and lookup_code='MULTIPLE') pl2 ,pa_locations loc ,pa_project_subteams pst ,pa_project_subteam_parties psp ,pa_project_statuses ps ,pa_project_statuses ps1 ,pa_work_types_v wt ,per_jobs pj ,per_job_groups pjg ,fnd_territories_tl ftt ,pa_project_parties ppp ,fnd_user fu ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof WHERE prof.val = 'N' AND asgn.project_id = proj.project_id AND asgn.resource_id = res.resource_id AND asgn.start_date BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date AND asgn.resource_id = res.resource_id AND asgn.resource_id = res2.resource_id(+) AND sysdate BETWEEN res2.resource_effective_start_date(+) AND res2.resource_effective_end_date(+) AND asgn.project_role_id = prt.project_role_id AND asgn.staffing_priority_code = pl.lookup_code(+) AND asgn.expense_owner = pl1.lookup_code(+) AND asgn.location_id = loc.location_id (+) AND asgn.assignment_id = psp.object_id (+) AND asgn.status_code = ps.project_status_code (+) AND asgn.apprvl_status_code = ps1.project_status_code (+) AND asgn.work_type_id = wt.work_type_id AND res.job_id = pj.job_id (+) AND pj.job_group_id = pjg.job_group_id (+) AND loc.country_code = ftt.TERRITORY_CODE (+) AND ftt.language(+) = userenv('LANG') AND psp.object_type(+) = 'PA_PROJECT_ASSIGNMENTS' AND psp.primary_subteam_flag(+) = 'Y' AND psp.project_subteam_id = pst.project_subteam_id(+) AND proj.project_id = ppp.project_id AND ppp.project_role_id = 1 AND ppp.resource_source_id = fu.employee_id AND fu.user_id = fnd_global.user_id UNION SELECT proj.carrying_out_organization_id project_organization_id ,pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id) project_organization_name ,proj.project_id ,proj.name project_name ,proj.segment1 project_number ,proj.name || '(' || proj.segment1 || ')' project_name_number ,asgn.assignment_id assignment_id ,asgn.assignment_name assignment_name ,asgn.assignment_number assignment_number ,asgn.project_role_id project_role_id ,prt.meaning project_role_name ,asgn.resource_id ,decode(res.resource_name, null, pa_resource_utils.get_resource_name(asgn.resource_id), res.resource_name) resource_name ,res.resource_job_level resource_job_level ,pa_assignment_utils.Get_Assignment_Measures(asgn.assignment_id, asgn.resource_id, asgn.assignment_effort, asgn.start_date, asgn.end_date, asgn.multiple_status_flag) resource_capacity_hours ,pjg.displayed_name resource_job_group ,pj.name resource_job ,pa_expenditures_utils.GetOrgTlName(res.resource_organization_id) resource_organization ,pa_expenditures_utils.GetOrgTlName(res2.resource_organization_id) resource_cur_organization ,asgn.start_date asmt_start_date ,asgn.end_date asmt_end_date ,pa_assignment_utils.Get_Asgn_Provisional_Hours() provisional_hours ,pa_assignment_utils.Get_Asgn_Confirmed_Hours() confirmed_hours ,asgn.assignment_effort effort_hours ,asgn.revenue_bill_rate || ' ' || asgn.revenue_currency_code bill_rate_currency ,asgn.revenue_bill_rate revenue_bill_rate ,asgn.revenue_currency_code revenue_currency_code ,wt.name work_type_name ,pl.meaning staffing_priority ,pst.name project_subteam_name ,ftt.territory_short_name country ,loc.region region ,loc.city city ,decode(loc.location_id, null, null, ftt.territory_short_name || '.' || loc.region || '.' || loc.city) location ,asgn.extension_possible extention_possible ,pl1.meaning expense_owner_name ,asgn.expense_limit expense_limit ,(trunc(asgn.end_date) - trunc(asgn.start_date) +1 ) assignment_duration ,ps1.project_status_name approval_status ,decode (asgn.multiple_status_flag,'Y', pl2.meaning, ps.project_status_name) schedule_status ,asgn.expenditure_type ,asgn.bill_rate_override || ' ' || asgn.bill_rate_curr_override bill_rate_override_curr ,asgn.bill_rate_override bill_rate_override ,asgn.bill_rate_curr_override bill_rate_curr_override ,asgn.markup_percent markup_percent ,asgn.markup_percent_override markup_percent_override ,asgn.tp_rate_override || ' ' || asgn.tp_currency_override tp_rate_override_curr ,asgn.tp_rate_override tp_rate_override ,asgn.tp_currency_override tp_currency_override ,asgn.tp_calc_base_code_override tp_basis_override ,asgn.tp_percent_applied_override tp_percent_override ,asgn.expenditure_org_id expenditure_org_id ,asgn.description ,asgn.additional_information ,asgn.calendar_type ,asgn.resource_calendar_percent ,asgn.expenditure_type_class ,asgn.note_to_approver ,asgn.search_min_availability ,asgn.search_country_code ,asgn.search_min_candidate_score ,asgn.last_auto_search_date ,asgn.competence_match_weighting ,asgn.availability_match_weighting ,asgn.job_level_match_weighting ,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_project_assignments asgn ,pa_projects_all proj ,pa_resources_denorm res ,pa_resources_denorm res2 ,pa_project_role_types prt ,(select lookup_code, meaning from pa_lookups where lookup_type='STAFFING_PRIORITY_CODE') pl ,(select lookup_code, meaning from pa_lookups where lookup_type='EXPENSE_OWNER_TYPE') pl1 ,(select meaning from pa_lookups where lookup_type='MULTIPLE_STATUS_TEXT' and lookup_code='MULTIPLE') pl2 ,pa_locations loc ,pa_project_subteams pst ,pa_project_subteam_parties psp ,pa_project_statuses ps ,pa_project_statuses ps1 ,pa_work_types_v wt ,per_jobs pj ,per_job_groups pjg ,fnd_territories_tl ftt ,(select NVL(fnd_profile.value('PA_SUPER_PROJECT_VIEW'), 'N') val from dual) prof WHERE prof.val = 'Y' AND asgn.project_id = proj.project_id AND asgn.resource_id = res.resource_id AND asgn.start_date BETWEEN res.resource_effective_start_date AND res.resource_effective_end_date AND asgn.resource_id = res.resource_id AND asgn.resource_id = res2.resource_id(+) AND sysdate BETWEEN res2.resource_effective_start_date(+) AND res2.resource_effective_end_date(+) AND asgn.project_role_id = prt.project_role_id AND asgn.staffing_priority_code = pl.lookup_code(+) AND asgn.expense_owner = pl1.lookup_code(+) AND asgn.location_id = loc.location_id (+) AND asgn.assignment_id = psp.object_id (+) AND asgn.status_code = ps.project_status_code (+) AND asgn.apprvl_status_code = ps1.project_status_code (+) AND asgn.work_type_id = wt.work_type_id AND res.job_id = pj.job_id (+) AND pj.job_group_id = pjg.job_group_id (+) AND loc.country_code = ftt.TERRITORY_CODE (+) AND ftt.language(+) = userenv('LANG') AND psp.object_type(+) = 'PA_PROJECT_ASSIGNMENTS' AND psp.primary_subteam_flag(+) = 'Y' AND psp.project_subteam_id = pst.project_subteam_id(+) ) asmt_dts ,pa_implementations_all imp WHERE asmt_dts.expenditure_org_id = imp.org_id (+)

View Text - HTML Formatted

SELECT ASMT_DTS.PROJECT_ORGANIZATION_ID
, ASMT_DTS.PROJECT_ORGANIZATION_NAME
, ASMT_DTS.PROJECT_ID
, ASMT_DTS.PROJECT_NAME
, ASMT_DTS.PROJECT_NUMBER
, ASMT_DTS.PROJECT_NAME_NUMBER
, ASMT_DTS.ASSIGNMENT_ID
, ASMT_DTS.ASSIGNMENT_NAME
, ASMT_DTS.ASSIGNMENT_NUMBER
, ASMT_DTS.PROJECT_ROLE_ID
, ASMT_DTS.PROJECT_ROLE_NAME
, ASMT_DTS.RESOURCE_ID
, ASMT_DTS.RESOURCE_NAME
, ASMT_DTS.RESOURCE_JOB_LEVEL
, ASMT_DTS.RESOURCE_CAPACITY_HOURS
, ASMT_DTS.RESOURCE_CAPACITY_HOURS * IMP.FTE_DAY RESOURCE_CAPACITY_DAYS
, ASMT_DTS.RESOURCE_CAPACITY_HOURS * IMP.FTE_WEEK RESOURCE_CAPACITY_WEEKS
, ASMT_DTS.RESOURCE_JOB_GROUP
, ASMT_DTS.RESOURCE_JOB
, ASMT_DTS.RESOURCE_ORGANIZATION
, ASMT_DTS.RESOURCE_CUR_ORGANIZATION
, ASMT_DTS.ASMT_START_DATE
, ASMT_DTS.ASMT_END_DATE
, ASMT_DTS.PROVISIONAL_HOURS
, ASMT_DTS.PROVISIONAL_HOURS * IMP.FTE_DAY PROVISIONAL_DAYS
, ASMT_DTS.PROVISIONAL_HOURS * IMP.FTE_WEEK PROVISIONAL_WEEKS
, ASMT_DTS.CONFIRMED_HOURS
, ASMT_DTS.CONFIRMED_HOURS * IMP.FTE_DAY CONFIRMED_DAYS
, ASMT_DTS.CONFIRMED_HOURS * IMP.FTE_WEEK CONFIRMED_WEEKS
, ASMT_DTS.EFFORT_HOURS
, ASMT_DTS.EFFORT_HOURS * IMP.FTE_DAY EFFORT_DAYS
, ASMT_DTS.EFFORT_HOURS * IMP.FTE_WEEK EFFORT_WEEKS
, ASMT_DTS.BILL_RATE_CURRENCY
, ASMT_DTS.REVENUE_BILL_RATE
, ASMT_DTS.REVENUE_CURRENCY_CODE
, ASMT_DTS.WORK_TYPE_NAME
, ASMT_DTS.STAFFING_PRIORITY
, ASMT_DTS.PROJECT_SUBTEAM_NAME
, ASMT_DTS.COUNTRY
, ASMT_DTS.REGION
, ASMT_DTS.CITY
, ASMT_DTS.LOCATION
, ASMT_DTS.EXTENTION_POSSIBLE
, ASMT_DTS.EXPENSE_OWNER_NAME
, ASMT_DTS.EXPENSE_LIMIT
, ASMT_DTS.ASSIGNMENT_DURATION
, ASMT_DTS.APPROVAL_STATUS
, ASMT_DTS.SCHEDULE_STATUS
, ASMT_DTS.EXPENDITURE_TYPE
, ASMT_DTS.BILL_RATE_OVERRIDE_CURR
, ASMT_DTS.BILL_RATE_OVERRIDE
, ASMT_DTS.BILL_RATE_CURR_OVERRIDE
, ASMT_DTS.MARKUP_PERCENT
, ASMT_DTS.MARKUP_PERCENT_OVERRIDE
, ASMT_DTS.TP_RATE_OVERRIDE_CURR
, ASMT_DTS.TP_RATE_OVERRIDE
, ASMT_DTS.TP_CURRENCY_OVERRIDE
, ASMT_DTS.TP_BASIS_OVERRIDE
, ASMT_DTS.TP_PERCENT_OVERRIDE
, ASMT_DTS.DESCRIPTION
, ASMT_DTS.ADDITIONAL_INFORMATION
, ASMT_DTS.CALENDAR_TYPE
, ASMT_DTS.RESOURCE_CALENDAR_PERCENT
, ASMT_DTS.EXPENDITURE_TYPE_CLASS
, ASMT_DTS.NOTE_TO_APPROVER
, ASMT_DTS.SEARCH_MIN_AVAILABILITY
, ASMT_DTS.SEARCH_COUNTRY_CODE
, ASMT_DTS.SEARCH_MIN_CANDIDATE_SCORE
, ASMT_DTS.LAST_AUTO_SEARCH_DATE
, ASMT_DTS.COMPETENCE_MATCH_WEIGHTING
, ASMT_DTS.AVAILABILITY_MATCH_WEIGHTING
, ASMT_DTS.JOB_LEVEL_MATCH_WEIGHTING
, ASMT_DTS.ATTRIBUTE_CATEGORY
, ASMT_DTS.ATTRIBUTE1
, ASMT_DTS.ATTRIBUTE2
, ASMT_DTS.ATTRIBUTE3
, ASMT_DTS.ATTRIBUTE4
, ASMT_DTS.ATTRIBUTE5
, ASMT_DTS.ATTRIBUTE6
, ASMT_DTS.ATTRIBUTE7
, ASMT_DTS.ATTRIBUTE8
, ASMT_DTS.ATTRIBUTE9
, ASMT_DTS.ATTRIBUTE10
, ASMT_DTS.ATTRIBUTE11
, ASMT_DTS.ATTRIBUTE12
, ASMT_DTS.ATTRIBUTE13
, ASMT_DTS.ATTRIBUTE14
, ASMT_DTS.ATTRIBUTE15
FROM (SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.RESOURCE_ID
, DECODE(RES.RESOURCE_NAME
, NULL
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(ASGN.RESOURCE_ID)
, RES.RESOURCE_NAME) RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL RESOURCE_JOB_LEVEL
, PA_ASSIGNMENT_UTILS.GET_ASSIGNMENT_MEASURES(ASGN.ASSIGNMENT_ID
, ASGN.RESOURCE_ID
, ASGN.ASSIGNMENT_EFFORT
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.MULTIPLE_STATUS_FLAG) RESOURCE_CAPACITY_HOURS
, PJG.DISPLAYED_NAME RESOURCE_JOB_GROUP
, PJ.NAME RESOURCE_JOB
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES.RESOURCE_ORGANIZATION_ID) RESOURCE_ORGANIZATION
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES2.RESOURCE_ORGANIZATION_ID) RESOURCE_CUR_ORGANIZATION
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, PA_ASSIGNMENT_UTILS.GET_ASGN_PROVISIONAL_HOURS() PROVISIONAL_HOURS
, PA_ASSIGNMENT_UTILS.GET_ASGN_CONFIRMED_HOURS() CONFIRMED_HOURS
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, WT.NAME WORK_TYPE_NAME
, PL.MEANING STAFFING_PRIORITY
, PST.NAME PROJECT_SUBTEAM_NAME
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, PS1.PROJECT_STATUS_NAME APPROVAL_STATUS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) SCHEDULE_STATUS
, ASGN.EXPENDITURE_TYPE
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.EXPENDITURE_ORG_ID EXPENDITURE_ORG_ID
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, 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_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_RESOURCES_DENORM RES
, PA_RESOURCES_DENORM RES2
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS1
, PA_WORK_TYPES_V WT
, PER_JOBS PJ
, PER_JOB_GROUPS PJG
, FND_TERRITORIES_TL FTT
, (SELECT INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH')
, -1) MENU_ID
, NVL(PA_SECURITY_PVT.GET_GRANTEE_KEY
, -1) GRANTEE_KEY
FROM DUAL) PRJ_AUTH_MENU
WHERE FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = PRJ_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = PRJ_AUTH_MENU.GRANTEE_KEY
AND FU.USER_ID = FND_GLOBAL.USER_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))) AUTH_ORG
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'N'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.RESOURCE_ID = RES2.RESOURCE_ID(+)
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES2.RESOURCE_EFFECTIVE_END_DATE(+)
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.APPRVL_STATUS_CODE = PS1.PROJECT_STATUS_CODE (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND RES.JOB_ID = PJ.JOB_ID (+)
AND PJ.JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND PROJ.CARRYING_OUT_ORGANIZATION_ID IN AUTH_ORG.INSTANCE_PK1_VALUE UNION SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.RESOURCE_ID
, DECODE(RES.RESOURCE_NAME
, NULL
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(ASGN.RESOURCE_ID)
, RES.RESOURCE_NAME) RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL RESOURCE_JOB_LEVEL
, PA_ASSIGNMENT_UTILS.GET_ASSIGNMENT_MEASURES(ASGN.ASSIGNMENT_ID
, ASGN.RESOURCE_ID
, ASGN.ASSIGNMENT_EFFORT
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.MULTIPLE_STATUS_FLAG) RESOURCE_CAPACITY_HOURS
, PJG.DISPLAYED_NAME RESOURCE_JOB_GROUP
, PJ.NAME RESOURCE_JOB
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES.RESOURCE_ORGANIZATION_ID) RESOURCE_ORGANIZATION
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES2.RESOURCE_ORGANIZATION_ID) RESOURCE_CUR_ORGANIZATION
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, PA_ASSIGNMENT_UTILS.GET_ASGN_PROVISIONAL_HOURS() PROVISIONAL_HOURS
, PA_ASSIGNMENT_UTILS.GET_ASGN_CONFIRMED_HOURS() CONFIRMED_HOURS
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, WT.NAME WORK_TYPE_NAME
, PL.MEANING STAFFING_PRIORITY
, PST.NAME PROJECT_SUBTEAM_NAME
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, PS1.PROJECT_STATUS_NAME APPROVAL_STATUS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) SCHEDULE_STATUS
, ASGN.EXPENDITURE_TYPE
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.EXPENDITURE_ORG_ID EXPENDITURE_ORG_ID
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, 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_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_RESOURCES_DENORM RES
, PA_RESOURCES_DENORM RES2
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS1
, PA_WORK_TYPES_V WT
, PER_JOBS PJ
, PER_JOB_GROUPS PJG
, FND_TERRITORIES_TL FTT
, PA_PROJECT_PARTIES PPP
, FND_USER FU
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'N'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.RESOURCE_ID = RES2.RESOURCE_ID(+)
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES2.RESOURCE_EFFECTIVE_END_DATE(+)
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE(+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.APPRVL_STATUS_CODE = PS1.PROJECT_STATUS_CODE (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND RES.JOB_ID = PJ.JOB_ID (+)
AND PJ.JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND PROJ.PROJECT_ID = PPP.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_SOURCE_ID = FU.EMPLOYEE_ID
AND FU.USER_ID = FND_GLOBAL.USER_ID UNION SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.RESOURCE_ID
, DECODE(RES.RESOURCE_NAME
, NULL
, PA_RESOURCE_UTILS.GET_RESOURCE_NAME(ASGN.RESOURCE_ID)
, RES.RESOURCE_NAME) RESOURCE_NAME
, RES.RESOURCE_JOB_LEVEL RESOURCE_JOB_LEVEL
, PA_ASSIGNMENT_UTILS.GET_ASSIGNMENT_MEASURES(ASGN.ASSIGNMENT_ID
, ASGN.RESOURCE_ID
, ASGN.ASSIGNMENT_EFFORT
, ASGN.START_DATE
, ASGN.END_DATE
, ASGN.MULTIPLE_STATUS_FLAG) RESOURCE_CAPACITY_HOURS
, PJG.DISPLAYED_NAME RESOURCE_JOB_GROUP
, PJ.NAME RESOURCE_JOB
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES.RESOURCE_ORGANIZATION_ID) RESOURCE_ORGANIZATION
, PA_EXPENDITURES_UTILS.GETORGTLNAME(RES2.RESOURCE_ORGANIZATION_ID) RESOURCE_CUR_ORGANIZATION
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, PA_ASSIGNMENT_UTILS.GET_ASGN_PROVISIONAL_HOURS() PROVISIONAL_HOURS
, PA_ASSIGNMENT_UTILS.GET_ASGN_CONFIRMED_HOURS() CONFIRMED_HOURS
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, WT.NAME WORK_TYPE_NAME
, PL.MEANING STAFFING_PRIORITY
, PST.NAME PROJECT_SUBTEAM_NAME
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, PS1.PROJECT_STATUS_NAME APPROVAL_STATUS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) SCHEDULE_STATUS
, ASGN.EXPENDITURE_TYPE
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.EXPENDITURE_ORG_ID EXPENDITURE_ORG_ID
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, 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_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_RESOURCES_DENORM RES
, PA_RESOURCES_DENORM RES2
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PA_PROJECT_STATUSES PS1
, PA_WORK_TYPES_V WT
, PER_JOBS PJ
, PER_JOB_GROUPS PJG
, FND_TERRITORIES_TL FTT
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'Y'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.START_DATE BETWEEN RES.RESOURCE_EFFECTIVE_START_DATE
AND RES.RESOURCE_EFFECTIVE_END_DATE
AND ASGN.RESOURCE_ID = RES.RESOURCE_ID
AND ASGN.RESOURCE_ID = RES2.RESOURCE_ID(+)
AND SYSDATE BETWEEN RES2.RESOURCE_EFFECTIVE_START_DATE(+)
AND RES2.RESOURCE_EFFECTIVE_END_DATE(+)
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE(+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.APPRVL_STATUS_CODE = PS1.PROJECT_STATUS_CODE (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND RES.JOB_ID = PJ.JOB_ID (+)
AND PJ.JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+) ) ASMT_DTS
, PA_IMPLEMENTATIONS_ALL IMP
WHERE ASMT_DTS.EXPENDITURE_ORG_ID = IMP.ORG_ID (+)