The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE InsertHistForOwnerApprove (itemtype VARCHAR2,
itemkey VARCHAR2,
p_document_id NUMBER,
p_document_type VARCHAR2,
p_document_subtype VARCHAR2);
PROCEDURE AutoUpdateCloseAutonomous(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
, x_return_code OUT NOCOPY VARCHAR2
);
select USER_ID from fnd_user where supplier_id = supplier_contact_id;
select vendor_contact_id, nvl(acceptance_required_flag, 'N'), acceptance_due_date, nvl(revision_num, 0)
into x_vendor_contact_id, x_acceptance_required_flag, x_acceptance_due_date, x_doc_revision
from po_headers
where po_header_id = document_id;
select count(*) into x_item_exists
from wf_items
where item_type = 'POSPOACK'
and item_key = l_itemkey;
select (trunc(nvl(x_acceptance_due_date, sysdate)) - trunc(sysdate)) * 60
into x_minutes_to_acceptance
from sys.dual;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select nvl(status, 'DRAFT') into l_mod_status
from po_drafts
where draft_id = l_draft_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_insert_owner_app Boolean := true;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
l_insert_owner_app := false;
if (l_document_type = 'REQUISITION' and l_insert_owner_app) then
po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'NOTE',
avalue => '');
InsertHistForOwnerApprove(itemtype, itemkey,
l_document_id, l_document_type, l_document_subtype);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT 'E'
INTO l_error
FROM dual
WHERE EXISTS (SELECT 1
FROM PO_ONLINE_REPORT_TEXT
WHERE online_report_id = x_online_report_id
AND NVL(message_type, 'E') = 'E'); -- Bug 3906870
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PROCEDURE InsertHistForOwnerApprove (itemtype VARCHAR2,
itemkey VARCHAR2,
p_document_id NUMBER,
p_document_type VARCHAR2,
p_document_subtype VARCHAR2) IS
pragma AUTONOMOUS_TRANSACTION;
x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove begin';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT sequence_num,
action_code,
object_revision_num,
approval_path_id
INTO l_sequence_num,
l_action_code,
l_object_rev_num,
l_approval_path_id
FROM po_action_history
WHERE object_id = p_document_id
AND object_type_code = p_document_type
AND sequence_num = (SELECT max(sequence_num)
FROM po_action_history
WHERE object_id = p_document_id
AND Object_Type_Code = P_Document_Type
AND object_sub_type_code = p_document_subtype)
AND Object_Sub_Type_Code = P_Document_Subtype;
x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove action_code: ' || l_action_code;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
SELECT employee_id
INTO l_employee_id
FROM po_action_history
WHERE object_id = p_document_id
AND object_type_code = p_document_type
AND sequence_num = (SELECT max(sequence_num)
FROM po_action_history
WHERE object_id = p_document_id
AND action_code = 'APPROVE'
AND Object_Type_Code = P_Document_Type
AND Object_Sub_Type_Code = P_Document_Subtype)
AND object_sub_type_code = p_document_subtype;
SELECT employee_id
INTO l_employee_id
FROM po_action_history
WHERE object_id = p_document_id
AND object_type_code = p_document_type
AND sequence_num = (SELECT max(sequence_num)
FROM po_action_history
WHERE object_id = p_document_id
AND action_code = 'SUBMIT'
AND Object_Type_Code = P_Document_Type
AND Object_Sub_Type_Code = P_Document_Subtype)
AND object_sub_type_code = p_document_subtype;
SELECT employee_id
INTO l_employee_id
FROM po_action_history
WHERE object_id = p_document_id
AND object_type_code = p_document_type
AND sequence_num = (SELECT max(sequence_num)
FROM Po_Action_History
WHERE object_id = p_document_id
AND action_code = decode(l_action_code, 'SIGNED','SUBMIT',l_action_code) --Bug 13444730
AND Object_Type_Code = P_Document_Type
AND Object_Sub_Type_Code = P_Document_Subtype)
AND object_sub_type_code = p_document_subtype;
x_progress := ' PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove employee_id: ' || l_employee_id;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
INSERT into PO_ACTION_HISTORY
(object_id,
object_type_code,
object_sub_type_code,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
action_code,
action_date,
employee_id,
note,
object_revision_num,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
approval_path_id,
offline_code)
VALUES
(p_document_id,
p_document_type,
p_document_subtype,
l_sequence_num + 1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
NULL,
NULL,
l_employee_id,
NULL,
l_object_rev_num,
fnd_global.login_id,
0,
0,
0,
'',
l_approval_path_id,
'' );
x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove inserted';
x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove end';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
END InsertHistForOwnerApprove;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
l_document_type,
l_document_id,
l_kanban_return_status);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
x_progress := 'OpenDocumentState: calling autoupdatecloseautonomous with: ' || 'Doc_type= ' ||
l_document_type || ' Subtype= ' || l_document_subtype ||
' Doc_id= ' || to_char(l_document_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
AutoUpdateCloseAutonomous(
p_document_id => l_document_id
, p_document_type => l_document_type
, p_document_subtype => l_document_subtype
, x_return_status => l_ret_sts
, x_exception_msg => l_exc_msg
, x_return_code => l_ret_code
);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
x_progress := 'PO_REQAPPROVAL_ACTION.OpenDocumentState: auto_close_update_autonomous returned with: ' ||
l_ret_sts;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT substr(text_line, 1, 2000)
BULK COLLECT INTO l_report_text_lines
FROM po_online_report_text
WHERE online_report_id = p_online_report_id
ORDER BY sequence;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PROCEDURE AutoUpdateCloseAutonomous(
p_document_id IN NUMBER
, p_document_type IN VARCHAR2
, p_document_subtype IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_exception_msg OUT NOCOPY VARCHAR2
, x_return_code OUT NOCOPY VARCHAR2
)
IS
pragma AUTONOMOUS_TRANSACTION;
PO_DOCUMENT_ACTION_PVT.auto_update_close_state(
p_document_id => p_document_id
, p_document_type => p_document_type
, p_document_subtype => p_document_subtype
, p_line_id => NULL
, p_shipment_id => NULL
, p_calling_mode => 'PO'
, p_called_from_conc => FALSE
, x_return_status => x_return_status
, x_exception_msg => x_exception_msg
, x_return_code => x_return_code
);
wf_core.context('PO_REQAPPROVAL_ACTION', 'AutoUpdateCloseAutonomous', to_char(d_progress));
END AutoUpdateCloseAutonomous;