[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
p_original_recipient_id IN NUMBER,
p_responder_id IN NUMBER,
p_last_approver IN BOOLEAN,
p_action_code IN VARCHAR2,
p_note IN VARCHAR2,
p_req_header_id IN NUMBER,
p_app_and_fwd_flag IN BOOLEAN );
SELECT object_id,
object_type_code,
object_sub_type_code,
sequence_num,
object_revision_num,
request_id,
program_application_id,
program_date,
program_id,
last_update_date,
employee_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = x_req_header_id
AND sequence_num = x_sequence_num;
SELECT count(*)
INTO x_count
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = x_req_header_id
AND action_code IS NULL;
** code inserts the first NULL row
*/
IF (x_count > 1) THEN
RAISE e_invalid_action;
SELECT max(sequence_num)
INTO x_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = x_req_header_id;
** an implicit forward. We want to update the
** first NULL row in POAH with FORWARD action
*/
IF (x_count = 1) THEN
/*bug 5142600: need to update the note also */
l_note := wf_engine.GetItemAttrText(itemtype=>itemtype,
itemkey=>itemkey,
aname=>'NOTE');
po_forward_sv1.update_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.employee_id,
'FORWARD',
l_note,
fnd_global.user_id,
fnd_global.login_id
);
po_forward_sv1.insert_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.object_sub_type_code,
Recinfo.sequence_num+1,
NULL,
NULL,
x_forward_to_id,
x_approval_path_id,
NULL,
Recinfo.object_revision_num,
NULL, /* offline_code */
Recinfo.request_id,
Recinfo.program_application_id,
Recinfo.program_id,
Recinfo.program_date,
fnd_global.user_id,
fnd_global.login_id);
procedure Update_Action_History(itemtype in varchar2,
itemkey in varchar2,
x_action in varchar2,
x_req_header_id in number,
x_last_approver in boolean,
x_note in varchar2) IS
l_progress VARCHAR2(100) := '000';
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');
SELECT NVL(MAX(wf.notification_id), -9995)
INTO x_notification_id
FROM WF_NOTIFICATIONS WF,
WF_ITEM_ACTIVITY_STATUSES WIAS
WHERE WIAS.ITEM_TYPE = itemtype AND
WIAS.ITEM_KEY = itemkey AND
WIAS.NOTIFICATION_ID = WF.group_id;
SELECT wfn.responder, wfn.recipient_role,
wfn.original_recipient, wfn.more_info_role
INTO l_responder, l_recipient_role,
l_original_recipient, l_more_info_role
FROM wf_notifications wfn
WHERE wfn.notification_id = x_notification_id;
SELECT WFU.ORIG_SYSTEM_ID
INTO l_original_recipient_id
FROM WF_ROLES WFU
WHERE WFU.NAME = l_original_recipient
AND WFU.ORIG_SYSTEM NOT IN ('POS', 'ENG_LIST', 'CUST_CONT');
/* Bug 2893011: Move update history logic to private autonomus procedure. */
UpdateActionHistory(l_more_origsysid, --bug 3090563
l_original_recipient_id,
x_responder_id,
x_last_approver,
x_action_code,
x_note,
x_req_header_id,
l_appr_and_fwd_flag);
END Update_Action_History;
** Desc: Added new procedure to insert null action in
** po_action_history for the Requisition if it does not exists.
*/
procedure Reserve_Action_History(x_approval_path_id in number,
x_req_header_id in number,
x_approver_id in number) IS
pragma AUTONOMOUS_TRANSACTION;
SELECT object_id,
object_type_code,
object_sub_type_code,
sequence_num,
action_code,
object_revision_num,
request_id,
program_application_id,
program_date,
program_id,
last_update_date,
employee_id
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = x_req_header_id
AND sequence_num = x_sequence_num;
SELECT max(sequence_num)
INTO x_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = x_req_header_id;
po_forward_sv1.insert_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.object_sub_type_code,
Recinfo.sequence_num+1,
NULL,
NULL,
x_approver_id,
x_approval_path_id,
NULL,
Recinfo.object_revision_num,
NULL, /* offline_code */
Recinfo.request_id,
Recinfo.program_application_id,
Recinfo.program_id,
Recinfo.program_date,
fnd_global.user_id,
fnd_global.login_id);
* This method is a private method to update the action history in autonomous context.
*/
PROCEDURE UpdateActionHistory(p_more_info_id IN NUMBER,
p_original_recipient_id IN NUMBER,
p_responder_id IN NUMBER,
p_last_approver IN BOOLEAN,
p_action_code IN VARCHAR2,
p_note IN VARCHAR2,
p_req_header_id IN NUMBER,
p_app_and_fwd_flag IN BOOLEAN )
IS
pragma AUTONOMOUS_TRANSACTION;
SELECT PH.ACTION_CODE action_code ,
PH.OBJECT_TYPE_CODE object_type_code ,
PH.OBJECT_SUB_TYPE_CODE object_sub_type_code ,
PH.SEQUENCE_NUM sequence_num ,
PH.OBJECT_REVISION_NUM object_revision_num ,
PH.APPROVAL_PATH_ID approval_path_id ,
PH.REQUEST_ID request_id ,
PH.PROGRAM_APPLICATION_ID program_application_id ,
PH.PROGRAM_DATE program_date ,
PH.PROGRAM_ID program_id ,
PH.LAST_UPDATE_DATE last_update_date ,
PH.OBJECT_ID object_id
FROM
PO_DOCUMENT_TYPES PODT,
PO_REQUISITION_HEADERS PRH,
PO_ACTION_HISTORY PH
WHERE PRH.REQUISITION_HEADER_ID = PH.OBJECT_ID AND
PODT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND
PODT.DOCUMENT_SUBTYPE (+) = PRH.TYPE_LOOKUP_CODE AND
PODT.DOCUMENT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND
PRH.TYPE_LOOKUP_CODE = PH.OBJECT_SUB_TYPE_CODE AND
PRH.requisition_header_id=p_req_header_id and
PH.SEQUENCE_NUM = X_SEQUENCE_NUM;
SELECT max(sequence_num)
INTO x_sequence_num
FROM PO_ACTION_HISTORY
WHERE object_type_code = 'REQUISITION'
AND object_id = p_req_header_id;
po_forward_sv1.insert_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.object_sub_type_code,
Recinfo.sequence_num + 1,
NULL,
NULL,
p_original_recipient_id,
Recinfo.approval_path_id,
NULL,
Recinfo.object_revision_num,
NULL, /* offline_code */
Recinfo.request_id,
Recinfo.program_application_id,
Recinfo.program_id,
Recinfo.program_date,
fnd_global.user_id,
fnd_global.login_id);
** if the ntf has been reassigned, update the original NULL row in POAH
** with action NO ACTION and insert a new row with NULL action
** for the new responder
*/
IF (p_responder_id <> -9996) THEN
/** bug 3090563
** the logic to handle re-assignment is now in post notification function
** so that the update to action history can be viewed
** at the moment of reassignment.
**
** this following is used to handle request for more info:
** 1. at the moment an approver requests for more info,
** action history is updated (performed within post notification)
** 2. if the approver approve/reject the requisition
** before the more info request is responded
** then we need to update the action history
** to reflect 'no action' from the more info role
*/
l_progress := '030';
** update the original NULL row for the original approver with
** action code of 'NO ACTION'
*/
l_progress := '040';
po_forward_sv1.update_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
p_more_info_id,
'NO ACTION',
NULL,
fnd_global.user_id,
fnd_global.login_id
);
** insert a new NULL row into PO_ACTION_HISTORY for
** the new approver
*/
l_progress := '050';
po_forward_sv1.insert_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.object_sub_type_code,
Recinfo.sequence_num + 1,
NULL,
NULL,
p_responder_id,
Recinfo.approval_path_id,
NULL,
Recinfo.object_revision_num,
NULL, /* offline_code */
Recinfo.request_id,
Recinfo.program_application_id,
Recinfo.program_id,
Recinfo.program_date,
fnd_global.user_id,
fnd_global.login_id);
** update pending row of action history with approval action
*/
UPDATE PO_ACTION_HISTORY
SET last_update_date = sysdate,
last_updated_by = fnd_global.user_id, --x_user_id,
last_update_login = fnd_global.login_id, --x_login_id,
action_date = sysdate,
action_code = p_action_code, --x_action_code,
note = l_note, --x_note,
offline_code = NULL
WHERE object_id = Recinfo.object_id
AND object_type_code = Recinfo.object_type_code
AND action_code IS NULL;
po_forward_sv1.insert_action_history (
Recinfo.object_id,
Recinfo.object_type_code,
Recinfo.object_sub_type_code,
Recinfo.sequence_num + 1,
'FORWARD',
sysdate,
p_responder_id,
Recinfo.approval_path_id,
substrb(p_note,1,4000), -- Inserting note in forwarded row
Recinfo.object_revision_num,
NULL, /* offline_code */
Recinfo.request_id,
Recinfo.program_application_id,
Recinfo.program_id,
Recinfo.program_date,
fnd_global.user_id,
fnd_global.login_id);