[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select project_status_code
from pa_project_statuses
where project_status_name = x_project_status;
select distribution_rule
from pa_distribution_rules
where meaning = x_dist_name;
select project_type_class_code
from pa_project_types
where project_type = x_project_type;
select project_type_class_code
from pa_project_types_all t, pa_projects_all p -- Modified pa_projects and pa_project_types to pa_projects_all and pa_project_types_all for bug#3512486
where p.project_id = x_project_id
and p.project_type = t.project_type
-- and nvl(p.org_id, -99) = nvl(t.org_id, -99); -- Added the and condition for bug#3512486
select project_id
from pa_projects_all
where name = x_project_name
AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
select project_id
from pa_projects_all
where long_name = x_long_name
AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
select project_id
from pa_projects_all
where segment1 = x_project_number
AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
select 1
from pa_project_classes
where project_id = x_project_id
AND class_category = x_class_category
AND class_code = x_class_code
AND (x_rowid is null
or x_rowid <> pa_project_classes.rowid);
select 1
from pa_project_customers
where project_id = x_project_id
AND customer_id = x_customer_id
AND (x_rowid is null
or x_rowid <> pa_project_customers.rowid);
select project_type from pa_project_types
where project_type = x_project_type ;
select 1
from sys.dual
where exists (SELECT person_id
FROM pa_project_players
WHERE project_id = x_project_id
and project_role_type = 'PROJECT MANAGER'
AND TRUNC(sysdate) BETWEEN start_date_active
AND NVL(end_date_active, TRUNC(sysdate)));
SELECT NULL
FROM PA_PROJECT_CUSTOMERS
WHERE PROJECT_ID = x_PROJECT_ID
GROUP BY PROJECT_ID
HAVING SUM(CUSTOMER_BILL_SPLIT) = 100;
select 1
from sys.dual
where exists (SELECT NULL
FROM PA_PROJECT_CUSTOMERS CUST
WHERE CUST.PROJECT_ID = x_project_id
AND CUST.CUSTOMER_BILL_SPLIT > 0
AND EXISTS (SELECT NULL
FROM PA_PROJECT_CONTACTS CONT
WHERE CONT.PROJECT_ID = x_project_id
AND CONT.CUSTOMER_ID= CUST.CUSTOMER_ID
AND CONT.PROJECT_CONTACT_TYPE_CODE = 'BILLING'));
select 1
from sys.dual
where exists (SELECT NULL
-- anlee - modified for Classifications enhancements
/*
FROM PA_CLASS_CATEGORIES CC
WHERE MANDATORY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
*/
FROM PA_VALID_CATEGORIES_V VC,
PA_PROJECTS_ALL PPA,
PA_PROJECT_TYPES_ALL PPTA
WHERE VC.MANDATORY_FLAG = 'Y'
AND PPA.PROJECT_ID = x_project_id
AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
--AND nvl(PPA.ORG_ID, -99) = nvl(PPTA.ORG_ID, -99)
AND PPA.org_id = PPTA.org_id
AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_CLASSES PC
WHERE PC.PROJECT_ID = x_PROJECT_ID
-- AND PC.CLASS_CATEGORY = CC.CLASS_CATEGORY));
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT NULL
FROM pa_draft_invoices
WHERE project_id = x_project_id);
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT NULL
FROM pa_draft_revenues
WHERE project_id = x_project_id);
select 1
from sys.dual
where exists (SELECT null
FROM pa_projects
where created_from_project_id = x_project_id);
procedure check_delete_project_ok ( x_project_id IN number
, x_validation_mode IN VARCHAR2 DEFAULT 'U' --Bug 2947492
, x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
, x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
is
old_stack varchar2(630);
cursor p1 is select 1 from pa_project_types
where burden_sum_dest_project_id = x_project_id;
SELECT 'Y'
FROM pa_projects_all ppa, pa_forecasting_options_all pfoa
WHERE ppa.project_id = l_project_id
AND ppa.org_id = pfoa.org_id
AND (pfoa.bill_unassign_proj_id = ppa.project_id OR pfoa.nonbill_unassign_proj_id = ppa.project_id);
CURSOR c_is_pjr_delete_allowed(l_project_id IN NUMBER) IS
SELECT 'N'
FROM pa_project_assignments ppa, pa_project_statuses pps
WHERE ppa.project_id = l_project_id
AND ppa.assignment_type = 'STAFFED_ASSIGNMENT'
AND pps.status_type = 'STAFFED_ASGMT'
AND ppa.status_code = pps.project_status_code
AND pps.project_system_status_code = 'STAFFED_ASGMT_CONF'
UNION
SELECT 'N'
FROM pa_project_assignments ppa, pa_assignments_history pph, pa_project_statuses pps, pa_project_statuses pps1
WHERE ppa.project_id = l_project_id
AND ppa.assignment_id = pph.assignment_id
AND ppa.assignment_type = 'STAFFED_ASSIGNMENT'
-- AND pph.assignment_type = 'STAFFED_ASSIGNMENT' -- Not required since a staffed assignment in present is checked with staffed assignment in the past
AND pps.status_type = 'STAFFED_ASGMT'
AND pph.status_code = pps.project_status_code
AND pps.project_system_status_code = 'STAFFED_ASGMT_CONF'
AND pps1.status_type = 'STAFFED_ASGMT'
AND ppa.status_code = pps1.project_status_code
AND pps1.project_system_status_code <> 'STAFFED_ASGMT_CANCEL';
x_err_stack := x_err_stack || '->check_delete_project_ok';
for task_rec in (select task_id
from pa_tasks
where project_id = x_project_id
and task_id = top_task_id) loop
pa_task_utils.check_delete_task_ok(
x_task_id => task_rec.task_id,
x_validation_mode => x_validation_mode, --Bug 2947492
x_err_code => x_err_code,
x_err_stage => x_err_stage,
x_err_stack => x_err_stack);
PON_PROJECTS_INTEGRATION_GRP.CHECK_DELETE_PROJECT_OK(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_project_id => x_project_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
x_err_stage := l_msg_data; -- 'PON_PROJECT_USED_NO_DELETE';
-- Start Bug 5750624: Cursor to check the project is allowed to deleted from PJR assignments
-- We should not allow to delete project with Confirmed Assignment or any assignment which had been in confirmed state previously
OPEN c_is_pjr_delete_allowed(x_project_id);
FETCH c_is_pjr_delete_allowed INTO l_temp_char;
IF c_is_pjr_delete_allowed%NOTFOUND THEN
CLOSE c_is_pjr_delete_allowed;
CLOSE c_is_pjr_delete_allowed;
-- End Bug 5750624: Cursor to check the project is allowed to deleted from PJR assignments
/* Start changes for Service Intgration bug#16535441*/
x_err_stage := 'check expenditure item for '|| x_project_id;
end check_delete_project_ok;
select 1
into temp
from sys.dual where
exists ( select 1 from pa_options where parent_option_code = p_option_code);
SELECT NVL(SUM(NVL(allocated_amount,0)),0) allocated_amount
FROM pa_project_fundings
WHERE project_id = x_project_id;
select null
into dummy
from sys.dual
where not exists (
select null
from pa_expenditure_items_all pai
/* Bug#3461661 : removed join with pa_tasks
* ,pa_tasks t
*/
,pa_cost_distribution_lines_all pcd
where
/* Bug#3461661 : removed join condition
* pai.task_id = t.task_id
* and
*/
pai.expenditure_item_id = pcd.expenditure_item_id
and pai.project_id = x_project_id);
select null
into dummy
from sys.dual
where not exists (
select null
from pa_draft_revenues_all
where project_id = x_project_id);
select null
into dummy
from sys.dual
where not exists (
select null
from pa_draft_invoices_all
where project_id = x_project_id);
SELECT user_defined_project_num_code
FROM pa_implementations;
SELECT manual_project_num_type
FROM pa_implementations;
SELECT project_status_code
FROM pa_projects_all pap
WHERE pap.project_id = x_project_id;
SELECT enabled_flag ,project_system_status_code
FROM pa_project_status_controls
WHERE project_status_code = x_project_status_code
AND action_code = x_action_code;
SELECT enabled_flag,project_status_code
FROM pa_project_status_controls
WHERE project_system_status_code = x_project_system_status_code
AND action_code = x_action_code;
SELECT '1'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM PA_PROJECTS_ALL PP
WHERE PP.labor_tp_schedule_id=p_tp_schedule_id
OR PP.nl_tp_schedule_id=p_tp_schedule_id
)
OR EXISTS
(SELECT 'Y'
FROM PA_TASKS PT
WHERE PT.labor_tp_schedule_id=p_tp_schedule_id
OR PT.nl_tp_schedule_id=p_tp_schedule_id
);
SELECT administrative_flag INTO l_admin_flag
FROM pa_project_types_all pt,
pa_projects_all proj
WHERE pt.project_type = proj.project_type
--AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
AND pt.org_id = proj.org_id --avajain
AND proj.project_id = p_project_id;
SELECT pt.unassigned_time INTO l_unassigned_time_flag
FROM pa_project_types_all pt,
pa_projects_all proj
WHERE pt.project_type = proj.project_type
--AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
AND pt.org_id = proj.org_id
AND proj.project_id = p_project_id;
select 'Y'
into x_val
from pa_project_parties_v
where project_id = p_project_id
and project_role_id = 1
and user_id = p_user_id
and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate)); ----- Project Manager
procedure check_delete_project_type_ok (
p_project_type_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_return_value VARCHAR2(1) := 'N';
end check_delete_project_type_ok;
Procedure check_delete_class_catg_ok (
p_class_category IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
BEGIN
x_return_status :='S';
end check_delete_class_catg_ok;
Procedure check_delete_class_code_ok (
p_class_category IN VARCHAR2
,p_class_code IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
BEGIN
x_return_status :='S';
end check_delete_class_code_ok;
select project_id
from pa_projects_all
where pm_project_reference = p_proj_ref
and pm_product_code = p_prod_code -- added for bug 4870305
AND (p_ROWID IS NULL OR p_ROWID <> pa_projects_all.ROWID);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM PA_PROJECT_CUSTOMERS
WHERE project_id = p_project_id
AND (customer_id <> bill_to_customer_id
OR customer_id <> ship_to_customer_id )) ;
select APPLICATION_COLUMN_NAME
into
v_application_names(l_index)
from fnd_descr_flex_column_usages
where END_USER_COLUMN_NAME = l_global_req_segs_info1.required_segment_names(x)
and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements' ;
select APPLICATION_COLUMN_NAME
into
v_application_names(l_index)
from fnd_descr_flex_column_usages
where END_USER_COLUMN_NAME = l_context_req_segs_info1.required_segment_names(y) --x_context_req_segs_info1(l_index)
and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_category ;
SELECT papf.person_id into l_person_id
FROM per_all_people_f papf, fnd_user usr
WHERE papf.person_id = usr.employee_id
AND trunc(sysdate) between papf.effective_start_date
AND nvl(papf.effective_end_date, sysdate+1)
AND trunc(sysdate) between usr.start_date
AND nvl(usr.end_date, sysdate+1)
AND usr.user_id = p_user_id;