[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| status is updated to 'missing account/profile'.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performAssignments(
p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%type,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.performAssignments';
l_updateDocsRec IBY_ASSIGN_PUB.updateDocAttributesRec;
SELECT document_payable_id,
calling_app_id, --| These seven
calling_app_doc_unique_ref1, --| are used
calling_app_doc_unique_ref2, --| by the
calling_app_doc_unique_ref3, --| calling app
calling_app_doc_unique_ref4, --| to uniquely
calling_app_doc_unique_ref5, --| id a
pay_proc_trxn_type_code, --| document
NVL(internal_bank_account_id, -1), -- Internal bank account id
NVL(payment_profile_id, -1), -- Payment profile id
payment_currency_code,
payment_method_code,
payment_format_code,
org_id,
org_type,
payment_date,
NVL(ext_payee_id, -1) -- payee id
FROM IBY_DOCS_PAYABLE_ALL
WHERE payment_service_request_id = p_payment_request_id
AND (internal_bank_account_id IS NULL OR
payment_profile_id IS NULL)
ORDER BY document_payable_id;
* Populate the updateDocAttributesRec record. This information
* will be used to update all documents in IBY_DOCS_PAYABLE_ALL
* that have this payment request id.
*/
IF (req_int_bank_acct_id IS NOT NULL) THEN
l_updateDocsRec.payment_request_id := p_payment_request_id;
l_updateDocsRec.int_bank_acct_id := req_int_bank_acct_id;
l_updateDocsRec.bank_acct_flag := true;
l_updateDocsRec.payment_request_id := p_payment_request_id;
l_updateDocsRec.pay_profile_id := req_profile_id;
l_updateDocsRec.pay_profile_flag := true;
* then we have all the information we need to update the documents.
* Update the documents and exit.
*/
IF (req_int_bank_acct_id IS NOT NULL AND req_profile_id IS NOT NULL) THEN
/*
* Update IBY_DOCS_PAYABLE_ALL table with the account id and
* profile id from request.
*/
updateDocumentAssignments(l_updateDocsRec);
* Update document and request statuses.
*/
finalizeStatuses(p_payment_request_id, x_return_status);
* Update IBY_DOCS_PAYABLE_ALL table with either the account
* id or the profile id (whichever was available).
*
* Then continue down the process to individually assign
* profile/account to the documents on a case-by-case basis.
*/
updateDocumentAssignments(l_updateDocsRec);
iby_disburse_submit_pub_pkg.delete_docspayTab;
print_debuginfo(l_module_name, 'Inserting values into Cache Structure l_int_bank_accts_tbl ' || l_int_bank_accts_index);
* Add this record to the PLSQL table. We will update the
* PLSQL table outside this loop when all documents have
* been processed.
*/
l_setDocsTab(l_setDocsTab.COUNT + 1) := l_setDocsRec;
* Update document and request statuses.
*/
finalizeStatuses(p_payment_request_id, x_return_status);
iby_disburse_submit_pub_pkg.delete_docspayTab;
* Update the bank account and/or profile for the
* documents for which we were able to come up with
* defaults.
*/
setDocumentAssignments(l_setDocsTab);
* Update the payment request status. This depends upon whether
* all documents in the request have their bank account and profile
* assigned or not.
*
* Internally, this function will call a hook to access an external
* application if all documents have not been completely assigned
* their bank account / profile.
*/
updateRequestStatus(p_payment_request_id, x_return_status);
SELECT call_app_pay_service_req_code,
calling_app_id,
internal_bank_account_id, -- Internal bank account ID
payment_profile_id -- Payment profile ID
INTO x_caPayReqCd,
x_caId,
x_bankAcctId,
x_profileId
FROM IBY_PAY_SERVICE_REQUESTS
WHERE payment_service_request_id = p_payReqId;
| updateDocumentAssignments
|
| PURPOSE:
| Updates the account/profile attributes of documents in the
| payment request using information from the given PLSQL table.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updateDocumentAssignments(
p_updateDocsRec IN IBY_ASSIGN_PUB.updateDocAttributesRec
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.updateDocumentAssignments';
/* Check if account needs to be updated */
IF (p_updateDocsRec.bank_acct_flag = true) THEN
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Internal bank account '
|| 'will be set to '
|| p_updateDocsRec.int_bank_acct_id
|| ' for all documents of payment request '
|| p_updateDocsRec.payment_request_id
);
/* Check if profile needs to be updated */
IF (p_updateDocsRec.pay_profile_flag = true) THEN
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Payment profile '
|| 'will be set to '
|| p_updateDocsRec.pay_profile_id
|| ' for all documents of payment request '
|| p_updateDocsRec.payment_request_id
);
* 1. Both the account and profile need to be updated
* 2. Only the account needs to be updated
* 3. Only the profile needs to be updated
*
* All of these situations will be handled by the SQL
* string below.
*/
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
internal_bank_account_id =
NVL(
p_updateDocsRec.int_bank_acct_id,
internal_bank_account_id
),
payment_profile_id =
NVL(
p_updateDocsRec.pay_profile_id,
payment_profile_id
)
WHERE
payment_service_request_id = p_updateDocsRec.payment_request_id
;
END updateDocumentAssignments;
| Updates the statuses of the documents and the payment request.
|
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE finalizeStatuses(
p_payReqID IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
x_req_status IN OUT NOCOPY VARCHAR2
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.finalizeStatuses';
/* Update document statuses */
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_FULL_ASSIGNED
WHERE
payment_service_request_id = p_payReqID
;
/* Update payment request statuse */
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = REQ_STATUS_FULL_ASSIGNED
WHERE
payment_service_request_id = p_payReqID
;
| Updates the account/profile attributes of individual documents
| using information from the given PLSQL table.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE setDocumentAssignments(
p_setDocAttribsTab IN IBY_ASSIGN_PUB.setDocAttribsTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.setDocumentAssignments';
l_update_acct VARCHAR2(1000);
l_update_prof VARCHAR2(1000);
l_update_str VARCHAR2(2000);
* Exit if no records were provided to update.
*
*/
IF (p_setDocAttribsTab.COUNT = 0) THEN
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'No records were provided. Exiting ..');
l_update_acct := '';
l_update_prof := '';
l_update_acct := 'internal_bank_account_id = '
|| p_setDocAttribsTab(i).int_bank_acct_id;
l_update_prof := 'payment_profile_id = '
|| p_setDocAttribsTab(i).pay_profile_id;
* Update the straight through flag whenever a
* document is missing internal bank account / profile
* or both.
*/
l_status_str := 'document_status = :status, '
|| 'straight_through_flag = :flag';
* 1. Both the account and profile need to be updated
* 2. Only the account needs to be updated
* 3. Only the profile needs to be updated
* 4. Neither account nor profile needs to be updated
*
* Depending upon the situation, form the appropriate
* SQL string.
*/
IF (l_acct_flag = true AND l_prof_flag = true) THEN
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Updating both account and '
|| 'profile');
l_update_str := l_update_acct
|| ', '
|| l_update_prof
|| ', '
|| l_status_str
;
l_update_str := l_update_acct || ', ' || l_status_str;
l_update_str := l_update_prof || ', ' || l_status_str;
* Therefore, update the document status appropriately.
*/
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Updating status of doc '
|| p_setDocAttribsTab(i).doc_id
|| ' to '
|| p_setDocAttribsTab(i).status
);
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = p_setDocAttribsTab(i).status,
straight_through_flag = 'N'
WHERE
document_payable_id = p_setDocAttribsTab(i).doc_id
;
l_sql_str := 'UPDATE IBY_DOCS_PAYABLE_ALL SET '
|| l_update_str
|| ' WHERE document_payable_id = '
|| p_setDocAttribsTab(i).doc_id;
* Dynamic SQL update
*/
EXECUTE IMMEDIATE
l_sql_str
USING
p_setDocAttribsTab(i).status, /* document status */
'N' /* straight through flag */
;
| updateRequestStatus
|
| PURPOSE:
| Updates the payment request status. If all documents have an
| account and a profile (either from the start, or after defaulting)
| then the request status will be 'fully assigned', otherwise
| the request status will be set to 'information required'.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updateRequestStatus(
p_payReqID IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
x_req_status IN OUT NOCOPY VARCHAR2
)
IS
l_unassgnDocsTab IBY_ASSIGN_PUB.unassignedDocsTabType;
'.updateRequestStatus';
SELECT document_payable_id,
payment_currency_code,
org_id,
payment_method_code,
calling_app_id, --| These seven
calling_app_doc_unique_ref1, --| are used
calling_app_doc_unique_ref2, --| by the
calling_app_doc_unique_ref3, --| calling app
calling_app_doc_unique_ref4, --| to uniquely
calling_app_doc_unique_ref5, --| id a
pay_proc_trxn_type_code, --| document
NVL(internal_bank_account_id, -1),
NVL(payment_profile_id, -1)
FROM IBY_DOCS_PAYABLE_ALL
WHERE payment_service_request_id = p_payment_request_id
AND (internal_bank_account_id IS NULL OR
payment_profile_id IS NULL)
ORDER BY document_payable_id;
* If there are no unassigned documents, update the
* status of the payment request to 'ASSIGNMENT_COMPLETE'.
*/
IF (l_unassgnDocsTab.COUNT = 0) THEN
x_req_status := REQ_STATUS_FULL_ASSIGNED;
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = REQ_STATUS_FULL_ASSIGNED
WHERE
payment_service_request_id = p_payreqID
;
print_debuginfo(l_module_name, 'Payment request status updated to '
|| 'ASSIGNMENT_COMPLETE status.');
* Update the documents table with the hook provided
* data.
*/
setDocumentAssignments(l_setDocAttribsTab);
* Update the status of the payment request.
*/
FOR i in l_unassgnDocsTab.FIRST .. l_unassgnDocsTab.LAST LOOP
IF (l_unassgnDocsTab(i).int_bank_acct_id = -1 OR
l_unassgnDocsTab(i).pay_profile_id = -1) THEN
/*
* At least one document in the request does not
* have an assigned bank account / profile.
*/
IF (G_LEVEL_STATEMENT >= G_CUR_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Unassigned documents '
|| 'exist for this payment request.');
GOTO label_update_request_status;
<>
/*
* Update the payment request status appropriately.
*/
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = l_request_status
WHERE
payment_service_request_id = p_payreqID
;
print_debuginfo(l_module_name, 'Payment request status updated to '
|| l_request_status || ' status.');
|| 'attempting to update request status.',
FND_LOG.LEVEL_UNEXPECTED);
END updateRequestStatus;
* This document attributes table will be used to update
* the DB with the hook provided assignments.
*/
FOR i in p_hookAsgnDocsTab.FIRST .. p_hookAsgnDocsTab.LAST LOOP
/* document id */
l_docAttrsRec.doc_id := p_hookAsgnDocsTab(i).document_id;
/* if both attributes are available, update the status */
IF (NVL(p_hookAsgnDocsTab(i).int_bank_acct_id,-1) <> -1 AND
NVL(p_hookAsgnDocsTab(i).pay_profile_id, -1) <> -1) THEN
l_docAttrsRec.status := DOC_STATUS_FULL_ASSIGNED;
SELECT IBY_EVENT_KEY_S.nextval INTO l_event_key
FROM DUAL;
* Select all docs that:
* 1. Have the given pay req id
* 2. Are missing either account or profile (or both)
*
* And create an XML fragment with these documents.
*/
l_xml_clob := getXMLClob(p_payreq_id);
* Select all docs that:
* 1. Have the given pay req id
* 2. Are missing either account or profile (or both)
*/
l_sql := 'SELECT calling_app_id, '
|| 'calling_app_doc_unique_ref1, '
|| 'calling_app_doc_unique_ref2, '
|| 'calling_app_doc_unique_ref3, '
|| 'calling_app_doc_unique_ref4, '
|| 'calling_app_doc_unique_ref5, '
|| 'pay_proc_trxn_type_code, '
|| 'internal_bank_account_id, '
|| 'payment_profile_id '
|| 'FROM IBY_DOCS_PAYABLE_ALL '
|| 'WHERE payment_service_request_id = :payreq_id '
|| 'AND (internal_bank_account_id IS NULL '
|| 'OR payment_profile_id IS NULL)';
* This select will fail if is more than one row
* or no rows. That's perfect because we want to find
* a profile that is linked to exactly one format.
*/
SELECT
NVL(prof.payment_profile_id, -1)
INTO
l_profile_id
FROM
IBY_PAYMENT_PROFILES prof,
IBY_EXTERNAL_PAYEES_ALL payee
WHERE
payee.ext_payee_id = ext_payee_id AND
prof.payment_format_code = payee.payment_format_code
;