[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c_task_name is select element_name from PA_FIN_LATEST_PUB_TASKS_V
where proj_element_id = p_object_id
and project_id = p_project_id;
select impact_Type_code
from pa_ci_impact_type_usage
where ci_type_id = p_ci_type_id;
select impact_type_code
from pa_ci_impacts
where ci_id = p_ci_id;
SELECT impact_budget_type_code
from pa_ci_types_b where ci_type_id=p_ci_type_id;
select 'Y'
into temp
from pa_control_items ci,pa_ci_types_b ci_type1 ,pa_ci_types_b ci_type2
where ci.ci_type_id = ci_type2.ci_type_id
and ci_type2.impact_budget_type_code=impact_bud_type_code
and ci.ci_id = p_ci_id_2
and ci_type1.ci_type_id = p_ci_type_id_1
and ci_type1.supp_cost_reg_flag = ci_type2.supp_cost_reg_flag
and ci_type1.dir_cost_reg_flag = ci_type2.dir_cost_reg_flag ;
select 'Y'
into temp
from pa_control_items ci,pa_ci_types_b ci_type
where ci.ci_type_id = ci_type.ci_type_id
and ci_type.impact_budget_type_code=impact_bud_type_code
and ci.ci_id = p_ci_id_2;
select 'Y'
into temp
from pa_ci_impact_type_usage
where ci_type_id = p_ci_type_id_2
and impact_type_code <> 'FINPLAN' /* Bug# 3724520 */
and impact_Type_code not in (select impact_Type_code
from pa_ci_impact_type_usage
where ci_type_id = p_ci_type_id_1
and impact_type_code <> 'FINPLAN'); /* Bug# 3724520 */
select 'Y'
into temp
from pa_ci_impacts
where ci_id = p_ci_id_2
and impact_type_code <> 'FINPLAN' /* Bug# 3724520 */
and impact_Type_code not in (select impact_Type_code
from pa_ci_impact_type_usage
where ci_type_id = p_ci_type_id_1
and impact_type_code <> 'FINPLAN'); /* Bug# 3724520 */
Select enabled_flag
from pa_project_status_controls
where status_type = l_status_type
and project_status_code = l_status_code
and action_code = p_action_code;
Select enabled_flag
from pa_project_status_controls sc
,pa_project_statuses ps
where ps.project_status_code = l_status_code
and ps.project_system_status_code = sc.project_system_status_code
and sc.status_type = l_status_type
and sc.action_code = p_action_code;
SELECT status_code
FROM pa_control_items
WHERE ci_id = p_ci_id;
select
ci_id
,ci.ci_type_id
,status_code
,ps.project_system_status_code system_status_code
,cit.ci_type_class_code ci_type_class
,cit.approval_required_flag approval_required_flag
from pa_control_items ci
,pa_ci_types_b cit
,pa_project_statuses ps
where ci.ci_id = p_ci_id
and ci.ci_type_id = cit.ci_type_id
and ci.status_code = ps.project_status_code;
Select project_system_status_code
from pa_project_statuses
where project_status_code = p_next_status;
select status_code
into l_ci_status
from pa_control_items
where ci_id = p_ci_id ;
select 'Y'
into l_ci_type_has_impact
from dual
where exists ( select '1'
from pa_ci_impact_type_usage CIIU
,pa_control_items CI
where CIIU.ci_type_id= CI.ci_type_id
and CI.CI_id = p_ci_id ) ;
select 'Y'
into l_type_has_impact
from dual
where exists ( select '1'
from pa_ci_impact_type_usage CIIU
where CIIU.ci_type_id = p_ci_type_id
) ;
l_check_update_access varchar2(1) := 'F';
OR p_currpage_code = 'UPDATE_STATUS_OVERVIEW'
OR p_currpage_code = 'IMPACT_DETAILS'
OR p_currpage_code = 'UPDATE_RESOLUTION' ) THEN
return 'Y';
l_check_update_access := nvl(pa_ci_security_pkg.check_create_action(p_ci_id),'F');
if l_check_update_access <> 'T' then
return 'N';
OR p_page_code = 'UPDATE_RESOLUTION'
OR p_page_code = 'UPDATE_STATUS_OVERVIEW'
OR p_page_code = 'RELATED_ITEM'
OR p_page_code = 'ADD_ATTACHMENTS')
THEN
l_check_update_access := nvl(pa_ci_security_pkg.check_update_access(p_ci_id),'F');
if l_check_update_access <> 'T' then
return 'N';
SELECT workflow_item_type, workflow_process, enable_wf_flag
FROM pa_project_statuses
WHERE status_type = 'CONTROL_ITEM'
AND project_status_code = p_status_code;
SELECT MAX(pwp.item_key), max(pwp.item_type)
from pa_wf_processes pwp, pa_project_statuses pps
where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
and pps.status_type = 'CONTROL_ITEM'
and pps.project_status_code = 'CI_SUBMITTED'
AND entity_key2 = p_ci_id
AND pwp.wf_type_code = 'Control Item'
AND pwp.entity_key1 = l_project_id;
select 'Y' FROM dual
WHERE exists
(SELECT *
from wf_item_activity_statuses wias, pa_project_statuses pps
WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
AND wias.item_key = l_item_key
AND wias.activity_status = 'ACTIVE'
AND pps.status_type = 'CONTROL_ITEM'
AND pps.project_status_code = 'CI_SUBMITTED');
SELECT project_id
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT pci.status_code, pci.project_id
FROM pa_control_items pci
WHERE ci_id = p_ci_id;
IS SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS'
AND lookup_code = l_code;
SELECT pl.meaning,pl.lookup_code
FROM pa_lookups pl, pa_control_items pci, pa_ci_types_b pcit
WHERE
pl.lookup_type = 'PA_CI_TYPE_CLASSES'
and pci.ci_type_id = pcit.ci_type_id
and pl.lookup_code = pcit.ci_type_class_code
AND pci.ci_id = p_ci_id;
SELECT 'Y' FROM dual
WHERE exists
(
SELECT * FROM pa_ci_impacts
WHERE ci_id = p_ci_id
AND impact_type_code = 'FINPLAN'
);
select 'Y', wf_success_status_code from pa_project_statuses
where project_status_code = p_project_status_code
and enable_wf_flag = 'Y'
and workflow_item_type is not null
and workflow_process is not null
and wf_success_status_code is not null
and wf_failure_status_code is not null;
SELECT ci_action_id, ci_action_number, sign_off_required_flag
from pa_ci_actions where ci_id = p_ci_id and status_code = 'CI_ACTION_OPEN';
pa_debug.write_file('ChangeCiStatus: before call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');
pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
1.0,
p_init_msg_list,
p_commit,
p_validate_only,
p_max_msg_count,
p_ci_id,
l_new_status,
p_record_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
pa_debug.write_file('ChangeCiStatus: after call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');
/* Bug#3297238: call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
IF P_PA_DEBUG_MODE = 'Y' THEN
pa_debug.write_file('ChangeCiStatus: before call to ADD_STATUS_CHANGE_COMMENT');
SELECT employee_id, NVL(person_party_id, supplier_id) -- For Bug 4527617
INTO l_emp_id, l_party_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT party_name
INTO G_party_name
FROM hz_parties
WHERE party_id = l_party_id;
SELECT full_name
INTO G_party_name
FROM per_all_people_f
WHERE person_id = l_emp_id
and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)
and (CURRENT_EMPLOYEE_FLAG = 'Y' or CURRENT_NPW_FLAG = 'Y'); /* Added OR condition for bug 7132968 */
SELECT employee_id, NVL(person_party_id, supplier_id) -- For Bug 4527617
INTO l_emp_id, G_party_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT party_id
INTO G_party_id
FROM per_all_people_f
WHERE person_id = l_emp_id
AND ROWNUM=1;
SELECT ci_type_class_code, approval_required_flag
INTO l_type, l_approval_required
FROM pa_ci_types_b cit,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and cit.ci_type_id = ci.ci_type_id;
SELECT (case cit.approval_type_code
when 'STANDARD' THEN 'Y'
when 'EXTERNAL_APPROVAL' THEN 'Y'
when 'AUTOMATIC_APPROVAL' THEN 'A'
else 'A'
end ) l_approval_required
INTO l_approval_required
FROM pa_ci_types_b cit,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and cit.ci_type_id = ci.ci_type_id
and cit.ci_type_class_code IN ('CHANGE_ORDER', 'CHANGE_REQUEST', 'ISSUE'); --9882753
select
resolution_required_flag into l_resolution_required
from pa_ci_types_b cit
,pa_control_items ci
where ci.ci_id = p_ci_id
and cit.ci_type_id = ci.ci_type_id;
select
resolution_required_flag, resolution_code_id, resolution
from pa_ci_types_b cit
,pa_control_items ci
where ci.ci_id = p_ci_id
and cit.ci_type_id = ci.ci_type_id;
select
ci_type_class_code into l_type_class_code
from pa_ci_types_b cit
,pa_control_items ci
where ci.ci_id = p_ci_id
and cit.ci_type_id = ci.ci_type_id;
select ps.project_system_status_code
into l_ci_system_status
from pa_control_items ci
,pa_project_statuses ps
where ci_id = p_ci_id
and ps.project_status_code = nvl(ci.status_code,' ');
select ps.project_system_status_code
into l_ci_system_status
from
pa_project_statuses ps
where ps.project_status_code = nvl(p_status_code,' ');
FUNCTION deleteAllowed ( p_ci_id IN NUMBER := NULL
,p_owner_id IN NUMBER := NULL
,p_created_by_id IN NUMBER := NULL
,p_system_status IN VARCHAR2 := NULL)
return VARCHAR2
is
l_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
END deleteAllowed;
items are in DRAFT status, i.e. may be deleted.
NOTE: this function returns NULL when IN parm, p_project_id, is NULL.
-----------------------------------------------------------------------------*/
FUNCTION CheckNonDraftCI(p_project_id in Number)
return Varchar2
is
cursor c_non_draft_ci is
select
ci_id
from pa_control_items
,pa_project_statuses
where pa_control_items.project_id = p_project_id
and pa_control_items.status_code = pa_project_statuses.project_status_code
and pa_project_statuses.project_system_status_code <> 'CI_DRAFT';
SELECT 1
INTO tmp
FROM pa_control_items
WHERE project_id = p_project_id
AND ( ( p_task_id IS NOT NULL
AND object_type='PA_TASKS'
AND object_id=p_task_id)
OR p_task_id IS NULL)
AND ROWNUM = 1;
SELECT 1
INTO tmp
FROM pa_ci_types_b
WHERE ( classification_category = p_class_category
OR reason_category = p_class_category
OR resolution_category = p_class_category)
AND ROWNUM = 1;
SELECT 1
INTO tmp
FROM pa_control_items ci,
pa_class_codes cc
WHERE cc.class_category = p_class_category
AND cc.class_code = p_class_code
AND ( ci.classification_code_id = cc.class_code_id
OR ci.reason_code_id = cc.class_code_id
OR ci.resolution_code_id = cc.class_code_id)
AND ROWNUM = 1;
SELECT 1
INTO tmp
FROM pa_ci_types_b cit,
pa_object_dist_lists odl,
pa_dist_list_items dli
WHERE odl.object_type = 'PA_CI_TYPES'
AND odl.object_id = cit.ci_type_id
AND dli.list_id = odl.list_id
AND dli.recipient_type = 'PROJECT_ROLE'
AND dli.recipient_id = p_project_role_id
AND ROWNUM = 1;
SELECT 1
INTO tmp
FROM pa_ci_type_usage
WHERE project_type_id = p_project_type_id
AND ROWNUM = 1;
l_check_update_access varchar2(1) := 'F';
l_check_update_access := nvl(pa_ci_security_pkg.check_update_access(p_ci_id),'F');
if l_check_update_access <> 'T' then
return 'N';
merged the similar select for change order/request/issue using the bind variable p_item_type*/
if (p_item_type = 'ISSUE' or p_item_type = 'CHANGE_ORDER' or p_item_type = 'CHANGE_REQUEST') then
select count(*)
into tot_num
from pa_control_items pci, pa_ci_types_b pctb
where pci.project_id = p_project_id
and pci.object_type = p_object_type
and pci.object_id = p_object_id
and pci.ci_type_id = pctb.ci_type_id
and pctb.ci_type_class_Code = p_item_type --'ISSUE'
and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
from pa_project_statuses
where status_type = 'CONTROL_ITEM'
and project_system_status_code
in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
select count(*)
into tot_num
from pa_control_items pci, pa_ci_types_b pctb
where pci.project_id = p_project_id
and pci.object_type = p_object_type
and pci.object_id = p_object_id
and pci.ci_type_id = pctb.ci_type_id
and pctb.ci_type_class_Code in ('CHANGE_ORDER','CHANGE_REQUEST')
and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
from pa_project_statuses
where status_type = 'CONTROL_ITEM'
and project_system_status_code
in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
SELECT ps.workflow_item_type,
ps.workflow_process
FROM pa_project_statuses ps,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and ci.status_code = ps.project_status_code
and ps.enable_wf_flag = 'Y'
and ps.wf_success_status_code is NOT NULL
and ps.wf_failure_status_code is NOT NULL;
SELECT MAX(item_key)
FROM pa_wf_processes
WHERE item_type = p_wf_item_type
AND description = p_wf_process
AND entity_key2 = p_ci_id
AND entity_key1 = p_project_id
AND wf_type_code = 'Control Item';
SELECT ps.workflow_item_type,
ps.workflow_process
FROM pa_project_statuses ps,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
and ci.status_code = ps.project_status_code
and ps.enable_wf_flag = 'Y'
and ps.wf_success_status_code is NOT NULL
and ps.wf_failure_status_code is NOT NULL;
SELECT MAX(item_key)
FROM pa_wf_processes
WHERE item_type = p_wf_item_type
AND description = p_wf_process
AND entity_key2 = p_ci_id
AND entity_key1 = p_project_id
AND wf_type_code = 'Control Item';
select a.old_project_status_code, a.new_project_status_code
from (select obj_status_change_id,
old_project_status_code,
new_project_status_code
from pa_obj_status_changes
where object_type = 'PA_CI_TYPES'
and object_id = p_ci_id
order by obj_status_change_id desc) a
where rownum = 1;
pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_validate_only => FND_API.G_FALSE
,p_ci_id => p_ci_id
,p_status_code => l_prev_status
,p_record_version_number => p_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
/* Bug#3297238: call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
fnd_message.set_name('PA', 'PA_CI_ABORT_WF_COMMENT');
,P_LAST_UPDATED_BY IN NUMBER default fnd_global.user_id
,P_LAST_UPDATE_DATE IN DATE default sysdate
,P_LAST_UPDATE_LOGIN IN NUMBER default fnd_global.user_id
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 )
IS
cursor c_status_name(p_status VARCHAR2) is
select project_status_name
from pa_project_statuses
where status_type = 'CONTROL_ITEM'
and project_status_code = p_status;
SELECT pa_obj_status_changes_s.NEXTVAL
INTO l_obj_status_change_id
FROM dual;
PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
X_OBJECT_TYPE => p_object_type,
X_OBJECT_ID => p_object_id,
X_STATUS_TYPE => p_status_type,
X_NEW_PROJECT_STATUS_CODE => p_new_project_status,
X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sysstatus,
X_OLD_PROJECT_STATUS_CODE => p_old_project_status,
X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sysstatus,
X_CHANGE_COMMENT => p_comment,
X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
X_CREATED_BY => P_CREATED_BY,
X_CREATION_DATE => P_CREATION_DATE,
X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN);
PA_CI_COMMENTS_PKG.INSERT_ROW(
P_CI_COMMENT_ID => L_CI_COMMENT_ID,
P_CI_ID => P_OBJECT_ID,
P_TYPE_CODE => P_TYPE_CODE,
P_COMMENT_TEXT => L_COMMENT_TEXT,
P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
P_CREATED_BY => P_CREATED_BY,
P_CREATION_DATE => P_CREATION_DATE,
P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
P_CI_ACTION_ID => L_CI_ACTION_ID);
PROCEDURE DELETE_OBJ_STATUS_CHANGES(
p_object_type IN VARCHAR2
,p_object_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 )
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
DELETE FROM pa_obj_status_changes
WHERE object_type = p_object_type
AND object_id = p_object_id;
p_procedure_name => 'DELETE_OBJ_STATUS_CHANGES',
p_error_text => SUBSTRB(SQLERRM,1,240));
END DELETE_OBJ_STATUS_CHANGES;
SELECT pci.status_code, pci.project_id
FROM pa_control_items pci
WHERE ci_id = p_ci_id;
IS SELECT meaning
FROM pa_lookups
WHERE lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS'
AND lookup_code = l_code;
SELECT pl.meaning,pl.lookup_code
FROM pa_lookups pl, pa_control_items pci, pa_ci_types_b pcit
WHERE
pl.lookup_type = 'PA_CI_TYPE_CLASSES'
and pci.ci_type_id = pcit.ci_type_id
and pl.lookup_code = pcit.ci_type_class_code
AND pci.ci_id = p_ci_id;
SELECT 'Y' FROM dual
WHERE exists
(
SELECT * FROM pa_ci_impacts
WHERE ci_id = p_ci_id
AND impact_type_code like 'FINPLAN%'
);
select 'Y', wf_success_status_code from pa_project_statuses
where project_status_code = p_project_status_code
and enable_wf_flag = 'Y'
and workflow_item_type is not null
and workflow_process is not null
and wf_success_status_code is not null
and wf_failure_status_code is not null;
pa_ci_security_pkg.GET_RESP_WITH_ACCESS( p_privilege => 'PA_CI_UPDATE',
p_project_id => l_project_id,
x_has_access => l_temp,
x_resp_key => l_resp_key,
x_appl_short_name => l_appl_short_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
pa_debug.write_file('ChangeCiStatusValidate: PA_CI_STATUS_UPDATE_INV');
,p_msg_name => 'PA_CI_STATUS_UPDATE_INV');
pa_debug.write_file('ChangeCiStatus: before call to pa_control_items_pvt.delete_all_included_crs');
pa_control_items_pvt.delete_all_included_crs
(p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_debug.write_file('ChangeCiStatus: after call to pa_control_items_pvt.delete_all_included_crs');
pa_debug.write_file('ChangeCIStatusWorking: before call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');
pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
1.0,
p_init_msg_list,
p_commit,
p_validate_only,
p_max_msg_count,
p_ci_id,
l_new_status,
p_record_version_number,
x_return_status,
x_msg_count,
x_msg_data
);
pa_debug.write_file('ChangeCIStatusWorking: after call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');