[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.assignment_type, ppa.project_id, ppa.start_date, ppa.status_code,
pps.project_status_name, ppa.pending_approval_flag
FROM pa_project_assignments ppa,
pa_project_statuses pps,
pa_projects_all pal
WHERE ppa.assignment_id = p_assignment_id
AND ppa.project_id = pal.project_id
AND pal.project_status_code = pps.project_status_code;
SELECT DISTINCT status_code
FROM pa_schedules /* Bug 5614557 Changed usage from pa_schedules_v to pa_schedules */
WHERE assignment_id = p_assignment_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_submitter_user_id;
PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.delete;
PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.update_status',
'Update Assignment Approval Status.', 5);
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(p_assignment_id => p_assignment_id
,p_action_code => l_action_code
,p_note_to_approver => p_note_to_approver
,p_record_version_number => p_record_version_number
,x_apprvl_status_code => l_apprvl_status_code
,x_change_id => l_change_id
,x_record_version_number => l_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
END IF; -- return_status of update_approval_status = FND_API.G_RET_STS_SUCCESS
PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.DELETE;
PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.DELETE;
SELECT apprvl_status_code, record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT
assignment_id
,assignment_name
,assignment_type
,multiple_status_flag
,record_version_number
,change_id
,apprvl_status_code
,status_code
,staffing_priority_code
,project_id
,project_role_id
,resource_id
,project_party_id
,project_subteam_id
,description
,note_to_approver
,start_date
,end_date
,assignment_effort
,extension_possible
,source_assignment_id
,assignment_template_id
,min_resource_job_level
,max_resource_job_level
,assignment_number
,additional_information
,work_type_id
,revenue_currency_code
,revenue_bill_rate
,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
,location_id
,calendar_type
,calendar_id
,resource_calendar_percent
-- ,no_of_active_candidates
,competence_match_weighting
,availability_match_weighting
,job_level_match_weighting
,search_min_availability
,search_country_code
,search_exp_org_struct_ver_id
,search_exp_start_org_id
,search_min_candidate_score
,last_auto_search_date
,enable_auto_cand_nom_flag
,mass_wf_in_progress_flag
,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
,markup_percent
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
INTO
l_assignment_rec.assignment_id
,l_assignment_rec.assignment_name
,l_assignment_rec.assignment_type
,l_assignment_rec.multiple_status_flag
,l_assignment_rec.record_version_number
,l_change_id
,l_assignment_rec.apprvl_status_code
,l_assignment_rec.status_code
,l_assignment_rec.staffing_priority_code
,l_assignment_rec.project_id
,l_assignment_rec.project_role_id
,l_assignment_rec.resource_id
,l_assignment_rec.project_party_id
,l_project_subteam_id
,l_assignment_rec.description
,l_assignment_rec.note_to_approver
,l_assignment_rec.start_date
,l_assignment_rec.end_date
,l_assignment_rec.assignment_effort
,l_assignment_rec.extension_possible
,l_assignment_rec.source_assignment_id
,l_assignment_rec.assignment_template_id
,l_assignment_rec.min_resource_job_level
,l_assignment_rec.max_resource_job_level
,l_assignment_rec.assignment_number
,l_assignment_rec.additional_information
,l_assignment_rec.work_type_id
,l_assignment_rec.revenue_currency_code
,l_assignment_rec.revenue_bill_rate
,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.location_id
,l_assignment_rec.calendar_type
,l_assignment_rec.calendar_id
,l_assignment_rec.resource_calendar_percent
-- ,l_assignment_rec.no_of_active_candidates
,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.last_auto_search_date
,l_assignment_rec.enable_auto_cand_nom_flag
,l_assignment_rec.mass_wf_in_progress_flag
,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.markup_percent
,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
FROM pa_assignments_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.delete_row'
,x_msg => 'Deleting last approved record.'
,x_log_level => 5);
PA_ASSIGNMENTS_HISTORY_PKG.Delete_Row( p_assignment_id => p_assignment_id
,p_last_approved_flag => 'Y'
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.update_row'
,x_msg => 'Update current record with history record details.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
(p_assignment_id => l_assignment_rec.assignment_id
,p_record_version_number => l_record_version_number
,p_assignment_name => l_assignment_rec.assignment_name
,p_assignment_type => l_assignment_rec.assignment_type
,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
,p_apprvl_status_code => l_next_status_code
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_status_code => l_assignment_rec.status_code
,p_project_id => l_assignment_rec.project_id
,p_project_role_id => l_assignment_rec.project_role_id
,p_resource_id => l_assignment_rec.resource_id
,p_project_party_id => l_assignment_rec.project_party_id
,p_description => l_assignment_rec.description
,p_note_to_approver => l_assignment_rec.note_to_approver
,p_start_date => l_assignment_rec.start_date
,p_end_date => l_assignment_rec.end_date
,p_assignment_effort => l_assignment_rec.assignment_effort
,p_extension_possible => l_assignment_rec.extension_possible
,p_source_assignment_id => l_assignment_rec.source_assignment_id
,p_assignment_template_id => l_assignment_rec.assignment_template_id
,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
,p_assignment_number => l_assignment_rec.assignment_number
,p_additional_information => l_assignment_rec.additional_information
,p_work_type_id => l_assignment_rec.work_type_id
,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
,p_fcst_job_id => l_assignment_rec.fcst_job_id
,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
,p_expenditure_type => l_assignment_rec.expenditure_type
,p_expense_owner => l_assignment_rec.expense_owner
,p_expense_limit => l_assignment_rec.expense_limit
,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
,p_location_id => l_assignment_rec.location_id
,p_calendar_type => l_assignment_rec.calendar_type
,p_calendar_id => l_assignment_rec.calendar_id
,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
,p_pending_approval_flag => 'N'
-- ,p_no_of_active_candidates => l_assignment_rec.no_of_active_candidates
,p_comp_match_weighting => l_assignment_rec.comp_match_weighting
,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
,p_search_min_availability => l_assignment_rec.search_min_availability
,p_search_country_code => l_assignment_rec.search_country_code
,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
-- ,p_last_auto_search_date => l_assignment_rec.last_auto_search_date
,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
-- ,p_mass_wf_in_progress_flag => l_assignment_rec.mass_wf_in_progress_flag
,p_bill_rate_override => l_assignment_rec.bill_rate_override
,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
,p_markup_percent_override => l_assignment_rec.markup_percent_override
,p_tp_rate_override => l_assignment_rec.tp_rate_override
,p_tp_currency_override => l_assignment_rec.tp_currency_override
,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
,p_markup_percent => l_assignment_rec.markup_percent
,p_attribute_category => l_assignment_rec.attribute_category
,p_attribute1 => l_assignment_rec.attribute1
,p_attribute2 => l_assignment_rec.attribute2
,p_attribute3 => l_assignment_rec.attribute3
,p_attribute4 => l_assignment_rec.attribute4
,p_attribute5 => l_assignment_rec.attribute5
,p_attribute6 => l_assignment_rec.attribute6
,p_attribute7 => l_assignment_rec.attribute7
,p_attribute8 => l_assignment_rec.attribute8
,p_attribute9 => l_assignment_rec.attribute9
,p_attribute10 => l_assignment_rec.attribute10
,p_attribute11 => l_assignment_rec.attribute11
,p_attribute12 => l_assignment_rec.attribute12
,p_attribute13 => l_assignment_rec.attribute13
,p_attribute14 => l_assignment_rec.attribute14
,p_attribute15 => l_assignment_rec.attribute15
,x_return_status => l_return_status);
PA_PROJECT_SUBTEAM_PARTIES_PVT.Update_SPT_Assgn
( p_validate_only => p_validate_only
,p_get_subteam_party_id_flag => 'Y'
,p_project_subteam_id => l_project_subteam_id
,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
,p_object_id => p_assignment_id
,x_project_subteam_party_id => l_project_subteam_party_id
,x_return_status => l_return_status
,x_record_version_number => l_record_version_number
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END IF; -- end of update row and revert
l_updated_asmt_rec pa_assignments_pub.assignment_rec_type;
SELECT record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT location_id, city, region, country_code
FROM pa_locations
WHERE location_id in (new_location_id, old_location_id);
SELECT project_subteam_id, name
FROM pa_project_subteams
WHERE project_subteam_id in (new_project_subteam_id, old_project_subteam_id);
SELECT project_subteam_id
FROM pa_project_subteam_parties
WHERE object_type = 'PA_PROJECT_ASSIGNMENTS'
AND object_id = p_assignment_id
AND primary_subteam_flag = 'Y';
SELECT project_subteam_id
FROM pa_assignments_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
SELECT work_type_id, name
FROM pa_work_types_vl
WHERE work_type_id in (new_work_type_id, old_work_type_id);
l_updated_asmt_rec := l_saved_asmt_rec;
IF (p_populate_mode = 'ASSIGNMENT_UPDATED') THEN
-- get the latest updated_asmt_rec based on db saved rec and the passed(unsaved) values, we will use
-- the latest updated_asmt_rec and l_asmt_history_rec to get the changed items.
SELECT DECODE(p_assignment_id, null, l_saved_asmt_rec.assignment_id, p_assignment_id),
DECODE(p_assignment_name, null, l_saved_asmt_rec.assignment_name, p_assignment_name),
DECODE(p_staffing_priority_code, null, l_saved_asmt_rec.staffing_priority_code, p_staffing_priority_code),
DECODE(p_description, null, l_saved_asmt_rec.description, p_description),
DECODE(p_extension_possible, null, l_saved_asmt_rec.extension_possible, p_extension_possible),
DECODE(p_additional_information, null, l_saved_asmt_rec.additional_information, p_additional_information),
DECODE(p_work_type_id, null, l_saved_asmt_rec.work_type_id, p_work_type_id),
DECODE(p_expense_owner, null, l_saved_asmt_rec.expense_owner, p_expense_owner),
DECODE(p_expense_limit, null, l_saved_asmt_rec.expense_limit, p_expense_limit),
DECODE(p_fcst_tp_amount_type, null, l_saved_asmt_rec.fcst_tp_amount_type, p_fcst_tp_amount_type),
DECODE(p_expenditure_type_class, null, l_saved_asmt_rec.expenditure_type_class, p_expenditure_type_class),
DECODE(p_expenditure_type, null, l_saved_asmt_rec.expenditure_type, p_expenditure_type),
DECODE(p_location_id, null, l_saved_asmt_rec.location_id, p_location_id),
DECODE(p_staffing_owner_person_id, null, l_saved_asmt_rec.staffing_owner_person_id, p_staffing_owner_person_id)
INTO l_updated_asmt_rec.assignment_id,
l_updated_asmt_rec.assignment_name,
l_updated_asmt_rec.staffing_priority_code,
l_updated_asmt_rec.description,
l_updated_asmt_rec.extension_possible,
l_updated_asmt_rec.additional_information,
l_updated_asmt_rec.work_type_id,
l_updated_asmt_rec.expense_owner,
l_updated_asmt_rec.expense_limit,
l_updated_asmt_rec.fcst_tp_amount_type,
l_updated_asmt_rec.expenditure_type_class,
l_updated_asmt_rec.expenditure_type,
l_updated_asmt_rec.location_id,
l_updated_asmt_rec.staffing_owner_person_id
FROM DUAL;
IF (NVL(l_asmt_history_rec.additional_information,-1) <> NVL(l_updated_asmt_rec.additional_information,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id,l_changed_item,l_updated_asmt_rec.additional_information,l_asmt_history_rec.additional_information);
IF (NVL(l_asmt_history_rec.expenditure_type,-1) <> NVL(l_updated_asmt_rec.expenditure_type,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.expenditure_type, l_asmt_history_rec.expenditure_type);
IF (NVL(l_asmt_history_rec.assignment_name,-1) <> NVL(l_updated_asmt_rec.assignment_name,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.assignment_name, l_asmt_history_rec.assignment_name);
IF (NVL(l_asmt_history_rec.description,-1) <> NVL(l_updated_asmt_rec.description,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.description, l_asmt_history_rec.description);
IF (NVL(l_asmt_history_rec.expense_limit,-1) <> NVL(l_updated_asmt_rec.expense_limit,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.expense_limit),
to_char(l_asmt_history_rec.expense_limit));
IF (NVL(l_asmt_history_rec.expense_owner,-1) <> NVL(l_updated_asmt_rec.expense_owner,-1)) THEN
l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('EXPENSE_OWNER_TYPE', l_updated_asmt_rec.expense_owner);
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
IF (NVL(l_asmt_history_rec.extension_possible,-1) <> NVL(l_updated_asmt_rec.extension_possible,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.extension_possible, l_asmt_history_rec.extension_possible);
IF (NVL(l_asmt_history_rec.location_id,-1) <> NVL(l_updated_asmt_rec.location_id,-1)) THEN
FOR c2 IN get_locations(l_asmt_history_rec.location_id, l_updated_asmt_rec.location_id) LOOP
IF c2.location_id = l_updated_asmt_rec.location_id THEN
l_new_city := c2.city;
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_city || ', ' || l_new_region || ', ' || l_new_country_code,
l_old_city||', ' || l_old_region || ', ' || l_old_country_code);
IF (NVL(l_asmt_history_rec.staffing_priority_code,-1) <> NVL(l_updated_asmt_rec.staffing_priority_code,-1)) THEN
l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('STAFFING_PRIORITY_CODE', l_updated_asmt_rec.staffing_priority_code);
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_subteam_name, l_old_subteam_name);
IF (NVL(l_asmt_history_rec.staffing_owner_person_id,-1) <> NVL(l_updated_asmt_rec.staffing_owner_person_id,-1)) THEN
-- If p_staffing_owner_person_id=null and p_staffing_owner_name<>null(when user just type name instead of
-- using LOV in Mass Update page), l_updated_asmt_rec.staffing_owner_person_id won't have the latest
-- value. Because p_staffing_owner_person_id=null, we can get the latest value from p_staffing_owner_name
IF p_staffing_owner_name IS NOT NULL THEN
l_new_value := p_staffing_owner_name;
pa_resource_utils.get_person_name (p_person_id => l_updated_asmt_rec.staffing_owner_person_id,
x_person_name => l_new_value,
x_return_status => l_return_Status);
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
IF (NVL(l_asmt_history_rec.fcst_tp_amount_type,-1) <> NVL(l_updated_asmt_rec.fcst_tp_amount_type,-1)) THEN
l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('TP_AMOUNT_TYPE', l_updated_asmt_rec.fcst_tp_amount_type);
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
IF (NVL(l_asmt_history_rec.work_type_id,-1) <> NVL(l_updated_asmt_rec.work_type_id,-1)) THEN
FOR c2 IN get_work_type_names(l_asmt_history_rec.work_type_id, l_updated_asmt_rec.work_type_id) LOOP
IF c2.work_type_id = l_updated_asmt_rec.work_type_id THEN
l_new_work_type := c2.name;
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_new_work_type, l_old_work_type);
IF (NVL(l_asmt_history_rec.tp_currency_override,-1) <> NVL(l_updated_asmt_rec.tp_currency_override,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_currency_override,
l_asmt_history_rec.tp_currency_override);
IF (NVL(l_asmt_history_rec.tp_rate_override,-1) <> NVL(l_updated_asmt_rec.tp_rate_override,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_rate_override),
to_char(l_asmt_history_rec.tp_rate_override));
IF (NVL(l_asmt_history_rec.tp_calc_base_code_override,-1) <> NVL(l_updated_asmt_rec.tp_calc_base_code_override,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_calc_base_code_override,
l_asmt_history_rec.tp_calc_base_code_override);
IF (NVL(l_asmt_history_rec.tp_percent_applied_override,-1) <> NVL(l_updated_asmt_rec.tp_percent_applied_override,-1)) THEN
INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_percent_applied_override),
to_char(l_asmt_history_rec.tp_percent_applied_override));
PA_SCHEDULE_PVT.update_asgmt_changed_items_tab (
p_assignment_id => p_assignment_id
,p_populate_mode => p_populate_mode
,p_change_id => l_change_id
,p_exception_type_code => p_exception_type_code
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_requirement_status_code => p_requirement_status_code
,p_assignment_status_code => p_assignment_status_code
,p_start_date_tbl => p_start_date_tbl
,p_end_date_tbl => p_end_date_tbl
,p_monday_hours_tbl => p_monday_hours_tbl
,p_tuesday_hours_tbl => p_tuesday_hours_tbl
,p_wednesday_hours_tbl => p_wednesday_hours_tbl
,p_thursday_hours_tbl => p_thursday_hours_tbl
,p_friday_hours_tbl => p_friday_hours_tbl
,p_saturday_hours_tbl => p_saturday_hours_tbl
,p_sunday_hours_tbl => p_sunday_hours_tbl
,p_non_working_day_flag => p_non_working_day_flag
,p_change_hours_type_code => p_change_hours_type_code
,p_hrs_per_day => p_hrs_per_day
,p_calendar_percent => p_calendar_percent
,p_change_calendar_type_code => p_change_calendar_type_code
,p_change_calendar_name => p_change_calendar_name
,p_change_calendar_id => p_change_calendar_id
,p_duration_shift_type_code => p_duration_shift_type_code
,p_duration_shift_unit_code => p_duration_shift_unit_code
,p_number_of_shift => p_number_of_shift
,x_return_status => l_return_status );
SELECT ROWID, project_id, calendar_id, start_date, end_date, source_assignment_id, project_party_id, resource_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id
AND record_version_number = p_record_version_number;
SELECT assignment_type, start_date
FROM pa_project_assignments
WHERE assignment_id = l_source_assignment_id;
SELECT record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT action_set_id, record_version_number
FROM pa_action_sets
WHERE object_id = p_assignment_id
AND object_type = 'OPEN_ASSIGNMENT'
AND action_set_type_code = 'ADVERTISEMENT'
AND status_code <> 'DELETED';
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
-- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
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 pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
-- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
AND ra.project_assignment_id = p_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
-- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
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 pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
-- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
AND ra.project_assignment_id = p_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 = p_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;
,x_msg => 'calling PA_ACTION_SETS_PUB.Update_Action_Set'
,x_log_level => 5);
PA_ACTION_SETS_PUB.Update_Action_Set(
p_action_set_id => l_action_set_id
,p_object_id => p_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 => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
PA_PROJECT_PARTIES_PVT.Delete_Project_Party(
p_commit => 'F',
p_validate_only => 'F',
p_project_party_id => l_project_party_id,
p_calling_module => 'ASSIGNMENT',
p_record_version_number => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE pa_project_assignments
SET source_assignment_id = p_assignment_id
WHERE assignment_id = l_new_assignment_id;
PA_CANDIDATE_PUB.Delete_Candidates(p_assignment_id => l_source_assignment_id,
p_status_code => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
update pa_project_assignments set no_of_active_candidates=0
where assignment_id = l_source_assignment_id;
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(
P_ASSIGNMENT_ID => p_assignment_id
,P_ACTION_CODE => PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action
,P_RECORD_VERSION_NUMBER => NULL
,X_APPRVL_STATUS_CODE => l_apprvl_status_code
,X_CHANGE_ID => l_change_id
,X_RECORD_VERSION_NUMBER => l_record_version_number
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data);
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
);
SELECT item_key, item_type
FROM pa_wf_processes
WHERE item_key = (
SELECT max(item_key)
FROM pa_wf_processes
WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
AND entity_key1 = to_char(p_project_id)
AND entity_key2 = to_char(p_assignment_id)
)
and item_type = 'PAWFAAP';
SELECT fu.user_id
FROM pa_wf_ntf_performers pwnp,
fnd_user fu
WHERE pwnp.object_id1 = p_assignment_id
AND pwnp.object_id2 = p_project_id
AND pwnp.item_type = l_item_type
AND pwnp.item_key = l_item_key
AND pwnp.current_approver_flag = 'Y'
AND pwnp.user_name = fu.user_name;
SELECT nvl(mass_wf_in_progress_flag,'N')
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT fu.user_id
FROM pa_wf_ntf_performers pwnp,
fnd_user fu
WHERE wf_type_code = 'MASS_ASSIGNMENT_APPROVAL'
AND object_id1 = p_assignment_id
AND object_id2 = -1
AND fu.user_name = pwnp.user_name
AND (group_id, routing_order) in (SELECT max(group_id), min(routing_order)
FROM pa_wf_ntf_performers
WHERE wf_type_code = 'MASS_ASSIGNMENT_APPROVAL'
AND object_id1 = p_assignment_id
AND object_id2 = -1);
SELECT asgn.start_date, asgn.end_date, asgn.project_id, res.person_id
FROM pa_project_assignments asgn,
pa_resources_denorm res
WHERE assignment_id = p_assignment_id
AND res.resource_id = asgn.resource_id
AND rownum=1;
SELECT project_id
INTO l_project_id
FROM pa_projects_all
WHERE segment1 = p_project_number;
SELECT NVL(pt.administrative_flag,'N') admin_flag
INTO l_admin_project
FROM pa_projects_all pap,
pa_project_types_all pt
WHERE pap.project_id = l_project_id
AND pt.project_type = pap.project_type
-- AND nvl(pap.org_id, -99) = nvl(pt.org_id, -99); /* Added nvl for bug#2467666 */ -R12: Bug 4633092
,p_approver1_id_tbl => l_approver1_id_tbl --The local updated table is passed
,p_approver1_name_tbl => p_approver1_name_tbl
,p_approver2_id_tbl => l_approver2_id_tbl --The local updated table is passed
,p_approver2_name_tbl => p_approver2_name_tbl
,p_appr_over_auth_flag => p_appr_over_auth_flag
,p_note_to_all_approvers => p_note_to_all_approvers
,p_competence_id_tbl => p_competence_id_tbl
,p_competence_name_tbl => p_competence_name_tbl
,p_competence_alias_tbl => p_competence_alias_tbl
,p_rating_level_id_tbl => p_rating_level_id_tbl
,p_mandatory_flag_tbl => p_mandatory_flag_tbl
,p_resolve_con_action_code => p_resolve_con_action_code
,x_return_status => l_return_status );
,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,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_return_status VARCHAR2(1);
SELECT resource_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT ps.enable_wf_flag
FROM pa_project_statuses ps
WHERE ps.project_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
SELECT distinct user_name
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
AND ntf.routing_order = 1;
SELECT pap.project_id project_id,
pap.name name,
pap.segment1 segment1,
pap.carrying_out_organization_id carrying_out_organization_id,
pap.location_id,
hr.name organization_name,
NVL(pt.administrative_flag,'N') admin_flag
FROM pa_projects_all pap,
hr_all_organization_units_tl hr, -- Bug 4358492
pa_project_types_all pt
WHERE pap.project_id = l_project_id
AND pap.carrying_out_organization_id = hr.organization_id
AND pt.project_type = pap.project_type
AND pap.org_id = pt.org_id -- Bug 4358492
AND userenv('LANG') = hr.language; -- Bug 4358492
SELECT employee_id,
user_name
FROM fnd_user
WHERE user_id = p_submitter_user_id;
SELECT PA_WF_NTF_PERFORMERS_S.nextval
INTO l_group_id
FROM dual;
SELECT apprvl_status_code
INTO l_asgn_approval_status
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id_tbl(i);
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
( p_assignment_id => l_assignment_id_tbl(i)
,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action
,p_note_to_approver => p_note_to_all_approvers
,p_record_version_number => p_record_version_number
,x_apprvl_status_code => l_apprvl_status_code
,x_change_id => l_change_id
,x_record_version_number => l_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
log_message('Error in Update approval status 1');
INSERT INTO pa_wf_ntf_performers(
WF_TYPE_CODE
,ITEM_TYPE
,ITEM_KEY
,OBJECT_ID1
,OBJECT_ID2
,GROUP_ID
,USER_NAME
,USER_TYPE
,ROUTING_ORDER
,APPROVAL_STATUS)
VALUES ('MASS_ASSIGNMENT_APPROVAL'
,'-1'
,'-1'
,l_assignment_id_tbl(i)
,-1
,l_group_id
,l_submitter_user_name
,'APPROVER'
,1
,l_asgn_approval_status
);
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
( p_assignment_id => l_assignment_id_tbl(i)
,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action
,p_note_to_approver => p_note_to_all_approvers
,p_record_version_number => p_record_version_number
,x_apprvl_status_code => l_apprvl_status_code
,x_change_id => l_change_id
,x_record_version_number => l_record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
log_message('Error in Update approval status 2');
l_approvers_list_tbl.DELETE ;
INSERT INTO pa_wf_ntf_performers(
WF_TYPE_CODE
,ITEM_TYPE
,ITEM_KEY
,OBJECT_ID1
,OBJECT_ID2
,GROUP_ID
,USER_NAME
,USER_TYPE
,ROUTING_ORDER
,APPROVAL_STATUS)
VALUES ('MASS_ASSIGNMENT_APPROVAL'
,'-1'
,'-1'
,l_assignment_id_tbl(i)
,-1
,l_group_id
,l_out_approvers_list_tbl(k).user_name
,'APPROVER'
,l_routing_order + 1
,l_asgn_approval_status
);
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id = l_assignment_id_tbl(i);
update the approver_group_id in PA_WF_NTF_PERFORMERS for these assignments with a
new sequence value. Store this value in l_approver_group_id
The l_approver_group_id is the approver transaction id which is used
in grouping and sending approval required notifications for the next (second) set of approvers.
Approver group id is used in grouping the approvers in the next routing order
*/
-----------------------------------------------------------------------------------------
FOR rec IN distinct_approvers LOOP
SELECT PA_WF_NTF_PERFORMERS_S.nextval
INTO l_approver_group_id
FROM dual;
UPDATE pa_wf_ntf_performers
SET approver_group_id = l_approver_group_id
WHERE group_id = l_group_id
AND user_name = rec.user_name
AND routing_order = 1;
,p_update_info_doc => p_update_info_doc
,p_project_name => l_projects_rec.name
,p_project_number => l_projects_rec.segment1
,p_project_manager => l_project_manager_name
,p_project_org => l_projects_rec.organization_name
,p_project_cus => l_customer_name
,p_submitter_user_name => l_submitter_user_name
,p_conflict_group_id => p_conflict_group_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT count(*)
INTO l_num_apr_asgns
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.routing_order = 1
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
SELECT count(*)
INTO l_num_rej_asgns
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.routing_order = 1
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_rejected ;
SELECT count( distinct ( attribute2))
INTO l_error_count
FROM PA_REPORTING_EXCEPTIONS
WHERE context = PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1
AND sub_context = 'MASS_APPROVAL'
AND source_identifier1 = 'PAWFAAP'
AND source_identifier2 = l_group_id;
SELECT ntf.object_id1
BULK COLLECT INTO l_appr_asgmt_id_tbl
FROM pa_wf_ntf_performers ntf,
pa_project_assignments asgn
WHERE ntf.group_id = l_group_id
AND ntf.routing_order = 1
AND ntf.object_id1 = asgn.assignment_id
AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
,p_update_info_doc => p_update_info_doc
,p_num_apr_asgns => l_num_apr_asgns
,p_num_rej_asgns => l_num_rej_asgns
,p_project_name => l_projects_rec.name
,p_project_number => l_projects_rec.segment1
,p_project_manager => l_project_manager_name
,p_project_org => l_projects_rec.organization_name
,p_project_cus => l_customer_name
,p_submitter_user_name => l_submitter_user_name
,p_conflict_group_id => p_conflict_group_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
,p_update_info_doc => p_update_info_doc
,p_num_apr_asgns => l_num_apr_asgns
,p_num_rej_asgns => l_num_rej_asgns
,p_project_name => l_projects_rec.name
,p_project_number => l_projects_rec.segment1
,p_project_manager => l_project_manager_name
,p_project_org => l_projects_rec.organization_name
,p_project_cus => l_customer_name
,p_submitter_user_name => l_submitter_user_name
,p_assignment_id => p_assignment_id_tbl(1)
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
l_update_info_doc VARCHAR2(32767);
UPDATE pa_wf_ntf_performers
SET object_id2 = 100
where group_id = p_group_id
and routing_order = p_routing_order
and approver_group_id = p_approver_group_id;
SELECT pa_prm_wf_item_key_s.nextval
INTO l_itemkey
FROM dual;
l_update_info_doc := wf_engine.getItemAttrDocument
( itemtype => 'PAWFAAP'
, itemkey => p_item_key
, aname => 'UPDATED_INFO_DOC' );
, aname => 'UPDATED_INFO_DOC'
, documentid => l_update_info_doc );
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
,p_item_type => l_wf_item_type
,p_item_key => l_itemkey
,p_entity_key1 => to_char(p_project_id)
,p_entity_key2 => to_char(p_group_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack );
SELECT start_date
INTO l_assignment_start_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id_tbl(i);
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id = p_assignment_id_tbl(i);
SELECT resource_id
INTO l_resource_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id_tbl(i);