[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM pa_assignments_history pah,
pa_project_assignments ppa
WHERE pah.assignment_id = p_assignment_id
OR ( ppa.assignment_id = p_assignment_id
AND ppa.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved);
SELECT 'X'
FROM pa_assignments_history pah
WHERE pah.assignment_id = p_assignment_id
UNION ALL
SELECT 'X'
FROM pa_project_assignments ppa
WHERE ppa.assignment_id = p_assignment_id
AND ppa.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved;
SELECT change_id
FROM pa_assignments_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
PROCEDURE Update_Approval_Status
(
p_assignment_id IN pa_project_assignments.assignment_id%TYPE
,p_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_record_version_number IN pa_project_assignments.record_version_number%TYPE
,p_note_to_approver IN VARCHAR2 := FND_API.G_MISS_CHAR
,x_apprvl_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
,x_record_version_number OUT NOCOPY pa_project_assignments.record_version_number%TYPE --File.Sql.39 bug 4440895
,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_record_version_number NUMBER;
SELECT apprvl_status_code, record_version_number
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.begin'
,x_msg => 'Beginning of Update_Approval_Status.'
,x_log_level => 5);
AND p_action_code =PA_ASSIGNMENT_APPROVAL_PUB.g_update_action THEN
--Log Message
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.insert_history'
,x_msg => 'Inserting record into assignment history table.'
,x_log_level => 5);
PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History ( p_assignment_id => p_assignment_id
,x_change_id => x_change_id
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.get_next_stus'
,x_msg => 'Get next assignment approval status.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row ( p_assignment_id => p_assignment_id
,p_record_version_number => p_record_version_number
,p_apprvl_status_code => x_apprvl_status_code
,p_note_to_approver => p_note_to_approver
,x_return_status => l_return_status );
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.schedule_success'
,x_msg => 'Calling schedule success method.'
,x_log_level => 5);
PA_SCHEDULE_PVT.UPDATE_SCH_WF_SUCCESS ( p_assignment_id => p_assignment_id
,p_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);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.schedule_failure'
,x_msg => 'Calling schedule failure method.'
,x_log_level => 5);
PA_SCHEDULE_PVT.UPDATE_SCH_WF_FAILURE ( p_assignment_id => p_assignment_id
,p_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);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.update_status'
,x_msg => 'update current approval status.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row ( p_assignment_id => p_assignment_id
,p_record_version_number => l_record_version_number
,p_apprvl_status_code => x_apprvl_status_code
,p_note_to_approver => p_note_to_approver
,x_return_status => l_return_status );
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Approval_Status;
PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action, PA_ASSIGNMENT_APPROVAL_PUB.g_update_action,
PA_ASSIGNMENT_APPROVAL_PUB.g_revert_action, PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action,
PA_MASS_ASGMT_TRX.g_save_and_submit) THEN /*SAVE_AND_SUBMIT added for bug 6625421*/
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_UNEXP_APPRVL_ACTION');
ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_update_action THEN
IF p_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_working THEN
x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_working;
PROCEDURE Insert_Into_Assignment_History
(
p_assignment_id IN pa_project_assignments.assignment_id%TYPE
,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
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';
PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.begin'
,x_msg => 'Beginning of Insert_Into_Assignment_History'
,x_log_level => 5);
SELECT
assignment_id
,assignment_name
,assignment_type
,multiple_status_flag
,record_version_number
,apprvl_status_code
,status_code
,staffing_priority_code
,staffing_owner_person_id
,project_id
,project_role_id
,resource_id
,project_party_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
,pending_approval_flag
,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
,transfer_price_rate -- Added for bug 3051110
,transfer_pr_rate_curr
,discount_percentage -- Added for bug 3041583
,rate_disc_reason_code -- Added for bug 3041583
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_assignment_rec.apprvl_status_code
,l_assignment_rec.status_code
,l_assignment_rec.staffing_priority_code
,l_assignment_rec.staffing_owner_person_id
,l_assignment_rec.project_id
,l_assignment_rec.project_role_id
,l_assignment_rec.resource_id
,l_assignment_rec.project_party_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_pending_approval_flag
,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
,l_assignment_rec.transfer_price_rate -- Added for bug 3051110
,l_assignment_rec.transfer_pr_rate_curr
,l_assignment_rec.discount_percentage -- Added for bug 3041583
,l_assignment_rec.rate_disc_reason_code -- Added for bug 3041583
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.last_approved_flag'
,x_msg => 'update previously last approved record flag to N'
,x_log_level => 5);
UPDATE pa_assignments_history
SET last_approved_flag = 'N'
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.insert_row'
,x_msg => 'insert last approved record into assignment history table.'
,x_log_level => 5);
PA_ASSIGNMENTS_HISTORY_PKG.Insert_Row
(p_assignment_id => l_assignment_rec.assignment_id
,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_record_version_number => l_assignment_rec.record_version_number
,p_apprvl_status_code => l_assignment_rec.apprvl_status_code
,p_status_code => l_assignment_rec.status_code
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id
,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_project_subteam_id => l_project_subteam_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 => l_pending_approval_flag
,p_last_approved_flag => 'Y'
,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
,p_transfer_price_rate => l_assignment_rec.transfer_price_rate -- Added for bug 3051110
,p_transfer_pr_rate_curr => l_assignment_rec.transfer_pr_rate_curr
,p_discount_percentage => l_assignment_rec.discount_percentage -- Added for bug 3041583
,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- Added for bug 3041583
,x_assignment_row_id => l_assignment_row_id
,x_change_id => l_change_id
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.schedule_history'
,x_msg => 'Updating Schedule history table.'
,x_log_level => 5);
PA_SCHEDULE_PVT.UPDATE_HISTORY_TABLE ( P_ASSIGNMENT_ID => l_assignment_rec.assignment_id
,P_CHANGE_ID => l_change_id
,X_RETURN_STATUS => l_return_status
,X_MSG_COUNT => l_msg_count
,X_MSG_DATA => l_msg_data);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Insert_Into_Assignment_History;
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';
PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History');
SELECT meaning
INTO l_meaning
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT assignment_id,
assignment_name,
staffing_priority_code,
description,
extension_possible,
additional_information,
work_type_id,
expense_owner,
expense_limit,
fcst_tp_amount_type,
expenditure_type_class,
expenditure_type,
location_id,
tp_currency_override,
tp_rate_override,
tp_calc_base_code_override,
tp_percent_applied_override,
staffing_owner_person_id
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;
SELECT assignment_id,
assignment_name,
staffing_priority_code,
description,
extension_possible,
additional_information,
work_type_id,
expense_owner,
expense_limit,
fcst_tp_amount_type,
expenditure_type_class,
expenditure_type,
location_id,
tp_currency_override,
tp_rate_override,
tp_calc_base_code_override,
tp_percent_applied_override,
staffing_owner_person_id
FROM pa_assignments_history
WHERE assignment_id = p_assignment_id
AND last_approved_flag = 'Y';
SELECT apprvl_status_code
FROM pa_project_assignments
WHERE assignment_id = p_assignment_id;