The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_APPROVALLIST_S1.update_approval_list_itemkey(
p_approval_list_header_id=>l_approval_list_header_id,
p_itemtype=>itemtype,
p_itemkey=>itemkey,
p_return_code=>l_return_code);
l_progress := 'Find_Approval_List: 005- update_approval_list_itemkey - '||
to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
p_last_update_date=>null,
p_return_code=>l_return_code,
p_error_stack=>l_error_stack);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_APPROVALLIST_S1.update_approval_list_itemkey(
p_approval_list_header_id=>l_approval_list_header_id,
p_itemtype=>itemtype,
p_itemkey=>itemkey,
p_return_code=>l_return_code);
'- update_approval_list_itemkey-'||
to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
procedure Insert_Action_History(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
l_progress := 'Insert_Action_History: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Forward_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_forward_to_id=>l_next_approver_id,
x_req_header_id=>l_req_header_id,
x_approval_path_id=>l_approval_path_id);
l_progress := 'Insert_Action_History: 005 - Forward_Action_History';
/* DEBUG */ 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('PO_APPROVAL_LIST_WF1S','Insert_Action_History',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.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(100) := '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;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_Approve: 005 - Update_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Approve: 006';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Approve: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Approve',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APPROVE');
END Update_Action_History_Approve;
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;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_Timeout: 003- Update_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_App_Fwd: 004';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Timeout: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Timeout',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_TIMEOUT');
END Update_Action_History_Timeout;
procedure Update_Action_History_App_Fwd(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_App_Fwd: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_App_Fwd: 002-'||
to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_App_Fwd: 005- Update_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_App_Fwd: 006';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_App_Fwd: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_App_Fwd',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APP_FWD');
END Update_Action_History_App_Fwd;
procedure Update_Action_History_Forward(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_Forward: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Forward: 002-'||
to_char(l_document_id)||'-'||
l_document_type||'-'||l_document_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_Forward: 005- Update_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Forward: 006';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Forward: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Forward',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_FORWARD');
END Update_Action_History_Forward;
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;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* update po action history */
PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
itemkey=>itemkey,
x_action=>l_action,
x_req_header_id=>l_document_id,
x_last_approver=>l_result,
x_note=>l_note);
l_progress := 'Update_Action_History_Reject: 005 - Update_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Reject: 006';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Action_History_Reject: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Reject',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_REJECT');
END Update_Action_History_Reject;
** Desc: We now use the new API: Update_App_List_Resp_Success.
**
** Also reverted the change of bug# 1394711 in the old API
** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
** as before the fix in bug# 1394711.
**
** This is to support reqs submitted for approval before applying this bug
** fix otherwise their approval will error out due to change in return value.
**
** For reqs submitted for approval after bug# 1394711 and before this new
** fix, modified the API 'Update_Approval_List_Response' to check for the
** expected result type for that req approval process and return
** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
** lookup type is 'PO_SUCCESS_FAILURE'.
*/
procedure Update_Approval_List_Response(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
CURSOR c_group_id (p_itemtype VARCHAR2, p_itemkey VARCHAR2, p_activity_name VARCHAR2) IS
SELECT notification_id
FROM wf_item_activity_statuses_v
WHERE item_type = p_itemtype
AND item_key = p_itemkey
AND activity_name = p_activity_name
ORDER BY activity_end_date DESC;
SELECT '1'
FROM WF_NOTIFICATIONS
WHERE notification_id = notif_id
AND status = 'CANCELED';
SELECT recipient_role, attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'RESULT';
SELECT attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'NOTE';
SELECT nvl((wfu.orig_system_id), -9996)
FROM wf_users wfu
WHERE wfu.name = wf_notification.responder(p_notification_id)
AND wfu.orig_system not in ('POS', 'ENG_LIST', 'CUST_CONT');
SELECT nvl((wfu.orig_system_id), -9996)
FROM wf_users wfu
WHERE wfu.name = p_responder
AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
E_UPDATE_RESPONSE_FAIL EXCEPTION;
l_progress := 'Update_Approval_List_Response: 001- at beginning of function';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
** fix, modified the API 'Update_Approval_List_Response' to check for the
** expected result type for that req approval process and return
** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
** lookup type is 'PO_SUCCESS_FAILURE'.
**
** We can achieve this by running the following sql which returns
** the expected result type.
*/
Begin
select wa.result_type
into exp_result
from wf_activities wa,
wf_process_activities wpa,
wf_items wi
where wpa.instance_id = actid
and wpa.process_item_type = wa.item_type
and wpa.activity_name = wa.name
and wi.item_type = wpa.process_item_type
and wi.item_key = itemkey
and wi.begin_date > wa.begin_date
and wi.begin_date <= nvl(wa.end_date,wi.begin_date);
l_progress := 'Update_Approval_List_Response : 001-2'||
'exp_result: ' || exp_result || ' actid: ' || to_char(actid);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 002';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 003';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 0031';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 0032';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 0033';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 0034';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 00341';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 00342';
l_progress := 'Update_Approval_List_Response: 0035';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 004';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT wfn.responder, wfn.recipient_role, wfn.end_date
INTO l_responder, l_recipient_role, l_end_date
FROM wf_notifications wfn
WHERE wfn.notification_id = l_group_id;
l_progress := 'Update_Approval_List_Response: 005';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 007 -' || l_responder_user_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 008' ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 009' ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 010 APP'||
to_char(l_approver_id)||
' RES'||to_char(l_responder_id)||
' FWD'||to_char(l_forward_to_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_APPROVALLIST_S1.update_approval_list_response(
p_document_id=>l_document_id,
p_document_type=>l_document_type,
p_document_subtype=>l_document_subtype,
p_itemtype=>itemtype,
p_itemkey=>itemkey,
p_approver_id=>l_approver_id,
p_responder_id=>l_responder_id,
p_forward_to_id=>l_forward_to_id,
p_response=>l_value,
p_response_date=>l_end_date,
p_comments=>substrb(l_note,1,480), -- bug 3105327
p_return_code=>l_return_code);
l_progress := 'Update_Approval_List_Response: 011'||to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response: 012';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_Approval_List_Response : 013'||to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
** Desc: The Update_Approval_List_Response() procedure raises exception
** when the rebuild_approval_list() fails and the approval workflow
** hangs. We need to handle the situation when the rebuild_approval_list()
** fails because of No approver found in order to return the Requisition
** to the preparer.
**
** Changed the procedure Update_Approval_List_Response() to return FAILURE
** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
**
** The Requsition workflow also has been changed to handle the above.
**
** Dependency: poxwfrqa.wft
*/
ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN
/* Bug# 1712121 */
if retnew = TRUE then
resultout:='COMPLETE' || ':' || 'FAILURE';
l_progress := 'Update_Approval_List_Response : 100';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
END IF; -- update success
l_progress := 'Update_Approval_List_Response : 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
RAISE E_UPDATE_RESPONSE_FAIL;
WHEN E_UPDATE_RESPONSE_FAIL THEN
IF (c_group_id%ISOPEN) THEN
CLOSE c_group_id;
'Update_Approval_List_Response E_FAILURE',
l_progress,l_return_code,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
'Update_Approval_List_Response',l_progress,sqlerrm);
PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
END Update_Approval_List_Response;
SELECT notification_id
FROM wf_item_activity_statuses_v
WHERE item_type = p_itemtype
AND item_key = p_itemkey
AND activity_name in ( p_activity_name, p_activity_name2,
p_activity_name3, p_activity_name4)
ORDER BY activity_end_date DESC;
SELECT '1'
FROM WF_NOTIFICATIONS
WHERE notification_id = notif_id
AND status = 'CANCELED';
SELECT recipient_role, attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'RESULT';
SELECT attribute_value
FROM wf_notification_attr_resp_v
WHERE group_id = p_group_id
AND attribute_name = 'NOTE';
SELECT nvl((wfu.orig_system_id), -9996)
FROM wf_users wfu
WHERE wfu.name = p_responder
AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 002';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 003';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 0031';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 0032';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 0033';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 0034';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 00341';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 00342';
l_progress := 'Update_App_List_Resp_Success: 0035';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 004';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
SELECT wfn.responder, wfn.recipient_role, wfn.end_date
INTO l_responder, l_recipient_role, responseEndDate
FROM wf_notifications wfn
WHERE wfn.notification_id = l_group_id;
l_progress := 'Update_App_List_Resp_Success: 005';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 007 -' || l_responder_user_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 008' ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 009' ;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success : 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
'Update_App_List_Resp_Success',l_progress,sqlerrm);
** Update_Approval_List_Response from 'Activity Performed' to 'SUCCESS/FAILURE'.
** This changed was made to the API as well as the workflow.
**
** Now the reqs created after applying this patch would work fine but the
** requisitions submitted for approval before applying this fix, which are still
** in process, get stuck when the users try to approve them.
**
** In scenarios when we have to change the return type in wf, we should
** create a new API and leave the old one as it is. Now the workflow activity
** should be calling the new API. With this the new reqs will work fine as the
** new API will be returning the changed return types as expected by the new
** workflow definition and also the reqs submitted for approval before the fix
** will also work fine as the old workflow definition will be calling the old
** API which still returns the return types as expected by the old definition.
**
** Created a new API 'Update_App_List_Resp_Success'. This API will return
** 'SUCCESS-FAILURE'. The workflow activity 'Update Approval List Response'
** has also been changed to call this new API.
**
** Also reverted the change of bug# 1394711 in the old API
** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
** as before the fix in bug# 1394711.
*/
procedure Update_App_List_Resp_Success(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(1000) := '000';
E_UPDATE_RESPONSE_FAIL EXCEPTION;
l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 010 APP'||
to_char(l_approver_id)||
' RES'||to_char(l_responder_id)||
' FWD'||to_char(l_forward_to_id);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
PO_APPROVALLIST_S1.update_approval_list_response(
p_document_id=>l_document_id,
p_document_type=>l_document_type,
p_document_subtype=>l_document_subtype,
p_itemtype=>itemtype,
p_itemkey=>itemkey,
p_approver_id=>l_approver_id,
p_responder_id=>l_responder_id,
p_forward_to_id=>l_forward_to_id,
p_response=>l_value,
p_response_date=>l_end_date,
p_comments=>substrb(l_note,1,480), -- bug 3105327
p_return_code=>l_return_code);
l_progress := 'Update_App_List_Resp_Success: 011'||to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success: 012';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Update_App_List_Resp_Success : 013'||to_char(l_return_code);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
** Desc: The Update_Approval_List_Response() procedure raises exception
** when the rebuild_approval_list() fails and the approval workflow
** hangs. We need to handle the situation when the rebuild_approval_list()
** fails because of No approver found in order to return the Requisition
** to the preparer.
**
** Changed the procedure Update_Approval_List_Response() to return FAILURE
** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
**
** The Requsition workflow also has been changed to handle the above.
**
** Dependency: poxwfrqa.wft
*/
ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN
resultout:='COMPLETE' || ':' || 'FAILURE';
END IF; -- update success
l_progress := 'Update_App_List_Resp_Success : 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
RAISE E_UPDATE_RESPONSE_FAIL;
WHEN E_UPDATE_RESPONSE_FAIL THEN
l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
'Update_App_List_Resp_Success E_FAILURE',
l_progress,l_return_code,sqlerrm);
sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
'Update_App_List_Resp_Success',l_progress,sqlerrm);
sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
END Update_App_List_Resp_Success;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
** Desc: Added new wf api to insert null action before
** Reserving a Requisition, if the null action does not exists.
** Otherwise the Reserve action is not recorded.
*/
procedure Insert_Res_Action_History(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) IS
l_progress VARCHAR2(100) := '000';
l_progress := 'Insert_Res_Action_History: 001';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Insert_Res_Action_History: 005 - Reserve_Action_History';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
l_progress := 'Insert_Res_Action_History: 999';
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
wf_core.context('PO_APPROVAL_LIST_WF1S','Insert_Res_Action_History',
l_progress,sqlerrm);
l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.INSERT_ACTION_HISTORY');
END Insert_Res_Action_History;