The following lines contain the word 'select', 'insert', 'update' or 'delete':
select payment_instruction_id,
first_available_document_num,
last_available_document_number,
last_issued_document_number
into l_pmt_instruction_id,
l_first_avail_num,
l_last_avail_num,
l_last_issued_num
from ce_payment_documents
where payment_document_id = p_payment_document_id;
select 1
into l_used
from dual
where exists(
select 'USED'
from IBY_USED_PAYMENT_DOCS
where payment_document_id = p_payment_document_id
and used_document_number between p_from_doc_num and p_to_doc_num
and document_use in ('SPOILED', 'ISSUED', 'UNUSED_VOIDED', 'SETUP', 'VOID' , 'OVERFLOW', 'PRINTED'));
select 1
into l_used
from dual
where exists(
select 'USED'
FROM
iby_payments_all pmt,
iby_pay_instructions_all inst
WHERE
inst.payment_document_id = p_payment_document_id
AND inst.payment_instruction_id = pmt.payment_instruction_id
AND pmt.paper_document_number BETWEEN p_from_doc_num and p_to_doc_num);
SELECT cedocs.used_document_number
INTO l_skipped_document_number
FROM iby_used_payment_docs cedocs
WHERE cedocs.payment_document_id = p_payment_document_id
AND cedocs.used_document_number = (p_from_doc_num + i -1)
AND cedocs.document_use = 'SKIPPED';
UPDATE IBY_USED_PAYMENT_DOCS
SET DOCUMENT_USE = 'UNUSED_VOIDED',
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = fnd_global.login_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
WHERE payment_document_id = p_payment_document_id
AND used_document_number = (p_from_doc_num + i -1)
AND document_use = 'SKIPPED';
print_debuginfo(l_module_name, 'Document number not found, inserting new',G_LEVEL_STATEMENT);
--if the document is not skipped earlier insert new
INSERT INTO IBY_USED_PAYMENT_DOCS (
PAYMENT_DOCUMENT_ID,
USED_DOCUMENT_NUMBER,
DATE_USED,
DOCUMENT_USE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES (
p_payment_document_id,
(p_from_doc_num + i -1),
p_void_date,
'UNUSED_VOIDED',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
UPDATE ce_payment_documents
set last_issued_document_number = p_to_doc_num
where payment_document_id = p_payment_document_id;