[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE iby_payments_all
SET STOP_REQUEST_PLACED_FLAG = 'Y',
STOP_REQUEST_PLACED_BY = p_requested_by,
STOP_REQUEST_REASON = p_request_reason,
STOP_REQUEST_REFERENCE= p_request_reference,
STOP_REQUEST_DATE = p_request_date,
STOP_RELEASED_FLAG = 'N',
STOP_RELEASED_BY = NULL,
STOP_RELEASE_DATE = NULL,
STOP_RELEASE_REASON = NULL,
STOP_RELEASE_REFERENCE = NULL
WHERE
PAYMENT_ID = p_payment_id;
UPDATE iby_payments_all
SET STOP_RELEASED_FLAG = 'Y',
STOP_RELEASED_BY = p_released_by,
STOP_RELEASE_DATE = p_release_date,
STOP_RELEASE_REASON = p_release_reason,
STOP_RELEASE_REFERENCE = p_release_reference
WHERE
PAYMENT_ID = p_payment_id;
SELECT
IBY_REJECTED_DOCS_GROUP_S.NEXTVAL
INTO
l_rejection_id
FROM
DUAL
;
* Update the removed document with the rejected document
* group id. The calling application will identify rejected
* documents using this id.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
rejected_docs_group_id = l_rejection_id
WHERE
document_payable_id = p_doc_id AND
document_status = p_doc_status
;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_DOCS_PAYABLE_ALL doc
WHERE
fnd.application_id = doc.calling_app_id AND
doc.document_payable_id = p_doc_id
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_REMOVED,
/*
* Fix for bug 4405981:
*
* The straight through flag should be set to 'N',
* if the document was rejected / required manual
* intervention.
*/
straight_through_flag = 'N'
WHERE
payment_id = p_pmt_id
;
SELECT
IBY_REJECTED_DOCS_GROUP_S.NEXTVAL
INTO
l_rejection_id
FROM
DUAL
;
* Update the removed documents with the rejected document
* group id. The calling application will identify rejected
* documents using this id.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
rejected_docs_group_id = l_rejection_id
WHERE
payment_id = p_pmt_id
;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
pmt.payment_id = p_pmt_id
;
SELECT
doc.document_payable_id,
doc.document_status
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_service_request_id = p_payreq_id
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_STOPPED
WHERE
payment_id = p_pmt_id
;
SELECT
IBY_REJECTED_DOCS_GROUP_S.NEXTVAL
INTO
l_rejection_id
FROM
DUAL
;
* Update the removed documents with the rejected document
* group id. The calling application will identify rejected
* documents using this id.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
rejected_docs_group_id = l_rejection_id
WHERE
payment_id = p_pmt_id
;
SELECT
instr.payment_instruction_id
INTO
l_instr_id
FROM
IBY_PAY_INSTRUCTIONS_ALL instr,
IBY_PAYMENTS_ALL pmt
WHERE
instr.payment_instruction_id = pmt.payment_instruction_id AND
pmt.payment_id = p_pmt_id;
SELECT
count(*)
INTO
l_valid_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = l_instr_id AND
pmt.payment_reference_number is not null AND
pmt.payment_status NOT IN
(PAY_STATUS_VOID, PAY_STATUS_REMOVED, PAY_STATUS_STOPPED, PAY_STATUS_REJECTED);
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_TERMINATED
WHERE
payment_instruction_id = l_instr_id;
UPDATE
CE_PAYMENT_DOCUMENTS
SET
payment_instruction_id = NULL,
/* Bug 6707369
* If some of the documents are skipped, the payment
* document's last issued check number must be updated
*/
last_issued_document_number = nvl(
(SELECT MAX(pmt.paper_document_number)
FROM iby_payments_all pmt
WHERE pmt.payment_instruction_id = l_instr_id)
,last_issued_document_number
)
WHERE
payment_instruction_id = l_instr_id;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
pmt.payment_id = p_pmt_id
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_VOID,
voided_by = p_voided_by,
void_date = p_void_date,
void_reason = p_void_reason
WHERE
payment_id = p_pmt_id
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_VOIDED
WHERE
payment_id = p_pmt_id
;
SELECT
instr.payment_instruction_id
INTO
l_instr_id
FROM
IBY_PAY_INSTRUCTIONS_ALL instr,
IBY_PAYMENTS_ALL pmt
WHERE
instr.payment_instruction_id = pmt.payment_instruction_id AND
pmt.payment_id = p_pmt_id
;
SELECT
count(*)
INTO
l_valid_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = l_instr_id AND
pmt.payment_status NOT IN
(
PAY_STATUS_VOID,
PAY_STATUS_REMOVED
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_TERMINATED
WHERE
payment_instruction_id = l_instr_id
;
|| ' updated to TERMINATED because it has no remaining '
|| 'valid payments.'
);
UPDATE
CE_PAYMENT_DOCUMENTS
SET
payment_instruction_id = NULL,
/* Bug 6707369
* If some of the documents are skipped, the payment
* document's last issued check number must be updated
*/
last_issued_document_number = nvl(
(SELECT MAX(pmt.paper_document_number)
FROM iby_payments_all pmt
WHERE pmt.payment_instruction_id = l_instr_id)
,last_issued_document_number
)
WHERE
payment_instruction_id = l_instr_id;
select 'row found in moac synonym'
into l_test
from ap_invoices
where invoice_id = 10045;
select organization_id
into l_org
from ce_security_profiles_v
where organization_type='OPERATING_UNIT';
SELECT
pmt.payment_status
INTO
l_curr_pmt_status
FROM
IBY_PAYMENTS_ALL pmt
WHERE
payment_id = p_pmt_id
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_VOID,
voided_by = p_voided_by,
void_date = p_void_date,
void_reason = p_void_reason
WHERE
payment_id = p_pmt_id
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_VOIDED
WHERE
payment_id = p_pmt_id
;
SELECT
instr.payment_instruction_id
INTO
l_instr_id
FROM
IBY_PAY_INSTRUCTIONS_ALL instr,
IBY_PAYMENTS_ALL pmt
WHERE
instr.payment_instruction_id = pmt.payment_instruction_id AND
pmt.payment_id = p_pmt_id
;
SELECT
count(*)
INTO
l_valid_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = l_instr_id AND
pmt.payment_status NOT IN
(
PAY_STATUS_VOID,
PAY_STATUS_REMOVED
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_TERMINATED
WHERE
payment_instruction_id = l_instr_id
;
|| ' updated to TERMINATED because it has no remaining '
|| 'valid payments.'
);
UPDATE
CE_PAYMENT_DOCUMENTS
SET
payment_instruction_id = NULL,
/* Bug 6707369
* If some of the documents are skipped, the payment
* document's last issued check number must be updated
*/
last_issued_document_number = nvl(
(SELECT MAX(pmt.paper_document_number)
FROM iby_payments_all pmt
WHERE pmt.payment_instruction_id = l_instr_id)
,last_issued_document_number
)
WHERE
payment_instruction_id = l_instr_id;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
pmt.payment_id = p_pmt_id
;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
pmt.payment_id = p_pmt_id
;
SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_instruction_id = p_instr_id AND
payments_complete_flag = 'Y'
;
UPDATE
iby_pay_instructions_all
SET
payment_instruction_status = INS_STATUS_TERMINATED
WHERE
payment_instruction_id = p_instr_id
;
SELECT
NVL(last_issued_document_number, 0),
NVL(first_available_document_num, 0),
NVL(last_available_document_number, -1),
payment_document_name
INTO
l_last_used_check_num,
l_first_avail_check_num,
l_last_avail_check_num,
l_pmt_doc_name
FROM
CE_PAYMENT_DOCUMENTS
WHERE
payment_document_id = p_payment_doc_id
;
| UPDATE ON MAY-04-2006, rameshsh
| This method was not performing a COMMIT earlier. Due to bug 5206672
| a COMMIT has been added in the code before invoking unlock_pmt_entity(..).
*---------------------------------------------------------------------*/
PROCEDURE terminate_pmt_instruction (
p_instr_id IN NUMBER,
p_instr_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.terminate_pmt_instruction';
SELECT DISTINCT
fnd.application_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
pmt.payment_instruction_id = ins.payment_instruction_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
fnd.application_id = req.calling_app_id AND
ins.payment_instruction_id = p_instr_id
;
SELECT
fnd.application_id
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
pmt.payment_instruction_id = ins.payment_instruction_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
fnd.application_id = req.calling_app_id AND
ins.payment_instruction_id = p_instr_id
;
SELECT
count(*)
INTO
l_valid_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = p_instr_id AND
pmt.payment_status NOT IN
(
PAY_STATUS_VOID,
PAY_STATUS_REMOVED,
PAY_STATUS_STOPPED
);
* Update payment instruction, payment and document statuses
* to 'terminated'.
*/
UPDATE
iby_pay_instructions_all
SET
payment_instruction_status = INS_STATUS_TERMINATED
WHERE
payment_instruction_id = p_instr_id
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_INS_TERM
WHERE
payment_instruction_id = p_instr_id AND
(
payment_status <> PAY_STATUS_REMOVED AND
payment_status <> PAY_STATUS_VOID_SETUP AND
payment_status <> PAY_STATUS_VOID_OVERFLOW AND
payment_status <> PAY_STATUS_SPOILED AND
payment_status <> PAY_STATUS_STOPPED AND
payment_status <> PAY_STATUS_INS_TERM AND
payment_status <> PAY_STATUS_REQ_TERM AND
payment_status <> PAY_STATUS_VOID AND
payment_status <> PAY_STATUS_ACK AND
payment_status <> PAY_STATUS_BNK_VALID AND
payment_status <> PAY_STATUS_PAID
)
;
|| 'when attempting to update status of payments '
|| 'for payment instruction '
|| p_instr_id
|| '. Aborting program ..'
);
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_INS_TERM,
/*
* Fix for bug 4405981:
*
* The straight through flag should be set to 'N',
* if the document was rejected / required manual
* intervention.
*/
straight_through_flag = 'N'
WHERE
payment_id IN
(
SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_instruction_id = p_instr_id AND
payment_status = PAY_STATUS_INS_TERM
)
AND
(
document_status <> DOC_STATUS_REJECTED AND
document_status <> DOC_STATUS_REMOVED AND
document_status <> DOC_STATUS_PMT_REMOVED AND
document_status <> DOC_STATUS_PMT_STOPPED AND
document_status <> DOC_STATUS_REQ_TERM AND
document_status <> DOC_STATUS_INS_TERM AND
document_status <> DOC_STATUS_VOID_SETUP AND
document_status <> DOC_STATUS_PMT_VOIDED
)
;
|| 'when attempting to update status of payments '
|| 'for payment instruction '
|| p_instr_id
|| '. Aborting program ..'
);
SELECT last_issued_document_number INTO l_last_issued
FROM ce_payment_documents WHERE payment_instruction_id = p_instr_id;
SELECT Max(paper_document_number), Min(paper_document_number), Count(*)
INTO l_max_paper_document_number, l_min_paper_document_number, l_number_of_payments
FROM iby_payments_all WHERE payment_instruction_id = p_instr_id;
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number = l_last_issued_modified
WHERE
payment_instruction_id = p_instr_id
RETURNING
payment_document_id,
payment_document_name
INTO
l_pmt_doc_id,
l_pmt_doc_name
;
UPDATE
CE_PAYMENT_DOCUMENTS
SET
payment_instruction_id = NULL
WHERE
payment_instruction_id = p_instr_id
RETURNING
payment_document_id,
payment_document_name
INTO
l_pmt_doc_id,
l_pmt_doc_name
;
SELECT
IBY_REJECTED_DOCS_GROUP_S.NEXTVAL
INTO
l_rejection_id
FROM
DUAL
;
* Update the terminated documents for this calling app
* with the rejected document group id. The calling
* application will identify rejected documents using
* this id.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
rejected_docs_group_id = l_rejection_id
WHERE
document_status = DOC_STATUS_INS_TERM AND
calling_app_id = l_appIdsTab(i) AND
payment_id IN
(SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_instruction_id = p_instr_id AND
payment_status = PAY_STATUS_INS_TERM
)
;
* been updated to TERMINATED status and the calling app
* has been informed.
*
* Perform a COMMIT here before calling unlock_pmt_entity(..)
* otherwise a deadlock ensues.
*/
COMMIT;
| UPDATE ON MAY-05-2006, rameshsh
| This method was not performing a COMMIT earlier. Due to bug 5206672
| a COMMIT has been added in the code before invoking unlock_pmt_entity(..).
|
*---------------------------------------------------------------------*/
PROCEDURE terminate_pmt_request (
p_req_id IN NUMBER,
p_req_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.terminate_pmt_request';
UPDATE
iby_pay_service_requests
SET
payment_service_request_status = REQ_STATUS_TERMINATED
WHERE
payment_service_request_id = p_req_id
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_REQ_TERM
WHERE
payment_service_request_id = p_req_id AND
(
payment_status <> PAY_STATUS_REMOVED AND
payment_status <> PAY_STATUS_VOID_SETUP AND
payment_status <> PAY_STATUS_VOID_OVERFLOW AND
payment_status <> PAY_STATUS_SPOILED AND
payment_status <> PAY_STATUS_STOPPED AND
payment_status <> PAY_STATUS_INS_TERM AND
payment_status <> PAY_STATUS_REQ_TERM AND
payment_status <> PAY_STATUS_VOID AND
payment_status <> PAY_STATUS_ACK AND
payment_status <> PAY_STATUS_BNK_VALID AND
payment_status <> PAY_STATUS_PAID
)
;
|| 'when attempting to update status of payments '
|| 'for payment request '
|| p_req_id
|| '. Aborting program ..'
);
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_REQ_TERM,
/*
* Fix for bug 4405981:
*
* The straight through flag should be set to 'N',
* if the document was rejected / required manual
* intervention.
*/
straight_through_flag = 'N'
WHERE
payment_service_request_id = p_req_id AND
(
document_status <> DOC_STATUS_REJECTED AND
document_status <> DOC_STATUS_REMOVED AND
document_status <> DOC_STATUS_PMT_REMOVED AND
document_status <> DOC_STATUS_PMT_STOPPED AND
document_status <> DOC_STATUS_REQ_TERM AND
document_status <> DOC_STATUS_INS_TERM AND
document_status <> DOC_STATUS_VOID_SETUP AND
document_status <> DOC_STATUS_PMT_VOIDED
)
;
|| 'when attempting to update status of documents '
|| 'payable for payment request '
|| p_req_id
|| '. Aborting program ..'
);
SELECT
IBY_REJECTED_DOCS_GROUP_S.NEXTVAL
INTO
l_rejection_id
FROM
DUAL
;
* Update the terminated documents with the rejected document
* group id. The calling application will identify rejected
* documents using this id.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
rejected_docs_group_id = l_rejection_id
WHERE
document_status = DOC_STATUS_REQ_TERM AND
payment_service_request_id = p_req_id
;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAY_SERVICE_REQUESTS req
WHERE
fnd.application_id = req.calling_app_id AND
req.payment_service_request_id = p_req_id
;
* been updated to TERMINATED status and the calling app
* has been informed.
*
* Perform a COMMIT here before calling unlock_pmt_entity(..)
* otherwise a deadlock ensues.
*/
COMMIT;
SELECT
payment_service_request_id,
calling_app_id,
call_app_pay_service_req_code,
payment_service_request_status,
process_type,
allow_zero_payments_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
last_update_login,
internal_bank_account_id,
payment_profile_id,
maximum_payment_amount,
minimum_payment_amount,
document_rejection_level_code,
payment_rejection_level_code,
require_prop_pmts_review_flag,
org_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
create_pmt_instructions_flag,
payment_document_id,
request_id
INTO
l_req_attribs
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
PAYMENT_SERVICE_REQUEST_ID = p_payreq_id
;
| The list of used paper documents numbers will be used to insert
| records into the IBY_USED_PAYMENT_DOCS table.
|
| The list of new paper document numbers will be used to update the
| IBY_PAYMENTS_ALL table with the new paper document numbers for
| the corresponding paments. The payment status will then be set to
| READY_FOR_REPRINT.
|
| Finally, this API will invoke the paper printing flow.
|
| This method should only be invoked for reprinting payment documents
| that are prenumbered (paper stock type is 'prenumbered'). For
| reprinting payment documents that are on blank stock use the method
| reprint_blank_pmt_documents().
|
| PARAMETERS:
| IN
| p_instr_id - ID of the payment instruction, for which some
| payments need to be re-printed.
| p_pmt_doc_id - The payment document id (check stock) which
| is to be used for re-printing.
| p_pmt_list - List of payments that are affected by the
| re-print. These payments will be updated with
| new paper document numbers (provided by the user).
| p_new_ppr_docs_list
| - List of new paper document numbers to print
| the provided payments on.
| p_old_ppr_docs_list
| - List of previously used paper document numbers.
| These will be inserted into IBY_USED_PAYMENT_DOCS
| table indicating that they were spoiled.
| p_printer_name - Printer to use for re-printing payments.
|
|
| OUT
| x_return_status - Result of the API call:
| FND_API.G_RET_STS_SUCCESS indicates that the
| reprint process was triggered successfully.
| In this case the caller must COMMIT
| the status change.
|
| FND_API.G_RET_STS_ERROR (or other) indicates
| that API did not complete successfully.
| In this case, the caller must issue a
| ROLLBACK to undo all status changes.
|
| RETURNS:
|
| NOTES:
| Internal API, not for public use.
|
*---------------------------------------------------------------------*/
PROCEDURE reprint_prenum_pmt_documents(
p_instr_id IN NUMBER,
p_pmt_doc_id IN NUMBER,
p_pmt_list IN pmtIDTab,
p_new_ppr_docs_list IN pmtDocsTab,
p_old_ppr_docs_list IN pmtDocsTab,
p_printer_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.reprint_prenum_pmt_documents';
UPDATE iby_pay_instructions_all ins
SET ins.printer_name = p_printer_name
WHERE ins.payment_instruction_id = p_instr_id;
* Update the IBY_USED_PAYMENT_DOCS table with the list of
* used paper document numbers. Since the user invokes this API
* to reprint the paper documents, it follows that the earlier
* used paper document was spoilt. Therefore, set the status of
* the earlier used paper document to SPOILED.
*/
FOR i IN p_old_ppr_docs_list.FIRST .. p_old_ppr_docs_list.LAST LOOP
print_debuginfo(l_module_name, 'Updating paper doc number '
|| p_old_ppr_docs_list(i)
|| ' of payment document id '
|| p_pmt_doc_id
|| ' to spoiled status ..'
);
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_pmt_doc_id,
p_old_ppr_docs_list(i),
sysdate,
DOC_USE_SPOILED,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
* Update the IBY_PAYMENTS_ALL table with the list of
* new paper document numbers. The payment ids and new
* paper document numbers have been provided in matching
* order. The status of the payment also needs to be
* updated to READY_FOR_REPRINT.
*/
FOR i IN p_new_ppr_docs_list.FIRST .. p_new_ppr_docs_list.LAST LOOP
/*
* Get the current status of the
* payment into l_curr_pmt_status
*/
SELECT
pmt.payment_status
INTO
l_curr_pmt_status
FROM
IBY_PAYMENTS_ALL pmt
WHERE
payment_id = p_pmt_list(i)
;
UPDATE
IBY_PAYMENTS_ALL
SET
paper_document_number = p_new_ppr_docs_list(i),
payment_status = DECODE(
l_curr_pmt_status,
PAY_STATUS_VOID_SETUP,
PAY_STATUS_SETUP_REPRINT,
PAY_STATUS_VOID_OVERFLOW,
PAY_STATUS_OVERFLOW_REPRINT,
PAY_STATUS_REPRINT
)
WHERE
payment_id = p_pmt_list(i) AND
payment_instruction_id = p_instr_id
;
* Update the last issued document number
* in CE_PAYMENT_DOCUMENTS table using the
* greatest document number from the user
* provided list of new document numbers
* for reprinting.
*/
l_last_list_num := -1;
* This update uses an extra security check - we
* only update the last issued doc number if the
* provided list contains a doc number greater
* than what is already stored in the database.
*/
UPDATE
CE_PAYMENT_DOCUMENTS
SET
last_issued_document_number =
GREATEST(l_last_list_num, last_issued_document_number)
WHERE
payment_document_id = p_pmt_doc_id
RETURNING
last_issued_document_number
INTO
l_last_doc_num
;
|| ' updated with last issued doc number set to: '
|| l_last_doc_num
);
| updated to indicate they are ready for reprint.
| p_printer_name - Printer to use for re-printing payments.
|
|
| OUT
| x_return_status - Result of the API call:
| FND_API.G_RET_STS_SUCCESS indicates that the
| reprint process was triggered successfully.
| In this case the caller must COMMIT
| the status change.
|
| FND_API.G_RET_STS_ERROR (or other) indicates
| that API did not complete successfully.
| In this case, the caller must issue a
| ROLLBACK to undo all status changes.
|
| RETURNS:
|
| NOTES:
| Internal API, not for public use.
|
*---------------------------------------------------------------------*/
PROCEDURE reprint_blank_pmt_documents(
p_instr_id IN NUMBER,
p_pmt_list IN pmtIDTab,
p_printer_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.reprint_blank_pmt_documents';
UPDATE iby_pay_instructions_all ins
SET ins.printer_name = p_printer_name
WHERE ins.payment_instruction_id = p_instr_id;
* Update the IBY_PAYMENTS_ALL table with the new status.
* The status of the payment needs to be READY_FOR_REPRINT.
*/
FOR i IN p_pmt_list.FIRST .. p_pmt_list.LAST LOOP
/*
* Get the current status of the
* payment into l_curr_pmt_status
*/
SELECT
pmt.payment_status
INTO
l_curr_pmt_status
FROM
IBY_PAYMENTS_ALL pmt
WHERE
payment_id = p_pmt_list(i)
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = DECODE(
l_curr_pmt_status,
PAY_STATUS_VOID_SETUP,
PAY_STATUS_SETUP_REPRINT,
PAY_STATUS_VOID_OVERFLOW,
PAY_STATUS_OVERFLOW_REPRINT,
PAY_STATUS_REPRINT
)
WHERE
payment_id = p_pmt_list(i) AND
payment_instruction_id = p_instr_id
;
* Update the status of the payments of the given
* instruction to indicate that they must be reprinted.
*/
print_debuginfo(l_module_name, 'Updating statuses of the '
|| 'payments of payment instruction '
|| p_instr_id
|| ' to formatted ..'
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_FORMATTED
WHERE
payment_instruction_id = p_instr_id
;
* Update the status of the payment instruction
* to indicate that it must be re-printed.
*/
print_debuginfo(l_module_name, 'Updating status of '
|| 'payment instruction '
|| p_instr_id
|| ' to formatted - ready for printing ..'
);
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_FORMAT_TO_PRINT
WHERE
payment_instruction_id = p_instr_id
;
* condition is created when we try to update the same
* payment instruction with the concurrent request id
* (for handling intermediate statuses).
*/
COMMIT;
| API will insert the given set of paper document numbers into the
| IBY_USED_PAYMENT_DOCS table along with the usage reason indicating
| whether the paper document was spoiled.
|
| Note that this API should only be invoked with the list of spoiled
| paper documents. The UI should directly handle the successfully
| printed paper documents. For performance reasons, this API is
| designed only to handle the spoiled paper document case.
|
| This API will set the usage reason for each provided document
| to 'SPOILED' in the IBY_USED_PAYMENT_DOCS table, and it will also
| set the status of the corresponding payment to
| REMOVED_DOCUMENT_SPOILED.
|
| Then, this API will set the status of the successfully printed
| payments to ISSUED status and then invoke the 'mark complete'
| API to mark the payments of this payment instruction as complete.
|
| PARAMETERS:
| IN
| p_pmt_doc_id - The payment document id (check stock id)
| of the given list of paper documents.
| p_used_docs_list - The list of paper documents that have been
| used for printing
| p_use_reason_list - The list of paper document usage reasons. This
| list will contain a lookup code that specifies
| whether the paper document was correctly
| printed or not. Possible values include
| ISSUED | SPOILED. SKIPPED will never be a
| provided reason because skipped documents have
| successfully printed (only numbering is wrong).
| p_submit_postive_pay
| - Flag indicating whether positive pay file
| report needs to be launched after finalizing
| the payments.
|
| OUT
| x_return_status - Result of the API call:
| FND_API.G_RET_STS_SUCCESS indicates that the
| finalization process completed raised
| successfully. In this case the caller must
| COMMIT the status change.
|
| FND_API.G_RET_STS_ERROR (or other) indicates
| that API did not complete successfully.
| In this case, the caller must issue a
| ROLLBACK to undo all status changes.
|
| RETURNS:
|
| NOTES:
| Internal API, not for public use.
|
*---------------------------------------------------------------------*/
PROCEDURE finalize_print_status(
p_instr_id IN NUMBER,
p_pmt_doc_id IN NUMBER,
p_used_docs_list IN paperDocNumTab,
p_submit_postive_pay IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.finalize_print_status';
SELECT
doc.document_payable_id,
doc.document_status
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_id = p_pmt_id
;
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_pmt_doc_id,
p_used_docs_list(i),
sysdate,
DOC_USE_SPOILED,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_SPOILED
WHERE
payment_instruction_id = p_instr_id AND
payment_id = p_used_docs_list(i)
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_SPOILED
WHERE
payment_id = p_used_docs_list(i)
;
|| 'when attempting to update payment with payment '
|| 'instruction id '
|| p_instr_id
|| ' and paper document number '
|| l_paper_doc_num
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_ISSUED
WHERE
payment_instruction_id = p_instr_id AND
(
payment_status IN
(
PAY_STATUS_FORMATTED,
PAY_STATUS_SUB_FOR_PRINT
)
OR
(
process_type = PROCESS_TYPE_IMMEDIATE
)
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_PRINTED
WHERE
payment_instruction_id = p_instr_id
;
| API will insert the given set of paper document numbers into the
| IBY_USED_PAYMENT_DOCS table along with the usage reason indicating
| whether the paper document was spoiled.
|
| Note that this API should only be invoked with the list of spoiled
| paper documents. The UI should directly handle the successfully
| printed paper documents. For performance reasons, this API is
| designed only to handle the spoiled paper document case.
|
| This API will set the usage reason for each provided document
| to 'SPOILED' in the IBY_USED_PAYMENT_DOCS table, and it will also
| set the status of the corresponding payment to
| REMOVED_DOCUMENT_SPOILED.
|
| Then, this API will set the status of the successfully printed
| payments to ISSUED status and then invoke the 'mark complete'
| API to mark the payments of this payment instruction as complete.
|
| PARAMETERS:
| IN
| p_pmt_doc_id - The payment document id (check stock id)
| of the given list of paper documents.
| p_used_docs_list - The list of paper documents that have been
| used for printing
| p_use_reason_list - The list of paper document usage reasons. This
| list will contain a lookup code that specifies
| whether the paper document was correctly
| printed or not. Possible values include
| ISSUED | SPOILED. SKIPPED will never be a
| provided reason because skipped documents have
| successfully printed (only numbering is wrong).
| p_submit_postive_pay
| - Flag indicating whether positive pay file
| report needs to be launched after finalizing
| the payments.
|
| OUT
| x_return_status - Result of the API call:
| FND_API.G_RET_STS_SUCCESS indicates that the
| finalization process completed raised
| successfully. In this case the caller must
| COMMIT the status change.
|
| FND_API.G_RET_STS_ERROR (or other) indicates
| that API did not complete successfully.
| In this case, the caller must issue a
| ROLLBACK to undo all status changes.
|
| RETURNS:
|
| NOTES:
| Internal API, not for public use.
|
*---------------------------------------------------------------------*/
PROCEDURE finalize_print_status(
p_instr_id IN NUMBER,
p_pmt_doc_id IN NUMBER,
p_used_docs_list IN paperDocNumTab,
p_used_pmts_list IN paperDocNumTab,
p_submit_postive_pay IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.finalize_print_status';
SELECT
doc.document_payable_id,
doc.document_status
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_id = p_pmt_id
;
SELECT payment_status
INTO l_payment_status_list(i)
FROM IBY_PAYMENTS_ALL
WHERE paper_document_number = p_used_docs_list(i)
AND payment_id = p_used_pmts_list(i)
AND payment_instruction_id = p_instr_id;
SELECT cedocs.used_document_number
INTO l_skipped_document_number
FROM iby_used_payment_docs cedocs
WHERE cedocs.payment_document_id = p_pmt_doc_id
AND cedocs.used_document_number = p_used_docs_list(i)
AND cedocs.document_use = 'SKIPPED';
--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_pmt_doc_id,
p_used_docs_list(i),
sysdate,
decode(l_payment_status_list(i),'REMOVED_DOCUMENT_SPOILED','SPOILED','ISSUED'),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
print_debuginfo(l_module_name, 'Document number not found, inserting new');
UPDATE IBY_USED_PAYMENT_DOCS
SET DOCUMENT_USE = decode(l_payment_status_list(i),'REMOVED_DOCUMENT_SPOILED','SPOILED','ISSUED')
WHERE payment_document_id = p_pmt_doc_id
AND used_document_number = p_used_docs_list(i);
SELECT cedocs.used_document_number
INTO l_skipped_document_number
FROM iby_used_payment_docs cedocs
WHERE cedocs.payment_document_id = p_pmt_doc_id
AND cedocs.used_document_number = i;
print_debuginfo(l_module_name, 'inserting as skipped ' || i);
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_pmt_doc_id,
i,
sysdate,
'SKIPPED',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_SPOILED
WHERE
payment_instruction_id = p_instr_id AND
payment_id = p_used_pmts_list(i)
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_PMT_SPOILED
WHERE
payment_id = p_used_pmts_list(i)
;
|| 'when attempting to update payment with payment '
|| 'instruction id '
|| p_instr_id
|| ' and paper document number '
|| l_paper_doc_num
);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_ISSUED
WHERE
payment_instruction_id = p_instr_id AND
(
payment_status IN
(
PAY_STATUS_FORMATTED,
PAY_STATUS_SUB_FOR_PRINT
)
OR
(
process_type = PROCESS_TYPE_IMMEDIATE
)
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_PRINTED
WHERE
payment_instruction_id = p_instr_id
;
| are part of a payment instruction. This API will insert the paper
| document numbers linked to each payment in the instruction into the
| IBY_USED_PAYMENT_DOCS table along with the usage reason indicating
| that the paper document was successfully printed.
|
| This API should only invoked when *all* the payments that are part
| of a payment instruction have been printed successfully.
|
| This API is a light weight alternative to the finalize_print_status()
| API because the caller does not have to provide the list of used
| payment documents along with the usage reason. This API derives the
| list of used payment documents from the payments on the instruction
| and it sets the usage reason for each payment document as 'issued'.
|
| PARAMETERS:
| IN
| p_instr_id - The payment instruction id for which all
| checks were successfully printed.
|
| p_submit_postive_pay
| - Flag indicating whether positive pay file
| report needs to be launched after finalizing
| the payments.
|
| OUT
| x_return_status - Result of the API call:
| FND_API.G_RET_STS_SUCCESS indicates that the
| finalization process completed raised
| successfully. In this case the caller must
| COMMIT the status change.
|
| FND_API.G_RET_STS_ERROR (or other) indicates
| that API did not complete successfully.
| In this case, the caller must issue a
| ROLLBACK to undo all status changes.
|
| RETURNS:
|
| NOTES:
| Internal API, not for public use.
|
*---------------------------------------------------------------------*/
PROCEDURE finalize_instr_print_status(
p_instr_id IN NUMBER,
p_submit_postive_pay IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME
|| '.finalize_instr_print_status';
SELECT
pmt.paper_document_number
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = p_instr_id AND
(
(
pmt.payment_status = PAY_STATUS_FORMATTED OR
pmt.payment_status = PAY_STATUS_SUB_FOR_PRINT
)
OR
(
pmt.process_type = PROCESS_TYPE_IMMEDIATE
)
)
;
* id also needs to be inserted into the IBY_USED_PAYMENT_DOCUMENTS
* table.
*/
SELECT
payment_document_id
INTO
l_pmt_doc_id
FROM
IBY_PAY_INSTRUCTIONS_ALL
WHERE
payment_instruction_id = p_instr_id
;
* Insert the paper document numbers and the payment
* doc id we picked up in steps (1) and (2) into the
* IBY_USED_PAYMENT_DOCUMENTS table. Set the usage
* reason for each paper document as 'issued'.
*/
FOR i IN l_pmtDocsTab.FIRST .. l_pmtDocsTab.LAST LOOP
BEGIN
-- finding if the document is already skipped earlier
SELECT cedocs.used_document_number
INTO l_skipped_document_number
FROM iby_used_payment_docs cedocs
WHERE cedocs.payment_document_id = l_pmt_doc_id
AND cedocs.used_document_number = l_pmtDocsTab(i)
AND cedocs.document_use = 'SKIPPED';
-- if not skipped earlier, inserting 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 (
l_pmt_doc_id,
l_pmtDocsTab(i),
sysdate,
DOC_USE_ISSUED,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1
);
print_debuginfo(l_module_name, 'Document number not found, inserting new');
UPDATE IBY_USED_PAYMENT_DOCS
SET DOCUMENT_USE = DOC_USE_ISSUED
WHERE payment_document_id = l_pmt_doc_id
AND used_document_number = l_pmtDocsTab(i);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_ISSUED
WHERE
payment_instruction_id = p_instr_id AND
(
payment_status IN
(
PAY_STATUS_FORMATTED,
PAY_STATUS_SUB_FOR_PRINT
)
OR
(
process_type = PROCESS_TYPE_IMMEDIATE
)
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status = INS_STATUS_PRINTED
WHERE
payment_instruction_id = p_instr_id
;
* Added distinct clause to select statement so that duplicate
* calling app ids are eliminated.
*
* Otherwise, the call out could be invoked multiple times
* per calling app.
*/
CURSOR c_app_names (p_instr_id NUMBER)
IS
SELECT DISTINCT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
pmt.payment_instruction_id = ins.payment_instruction_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
fnd.application_id = req.calling_app_id AND
ins.payment_instruction_id = p_instr_id
;
SELECT
fnd.application_id
FROM
FND_APPLICATION fnd,
IBY_PAYMENTS_ALL pmt,
IBY_PAY_SERVICE_REQUESTS req,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
pmt.payment_instruction_id = ins.payment_instruction_id AND
req.payment_service_request_id = pmt.payment_service_request_id AND
fnd.application_id = req.calling_app_id AND
ins.payment_instruction_id = p_instr_id
;
* The processing type is used as a criterion in selecting
* which payments of this payment instruction are updated
* to completed status.
*/
SELECT
prof.processing_type,
inst.payments_complete_code,
inst.process_type
INTO
l_processing_type,
l_pmts_complete_code,
l_process_type
FROM
IBY_PAYMENT_PROFILES prof,
IBY_PAY_INSTRUCTIONS_ALL inst
WHERE
prof.payment_profile_id = inst.payment_profile_id AND
inst.payment_instruction_id = p_instr_id;
UPDATE
IBY_PAYMENTS_ALL
SET
payments_complete_flag = 'Y'
WHERE
payment_instruction_id = p_instr_id AND
payment_status = PAY_STATUS_ISSUED
;
UPDATE
IBY_PAYMENTS_ALL
SET
payments_complete_flag = 'Y'
WHERE
payment_instruction_id = p_instr_id AND
payment_status IN
(
PAY_STATUS_FORMATTED,
PAY_STATUS_TRANSMITTED,
PAY_STATUS_ACK,
PAY_STATUS_BNK_VALID,
PAY_STATUS_PAID
)
;
UPDATE
IBY_PAYMENTS_ALL
SET
payments_complete_flag = 'Y'
WHERE
payment_instruction_id = p_instr_id AND
payment_status IN
(
PAY_STATUS_FORMATTED
)
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payments_complete_code = PMT_COMPLETE_YES
WHERE
payment_instruction_id = p_instr_id
;
SELECT
IBY_COMPLETED_PMTS_GROUP_S.NEXTVAL
INTO
l_completion_id
FROM
DUAL
;
* Update the completed payments for this calling app
* with the completed document group id. The calling
* application will identify completed documents using
* this id.
*/
IF (l_processing_type = P_TYPE_PRINTED) THEN
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.completed_pmts_group_id = l_completion_id
WHERE
pmt.payment_instruction_id = p_instr_id AND
pmt.payments_complete_flag = 'Y' AND
payment_status = PAY_STATUS_ISSUED AND
pmt.payment_id IN
(SELECT
doc.payment_id
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_id = pmt.payment_id AND
doc.calling_app_id = l_appIdsTab(i)
)
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.completed_pmts_group_id = l_completion_id
WHERE
pmt.payment_instruction_id = p_instr_id AND
pmt.payments_complete_flag = 'Y' AND
payment_status IN
(
PAY_STATUS_FORMATTED,
PAY_STATUS_TRANSMITTED,
PAY_STATUS_ACK,
PAY_STATUS_BNK_VALID,
PAY_STATUS_PAID
) AND
pmt.payment_id IN
(SELECT
doc.payment_id
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_id = pmt.payment_id AND
doc.calling_app_id = l_appIdsTab(i)
)
;
* Update the documents of the completed payments with
* the completed document group id. This will allow the
* calling app to select the completed documents directly
* if they so wish.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL doc
SET
doc.completed_pmts_group_id = l_completion_id
WHERE
doc.document_status <> 'REMOVED' AND /* Bug 6388935- removed
document handling */
doc.payment_id IN
(SELECT
pmt.payment_id
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.completed_pmts_group_id = l_completion_id
)
;
SELECT
count(*)
INTO
l_pmt_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.completed_pmts_group_id = l_completion_id
;
SELECT
count(*)
INTO
l_doc_count
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.completed_pmts_group_id = l_completion_id
;
UPDATE
CE_PAYMENT_DOCUMENTS
SET
payment_instruction_id = NULL,
/* Bug 6707369
* If some of the documents are skipped, the payment
* document's last issued check number must be updated
*/
last_issued_document_number = nvl(
(SELECT MAX(pmt.paper_document_number)
FROM iby_payments_all pmt
WHERE pmt.payment_instruction_id = p_instr_id)
,last_issued_document_number
)
WHERE
payment_instruction_id = p_instr_id
RETURNING
payment_document_id,
payment_document_name
INTO
l_pmt_doc_id,
l_pmt_doc_name
;
SELECT
prof.payment_profile_id,
prof.positive_pay_format_code,
sys_prof.automatic_pi_reg_submit,
sys_prof.pi_register_format,
remit.automatic_sra_submit_flag,
remit.remittance_advice_format_code
INTO
l_profile_id,
l_pos_pay_format,
l_auto_pi_reg_submit_flag,
l_pi_reg_format,
l_auto_sra_submit_flag,
l_remit_format_code
FROM
IBY_PAYMENT_PROFILES prof,
IBY_SYS_PMT_PROFILES_B sys_prof,
IBY_PAY_INSTRUCTIONS_ALL inst,
IBY_REMIT_ADVICE_SETUP remit
WHERE
prof.payment_profile_id = inst.payment_profile_id AND
sys_prof.system_profile_code = prof.system_profile_code AND
remit.system_profile_code = prof.system_profile_code AND
inst.payment_instruction_id = p_instr_id;
IBY_FD_POST_PICP_PROGS_PVT.Insert_Transmission_Error(p_instr_id,
p_error_code,
p_error_msg);
SELECT t.token_name, t.token_value, t.lookup_type_source, l.meaning
FROM iby_trxn_error_tokens t, fnd_lookups l
WHERE t.transaction_error_id = (c_trxn_error_id)
AND t.lookup_type_source = l.lookup_type(+)
AND t.token_value = l.lookup_code(+);
SELECT transaction_error_id, error_code
FROM iby_transaction_errors
WHERE transaction_id = p_transaction_id
AND transaction_type = p_transaction_type
AND error_status = 'ACTIVE';
SELECT t.token_name, t.token_value, t.lookup_type_source, l.meaning
FROM iby_trxn_error_tokens t, fnd_lookups l
WHERE t.transaction_error_id = (c_trxn_error_id)
AND t.lookup_type_source = l.lookup_type(+)
AND t.token_value = l.lookup_code(+);
| If that underlying pmt entity has already been updated, but not
| committed a deadlock condition ensues and this API exits with
| error 'ORA-00060: deadlock detected while waiting for resource'.
|
| Therefore, a COMMIT is performed after the payment instruction
| is updated in this method. The Post-PICP API will lock the
| same payment instruction by stamping the conc request id on it.
| This operation does not fail now because the changes made to the
| payment instruction have already been committed.
|
| See bug 5195769 for an example of this deadlock scenario.
|
*---------------------------------------------------------------------*/
PROCEDURE perform_check_print(
p_instruction_id IN NUMBER,
p_pmt_document_id IN NUMBER,
p_printer_name IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_return_message IN OUT NOCOPY VARCHAR2) IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME|| '.perform_check_print';
SELECT
payment_instruction_status
INTO
l_instr_status
FROM
IBY_PAY_INSTRUCTIONS_ALL
WHERE
payment_instruction_id = p_instruction_id
;
* Update the printer associated with this payment instruction
* with the provided printer name. The Post-PICP modules
* will use this printer for printing the checks.
*/
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
printer_name = p_printer_name
WHERE
payment_instruction_id = p_instruction_id
;
* condition is created when we try to update the same
* payment instruction with the concurrent request id
* (for handling intermediate statuses).
*/
COMMIT;
SELECT
used_document_number
INTO
l_used_paper_doc_number
FROM
IBY_USED_PAYMENT_DOCS
WHERE
payment_document_id = p_pmt_document_id AND
used_document_number = p_paper_doc_num AND
document_use <> 'SKIPPED'
;
| insert_conc_request
|
| PURPOSE:
| Inserts a given concurrent request id into the
| IBY_PROCESS_CONC_REQUESTS table for audit purposes.
|
| PARAMETERS:
| IN
| p_object_id The id of the payment entity. This can be
| a payment id, a payment request id or a payment
| instruction id.
|
| p_object_type The type of the payment entity. This can be
| one of the following
| PAYMENT
| PAYMENT_REQUEST
| PAYMENT_INSTRUCTION
|
| p_conc_request_id The concurrent request id.
|
| p_completed_flag Flag indicating whether the concurrent request
| has completed.
|
| OUT
| x_return_status Return status (S, E, U)
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insert_conc_request(
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_conc_request_id IN NUMBER,
p_completed_flag IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME|| '.insert_conc_request';
print_debuginfo(l_module_name, 'Inserting conc request id '
|| p_conc_request_id
|| ' into IBY_PROCESS_CONC_REQUESTS for object id '
|| p_object_id
|| ' with object type '
|| p_object_type
);
INSERT INTO IBY_PROCESS_CONC_REQUESTS
(
OBJECT_ID, /* 1 */
OBJECT_TYPE,
REQUEST_ID,
COMPLETED_FLAG,
CREATED_BY, /* 5 */
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER /* 10 */
)
VALUES
(
p_object_id, /* 1 */
p_object_type,
p_conc_request_id,
p_completed_flag,
fnd_global.user_id, /* 5 */
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
1 /* 10 */
)
;
|| 'inserting conc request id '
|| p_conc_request_id
|| ' into IBY_PROCESS_CONC_REQUESTS for object id '
|| p_object_id
|| ' with object type '
|| p_object_type
|| '.'
);
END insert_conc_request;
| This method also inserts the provided concurrent request into
| IBY_PROCESS_CONC_REQUESTS table for audit purposes.
|
| PARAMETERS:
| IN
| p_object_id The id of the payment entity. This can be
| a payment id, a payment request id or a payment
| instruction id.
|
| p_object_type The type of the payment entity. This can be
| one of the following
| PAYMENT
| PAYMENT_REQUEST
| PAYMENT_INSTRUCTION
|
| p_conc_request_id The concurrent request id to stamp the payment
| entity with. If NULL is provided, the value of
| FND_GLOBAL.CONC_REQUEST_ID will be used.
|
| OUT
| x_return_status Return status (S, E, U)
|
| RETURNS:
|
| NOTES:
| This method is implemented as an autonomous transaction
| so that a COMMIT can be performed on the payment entity
| without side effects on the main transaction.
|
*---------------------------------------------------------------------*/
PROCEDURE lock_pmt_entity(
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_conc_request_id IN NUMBER DEFAULT NULL,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE
IBY_PAY_SERVICE_REQUESTS req
SET
req.request_id = l_conc_request_id
WHERE
req.payment_service_request_id = p_object_id
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL inst
SET
inst.request_id = l_conc_request_id
WHERE
inst.payment_instruction_id = p_object_id
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.request_id = l_conc_request_id
WHERE
pmt.payment_id = p_object_id
;
* Insert concurrent request into audit table.
*/
print_debuginfo(l_module_name, 'Inserting cp into audit table');
insert_conc_request(
p_object_id,
p_object_type,
l_conc_request_id,
'N',
x_return_status
);
|| 'insert_conc_request(..) call = '
|| x_return_status
);
UPDATE
IBY_PAY_SERVICE_REQUESTS req
SET
req.request_id = NULL
WHERE
req.payment_service_request_id = p_object_id
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL inst
SET
inst.request_id = NULL
WHERE
inst.payment_instruction_id = p_object_id
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.request_id = NULL
WHERE
pmt.payment_id = p_object_id
;
select lookup_code
from fnd_lookups
where lookup_type='IBY_PAYMENT_FUNCTIONS';
select count(1)
into l_count
from IBY_USER_PAY_FUNS_GT;
print_debuginfo(l_module_name, 'Exception in select ');
insert into IBY_USER_PAY_FUNS_GT(payment_function)
values(l_func);
select count(1)
into l_function_count
from iby_process_functions pfun
where object_id=p_object_id
and object_type=p_object_type
and pfun.payment_function not in
(select payment_function
from IBY_USER_PAY_FUNS_GT
);
select count(1)
into l_org_count
from iby_process_orgs porg
where object_id=p_object_id
and object_type=p_object_type
and porg.org_id not in
(select organization_id
from ce_security_profiles_v cep
where cep.organization_type=porg.org_type
);
SELECT
COUNT(*)
INTO
l_all_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = p_instr_id
;
SELECT
COUNT(*)
INTO
l_term_pmts_count
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id = p_instr_id
AND
pmt.payment_status = PAY_STATUS_REQ_TERM
;
SELECT
pmt.payment_id
INTO
l_test
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id IS NOT NULL
AND
pmt.payment_service_request_id = p_payreq_id
FOR UPDATE
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
pmt.payment_id
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_instruction_id IS NOT NULL
AND
pmt.payment_service_request_id = p_payreq_id
)
;
* Update the payment instruction and payment statuses
* to final statuses accordingly.
*/
l_flag := checkIfInstrXmitOutsideSystem(p_instr_id);
UPDATE
IBY_PAY_INSTRUCTIONS_ALL inst
SET
inst.payment_instruction_status = INS_STATUS_FORMATTED_ELEC
WHERE
inst.payment_instruction_id = p_instr_id
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.payment_status = PAY_STATUS_FORMATTED
WHERE
pmt.payment_instruction_id = p_instr_id
;
UPDATE
IBY_PAY_INSTRUCTIONS_ALL inst
SET
inst.payment_instruction_status = INS_STATUS_TRANSMITTED
WHERE
inst.payment_instruction_id = p_instr_id
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.payment_status = PAY_STATUS_TRANSMITTED
WHERE
pmt.payment_instruction_id = p_instr_id
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
instr.payment_instruction_id
FROM
IBY_PAY_INSTRUCTIONS_ALL instr,
IBY_PAYMENT_PROFILES prof
WHERE
instr.payment_instruction_id = p_instr_id
AND
prof.payment_profile_id = instr.payment_profile_id
AND
prof.transmit_configuration_id IS NULL
)
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
req.payment_service_request_id
FROM
IBY_PAY_SERVICE_REQUESTS req
WHERE
req.payment_service_request_id = p_object_id
AND
req.request_id IS NOT NULL
)
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
inst.payment_instruction_id
FROM
IBY_PAY_INSTRUCTIONS_ALL inst
WHERE
inst.payment_instruction_id = p_object_id
AND
inst.request_id IS NOT NULL
)
;
SELECT
'TRUE'
INTO
l_test
FROM
DUAL
WHERE
EXISTS
(
SELECT
pmt.payment_id
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.payment_id = p_object_id
AND
pmt.request_id IS NOT NULL
)
;
SELECT
pmt.payment_id
FROM
IBY_PAYMENTS_ALL pmt
WHERE
pmt.completed_pmts_group_id = p_complete_id
;
SELECT
doc.document_payable_id
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.completed_pmts_group_id = p_complete_id
;
select count(1)
into l_doc_function_count
FROM IBY_DOCS_PAYABLE_ALL docs
where docs.document_status IN
( 'REJECTED',
'FAILED_BY_RELATED_DOCUMENT',
'FAILED_BY_REJECTION_LEVEL',
'FAILED_BY_CALLING_APP',
'REMOVED',
'REMOVED_PAYMENT_REMOVED',
'REMOVED_REQUEST_TERMINATED',
'REMOVED_INSTRUCTION_TERMINATED',
'REMOVED_PAYMENT_STOPPED',
'REMOVED_PAYMENT_VOIDED')
AND docs.payment_service_request_id = p_pay_service_request_id
and docs.payment_function not in
(select payment_function
from IBY_USER_PAY_FUNS_GT
);
select count(1)
into l_doc_org_count
FROM IBY_DOCS_PAYABLE_ALL docs
where docs.document_status IN
( 'REJECTED',
'FAILED_BY_RELATED_DOCUMENT',
'FAILED_BY_REJECTION_LEVEL',
'FAILED_BY_CALLING_APP',
'REMOVED',
'REMOVED_PAYMENT_REMOVED',
'REMOVED_REQUEST_TERMINATED',
'REMOVED_INSTRUCTION_TERMINATED',
'REMOVED_PAYMENT_STOPPED',
'REMOVED_PAYMENT_VOIDED')
AND docs.payment_service_request_id = p_pay_service_request_id
and docs.org_id not in
(select organization_id
from ce_security_profiles_v cep
where cep.organization_type=docs.org_type
);
select count(1)
into l_pmt_org_count
FROM iby_payments_all payments
where payments.payment_status IN
('FAILED_BY_REJECTION_LEVEL',
'FAILED_BY_CALLING_APP',
'REMOVED',
'REMOVED_REQUEST_TERMINATED',
'REMOVED_INSTRUCTION_TERMINATED',
'REMOVED_DOCUMENT_SPOILED',
'REMOVED_PAYMENT_STOPPED',
'VOID')
and payments.org_id not in
(select organization_id
from ce_security_profiles_v cep
where cep.organization_type=payments.org_type
);