[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.work_type_id,ppa.calendar_id ,pl.location_id ,pl.country_code
FROM pa_projects_all ppa,pa_locations pl
WHERE project_id = l_assignment_rec.project_id
AND PPA.LOCATION_ID = PL.LOCATION_ID(+) ;
SELECT ppt.administrative_flag, ppa.calendar_id, ppa.project_currency_code, ppt.unassigned_time
FROM pa_projects_all ppa,pa_project_types_all ppt
WHERE segment1 = p_project_number
AND PPA.PROJECT_TYPE = PPT.PROJECT_TYPE
AND PPA.ORG_ID = PPT.ORG_ID;
SELECT system_linkage_function
FROM pa_expend_typ_sys_links_v
WHERE expenditure_type = l_assignment_rec.expenditure_type
AND system_linkage_function in ('ST', 'OT');
SELECT workflow_in_progress_flag
FROM pa_team_templates
WHERE team_template_id = l_assignment_rec.assignment_template_id;
SELECT person_id
FROM pa_resource_txn_attributes
WHERE resource_id = l_assignment_rec.resource_id;
PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.DELETE;
SELECT role_list_id INTO l_role_list_id
FROM pa_projects_all
WHERE project_id = l_assignment_rec.project_id;
SELECT role_list_id INTO l_role_list_id
FROM pa_team_templates
WHERE team_template_id = l_assignment_rec.assignment_template_id;
PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.DELETE;*/
PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.DELETE;
SELECT ROWID
FROM pa_project_assignments
WHERE assignment_id = p_assignment_rec.source_assignment_id
AND record_version_number = p_assignment_rec.record_version_number;
SELECT DISTINCT status_code
FROM pa_schedules /* Bug 5614557 Changed usage from pa_schedules_v to pa_schedules */
WHERE assignment_id = p_assignment_rec.source_assignment_id;
SELECT mass_wf_in_progress_flag
FROM pa_project_assignments
WHERE assignment_id = p_assignment_rec.source_assignment_id;
SELECT action_set_id, record_version_number
FROM pa_action_sets
WHERE object_id = p_assignment_rec.source_assignment_id
AND object_type = 'OPEN_ASSIGNMENT'
AND action_set_type_code = 'ADVERTISEMENT'
AND status_code <> 'DELETED';
SELECT pa.resource_list_member_id, rta.person_id
FROM pa_project_assignments pa,
pa_resource_txn_attributes rta
WHERE pa.assignment_id = c_assignment_id
AND pa.resource_id = rta.resource_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id)
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id, task_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id, ra.task_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id, ra.task_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id)
) linked_res_asgmts
WHERE NOT EXISTS
(
SELECT *
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = linked_res_asgmts.budget_version_id
AND ra.resource_list_member_id = p_new_resource_list_member_id
AND ra.task_id = linked_res_asgmts.task_id
AND ra.resource_assignment_id <> linked_res_asgmts.resource_assignment_id
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id, task_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id, ra.task_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id, ra.task_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.source_assignment_id)
) linked_res_asgmts
WHERE EXISTS
(
SELECT *
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = linked_res_asgmts.budget_version_id
AND ra.resource_list_member_id = p_new_resource_list_member_id
AND ra.task_id = linked_res_asgmts.task_id
AND ra.resource_assignment_id <> linked_res_asgmts.resource_assignment_id
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT rf.ROLE_ENABLED_FLAG
FROM pa_res_formats_b rf,
pa_resource_list_members rlm,
pa_project_assignments pa
WHERE pa.assignment_id = l_assignment_rec.source_assignment_id
AND pa.resource_list_member_id IS NOT NULL
AND rlm.resource_list_member_id = pa.resource_list_member_id
AND rlm.res_format_id = rf.res_format_id;
SELECT assignment_id
,assignment_name
,assignment_type
,status_code
,staffing_priority_code
,project_id
,project_role_id
,description
,start_date
,end_date
,assignment_effort
,extension_possible
,source_assignment_id
,min_resource_job_level
,max_resource_job_level
,additional_information
,location_id
,work_type_id
,revenue_currency_code
,revenue_bill_rate
,markup_percent
,expense_owner
,expense_limit
,expense_limit_currency_code
,fcst_tp_amount_type
,fcst_job_id
,fcst_job_group_id
,expenditure_org_id
,expenditure_organization_id
,expenditure_type_class
,expenditure_type
,calendar_type
,calendar_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,bill_rate_override
,bill_rate_curr_override
,markup_percent_override
,tp_rate_override
,tp_currency_override
,tp_calc_base_code_override
,tp_percent_applied_override
,staffing_owner_person_id
,resource_list_member_id
INTO
l_source_assignment_rec.assignment_id
,l_source_assignment_rec.assignment_name
,l_source_assignment_rec.assignment_type
,l_source_assignment_rec.status_code
,l_source_assignment_rec.staffing_priority_code
,l_source_assignment_rec.project_id
,l_source_assignment_rec.project_role_id
,l_source_assignment_rec.description
,l_source_assignment_rec.start_date
,l_source_assignment_rec.end_date
,l_source_assignment_rec.assignment_effort
,l_source_assignment_rec.extension_possible
,l_source_assignment_rec.source_assignment_id
,l_source_assignment_rec.min_resource_job_level
,l_source_assignment_rec.max_resource_job_level
,l_source_assignment_rec.additional_information
,l_source_assignment_rec.location_id
,l_source_assignment_rec.work_type_id
,l_source_assignment_rec.revenue_currency_code
,l_source_assignment_rec.revenue_bill_rate
,l_source_assignment_rec.markup_percent
,l_source_assignment_rec.expense_owner
,l_source_assignment_rec.expense_limit
,l_source_assignment_rec.expense_limit_currency_code
,l_source_assignment_rec.fcst_tp_amount_type
,l_source_assignment_rec.fcst_job_id
,l_source_assignment_rec.fcst_job_group_id
,l_source_assignment_rec.expenditure_org_id
,l_source_assignment_rec.expenditure_organization_id
,l_source_assignment_rec.expenditure_type_class
,l_source_assignment_rec.expenditure_type
,l_source_assignment_rec.calendar_type
,l_source_assignment_rec.calendar_id
,l_source_assignment_rec.attribute_category
,l_source_assignment_rec.attribute1
,l_source_assignment_rec.attribute2
,l_source_assignment_rec.attribute3
,l_source_assignment_rec.attribute4
,l_source_assignment_rec.attribute5
,l_source_assignment_rec.attribute6
,l_source_assignment_rec.attribute7
,l_source_assignment_rec.attribute8
,l_source_assignment_rec.attribute9
,l_source_assignment_rec.attribute10
,l_source_assignment_rec.attribute11
,l_source_assignment_rec.attribute12
,l_source_assignment_rec.attribute13
,l_source_assignment_rec.attribute14
,l_source_assignment_rec.attribute15
,l_source_assignment_rec.bill_rate_override
,l_source_assignment_rec.bill_rate_curr_override
,l_source_assignment_rec.markup_percent_override
,l_source_assignment_rec.tp_rate_override
,l_source_assignment_rec.tp_currency_override
,l_source_assignment_rec.tp_calc_base_code_override
,l_source_assignment_rec.tp_percent_applied_override
,l_source_assignment_rec.staffing_owner_person_id
,l_old_rlm_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_rec.source_assignment_id;
UPDATE pa_project_assignments
SET resource_list_member_id = l_old_rlm_id
WHERE assignment_id = x_new_assignment_id;
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => x_new_assignment_id
-- change resource list member
,p_resource_list_member_id => l_new_rlm_id
,p_named_role => l_assignment_rec.assignment_name
,p_project_role_id => l_assignment_rec.project_role_id
,x_return_status => l_return_status
);
,x_msg => 'Update_task_assignments(case 1), status='||l_return_status
,x_log_level => 5);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => -1
,x_return_status => l_return_status
);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => -1
,p_named_role => FND_API.G_MISS_CHAR
,x_return_status => l_return_status
);
,x_msg => 'Update_task_assignments(case 2), status='||l_return_status
,x_log_level => 5);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => x_new_assignment_id
,p_named_role => l_assignment_rec.assignment_name
,p_project_role_id => l_assignment_rec.project_role_id
,x_return_status => l_return_status
);
,x_msg => 'Update_task_assignments(case 3), status='||l_return_status
,x_log_level => 5);
PA_ACTION_SETS_PUB.Update_Action_Set(
p_action_set_id => l_action_set_id
,p_object_id => l_source_assignment_rec.assignment_id
,p_object_type => 'OPEN_ASSIGNMENT'
,p_action_set_type_code => 'ADVERTISEMENT'
,p_status_code => 'CLOSED'
,p_record_version_number => l_record_version_number
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_CANDIDATE_PUB.Update_Remaining_Candidates(p_assignment_id => p_assignment_rec.source_assignment_id
,p_resource_id => x_resource_id
,p_status_code => p_remaining_candidate_code
,p_change_reason_code => p_change_reason_code
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count);
PROCEDURE Execute_Update_Assignment
( p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_assignment_row_id IN ROWID := NULL
,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
,p_assignment_template_id IN pa_project_assignments.assignment_template_id%TYPE := FND_API.G_MISS_NUM
,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
,p_project_party_id IN pa_project_assignments.project_party_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := FND_API.G_MISS_NUM
,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
,p_assignment_effort IN pa_project_assignments.assignment_effort%TYPE := FND_API.G_MISS_NUM
,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
,p_source_assignment_id IN pa_project_assignments.source_assignment_id%TYPE := FND_API.G_MISS_NUM
,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
,p_revenue_currency_code IN pa_project_assignments.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_revenue_bill_rate IN pa_project_assignments.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
,p_markup_percent IN pa_project_assignments.markup_percent%TYPE := FND_API.G_MISS_NUM
,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR /* Bug 1851096 */
,p_resource_name IN pa_resources.name%TYPE := FND_API.G_MISS_CHAR
,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
,p_resource_id IN pa_resources.resource_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
,p_calendar_id IN jtf_calendars_tl.calendar_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
,p_bill_rate_override IN pa_project_assignments.bill_rate_override%TYPE := FND_API.G_MISS_NUM
,p_bill_rate_curr_override IN pa_project_assignments.bill_rate_curr_override%TYPE := FND_API.G_MISS_CHAR
,p_markup_percent_override IN pa_project_assignments.markup_percent_override%TYPE := FND_API.G_MISS_NUM
,p_discount_percentage IN pa_project_assignments.discount_percentage%TYPE := FND_API.G_MISS_NUM -- Bug 2590938
,p_rate_disc_reason_code IN pa_project_assignments.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR -- Bug 2590938
,p_tp_rate_override IN pa_project_assignments.tp_rate_override%TYPE := FND_API.G_MISS_NUM
,p_tp_currency_override IN pa_project_assignments.tp_currency_override%TYPE := FND_API.G_MISS_CHAR
,p_tp_calc_base_code_override IN pa_project_assignments.tp_calc_base_code_override%TYPE := FND_API.G_MISS_CHAR
,p_tp_percent_applied_override IN pa_project_assignments.tp_percent_applied_override%TYPE := FND_API.G_MISS_NUM
,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
,p_resource_list_member_id IN pa_project_assignments.resource_list_member_id%TYPE := FND_API.G_MISS_NUM
,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
SELECT impl.RATE_DISCOUNT_REASON_FLAG,impl.BR_OVERRIDE_FLAG,impl.BR_DISCOUNT_OVERRIDE_FLAG
FROM PA_IMPLEMENTATIONS_ALL impl,pa_projects_all proj
WHERE proj.org_id=impl.org_id
and proj.project_id = p_project_id ;
PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PUB.Execute_Update_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Execute_Update_Assignment.begin'
,x_msg => 'Beginning of Execute_Update_Assignment'
,x_log_level => 5);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Execute_Update_Assignment.update_assignment'
,x_msg => 'After record assignment, Calling Update_Assignment'
,x_log_level => 5);
PA_ASSIGNMENTS_PUB.Update_Assignment
( p_assignment_rec => l_assignment_rec
,p_asgn_update_mode => p_asgn_update_mode
,p_project_number => p_project_number
,p_resource_name => p_resource_name
,p_resource_source_id => p_resource_source_id
,p_resource_id => p_resource_id
,p_project_status_name => p_project_status_name
,p_project_subteam_id => p_project_subteam_id
,p_project_subteam_party_id => p_project_subteam_party_id
,p_project_subteam_name => p_project_subteam_name
,p_calendar_name => p_calendar_name
,p_staffing_priority_name => p_staffing_priority_name
,p_project_role_name => p_project_role_name
,p_location_city => p_location_city
,p_location_region => p_location_region
,p_location_country_name => p_location_country_name
,p_location_country_code => p_location_country_code
,p_work_type_name => p_work_type_name
,p_fcst_job_name => p_fcst_job_name
,p_fcst_job_group_name => p_fcst_job_group_name
,p_expenditure_org_name => p_expenditure_org_name
,p_exp_organization_name => p_exp_organization_name
,p_search_country_name => p_search_country_name
,p_search_exp_org_hier_name => p_search_exp_org_hier_name
,p_search_exp_start_org_name => p_search_exp_start_org_name
,p_staffing_owner_name => p_staffing_owner_name
,p_api_version => p_api_version
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_max_msg_count => p_max_msg_count
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENT_PUB.Execute_Update_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Execute_Update_Assignment;
PROCEDURE Update_Assignment
( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
,p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR /* Bug 1851096 */
,p_resource_name IN pa_resources.name%TYPE := FND_API.G_MISS_CHAR
,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
,p_resource_id IN pa_resources.resource_id%TYPE := FND_API.G_MISS_NUM
,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)IS
l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
SELECT ROWID, apprvl_status_code
FROM pa_project_assignments
WHERE assignment_id = p_assignment_rec.assignment_id
AND record_version_number = nvl (p_assignment_rec.record_version_number, record_version_number);
SELECT system_linkage_function
FROM pa_expend_typ_sys_links_v
WHERE expenditure_type = l_assignment_rec.expenditure_type
AND system_linkage_function in ('ST', 'OT');
SELECT start_date
FROM pa_project_assignments
WHERE assignment_id = l_assignment_rec.assignment_id;
SELECT workflow_in_progress_flag
FROM pa_team_templates
WHERE team_template_id = l_assignment_rec.assignment_template_id;
SELECT mass_wf_in_progress_flag
FROM pa_project_assignments
WHERE assignment_id = l_assignment_rec.assignment_id;
SELECT ps.project_system_status_code
FROM pa_project_assignments asgn,
pa_project_statuses ps
WHERE asgn.assignment_id = l_assignment_rec.assignment_id
AND asgn.status_code = ps.project_status_code(+);
SELECT impl.RATE_DISCOUNT_REASON_FLAG
,impl.BR_OVERRIDE_FLAG
,impl.BR_DISCOUNT_OVERRIDE_FLAG
FROM PA_IMPLEMENTATIONS_ALL impl
,pa_projects_all proj
WHERE proj.org_id=impl.org_id
and proj.project_id = l_assignment_rec.project_id ;
PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PUB.Update_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Update_Assignment.begin'
,x_msg => 'Beginning of Update_Assignment'
,x_log_level => 5);
SAVEPOINT ASG_PUB_UPDATE_ASSIGNMENT;
IF p_asgn_update_mode <> PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO
and p_asgn_update_mode <> PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS
and p_asgn_update_mode <> 'MASS_ONLINE' THEN
--dbms_output.put_line('single update - check number_mass_wf_in_progress');
IF p_asgn_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO
OR p_asgn_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS THEN
OPEN get_project_system_status_code;
,p_msg_name => 'PA_UPDATE_CAN_FILL_ASMT');
,p_msg_name => 'PA_SCH_UPDATE_NOT_ALLOWED');
ELSIF p_asgn_update_mode <> PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO
AND p_asgn_update_mode <> PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS
AND ((l_assignment_rec.project_role_id <> FND_API.G_MISS_NUM OR l_assignment_rec.project_role_id IS NULL) OR
(p_project_role_name <> FND_API.G_MISS_CHAR OR p_project_role_name IS NULL) OR
(p_resource_id <> FND_API.G_MISS_NUM OR p_resource_id IS NULL) OR
(p_resource_name <> FND_API.G_MISS_CHAR OR p_resource_name IS NULL) OR
(p_resource_source_id <> FND_API.G_MISS_NUM OR p_resource_source_id IS NULL)) THEN
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_RES_OR_ROLE_NOT_ALLOWED');
IF p_asgn_update_mode <> 'MASS_ONLINE' THEN
OPEN check_record_version;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Update_Assignment.Id_validation'
,x_msg => 'Do Value to ID conversion.'
,x_log_level => 5);
IF (p_asgn_update_mode <> 'MASS_ONLINE') THEN
IF l_assignment_rec.start_date IS NULL OR l_assignment_rec.start_date = FND_API.G_MISS_DATE THEN
--
--Get assignment start date
--
OPEN get_start_date;
IF (p_asgn_update_mode <> 'MASS_ONLINE') THEN
--
--Validate Expenditure Type
--
--dbms_output.put_line('start date:'||l_assignment_rec.start_date);
IF (p_asgn_update_mode <> 'MASS_ONLINE') THEN
--check a valid expenditure organization
l_valid_flag := PA_UTILS2.CheckExpOrg (x_org_id => l_assignment_rec.expenditure_organization_id,
x_txn_date => l_assignment_rec.start_date);
END IF; -- end of checking in Mass Update Online Mode, in which case start date related checks are skipped.
pa_hr_update_api.check_exp_OU(p_org_id =>l_assignment_rec.expenditure_org_id
,x_return_status =>l_return_status
,x_error_message_code =>l_error_message_code ) ;
IF p_asgn_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO OR
p_asgn_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS THEN
--dbms_output.put_line('check PA_ADM_ASN_CR_AND_DL for Mass Update ');
IF p_asgn_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS THEN
--dbms_output.put_line('check PA_ADM_ASN_CR_AND_DL for Mass Update Forecast ');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Update_Assignment.pvt_update_asgmt'
,x_msg => 'Calling PVT Update_Assignment'
,x_log_level => 5);
IF p_asgn_update_mode <> 'MASS_ONLINE' THEN
--dbms_output.put_line('Calling PVTB Update_Assignment');
PA_ASSIGNMENTS_PVT.Update_Assignment
( p_assignment_rec => l_assignment_rec
,p_project_subteam_id => l_subteam_id
,p_project_subteam_party_id=> p_project_subteam_party_id
,p_location_city => p_location_city
,p_location_region => p_location_region
,p_location_country_code => l_location_country_code
,p_commit => p_commit
,p_validate_only => p_validate_only
,x_return_status => l_return_status
);
ROLLBACK TO ASG_PUB_UPDATE_ASSIGNMENT;
ROLLBACK TO ASG_PUB_UPDATE_ASSIGNMENT;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PUB.Update_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Assignment;
PROCEDURE Delete_Assignment
( p_assignment_row_id IN ROWID := NULL
,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
,p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
,p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0 /* Bug 1851096 */
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_project_party_id NUMBER;
SELECT ROWID, project_party_id,project_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id
AND record_version_number = p_record_version_number;
SELECT assignment_id
FROM pa_project_assignments
WHERE source_assignment_id = p_assignment_id;
SELECT tt.workflow_in_progress_flag
FROM pa_project_assignments asgn,
pa_team_templates tt
WHERE asgn.assignment_id = p_assignment_id
AND asgn.template_flag = 'Y'
AND tt.team_template_id = asgn.assignment_template_id;
SELECT mass_wf_in_progress_flag
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PUB.Delete_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Delete_Assignment.begin'
,x_msg => 'Beginning of Delete_Assignment'
,x_log_level => 5);
SAVEPOINT ASG_PUB_DELETE_ASSIGNMENT;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Delete_Assignment.pvt_delete_asgmt'
,x_msg => 'Calling PVT Delete_Assignment'
,x_log_level => 5);
PA_ASSIGNMENTS_PVT.Delete_Assignment
( p_assignment_row_id => l_assignment_row_id
,p_assignment_id => p_assignment_id
,p_assignment_type => p_assignment_type
,p_record_version_number => p_record_version_number
,p_assignment_number => p_assignment_number
,p_project_party_id => l_project_party_id
,p_calling_module => p_calling_module
,p_commit => p_commit
,p_validate_only => p_validate_only
,x_return_status => l_return_status
);
ROLLBACK TO ASG_PUB_DELETE_ASSIGNMENT;
ROLLBACK TO ASG_PUB_DELETE_ASSIGNMENT;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PUB.Delete_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Delete_Assignment;
SELECT pa.assignment_name
,'OPEN_ASSIGNMENT'
,pa.assignment_type
,pa.staffing_priority_code
,pa.project_id
,pa.project_role_id
,pa.description
,pa.start_date
,pa.end_date
,pa.status_code
,pa.assignment_effort
,pa.extension_possible
,pa.min_resource_job_level
,pa.max_resource_job_level
,pa.additional_information
,pa.location_id
,pa.work_type_id
,pa.revenue_currency_code
,pa.revenue_bill_rate
,pa.markup_percent
,pa.expense_owner
,pa.expense_limit
,pa.expense_limit_currency_code
,pa.fcst_tp_amount_type
,pa.fcst_job_id
,pa.fcst_job_group_id
,pa.expenditure_org_id
,pa.expenditure_organization_id
,pa.expenditure_type_class
,pa.expenditure_type
,pa.calendar_type
,pa.calendar_id
,pa.competence_match_weighting
,pa.availability_match_weighting
,pa.job_level_match_weighting
,pa.search_min_availability
,pa.search_country_code
,pa.search_exp_org_struct_ver_id
,pa.search_exp_start_org_id
,pa.search_min_candidate_score
,pa.enable_auto_cand_nom_flag
,pa.staffing_owner_person_id
,pa.attribute_category
,pa.attribute1
,pa.attribute2
,pa.attribute3
,pa.attribute4
,pa.attribute5
,pa.attribute6
,pa.attribute7
,pa.attribute8
,pa.attribute9
,pa.attribute10
,pa.attribute11
,pa.attribute12
,pa.attribute13
,pa.attribute14
,pa.attribute15
,asets.action_set_id
,pa.bill_rate_override
,pa.bill_rate_curr_override
,pa.markup_percent_override
,pa.tp_rate_override
,pa.tp_currency_override
,pa.tp_calc_base_code_override
,pa.tp_percent_applied_override
,pa.resource_list_member_id -- FP.M Development
INTO
l_assignment_rec.assignment_name
,l_assignment_rec.assignment_type
,l_assignment_rec.source_assignment_type
,l_assignment_rec.staffing_priority_code
,l_assignment_rec.project_id
,l_assignment_rec.project_role_id
,l_assignment_rec.description
,l_assignment_rec.start_date
,l_assignment_rec.end_date
,l_assignment_rec.status_code
,l_assignment_rec.assignment_effort
,l_assignment_rec.extension_possible
,l_assignment_rec.min_resource_job_level
,l_assignment_rec.max_resource_job_level
,l_assignment_rec.additional_information
,l_assignment_rec.location_id
,l_assignment_rec.work_type_id
,l_assignment_rec.revenue_currency_code
,l_assignment_rec.revenue_bill_rate
,l_assignment_rec.markup_percent
,l_assignment_rec.expense_owner
,l_assignment_rec.expense_limit
,l_assignment_rec.expense_limit_currency_code
,l_assignment_rec.fcst_tp_amount_type
,l_assignment_rec.fcst_job_id
,l_assignment_rec.fcst_job_group_id
,l_assignment_rec.expenditure_org_id
,l_assignment_rec.expenditure_organization_id
,l_assignment_rec.expenditure_type_class
,l_assignment_rec.expenditure_type
,l_assignment_rec.calendar_type
,l_assignment_rec.calendar_id
,l_assignment_rec.comp_match_weighting
,l_assignment_rec.avail_match_weighting
,l_assignment_rec.job_level_match_weighting
,l_assignment_rec.search_min_availability
,l_assignment_rec.search_country_code
,l_assignment_rec.search_exp_org_struct_ver_id
,l_assignment_rec.search_exp_start_org_id
,l_assignment_rec.search_min_candidate_score
,l_assignment_rec.enable_auto_cand_nom_flag
,l_assignment_rec.staffing_owner_person_id
,l_assignment_rec.attribute_category
,l_assignment_rec.attribute1
,l_assignment_rec.attribute2
,l_assignment_rec.attribute3
,l_assignment_rec.attribute4
,l_assignment_rec.attribute5
,l_assignment_rec.attribute6
,l_assignment_rec.attribute7
,l_assignment_rec.attribute8
,l_assignment_rec.attribute9
,l_assignment_rec.attribute10
,l_assignment_rec.attribute11
,l_assignment_rec.attribute12
,l_assignment_rec.attribute13
,l_assignment_rec.attribute14
,l_assignment_rec.attribute15
,l_adv_action_set_id
,l_assignment_rec.bill_rate_override
,l_assignment_rec.bill_rate_curr_override
,l_assignment_rec.markup_percent_override
,l_assignment_rec.tp_rate_override
,l_assignment_rec.tp_currency_override
,l_assignment_rec.tp_calc_base_code_override
,l_assignment_rec.tp_percent_applied_override
,l_assignment_rec.resource_list_member_id
FROM pa_project_assignments pa,
pa_action_sets asets
WHERE pa.assignment_id = p_assignment_id
AND asets.object_id(+) = pa.assignment_id
AND asets.object_type(+) = 'OPEN_ASSIGNMENT'
AND asets.action_set_type_code(+) = 'ADVERTISEMENT'
AND asets.status_code(+) <> 'DELETED';
SELECT calendar_id INTO l_assignment_rec.calendar_id
FROM pa_projects_all
WHERE project_id = l_assignment_rec.project_id;
SELECT fcst_tp_amount_type
FROM pa_project_assignments -- 5078700 changed from pa_project_assignments_v to pa_project_assignments
WHERE assignment_id = p_asg_id;
PROCEDURE Mass_Exec_Update_Assignments
( p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_assignment_id_tbl IN system.pa_num_tbl_type
,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
,p_append_description_flag IN VARCHAR2 := 'N'
,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_append_information_flag IN VARCHAR2 := 'N'
,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_wf_mode VARCHAR2(200);
pa_assignments_pub.Execute_Update_Assignment
( p_asgn_update_mode => 'MASS_ONLINE'
,p_assignment_name => p_assignment_name
,p_assignment_type => p_assignment_type
,p_staffing_priority_code => p_staffing_priority_code
,p_project_id => p_project_id
,p_project_subteam_id => p_project_subteam_id
,p_description => p_description
,p_extension_possible => p_extension_possible
,p_min_resource_job_level => p_min_resource_job_level
,p_max_resource_job_level => p_max_resource_job_level
,p_additional_information => p_additional_information
,p_location_id => p_location_id
,p_work_type_id => p_work_type_id
,p_expense_owner => p_expense_owner
,p_expense_limit => p_expense_limit
,p_expense_limit_currency_code => p_expense_limit_currency_code
,p_fcst_tp_amount_type => p_fcst_tp_amount_type
,p_fcst_job_id => p_fcst_job_id
,p_fcst_job_group_id => p_fcst_job_group_id
,p_expenditure_org_id => p_expenditure_org_id
,p_expenditure_organization_id => p_expenditure_organization_id
,p_expenditure_type_class => p_expenditure_type_class
,p_expenditure_type => p_expenditure_type
,p_project_subteam_name => p_project_subteam_name
,p_staffing_priority_name => p_staffing_priority_name
,p_location_city => p_location_city
,p_location_region => p_location_region
,p_location_country_name => p_location_country_name
,p_location_country_code => p_location_country_code
,p_work_type_name => p_work_type_name
,p_fcst_job_name => p_fcst_job_name
,p_fcst_job_group_name => p_fcst_job_group_name
,p_expenditure_org_name => p_expenditure_org_name
,p_exp_organization_name => p_exp_organization_name
,p_comp_match_weighting => p_avail_match_weighting
,p_avail_match_weighting => p_avail_match_weighting
,p_job_level_match_weighting => p_job_level_match_weighting
,p_search_min_availability => p_search_min_availability
,p_search_country_code => p_search_country_code
,p_search_country_name => p_search_country_name
,p_search_exp_org_struct_ver_id => p_search_exp_org_struct_ver_id
,p_search_exp_org_hier_name => p_search_exp_org_hier_name
,p_search_exp_start_org_id => p_search_exp_start_org_id
,p_search_exp_start_org_name => p_search_exp_start_org_name
,p_search_min_candidate_score => p_search_min_candidate_score
,p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag
,p_staffing_owner_person_id => p_staffing_owner_person_id
,p_staffing_owner_name => p_staffing_owner_name
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_max_msg_count => p_max_msg_count
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF p_asgn_update_mode = 'Forecast' THEN
l_wf_mode := PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS;
l_wf_mode := PA_MASS_ASGMT_TRX.G_MASS_UPDATE_ASGMT_BASIC_INFO;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PUB.Mass_Exec_Update_Assignments'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Mass_Exec_Update_Assignments;
PROCEDURE Mass_Update_Assignments
( p_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_assignment_id_tbl IN system.pa_num_tbl_type
,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
,p_append_description_flag IN VARCHAR2 := 'N'
,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_append_information_flag IN VARCHAR2 := 'N'
,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM --FP.L Development
,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR --FP.L Development
,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_success_assignment_id_tbl OUT NOCOPY system.pa_num_tbl_type -- For 1159 mandate changes bug#2674619
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_assignment_id_tbl system.pa_num_tbl_type;
SELECT ppsp.project_subteam_party_id, ppa.assignment_type, ppa.description, ppa.additional_information, ppa.start_date, ppa.end_date, ppa.min_resource_job_level, ppa.max_resource_job_level, ppa.resource_id
FROM pa_project_assignments ppa,
pa_project_subteam_parties ppsp
WHERE ppa.assignment_id = l_asmt_id
AND ppsp.object_type (+)= 'PA_PROJECT_ASSIGNMENTS'
AND ppa.assignment_id = ppsp.object_id(+)
AND ppsp.primary_subteam_flag(+) = 'Y';
pa_assignments_pub.Execute_Update_Assignment
( p_asgn_update_mode => p_update_mode
,p_assignment_id => p_assignment_id_tbl(i)
,p_record_version_number => NULL
,p_assignment_type => l_assignment_type
,p_assignment_name => nvl(p_assignment_name, FND_API.G_MISS_CHAR)
,p_staffing_priority_code => nvl(p_staffing_priority_code, FND_API.G_MISS_CHAR)
,p_project_id => p_project_id
,p_project_subteam_id => nvl(p_project_subteam_id, FND_API.G_MISS_NUM)
,p_project_subteam_party_id => l_subteam_party_id
,p_description => nvl(l_description, FND_API.G_MISS_CHAR)
,p_extension_possible => nvl(p_extension_possible, FND_API.G_MISS_CHAR)
,p_min_resource_job_level => nvl(l_min_resource_job_level, l_req_min_resource_job_level)
,p_max_resource_job_level => nvl(l_max_resource_job_level, l_req_max_resource_job_level)
,p_additional_information => nvl(l_additional_info, FND_API.G_MISS_CHAR)
,p_location_id => nvl(p_location_id, FND_API.G_MISS_NUM)
,p_work_type_id => nvl(p_work_type_id, FND_API.G_MISS_NUM)
,p_expense_owner => nvl(p_expense_owner, FND_API.G_MISS_CHAR)
,p_expense_limit => nvl(p_expense_limit, FND_API.G_MISS_NUM)
,p_expense_limit_currency_code => nvl(p_expense_limit_currency_code, FND_API.G_MISS_CHAR)
,p_fcst_tp_amount_type => nvl(p_fcst_tp_amount_type, FND_API.G_MISS_CHAR)
,p_fcst_job_id => nvl(p_fcst_job_id, FND_API.G_MISS_NUM)
,p_fcst_job_group_id => nvl(p_fcst_job_group_id, FND_API.G_MISS_NUM)
,p_expenditure_org_id => nvl(p_expenditure_org_id, FND_API.G_MISS_NUM)
,p_expenditure_organization_id => nvl(p_expenditure_organization_id, FND_API.G_MISS_NUM)
,p_expenditure_type_class => nvl(p_expenditure_type_class, FND_API.G_MISS_CHAR)
,p_expenditure_type => nvl(p_expenditure_type, FND_API.G_MISS_CHAR)
,p_project_subteam_name => nvl(p_project_subteam_name, FND_API.G_MISS_CHAR)
,p_staffing_priority_name => nvl(p_staffing_priority_name, FND_API.G_MISS_CHAR)
,p_location_city => nvl(p_location_city, FND_API.G_MISS_CHAR)
,p_location_region => nvl(p_location_region, FND_API.G_MISS_CHAR)
,p_location_country_name => nvl(p_location_country_name, FND_API.G_MISS_CHAR)
,p_location_country_code => nvl(p_location_country_code, FND_API.G_MISS_CHAR)
,p_work_type_name => nvl(p_work_type_name, FND_API.G_MISS_CHAR)
,p_fcst_job_name => nvl(p_fcst_job_name, FND_API.G_MISS_CHAR)
,p_fcst_job_group_name => nvl(p_fcst_job_group_name, FND_API.G_MISS_CHAR)
,p_expenditure_org_name => nvl(p_expenditure_org_name, FND_API.G_MISS_CHAR)
,p_exp_organization_name => nvl(p_exp_organization_name, FND_API.G_MISS_CHAR)
,p_resource_id => nvl(l_resource_id, FND_API.G_MISS_NUM)
,p_comp_match_weighting => nvl(p_comp_match_weighting, FND_API.G_MISS_NUM)
,p_avail_match_weighting => nvl(p_avail_match_weighting, FND_API.G_MISS_NUM)
,p_job_level_match_weighting => nvl(p_job_level_match_weighting, FND_API.G_MISS_NUM)
,p_search_min_availability => nvl(p_search_min_availability, FND_API.G_MISS_NUM)
,p_search_country_code => nvl(p_search_country_code, FND_API.G_MISS_CHAR)
,p_search_country_name => nvl(p_search_country_name, FND_API.G_MISS_CHAR)
,p_search_exp_org_struct_ver_id => nvl(p_search_exp_org_struct_ver_id, FND_API.G_MISS_NUM)
,p_search_exp_org_hier_name => nvl(p_search_exp_org_hier_name, FND_API.G_MISS_CHAR)
,p_search_exp_start_org_id => nvl(p_search_exp_start_org_id, FND_API.G_MISS_NUM)
,p_search_exp_start_org_name => nvl(p_search_exp_start_org_name, FND_API.G_MISS_CHAR)
,p_search_min_candidate_score => nvl(p_search_min_candidate_score, FND_API.G_MISS_NUM)
,p_enable_auto_cand_nom_flag => nvl(p_enable_auto_cand_nom_flag, FND_API.G_MISS_CHAR)
-- FP.L Development
,p_staffing_owner_person_id => nvl(p_staffing_owner_person_id, FND_API.G_MISS_NUM)
,p_staffing_owner_name => nvl(p_staffing_owner_name, FND_API.G_MISS_CHAR)
,p_attribute_category => nvl(p_attribute_category, FND_API.G_MISS_CHAR)
,p_attribute1 => nvl(p_attribute1, FND_API.G_MISS_CHAR)
,p_attribute2 => nvl(p_attribute2, FND_API.G_MISS_CHAR)
,p_attribute3 => nvl(p_attribute3, FND_API.G_MISS_CHAR)
,p_attribute4 => nvl(p_attribute4, FND_API.G_MISS_CHAR)
,p_attribute5 => nvl(p_attribute5, FND_API.G_MISS_CHAR)
,p_attribute6 => nvl(p_attribute6, FND_API.G_MISS_CHAR)
,p_attribute7 => nvl(p_attribute7, FND_API.G_MISS_CHAR)
,p_attribute8 => nvl(p_attribute8, FND_API.G_MISS_CHAR)
,p_attribute9 => nvl(p_attribute9, FND_API.G_MISS_CHAR)
,p_attribute10 => nvl(p_attribute10, FND_API.G_MISS_CHAR)
,p_attribute11 => nvl(p_attribute11, FND_API.G_MISS_CHAR)
,p_attribute12 => nvl(p_attribute12, FND_API.G_MISS_CHAR)
,p_attribute13 => nvl(p_attribute13, FND_API.G_MISS_CHAR)
,p_attribute14 => nvl(p_attribute14, FND_API.G_MISS_CHAR)
,p_attribute15 => nvl(p_attribute15, FND_API.G_MISS_CHAR)
,p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_max_msg_count => p_max_msg_count
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
p_update_mode = PA_MASS_ASGMT_TRX.G_MASS_UPDATE_FORECAST_ITEMS THEN
--dbms_output.put_line('calling PA_FORECASTITEM_PVT.Create_Forecast_Item');
p_source_type2 => p_update_mode,
p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
p_context1 => p_project_id,
p_context2 => p_assignment_id_tbl(i),
p_context3 => NULL,
p_commit => FND_API.G_TRUE,
x_return_status => l_return_status);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PUB.Mass_Update_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Mass_Update_Assignments;
PROCEDURE Execute_Update_Requirement
( p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_assignment_row_id IN ROWID := NULL
,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
,p_assignment_template_id IN pa_project_assignments.assignment_template_id%TYPE := FND_API.G_MISS_NUM
,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
,p_project_party_id IN pa_project_assignments.project_party_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := FND_API.G_MISS_NUM
,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
,p_assignment_effort IN pa_project_assignments.assignment_effort%TYPE := FND_API.G_MISS_NUM
,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
,p_source_assignment_id IN pa_project_assignments.source_assignment_id%TYPE := FND_API.G_MISS_NUM
,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
,p_assignment_number IN pa_project_assignments.assignment_number%TYPE := FND_API.G_MISS_NUM
,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
,p_revenue_currency_code IN pa_project_assignments.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_revenue_bill_rate IN pa_project_assignments.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
,p_markup_percent IN pa_project_assignments.markup_percent%TYPE := FND_API.G_MISS_NUM
,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR /* Bug 1851096 */
,p_resource_name IN pa_resources.name%TYPE := FND_API.G_MISS_CHAR
,p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM
,p_resource_id IN pa_resources.resource_id%TYPE := FND_API.G_MISS_NUM
,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
,p_calendar_id IN jtf_calendars_tl.calendar_id%TYPE := FND_API.G_MISS_NUM
,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
,p_bill_rate_override IN pa_project_assignments.bill_rate_override%TYPE := FND_API.G_MISS_NUM
,p_bill_rate_curr_override IN pa_project_assignments.bill_rate_curr_override%TYPE := FND_API.G_MISS_CHAR
,p_markup_percent_override IN pa_project_assignments.markup_percent_override%TYPE := FND_API.G_MISS_NUM
,p_discount_percentage IN pa_project_assignments.discount_percentage%TYPE := FND_API.G_MISS_NUM -- Bug 2590938
,p_rate_disc_reason_code IN pa_project_assignments.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR -- Bug 2590938
,p_tp_rate_override IN pa_project_assignments.tp_rate_override%TYPE := FND_API.G_MISS_NUM
,p_tp_currency_override IN pa_project_assignments.tp_currency_override%TYPE := FND_API.G_MISS_CHAR
,p_tp_calc_base_code_override IN pa_project_assignments.tp_calc_base_code_override%TYPE := FND_API.G_MISS_CHAR
,p_tp_percent_applied_override IN pa_project_assignments.tp_percent_applied_override%TYPE := FND_API.G_MISS_NUM
,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
,p_attribute_category IN pa_project_assignments.attribute_category%TYPE := FND_API.G_MISS_CHAR
,p_attribute1 IN pa_project_assignments.attribute1%TYPE := FND_API.G_MISS_CHAR
,p_attribute2 IN pa_project_assignments.attribute2%TYPE := FND_API.G_MISS_CHAR
,p_attribute3 IN pa_project_assignments.attribute3%TYPE := FND_API.G_MISS_CHAR
,p_attribute4 IN pa_project_assignments.attribute4%TYPE := FND_API.G_MISS_CHAR
,p_attribute5 IN pa_project_assignments.attribute5%TYPE := FND_API.G_MISS_CHAR
,p_attribute6 IN pa_project_assignments.attribute6%TYPE := FND_API.G_MISS_CHAR
,p_attribute7 IN pa_project_assignments.attribute7%TYPE := FND_API.G_MISS_CHAR
,p_attribute8 IN pa_project_assignments.attribute8%TYPE := FND_API.G_MISS_CHAR
,p_attribute9 IN pa_project_assignments.attribute9%TYPE := FND_API.G_MISS_CHAR
,p_attribute10 IN pa_project_assignments.attribute10%TYPE := FND_API.G_MISS_CHAR
,p_attribute11 IN pa_project_assignments.attribute11%TYPE := FND_API.G_MISS_CHAR
,p_attribute12 IN pa_project_assignments.attribute12%TYPE := FND_API.G_MISS_CHAR
,p_attribute13 IN pa_project_assignments.attribute13%TYPE := FND_API.G_MISS_CHAR
,p_attribute14 IN pa_project_assignments.attribute14%TYPE := FND_API.G_MISS_CHAR
,p_attribute15 IN pa_project_assignments.attribute15%TYPE := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PUB.Execute_Update_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Execute_Update_Requirement.begin'
,x_msg => 'Beginning of Execute_Update_Assignment'
,x_log_level => 5);
SELECT 'Y'
INTO l_exists
FROM pa_project_assignments asgn
,pa_project_subteam_parties psp
,pa_locations loc
WHERE asgn.project_id = p_project_id
AND asgn.assignment_id = p_assignment_id
AND asgn.assignment_name = p_assignment_name
AND asgn.min_resource_job_level = p_min_resource_job_level
AND asgn.max_resource_job_level = p_max_resource_job_level
AND asgn.staffing_priority_code = p_staffing_priority_code
AND asgn.staffing_owner_person_id = p_staffing_owner_person_id
AND asgn.description = p_description
AND asgn.additional_information = p_additional_information
AND psp.project_subteam_id = p_project_subteam_id
AND loc.country_code = p_location_country_code
AND loc.region = p_location_region
AND loc.city = p_location_city
AND asgn.assignment_id = psp.object_id (+)
AND psp.object_type(+) = 'PA_PROJECT_ASSIGNMENTS'
AND psp.primary_subteam_flag(+) = 'Y'
AND asgn.location_id = loc.location_id (+);
SELECT 'Y'
INTO l_exists
FROM pa_project_assignments
WHERE project_id = p_project_id
AND assignment_id = p_assignment_id
AND competence_match_weighting = p_comp_match_weighting
AND availability_match_weighting = p_avail_match_weighting
AND job_level_match_weighting = p_job_level_match_weighting
AND enable_auto_cand_nom_flag = p_enable_auto_cand_nom_flag
AND search_min_availability = p_search_min_availability
AND search_exp_org_struct_ver_id = p_search_exp_org_struct_ver_id
AND search_exp_start_org_id = p_search_exp_start_org_id
AND search_country_code = p_search_country_code
AND search_min_candidate_score = p_search_min_candidate_score;
SELECT 'Y'
INTO l_exists
FROM pa_project_assignments
WHERE project_id = p_project_id
AND assignment_id = p_assignment_id
AND extension_possible = p_extension_possible
AND expense_owner = p_expense_owner
AND expense_limit = p_expense_limit
AND expenditure_org_id = p_expenditure_org_id
AND expenditure_organization_id = p_expenditure_organization_id
AND expenditure_type_class = p_expenditure_type_class
AND fcst_job_group_id = p_fcst_job_group_id
AND fcst_job_id = p_fcst_job_id
AND work_type_id = p_fcst_job_id;
PA_ASSIGNMENTS_PUB.Update_Assignment
( p_assignment_rec => l_assignment_rec
,p_asgn_update_mode => p_asgn_update_mode
,p_project_number => p_project_number
,p_resource_name => p_resource_name
,p_resource_source_id => p_resource_source_id
,p_resource_id => p_resource_id
,p_project_status_name => p_project_status_name
,p_project_subteam_id => p_project_subteam_id
,p_project_subteam_party_id => p_project_subteam_party_id
,p_project_subteam_name => p_project_subteam_name
,p_calendar_name => p_calendar_name
,p_staffing_priority_name => p_staffing_priority_name
,p_project_role_name => p_project_role_name
,p_location_city => p_location_city
,p_location_region => p_location_region
,p_location_country_name => p_location_country_name
,p_location_country_code => p_location_country_code
,p_work_type_name => p_work_type_name
,p_fcst_job_name => p_fcst_job_name
,p_fcst_job_group_name => p_fcst_job_group_name
,p_expenditure_org_name => p_expenditure_org_name
,p_exp_organization_name => p_exp_organization_name
,p_search_country_name => p_search_country_name
,p_search_exp_org_hier_name => p_search_exp_org_hier_name
,p_search_exp_start_org_name => p_search_exp_start_org_name
,p_staffing_owner_name => p_staffing_owner_name
,p_api_version => p_api_version
,p_commit => p_commit
,p_validate_only => p_validate_only
,p_max_msg_count => p_max_msg_count
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END Execute_Update_Requirement;
PROCEDURE DELETE_PJR_TXNS
(p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
,p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
CURSOR asgn_details IS
SELECT ROWID row_id
,assignment_id
,assignment_type
,record_version_number
,assignment_number
,project_party_id
FROM pa_project_assignments a
WHERE a.project_id = p_project_id;
PA_DEBUG.init_err_stack('PA_ASSIGNMENTS_PUB.Delete_PJR_Txns');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Delete_PJR_Txns.begin'
,x_msg => 'Beginning of Delete_PJR_Txns'
,x_log_level => 5);
SAVEPOINT ASG_PUB_DELETE_PJR_TXNS;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENTS_PUB.Delete_PJR_Txns.pvt_delete_asgmt'
,x_msg => 'Calling PVT Delete_Assignment'
,x_log_level => 5);
PA_ASSIGNMENTS_PVT.Delete_Assignment
( p_assignment_row_id => l_rec.row_id
,p_assignment_id => l_rec.assignment_id
,p_assignment_type => l_rec.assignment_type
,p_record_version_number => l_rec.record_version_number
,p_assignment_number => l_rec.assignment_number
,p_project_party_id => l_rec.project_party_id
,p_calling_module => p_calling_module
,p_commit => p_commit
,p_validate_only => p_validate_only
,x_return_status => l_return_status
);
ROLLBACK TO ASG_PUB_DELETE_PJR_TXNS;
ROLLBACK TO ASG_PUB_DELETE_PJR_TXNS;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_PUB.Delete_PJR_Txns'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END DELETE_PJR_TXNS;