[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pmt.payment_id,
instr.payment_instruction_id,
pmt.payment_amount,
pmt.payment_currency_code,
pmt.payment_status,
pmt.payment_profile_id,
prof.processing_type,
-1, /* pmt_details_len */
-1, /* document_count */
pmt.separate_remit_advice_req_flag,
pmt.paper_document_number
FROM
IBY_PAYMENTS_ALL pmt,
IBY_PAY_INSTRUCTIONS_ALL instr,
IBY_PAYMENT_PROFILES prof
WHERE
instr.payment_instruction_id = p_instr_id AND
instr.payment_instruction_id = pmt.payment_instruction_id AND
instr.payment_profile_id = pmt.payment_profile_id AND
instr.payment_profile_id = prof.payment_profile_id AND
pmt.payment_status = PMT_STATUS_INS_CREATED
/*
* Fix for bug 5198523:
*
* Ordering the payments by payment reference number
* guarantees that the paper document numbers provided
* to these payments downstream also follow the same
* order.
*/
ORDER BY pmt.payment_reference_number ASC
;
SELECT
payment_instruction_id,
payment_profile_id,
process_type,
payment_instruction_status,
payments_complete_code,
generate_sep_remit_advice_flag,
remittance_advice_created_flag,
regulatory_report_created_flag,
bill_payable_flag,
legal_entity_id,
payment_count,
positive_pay_file_created_flag,
print_instruction_immed_flag,
transmit_instr_immed_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
internal_bank_account_id,
pay_admin_assigned_ref_code,
transmission_date,
acknowledgement_date,
comments,
bank_assigned_ref_code,
org_id,
org_type,
payment_date,
payment_currency_code,
payment_service_request_id,
payment_function,
payment_reason_code,
rfc_identifier,
payment_reason_comments,
payment_document_id,
printer_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO
l_pmtInstrRec.payment_instruction_id,
l_pmtInstrRec.payment_profile_id,
l_pmtInstrRec.process_type,
l_pmtInstrRec.payment_instruction_status,
l_pmtInstrRec.payments_complete_code,
l_pmtInstrRec.generate_sep_remit_advice_flag,
l_pmtInstrRec.remittance_advice_created_flag,
l_pmtInstrRec.regulatory_report_created_flag,
l_pmtInstrRec.bill_payable_flag,
l_pmtInstrRec.legal_entity_id,
l_pmtInstrRec.payment_count,
l_pmtInstrRec.positive_pay_file_created_flag,
l_pmtInstrRec.print_instruction_immed_flag,
l_pmtInstrRec.transmit_instr_immed_flag,
l_pmtInstrRec.created_by,
l_pmtInstrRec.creation_date,
l_pmtInstrRec.last_updated_by,
l_pmtInstrRec.last_update_date,
l_pmtInstrRec.last_update_login,
l_pmtInstrRec.object_version_number,
l_pmtInstrRec.internal_bank_account_id,
l_pmtInstrRec.pay_admin_assigned_ref_code,
l_pmtInstrRec.transmission_date,
l_pmtInstrRec.acknowledgement_date,
l_pmtInstrRec.comments,
l_pmtInstrRec.bank_assigned_ref_code,
l_pmtInstrRec.org_id,
l_pmtInstrRec.org_type,
l_pmtInstrRec.payment_date,
l_pmtInstrRec.payment_currency_code,
l_pmtInstrRec.payment_service_request_id,
l_pmtInstrRec.payment_function,
l_pmtInstrRec.payment_reason_code,
l_pmtInstrRec.rfc_identifier,
l_pmtInstrRec.payment_reason_comments,
l_pmtInstrRec.payment_document_id,
l_pmtInstrRec.printer_name,
l_pmtInstrRec.attribute_category,
l_pmtInstrRec.attribute1,
l_pmtInstrRec.attribute2,
l_pmtInstrRec.attribute3,
l_pmtInstrRec.attribute4,
l_pmtInstrRec.attribute5,
l_pmtInstrRec.attribute6,
l_pmtInstrRec.attribute7,
l_pmtInstrRec.attribute8,
l_pmtInstrRec.attribute9,
l_pmtInstrRec.attribute10,
l_pmtInstrRec.attribute11,
l_pmtInstrRec.attribute12,
l_pmtInstrRec.attribute13,
l_pmtInstrRec.attribute14,
l_pmtInstrRec.attribute15
FROM
IBY_PAY_INSTRUCTIONS_ALL
WHERE
payment_instruction_id = p_instruction_id
;
SELECT
ce.payment_instruction_id
INTO
l_instruction_id
FROM
CE_PAYMENT_DOCUMENTS ce
WHERE
ce.payment_document_id = p_pmt_document_id
;
* Now, that we have completed check printing, update
* the payments of the payment instruction with check
* numbers.
*
* Insert the created dummy documents (setup and overflow
* documents) into the database.
*
* Finally, change the status of the payment instruction
* to 'submitted for printing'.
*/
IF (l_ret_status = 0) THEN
performDBUpdates(p_instruction_id,
l_pmtsInInstrTab, l_dummy_pmts_tab,
l_dummy_docs_tab, l_overflow_docs_tab,
l_instr_err_tab, l_err_tokens_tab);
SELECT
count(*),
DOCS.payment_id
FROM
IBY_DOCS_PAYABLE_ALL DOCS, IBY_PAYMENTS_ALL PMTS
WHERE
PMTS.PAYMENT_INSTRUCTION_ID = pmt_instruction_id
AND PMTS.PAYMENT_ID = DOCS.PAYMENT_ID
AND DOCS.document_status = DOC_STATUS_PAY_CREATED
GROUP BY
DOCS.payment_id
;
| updatePaymentInstructions
|
| PURPOSE:
| Performs an update of all created instructions from PLSQL
| table into IBY_PAY_INSTRUCTIONS_ALL table.
|
| The created instructions have already been inserted into
| IBY_PAY_INSTRUCTIONS_ALL after grouping (and before validation).
| So we only need to update certain fields of the instruction
| that have been changed after the grouping was performed.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePaymentInstructions(
p_payInstrTab IN IBY_PAYINSTR_PUB.pmtInstrTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePaymentInstructions';
|| ' were found to update IBY_PAY_INSTRUCTIONS_ALL table.');
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = p_payInstrTab(i).
payment_instruction_status
WHERE
payment_instruction_id = p_payInstrTab(i).payment_instruction_id;
END updatePaymentInstructions;
| insertPaperDocuments
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertPaperDocuments(
p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
p_setupDocsTab IN docsTabType,
p_overflowDocsTab IN overflowDocsTabType
)
IS
l_setup_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPaperDocuments';
|| 'provided for insert. Exiting ..'
);
print_debuginfo(l_module_name, 'Inserting '
|| l_setup_pmts_tab.COUNT
|| ' setup payments.'
);
/* setup payments can be bulk inserted */
FORALL i in l_setup_pmts_tab.FIRST..l_setup_pmts_tab.LAST
INSERT INTO IBY_PAYMENTS_ALL VALUES l_setup_pmts_tab(i);
print_debuginfo(l_module_name, 'Inserting '
|| p_setupDocsTab.COUNT
|| ' setup documents.'
);
/* documents related to setup payments can be bulk inserted */
FORALL i in p_setupDocsTab.FIRST..p_setupDocsTab.LAST
INSERT INTO IBY_DOCS_PAYABLE_ALL VALUES p_setupDocsTab(i);
print_debuginfo(l_module_name, 'Inserting '
|| l_overflow_pmts_tab.COUNT
|| ' overflow payments.'
);
/* overflow payments can be bulk inserted */
FORALL i in l_overflow_pmts_tab.FIRST..l_overflow_pmts_tab.LAST
INSERT INTO IBY_PAYMENTS_ALL VALUES l_overflow_pmts_tab(i);
UPDATE
iby_payments_all overflow
SET
(
overflow.payment_method_code,
overflow.internal_bank_account_id,
overflow.org_id,
overflow.org_type,
overflow.legal_entity_id,
overflow.delivery_channel_code,
overflow.ext_payee_id,
overflow.payment_profile_id,
overflow.payee_party_id,
overflow.party_site_id,
overflow.supplier_site_id,
overflow.payment_reason_code,
overflow.payment_reason_comments,
overflow.payment_date,
overflow.remittance_message1,
overflow.remittance_message2,
overflow.remittance_message3,
overflow.payment_due_date,
overflow.beneficiary_party,
overflow.remit_to_location_id,
overflow.payee_name,
overflow.payee_address1,
overflow.payee_address2,
overflow.payee_address3,
overflow.payee_address4,
overflow.payee_city,
overflow.payee_postal_code,
overflow.payee_state,
overflow.payee_province,
overflow.payee_county,
overflow.payee_country,
overflow.org_name,
overflow.payer_legal_entity_name,
overflow.payee_party_name,
overflow.payer_party_site_name,
overflow.payee_address_concat,
overflow.beneficiary_name,
overflow.payer_party_number,
overflow.payee_party_number,
overflow.payee_alternate_name,
overflow.payee_site_alternate_name,
overflow.payee_supplier_number,
overflow.payee_first_party_reference,
overflow.address_source,
overflow.employee_address_code,
overflow.employee_person_id,
overflow.employee_payment_flag,
overflow.employee_address_id,
overflow.payer_party_id,
overflow.payer_location_id,
overflow.payee_supplier_id,
overflow.payee_supplier_site_name,
overflow.payee_supplier_site_alt_name
) =
(
SELECT
real.payment_method_code,
real.internal_bank_account_id,
real.org_id,
real.org_type,
real.legal_entity_id,
real.delivery_channel_code,
real.ext_payee_id,
real.payment_profile_id,
real.payee_party_id,
real.party_site_id,
real.supplier_site_id,
real.payment_reason_code,
real.payment_reason_comments,
real.payment_date,
real.remittance_message1,
real.remittance_message2,
real.remittance_message3,
real.payment_due_date,
real.beneficiary_party,
real.remit_to_location_id,
real.payee_name,
real.payee_address1,
real.payee_address2,
real.payee_address3,
real.payee_address4,
real.payee_city,
real.payee_postal_code,
real.payee_state,
real.payee_province,
real.payee_county,
real.payee_country,
real.org_name,
real.payer_legal_entity_name,
real.payee_party_name,
real.payer_party_site_name,
real.payee_address_concat,
real.beneficiary_name,
real.payer_party_number,
real.payee_party_number,
real.payee_alternate_name,
real.payee_site_alternate_name,
real.payee_supplier_number,
real.payee_first_party_reference,
real.address_source,
real.employee_address_code,
real.employee_person_id,
real.employee_payment_flag,
real.employee_address_id,
real.payer_party_id,
real.payer_location_id,
real.payee_supplier_id,
real.payee_supplier_site_name,
real.payee_supplier_site_alt_name
FROM
iby_payments_all real
WHERE
real.payment_id = overflow.external_bank_account_id
)
WHERE overflow.payment_id = l_overflow_pmts_tab(i).payment_id
;
* to be updated to account for the overflow payments
* (1 overflow payment = 1 printed void check).
*/
FOR i in p_overflowDocsTab.FIRST..p_overflowDocsTab.LAST LOOP
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
formatting_payment_id =
p_overflowDocsTab(i).format_payment_id
WHERE
document_payable_id = p_overflowDocsTab(i).doc_id;
END insertPaperDocuments;
| performDBUpdates
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performDBUpdates(
p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
payment_instruction_id%TYPE,
x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
x_setupDocsTab IN OUT NOCOPY docsTabType,
x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
x_insErrorsTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
trxnErrTokenTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
* Update the payments table by providing a check number to
* each payment.
*/
updatePmtsWithCheckNumbers(x_pmtsInPmtInstrTab);
* Insert the setup and overflow documents that have been
* created as part of paper payments handing.
*/
insertPaperDocuments(x_dummyPaperPmtsTab, x_setupDocsTab,
x_overflowDocsTab);
* Insert any payment instruction errors into
* IBY_TRANSACTION_ERRORS table.
*/
IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_insErrorsTab,
x_insTokenTab);
END performDBUpdates;
SELECT
payment_document_id,
payment_document_name,
NVL(number_of_setup_documents, 0),
DECODE(attached_remittance_stub_flag,'Y',number_of_lines_per_remit_stub,NULL) number_of_lines_per_remit_stub
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_pmt_doc_id
;
SELECT
COUNT(*)
INTO
x_pmtsInPmtInstrTab(j).document_count
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_id = x_pmtsInPmtInstrTab(j).payment_id
AND
document_status = DOC_STATUS_PAY_CREATED;
* will be used for inserting dummy payments
* into the IBY_DOCS_PAYABLE_ALL and
* IBY_PAYMENTS_ALL tables to account for setup
* and overflow checks.
*/
l_paper_special_docs_rec.payment_id :=
x_pmtsInPmtInstrTab(j).payment_id;
* of dummy payments (to insert into IBY_PAYMENTS_ALL table)
* and corresponding documents (to insert into
* IBY_DOCS_PAYABLE_ALL table).
*/
performSpecialPaperDocHandling(
l_paper_special_docs_tab,
x_dummyPaperPmtsTab,
x_setupDocsTab,
x_overflowDocsTab
);
* finally update the database.
*/
/* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - Start */
FOR p in x_pmtsInPmtInstrTab.FIRST ..
x_pmtsInPmtInstrTab.LAST LOOP
FOR m in l_paper_special_docs_tab.FIRST ..
l_paper_special_docs_tab.LAST LOOP
IF (l_paper_special_docs_tab(m).payment_id
= x_pmtsInPmtInstrTab(p).payment_id) THEN
x_pmtsInPmtInstrTab(p).check_number
:= l_paper_special_docs_tab(m).check_number;
l_paper_special_docs_tab.DELETE(m);
/* Delete this record as its updated,no need to have it in the next time we loop */
EXIT; /* exit out the loop since check_number is unique. */
SELECT
COUNT(*)
INTO
x_pmtsInPmtInstrTab(j).document_count
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_id = x_pmtsInPmtInstrTab(j).payment_id
AND
document_status = DOC_STATUS_PAY_CREATED;
* will be used for inserting dummy payments
* into the IBY_DOCS_PAYABLE_ALL and
* IBY_PAYMENTS_ALL tables to account for setup
* and overflow checks.
*/
l_paper_special_docs_rec.payment_id :=
x_pmtsInPmtInstrTab(j).payment_id;
* of dummy payments (to insert into IBY_PAYMENTS_ALL table)
* and corresponding documents (to insert into
* IBY_DOCS_PAYABLE_ALL table).
*/
/*Special Paper Doc Handling is not required for Electronic type of payments*/
/*performSpecialPaperDocHandling(
l_paper_special_docs_tab,
x_dummyPaperPmtsTab,
x_setupDocsTab,
x_overflowDocsTab
);*/
* finally update the database.
*/
/* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - Start */
FOR p in x_pmtsInPmtInstrTab.FIRST ..
x_pmtsInPmtInstrTab.LAST LOOP
FOR m in l_paper_special_docs_tab.FIRST ..
l_paper_special_docs_tab.LAST LOOP
IF (l_paper_special_docs_tab(m).payment_id
= x_pmtsInPmtInstrTab(p).payment_id) THEN
x_pmtsInPmtInstrTab(p).check_number
:= l_paper_special_docs_tab(m).check_number;
l_paper_special_docs_tab.DELETE(m);
/* Delete this record as its updated,no need to have it in the next time we loop */
EXIT; /* exit out the loop since check_number is unique. */
SELECT
send_to_file_flag
INTO
l_send_to_file_flag
FROM
IBY_PAYMENT_PROFILES
WHERE
payment_profile_id = x_pmtInstrRec.payment_profile_id
;
* Blindly update the payment instruction status
* (see above comment).
*/
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = l_instr_status
WHERE
payment_instruction_id = x_pmtInstrRec.payment_instruction_id;
* Note: This SELECT will lock the underlying base
* table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
* table because we need to update the last_document_number.
*/
SELECT
payment_document_name,
payment_instruction_id
INTO
l_pmt_doc_name,
l_pmt_instr_id
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_payment_doc_id
;
* Note: This SELECT will lock the underlying base
* table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
* table because we need to update the last_document_number.
*
* If document is already locked for single payment,
* NO_DATA_FOUND exception would be thrown.
* Bug - 7499044
*/
BEGIN
SELECT
NVL(last_issued_document_number, 0),
NVL(last_available_document_number, -1),
payment_document_name,
payment_instruction_id
INTO
l_last_used_check_num,
l_last_avail_check_num,
l_pmt_doc_name,
l_pmt_instr_id
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_payment_doc_id
FOR UPDATE SKIP LOCKED
;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_instr_err_rec,
x_instrErrorTab,
x_insTokenTab
);
* paper payments. Therefore, update the last used paper
* stock number in CE_PAYMENT_DOCUMENTS.
*
* That way if another instance of the payment instruction
* creation program is operating concurrently, it will
* be blocked by the SELECT .. FOR UPDATE statement in
* this method.
*
*/
l_anticipated_last_check_num := l_last_used_check_num
+ l_paper_pmts_count;
* not yet have been inserted into the IBY_USED_PAYMENT_DOCS
* table (because the user has not yet confirmed the
* payment).
*
* Therefore, for single payments, when the user provides
* the start number for check numbering, we will have to
* verify that the provided number is unused by checking
* the paper document number on existing payments.
*/
IF (p_user_assgn_num IS NOT NULL) THEN
l_used_flag := isPaperNosUsedOnExistPmt(
p_payment_doc_id,
l_last_used_check_num + 1,
l_anticipated_last_check_num);
* Update the check stock to reflect the latest used
* check number.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_anticipated_last_check_num
WHERE
payment_document_id = p_payment_doc_id
AND
last_issued_document_number < l_anticipated_last_check_num
;
* Update the check stock to reflect the latest used
* check number, and lock the check stock.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_anticipated_last_check_num,
payment_instruction_id = x_pmtInstrRec.payment_instruction_id
WHERE
payment_document_id = p_payment_doc_id
;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_instr_err_rec,
x_instrErrorTab,
x_insTokenTab
);
SELECT
document_payable_id,
payment_id,
payment_currency_code,
payment_function,
formatting_payment_id,
org_id,
org_type
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_id = p_payment_id
ORDER BY
document_payable_id ASC
;
* a. Insert a dummy payment for each setup document that is to be
* printed.
* b. For each inserted dummy payment, insert as many dummy documents
* payable as will fit onto the check stub of the setup document.
* c. Add this dummy payment to the corresponding payment instruction.
*
* Note that setup checks are to be printed once per payment
* instruction (not once per payment).
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Performing setup document handling ..');
* Insert a dummy payment into IBY_PAYMENTS_ALL
* table for each setup document.
*/
/*
* This is a new payment; Get an id for this payment
l_pmt_rec.last_updated_by := fnd_global.user_id;
l_pmt_rec.last_update_date := sysdate;
l_pmt_rec.last_update_login := fnd_global.login_id;
/* this will be used to insert into IBY_PAMENTS table */
x_dummyPaperPmtsTab(x_dummyPaperPmtsTab.COUNT + 1)
:= l_pmt_rec;
* For each dummy payment, insert corresponding dummy
* documents. Insert as many dummy documents as will
* fit into the stub of the printed document.
*/
/*
* Fix for bug 5642449:
*
* Only create dummy documents to stamp on the setup
* check if we know the number of lines per stub.
* Otherwise, simply create the setup checks without
* populating the check stub with document ids (because
* we do not know how many document ids will fit on the
* check stub).
*/
IF (x_paperSpecialTab(i).num_lines_per_stub IS NOT NULL AND
x_paperSpecialTab(i).num_lines_per_stub > 0) THEN
FOR k IN 1 .. x_paperSpecialTab(i).num_lines_per_stub LOOP
/*
* This is a new document; Get an id for this document
l_doc_rec.last_updated_by := fnd_global.user_id;
l_doc_rec.last_update_date := sysdate;
l_doc_rec.last_update_login := fnd_global.login_id;
updateSetupDocsFlagForInstr(x_paperSpecialTab(i).instruction_id,
x_paperSpecialTab);
* b. For each overflow check, insert a dummy payment.
* c. Add this dummy payment to the correspoding payment instruction.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Performing overflow document '
|| 'handling ..');
* Insert a dummy payment into IBY_PAYMENTS_ALL
* table for each set of overflow documents.
*/
/*
* This is a new payment; Get an id for this payment
l_pmt_rec.last_updated_by := fnd_global.user_id;
l_pmt_rec.last_update_date := sysdate;
l_pmt_rec.last_update_login := fnd_global.login_id;
* UPDATE:
* Use external_bank_account_id as the placeholder
* for the related original payment id. We are trying to
* use some field on the payment, that is relatively
* useless, to store some extra information.
*
* The external bank account id is a good candidate
* for this because this field is expected to be
* not used for paper payments anyway. The payee
* party site id field will be overwritten with data
* from the original payment as part of fix for
* bug 6765314.
*
* Even though, we really don't need this link between
* the overflow payment and the original payment
* outside of this package, it would be a good idea to
* persist this information somehow so that we can
* quickly answer the question "What is the original
* payment that this overflow payment is related to?"
*
* Ideally we should have a specific column on the
* payment to store this information, so that we don't
* have these kludges.
*/
l_pmt_rec.external_bank_account_id :=
x_paperSpecialTab(i).payment_id;
* Some documents will need to be updated so that their
* formatting_payment_id is set to the id of the dummy
* payment that we just created.
*
* Originally, all documents will have the same value
* for payment_id and formatting_payment_id. When there
* are overflow documents, then the following will happen:
*
* a. Documents that will fit into one stub will
* contain payment_id same as original document
* id.
*
* b. All documents that will not fit into the stub (a)
* will have to printed on void checks. These will
* be identified because for these documents the
* formatting_payment_id <> the payment id.
*
* So at this point, we have to identify documents
* for this payment that will not fit into the first
* stub and change their formatting_payment_id to the one
* that we generated for the dummy payment. We do this in
* a loop to finish all excess documents.
*
* Example,
* Payment 102 contains 10 documents.
* Check stub contains space for 4 document lines.
*
* Therefore, number of printed checks will be
* (4 docs) + (4 docs) + (2 docs) = 3 checks
* [Void check] [Void check] [Real check]
*
* One check will be the real check, 2 checks are overflow.
* We have already calculated the number of overflow
* checks by the time we come here.
*
* We need to link the documents that will fit into the
* overflow checks to the dummy payments (void checks)
* that we just created.
*/
/*
* Change the format_payment_id of 'n' documents of this
* payment to the dummy payment id, where 'n' = num lines
* that will fit on the stub. These 'n' documents
* will be printed on a voided check.
*/
/*
* Make sure that the end index does not exceed the
* size of the docs array.
*/
/* need to subtract 1 because 'begin index' is 1 based */
IF (l_begin_doc_index +
(x_paperSpecialTab(i).num_lines_per_stub - 1)
> x_overflowDocsTab.COUNT) THEN
l_end_doc_index := x_overflowDocsTab.COUNT;
| updateSetupDocsFlagForInstr
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updateSetupDocsFlagForInstr(
p_instrId IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType
)
IS
BEGIN
/*
* Loop through all the paper payments table. If we find any
* payment instruction that matches the given payment
* instruction, set the 'set up docs' flag for the payment
* instruction to 'Y' to indicate that setup documents
* have already been generated for this payment instruction.
*/
FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
IF (x_paperSpecialTab(i).instruction_id = p_instrId) THEN
x_paperSpecialTab(i).setup_docs_for_instr_finished := 'Y';
END updateSetupDocsFlagForInstr;
| updatePmtsWithCheckNumbers
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePmtsWithCheckNumbers(
p_pmtsInPayInstTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePmtsWithCheckNumbers';
|| 'were provided to update '
|| 'IBY_PAYMENTS_ALL table. Possible data '
|| 'corruption issue.');
* Update the payments. We cannot use bulk update here
* because the bulk update syntax does not allow us to
* reference individual fields of the PL/SQL record.
*
* TBD: Is there any way to optimize this update?
*/
FOR i in p_pmtsInPayInstTab.FIRST..p_pmtsInPayInstTab.LAST LOOP
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Instruction: '
|| p_pmtsInPayInstTab(i).pay_instr_id || ', payment: '
|| p_pmtsInPayInstTab(i).payment_id);
UPDATE
IBY_PAYMENTS_ALL
SET
paper_document_number = p_pmtsInPayInstTab(i).check_number
WHERE
payment_id = p_pmtsInPayInstTab(i).payment_id
AND payment_status = p_pmtsInPayInstTab(i).payment_status;
END updatePmtsWithCheckNumbers;
SELECT
used_document_number
INTO
l_used_paper_doc_number
FROM
IBY_USED_PAYMENT_DOCS
WHERE
payment_document_id = p_payment_doc_id AND
used_document_number = x_paper_doc_num
;
SELECT
req.process_type
INTO
l_process_type
FROM
IBY_PAY_SERVICE_REQUESTS req,
IBY_PAYMENTS_ALL pmt
WHERE
req.payment_service_request_id =
pmt.payment_service_request_id AND
pmt.payment_id = l_pmt_rec.payment_id
;
SELECT
pmt_doc.last_issued_document_number,
pmt_doc.last_available_document_number
INTO
l_last_issued_doc_num,
l_last_avail_doc_num
FROM
CE_PAYMENT_DOCUMENTS pmt_doc
WHERE
pmt_doc.payment_document_id = p_payment_doc_id ;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE EXISTS
(
SELECT
used_document_number
FROM
IBY_USED_PAYMENT_DOCS
WHERE
payment_document_id = p_payment_doc_id AND
document_use <> 'SKIPPED' AND
used_document_number >= p_start_number AND
used_document_number <= p_end_number
)
;
SELECT
pmt.paper_document_number,
pmt.payment_id
INTO
l_test_paper_doc_num,
l_test_pmt_id
FROM
IBY_PAYMENTS_ALL pmt,
IBY_PAY_INSTRUCTIONS_ALL inst
WHERE
pmt.payment_instruction_id = inst.payment_instruction_id AND
inst.payment_document_id = p_payment_doc_id AND
pmt.paper_document_number = l_paper_doc_num
;
* Note: This SELECT will lock the underlying base
* table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
* table because we need to update the last_document_number.
*/
SELECT
payment_document_name,
payment_instruction_id
INTO
l_pmt_doc_name,
l_pmt_instr_id
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_payment_doc_id
;
* Note: This SELECT will lock the underlying base
* table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
* table because we need to update the last_document_number.
*
* If document is already locked for single payment,
* NO_DATA_FOUND exception would be thrown.
* Bug - 7499044
*/
BEGIN
SELECT
NVL(last_issued_document_number, 0),
NVL(last_available_document_number, -1),
payment_document_name,
payment_instruction_id
INTO
l_last_used_check_num,
l_last_avail_check_num,
l_pmt_doc_name,
l_pmt_instr_id
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_payment_doc_id
FOR UPDATE SKIP LOCKED
;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_instr_err_rec,
x_instrErrorTab,
x_insTokenTab
);
* paper payments. Therefore, update the last used paper
* stock number in CE_PAYMENT_DOCUMENTS.
*
* That way if another instance of the payment instruction
* creation program is operating concurrently, it will
* be blocked by the SELECT .. FOR UPDATE statement in
* this method.
*
*/
l_anticipated_last_check_num := l_last_used_check_num
+ l_paper_pmts_count;
* not yet have been inserted into the IBY_USED_PAYMENT_DOCS
* table (because the user has not yet confirmed the
* payment).
*
* Therefore, for single payments, when the user provides
* the start number for check numbering, we will have to
* verify that the provided number is unused by checking
* the paper document number on existing payments.
*/
IF (p_user_assgn_num IS NOT NULL) THEN
l_used_flag := isPaperNosUsedOnExistPmt(
p_payment_doc_id,
l_last_used_check_num + 1,
l_anticipated_last_check_num);
* Update the check stock to reflect the latest used
* check number.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_anticipated_last_check_num
WHERE
payment_document_id = p_payment_doc_id
AND
last_issued_document_number < l_anticipated_last_check_num
;
* Update the check stock to reflect the latest used
* check number, and lock the check stock.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_anticipated_last_check_num,
payment_instruction_id = x_pmtInstrRec.payment_instruction_id
WHERE
payment_document_id = p_payment_doc_id
;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_instr_err_rec,
x_instrErrorTab,
x_insTokenTab
);