[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| inserted into the error table identifying the invalid character.
|
| If no invalid characters are found in the input string, this
| procedure simply returns.
|
| PARAMETERS:
| IN
| p_fieldName - The name of the input field (used for logging)
| p_fieldValue - The value of the input field
| p_isComposite - Indidicates that the field is a composite
| field created by appending multiple individual
| fields. For composite fields, the field value
| will not be printed in the error message.
| p_invalid_chars_list - List of invalid characters that
| should not be present in the field value
| OUT
| x_docErrorRec - Error message record. This should contain the
| document id when coming into this method.
| x_docErrorTab - Error messages table. An error message will be
| added to this table if the input field value
| contains an invalid char.
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE checkForInvalidChars(
p_fieldName IN VARCHAR2,
p_fieldValue IN VARCHAR2,
p_isComposite IN BOOLEAN,
p_validCharsList IN VARCHAR2,
p_invalidCharsList IN VARCHAR2,
x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
docErrorTabType
)
IS
l_error_code VARCHAR2(100);
SELECT translate(p_fieldValue, p_validCharsList, l_val_mask)
INTO l_translated_value
FROM DUAL;
SELECT translate(p_fieldValue, p_invalidCharsList, l_inv_mask)
INTO l_translated_value
FROM DUAL;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
x_docErrorRec, x_docErrorTab);
SELECT
'Y'
INTO p_valid_flag
FROM
IBY_APPLICABLE_PMT_PROFS app1,
IBY_APPLICABLE_PMT_PROFS app2,
IBY_APPLICABLE_PMT_PROFS app3,
IBY_APPLICABLE_PMT_PROFS app4,
IBY_SYS_PMT_PROFILES_B sys_prof,
IBY_ACCT_PMT_PROFILES_B acct_prof
WHERE
/*
* org id is a special case; since each org id has meaning
SELECT
acct_prof.payment_profile_id, 'Y'
INTO p_profile_id, p_valid_flag
FROM
IBY_APPLICABLE_PMT_PROFS app1,
IBY_APPLICABLE_PMT_PROFS app2,
IBY_APPLICABLE_PMT_PROFS app3,
IBY_APPLICABLE_PMT_PROFS app4,
IBY_SYS_PMT_PROFILES_B sys_prof,
IBY_ACCT_PMT_PROFILES_B acct_prof
WHERE
/*
* org id is a special case; since each org id has meaning
* We need to select payment profiles that are applicable to
* given (payment method, org, currency, int bank account).
*
*
* | Profiles |
* | applicable to |
* | given pmt | Profiles applicable to
* | method | given payment currency
* | | /
* | | | /
* | V | L
* | |
* |----------------|--------------------------
* |/ \| Profiles
* | | applicable to
* | Intersection | <-- given
* | | org id and org type
* |\ /|
* |----------------|--------------------------
* | |
* | | .__
* | | |\
* | | \
* | |
* | | Profiles applicable to
* | | given internal bank
* | | account
* | |
* | |
*
* We need the intersection of (profiles applicable to
* a given payment method) and (profiles applicable to
* a given org) and (profiles applicable to given payment
* currency) and (profiles applicable to given internal
* bank account) as shown in the graphic.
*
* Therefore, we need to join with the IBY_APPLICABLE_PMT_PROFS
* four times - once to get the profiles for the method, once to get
* the profiles for the org, and once to get the profiles for the
* currency etc. If we are able to get a non-null intersect for these
* five queries, it means that there is a profile that matches the
* (org, method, currency, bank acct) combination.
*
* If the 'applicable_value_to' is set to NULL, it means that the
* profile is applicable to 'all orgs' | 'all methods' |
* 'all formats' etc., depending upon the applicable_type_code.
* Therefore, we need to factor this condition in the join.
*/
/*
* Redundant joins of the form where a=b=c=a execute faster than
* where a=b=c joins. That's why this select has a redundant join.
*/
CURSOR c_profiles(
p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.
payment_method_code%TYPE,
p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.
payment_currency_code%TYPE,
p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.
internal_bank_account_id%TYPE
)
IS
SELECT
acct_prof.payment_profile_id
FROM
IBY_APPLICABLE_PMT_PROFS app1,
IBY_APPLICABLE_PMT_PROFS app2,
IBY_APPLICABLE_PMT_PROFS app3,
IBY_APPLICABLE_PMT_PROFS app4,
IBY_SYS_PMT_PROFILES_B sys_prof,
IBY_ACCT_PMT_PROFILES_B acct_prof
WHERE
/*
* org id is a special case; since each org id has meaning
SELECT
payment_profile_id,
system_profile_code
FROM
IBY_PAYMENT_PROFILES;
UPDATE
IBY_TRANSACTION_ERRORS err
SET
err.error_status = 'INACTIVE',
err.last_update_date = sysdate,
err.last_updated_by = fnd_global.user_id
WHERE
err.transaction_id = p_trxn_id AND
err.transaction_type = p_trxn_type
;
UPDATE
IBY_TRANSACTION_ERRORS err
SET
err.error_status = 'INACTIVE',
err.last_update_date = sysdate,
err.last_updated_by = fnd_global.user_id
WHERE
err.transaction_id IN
(
SELECT
document_payable_id
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_id = p_trxn_id
)
AND
err.transaction_type = 'DOCUMENT_PAYABLE'
;
* status. The transaction status will get updated to a
* failure status in case the transaction fails validation
* later.
*/
IF (p_trxn_type = 'DOCUMENT_PAYABLE') THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Resetting status of doc'
|| ' id: '
|| p_trxn_id
|| ' to '
|| DOC_STATUS_VALIDATED
);
UPDATE
IBY_DOCS_PAYABLE_ALL doc
SET
doc.document_status = DOC_STATUS_VALIDATED
WHERE
doc.document_payable_id = p_trxn_id
;
UPDATE
IBY_PAYMENTS_ALL pmt
SET
pmt.payment_status = PAY_STATUS_CREATED
WHERE
pmt.payment_id = p_trxn_id
;
|| 'when attempting to update error status to inactive '
|| 'for transaction.'
);
* Delete the existing errors stored against the payments
* of the PPR.
*
* We are going to revalidate these payments, so old errors
* need not be persisted at this point.
*/
DELETE
IBY_TRANSACTION_ERRORS err
WHERE
err.transaction_type = 'PAYMENT' AND
err.transaction_id IN
(
SELECT
payment_id
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = p_payment_request_id AND
payment_status IN
(
PAY_STATUS_CREATED,
PAY_STATUS_MODIFIED,
PAY_STATUS_FAIL_VALID
)
)
;
* Delete existing errors against documents that
* were failed because their parent payment
* failed validation.
*
* Fix for bug 5742548:
*
* When deleting payment errors, remember to
* delete errors on underlying documents payable
* as well.
*/
DELETE
IBY_TRANSACTION_ERRORS err
WHERE
err.transaction_type = 'DOCUMENT_PAYABLE' AND
err.transaction_id IN
(
SELECT
docs.document_payable_id
FROM
IBY_DOCS_PAYABLE_ALL docs
WHERE
docs.document_status = DOC_STATUS_PAY_VAL_FAIL AND
docs.payment_service_request_id = p_payment_request_id
)
;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = PAY_STATUS_CREATED
WHERE
payment_service_request_id = p_payment_request_id AND
payment_status IN
(
PAY_STATUS_MODIFIED,
PAY_STATUS_FAIL_VALID
)
;
* Delete existing errors against documents payable that
* are to be re-validated
*/
DELETE
IBY_TRANSACTION_ERRORS err
WHERE
err.transaction_type = 'DOCUMENT_PAYABLE' AND
err.transaction_id IN
(
SELECT
document_payable_id
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_service_request_id = p_payment_request_id AND
document_status = DOC_STATUS_FAIL_VALID
)
;
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_VALIDATED
WHERE
payment_service_request_id = p_payment_request_id AND
document_status = DOC_STATUS_FAIL_VALID
;
* Delete existing errors against the payment instruction
* that is to be re-validated
*
* There is no need to delete the errors against any
* child elements (like payments) etc. because these
* are untouched by the PICP.
*/
DELETE
IBY_TRANSACTION_ERRORS err
WHERE
err.transaction_type = 'PAYMENT_INSTRUCTION' AND
err.transaction_id = p_instr_id
;