[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_name
FROM FND_USER
WHERE user_id = l_starter_user_id;
SELECT e.first_name||' '||e.last_name
FROM FND_USER f, PER_ALL_PEOPLE_F e
WHERE f.user_id = l_starter_user_id
AND f.employee_id = e.person_id
AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
FROM per_all_people_f papf
WHERE papf.person_id = e.person_id);
SELECT SYSDATE FROM SYS.DUAL;
SELECT ci_id,
summary,
pci.description,
pctb.short_name||' ('||pci.ci_number||')' ci_number,
ci_type_class_code,
pci.created_by created_by,
pci.creation_date creation_date
FROM PA_CONTROL_ITEMS pci,
PA_CI_TYPES_vl pctb
WHERE ci_id = p_ci_id
AND pctb.ci_type_id = pci.ci_type_id;
SELECT pa_workflow_itemkey_s.nextval
INTO itemkey
FROM DUAL;
PA_WORKFLOW_UTILS.Insert_WF_Processes (p_wf_type_code => 'PATASKWF'
,p_item_type => ItemType
,p_item_key => ItemKey
,p_entity_key1 => c_task_info_rec.task_id
,p_description => c_task_info_rec.task_number
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT name organization_name
FROM HR_ORGANIZATION_UNITS
WHERE organization_id = p_carrying_out_organization_id;
SELECT ci_id,
summary,
pci.description,
pctb.short_name||' ('||pci.ci_number||')' ci_number,
ci_type_class_code,
pci.created_by created_by,
pci.creation_date creation_date
FROM PA_CONTROL_ITEMS pci,
PA_CI_TYPES_vl pctb
WHERE ci_id = p_ci_id
AND pctb.ci_type_id = pci.ci_type_id;
SELECT e.first_name||' '||e.last_name
INTO l_mgr_name
FROM PA_EMPLOYEES e
WHERE person_id = c_task_info_rec.manager_person_id;
SELECT page_content
INTO l_clob
FROM PA_PAGE_CONTENTS
WHERE page_content_id = l_page_content_id FOR UPDATE NOWAIT;
SELECT page_content
FROM PA_PAGE_CONTENTS
WHERE page_content_id =document_id
AND object_type = 'PA_TASK_APPROVAL_WF'
AND pk2_value IS NULL;
UPDATE PA_PROJ_ELEMENTS
SET task_status = 'SUBMITTED'
WHERE proj_element_id = l_proj_element;
UPDATE PA_PROJ_ELEMENTS
SET task_status = 'SUBMITTED'
WHERE proj_element_id = l_proj_element;
SELECT ppe.proj_element_id,
ppev.element_version_id,
ppev.parent_structure_version_id
FROM PA_PROJ_ELEMENTS ppe,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.proj_element_id = ppe.proj_element_id
AND ppe.proj_element_id = p_proj_elemt_id;
SELECT ppe.proj_element_Id task_id,
ppe.record_version_number,
ppev.parent_structure_version_id,
ppe.task_approver_id task_app_chg_id
FROM PA_PROJ_ELEMENTS PPE, PA_OBJECT_RELATIONSHIPS POR, PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE ppe.project_id = p_project_id
AND ppev.proj_element_id = ppe.proj_element_id
AND ppev.financial_task_flag = 'Y'
AND por.object_id_to1 = ppev.element_version_id
AND por.relationship_type = 'S'
AND por.relationship_subtype = 'TASK_TO_TASK'
AND por.object_id_from1 = p_element_version_id
AND ppe.link_task_flag = 'Y'
AND ppe.task_status ='PENDING';
SELECT pci.ci_id,
pcia.ci_action_id action_id
FROM pa_control_items pci, pa_ci_actions pcia
WHERE pci.project_id = p_project_id
AND pcia.ci_id(+) = pci.ci_id
AND pcia.ci_action_number(+) = pci.open_action_num
AND EXISTS (SELECT 1 FROM pa_budget_versions pbv, pa_resource_assignments pra
WHERE pbv.project_id = pci.project_Id
AND pbv.ci_id = pci.ci_id
AND pra.budget_version_id = pbv.budget_version_id
AND pra.project_id = p_project_id
AND pra.task_id = p_task_id)
AND pci.status_code in ('CI_SUBMITTED');
UPDATE PA_PROJ_ELEMENTS SET link_task_flag = 'N', task_status = ''
WHERE proj_element_id = l_task_id;
PROCEDURE Update_Task_Status(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2) IS
l_aprv_user_id NUMBER;
log_message('Inside Update_Task_Status Procedure',3);
UPDATE PA_PROJ_ELEMENTS SET task_status = 'PENDING',
task_approver_id = l_aprv_user_id
WHERE proj_element_id = l_proj_element_id;
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Update_Task_Status',itemtype, itemkey, to_char(actid), funcmode);
END Update_Task_Status;
PROCEDURE Delete_Task(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2) IS
-- Cursor to fetch the element version id and parent structure version id of a given task
CURSOR C1(p_proj_elemt_id NUMBER) IS
SELECT ppe.proj_element_id,
ppev.element_version_id,
ppev.parent_structure_version_id
FROM PA_PROJ_ELEMENTS ppe,
PA_PROJ_ELEMENT_VERSIONS ppev
WHERE ppev.proj_element_id = ppe.proj_element_id
AND ppe.proj_element_id = p_proj_elemt_id;
SELECT ppe.proj_element_Id task_id,
ppe.record_version_number,
ppev.parent_structure_version_id,
ppev.element_version_id
FROM PA_PROJ_ELEMENTS PPE,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE ppe.project_id = p_project_id
AND ppev.proj_element_id = ppe.proj_element_id
AND ppev.financial_task_flag = 'Y'
AND ppe.task_status IN ('NEW','SUBMITTED','PENDING')
AND ppev.element_version_id in (
SELECT object_id_to1
FROM pa_object_relationships
WHERE relationship_type = 'S'
AND relationship_subtype = 'TASK_TO_TASK'
START WITH object_id_from1 = p_element_version_id
AND relationship_type = 'S'
CONNECT BY object_id_from1 = PRIOR object_id_to1
AND relationship_type = prior relationship_type AND relationship_type = 'S'
) UNION ALL
SELECT ppe.proj_element_Id task_id,
ppe.record_version_number,
ppev.parent_structure_version_id,
ppev.element_version_id
FROM PA_PROJ_ELEMENTS PPE,
PA_PROJ_ELEMENT_VERSIONS PPEV
WHERE ppe.project_id = p_project_id
AND ppe.proj_element_id = ppev.proj_element_id
AND ppe.proj_element_id =p_task_id;
log_message('Inside Delete_Task Procedure',3);
g_del_taskrec.delete;
log_message('Storing the task details into pl/sql table and we delete the data
furtherly in is_last_task procedure',3);
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('PA_TASK_WORKFLOW_PKG','Delete_Task',itemtype, itemkey, to_char(actid), funcmode);
END Delete_Task;
Select distinct task_id from
pa_resource_assignments pra where
budget_version_id in (
select budget_version_id from pa_budget_versions where ci_id = l_ci_id )
and exists (select 1
from pa_proj_elements ppe,
pa_proj_element_versions ppev,
pa_object_relationships por
where ppe.proj_element_id = pra.task_id
and ppe.project_id = pra.project_id
and ppe.link_task_flag = 'Y'
and ppe.type_id = 1
and ppev.proj_element_id = ppe.proj_element_id
and por.object_id_to1 = ppev.element_version_id
and por.object_type_to = 'PA_TASKS'
and por.relationship_type = 'S'
and ppev.financial_task_flag = 'Y')
and not exists (select 1 from pa_tasks where task_id = pra.task_id and project_id = pra.project_id);
SELECT max(notification_id)
INTO l_max_notification_id
FROM WF_NOTIFICATIONS WFN
WHERE message_type = 'PATASKWF'
AND status = 'OPEN'
AND EXISTS (
SELECT 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'TASK_NUMBER'
AND text_value like c_task_rec.task_number
)
AND EXISTS (
SELECT 1
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE notification_id = wfn.notification_id
AND name = 'PROJECT_NUMBER'
AND text_value like c_task_rec.project_number
);
UPDATE WF_NOTIFICATIONS
SET status = 'CLOSED'
WHERE notification_id = l_max_notification_id;
SELECT user_name
FROM FND_USER
WHERE user_id = l_starter_user_id;
SELECT e.first_name||' '||e.last_name
FROM FND_USER f, PER_ALL_PEOPLE_F e
WHERE f.user_id = l_starter_user_id
AND f.employee_id = e.person_id
AND e.effective_end_date = ( SELECT MAX(papf.effective_end_date)
FROM per_all_people_f papf
WHERE papf.person_id = e.person_id);
DELETE FROM PA_RESOURCE_ASSIGNMENTS
WHERE project_id = l_project_id
AND task_id = c_task_info_rec.task_id;
log_message('Before calling PA_TASK_PUB1.Delete_Task_Version',3);
PA_TASK_PUB1.Delete_Task_Version
( p_task_version_id => l_elem_ver_id
,p_record_version_number => l_rec_ver_num
,p_structure_version_id => l_parent_struc_ver
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data ) ;
DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
WHERE element_version_id = l_elem_ver_id;
g_del_taskrec.delete(g_del_taskrec.FIRST,g_del_taskrec.FIRST);