[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_approval_list.DELETE;
SELECT preparer_id,
authorization_status
INTO l_preparer_id,
l_authorization_status
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
SELECT NVL(p_approval_path_id, default_approval_path_id),
forwarding_mode_code,
NVL(can_preparer_approve_flag, 'N')
INTO l_approval_path_id,
l_forwarding_mode_code,
l_can_preparer_approve_flag
FROM po_document_types podt
WHERE podt.document_type_code = p_document_type
AND podt.document_subtype = p_document_subtype;
SELECT NVL(use_positions_flag, 'N'),
business_group_id
INTO l_use_positions_flag,
l_business_group_id
FROM financials_system_parameters;
SELECT /*+ LEADING(POEH) */ poeh.superior_id, poeh.superior_level, hrec.full_name
FROM hr_employees_current_v hrec,
po_employee_hierarchies poeh
WHERE poeh.position_structure_id = p_approval_path_id
AND poeh.employee_id = p_employee_id
AND hrec.employee_id = poeh.superior_id
AND poeh.superior_level > 0
UNION ALL
SELECT /*+ LEADING(POEH) */ poeh.superior_id, poeh.superior_level, cwk.full_name
FROM per_cont_workers_current_x cwk,
po_employee_hierarchies poeh
WHERE poeh.position_structure_id = p_approval_path_id
AND poeh.employee_id = p_employee_id
AND cwk.person_id = poeh.superior_id
AND poeh.superior_level > 0
AND nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y'
ORDER BY superior_level, full_name;
p_superior_list.DELETE;
p_superior_list.DELETE;
SELECT pera.supervisor_id
FROM per_assignments_f pera
WHERE trunc(SYSDATE) BETWEEN pera.effective_start_date AND pera.effective_end_date
AND pera.person_id = p_employee_id
AND pera.primary_flag = 'Y'
AND EXISTS
(SELECT '1'
FROM per_people_f PERF, per_assignments_f PERA1
WHERE trunc(sysdate) BETWEEN PERF.effective_start_date
AND PERF.effective_end_date
AND PERF.person_id = PERA.supervisor_id
AND PERA1.person_id = PERF.person_id
AND trunc(SYSDATE) BETWEEN PERA1.effective_start_date
AND PERA1.effective_end_date
AND PERA1.primary_flag = 'Y'
AND EXISTS
(SELECT '1'
FROM per_person_types PPT
WHERE PPT.system_person_type IN ('EMP','EMP_APL','CWK') --
AND PPT.person_type_id = PERF.person_type_id));
p_supervisor_list.DELETE;
p_supervisor_list.DELETE;
/* Bug 2794501. When selecting the records from per_assignments_f only the
records corresponding to assignment_type 'E' should be selected */
CURSOR c_hr_hier(p_employee_id NUMBER, p_business_group_id NUMBER) IS
SELECT pera.supervisor_id
FROM per_assignments_f pera
WHERE
EXISTS
(SELECT '1'
FROM per_people_f PERF, per_assignments_f PERA1
WHERE trunc(sysdate) BETWEEN PERF.effective_start_date
AND PERF.effective_end_date
AND PERF.person_id = PERA.supervisor_id
AND PERA1.person_id = PERF.person_id
AND trunc(SYSDATE) BETWEEN PERA1.effective_start_date
AND PERA1.effective_end_date
AND PERA1.primary_flag = 'Y'
AND PERA1.ASSIGNMENT_TYPE IN ('E','C') --
AND EXISTS
(SELECT '1'
FROM per_person_types PPT
WHERE PPT.system_person_type IN ('EMP','EMP_APL','CWK') --
AND PPT.person_type_id = PERF.person_type_id))
START WITH pera.person_id = p_employee_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
AND pera.primary_flag = 'Y'
AND PERA.ASSIGNMENT_TYPE IN ('E','C') --
CONNECT BY PRIOR pera.supervisor_id = pera.person_id
AND trunc(SYSDATE) BETWEEN pera.effective_start_date
AND pera.effective_end_date
AND pera.primary_flag = 'Y'
AND PERA.ASSIGNMENT_TYPE IN ('E','C'); --
p_supervisor_list.DELETE;
p_supervisor_list.DELETE;
p_last_update_date OUT NOCOPY DATE,
p_approval_list OUT NOCOPY ApprovalListType) IS
CURSOR c_approval_list_lines (p_approval_list_header_id NUMBER) IS
SELECT approval_list_line_id,
sequence_num,
approver_id,
responder_id,
forward_to_id,
status,
response_date,
mandatory_flag,
approver_type
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
ORDER BY sequence_num;
l_last_update_date DATE;
SELECT approval_list_header_id,
NVL(current_sequence_num, 0),
last_update_date
INTO l_approval_list_header_id,
l_current_sequence_num,
l_last_update_date
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND latest_revision = 'Y';
p_approval_list.DELETE;
p_last_update_date := l_last_update_date;
p_approval_list.DELETE;
p_last_update_date := NULL;
p_last_update_date IN DATE,
p_approval_list_header_id IN OUT NOCOPY NUMBER,
p_return_code OUT NOCOPY NUMBER,
p_error_stack OUT NOCOPY ErrorStackType) IS
CURSOR c_lock_approval_list_lines(p_approval_list_header_id NUMBER) IS
SELECT approval_list_line_id
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
FOR UPDATE NOWAIT;
l_old_last_update_date DATE := NULL;
SELECT last_update_date
INTO l_old_last_update_date
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND approval_list_header_id = p_approval_list_header_id
AND latest_revision = 'Y';
IF (l_old_last_update_date <> p_last_update_date) THEN
p_return_code := E_LIST_MODIFIED_SINCE_RETRIEVE;
SELECT approval_list_header_id,
NVL(revision, 0),
NVL(current_sequence_num, 0),
first_approver_id,
approval_path_id,
last_update_date,
wf_item_type,
wf_item_key
INTO l_old_approval_list_header_id,
l_old_revision,
l_old_current_sequence_num,
l_old_first_approver_id,
l_old_approval_path_id,
l_old_last_update_date,
l_old_wf_item_type,
l_old_wf_item_key
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND latest_revision = 'Y'
FOR UPDATE NOWAIT;
(l_old_last_update_date <> p_last_update_date)) THEN
p_return_code := E_LIST_MODIFIED_SINCE_RETRIEVE;
l_old_last_update_date := NULL;
SELECT po_approval_list_headers_s.nextval
INTO l_new_approval_list_header_id
FROM sys.dual;
INSERT INTO po_approval_list_headers (
approval_list_header_id,
document_id,
document_type,
document_subtype,
revision,
current_sequence_num,
latest_revision,
first_approver_id,
approval_path_id,
wf_item_type,
wf_item_key,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
VALUES(
l_new_approval_list_header_id,
p_document_id,
p_document_type,
p_document_subtype,
decode(p_approval_list_header_id, NULL, 1, l_old_revision+1),
decode(p_approval_list_header_id, NULL, NULL, l_old_current_sequence_num),
'Y',
decode(p_approval_list_header_id, NULL, p_first_approver_id, l_old_first_approver_id),
decode(p_approval_list_header_id, NULL, p_approval_path_id, l_old_approval_path_id),
decode(p_approval_list_header_id, NULL, NULL, l_old_wf_item_type),
decode(p_approval_list_header_id, NULL, NULL, l_old_wf_item_key),
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE);
INSERT INTO po_approval_list_lines (
approval_list_header_id,
approval_list_line_id,
next_element_id,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
SELECT l_new_approval_list_header_id,
po_approval_list_lines_s.nextval,
NULL,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
AND sequence_num <= l_old_current_sequence_num;
INSERT INTO po_approval_list_lines (
approval_list_header_id,
approval_list_line_id,
next_element_id,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
SELECT l_new_approval_list_header_id,
po_approval_list_lines_s.nextval,
NULL,
p_approval_list(l_index).approver_id,
p_approval_list(l_index).sequence_num,
NULL,
NULL,
NULL,
NULL,
p_approval_list(l_index).mandatory_flag,
'N',
p_approval_list(l_index).approver_type,
NULL, -- status
NULL, -- response_date
NULL, -- comments
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
FROM sys.dual;
UPDATE po_approval_list_headers
SET latest_revision = 'N',
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND approval_list_header_id = l_old_approval_list_header_id;
SELECT approval_list_line_id
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
FOR UPDATE;
SELECT forward_to_id,
sequence_num
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
AND forward_to_id IS NOT NULL
AND sequence_num <= p_current_sequence_num
ORDER BY sequence_num DESC;
SELECT approver_id,
sequence_num,
approver_type
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
AND approver_type IN ('SYSTEM', 'FORWARD')
AND sequence_num <= p_current_sequence_num
AND approval_list_line_id <> (select min(l2.approval_list_line_id)
from po_approval_list_lines l2
where l2.approval_list_header_id = p_approval_list_header_id)
ORDER BY sequence_num DESC;
SELECT approval_list_line_id,
approver_id,
sequence_num,
approver_type,
status,
mandatory_flag
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
AND sequence_num > NVL(p_current_sequence_num, 0)
ORDER BY sequence_num;
l_old_last_update_date1 DATE;
l_old_last_update_date2 DATE;
l_need_to_update_list BOOLEAN;
l_default_approval_list.DELETE;
l_complete_approval_list.DELETE;
SELECT preparer_id,
NVL(authorization_status, 'INCOMPLETE'),
change_pending_flag,
wf_item_type,
wf_item_key
INTO l_preparer_id,
l_authorization_status,
l_is_request_change_order,
l_old_wf_item_type1,
l_old_wf_item_key1
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
SELECT max(wf_item_type),
max(wf_item_key)
INTO l_old_wf_item_type1,
l_old_wf_item_key1
FROM po_change_requests
WHERE document_header_id = p_document_id
AND document_type = 'REQ'
AND action_type NOT IN ('DERIVED')
AND request_status NOT IN ('ACCEPTED', 'REJECTED');
SELECT approval_list_header_id,
first_approver_id,
approval_path_id,
current_sequence_num,
last_update_date
INTO l_old_approval_list_header_id1,
l_old_first_approver_id,
l_old_approval_path_id,
l_old_current_sequence_num,
l_old_last_update_date1
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND wf_item_key = l_old_wf_item_key1
AND wf_item_type = l_old_wf_item_type1
AND latest_revision = 'Y';
SELECT forward_to_id
INTO l_start_approver_id
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND sequence_num = l_old_current_sequence_num;
SELECT NVL(can_preparer_approve_flag, 'N')
INTO l_can_preparer_approve_flag
FROM po_document_types podt
WHERE podt.document_type_code = p_document_type
AND podt.document_subtype = p_document_subtype;
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND (sequence_num >= l_old_current_sequence_num AND approver_type <> 'SYSTEM' AND approver_type <> 'FORWARD');
l_default_approval_list.DELETE(l_index1);
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND ((sequence_num = l_old_current_sequence_num) OR
(sequence_num >= l_old_current_sequence_num AND approver_type <> 'SYSTEM'));
l_default_approval_list.DELETE(l_index1);
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND sequence_num <= l_old_current_sequence_num;
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND sequence_num <= l_old_current_sequence_num
AND requires_reapproval_flag = 'Y';
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND sequence_num <= l_old_current_sequence_num
AND status in ('APPROVE', 'APPROVE_AND_FORWARD');
l_default_approval_list.DELETE(l_index1);
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id1
AND approver_id = l_default_approval_list(l_index1).approver_id
AND sequence_num <= l_old_current_sequence_num
AND status in ('FORWARD');
l_default_approval_list.DELETE(l_index1);
l_default_approval_list.DELETE(l_index1);
SELECT approval_list_header_id,
NVL(revision, 0),
NVL(current_sequence_num, 0),
first_approver_id,
approval_path_id,
last_update_date,
wf_item_type,
wf_item_key
INTO l_old_approval_list_header_id2,
l_old_revision,
l_old_current_sequence_num,
l_old_first_approver_id,
l_old_approval_path_id,
l_old_last_update_date2,
l_old_wf_item_type2,
l_old_wf_item_key2
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND wf_item_type = l_old_wf_item_type1
AND wf_item_key = l_old_wf_item_key1
AND latest_revision = 'Y'
FOR UPDATE;
(l_old_last_update_date1 <> l_old_last_update_date2) THEN
ROLLBACK TO REBUILD_APPROVAL_LIST;
SELECT po_approval_list_headers_s.nextval
INTO l_new_approval_list_header_id
FROM sys.dual;
INSERT INTO po_approval_list_headers (
approval_list_header_id,
document_id,
document_type,
document_subtype,
revision,
current_sequence_num,
latest_revision,
first_approver_id,
approval_path_id,
wf_item_type,
wf_item_key,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
VALUES(
l_new_approval_list_header_id,
p_document_id,
p_document_type,
p_document_subtype,
l_old_revision+1,
l_old_current_sequence_num,
'Y',
l_old_first_approver_id,
l_old_approval_path_id,
l_old_wf_item_type2,
l_old_wf_item_key2,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE);
INSERT INTO po_approval_list_lines (
approval_list_header_id,
approval_list_line_id,
next_element_id,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
SELECT l_new_approval_list_header_id,
po_approval_list_lines_s.nextval,
NULL, -- next_element_id
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
decode(p_rebuild_code, 'DOCUMENT_CHANGED',
decode(status, 'APPROVE', 'Y', 'APPROVE_AND_FORWARD', 'Y', requires_reapproval_flag),
requires_reapproval_flag),
approver_type,
status,
response_date,
comments,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id2
AND sequence_num <= l_old_current_sequence_num;
SELECT NVL(max(sequence_num), 0)
INTO l_max_sequence_num
FROM po_approval_list_lines
WHERE approval_list_header_id = l_new_approval_list_header_id;
INSERT INTO po_approval_list_lines (
approval_list_header_id,
approval_list_line_id,
next_element_id,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
SELECT l_new_approval_list_header_id,
po_approval_list_lines_s.nextval,
NULL, -- next_element_id
l_complete_approval_list(l_index1).approver_id,
l_complete_approval_list(l_index1).sequence_num,
NULL, -- notification_id
NULL, -- notification_role
NULL, -- responder_id
NULL, -- forward_to_id
l_complete_approval_list(l_index1).mandatory_flag,
'N',
l_complete_approval_list(l_index1).approver_type,
NULL, -- status
NULL, -- response_date
NULL, -- comments
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
FROM sys.dual;
UPDATE po_approval_list_headers
SET latest_revision = 'N',
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND approval_list_header_id = l_old_approval_list_header_id2;
UPDATE po_approval_list_headers
SET last_update_date = SYSDATE
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND approval_list_header_id = l_new_approval_list_header_id;
SELECT 'Y'
INTO l_flag
FROM wf_users
WHERE orig_system = 'PER' and orig_system_id = p_approver_id and rownum=1;
SELECT approval_list_line_id
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
FOR UPDATE;
SELECT approver_id,
sequence_num,
approver_type
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
AND sequence_num > p_current_sequence_num
ORDER BY sequence_num;
SELECT approval_list_header_id,
NVL(current_sequence_num, 0),
wf_item_type,
wf_item_key
INTO l_old_approval_list_header_id,
l_old_current_sequence_num,
l_old_wf_item_type,
l_old_wf_item_key
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND latest_revision = 'Y'
FOR UPDATE;
** before we update the po_approval_list_headers
*/
IF (l_old_current_sequence_num > 0 ) THEN
SELECT approver_id
INTO l_current_approver_id
FROM po_approval_list_lines
WHERE approval_list_header_id = l_old_approval_list_header_id
AND sequence_num = l_old_current_sequence_num;
UPDATE po_approval_list_headers
SET current_sequence_num = l_sequence_num,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND approval_list_header_id = l_old_approval_list_header_id;
SELECT approval_list_header_id
INTO p_approval_list_header_id
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND latest_revision = 'Y'
AND ((wf_item_type IS NULL AND p_itemtype IS NULL) OR
(wf_item_type = p_itemtype))
AND ((wf_item_key IS NULL AND p_itemkey IS NULL) OR
(wf_item_key = p_itemkey));
PROCEDURE update_approval_list_itemkey(p_approval_list_header_id IN NUMBER,
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_return_code OUT NOCOPY NUMBER) IS
l_progress VARCHAR2(10) := '000';
UPDATE po_approval_list_headers
SET wf_item_type = p_itemtype,
wf_item_key = p_itemkey,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE approval_list_header_id = p_approval_list_header_id;
END update_approval_list_itemkey;
PROCEDURE update_approval_list_response(p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_document_subtype IN VARCHAR2,
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_approver_id IN NUMBER,
p_responder_id IN NUMBER,
p_forward_to_id IN NUMBER,
p_response IN VARCHAR2,
p_response_date IN DATE,
p_comments IN VARCHAR2,
p_return_code OUT NOCOPY NUMBER) IS
pragma AUTONOMOUS_TRANSACTION;
SELECT approval_list_header_id,
NVL(current_sequence_num, 0)
INTO l_approval_list_header_id,
l_current_sequence_num
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND wf_item_type = p_itemtype
AND wf_item_key = p_itemkey
AND latest_revision = 'Y'
FOR UPDATE;
SELECT approval_list_line_id
INTO l_approval_list_line_id
FROM po_approval_list_lines
WHERE approval_list_header_id = l_approval_list_header_id
AND approver_id = p_approver_id
AND sequence_num = l_current_sequence_num
FOR UPDATE;
* If there is no line to update. Add a new one
*/
l_line_found := FALSE;
UPDATE po_approval_list_lines
SET status = p_response,
forward_to_id = p_forward_to_id,
responder_id = p_responder_id,
response_date = p_response_date,
comments = substrb(p_comments,1,480),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE approval_list_line_id = l_approval_list_line_id;
UPDATE po_approval_list_headers
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE approval_list_header_id = l_approval_list_header_id;
INSERT INTO po_approval_list_lines (
approval_list_header_id,
approval_list_line_id,
next_element_id,
approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
VALUES
(
l_approval_list_header_id,
po_approval_list_lines_s.nextval,
NULL,
p_responder_id,
1,
null,
null,
p_responder_id,
p_forward_to_id,
'N',
'N',
'FORWARD',
'FORWARD',
SYSDATE,
'',
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
);
UPDATE po_approval_list_headers
SET current_sequence_num = 1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE approval_list_header_id = l_approval_list_header_id;
ROLLBACK TO UPDATE_RESPONSE;
END update_approval_list_response;
SELECT approval_list_header_id,
NVL(current_sequence_num, 0)
INTO l_approval_list_header_id,
l_current_sequence_num
FROM po_approval_list_headers
WHERE document_id = p_document_id
AND document_type = p_document_type
AND document_subtype = p_document_subtype
AND wf_item_type = p_itemtype
AND wf_item_key = p_itemkey
AND latest_revision = 'Y';
SELECT COUNT(*)
INTO l_count
FROM po_approval_list_lines
WHERE approval_list_header_id = l_approval_list_header_id
AND sequence_num > l_current_sequence_num;