[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select type_lookup_code
into g_document_subtype
from po_requisition_headers_all
where requisition_header_id = p_document_id;
p_last_update_date OUT NOCOPY VARCHAR2,
p_approval_list_string OUT NOCOPY VARCHAR2,
p_approval_list_count OUT NOCOPY NUMBER,
p_quote_char OUT NOCOPY VARCHAR2,
p_field_delimiter OUT NOCOPY VARCHAR2,
p_return_code OUT NOCOPY NUMBER,
p_error_stack_string OUT NOCOPY VARCHAR2,
p_preparer_can_approve OUT NOCOPY NUMBER,
p_append_saved_approver_flag IN NUMBER DEFAULT NULL,
p_checkout_flow_type IN VARCHAR2 DEFAULT NULL) IS
l_approval_list po_approvallist_s1.ApprovalListType;
l_last_update_date DATE;
p_last_update_date=>l_last_update_date,
p_approval_list=>l_approval_list);
p_last_update_date := to_char(l_last_update_date, g_date_format_mask);
p_last_update_date=>l_last_update_date,
p_approval_list=>l_approval_list);
p_last_update_date := to_char(l_last_update_date, g_date_format_mask);
l_error_stack.delete;
SELECT preparer_id
INTO l_preparer_id
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
SELECT NVL(can_preparer_approve_flag, 'N')
INTO l_can_preparer_approve_flag
FROM po_document_types podt
WHERE podt.document_type_code = g_document_type
AND podt.document_subtype = g_document_subtype;
SELECT preparer_id
INTO l_preparer_id
FROM po_requisition_headers
WHERE requisition_header_id = p_document_id;
l_error_stack.delete;
p_last_update_date IN OUT NOCOPY VARCHAR2,
p_quote_char IN VARCHAR2,
p_field_delimiter IN VARCHAR2,
p_return_code OUT NOCOPY NUMBER,
p_error_stack_string OUT NOCOPY VARCHAR2) IS
l_approval_list po_approvallist_s1.ApprovalListType;
l_last_update_date DATE;
l_last_update_date := NULL;
IF (p_last_update_date = NULL) THEN
RETURN;
l_last_update_date := to_date(p_last_update_date, g_date_format_mask);
p_last_update_date=>l_last_update_date,
p_approval_list_header_id=> p_approval_list_header_id,
p_return_code=>l_return_code,
p_error_stack=>l_error_stack);
select to_char(last_update_date, g_date_format_mask) into p_last_update_date
from po_approval_list_headers
where approval_list_header_id = p_approval_list_header_id;
l_error_stack.delete;
UPDATE po_requisition_headers
SET wf_item_type = 'REQAPPRV',
wf_item_key = l_wf_item_key
WHERE requisition_header_id = p_document_id;
PO_APPROVALLIST_S1.UPDATE_APPROVAL_LIST_ITEMKEY(p_document_id,
'REQAPPRV',
l_wf_item_key,
l_return);
select approver_id,
sequence_num,
notification_id,
notification_role,
responder_id,
forward_to_id,
mandatory_flag,
requires_reapproval_flag,
approver_type,
status,
response_date,
comments
FROM po_approval_list_lines
WHERE approval_list_header_id = p_approval_list_header_id
order by approval_list_line_id;
SELECT po_approval_list_headers_s.nextval
INTO l_new_approval_list_header_id
FROM sys.dual;
SELECT approval_list_header_id, current_sequence_num
INTO l_old_approval_list_header_id, l_old_current_sequence_num
FROM po_approval_list_headers
WHERE document_id = p_existing_requisition_id
AND document_type = 'REQUISITION'
AND latest_revision = 'Y';
DELETE FROM po_approval_list_lines
WHERE approval_list_header_id in
(select approval_list_header_id
FROM po_approval_list_headers
WHERE document_id = p_new_requisition_id
AND document_type = 'REQUISITION');
DELETE FROM po_approval_list_headers
WHERE document_id = p_new_requisition_id
AND document_type = 'REQUISITION';
SELECT COUNT(1)
into l_new_current_sequence_num
FROM po_approval_list_lines
WHERE sequence_num <= l_old_current_sequence_num and
approval_list_header_id = l_old_approval_list_header_id;
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)
SELECT l_new_approval_list_header_id,
p_new_requisition_id,
document_type,
document_subtype,
0, --revision,
decode (l_new_current_sequence_num, 0, current_sequence_num, l_new_current_sequence_num),
latest_revision,
first_approver_id,
approval_path_id,
wf_item_type,
wf_item_key,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
FROM po_approval_list_headers
WHERE document_id = p_existing_requisition_id
AND document_type = 'REQUISITION'
AND latest_revision = 'Y';
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_new_approval_list_header_id,
po_approval_list_lines_s.nextval,
null,
approver_rec.approver_id,
l_sequence_number,
approver_rec.notification_id,
approver_rec.notification_role,
approver_rec.responder_id,
approver_rec.forward_to_id,
approver_rec.mandatory_flag,
approver_rec.requires_reapproval_flag,
approver_rec.approver_type,
approver_rec.status,
approver_rec.response_date,
approver_rec.comments,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE
);
select pal.approver_id
from po_approval_list_lines pal, po_approval_list_headers pah,
po_requisition_headers_all prh
where pal.approval_list_header_id = pah.approval_list_header_id
and pah.latest_revision = 'Y'
and pal.mandatory_flag = 'N'
and approver_type ='USER'
and pal.status is null
and pah.document_id = prh.requisition_header_id
and pah.document_type = 'REQUISITION'
and prh.requisition_header_id = p_document_id
and prh.authorization_status = 'INCOMPLETE'
order by pal.sequence_num asc;