[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| performDBUpdates
|
| PURPOSE:
| Updates the status of the payment request and documents of the
| payment request.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performDBUpdates(
p_pay_service_request_id
IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%type,
p_allDocsTab IN docPayTabType,
x_errorDocsTab IN OUT NOCOPY docStatusTabType,
p_allDocsSuccessFlag IN BOOLEAN,
p_allDocsFailedFlag IN BOOLEAN,
p_rejectionLevel IN VARCHAR2,
x_txnErrorsTab IN OUT NOCOPY docErrorTabType,
x_errTokenTab IN OUT NOCOPY trxnErrTokenTabType,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_request_status VARCHAR2(200);
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
* Update the status of the invalid documents
*/
IF (x_errorDocsTab.COUNT > 0) THEN
FOR i in x_errorDocsTab.FIRST..x_errorDocsTab.LAST LOOP
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = x_errorDocsTab(i).doc_status,
/*
* Fix for bug 4405981:
*
* The straight through flag should be set to 'N',
* if the document was rejected / required manual
* intervention.
*/
straight_through_flag =
DECODE(
x_errorDocsTab(i).doc_status,
DOC_STATUS_REJECTED, 'N',
DOC_STATUS_RELN_FAIL, 'N',
DOC_STATUS_FAIL_BY_REJLVL, 'N',
DOC_STATUS_FAIL_BY_CA, 'N',
DOC_STATUS_REMOVED, 'N',
'Y'
)
WHERE
document_payable_id = x_errorDocsTab(i).doc_id
AND
payment_service_request_id = p_pay_service_request_id;
/* Update the status of the valid documents */
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
document_status = DOC_STATUS_VALIDATED
WHERE
document_status NOT IN
(
DOC_STATUS_REJECTED,
DOC_STATUS_RELN_FAIL,
DOC_STATUS_FAIL_BY_REJLVL,
DOC_STATUS_FAIL_VALID,
DOC_STATUS_FAIL_BY_CA,
DOC_STATUS_REMOVED
) AND
payment_service_request_id = p_pay_service_request_id;
* documents in a PLSQL table. Use this to update the
* IBY_TRANSACTION_ERRORS table.
*/
insert_transaction_errors('N', x_txnErrorsTab, x_errTokenTab);
* Finally, update the status of the payment request.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Updating status of payment request '
|| p_pay_service_request_id || ' to ' || l_request_status || '.');
UPDATE
IBY_PAY_SERVICE_REQUESTS
SET
payment_service_request_status = l_request_status
WHERE
payment_service_request_id = p_pay_service_request_id;
END performDBUpdates;
SELECT docs.document_payable_id,
docs.calling_app_doc_unique_ref1,
docs.calling_app_doc_unique_ref2,
docs.calling_app_doc_unique_ref3,
docs.calling_app_doc_unique_ref4,
docs.calling_app_doc_unique_ref5,
docs.calling_app_doc_ref_number,
docs.calling_app_id,
docs.pay_proc_trxn_type_code,
docs.payment_grouping_number,
docs.ext_payee_id,
docs.payment_profile_id,
docs.org_id,
docs.org_type,
docs.payment_method_code,
docs.payment_format_code,
docs.payment_currency_code,
docs.internal_bank_account_id,
docs.external_bank_account_id,
docs.payment_date,
docs.payee_party_id,
docs.supplier_site_id,
docs.party_site_id,
docs.payment_function,
docs.affects_rejection_level --AWT Enh 16296267
FROM
IBY_DOCS_PAYABLE_ALL docs
WHERE
docs.payment_service_request_id = p_pay_service_request_id
AND docs.document_status IN
(
DOC_STATUS_RDY_FOR_VAL,
DOC_STATUS_FAIL_VALID,
DOC_STATUS_FAIL_BY_REJLVL,
DOC_STATUS_RELN_FAIL,
DOC_STATUS_VALIDATED
);
SELECT DISTINCT
p_document_payable_id,
p_payment_grouping_number,
p_ext_payee_id,
val.validation_set_code,
val.validation_code_package,
val.validation_code_entry_point,
val_options.validation_assignment_id,
val_options.val_assignment_entity_type,
val.validation_set_display_name
FROM
IBY_VALIDATION_SETS_VL val,
IBY_VAL_ASSIGNMENTS val_options
WHERE
val.validation_set_code = val_options.validation_set_code
AND val.validation_level_code = 'DOCUMENT'
AND (val_options.val_assignment_entity_type = 'METHOD'
AND val_options.assignment_entity_id =
p_payment_method_code
OR val_options.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_options.assignment_entity_id =
to_char(p_int_bank_acct_id)
OR val_options.val_assignment_entity_type = 'FORMAT'
AND val_options.assignment_entity_id =
p_payment_format_code
OR val_options.val_assignment_entity_type = 'BANK'
AND val_options.assignment_entity_id =
to_char(p_bepid)
OR val_options.val_assignment_entity_type = 'TRANSPROTOCOL'
AND val_options.assignment_entity_id =
p_transmit_protocol_code
)
AND NVL(val_options.inactive_date, sysdate+1) > sysdate
/*
* Fix for bug 4997133:
*
* Select validation sets that have the same payment method
* code as the document, or have payment method code as null.
*
* Payment method code null implies that the validation
* set is applicable to all payment methods.
*/
AND (NVL(p_payment_method_code, '0') =
NVL(val_options.payment_method_code, '0') OR
val_options.payment_method_code IS NULL
)
/*
* Fix for bug 4997133:
*
* Select validation sets that have the same country code
* code as the document, or have country code as null.
*
* Country code null implies that the validation
* set is applicable to all countries.
*/
AND (p_country = val_options.territory_code OR
val_options.territory_code IS NULL
)
;
/* Call initDocDataForPPR to insert Documents data
* to temporary table
*/
initDocDataForPPR(p_pay_service_request_id);
* once per document, delete all the error messages linked
* to the failed documents of this PPR, and reset the document status
* of the failed documents in one shot.
*/
IBY_BUILD_UTILS_PKG.resetDocumentErrors(p_pay_service_request_id);
SELECT ieba.ext_bank_account_id,
ieba.country_code,
ieba.end_date,
ieba.foreign_payment_use_flag
INTO l_ext_bank_acct_id,
l_country_code,
l_end_date,
l_foreign_pmts_ok_flag
FROM IBY_EXT_BANK_ACCOUNTS ieba
WHERE ieba.ext_bank_account_id = l_docs_tab(i).ext_bank_acct_id;
SELECT cba.bank_account_id,
cb.country,
cb.bank_home_country
INTO l_int_bank_acct_id,
l_country,
l_bank_home_country
FROM CE_BANK_ACCOUNTS cba, CE_BANK_BRANCHES_V cb
WHERE cba.bank_branch_id = cb.branch_party_id
AND cba.bank_account_id = l_docs_tab(i).int_bank_acct_id;
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
select 'TRUE' into l_valid_assoc from dual where exists(
SELECT 'valid_assoc' FROM iby_pmt_instr_uses_all
WHERE instrument_type = 'BANKACCOUNT'
AND INSTRUMENT_ID = l_docs_tab(i).ext_bank_acct_id
AND payment_flow = 'DISBURSEMENTS'
AND EXT_PMT_PARTY_ID IN (
SELECT EXT_PAYEE_ID FROM IBY_EXTERNAL_PAYEES_ALL
WHERE PAYEE_PARTY_ID = l_docs_tab(i).payee_party_id
AND(
(supplier_site_id = l_docs_tab(i).supplier_site_id AND nvl(party_site_id,-99) = nvl(l_docs_tab(i).party_site_id,-99)
AND org_id = l_docs_tab(i).org_id AND org_type = l_docs_tab(i).org_type)
OR (supplier_site_id IS NULL AND party_site_id = l_docs_tab(i).party_site_id
AND org_id = l_docs_tab(i).org_id AND org_type = l_docs_tab(i).org_type)
OR (supplier_site_id IS NULL AND party_site_id = l_docs_tab(i).party_site_id AND org_id IS NULL AND org_type IS NULL)
OR (supplier_site_id IS NULL AND party_site_id IS NULL AND org_id IS NULL AND org_type IS NULL)
/* This condition is for Cash Payments (CE). Payee might be created for Legal Entity*/
OR (supplier_site_id IS NULL AND party_site_id IS NULL AND org_id = l_docs_tab(i).org_id AND org_type = l_docs_tab(i).org_type)
)
)
AND Nvl(END_DATE, SYSDATE+10) > SYSDATE
);
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
* in inserted into the IBY tables. If the logic to
* derive the ext payee id, could not find a matching
* ext payee id for the document, then the ext payee id
* would be set to -1 for that document.
*
* Fail all document that have the ext payee id set to
* -1. The user is expected to seed the IBY_EXTERNAL_PAYEES_ALL
* table such that the ext payee id is always available for
* payee context on the document (otherwise, the
* document cannot be paid!).
*/
/* Initialize flag */
l_is_valid := FALSE;
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
l_doc_token_tab);
SELECT ipp.payment_profile_id,
ipp.payment_format_code, ipp.bepid, ipp.transmit_protocol_code
INTO l_profile_id,l_payment_format_cd,l_bepid,l_transmit_protocol_cd
FROM IBY_PAYMENT_PROFILES ipp
WHERE ipp.payment_profile_id = l_docs_tab(i).profile_id ;
print_debuginfo(l_module_name, 'Before doing a bulk insert to the cursor');
print_debuginfo(l_module_name, 'Before doing a bulk insert doc_id is ' || l_docs_tab(i).doc_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert pmt_grp_num is ' || l_docs_tab(i).pmt_grp_num);
print_debuginfo(l_module_name, 'Before doing a bulk insert payee_id is ' || l_docs_tab(i).payee_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert pmt_method_cd is ' || l_docs_tab(i).pmt_method_cd);
print_debuginfo(l_module_name, 'Before doing a bulk insert int_bank_acct_id is ' || l_docs_tab(i).int_bank_acct_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert l_payment_format_cd is ' || l_payment_format_cd);
print_debuginfo(l_module_name, 'Before doing a bulk insert l_bepid is ' || l_bepid);
print_debuginfo(l_module_name, 'Before doing a bulk insert l_transmit_protocol_cd is ' || l_transmit_protocol_cd);
print_debuginfo(l_module_name, 'Before doing a bulk insert l_country is ' || l_country);
print_debuginfo(l_module_name, 'Before doing a bulk insert to the cursor and in If loop');
print_debuginfo(l_module_name, 'Before doing a bulk insert doc_id is ' || l_val_sets_tab(k).doc_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert pmt_grp_num is ' || l_val_sets_tab(k).pmt_grp_num);
print_debuginfo(l_module_name, 'Before doing a bulk insert payee_id is ' || l_val_sets_tab(k).payee_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_set_code is ' || l_val_sets_tab(k).val_set_code);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_code_pkg is ' || l_val_sets_tab(k).val_code_pkg);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_code_entry_point is ' || l_val_sets_tab(k).val_code_entry_point);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_assign_id is ' || l_val_sets_tab(k).val_assign_id);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_assign_entity_type is ' || l_val_sets_tab(k).val_assign_entity_type);
print_debuginfo(l_module_name, 'Before doing a bulk insert val_set_name is ' || l_val_sets_tab(k).val_set_name);
print_debuginfo(l_module_name, 'After doing a bulk insert to the cursor');
* Update the status of the documents and the payment
* request.
*/
performDBUpdates(
p_pay_service_request_id,
l_docs_tab,
l_invalid_docs_tab,
l_all_docs_success_flag,
l_all_docs_failed_flag,
l_rejection_level,
l_doc_error_tab,
l_doc_token_tab,
x_return_status
);
* have been inserted / updated. This is because you cannot
* 'rollback' a business event once raised.
*/
IF (p_is_singpay_flag = FALSE) THEN
raiseBizEvents(p_pay_service_request_id, req_ca_payreq_cd, req_ca_id,
l_all_docs_success_flag, l_rejection_level);
SELECT /*+ INDEX(docs IBY_DOCS_PAYABLE_GT_N1) NO_EXPAND */
docs.document_payable_id,
val.validation_set_code,
val.validation_code_package,
val.validation_code_entry_point,
val_options.validation_assignment_id,
val_options.val_assignment_entity_type,
val.validation_set_display_name
FROM
IBY_VALIDATION_SETS_VL val,
IBY_VAL_ASSIGNMENTS val_options,
IBY_DOCS_PAYABLE_GT docs,
IBY_VALIDATION_VALUES vld_val
WHERE
docs.document_payable_id = p_document_payable_id
AND
val.validation_set_code = val_options.validation_set_code
AND
val.validation_level_code = 'DOCUMENT'
AND (val_options.val_assignment_entity_type = 'METHOD'
AND val_options.assignment_entity_id = docs.payment_method_code
OR val_options.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_options.assignment_entity_id = docs.internal_bank_account_id
OR val_options.val_assignment_entity_type = 'FORMAT'
AND val_options.assignment_entity_id = docs.payment_format_code
)
AND NVL(val_options.inactive_date, sysdate+1) >= sysdate
AND val_options.validation_assignment_id = vld_val.validation_assignment_id (+)
AND vld_val.validation_parameter_code(+) = 'P_FIELD_NAME'
AND nvl(substr(vld_val.val_param_varchar2_value,1,8),'-1') <> 'INT_BANK'
;
SELECT
docs.calling_app_id ,
docs.calling_app_doc_id1 ,
docs.calling_app_doc_id2 ,
docs.calling_app_doc_id3 ,
docs.calling_app_doc_id4 ,
docs.calling_app_doc_id5 ,
docs.pay_proc_trxn_type_cd ,
docs.document_id ,
docs.document_amount ,
docs.document_pay_currency ,
docs.exclusive_payment_flag ,
docs.delivery_channel_code ,
docs.delivery_chn_format_val ,
docs.unique_remit_id_code ,
docs.payment_reason_comments ,
docs.settlement_priority ,
docs.remittance_message1 ,
docs.remittance_message2 ,
docs.remittance_message3 ,
docs.uri_check_digit ,
docs.external_bank_account_id ,
docs.int_bank_num ,
docs.int_bank_name ,
docs.int_bank_name_alt ,
docs.int_bank_branch_num ,
docs.int_bank_branch_name ,
docs.int_bank_branch_name_alt ,
docs.int_bank_branch_type , -- bug 16007784
docs.int_bank_acc_num ,
docs.int_bank_acc_name ,
docs.int_bank_acc_name_alt ,
docs.int_bank_acc_type ,
docs.int_bank_acc_iban ,
docs.int_bank_acc_curr ,
docs.int_bank_assigned_id1 ,
docs.int_bank_assigned_id2 ,
docs.int_eft_user_number ,
docs.int_bank_acc_chk_dgts ,
docs.int_eft_req_identifier ,
docs.int_bank_acc_short_name ,
docs.int_bank_acc_holder_name ,
docs.int_bank_acc_holder_name_alt ,
docs.payer_le_name ,
docs.payer_le_country ,
docs.payer_phone ,
docs.payer_registration_number ,
docs.payer_tax_registration_number ,
docs.ext_bank_num ,
docs.ext_bank_name ,
docs.ext_bank_name_alt ,
docs.ext_bank_branch_num ,
docs.ext_bank_branch_name ,
docs.ext_bank_branch_name_alt ,
docs.ext_bank_branch_type , -- bug 16007784
docs.ext_bank_country ,
docs.ext_bank_branch_addr1 ,
docs.ext_bank_branch_country ,
docs.ext_bank_acc_num ,
docs.ext_bank_acc_name ,
docs.ext_bank_acc_name_alt ,
docs.ext_bank_acc_type ,
docs.ext_bank_acc_iban ,
docs.ext_bank_acc_chk_dgts ,
docs.ext_bank_acc_short_name ,
docs.ext_bank_acc_holder_name ,
docs.ext_bank_acc_holder_name_alt ,
docs.ext_bank_acc_BIC ,
docs.payee_party_name ,
docs.payee_party_addr1 ,
docs.payee_party_addr2 ,
docs.payee_party_addr3 ,
docs.payee_party_city ,
docs.payee_party_state ,
docs.payee_party_province ,
docs.payee_party_county ,
docs.payee_party_postal ,
docs.payee_party_country ,
docs.bank_charge_bearer ,
docs.payment_reason_code ,
docs.payment_method_cd ,
docs.payee_payment_format_cd ,
docs.payee_party_site_name
FROM
IBY_DOCS_PAYABLE_VAL_GT docs
WHERE
docs.document_id = p_doc_id;
SELECT
docs.calling_app_id calling_app_id,
docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
docs.document_payable_id document_id,
docs.payment_amount document_amount,
docs.payment_currency_code document_pay_currency,
docs.exclusive_payment_flag exclusive_payment_flag,
docs.delivery_channel_code delivery_channel_code,
del_chn.format_value delivery_chn_format_val,
docs.unique_remittance_identifier unique_remit_id_code,
docs.payment_reason_comments payment_reason_comments,
docs.settlement_priority settlement_priority,
docs.remittance_message1 remittance_message1,
docs.remittance_message2 remittance_message2,
docs.remittance_message3 remittance_message3,
docs.uri_check_digit uri_check_digit,
docs.external_bank_account_id external_bank_account_id,
iba_bnk_branch.bank_number int_bank_num,
iba_bnk_branch.bank_name int_bank_name,
iba_bnk_branch.bank_name_alt int_bank_name_alt,
iba_bnk_branch.branch_number int_bank_branch_num,
iba_bnk_branch.bank_branch_name int_bank_branch_name,
iba_bnk_branch.bank_branch_name_alt int_bank_branch_name_alt,
iba_bnk_branch.bank_branch_type int_bank_branch_type, -- bug 16007784
iba.bank_account_num int_bank_acc_num,
iba.bank_account_name int_bank_acc_name,
iba.bank_account_name_alt int_bank_acc_name_alt,
iba.bank_account_type int_bank_acc_type,
iba.iban_number int_bank_acc_iban,
iba.currency_code int_bank_acc_curr,
'' int_bank_assigned_id1,
'' int_bank_assigned_id2,
iba.eft_user_num int_eft_user_number,
iba.check_digits int_bank_acc_chk_dgts,
iba.eft_requester_identifier int_eft_req_identifier,
iba.short_account_name int_bank_acc_short_name,
iba.account_holder_name int_bank_acc_holder_name,
iba.account_holder_name_alt int_bank_acc_holder_name_alt,
payer.party_legal_name payer_le_name,
payer.party_address_country payer_le_country,
payer.party_phone payer_phone,
payer.party_registration_number payer_registration_number, --added by asarada (SEPA Credit Transfer 3.3)
IBY_FD_EXTRACT_GEN_PVT.
Get_FP_TaxRegistration
(docs.legal_entity_id) payer_tax_registration_number, -- added by asarada (SEPA Credit Transfer 3.3 Changes)
eba.bank_number ext_bank_num,
eba.bank_name ext_bank_name,
eba_bank_branch.bank_name_alt ext_bank_name_alt, --Bug 10133698 - Corrected from NULL fetch
eba.branch_number ext_bank_branch_num,
eba.bank_branch_name ext_bank_branch_name,
eba_bank_branch.bank_branch_name_alt ext_bank_branch_name_alt,
eba_bank_branch.bank_branch_type ext_bank_branch_type, -- bug 16007784
eba.country_code ext_bank_country,
eba_bank_branch.address_line1 ext_bank_branch_addr1,
nvl(eba_bank_branch.country,eba_bank_branch.bank_home_country) ext_bank_branch_country,
eba.bank_account_number ext_bank_acc_num,
eba.bank_account_name ext_bank_acc_name,
eba.alternate_account_name ext_bank_acc_name_alt,
eba.bank_account_type ext_bank_acc_type,
eba.iban_number ext_bank_acc_iban,
eba.check_digits ext_bank_acc_chk_dgts,
eba.short_acct_name ext_bank_acc_short_name,
eba.primary_acct_owner_name ext_bank_acc_holder_name,
'' ext_bank_acc_holder_name_alt,
eba.eft_swift_code ext_eft_swift_code, -- The documentRecType in the ibyvalls.pls was modified
payee.party_name payee_party_name,
/*
* Note regarding bugfix for bug 5997016:
*
* Normally, this cursor c_onlineDocumentInfo, and the cursor
* above c_documentInfo are in sync. This means that both
* cursors pick up the same data except that the
* online document cursor picks up the document attributes
* from IBY_DOCS_PAYABLE_GT whereas the offline
* document validation cursor picks up the document
* attributes from IBY_DOCS_PAYABLE_ALL table.
*
* In fix for bug 5997016, we made the offline doc validation
* cursor pick up the address data dynamically from
* HR or TCA tables depending on the address source column.
*
* In the online validation cursor, we will not propagate
* the same logic. There are some reasons for this -
*
* 1. Some columns that are present in IBY_DOCS_PAYABLE_ALL
* table are not present in IBY_DOCS_PAYABLE_GT table.
* E.g., address_source is not available in the GT table.
* Therefore, to support the dynamic payee address
* functionality we would need to make a data model change.
*
* 2. The online validation API is meant to provide a
* a quick response to the user as the validation is
* called syncronously by the user. By adding complex
* joins, we will be adding a performance penalty to
* online validation.
*
* 3. The intent of online validations is to catch basic
* errors in the document. The payee address validation
* on the document is a corner case, and it is not
* necessary to do this as part of online validation.
*
* The offline validation / batch validation will catch
* these errors. The online validation is meant to be
* simple and quick that targets the basic errors on the
* document.
*
* We will continue to pick up the payee address from
* HZ_LOCATIONS. In the case of employee type payees
* the payee address fields will be null. This is
* fine. The offline validation will catch these
* errors anyway.
*/
payee_loc.address1 payee_party_addr1,
payee_loc.address2 payee_party_addr2,
payee_loc.address3 payee_party_addr3,
payee_loc.city payee_party_city,
payee_loc.state payee_party_state,
payee_loc.province payee_party_province,
payee_loc.county payee_party_county,
payee_loc.postal_code payee_party_postal,
payee_loc.country payee_party_country,
docs.bank_charge_bearer bank_charge_bearer,
docs.payment_reason_code payment_reason_code,
docs.payment_method_code payment_method_cd,
docs.payment_format_code payee_payment_format_cd,
payeesite.party_site_name payee_party_site_name
FROM
IBY_DOCS_PAYABLE_GT docs,
IBY_PP_FIRST_PARTY_V payer,
HZ_PARTIES payee,
HZ_PARTY_SITES payeesite,
HZ_LOCATIONS payee_loc,
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
IBY_EXT_BANK_ACCOUNTS_INT_V eba,
CE_BANK_BRANCHES_V eba_bank_branch,
IBY_DELIVERY_CHANNELS_B del_chn
WHERE
docs.document_payable_id = p_doc_id
AND docs.legal_entity_id = payer.party_legal_id
AND docs.payee_party_id = payee.party_id
AND docs.payee_party_site_id = payeesite.party_site_id (+)
AND payeesite.location_id = payee_loc.location_id(+)
AND docs.internal_bank_account_id = iba.bank_account_id (+)
AND iba_bnk_branch.branch_party_id (+) = iba.bank_branch_id
AND docs.external_bank_account_id = eba.ext_bank_account_id(+)
AND eba.bank_party_id = eba_bank_branch.bank_party_id(+)
AND eba.branch_party_id = eba_bank_branch.branch_party_id(+)
AND docs.delivery_channel_code = del_chn.delivery_channel_code(+)
;
SELECT
docs.calling_app_id calling_app_id,
docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
docs.document_payable_id document_id
FROM
IBY_DOCS_PAYABLE_ALL docs
WHERE
docs.document_payable_id = p_doc_id
;
SELECT
docs.calling_app_id calling_app_id,
docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
docs.document_payable_id document_id
FROM
IBY_DOCS_PAYABLE_GT docs
WHERE
docs.document_payable_id = p_doc_id
;
SELECT
/* DOCUMENT RELATED */
doc.document_payable_id,
doc.calling_app_id,
doc.calling_app_doc_unique_ref1,
doc.calling_app_doc_unique_ref2,
doc.calling_app_doc_unique_ref3,
doc.calling_app_doc_unique_ref4,
doc.calling_app_doc_unique_ref5,
doc.pay_proc_trxn_type_code,
doc.calling_app_doc_ref_number,
doc.unique_remittance_identifier,
doc.uri_check_digit,
doc.po_number,
doc.document_description,
doc.bank_assigned_ref_code,
doc.payment_reason_comments,
doc.remittance_message1,
doc.remittance_message2,
doc.remittance_message3,
dlv.format_value,
pmt_reason.format_value,
lines.calling_app_document_line_code,
lines.line_type,
lines.line_name,
lines.description,
lines.unit_of_measure,
lines.po_number,
/* PAYER */
payer.party_number, -- payer number
payer.party_name, -- payer name
payer.party_legal_name, -- payer legal name
payer.party_tax_id, -- payer tax id
payer.party_address_line1, -- payer add line 1
payer.party_address_line2, -- payer add line 2,
payer.party_address_line3, -- payer add line 3
payer.party_address_city, -- payer city
payer.party_address_county , -- payer county
payer.party_address_state, -- payer state
payer.party_address_country, -- payer country
payer.party_address_postal_code, -- payer postal code
/* PAYER BANK */
iba_bnk_branch.bank_name, -- payer bank name
iba_bnk_branch.bank_number, -- payer bank number
iba_bnk_branch.branch_number, -- payer bank branch number
iba_bnk_branch.bank_branch_name, -- payer bank branch name
iba_bnk_branch.eft_swift_code, -- payer bank swift code
iba_bnk_branch.address_line1, -- payer bank add line 1
iba_bnk_branch.address_line2, -- payer bank add line 2
iba_bnk_branch.address_line3, -- payer bank add line 3
iba_bnk_branch.city, -- payer bank city
iba_bnk_branch.province, -- payer bank county
iba_bnk_branch.state, -- payer bank state
nvl(iba_bnk_branch.country,iba_bnk_branch.bank_home_country), -- payer bank country
iba_bnk_branch.zip, -- payer bank postal code
iba_bnk_branch.bank_name_alt, -- payer bank name alt
iba_bnk_branch.bank_branch_name_alt, -- payer bank branch name alt
iba.bank_account_name_alt, -- payer bank acct name alt
iba.bank_account_type, -- payer bank acct type
'', -- payer bank assigned id1
'', -- payer bank assigned id2
iba.eft_user_num, -- payer eft user number
iba.eft_requester_identifier, -- payer eft req identifier
iba.short_account_name, -- payer bank acct short name
iba.account_holder_name_alt, -- payer bank acct holder name alt
iba.account_holder_name, -- payer bank account holder name
iba.bank_account_num, -- payer bank account num
iba.bank_account_name, -- payer bank account name
iba.iban_number, -- payer bank acct iban number
iba.check_digits, -- payer bank acct check digits
/* PAYEE */
payee.party_number, -- payee number
payee.party_name, -- payee name
payee.tax_reference, -- payee tax number
payee_loc.address1, -- payee add line1
payee_loc.address2, -- payee add line2
payee_loc.address3, -- payee add line3
payee_loc.city, -- payee city
payee_loc.county, -- payee county
payee_loc.province, -- payee province
payee_loc.state, -- payee state
payee_loc.country, -- payee country
payee_loc.postal_code, -- payee postal code
/* PAYEE BANK */
eba.bank_name, -- payee bank name
eba.bank_number, -- payee bank number
eba.branch_number, -- payee bank branch number
eba.bank_branch_name, -- payee bank branch name
eba.primary_acct_owner_name, -- payee bank account holder name
eba.bank_account_number, -- payee bank account number
eba.bank_account_name, -- payee bank account name
eba.iban_number, -- payee bank account IBAN
eba.eft_swift_code, -- payee bank swift code
eba.check_digits, -- payee bank account check digits
'', -- payee bank add line 1
'', -- payee bank add line 2
'', -- payee bank add line 3
'', -- payee bank city
'', -- payee bank county
'', -- payee bank state
'', -- payee bank country
'', -- payee bank postal code
'', -- payee bank name alternate
'', -- payee bank branch name alternate
eba.country_code, -- payee bank country code
eba.alternate_account_name, -- payee bank account name alternate
eba.bank_account_type, -- payee bank account type
eba.short_acct_name, -- payee bank account short name
'' -- payee bank acct holder name alt
FROM
/* Document related */
IBY_DOCS_PAYABLE_ALL doc,
IBY_PAYMENT_REASONS_VL pmt_reason,
IBY_DELIVERY_CHANNELS_VL dlv,
IBY_DOCUMENT_LINES lines,
/* Payer */
IBY_PP_FIRST_PARTY_V payer,
/* Payer bank */
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
/* Payee */
HZ_PARTIES payee,
HZ_LOCATIONS payee_loc,
/* Payee bank */
IBY_EXT_BANK_ACCOUNTS_V eba
WHERE
/* document related */
doc.document_payable_id = p_doc_id
AND doc.payment_reason_code = pmt_reason.payment_reason_code(+)
AND doc.delivery_channel_code = dlv.delivery_channel_code(+)
AND doc.document_payable_id = lines.document_payable_id(+)
/* payer */
AND doc.legal_entity_id = payer.party_legal_id
/* payer bank */
AND doc.internal_bank_account_id = iba.bank_account_id
AND iba_bnk_branch.branch_party_id = iba.bank_branch_id
/* payee */
AND doc.payee_party_id = payee.party_id
AND doc.remit_to_location_id = payee_loc.location_id(+)
/* payee bank */
AND doc.external_bank_account_id = eba.ext_bank_account_id(+)
;
SELECT
pay.payment_id pmt_id,
pay.payment_amount pmt_amount,
pay.payment_currency_code pmt_currency,
pay.delivery_channel_code pmt_delivery_channel_code,
payer.party_address_country pmt_payer_le_country,
pay.payment_details pmt_detail,
0 pmt_payment_reason_count,
pay.int_bank_account_iban int_bank_account_iban,
pay.payer_tax_registration_num payer_tax_registration_num,
pay.payer_le_registration_num payer_le_registration_num,
payer.party_address_line1,
payer.party_address_city,
payer.party_address_postal_code,
payer_bank_acc.currency_code
FROM
IBY_PAYMENTS_ALL pay,
IBY_PP_FIRST_PARTY_V payer,
CE_BANK_ACCOUNTS payer_bank_acc
WHERE
pay.payment_id = p_pay_id
AND
pay.legal_entity_id = payer.party_legal_id
AND
pay.internal_bank_account_id = payer_bank_acc.bank_account_id;
SELECT count(distinct payment_reason_code)
INTO x_payment_rec.pmt_payment_reason_count
FROM iby_docs_payable_all
WHERE payment_id = p_payment_id;
SELECT
instr.payment_instruction_id ins_id,
0 ins_amount,
0 ins_document_count
FROM
IBY_PAY_INSTRUCTIONS_ALL instr
WHERE
instr.payment_instruction_id = p_instr_id;
select sum(d.document_amount),
count(d.document_payable_id)
into x_instruction_rec.ins_amount,
x_instruction_rec.ins_document_count
from iby_docs_payable_all d, iby_payments_all p
where p.payment_instruction_id = p_instruction_id
and p.payment_id = d.payment_id
/*
* Fix for bug 5672789:
*
* When calculating payment count for an instruction,
* only pick up payments that are in
* 'INSTRUCTION_CREATED' status.
*/
and p.payment_status IN (PAY_STATUS_INS_CRTD)
;
| insert_transaction_errors
|
| PURPOSE:
| Inserts the error messages into the errors table. For
| online validations, the error messages are inserted into
| IBY_TRANSACTION_ERRORS_GT; for deferred validations, the
| error messages are inserted into IBY_TRANSACTION_ERRORS
| table.
|
| Validation sets populate the transaction errors into a PLSQL
| table. This method performs a bulk insert of the given records
| into the transaction errors table.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insert_transaction_errors(
p_isOnlineVal IN VARCHAR2,
x_docErrorTab IN OUT NOCOPY docErrorTabType,
x_trxnErrTokenTab IN OUT NOCOPY trxnErrTokenTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insert_transaction_errors';
* Column types for insertion into IBY_TRANSACTION_ERRORS table.
*/
TYPE t_transaction_error_id IS TABLE OF
IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_updated_by IS TABLE OF
IBY_TRANSACTION_ERRORS.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_date IS TABLE OF
IBY_TRANSACTION_ERRORS.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_login IS TABLE OF
IBY_TRANSACTION_ERRORS.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
l_last_updated_by t_last_updated_by;
l_last_update_date t_last_update_date;
l_last_update_login t_last_update_login;
* Column types for insertion into IBY_TRXN_ERROR_TOKENS table.
*/
TYPE t_trxn_error_id IS TABLE OF
IBY_TRXN_ERROR_TOKENS.transaction_error_id%TYPE
INDEX BY BINARY_INTEGER;
IBY_TRXN_ERROR_TOKENS.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
IBY_TRXN_ERROR_TOKENS.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
IBY_TRXN_ERROR_TOKENS.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
* will be used in the bulk insert.
*/
FOR i in x_docErrorTab.FIRST..x_docErrorTab.LAST LOOP
l_transaction_error_id(i)
:= x_docErrorTab(i).transaction_error_id;
l_last_updated_by(i)
:= NVL(x_docErrorTab(i).last_updated_by,
fnd_global.user_id);
l_last_update_date(i)
:= NVL(x_docErrorTab(i).last_update_date, sysdate);
l_last_update_login(i)
:= NVL(x_docErrorTab(i).last_update_login,
fnd_global.user_id);
* Insert error messages into IBY_TRANSACTION_ERRORS table.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Bulk inserting errors into '
|| 'IBY_TRANSACTION_ERRORS.');
* Use named columns in bulk insert syntax to avoid any
* dependencies on the order of the columns in the table.
*/
FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
INSERT INTO IBY_TRANSACTION_ERRORS
(
transaction_error_id,
transaction_type,
transaction_id,
error_code,
error_date,
error_status,
calling_app_doc_unique_ref1,
override_allowed_on_error_flag,
do_not_apply_error_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
last_update_login,
calling_app_id,
pay_proc_trxn_type_code,
calling_app_doc_unique_ref2,
calling_app_doc_unique_ref3,
calling_app_doc_unique_ref4,
calling_app_doc_unique_ref5,
error_type,
error_message,
validation_set_code,
pass_date,
override_justification,
override_date
)
VALUES
(
l_transaction_error_id(i),
l_transaction_type(i),
l_transaction_id(i),
l_error_code(i),
l_error_date(i),
l_error_status(i),
l_calling_app_doc_unique_ref1(i),
l_ovrride_allowed_on_err_flg(i),
l_do_not_apply_error_flag(i),
l_created_by(i),
l_creation_date(i),
l_last_updated_by(i),
l_last_update_date(i),
l_object_version_number(i),
l_last_update_login(i),
l_calling_app_id(i),
l_pay_proc_trxn_type_code(i),
l_calling_app_doc_unique_ref2(i),
l_calling_app_doc_unique_ref3(i),
l_calling_app_doc_unique_ref4(i),
l_calling_app_doc_unique_ref5(i),
l_error_type(i),
l_error_message(i),
l_validation_set_code(i),
l_pass_date(i),
l_override_justification(i),
l_override_date(i)
)
;
* Insert error messages into IBY_TRANSACTION_ERRORS_GT table.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Bulk Inserting errors into '
|| 'IBY_TRANSACTION_ERRORS_GT.');
* Use named columns in bulk insert syntax to avoid any
* dependencies on the order of the columns in the table.
*/
FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
INSERT INTO IBY_TRANSACTION_ERRORS_GT
(
transaction_error_id,
transaction_type,
transaction_id,
error_code,
error_date,
error_status,
calling_app_doc_unique_ref1,
override_allowed_on_error_flag,
do_not_apply_error_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
last_update_login,
calling_app_id,
pay_proc_trxn_type_code,
calling_app_doc_unique_ref2,
calling_app_doc_unique_ref3,
calling_app_doc_unique_ref4,
calling_app_doc_unique_ref5,
error_type,
error_message,
validation_set_code,
pass_date,
override_justification,
override_date
)
VALUES
(
l_transaction_error_id(i),
l_transaction_type(i),
l_transaction_id(i),
l_error_code(i),
l_error_date(i),
l_error_status(i),
l_calling_app_doc_unique_ref1(i),
l_ovrride_allowed_on_err_flg(i),
l_do_not_apply_error_flag(i),
l_created_by(i),
l_creation_date(i),
l_last_updated_by(i),
l_last_update_date(i),
l_object_version_number(i),
l_last_update_login(i),
l_calling_app_id(i),
l_pay_proc_trxn_type_code(i),
l_calling_app_doc_unique_ref2(i),
l_calling_app_doc_unique_ref3(i),
l_calling_app_doc_unique_ref4(i),
l_calling_app_doc_unique_ref5(i),
l_error_type(i),
l_error_message(i),
l_validation_set_code(i),
l_pass_date(i),
l_override_justification(i),
l_override_date(i)
)
;
* error tokens are always inserted into the IBY_TRXN_ERROR_TOKENS
* table.
*/
IF (x_trxnErrTokenTab.COUNT > 0) THEN
/*
* Create an array of values for each column. These arrays
* will be used in the bulk insert.
*/
FOR j in x_trxnErrTokenTab.FIRST..x_trxnErrTokenTab.LAST LOOP
l_trxn_error_id(j) := x_trxnErrTokenTab(j).
transaction_error_id;
last_updated_by,
fnd_global.user_id);
last_update_date, sysdate);
last_update_login,
fnd_global.user_id);
INSERT INTO IBY_TRXN_ERROR_TOKENS
(
transaction_error_id,
token_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
token_value,
lookup_type_source,
last_update_login
)
VALUES
(
l_trxn_error_id(j),
l_token_name(j),
l_crtd_by(j),
l_crt_date(j),
l_last_updtd_by(j),
l_last_updt_date(j),
l_object_ver_number(j),
l_token_value(j),
l_lookup_type_source(j),
l_last_updt_login(j)
)
;
'insert_transaction_errors : ' || SQLERRM);
END insert_transaction_errors;
| insert_transaction_errors
|
| PURPOSE:
| Original procedure that has been overloaded.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insert_transaction_errors(
p_isOnlineVal IN VARCHAR2,
x_docErrorTab IN OUT NOCOPY docErrorTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insert_transaction_errors';
insert_transaction_errors(p_isOnlineVal, x_docErrorTab,
l_dummy_err_token_tab);
END insert_transaction_errors;
| insertIntoErrorTable
|
| PURPOSE:
| Inserts the document validation errors into PLSQL Table
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertIntoErrorTable(
x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
x_docErrorTab IN OUT NOCOPY docErrorTabType,
x_trxnErrTokenTab IN OUT NOCOPY trxnErrTokenTabType
)
IS
l_transaction_error_id IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE;
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertIntoErrorTable';
SELECT
IBY_TRANSACTION_ERRORS_S.NEXTVAL
INTO
l_transaction_error_id
FROM
DUAL
;
END insertIntoErrorTable;
PROCEDURE insertIntoErrorTable(
x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
x_docErrorTab IN OUT NOCOPY docErrorTabType
)
IS
l_transaction_error_id IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE;
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertIntoErrorTable';
insertIntoErrorTable(x_docErrorRec, x_docErrorTab, l_dummy_err_token_tab);
END insertIntoErrorTable;
SELECT meaning
INTO l_msg_token
FROM fnd_lookups
WHERE lookup_type = 'IBY_VALIDATION_FIELDS'
AND lookup_code = p_object_code;
select count(*)
into l_deliv_cnt
from iby_delivery_channels_vl
where territory_code = p_char_value
and delivery_channel_code = p_fieldValue
-- and enabled_flag = 'Y'
;
select count(*)
into l_lookup_code_cnt
from fnd_lookups
where lookup_type = p_char_value
and lookup_code = p_fieldValue;
SELECT
IBY_TRANSACTION_ERRORS_S.NEXTVAL
INTO
l_transaction_error_id
FROM
DUAL
;
INSERT INTO IBY_TRXN_ERROR_TOKENS
(TRANSACTION_ERROR_ID, TOKEN_NAME, TOKEN_VALUE, LOOKUP_TYPE_SOURCE,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER)
VALUES
(l_transaction_error_id, 'ERR_OBJECT', p_fieldName, 'IBY_VALIDATION_FIELDS',
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.user_id, 1);
select decode(vp.validation_parameter_type,
'VARCHAR2', val_param_varchar2_value,
'NUMBER', val_param_number_value,
'DATE', val_param_date_value)
into p_value
from iby_val_assignments va,
iby_validation_values vv,
iby_validation_params_vl vp
where va.validation_set_code = p_validation_set_code
and va.validation_assignment_id = p_validation_assign_id
and va.validation_set_code = vv.validation_set_code
and va.validation_assignment_id = vv.validation_assignment_id
and vv.validation_parameter_code = p_validation_param_code
and vp.validation_set_code = va.validation_set_code
and vp.validation_parameter_code = vv.validation_parameter_code;
* Update by Ramesh:
*
* Change some of the payee address related field names
* because of the way the names are seeded in
* the IBY_VALIDATION_FIELDS lookup.
*
* For example, here the field name is PAYEE_PARTY_CITY
* but in the lookup, it is seeded as PAYEE_PARTY_SITE_CITY.
*
* Because of the mismatch, the field value is returned as
* null and the validation always fails. It is simpler
* to rename the field names here that in the lookup.
*
* Hence changing the payee addredd related field names
* here.
*/
elsif p_field_name = 'PAYEE_PARTY_SITE_CITY' then
p_field_value := p_document_rec.payee_party_city;
* Updated by sodash
* for Payee BIC validation
*/
elsif p_field_name = 'EXT_EFT_SWIFT_CODE' then
p_field_value := p_document_rec.ext_eft_swift_code;
* Updated by sodash
* Payer IBAN and Payer Address Validations
*/
elsif p_field_name = 'INT_BANK_ACC_IBAN' then
p_field_value := p_payment_rec.int_bank_account_iban;
SELECT
call_app_pay_service_req_code,
calling_app_id
INTO
x_caPayReqCd,
x_caId
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
payment_service_request_id = p_payReqId;
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 not in 'validated' status
*/
getRejectedDocs(p_payreq_id, l_rej_doc_id_list,
l_rej_doc_status_list);
insertIntoErrorTable(l_doc_err_rec,
x_docErrorTab, x_errTokenTab);
* docs table will be used to update the database.
*/
IF (l_newlyFailedDocsTab.COUNT <> 0) THEN
FOR i in l_newlyFailedDocsTab.FIRST .. l_newlyFailedDocsTab.LAST LOOP
x_failedDocsTab(x_failedDocsTab.COUNT + 1) :=
l_newlyFailedDocsTab(i);
insertIntoErrorTable(l_doc_err_rec,
x_docErrorTab, x_errTokenTab);
* docs table will be used to update the database.
*/
IF (l_newlyFailedDocsTab.COUNT <> 0) THEN
FOR i in l_newlyFailedDocsTab.FIRST .. l_newlyFailedDocsTab.LAST LOOP
x_failedDocsTab(x_failedDocsTab.COUNT + 1) :=
l_newlyFailedDocsTab(i);
insertIntoErrorTable(l_doc_err_rec, x_docErrorTab,
x_errTokenTab);
insertIntoErrorTable(l_doc_err_rec,
x_docErrorTab, x_errTokenTab);
* docs table will be used to update the database.
*/
IF (l_newlyFailedDocsTab.COUNT <> 0) THEN
FOR i in l_newlyFailedDocsTab.FIRST .. l_newlyFailedDocsTab.LAST LOOP
x_failedDocsTab(x_failedDocsTab.COUNT + 1) :=
l_newlyFailedDocsTab(i);
l_storedAwtPayees.DELETE;
* Select all docs that:
* 1. Have the given pay req id
* 2. Are not in 'documents_validated' status
* 3. Were updated in the process of payment creation
* (some docs might have failed earlier in document
* validation flow. We don't want to pick them up).
*/
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 '
|| 'FROM iby_docs_payable_all '
|| 'WHERE payment_service_request_id = :payreq_id '
|| 'AND document_status <> :doc_status';
SELECT
doc.document_payable_id,
doc.document_status
FROM
IBY_DOCS_PAYABLE_ALL doc
WHERE
doc.payment_service_request_id = p_payreq_id AND
doc.document_status <> DOC_STATUS_VALIDATED
;
SELECT
sysoptions.document_rejection_level_code
FROM
IBY_INTERNAL_PAYERS_ALL sysoptions
WHERE
sysoptions.org_id IS NULL
;
SELECT payee.ext_payee_id, payee.payment_format_code
INTO l_payee_id, l_payee_format_cd
FROM IBY_EXTERNAL_PAYEES_ALL payee
WHERE payee.ext_payee_id = p_payee_id;
SELECT
ipp.payment_profile_id, ipp.payment_format_code,ipp.bepid, ipp.transmit_protocol_code
INTO l_payment_profile_id,l_prof_pmt_format_cd,l_bepid,l_transmit_protocol_cd
FROM IBY_PAYMENT_PROFILES ipp
WHERE ipp.payment_profile_id = p_profile_id ;
| and inserts them in to a GT table (IBY_DOCS_PAYABLE_VAL_GT).
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE initDocDataForPPR(
p_pay_service_request_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%TYPE
)
IS
l_module_name CONSTANT VARCHAR2(200)
:= G_PKG_NAME || '.initDocDataForPPR';
SELECT PROCESS_TYPE
INTO l_process_type
FROM iby_pay_service_requests
WHERE payment_service_request_id = p_pay_service_request_id; --Bug 16679530
INSERT INTO IBY_DOCS_PAYABLE_VAL_GT(calling_app_id,
calling_app_doc_id1 ,
calling_app_doc_id2 ,
calling_app_doc_id3 ,
calling_app_doc_id4 ,
calling_app_doc_id5 ,
pay_proc_trxn_type_cd ,
document_id ,
document_amount ,
document_pay_currency ,
exclusive_payment_flag ,
delivery_channel_code ,
delivery_chn_format_val ,
unique_remit_id_code ,
payment_reason_comments ,
settlement_priority ,
remittance_message1 ,
remittance_message2 ,
remittance_message3 ,
uri_check_digit ,
external_bank_account_id ,
int_bank_num ,
int_bank_name ,
int_bank_name_alt ,
int_bank_branch_num ,
int_bank_branch_name ,
int_bank_branch_name_alt ,
int_bank_branch_type ,
int_bank_acc_num ,
int_bank_acc_name ,
int_bank_acc_name_alt ,
int_bank_acc_type , -- bug 16007784
int_bank_acc_iban ,
int_bank_acc_curr ,
int_bank_assigned_id1 ,
int_bank_assigned_id2 ,
int_eft_user_number ,
int_bank_acc_chk_dgts ,
int_eft_req_identifier ,
int_bank_acc_short_name ,
int_bank_acc_holder_name ,
int_bank_acc_holder_name_alt ,
payer_le_name ,
payer_le_country ,
payer_phone ,
payer_registration_number ,
payer_tax_registration_number ,
ext_bank_num ,
ext_bank_name ,
ext_bank_name_alt ,
ext_bank_branch_num ,
ext_bank_branch_name ,
ext_bank_branch_name_alt ,
ext_bank_branch_type , -- bug 16007784
ext_bank_country ,
ext_bank_branch_addr1 ,
ext_bank_branch_country ,
ext_bank_acc_num ,
ext_bank_acc_name ,
ext_bank_acc_name_alt ,
ext_bank_acc_type ,
ext_bank_acc_iban ,
ext_bank_acc_chk_dgts ,
ext_bank_acc_short_name ,
ext_bank_acc_holder_name ,
ext_bank_acc_holder_name_alt ,
ext_bank_acc_BIC ,
payee_party_name ,
payee_party_addr1 ,
payee_party_addr2 ,
payee_party_addr3 ,
payee_party_city ,
payee_party_state ,
payee_party_province ,
payee_party_county ,
payee_party_postal ,
payee_party_country ,
bank_charge_bearer ,
payment_reason_code ,
payment_method_cd ,
payee_payment_format_cd ,
payee_party_site_name
)
SELECT
docs.calling_app_id calling_app_id,
docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
docs.document_payable_id document_id,
docs.payment_amount document_amount,
docs.payment_currency_code document_pay_currency,
docs.exclusive_payment_flag exclusive_payment_flag,
docs.delivery_channel_code delivery_channel_code,
del_chn.format_value delivery_chn_format_val,
docs.unique_remittance_identifier unique_remit_id_code,
docs.payment_reason_comments payment_reason_comments,
docs.settlement_priority settlement_priority,
docs.remittance_message1 remittance_message1,
docs.remittance_message2 remittance_message2,
docs.remittance_message3 remittance_message3,
docs.uri_check_digit uri_check_digit,
docs.external_bank_account_id external_bank_account_id,
iba_bnk_branch.bank_number int_bank_num,
iba_bnk_branch.bank_name int_bank_name,
iba_bnk_branch.bank_name_alt int_bank_name_alt,
iba_bnk_branch.branch_number int_bank_branch_num,
iba_bnk_branch.bank_branch_name int_bank_branch_name,
iba_bnk_branch.bank_branch_name_alt int_bank_branch_name_alt,
iba_bnk_branch.bank_branch_type int_bank_branch_type, -- bug 16007784
iba.bank_account_num int_bank_acc_num,
iba.bank_account_name int_bank_acc_name,
iba.bank_account_name_alt int_bank_acc_name_alt,
iba.bank_account_type int_bank_acc_type,
iba.iban_number int_bank_acc_iban,
iba.currency_code int_bank_acc_curr,
'' int_bank_assigned_id1,
'' int_bank_assigned_id2,
iba.eft_user_num int_eft_user_number,
iba.check_digits int_bank_acc_chk_dgts,
iba.eft_requester_identifier int_eft_req_identifier,
iba.short_account_name int_bank_acc_short_name,
iba.account_holder_name int_bank_acc_holder_name,
iba.account_holder_name_alt int_bank_acc_holder_name_alt,
payer.party_legal_name payer_le_name,
payer.party_address_country payer_le_country,
payer.party_phone payer_phone,
payer.party_registration_number payer_registration_number,
IBY_FD_EXTRACT_GEN_PVT.
Get_FP_TaxRegistration
(docs.legal_entity_id) payer_tax_registration_number,
eba_bank_branch.bank_number ext_bank_num,
eba_bank_branch.bank_name ext_bank_name,
eba_bank_branch.bank_name_alt ext_bank_name_alt,
eba_bank_branch.branch_number ext_bank_branch_num,
eba_bank_branch.bank_branch_name ext_bank_branch_name,
eba_bank_branch.bank_branch_name_alt ext_bank_branch_name_alt,
eba_bank_branch.bank_branch_type ext_bank_branch_type, -- bug 16007784
eba.country_code ext_bank_country,
eba_bank_branch.address_line1 ext_bank_branch_addr1,
nvl(eba_bank_branch.country,
eba_bank_branch.bank_home_country) ext_bank_branch_country,
DECODE (l_process_type,
'STANDARD', iby_ext_bankacct_pub.Uncipher_Bank_Number (eba.bank_account_num,
eba.ba_num_sec_segment_id,
iby_utility_pvt.get_view_param('SYS_KEY'),
baek.subkey_cipher_text,
baes.segment_cipher_text,
baes.encoding_scheme,
eba.ba_mask_setting,
eba.ba_unmask_length),
eba.ba_num_sec_segment_id) ext_bank_acc_num, --Bug 16679530
eba.bank_account_name ext_bank_acc_name,
eba.bank_account_name_alt ext_bank_acc_name_alt,
eba.bank_account_type ext_bank_acc_type,
DECODE(l_process_type,
'STANDARD', iby_ext_bankacct_pub.Uncipher_Bank_Number (eba.iban,
eba.iban_sec_segment_id,
iby_utility_pvt.get_view_param('SYS_KEY'),
baik.subkey_cipher_text,
bais.segment_cipher_text,
bais.encoding_scheme,
eba.ba_mask_setting,
eba.ba_unmask_length),
eba.iban) ext_bank_acc_iban, --Bug 16679530
eba.check_digits ext_bank_acc_chk_dgts,
eba.short_acct_name ext_bank_acc_short_name,
op.party_name ext_bank_acc_holder_name,
'' ext_bank_acc_holder_name_alt,
eba_bank_branch.eft_swift_code ext_bank_acc_BIC,
payee.party_name payee_party_name,
payee_addr.add_line1 payee_party_addr1,
payee_addr.add_line2 payee_party_addr2,
payee_addr.add_line3 payee_party_addr3,
payee_addr.city payee_party_city,
payee_addr.state payee_party_state,
payee_addr.province payee_party_province,
payee_addr.county payee_party_county,
payee_addr.postal_code payee_party_postal,
payee_addr.country payee_party_country,
docs.bank_charge_bearer bank_charge_bearer,
docs.payment_reason_code payment_reason_code,
docs.payment_method_code payment_method_cd,
docs.payment_format_code payee_payment_format_cd,
payeesite.party_site_name payee_party_site_name
FROM
IBY_DOCS_PAYABLE_ALL docs,
IBY_PP_FIRST_PARTY_V payer,
HZ_PARTIES payee,
HZ_PARTY_SITES payeesite,
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
IBY_EXT_BANK_ACCOUNTS eba,
IBY_ACCOUNT_OWNERS ow,
HZ_PARTIES op,
IBY_SYS_SECURITY_SUBKEYS baek,
IBY_SECURITY_SEGMENTS baes,
IBY_SYS_SECURITY_SUBKEYS baik,
IBY_SECURITY_SEGMENTS bais,
CE_BANK_BRANCHES_V eba_bank_branch,
IBY_DELIVERY_CHANNELS_B del_chn,
/*
* Fix for bug 5997016:
*
* The payee address cannot be always assumed to be stored in
* HZ_LOCATIONS table (TCA).
*
* For employee type payees, the address is stored in
* per_addresses (HR).
*
* The 'address source' column on the document payable identifies
* the source of the address information -
* TCA = address is stored in HZ_LOCATIONS
* HR = address is stored in PER_ADDRESSES
*
* Therefore, we need to dynamically pick up the payee address
* fields from the correct table. The SELECT statement below is
* used to dynamically form the address table based on the
* address source. This dynamic table is aliased as payee_addr.
*
* There is a dynamic address tabled formed in a similar manner
* during the payment creation process as well [see method
* IBY_PAYGROUP_PUB.auditPaymentData(..)].
*/
/*13728551 - Added a join to fecth the provisional site addresses as well.
*/
(
SELECT
/* payee add line1 */
DECODE(
doc.address_source,
-- supplier address line 1
'TCA', payee_loc.address1,
-- employee add line 1
DECODE
(
doc.employee_address_code,
-- employee home addr line 1
'HOME', per_addr.address_line1,
-- employee office addr line 1
'OFFICE',per_loc.address_line_1,
-- employee provisional site addressline1
'PROVISIONAL',per_addr_prov.address_line1 ,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1,
per_addr.address_line1)
)
) add_line1,
/* payee add line2 */
DECODE(
doc.address_source,
-- supplier address line 2
'TCA', payee_loc.address2,
-- employee add line 2
DECODE
(
doc.employee_address_code,
-- employee home addr line 2
'HOME', per_addr.address_line2,
-- employee office addr line 2
'OFFICE',per_loc.address_line_2,
-- employee provisional site addressline1
'PROVISIONAL',per_addr_prov.address_line2 ,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_2,
per_addr.address_line2)
)
) add_line2,
/* payee add line3 */
DECODE(
doc.address_source,
-- supplier address line 3
'TCA', payee_loc.address3,
-- employee add line 3
DECODE
(
doc.employee_address_code,
-- employee home addr line 3
'HOME', per_addr.address_line3,
-- employee office addr line 3
'OFFICE',per_loc.address_line_3,
-- employee provisional site addressline1
'PROVISIONAL',per_addr_prov.address_line3 ,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_3,
per_addr.address_line3)
)
) add_line3,
/* payee add line4 */
DECODE(
doc.address_source,
-- supplier address line 4
'TCA', payee_loc.address4,
-- employee home/office/provisional addr line 4 (not available)
null
) add_line4,
/* payee city */
DECODE(
doc.address_source,
-- supplier city
'TCA', payee_loc.city,
-- employee city
DECODE
(
doc.employee_address_code,
-- employee home city
'HOME', per_addr.town_or_city,
-- employee office city
'OFFICE', per_loc.town_or_city,
-- employee provisional city
'PROVISIONAL',per_addr_prov.town_or_city,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.town_or_city,
per_addr.town_or_city)
)
) city,
/* payee county */
DECODE(
doc.address_source,
-- supplier county
'TCA', payee_loc.county,
-- employee county
(
DECODE(
doc.employee_address_code,
-- employee home county
'HOME',
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''),
-- employee office county
'OFFICE',
DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
-- employee provisional county
'PROVISIONAL',
DECODE(
per_addr_prov.style,
'US', NVL(per_addr_prov.region_1, ''),
'US_GLB', NVL(per_addr_prov.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
''),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''))
)
)
) county,
/* payee province */
DECODE(
doc.address_source,
-- supplier province
'TCA', payee_loc.province,
-- employee province
(
DECODE(
doc.employee_address_code,
-- employee home province
'HOME',
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
),
-- employee office province
'OFFICE',
DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
-- employee provisional province
'PROVISIONAL',
DECODE(per_addr_prov.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr_prov.region_1, ''),
'CA_GLB', NVL(per_addr_prov.region_1, ''),
'JP', NVL(per_addr_prov.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
'')
),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
))
)
)
) province,
/* payee state */
DECODE(
doc.address_source,
-- supplier state
'TCA', payee_loc.state,
-- employee state
(
DECODE(
doc.employee_address_code,
-- employee home state
'HOME',
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')),
-- employee office state
'OFFICE',
DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
-- employee provisional state
'PROVISIONAL',
DECODE(per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')))
)
)
) state,
/* payee country */
DECODE(
doc.address_source,
-- supplier country
'TCA', payee_loc.country,
-- employee country
(
DECODE(
doc.employee_address_code,
-- employee home country
'HOME', per_addr.country,
-- employee office country
'OFFICE',per_loc.country,
-- employee provisional country
'PROVISIONAL',per_addr_prov.country,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.country,
per_addr.country
)
)
)
) country,
/* payee postal code */
DECODE(
doc.address_source,
-- supplier postal code
'TCA', payee_loc.postal_code,
-- employee postal code
(
DECODE(
doc.employee_address_code,
-- employee home postal code
'HOME', per_addr.postal_code,
-- employee office postal code
'OFFICE',per_loc.postal_code,
-- employee provisional postal code
'PROVISIONAL',per_addr_prov.postal_code,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.postal_code,
per_addr.postal_code
)
)
)
) postal_code,
/* payee address concat */
DECODE(
doc.address_source,
-- supplier address concat
'TCA',
payee_loc.address1
|| ', '
|| payee_loc.address2
|| ', '
|| payee_loc.address3
|| ', '
|| payee_loc.city
|| ', '
|| payee_loc.state
|| ', '
|| payee_loc.country
|| ', '
|| payee_loc.postal_code,
-- employee address concat
(
DECODE(
doc.employee_address_code,
-- employee home address concat
'HOME',
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code,
-- employee office address concat
'OFFICE',
per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
-- employee provisional address concat
'PROVISIONAL',
per_addr_prov.address_line1
|| ', '
|| per_addr_prov.address_line2
|| ', '
|| per_addr_prov.address_line3
|| ', '
|| per_addr_prov.town_or_city
|| ', '
|| DECODE(
per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')
)
|| ', '
|| per_addr_prov.country
|| ', '
|| per_addr_prov.postal_code,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code)
)
)
) add_concat,
doc.document_payable_id document_payable_id
FROM
IBY_DOCS_PAYABLE_ALL doc,
/* Employee address related */
HR_LOCATIONS per_loc,
PER_ADDRESSES per_addr,
PER_ADDRESSES per_addr_prov,
PER_ALL_ASSIGNMENTS_F per_assgn,
/* Supplier address related */
HZ_LOCATIONS payee_loc
WHERE
doc.payment_service_request_id = p_pay_service_request_id
AND doc.employee_person_id = per_addr.person_id(+)
AND per_addr.primary_flag(+) = 'Y'
AND SYSDATE BETWEEN
per_addr.date_from(+)
AND NVL(per_addr.date_to(+), SYSDATE+1)
AND doc.employee_person_id = per_assgn.person_id(+)
AND per_assgn.location_id = per_loc.location_id(+)
AND per_assgn.primary_flag(+) = 'Y'
AND per_assgn.assignment_type(+) = 'E'
AND (TRUNC(SYSDATE) BETWEEN
per_assgn.effective_start_date(+)
AND per_assgn.effective_end_date(+)
)
AND doc.remit_to_location_id = payee_loc.location_id(+)
AND doc.employee_person_id = per_addr_prov.person_id(+)
AND per_addr_prov.address_type(+) = 'M'
AND SYSDATE BETWEEN
per_addr_prov.date_from(+)
AND NVL(per_addr_prov.date_to(+), SYSDATE+1)
) payee_addr
WHERE
docs.payment_service_request_id = p_pay_service_request_id
AND docs.document_payable_id = payee_addr.document_payable_id
AND docs.legal_entity_id = payer.party_legal_id
AND docs.payee_party_id = payee.party_id
AND docs.party_site_id = payeesite.party_site_id (+)
AND docs.internal_bank_account_id = iba.bank_account_id
AND iba_bnk_branch.branch_party_id = iba.bank_branch_id
AND docs.external_bank_account_id = eba.ext_bank_account_id(+)
AND eba.bank_id = eba_bank_branch.bank_party_id(+)
AND eba.branch_id = eba_bank_branch.branch_party_id(+)
AND eba.ext_bank_account_id = ow.ext_bank_account_id(+)
AND ow.primary_flag(+) = 'Y'
AND nvl(ow.end_date, sysdate + 10) > sysdate
AND ow.account_owner_party_id = op.party_id(+)
AND eba.ba_num_sec_segment_id = baes.sec_segment_id(+)
AND baes.sec_subkey_id = baek.sec_subkey_id(+)
AND eba.iban_sec_segment_id = bais.sec_segment_id(+)
AND bais.sec_subkey_id = baik.sec_subkey_id(+)
AND docs.delivery_channel_code = del_chn.delivery_channel_code(+)
;