[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UpdateActionHistory(p_document_id NUMBER,
p_action VARCHAR2,
p_note VARCHAR2,
p_current_approver NUMBER);
l_insertion_type VARCHAR2(30);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT
to_char(l_document_id) || '-' || to_char(PO_WF_ITEMKEY_S.nextval)
INTO l_item_key
FROM sys.dual;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
select first_position_id, first_approver_id
into l_first_position_id, l_first_approver_id
from po_requisition_headers_all
where l_document_id = requisition_header_id;
SELECT full_name
INTO l_next_approver_name
FROM per_all_people_f person
WHERE person_id = l_first_approver_id
--Bug#7207213#This query fetches multiple records so adding a filter
and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in (
SELECT assignment_status_type_id FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
) where rownum = 1;
SELECT employee_id
into l_next_approver_id
FROM fnd_user
WHERE user_id = g_next_approvers(l_approver_index).orig_system_id
and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
po_wf_debug_pkg.insert_debug(itemtype,itemkey,l_progress);
if (g_next_approvers(l_approver_index).api_insertion = 'Y') then
n_varval(5) := 1;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
g_next_approvers.delete;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT name into l_current_approver.name FROM
( SELECT name FROM wf_roles WHERE orig_system = l_current_approver.orig_system
and orig_system_id = l_current_approver.orig_system_id
order by start_date
)
WHERE rownum = 1;
ame_api2.updateApprovalStatus( applicationIdIn=>applicationId,
transactionIdIn=>l_document_id,
transactionTypeIn=>l_transaction_type,
approverIn => l_current_approver);
SELECT parent_item_type, parent_item_key
into l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype and item_key = itemkey;
SELECT wfi.item_type, wfi.item_key
FROM wf_items wfi,wf_item_activity_statuses wfias
WHERE wfi.parent_item_key =itemkey
and wfi.item_type=itemtype
AND wfias.item_type=wfi.item_type
AND wfias.item_key=wfi.item_key
AND wfias.activity_status='NOTIFIED'
AND wfias.notification_id IS NOT null;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT parent_item_type, parent_item_key
into l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype and item_key = itemkey;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
select first_position_id, first_approver_id
into l_first_position_id, l_first_approver_id
from po_requisition_headers_all
where l_req_header_id = requisition_header_id;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, approverList(i).orig_system || to_char(i) || ' ' ||
approverList(i).orig_system_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'authority' || ' ' || approverList(i).authority);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'approval_status'|| ' ' || approverList(i).approval_status);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'api_insertion'|| ' ' || approverList(i).api_insertion);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'group_or_chain_id' || ' ' || approverList(i).group_or_chain_id);
and approverList(i).api_insertion = ame_util.oamGenerated
and approverList(i).group_or_chain_id = l_approver_group_id) then
l_orig_system := approverList(i).orig_system;
SELECT person_id into l_person_id FROM (
SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in (
SELECT assignment_status_type_id FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
) where rownum = 1;
SELECT employee_id into l_person_id
FROM fnd_user
WHERE user_id = l_orig_system_id
and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
UpdateActionHistory(l_req_header_id, 'NO ACTION',
l_note, l_person_id);
-- Get the approver id as the person id to update the action history
l_person_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
itemkey => l_child_wf_cur.item_key,
aname => 'APPROVER_EMPID');
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
-- update the action history table
UpdateActionHistory(l_req_header_id, 'NO ACTION',
l_note, l_person_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT parent_item_type, parent_item_key
into l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype and item_key = itemkey;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT parent_item_type, parent_item_key
into l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype and item_key = itemkey;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
SELECT parent_item_type, parent_item_key
into l_parent_item_type, l_parent_item_key
FROM wf_items
WHERE item_type = itemtype and item_key = itemkey;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_progress);
procedure insertActionHistory( p_req_header_id in number,
p_employee_id in number,
p_approval_group_id in number)
is
pragma AUTONOMOUS_TRANSACTION;
SELECT max(sequence_num)
INTO l_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = p_req_header_id;
SELECT object_sub_type_code,
object_revision_num, approval_path_id, request_id,
program_application_id, program_date, program_id
INTO l_object_sub_type_code,
l_object_revision_num, l_approval_path_id, l_request_id,
l_program_application_id, l_program_date, l_program_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = p_req_header_id
AND sequence_num = l_sequence_num;
/* update po action history */
po_forward_sv1.insert_action_history (
p_req_header_id,
'REQUISITION',
l_object_sub_type_code,
l_sequence_num + 1,
NULL,
NULL,
p_employee_id,
NULL,
NULL,
l_object_revision_num,
NULL, /* offline_code */
l_request_id,
l_program_application_id,
l_program_id,
l_program_date,
fnd_global.user_id,
fnd_global.login_id,
p_approval_group_id);
end insertActionHistory;
procedure Insert_Action_History( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(500) := '000';
l_progress := 'Insert_Action_History: 001';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Insert_Action_History: 004 - Calling insertActionHistory.';
insertActionHistory(l_req_header_id, l_next_approver_id, l_approval_group_id);
l_progress := 'Insert_Action_History: 005 - Done with insertActionHistory.';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Insert_Action_History: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('POR_AME_REQ_WF_PVT','Insert_Action_History',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.INSERT_ACTION_HISTORY');
END Insert_Action_History;
procedure Update_Action_History_Approve( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(500) := '000';
l_progress := 'Update_Action_History_Approve: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Approve: 002-'|| to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
UpdateActionHistory(l_document_id, l_action,
l_note, l_current_approver);
l_progress := 'Update_Action_History_Approve: 003';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Approve',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.UPDATE_ACTION_HISTORY_APPROVE');
END Update_Action_History_Approve;
procedure Update_Action_History_Reject(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
l_progress := 'Update_Action_History_Reject: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Reject: 002-'|| to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
UpdateActionHistory(l_document_id, l_action,
l_note, l_current_approver);
l_progress := 'Update_Action_History_Reject: 003';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Reject',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Reject');
END Update_Action_History_Reject;
procedure Update_Action_History_Timeout(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
l_progress := 'Update_Action_History_Timeout: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Timeout: 002-'|| to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
UpdateActionHistory(l_document_id, l_action,
l_note, l_current_approver);
l_progress := 'Update_Action_History_Timeout: 003';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_Timeout',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_Timeout');
END Update_Action_History_Timeout;
procedure Update_Action_History_No_Act (itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_doc_header_id NUMBER;
x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 01';
x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 02 - l_doc_header_id ' || l_doc_header_id || ' -- l_doc_type :' || l_doc_type ;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
UpdateActionHistory(l_doc_header_id, 'NO ACTION', l_note, NULL);
x_progress := 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act: 03';
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
wf_core.context('POR_AME_REQ_WF_PVT','Update_Action_History_No_Act',x_progress);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_REQ_WF_PVT.Update_Action_History_No_Act');
END Update_Action_History_No_Act;
PROCEDURE UpdateActionHistory(p_document_id NUMBER,
p_action VARCHAR2,
p_note VARCHAR2,
p_current_approver NUMBER) IS
pragma AUTONOMOUS_TRANSACTION;
UPDATE po_action_history
SET action_code = p_action,
note = p_note,
action_date = sysdate
WHERE object_id = p_document_id and
employee_id = p_current_approver and
action_code is null and
object_type_code = 'REQUISITION'
and rownum=1;
UPDATE po_action_history
SET action_code = p_action,
note = p_note,
action_date = sysdate
WHERE object_id = p_document_id and
action_code is null and
object_type_code = 'REQUISITION'
;
END UpdateActionHistory;
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_doc_string );
select first_position_id, first_approver_id
into l_first_position_id, l_first_approver_id
from po_requisition_headers_all
where documentId = requisition_header_id;
SELECT full_name
INTO l_next_approver_name
FROM per_all_people_f person
WHERE person_id = l_first_approver_id
--Bug#7207213#This query fetches multiple records so adding a filter
and trunc(sysdate) between person.effective_start_date and nvl(person.effective_end_date, trunc(sysdate));
SELECT person_id, full_name into l_next_approver_id,l_next_approver_name FROM (
SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id and trunc(sysdate) between person.effective_start_date
and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
and asg.assignment_status_type_id not in (
SELECT assignment_status_type_id FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN'
) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
) where rownum = 1;
ame_api2.updateApprovalStatus( applicationIdIn =>applicationId,
transactionIdIn =>documentId,
transactionTypeIn =>documentType,
approverIn => g_next_approvers(l_approver_index)
);
g_next_approvers.delete(l_approver_index);