[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.VERSION_NUMBER, a.NAME, a.PROJECT_ID, a.LOCKED_BY_PERSON_ID,
a.record_version_number, a.description
from pa_proj_elem_ver_structure a,
pa_proj_element_versions b
where p_structure_version_id = b.element_version_id
and b.project_id = a.project_id
and b.element_version_id = a.element_version_id;
select a.name, a.segment1
from pa_projects_all a
where c_project_id = a.project_id;
select usr.user_id, usr.customer_id, usr.user_name, papf.email_address,
papf.full_name person_name
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 = l_locked_by_person_id;
SELECT usr.user_id, usr.person_party_id, usr.user_name, papf.email_address, --customer_id is replaced with person_party_id
papf.full_name person_name
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;
Name: SELECT_APPROVER
Type: Procedure
Description: This API has been created for selecting an approver.
This is a Client Extension provided to the customer
to modify the default approver when the approver is
not specified.
IN:
p_item_type --The internal name for the item type. Item types
are defined in the Oracle Workflow Builder.
p_item_key --A string that represents a primary key generated
by the workflow-enabled application for the item
type. The string uniquely identifies the item
within an item type.
actid --The ID number of the activity from which this
procedure is called.
funcmode --The execution mode of the activity. If the activity
is a function activity, the mode is either 'RUN' or
'CANCEL'. If the activity is a notification activity,
with a post-notification function, then the mode can
be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or
'RUN'. Other execution modes may be added in the
future.
OUT:
resultout --If a result type is specified in the Activities
properties page for the activity in the Oracle
Workflow Builder, this parameter represents the
expected result that is returned when the procedure
completes.
=================================================================*/
procedure SELECT_APPROVER
(
p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_project_id NUMBER;
select wp_approver_source_id, wp_approver_source_type
from pa_proj_workplan_attr
where project_id = l_project_id; */
select wp.wp_approver_source_id, wp.wp_approver_source_type, p1.full_name
from pa_proj_workplan_attr wp, per_all_people_f p1
where wp.project_id = l_project_id
and p1.person_id = wp.wp_approver_source_id
and trunc(sysdate) between trunc(p1.effective_start_date)
and NVL(p1.effective_end_date, sysdate);
/* Modify this cursor to select the default approver when
the approver is not specified */
CURSOR getProjectManagerHR IS
select fu.user_name, p1.supervisor_id person_id, p2.full_name person_name,
p2.email_address
from per_assignments_f p1, per_all_people_f p2,
fnd_user fu, pa_project_parties p
where p.project_id = l_project_id
and p.project_role_id = 1
and TRUNC(sysdate) between p.START_DATE_ACTIVE
and NVL(p.END_DATE_ACTIVE, sysdate+1)
-- and p1.assignment_type = 'E' /* Bug#2911451 */ -- Commented by avaithia for Bug 3448680
and p1.assignment_type in ('E','C') -- Included By avaithia for Bug 3448680
and p1.primary_flag = 'Y' /* Bug#2911451 */
and p.resource_source_id = p1.person_id
and p1.supervisor_id = p2.person_id
and trunc(sysdate) between p1.effective_start_date
and p1.effective_end_date
and trunc(sysdate) between p2.effective_start_date
and NVL(p2.effective_end_date, sysdate)
and fu.employee_id = p1.supervisor_id;
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf
where fu.employee_id = l_source_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu,per_all_people_f papf
where fu.customer_id = l_source_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE
and nvl(fu.END_DATE, sysdate+1);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu,per_all_people_f papf
where fu.person_party_id = l_source_id --reference is changed from customer_id to person_party_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.EFFECTIVE_START_DATE
and Nvl(papf.effective_end_date, Sysdate + 1)
and trunc(sysdate) between fu.START_DATE
and nvl(fu.END_DATE, sysdate+1);
END SELECT_APPROVER;
Description: This API has been created for selecting the notifying
party when the workplan has been approved or rejected.
This is a Client Extension provided to the customer
to modify the default receiver of the notifications
IN:
p_item_type --The internal name for the item type. Item types
are defined in the Oracle Workflow Builder.
p_item_key --A string that represents a primary key generated
by the workflow-enabled application for the item
type. The string uniquely identifies the item
within an item type.
actid --The ID number of the activity from which this
procedure is called.
funcmode --The execution mode of the activity. If the activity
is a function activity, the mode is either 'RUN' or
'CANCEL'. If the activity is a notification activity,
with a post-notification function, then the mode can
be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or
'RUN'. Other execution modes may be added in the
future.
OUT:
resultout --If a result type is specified in the Activities
properties page for the activity in the Oracle
Workflow Builder, this parameter represents the
expected result that is returned when the procedure
completes.
=================================================================*/
procedure set_notification_party
(
p_item_type IN VARCHAR2
,p_item_key IN VARCHAR2
,p_status_code IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_project_id NUMBER;
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
--and object_id = l_project_id Bug 4562762
-- Bug 4562762 : Added logic to select program too
and object_id IN (select ver.project_id
from pa_object_relationships obj
, pa_proj_element_versions ver
where obj.object_id_to1=l_structure_version_id
and obj.relationship_type = 'LW'
and obj.object_id_from1=ver.element_version_id
union
select l_project_id
from dual)
and ppp.resource_type_id = 101
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_source_id = fu.employee_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
and f2.function_id = f1.function_id)
UNION /*Added this clause for Approver 4291185 */
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf
where fu.user_id = fnd_global.USER_ID
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1) ;
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 112
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_id = fu.customer_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
and f2.function_id = f1.function_id);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 112
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_id = fu.person_party_id -- customer_id is changed to person_party_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
and f2.function_id = f1.function_id);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 101
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_source_id = fu.employee_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
and f2.function_id = f1.function_id)
UNION
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_proj_elements ele
where ele.project_id = l_project_id
and ele.MANAGER_PERSON_ID = fu.employee_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1);*/
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 101
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_source_id = fu.employee_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and EXISTS (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
and f2.function_id = f1.function_id)
UNION
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_proj_elements ele
where ele.project_id = l_project_id
and ele.MANAGER_PERSON_ID = fu.employee_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
UNION
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf
where fu.user_id = fnd_global.USER_ID
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 112
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_id = fu.customer_id
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
and f2.function_id = f1.function_id);
select distinct fu.user_id, fu.user_name, papf.email_address,
papf.full_name person_name
from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
pa_project_role_types pprt
where object_type = 'PA_PROJECTS'
and object_id = l_project_id
and ppp.resource_type_id = 112
and ppp.project_role_id = pprt.project_role_id
and ppp.resource_id = fu.person_party_id -- customer_id changed to person_party_id in fnd_user table
and papf.person_id = fu.employee_id
and trunc(sysdate) between papf.effective_start_date
and nvl(papf.effective_end_date, sysdate+1)
and trunc(sysdate) between fu.start_date
and nvl(fu.end_date, sysdate+1)
and trunc(sysdate) between ppp.start_date_active
and nvl(ppp.end_date_active, sysdate+1)
and pprt.menu_id IN (select f1.menu_id
from fnd_compiled_menu_functions f1, fnd_form_functions f2
where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
and f2.function_id = f1.function_id);
SELECT page_content
FROM PA_PAGE_CONTENTS
WHERE pk1_value = document_id
AND object_type = 'PA_STRUCTURES'
AND pk2_value IS NULL;
SELECT ppe.wf_start_lead_days
FROM pa_proj_elements ppe,
pa_projects_all pa
WHERE pa.segment1=p_project_number --Removed Upper() from both sides for Performance Bug Fix 3961136
AND ppe.element_number = p_task_number --Removed Upper() from both sides for Performance Bug Fix 3961136
AND pa.project_id = ppe.project_id ;