[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT plks.meaning
FROM pa_lookups plks
WHERE plks.lookup_type = 'TP_AMOUNT_TYPE'
AND plks.lookup_code = p_tp_amt_type;
SELECT pa_mass_asgmt_trx_wf_s.nextval
INTO l_item_key
FROM dual;
PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(
p_assignment_id => p_assignment_id_tbl(i)
,p_action_code => p_action
,p_note_to_approver => p_note_to_all_approvers
,p_record_version_number=> 1
,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);
SELECT project_id INTO l_project_id
FROM pa_projects_all
WHERE segment1 = p_project_number;
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => p_mode
,p_item_type => l_item_type
,p_item_key => to_char(l_item_key)
,p_entity_key1 => to_char(l_project_id)
,p_description => p_mode
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack);
INSERT INTO pa_wf_process_details(wf_type_code,
item_type,
item_key,
object_id1,
process_status_code,
source_attribute1,
source_attribute2,
source_attribute3,
source_attribute4,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(p_mode,
l_item_type,
to_char(l_item_key),
l_object_id_tbl(i),
'P',
decode(p_action, G_SAVE, NULL, to_char(l_approver1_id_tbl(i))),
decode(p_action, G_SAVE, NULL, l_approver1_name_tbl(i)),
decode(p_action, G_SAVE, NULL, to_char(l_approver2_id_tbl(i))),
decode(p_action, G_SAVE, NULL, l_approver2_name_tbl(i)),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
IF p_mode = G_MASS_ASGMT or p_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
--this attribute is not dynamically created because it will be displayed
--in the notification so it must be created as a workflow attribute at
--design time.
l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_NAME';
ELSIF p_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'WORK_TYPE_ID';
ELSIF p_mode = G_MASS_UPDATE_SCHEDULE THEN
l_text_attr_name_tbl(l_text_attr_name_tbl.COUNT+1) := 'EXCEPTION_TYPE_CODE';
ELSIF p_mode = G_MASS_UPDATE_COMPETENCIES THEN
IF p_competence_id_tbl.COUNT > 0 THEN
FOR i IN p_competence_id_tbl.FIRST .. p_competence_id_tbl.LAST LOOP
l_num_attr_name_tbl(l_num_attr_name_tbl.COUNT+1) := 'COMPETENCE_ID'||i;
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'Y'
WHERE assignment_id = p_assignment_id_tbl(i);
SELECT object_id1 BULK COLLECT INTO l_object_id_tbl
FROM pa_wf_process_details
WHERE item_type = p_item_type
AND item_key = p_item_key
AND process_status_code = 'P';
SELECT sum(decode(process_status_code, 'S', 1,0)),
sum(decode(process_status_code, 'A', 1, 'E', 1, 0))
INTO l_success_assignments,
l_failure_assignments
FROM pa_wf_process_details
WHERE item_type = p_item_type
AND item_key = p_item_key;
SELECT assignment_id INTO l_any_success_assignment_id
FROM pa_mass_txn_asgmt_success_v
WHERE item_type = p_item_type
AND item_key= p_item_key
AND ROWNUM = 1;
p_result := 'UPDATE_ASGMT_FAIL_OC';
p_result := 'UPDATE_ASGMT_FAILURE';
p_result := 'UPDATE_ASGMT_SUCCESS_OC';
p_result := 'UPDATE_ASGMT_SUCCESS';
SELECT project_id
INTO l_project_id
FROM pa_project_assignments
WHERE assignment_id = l_assignment_id_tbl(1);
SELECT proj.name,
proj.segment1,
pa_resource_utils.get_organization_name(proj.carrying_out_organization_id) CARRYING_OUT_ORGANIZATION_NAME ,
pa_project_parties_utils.get_project_manager_name(proj.project_id) person_name,
pa_projects_maint_utils.get_primary_customer_name(proj.project_id) customer_name
INTO l_project_name,
l_project_number,
l_project_organization,
l_project_manager,
l_project_customer
FROM pa_projects_all proj
WHERE project_id = l_project_id;
l_calling_page := 'MassAsgmtUpdateSubmitNotif';
l_calling_page := 'MassAsgmtUpdateFYINotif';
SELECT message_text INTO l_view_conf_action_text
FROM fnd_new_messages
WHERE message_name = l_message_name
and application_id = 275
and language_code = userenv('LANG');
aname => 'UPDATED_INFORMATION_DOCUMENT',
documentid => 'plsql:PA_MASS_ASGMT_TRX.Display_Updated_Attributes/' ||l_document);
SELECT lkup.meaning fcst_tp_amount_type_name
FROM pa_project_assignments asgn, pa_lookups lkup
WHERE lkup.lookup_type(+) = 'TP_AMOUNT_TYPE'
AND asgn.fcst_tp_amount_type = lkup.lookup_code(+)
AND asgn.assignment_id = p_asg_id;
IF l_mode = G_MASS_ASGMT OR l_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
l_assignment_name := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
itemkey => p_item_key,
aname => 'ASSIGNMENT_NAME');
SELECT meaning INTO l_staffing_priority_name
FROM pa_lookups
WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
AND lookup_code = l_staffing_priority_code;
SELECT name INTO l_project_subteam_name
FROM pa_project_subteams
WHERE project_subteam_id = l_project_subteam_id;
SELECT meaning INTO l_extension_possible_meaning
FROM fnd_lookups
WHERE lookup_type='YES_NO'
AND lookup_code = l_extension_possible;
SELECT meaning INTO l_expense_owner_meaning
FROM pa_lookups
WHERE lookup_type = 'EXPENSE_OWNER_TYPE'
AND lookup_code = l_expense_owner;
SELECT meaning INTO L_ENABLE_AUTO_CAND_NOM_MEANING
FROM fnd_lookups
WHERE lookup_type='YES_NO'
AND lookup_code = L_ENABLE_AUTO_CAND_NOM_FLAG;
ELSIF l_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
l_work_type_id := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
itemkey => p_item_key,
aname => 'WORK_TYPE_ID');
SELECT name INTO l_work_type_name
FROM pa_work_types_vl
WHERE work_type_id = l_work_type_id;
SELECT meaning INTO l_fcst_tp_amount_type_meaning
FROM pa_lookups
WHERE lookup_type = 'TP_AMOUNT_TYPE'
AND lookup_code = l_fcst_tp_amount_type;
ELSIF l_mode = G_MASS_UPDATE_SCHEDULE THEN
l_exception_type_code := WF_ENGINE.GetItemAttrText(itemtype => p_item_type,
itemkey => p_item_key,
aname => 'EXCEPTION_TYPE_CODE');
SELECT project_status_name INTO l_project_status_name
FROM pa_project_statuses
WHERE project_status_code = l_change_rqmt_status_code
AND status_type='OPEN_ASGMT' ;
SELECT project_status_name INTO l_project_status_name
FROM pa_project_statuses
WHERE project_status_code = l_change_asgmt_status_code
AND status_type='STAFFED_ASGMT' ;
END IF; -- End if for muti-period work pattern updates
ELSIF l_mode = G_MASS_UPDATE_COMPETENCIES THEN
l_number_of_competencies := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
itemkey => p_item_key,
aname => 'NUMBER_OF_COMPETENCIES');
SELECT step_value INTO l_rating_level
FROM per_rating_levels_v
WHERE rating_level_id = l_rating_level_id_tbl(i);
SELECT meaning INTO l_mandatory_flag_meaning
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = l_mandatory_flag_tbl(i);
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'Y'
WHERE assignment_id = l_success_assignment_id_tbl(1);
ELSIF l_mode = G_MASS_UPDATE_ASGMT_BASIC_INFO THEN
PA_ASSIGNMENTS_PUB.Mass_Update_Assignments
( p_update_mode => l_mode,
p_assignment_id_tbl => l_single_obj_id_tbl,
p_assignment_type => l_assignment_type ,
p_assignment_name => l_assignment_name ,
p_staffing_priority_code => l_staffing_priority_code ,
p_project_id => l_project_id ,
p_project_subteam_id => l_project_subteam_id ,
p_append_description_flag => l_append_description_flag ,
p_description => l_description ,
p_extension_possible => l_extension_possible ,
p_min_resource_job_level => l_min_resource_job_level ,
p_max_resource_job_level => l_max_resource_job_level ,
p_append_information_flag => l_append_information_flag ,
p_additional_information => l_additional_information ,
p_location_id => l_location_id ,
p_expense_owner => l_expense_owner ,
p_expense_limit => l_expense_limit ,
p_expense_limit_currency_code => l_expense_limit_currency_code ,
p_project_subteam_name => l_project_subteam_name ,
p_staffing_priority_name => l_staffing_priority_name ,
p_location_city => l_location_city ,
p_location_region => l_location_region ,
p_location_country_name => l_location_country_name ,
p_location_country_code => l_location_country_code ,
p_comp_match_weighting => l_comp_match_weighting,
p_avail_match_weighting => l_avail_match_weighting,
p_job_level_match_weighting => l_job_level_match_weighting,
p_search_min_availability => l_search_min_availability,
p_search_country_code => l_search_country_code,
p_search_country_name => l_search_country_name,
p_search_exp_org_struct_ver_id => l_search_exp_org_struct_ver_id,
p_search_exp_org_hier_name => l_search_exp_org_hier_name,
p_search_exp_start_org_id => l_search_exp_start_org_id,
p_search_exp_start_org_name => l_search_exp_start_org_name,
p_search_min_candidate_score => l_search_min_candidate_score,
p_enable_auto_cand_nom_flag => l_enable_auto_cand_nom_flag,
p_staffing_owner_person_id => l_staffing_owner_person_id,
p_staffing_owner_name => l_staffing_owner_name,
p_commit => FND_API.G_TRUE ,
p_validate_only => FND_API.G_FALSE ,
x_success_assignment_id_tbl => l_success_assignment_id_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ELSIF l_mode = G_MASS_UPDATE_COMPETENCIES THEN
PA_COMPETENCE_PUB.Mass_Process_Competences
(p_project_id => l_project_id ,
p_assignment_tbl => l_single_obj_id_tbl ,
p_competence_id_tbl => l_competence_id_tbl ,
p_competence_name_tbl => l_competence_name_tbl,
p_competence_alias_tbl => l_competence_alias_tbl,
p_rating_level_id_tbl => l_rating_level_id_tbl,
p_mandatory_flag_tbl => l_mandatory_flag_tbl,
p_commit => FND_API.G_TRUE ,
p_validate_only => FND_API.G_FALSE ,
x_success_assignment_id_tbl => l_success_assignment_id_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ELSIF l_mode = G_MASS_UPDATE_FORECAST_ITEMS THEN
PA_ASSIGNMENTS_PUB.Mass_Update_Assignments
( p_update_mode => l_mode,
p_assignment_id_tbl => l_single_obj_id_tbl ,
p_project_id => l_project_id,
p_fcst_tp_amount_type => l_fcst_tp_amount_type ,
p_fcst_job_id => l_fcst_job_id ,
p_fcst_job_group_id => l_fcst_job_group_id ,
p_expenditure_org_id => l_expenditure_org_id ,
p_expenditure_organization_id => l_expenditure_organization_id ,
p_expenditure_type_class => l_expenditure_type_class ,
p_expenditure_type => l_expenditure_type ,
p_work_type_name => l_work_type_name ,
p_work_type_id => l_work_type_id ,
p_fcst_job_name => l_fcst_job_name ,
p_fcst_job_group_name => l_fcst_job_group_name ,
p_expenditure_org_name => l_expenditure_org_name ,
p_exp_organization_name => l_exp_organization_name ,
p_commit => FND_API.G_TRUE ,
p_validate_only => FND_API.G_FALSE ,
x_success_assignment_id_tbl => l_success_assignment_id_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ELSIF l_mode = G_MASS_UPDATE_SCHEDULE THEN
PA_SCHEDULE_PUB.mass_update_schedule
( p_project_id => l_project_id ,
p_exception_type_code => l_exception_type_code ,
p_assignment_id_array => l_single_obj_id_tbl ,
p_change_start_date => l_change_start_date ,
p_change_end_date => l_change_end_date ,
p_change_rqmt_status_code => l_change_rqmt_status_code ,
p_change_asgmt_status_code => l_change_asgmt_status_code ,
p_change_start_date_tbl => l_change_start_date_tbl,
p_change_end_date_tbl => l_change_end_date_tbl,
p_monday_hours_tbl => l_monday_hours_tbl ,
p_tuesday_hours_tbl => l_tuesday_hours_tbl ,
p_wednesday_hours_tbl => l_wednesday_hours_tbl ,
p_thursday_hours_tbl => l_thursday_hours_tbl ,
p_friday_hours_tbl => l_friday_hours_tbl ,
p_saturday_hours_tbl => l_saturday_hours_tbl ,
p_sunday_hours_tbl => l_sunday_hours_tbl ,
p_non_working_day_flag => l_non_working_day_flag ,
p_change_hours_type_code => l_change_hours_type_code ,
p_hrs_per_day => l_hrs_per_day ,
p_calendar_percent => l_calendar_percent ,
p_change_calendar_type_code => l_change_calendar_type_code ,
p_change_calendar_name => l_change_calendar_name ,
p_change_calendar_id => l_change_calendar_id ,
p_duration_shift_type_code => l_duration_shift_type_code ,
p_duration_shift_unit_code => l_duration_shift_unit_code ,
p_number_of_shift => l_num_of_shift ,
p_commit => FND_API.G_TRUE ,
x_success_assignment_id_tbl => l_success_assignment_id_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT assignment_type INTO l_assignment_type
FROM pa_project_assignments
WHERE assignment_id = l_single_obj_id_tbl(1);
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id = l_single_obj_id_tbl(1);
UPDATE pa_wf_process_details
SET process_status_code = decode(l_success_assignment_id_tbl(1), NULL, 'E', 'S'),
object_id2 = decode(l_mode, G_MASS_ASGMT, l_success_assignment_id_tbl(1), NULL)
WHERE item_type = p_item_type
AND item_key = p_item_key
AND object_id1 = l_single_obj_id_tbl(1);
UPDATE pa_wf_process_details
SET process_status_code = 'S'
WHERE item_type = p_item_type
AND item_key = p_item_key
AND object_id1 = l_object_id_tbl(i);
select project_id into l_project_id
from pa_projects_all
where segment1 = l_project_number;
SELECT asgmt.assignment_id,
wf.source_attribute1,
wf.source_attribute2,
wf.source_attribute3,
wf.source_attribute4
BULK COLLECT INTO l_assignment_id_tbl,
l_approver1_id_tbl,
l_approver1_name_tbl,
l_approver2_id_tbl,
l_approver2_name_tbl
FROM pa_wf_process_details wf,
pa_project_assignments asgmt
WHERE wf.item_type = p_item_type
AND wf.item_key = p_item_key
AND wf.process_status_code = 'S'
AND decode(l_mode, G_MASS_ASGMT, object_id2, object_id1) = asgmt.assignment_id
AND asgmt.assignment_type <> 'OPEN_ASSIGNMENT'
AND asgmt.assignment_id NOT IN (
SELECT distinct assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = l_conflict_group_id
AND resolve_conflicts_action_code IN ('CANCEL_TXN_ITEM', 'REVERT_TXN_ITEM'))
;
aname => ' UPDATED_INFORMATION_DOCUMENT',
ignore_notfound => TRUE);
p_update_info_doc => l_document,
p_submitter_user_id => l_submitter_user_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id IN
(SELECT distinct assignment_id
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = l_conflict_group_id
AND resolve_conflicts_action_code IN ('CANCEL_TXN_ITEM', 'REVERT_TXN_ITEM'));
SELECT resolve_conflicts_action_code
FROM pa_assignment_conflict_hist
WHERE conflict_group_id = p_conflict_group_id
AND assignment_id = p_assignment_id;
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id IN
(SELECT decode(l_mode, G_MASS_ASGMT, object_id2, object_id1)
FROM pa_wf_process_details
WHERE item_type = p_item_type
AND item_key = p_item_key);
UPDATE pa_wf_process_details
SET process_status_code = 'A' --'A' for aborted
WHERE item_type = l_error_item_type
AND item_key = l_error_item_key
AND process_status_code = 'P'; --'P' for pending
UPDATE pa_project_assignments
SET mass_wf_in_progress_flag = 'N'
WHERE assignment_id IN
(SELECT decode(l_mode, G_MASS_ASGMT, object_id2, object_id1)
FROM pa_wf_process_details
WHERE item_type = l_error_item_type
AND item_key = l_error_item_key
AND process_status_code = 'A');
SELECT object_id1 BULK COLLECT INTO l_object_id1_tbl
FROM pa_wf_process_details
WHERE item_type = l_error_item_type
AND item_key = l_error_item_key
AND process_status_code = 'A';
PROCEDURE Display_Updated_Attributes(document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT NOCOPY VARCHAR2, --4537865
document_type IN OUT NOCOPY VARCHAR2) --4537865
IS
--4537865
l_original_document_type VARCHAR2(200);
WF_CORE.CONTEXT('pa_mass_asgmt_trx','Display_Updated_Attributes' );
SELECT meaning INTO l_meaning
FROM pa_lookups
WHERE lookup_type = 'MASS_ASSIGNMENT_UPDATE_ATTR'
AND lookup_code = p_lookup_code;