[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
payment_id,
payment_amount
from
IBY_HOOK_PAYMENTS_T
;
SELECT per.full_name
INTO p_emp_full_name
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND per.party_id = p_party_id
AND sysdate BETWEEN per.effective_start_date
AND per.effective_end_date;
| updateCommonAttribues
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
| Implementing only for Delivery Channel.
|
*---------------------------------------------------------------------*/
PROCEDURE updateCommonAttribues(
l_trx_audit_index IN BINARY_INTEGER
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||'.updateCommonAttribues';
SELECT DISTINCT(SETTLEMENT_PRIORITY)
INTO
l_settlement_pr
FROM IBY_DOCS_PAYABLE_ALL
WHERE
PAYMENT_ID = IBY_PAYGROUP_PUB.pmtTable.PAYMENT_ID(l_trx_audit_index);
END updateCommonAttribues;
SELECT
payment_id,
payment_status
FROM
iby_payments_all
WHERE payment_service_request_id = p_pmt_service_req_id
;
delete_pmtTable;
updateDocsWithPaymentID; -- new approach , without GT needs to be overridden
updateDocsWithPaymentID; -- new approach , without GT needs to be overridden
* and moving the logic to insertDocIntoPayment API.
* This code was called even if grouping rules for these 3 attributes were not
* set - to get common attributes to be used later */
--sweepCommonPmtAttributes(l_paymentTab, l_docsInPmtTab);
* Move the code to initializePmts to insertDocIntoPayment - when
* creating a new payment */
-- initializePmts(l_paymentTab);
* Update: Credit memo handling is now performed at the calling
* application itself.
* - rameshsh, 3/29/2005
*/
--printDocsInPmtTab(l_docsInPmtTab);
* payments in the IBY_PAYMENTS_ALL table. Therefore, insert
* the payments from the PLSQL table into the
* IBY_PAYMENTS_ALL table.
*
* Central bank reporting could be implemented via a hook
* that is expecting payments to be populated in
* IBY_PAYMENTS_ALL table. Therefore, payments need to
* inserted before performing central bank reporting as well.
*/
/* not needed now as updateDocsWithPaymentID is moved up and we are not inserting
* into IBY_PAYMENTS_ALL table now
*/
/*
IF (l_payreq_status <> REQ_STATUS_RETRY_PMT_CREAT AND
l_payreq_status <> REQ_STATUS_USER_REVW_ERR) THEN
insertPayments(l_paymentTab);
* Update the documents payable that are part of the created
* payments with payment ids.
*
* This is normally done in performDBUpdates(..) call at
* the end of this method. However, some customers might
* want to do custom validations on documents that are
* part of the created payments. Therefore, update the
* documents with payment ids before the payment validation
* call.
*
* We do this even before central bank reporting because
* again the customer could potentially want to retrieve
* documents that are part of the created payments in the
* central bank reporting hook.
*/
/*
updateDocsWithPaymentID(l_docsInPmtTab);
updateDocsWithPaymentID;
SELECT nvl(anticipated_float,0) + IBY_PAYGROUP_PUB.pmtTable.maturity_date(p_trx_pmt_line_index)
INTO l_antcptd_val_date
FROM iby_payment_methods_b
WHERE payment_method_code = IBY_PAYGROUP_PUB.pmtTable.payment_method_code(p_trx_pmt_line_index);
SELECT nvl(anticipated_float,0) + IBY_PAYGROUP_PUB.pmtTable.payment_date(p_trx_pmt_line_index)
INTO l_antcptd_val_date
FROM iby_payment_methods_b
WHERE payment_method_code = IBY_PAYGROUP_PUB.pmtTable.payment_method_code(p_trx_pmt_line_index);
SELECT nvl(anticipated_float,0) + IBY_PAYGROUP_PUB.pmtTable.maturity_date(p_trx_pmt_line_index)
INTO l_antcptd_val_date
FROM iby_payment_methods_b
WHERE payment_method_code = IBY_PAYGROUP_PUB.pmtTable.payment_method_code(p_trx_pmt_line_index);
SELECT nvl(anticipated_float,0) + IBY_PAYGROUP_PUB.pmtTable.payment_date(p_trx_pmt_line_index)
INTO l_antcptd_val_date
FROM iby_payment_methods_b
WHERE payment_method_code = IBY_PAYGROUP_PUB.pmtTable.payment_method_code(p_trx_pmt_line_index);
/* Bug: 10109218 - Update attributes which were missed due to payment grouping number*/
updateCommonAttribues(p_trx_pmt_line_index);
print_debuginfo(l_module_name, 'Call has come from IBY_SINGPAY_PUB API so calling insertPayments Directly');
insertPayments;
print_debuginfo(l_module_name, 'Call has come from IBY_DISBURSE_SUBMIT_PUB_PKG API so verifying the l_payreq_status before calling insertPayments');
insertPayments;
updatePayments;
updatePayments;
* The adjusted payments are read back and inserted into
* IBY_PAYMENTS_LL table.
*
* This is a general hook that is called for all other
* products except AP. For AP special hooks are called
* below.
*/
IF (l_ca_id <> 200) THEN
/*
* Only successful payments are passed to be passed to
* to the calling application via the hook / callout.
*
* From the existing list of all payments, create new data
* structures that only store successful payments. This
* 'success only' list of payments will be passed to the
* calling application.
*
* This method writes the payment data to global temp tables.
*/
/*performPreHookProcess(l_ca_payreq_cd, l_ca_id, l_paymentTab,
l_docsInPmtTab, l_hookPaymentTab, l_hookDocsInPmtTab);*/
SELECT 'Y'
INTO l_calc_jp_bank_charges
FROM dual
WHERE EXISTS (SELECT 1
FROM ap_system_parameters_all asp
, IBY_PAYMENTS_ALL pmt
WHERE pmt.PAYMENT_SERVICE_REQUEST_ID = p_payment_request_id
AND pmt.org_id = asp.org_id
AND nvl(asp.use_bank_charge_flag,'N') = 'Y');
SELECT 'Y'
INTO l_call_ap_awt_callout
FROM dual
WHERE EXISTS (SELECT 1
FROM ap_system_parameters_all asp,
IBY_PAYMENTS_ALL pmt
WHERE pmt.PAYMENT_SERVICE_REQUEST_ID = p_payment_request_id
AND pmt.org_id = asp.org_id
AND nvl(asp.allow_awt_flag,'N') = 'Y');
* Similarly, update the documents table by providing a
* payment id to each document.
*/
/*
* Pass in the current ppr status. This is used
* to determine the next status in some cases.
*/
x_return_status := l_payreq_status;
performDBUpdates(p_payment_request_id, l_rejection_level,
l_review_pmts_flag,
--l_paymentTab,
l_all_pmts_success_flag,
l_all_pmts_failed_flag, x_return_status,
l_docErrorTab, l_docTokenTab
);
* have been inserted / updated. This is because you cannot
* 'rollback' a business event once raised.
*/
IF(p_calling_procedure = 'IBY_DISBURSE_SINGLE_PMT_PKG') THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Call has come from IBY_SINGPAY_PUB API so not calling raiseBizEvents');
* PLSQL table is used in inserting a row into the IBY_PAYMENTS_ALL
* table.
*
* Since the IBY_PAYMENTS_ALL table does not contain a document id, a
* separate data structure is needed to keep track of the documents
* that are part of a payment. This information is tracked in the
* docsInPaymentTabType table. The rows in docsInPaymentTabType are
* used to update the rows in IBY_DOCS_PAYABLE_ALL table with
* payment ids.
*
* x_paymentTab x_docsInPmtTab
* (insert into IBY_PAYMENTS_ALL) (update IBY_DOCS_PAYABLE)
* /-------------------------------------\ /------------\
* |Payment|Payment|..|Payment|Docs |..| |Payment|Doc |
* |Id |Profile|..|Amount |Payable|..| |Id |Id |
* | |Id |..| |Count |..| | | |
* |-------------------------------------| |------------|
* | 4000| 10| | 550| 3| | | 4000| 501|
* | | | | | | | | 4000| 504|
* | | | | | | | | 4000| 505|
* |-------|-------|--|-------|-------|--| |-------|----|
* | 4001| 10| |1063.70| 19| | | 4001| 502|
* | | | | | | | | 4001| 509|
* | | | | | | | | 4001| 511|
* | | | | | | | | 4001| 523|
* | | | | | | | | : | : |
* |-------|-------|--|-------|-------|--| |-------|----|
* | : | : | | : | : | | | : | : |
* \_______|_______|__|_______|_______|__/ \_______|____/
*
* Combining these two structures into one structure is messy
* because you cannot directly use the combined data structure for
* bulk updates.
*/
/* these two are passed to calling app via hook */
l_hookPaymentTab IBY_PAYGROUP_PUB.hookPaymentTabType;
* payment request. The select statement will order the documents
* based on grouping criteria.
*
* As the grouping is operation is commutative, there is no
* need to group the documents by any particular order (in
* other words, grouping 1 followed by grouping 2, will lead
* to the same results as grouping 2 followed by grouping 1).
*
* Important Note:
* Always ensure that there is a corresponding order by
* clause for each grouping criterion that you wish to use.
* This is required in order to create minimum possible
* payments from a given set of documents.
*
*
* Populate legal entity ids for each of the created payments.
* The legal entity id on the payment is a special attribute.
* It is not a grouping rule, instead it has to be derived
* from the internal bank account and populated.
*
*/
CURSOR c_documents(p_payment_request_id VARCHAR2)
IS
SELECT prq.call_app_pay_service_req_code, -- Callers pay req id
docs.document_payable_id, -- Unique doc id asgn by IBY
docs.calling_app_id, --| The caller
docs.calling_app_doc_unique_ref1, --| uses these
docs.calling_app_doc_unique_ref2, --| seven fields
docs.calling_app_doc_unique_ref3, --| to uniquely
docs.calling_app_doc_unique_ref4, --| identify
docs.calling_app_doc_unique_ref5, --| a document
docs.pay_proc_trxn_type_code, --| payable
docs.payment_grouping_number, -- Identifies related docs
docs.payment_method_code, -- Payment method
docs.internal_bank_account_id, -- Internal bank account id
docs.external_bank_account_id, -- External bank account id
docs.payment_profile_id, -- Payment profile id
docs.org_id,
docs.org_type,
docs.payment_function,
docs.ext_payee_id, -- Ext payee id (payee context)
docs.payee_party_id,
docs.party_site_id,
docs.supplier_site_id,
docs.remit_to_location_id,
docs.amount_withheld, -- Passed to hook for adjustmt
ext_payee.bank_instruction1_code, --|
ext_payee.bank_instruction2_code, --| These fields are
ext_payee.payment_text_message1, --| derived from the
ext_payee.payment_text_message2, --| payee.
ext_payee.payment_text_message3, --|
docs.payment_currency_code,
docs.payment_amount,
docs.payment_date,
docs.exclusive_payment_flag,
docs.bank_charge_bearer, -- Bank charge bearer
docs.delivery_channel_code, -- Delivery channel
docs.settlement_priority,
docs.remittance_message1, -- Supplier message 1
docs.remittance_message2, -- Supplier message 2
docs.remittance_message3, -- Supplier message 3
docs.unique_remittance_identifier,
docs.uri_check_digit,
docs.payment_reason_code,
docs.payment_reason_comments,
docs.payment_due_date,
docs.discount_date,
docs.payment_curr_discount_taken,
docs.beneficiary_party,
docs.address_source, --
docs.employee_address_code, -- Employee address
docs.employee_person_id, -- related fields
docs.employee_address_id, --
docs.employee_payment_flag,
pcr.group_by_remittance_message,
pcr.group_by_bank_charge_bearer,
pcr.group_by_delivery_channel,
pcr.group_by_settle_priority_flag,
pcr.group_by_payment_details_flag,
DECODE(pcr.payment_details_length_limit, NULL,
-1, pcr.payment_details_length_limit),
pcr.payment_details_formula,
pcr.group_by_max_documents_flag, -- Max documents per payment flag
pcr.max_documents_per_payment, -- Max documents per payment value
pcr.group_by_unique_remit_id_flag,
pcr.group_by_payment_reason,
pcr.group_by_due_date_flag,
ppf.processing_type,
ppf.declaration_option,
ppf.dcl_only_foreign_curr_pmt_flag,
ppf.declaration_curr_fx_rate_type,
ppf.declaration_currency_code,
ppf.declaration_threshold_amount,
DECODE(prq.maximum_payment_amount, NULL, -1,
prq.maximum_payment_amount),
DECODE(prq.minimum_payment_amount, NULL, -1,
prq.minimum_payment_amount),
prq.allow_zero_payments_flag,
pmt_mthd.support_bills_payable_flag,
iba.account_owner_org_id,
/*TPP-Start*/
docs.inv_payee_party_id,
docs.inv_party_site_id,
docs.inv_supplier_site_id,
docs.inv_beneficiary_party,
docs.ext_inv_payee_id
/*TPP-Start*/
FROM IBY_DOCS_PAYABLE_ALL docs,
IBY_PMT_CREATION_RULES pcr,
IBY_PAYMENT_PROFILES ppf,
IBY_PAY_SERVICE_REQUESTS prq,
IBY_EXTERNAL_PAYEES_ALL ext_payee,
IBY_PAYMENT_METHODS_B pmt_mthd,
CE_BANK_ACCOUNTS iba
WHERE prq.payment_service_request_id = p_payment_request_id
AND docs.payment_service_request_id = prq.payment_service_request_id
AND docs.payment_profile_id = ppf.payment_profile_id
AND ppf.system_profile_code = pcr.system_profile_code(+)
AND docs.document_status = DOC_STATUS_VALIDATED
AND docs.ext_payee_id = ext_payee.ext_payee_id
AND docs.payment_method_code = pmt_mthd.payment_method_code
AND docs.internal_bank_account_id = iba.bank_account_id
ORDER BY
docs.exclusive_payment_flag,
docs.org_id,
docs.org_type,
ext_payee.ext_payee_id, -- \
docs.payment_currency_code, -- |
docs.payment_method_code, -- |
docs.payment_profile_id, -- |
docs.payment_grouping_number, -- |
docs.internal_bank_account_id, -- | Ensure that the grouping
docs.external_bank_account_id, -- | rules below follow the same
docs.payment_function, -- | ordering as this order by
docs.payment_date, -- | clause.
docs.remit_to_location_id, -- |
docs.beneficiary_party, -- |
docs.address_source, -- |
docs.employee_address_code, -- |
docs.employee_person_id, -- |
docs.employee_address_id, -- |
docs.employee_payment_flag, -- |
docs.bank_charge_bearer, -- |
docs.delivery_channel_code, -- | This order must be followed
docs.settlement_priority, -- | for creating minimum number
docs.remittance_message1, -- | of payments.
docs.remittance_message2, -- |
docs.remittance_message3, -- /
docs.unique_remittance_identifier,
docs.uri_check_digit,
docs.payment_reason_code,
docs.payment_reason_comments,
docs.payment_due_date,
docs.calling_app_doc_ref_number
;
SELECT
iba.bank_account_id int_bank_account,
iba.account_owner_org_id legal_entity_id
FROM
CE_BANK_ACCOUNTS iba
;
iby_disburse_submit_pub_pkg.delete_docspayTab;
* We will either insert this document into a new payment or
* we will be inserting this document into the currently running
* payment.
*
* In either case, we need to insert this doc into a payment.
* So pre-populate the payment record with attributes of
* this document. This is because the payment takes on the
* attributes of it's constituent documents.
*
* Note: For user defined grouping rules, we will
* have to populate the payment attribute only if
* the user has turned on grouping by that attribute.
*/
/* Only pre-fill hardcoded grouping rule attributes */
l_paymentRec.payment_profile_id := iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, false, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
iby_disburse_submit_pub_pkg.delete_docspayTab;
* PLSQL table is used in inserting a row into the IBY_PAYMENTS_ALL
* table.
*
* Since the IBY_PAYMENTS_ALL table does not contain a document id, a
* separate data structure is needed to keep track of the documents
* that are part of a payment. This information is tracked in the
* docsInPaymentTabType table. The rows in docsInPaymentTabType are
* used to update the rows in IBY_DOCS_PAYABLE_ALL table with
* payment ids.
*
* x_paymentTab x_docsInPmtTab
* (insert into IBY_PAYMENTS_ALL) (update IBY_DOCS_PAYABLE)
* /-------------------------------------\ /------------\
* |Payment|Payment|..|Payment|Docs |..| |Payment|Doc |
* |Id |Profile|..|Amount |Payable|..| |Id |Id |
* | |Id |..| |Count |..| | | |
* |-------------------------------------| |------------|
* | 4000| 10| | 550| 3| | | 4000| 501|
* | | | | | | | | 4000| 504|
* | | | | | | | | 4000| 505|
* |-------|-------|--|-------|-------|--| |-------|----|
* | 4001| 10| |1063.70| 19| | | 4001| 502|
* | | | | | | | | 4001| 509|
* | | | | | | | | 4001| 511|
* | | | | | | | | 4001| 523|
* | | | | | | | | : | : |
* |-------|-------|--|-------|-------|--| |-------|----|
* | : | : | | : | : | | | : | : |
* \_______|_______|__|_______|_______|__/ \_______|____/
*
* Combining these two structures into one structure is messy
* because you cannot directly use the combined data structure for
* bulk updates.
*/
/* these two are passed to calling app via hook */
l_hookPaymentTab IBY_PAYGROUP_PUB.hookPaymentTabType;
* payment request. The select statement will order the documents
* based on grouping criteria.
*
* As the grouping is operation is commutative, there is no
* need to group the documents by any particular order (in
* other words, grouping 1 followed by grouping 2, will lead
* to the same results as grouping 2 followed by grouping 1).
*
* Important Note:
* Always ensure that there is a corresponding order by
* clause for each grouping criterion that you wish to use.
* This is required in order to create minimum possible
* payments from a given set of documents.
*
*
* Populate legal entity ids for each of the created payments.
* The legal entity id on the payment is a special attribute.
* It is not a grouping rule, instead it has to be derived
* from the internal bank account and populated.
*
*/
CURSOR c_documents(p_payment_request_id VARCHAR2)
IS
SELECT prq.call_app_pay_service_req_code, -- Callers pay req id
docs.document_payable_id, -- Unique doc id asgn by IBY
docs.calling_app_id, --| The caller
docs.calling_app_doc_unique_ref1, --| uses these
docs.calling_app_doc_unique_ref2, --| seven fields
docs.calling_app_doc_unique_ref3, --| to uniquely
docs.calling_app_doc_unique_ref4, --| identify
docs.calling_app_doc_unique_ref5, --| a document
docs.pay_proc_trxn_type_code, --| payable
docs.payment_grouping_number, -- Identifies related docs
docs.payment_method_code, -- Payment method
docs.internal_bank_account_id, -- Internal bank account id
docs.external_bank_account_id, -- External bank account id
docs.payment_profile_id, -- Payment profile id
docs.org_id,
docs.org_type,
docs.payment_function,
docs.ext_payee_id, -- Ext payee id (payee context)
docs.payee_party_id,
docs.party_site_id,
docs.supplier_site_id,
docs.remit_to_location_id,
docs.amount_withheld, -- Passed to hook for adjustmt
ext_payee.bank_instruction1_code, --|
ext_payee.bank_instruction2_code, --| These fields are
ext_payee.payment_text_message1, --| derived from the
ext_payee.payment_text_message2, --| payee.
ext_payee.payment_text_message3, --|
docs.payment_currency_code,
docs.payment_amount,
docs.payment_date,
docs.exclusive_payment_flag,
docs.bank_charge_bearer, -- Bank charge bearer
docs.delivery_channel_code, -- Delivery channel
docs.settlement_priority,
docs.remittance_message1, -- Supplier message 1
docs.remittance_message2, -- Supplier message 2
docs.remittance_message3, -- Supplier message 3
docs.unique_remittance_identifier,
docs.uri_check_digit,
docs.payment_reason_code,
docs.payment_reason_comments,
docs.payment_due_date,
docs.discount_date,
docs.payment_curr_discount_taken,
docs.beneficiary_party,
docs.address_source, --
docs.employee_address_code, -- Employee address
docs.employee_person_id, -- related fields
docs.employee_address_id, --
docs.employee_payment_flag,
pcr.group_by_remittance_message,
pcr.group_by_bank_charge_bearer,
pcr.group_by_delivery_channel,
pcr.group_by_settle_priority_flag,
pcr.group_by_payment_details_flag,
DECODE(pcr.payment_details_length_limit, NULL,
-1, pcr.payment_details_length_limit),
pcr.payment_details_formula,
pcr.group_by_max_documents_flag, -- Max documents per payment flag
pcr.max_documents_per_payment, -- Max documents per payment value
pcr.group_by_unique_remit_id_flag,
pcr.group_by_payment_reason,
pcr.group_by_due_date_flag,
ppf.processing_type,
ppf.declaration_option,
ppf.dcl_only_foreign_curr_pmt_flag,
ppf.declaration_curr_fx_rate_type,
ppf.declaration_currency_code,
ppf.declaration_threshold_amount,
DECODE(prq.maximum_payment_amount, NULL, -1,
prq.maximum_payment_amount),
DECODE(prq.minimum_payment_amount, NULL, -1,
prq.minimum_payment_amount),
prq.allow_zero_payments_flag,
pmt_mthd.support_bills_payable_flag,
iba.account_owner_org_id,
/*TPP-Start*/
docs.inv_payee_party_id,
docs.inv_party_site_id,
docs.inv_supplier_site_id,
docs.inv_beneficiary_party,
docs.ext_inv_payee_id,
/*TPP-Start*/
docs.affects_rejection_level --AWT Enh 16296267
FROM IBY_DOCS_PAYABLE_ALL docs,
IBY_PMT_CREATION_RULES pcr,
IBY_PAYMENT_PROFILES ppf,
IBY_PAY_SERVICE_REQUESTS prq,
IBY_EXTERNAL_PAYEES_ALL ext_payee,
IBY_PAYMENT_METHODS_B pmt_mthd,
CE_BANK_ACCOUNTS iba
WHERE prq.payment_service_request_id = p_payment_request_id
AND docs.payment_service_request_id = prq.payment_service_request_id
AND docs.payment_profile_id = ppf.payment_profile_id
AND ppf.system_profile_code = pcr.system_profile_code(+)
AND docs.document_status = DOC_STATUS_VALIDATED
AND docs.ext_payee_id = ext_payee.ext_payee_id
AND docs.payment_method_code = pmt_mthd.payment_method_code
AND docs.internal_bank_account_id = iba.bank_account_id
ORDER BY
docs.exclusive_payment_flag,
docs.org_id,
docs.org_type,
ext_payee.ext_payee_id, -- \
docs.payment_currency_code, -- |
docs.payment_method_code, -- |
docs.payment_profile_id, -- |
docs.payment_grouping_number, -- |
docs.internal_bank_account_id, -- | Ensure that the grouping
docs.external_bank_account_id, -- | rules below follow the same
docs.payment_function, -- | ordering as this order by
docs.payment_date, -- | clause.
docs.remit_to_location_id, -- |
docs.beneficiary_party, -- |
docs.address_source, -- |
docs.employee_address_code, -- |
docs.employee_person_id, -- |
docs.employee_address_id, -- |
docs.employee_payment_flag, -- |
docs.bank_charge_bearer, -- |
docs.delivery_channel_code, -- | This order must be followed
docs.settlement_priority, -- | for creating minimum number
docs.remittance_message1, -- | of payments.
docs.remittance_message2, -- |
docs.remittance_message3, -- /
docs.unique_remittance_identifier,
docs.uri_check_digit,
docs.payment_reason_code,
docs.payment_reason_comments,
docs.payment_due_date,
docs.calling_app_doc_ref_number
;
SELECT
iba.bank_account_id int_bank_account,
iba.account_owner_org_id legal_entity_id
FROM
CE_BANK_ACCOUNTS iba
;
iby_disburse_submit_pub_pkg.delete_docspayTab;
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index) := fnd_global.user_id;
iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index) := sysdate;
iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index) := fnd_global.login_id;
* We will either insert this document into a new payment or
* we will be inserting this document into the currently running
* payment.
*
* In either case, we need to insert this doc into a payment.
* So pre-populate the payment record with attributes of
* this document. This is because the payment takes on the
* attributes of it's constituent documents.
*
* Note: For user defined grouping rules, we will
* have to populate the payment attribute only if
* the user has turned on grouping by that attribute.
*/
/* Only pre-fill hardcoded grouping rule attributes */
l_paymentRec.payment_profile_id := iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index);
insertDocIntoPayment(l_paymentRec,-- x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec,
--x_paymentTab,
l_calc_doc_detail,
true,
l_payment_id,
l_docs_in_pmt_count,
l_commonAttributes,
l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count,l_commonAttributes);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec, --x_paymentTab,
l_calc_doc_detail, true, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
insertDocIntoPayment(l_paymentRec,-- x_paymentTab,
l_calc_doc_detail, false, l_payment_id, l_docs_in_pmt_count, l_commonAttributes, l_trx_line_index);
INSERT INTO iby_docs_in_pmt_gt
(
pay_proc_trxn_type_code,
calling_app_id,
document_payable_id,
document_status,
document_currency_code,
document_amount,
ext_payee_id,
payee_party_id,
org_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
object_version_number,
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,
last_update_login,
party_site_id,
supplier_site_id,
org_type,
amount_withheld,
payment_curr_discount_taken,
discount_date,
payment_due_date,
payment_id,
internal_bank_account_id,
external_bank_account_id,
payment_grouping_number,
unique_remittance_identifier,
uri_check_digit,
delivery_channel_code,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description
)
VALUES
(
iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.document_status(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_amount(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payee_party_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.created_by(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.creation_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.object_version_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.party_site_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.amount_withheld(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.discount_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_due_date(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_flag(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_reason_code(l_trx_line_index),
iby_disburse_submit_pub_pkg.docspayTab.dont_pay_description(l_trx_line_index)
);
iby_disburse_submit_pub_pkg.delete_docspayTab;
| insertDocIntoPayment
|
| PURPOSE:
| Inserts a given document into a currently running payment
| or into a new payment as per given flag.
|
| This method is called by every grouping rule to add
| a given document into a current payment/new payment.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertDocIntoPayment(
x_paymentRec IN OUT NOCOPY IBY_PAYMENTS_ALL%ROWTYPE,
x_paymentTab IN OUT NOCOPY paymentTabType,
p_calcDocInfo IN VARCHAR2,
p_newPaymentFlag IN BOOLEAN,
x_currentPaymentId IN OUT NOCOPY IBY_PAYMENTS_ALL.payment_id%TYPE,
x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
x_docsInPmtRec IN OUT NOCOPY docsInPaymentRecType,
x_docsInPmtCount IN OUT NOCOPY NUMBER
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insertDocIntoPayment';
* table is updated with the details of this document
* within this if-else block.
*
* We need to do this each time we enter this procedure
* because this might well be the last document in
* in the payment request, and this procedure may
* not be called again for this payment request. So
* the PLSQL payments table should always be up-to-date
* when it exits this procedure.
*/
IF (p_newPaymentFlag = true) THEN
/*
* This is a new payment; Get an id for this payment
* document as a constituent, and insert this record
* into the PLSQL payments table.
*/
x_paymentRec.payment_id := x_currentPaymentId;
print_debuginfo(l_module_name,' Inserted document : '
|| x_docsInPmtRec.document_id || ' into new payment: '
|| x_currentPaymentId);
* doc, and insert the doc into the docs array.
*/
x_docsInPmtRec.payment_id := x_paymentRec.payment_id;
* initialize it by inserting a dummy record. This dummy
* record will get overwritten below.
*/
IF (x_paymentTab.COUNT = 0) THEN
getNextPaymentID(x_currentPaymentID);
* Insert the first record into the table. This
* is a dummy record.
*/
x_paymentTab(x_paymentTab.COUNT + 1) := x_paymentRec;
* PLSQL payments table with the updated record.
*/
x_paymentTab(x_paymentTab.COUNT) := x_paymentRec;
print_debuginfo(l_module_name, 'Inserted document : '
|| x_docsInPmtRec.document_id || ' into existing payment: '
|| x_currentPaymentId);
* doc, and insert the doc into the docs array.
*/
x_docsInPmtRec.payment_id := x_paymentRec.payment_id;
END insertDocIntoPayment;
| insertDocIntoPayment
|
| PURPOSE:
| Inserts a given document into a currently running payment
| or into a new payment as per given flag.
|
| This method is called by every grouping rule to add
| a given document into a current payment/new payment.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertDocIntoPayment(
x_paymentRec IN OUT NOCOPY IBY_PAYMENTS_ALL%ROWTYPE,
x_paymentTab IN OUT NOCOPY paymentTabType,
p_calcDocInfo IN VARCHAR2,
p_newPaymentFlag IN BOOLEAN,
x_currentPaymentId IN OUT NOCOPY IBY_PAYMENTS_ALL.payment_id%TYPE,
x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
x_docsInPmtRec IN OUT NOCOPY docsInPaymentRecType,
x_docsInPmtCount IN OUT NOCOPY NUMBER,
x_commonAttributes IN OUT NOCOPY commonAttributesTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insertDocIntoPayment';
* table is updated with the details of this document
* within this if-else block.
*
* We need to do this each time we enter this procedure
* because this might well be the last document in
* in the payment request, and this procedure may
* not be called again for this payment request. So
* the PLSQL payments table should always be up-to-date
* when it exits this procedure.
*/
IF (p_newPaymentFlag = true) THEN
/*
* This is a new payment; Get an id for this payment
* document as a constituent, and insert this record
* into the PLSQL payments table.
*/
x_paymentRec.payment_id := x_currentPaymentId;
print_debuginfo(l_module_name,' Inserted document : '
|| x_docsInPmtRec.document_id || ' into new payment: '
|| x_currentPaymentId);
* doc, and insert the doc into the docs array.
*/
x_docsInPmtRec.payment_id := x_paymentRec.payment_id;
x_paymentRec.last_updated_by := fnd_global.user_id;
x_paymentRec.last_update_login := fnd_global.user_id;
x_paymentRec.last_update_date := sysdate;
* initialize it by inserting a dummy record. This dummy
* record will get overwritten below.
*/
IF (x_paymentTab.COUNT = 0) THEN
getNextPaymentID(x_currentPaymentID);
* Insert the first record into the table. This
* is a dummy record.
*/
x_paymentTab(x_paymentTab.COUNT + 1) := x_paymentRec;
* PLSQL payments table with the updated record.
*/
x_paymentTab(x_paymentTab.COUNT) := x_paymentRec;
print_debuginfo(l_module_name, 'Inserted document : '
|| x_docsInPmtRec.document_id || ' into existing payment: '
|| x_currentPaymentId);
* doc, and insert the doc into the docs array.
*/
x_docsInPmtRec.payment_id := x_paymentRec.payment_id;
END insertDocIntoPayment;
| insertDocIntoPayment
|
| PURPOSE:
| Inserts a given document into a currently running payment
| or into a new payment as per given flag.
|
| This method is called by every grouping rule to add
| a given document into a current payment/new payment.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertDocIntoPayment(
x_paymentRec IN OUT NOCOPY IBY_PAYMENTS_ALL%ROWTYPE,
--x_paymentTab IN OUT NOCOPY paymentTabType,
p_calcDocInfo IN VARCHAR2,
p_newPaymentFlag IN BOOLEAN,
x_currentPaymentId IN OUT NOCOPY IBY_PAYMENTS_ALL.payment_id%TYPE,
x_docsInPmtCount IN OUT NOCOPY NUMBER,
x_commonAttributes IN OUT NOCOPY commonAttributesTabType,
p_trx_line_index IN BINARY_INTEGER
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insertDocIntoPayment';
* table is updated with the details of this document
* within this if-else block.
*
* We need to do this each time we enter this procedure
* because this might well be the last document in
* in the payment request, and this procedure may
* not be called again for this payment request. So
* the PLSQL payments table should always be up-to-date
* when it exits this procedure.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Before checking the new pmt flag');
* document as a constituent, and insert this record
* into the PLSQL payments table.
*/
/*
x_paymentRec.payment_id := x_currentPaymentId;
IBY_PAYGROUP_PUB.pmtTable.last_updated_by(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_login(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_date(l_trx_pmt_index) := sysdate;
--[ actually changed in postHook, bt b4 that insert happens]
IBY_PAYGROUP_PUB.pmtTable.payment_reference_number(l_trx_pmt_index) :=NULL;
x_paymentRec.last_updated_by := fnd_global.user_id;
x_paymentRec.last_update_login := fnd_global.user_id;
x_paymentRec.last_update_date := sysdate;
* initialize it by inserting a dummy record. This dummy
* record will get overwritten below.
*/
-- IF (x_paymentTab.COUNT = 0) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Before checking for NULL value in pmtTable ');
--[ actually changed in postHook, bt b4 that insert happens]
IBY_PAYGROUP_PUB.pmtTable.payment_reference_number(l_trx_pmt_index) :=NULL;
IBY_PAYGROUP_PUB.pmtTable.last_updated_by(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_login(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_date(l_trx_pmt_index) := sysdate;
* Insert the first record into the table. This
* is a dummy record.
*/
-- x_paymentTab(x_paymentTab.COUNT + 1) := x_paymentRec;
* PLSQL payments table with the updated record.
*/
iby_disburse_submit_pub_pkg.docspayTab.payment_id(p_trx_line_index) := x_currentPaymentId;
print_debuginfo(l_module_name, l_module_name||':Exception while inserting Doc into Payment');
END insertDocIntoPayment;
| insertPayments
|
| PURPOSE:
| Performs a bulk insert of all created payments from PLSQL
| table into IBY_PAYMENTS_ALL table.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertPayments(
p_paymentTab IN paymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertPayments';
|| ' were found to insert into IBY_PAYMENTS_ALL table.'
|| ' Possible data corruption issue.');
* array. These column arrays will be used in the bulk insert.
*/
FOR i in p_paymentTab.FIRST..p_paymentTab.LAST LOOP
paymentTab.payment_id(i)
:= p_paymentTab(i).payment_id;
paymentTab.last_updated_by(i)
:= NVL(p_paymentTab(i).last_updated_by, fnd_global.user_id);
paymentTab.last_update_login(i)
:= NVL(p_paymentTab(i).last_update_login, fnd_global.user_id);
paymentTab.last_update_date(i)
:= NVL(p_paymentTab(i).last_update_date, sysdate);
SELECT vat_registration_num
INTO l_tax_registration_num(i)
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_paymentTab(i).supplier_site_id;
SELECT asup.vat_registration_num
INTO l_tax_registration_num(i)
FROM ap_suppliers asup
, ap_supplier_sites_all asups
WHERE asups.vendor_site_id = p_paymentTab(i).supplier_site_id
AND asup.vendor_id = asups.vendor_id;
* Bulk insert into IBY_PAYMENTS_ALL table using the
* named columns syntax. This avoids any dependencies on
* column order.
*/
FORALL i in nvl(paymentTab.payment_id.FIRST,0) .. nvl(paymentTab.payment_id.LAST,-99)
INSERT INTO IBY_PAYMENTS_ALL
(
payment_id,
payment_method_code,
payment_service_request_id,
process_type,
payment_status,
payments_complete_flag,
payment_function,
payment_amount,
payment_currency_code,
bill_payable_flag,
exclusive_payment_flag,
separate_remit_advice_req_flag,
internal_bank_account_id,
org_id,
org_type,
legal_entity_id,
declare_payment_flag,
delivery_channel_code,
ext_payee_id,
payment_instruction_id,
payment_profile_id,
pregrouped_payment_flag,
stop_confirmed_flag,
stop_released_flag,
stop_request_placed_flag,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
object_version_number,
payee_party_id,
party_site_id,
supplier_site_id,
payment_reason_code,
payment_reason_comments,
payment_date,
anticipated_value_date,
declaration_amount,
declaration_currency_code,
discount_amount_taken,
payment_details,
bank_charge_bearer,
bank_charge_amount,
settlement_priority,
remittance_message1,
remittance_message2,
remittance_message3,
payment_reference_number,
paper_document_number,
bank_assigned_ref_code,
external_bank_account_id,
unique_remittance_identifier,
uri_check_digit,
bank_instruction1_code,
bank_instruction2_code,
bank_instruction_details,
payment_text_message1,
payment_text_message2,
payment_text_message3,
maturity_date,
payment_due_date,
document_category_code,
document_sequence_id,
document_sequence_value,
beneficiary_party,
stop_confirmed_by,
stop_confirm_date,
stop_confirm_reason,
stop_confirm_reference,
stop_released_by,
stop_release_date,
stop_release_reason,
stop_release_reference,
stop_request_date,
stop_request_placed_by,
stop_request_reason,
stop_request_reference,
voided_by,
void_date,
void_reason,
remit_to_location_id,
completed_pmts_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ext_branch_number,
ext_bank_number,
ext_bank_account_name,
ext_bank_account_number,
ext_bank_account_type,
ext_bank_account_iban_number,
payee_name,
payee_address1,
payee_address2,
payee_address3,
payee_address4,
payee_city,
payee_postal_code,
payee_state,
payee_province,
payee_county,
payee_country,
remit_advice_delivery_method,
remit_advice_email,
remit_advice_fax,
address_source,
employee_address_code,
employee_person_id,
employee_address_id,
employee_payment_flag,
payee_tax_registration_num,
ext_inv_payee_id ,
inv_payee_party_id ,
inv_party_site_id ,
inv_supplier_site_id ,
inv_beneficiary_party ,
inv_payee_name ,
inv_payee_address1 ,
inv_payee_address2 ,
inv_payee_address3 ,
inv_payee_address4 ,
inv_payee_city ,
inv_payee_postal_code ,
inv_payee_state ,
inv_payee_province ,
inv_payee_county ,
inv_payee_country ,
inv_payee_party_name ,
inv_payee_le_reg_num,
inv_payee_tax_reg_num,
inv_payee_address_concat ,
inv_beneficiary_name ,
inv_payee_party_number ,
inv_payee_alternate_name ,
inv_payee_site_alt_name,
inv_payee_supplier_number ,
inv_payee_first_party_ref,
ext_bnk_acct_ownr_inv_prty_id,
ext_bnk_branch_inv_prty_id,
ext_bnk_acct_ownr_inv_prty_nme,
inv_payee_party_attr_cat,
inv_payee_supplier_attr_cat,
inv_payee_spplr_site_attr_cat,
inv_payee_supplier_site_name ,
inv_payee_spplr_site_alt_name,
inv_payee_supplier_id
)
VALUES
(
paymentTab.payment_id(i),
paymentTab.payment_method_code(i),
paymentTab.payment_service_request_id(i),
paymentTab.process_type(i),
paymentTab.payment_status(i),
paymentTab.payments_complete_flag(i),
paymentTab.payment_function(i),
paymentTab.payment_amount(i),
paymentTab.payment_currency_code(i),
paymentTab.bill_payable_flag(i),
paymentTab.exclusive_payment_flag(i),
paymentTab.sep_remit_advice_req_flag(i),
paymentTab.internal_bank_account_id(i),
paymentTab.org_id(i),
paymentTab.org_type(i),
paymentTab.legal_entity_id(i),
paymentTab.declare_payment_flag(i),
paymentTab.delivery_channel_code(i),
paymentTab.ext_payee_id(i),
paymentTab.payment_instruction_id(i),
paymentTab.payment_profile_id(i),
paymentTab.pregrouped_payment_flag(i),
paymentTab.stop_confirmed_flag(i),
paymentTab.stop_released_flag(i),
paymentTab.stop_request_placed_flag(i),
paymentTab.created_by(i),
paymentTab.creation_date(i),
paymentTab.last_updated_by(i),
paymentTab.last_update_login(i),
paymentTab.last_update_date(i),
paymentTab.object_version_number(i),
paymentTab.payee_party_id(i),
paymentTab.party_site_id(i),
paymentTab.supplier_site_id(i),
paymentTab.payment_reason_code(i),
paymentTab.payment_reason_comments(i),
paymentTab.payment_date(i),
paymentTab.anticipated_value_date(i),
paymentTab.declaration_amount(i),
paymentTab.declaration_currency_code(i),
paymentTab.discount_amount_taken(i),
paymentTab.payment_details(i),
paymentTab.bank_charge_bearer(i),
paymentTab.bank_charge_amount(i),
paymentTab.settlement_priority(i),
paymentTab.remittance_message1(i),
paymentTab.remittance_message2(i),
paymentTab.remittance_message3(i),
paymentTab.payment_reference_number(i),
paymentTab.paper_document_number(i),
paymentTab.bank_assigned_ref_code(i),
paymentTab.external_bank_account_id(i),
paymentTab.unique_remittance_identifier(i),
paymentTab.uri_check_digit(i),
paymentTab.bank_instruction1_code(i),
paymentTab.bank_instruction2_code(i),
paymentTab.bank_instruction_details(i),
paymentTab.payment_text_message1(i),
paymentTab.payment_text_message2(i),
paymentTab.payment_text_message3(i),
paymentTab.maturity_date(i),
paymentTab.payment_due_date(i),
paymentTab.document_category_code(i),
paymentTab.document_sequence_id(i),
paymentTab.document_sequence_value(i),
paymentTab.beneficiary_party(i),
paymentTab.stop_confirmed_by(i),
paymentTab.stop_confirm_date(i),
paymentTab.stop_confirm_reason(i),
paymentTab.stop_confirm_reference(i),
paymentTab.stop_released_by(i),
paymentTab.stop_release_date(i),
paymentTab.stop_release_reason(i),
paymentTab.stop_release_reference(i),
paymentTab.stop_request_date(i),
paymentTab.stop_request_placed_by(i),
paymentTab.stop_request_reason(i),
paymentTab.stop_request_reference(i),
paymentTab.voided_by(i),
paymentTab.void_date(i),
paymentTab.void_reason(i),
paymentTab.remit_to_location_id(i),
paymentTab.completed_pmts_group_id(i),
paymentTab.attribute_category(i),
paymentTab.attribute1(i),
paymentTab.attribute2(i),
paymentTab.attribute3(i),
paymentTab.attribute4(i),
paymentTab.attribute5(i),
paymentTab.attribute6(i),
paymentTab.attribute7(i),
paymentTab.attribute8(i),
paymentTab.attribute9(i),
paymentTab.attribute10(i),
paymentTab.attribute11(i),
paymentTab.attribute12(i),
paymentTab.attribute13(i),
paymentTab.attribute14(i),
paymentTab.attribute15(i),
paymentTab.ext_branch_number(i),
paymentTab.ext_bank_number(i),
paymentTab.ext_bank_account_name(i),
paymentTab.ext_bank_account_number(i),
paymentTab.ext_bank_account_type(i),
paymentTab.ext_bank_account_iban_number(i),
paymentTab.payee_name(i),
paymentTab.payee_address1(i),
paymentTab.payee_address2(i),
paymentTab.payee_address3(i),
paymentTab.payee_address4(i),
paymentTab.payee_city(i),
paymentTab.payee_postal_code(i),
paymentTab.payee_state(i),
paymentTab.payee_province(i),
paymentTab.payee_county(i),
paymentTab.payee_country(i),
paymentTab.remit_advice_delivery_method(i),
paymentTab.remit_advice_email(i),
paymentTab.remit_advice_fax(i),
paymentTab.address_source(i),
paymentTab.employee_address_code(i),
paymentTab.employee_person_id(i),
paymentTab.employee_address_id(i),
paymentTab.employee_payment_flag(i),
l_tax_registration_num(i),
paymentTab.ext_inv_payee_id(i),
paymentTab.inv_payee_party_id(i),
paymentTab.inv_party_site_id(i),
paymentTab.inv_supplier_site_id(i),
paymentTab.inv_beneficiary_party(i),
paymentTab.inv_payee_name(i),
paymentTab.inv_payee_address1(i),
paymentTab.inv_payee_address2(i),
paymentTab.inv_payee_address3(i),
paymentTab.inv_payee_address4(i),
paymentTab.inv_payee_city(i),
paymentTab.inv_payee_postal_code(i),
paymentTab.inv_payee_state(i),
paymentTab.inv_payee_province(i),
paymentTab.inv_payee_county(i),
paymentTab.inv_payee_country(i),
paymentTab.inv_payee_party_name(i),
paymentTab.inv_payee_le_reg_num(i),
paymentTab.inv_payee_tax_reg_num(i),
paymentTab.inv_payee_address_concat(i),
paymentTab.inv_beneficiary_name(i),
paymentTab.inv_payee_party_number(i),
paymentTab.inv_payee_alternate_name(i),
paymentTab.inv_payee_site_alt_name(i),
paymentTab.inv_payee_supplier_number(i),
paymentTab.inv_payee_first_party_ref(i),
paymentTab.ext_bnk_acct_ownr_inv_prty_id(i),
paymentTab.ext_bnk_branch_inv_prty_id(i),
paymentTab.ext_bnk_acct_ownr_inv_prty_nme(i),
paymentTab.inv_payee_party_attr_cat(i),
paymentTab.inv_payee_supplier_attr_cat(i),
paymentTab.inv_payee_spplr_site_attr_cat(i),
paymentTab.inv_payee_supplier_site_name(i),
paymenttab.inv_payee_spplr_site_alt_name(i),
paymenttab.inv_payee_supplier_id(i)
);
delete_paymentTab;
END insertPayments;
| insertPayments
|
| PURPOSE:
| Performs a bulk insert of all created payments from PLSQL
| table into IBY_PAYMENTS_ALL table.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertPayments
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertPayments';
|| ' were found to insert into IBY_PAYMENTS_ALL table.'
|| ' Possible data corruption issue.');
* array. These column arrays will be used in the bulk insert.
*/
/* Removing the code to copy data into paymentTab, as we
* already have record of table to directly copy into
* IBY_PAYMENTS_ALL table */
/*
* Bulk insert into IBY_PAYMENTS_ALL table using the
* named columns syntax. This avoids any dependencies on
* column order.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FOR i in nvl(IBY_PAYGROUP_PUB.pmtTable.payment_id.FIRST,0) .. nvl(IBY_PAYGROUP_PUB.pmtTable.payment_id.LAST,-99) LOOP
/*
print_debuginfo(l_module_name, IBY_PAYGROUP_PUB.pmtTable.payment_id(i));
print_debuginfo(l_module_name,IBY_PAYGROUP_PUB.pmtTable.last_updated_by(i));
print_debuginfo(l_module_name,IBY_PAYGROUP_PUB.pmtTable.last_update_login(i));
print_debuginfo(l_module_name,IBY_PAYGROUP_PUB.pmtTable.last_update_date(i));
INSERT INTO IBY_PAYMENTS_ALL
(
payment_id,
payment_method_code,
payment_service_request_id,
process_type,
payment_status,
payments_complete_flag,
payment_function,
payment_amount,
payment_currency_code,
bill_payable_flag,
exclusive_payment_flag,
separate_remit_advice_req_flag,
internal_bank_account_id,
org_id,
org_type,
legal_entity_id,
declare_payment_flag,
delivery_channel_code,
ext_payee_id,
payment_instruction_id,
payment_profile_id,
pregrouped_payment_flag,
stop_confirmed_flag,
stop_released_flag,
stop_request_placed_flag,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
object_version_number,
payee_party_id,
party_site_id,
supplier_site_id,
payment_reason_code,
payment_reason_comments,
payment_date,
anticipated_value_date,
declaration_amount,
declaration_currency_code,
discount_amount_taken,
payment_details,
bank_charge_bearer,
bank_charge_amount,
settlement_priority,
remittance_message1,
remittance_message2,
remittance_message3,
payment_reference_number,
paper_document_number,
bank_assigned_ref_code,
external_bank_account_id,
unique_remittance_identifier,
uri_check_digit,
bank_instruction1_code,
bank_instruction2_code,
bank_instruction_details,
payment_text_message1,
payment_text_message2,
payment_text_message3,
maturity_date,
payment_due_date,
document_category_code,
document_sequence_id,
document_sequence_value,
beneficiary_party,
stop_confirmed_by,
stop_confirm_date,
stop_confirm_reason,
stop_confirm_reference,
stop_released_by,
stop_release_date,
stop_release_reason,
stop_release_reference,
stop_request_date,
stop_request_placed_by,
stop_request_reason,
stop_request_reference,
voided_by,
void_date,
void_reason,
remit_to_location_id,
completed_pmts_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ext_branch_number,
ext_bank_number,
ext_bank_account_name,
ext_bank_account_number,
ext_bank_account_type,
ext_bank_account_iban_number,
payee_name,
payee_address1,
payee_address2,
payee_address3,
payee_address4,
payee_city,
payee_postal_code,
payee_state,
payee_province,
payee_county,
payee_country,
remit_advice_delivery_method,
remit_advice_email,
remit_advice_fax,
address_source,
employee_address_code,
employee_person_id,
employee_address_id,
employee_payment_flag,
ext_inv_payee_id ,
inv_payee_party_id ,
inv_party_site_id ,
inv_supplier_site_id ,
inv_beneficiary_party ,
inv_payee_name ,
inv_payee_address1 ,
inv_payee_address2 ,
inv_payee_address3 ,
inv_payee_address4 ,
inv_payee_city ,
inv_payee_postal_code ,
inv_payee_state ,
inv_payee_province ,
inv_payee_county ,
inv_payee_country ,
inv_payee_party_name ,
inv_payee_le_reg_num,
inv_payee_tax_reg_num,
inv_payee_address_concat ,
inv_beneficiary_name ,
inv_payee_party_number ,
inv_payee_alternate_name ,
inv_payee_site_alt_name,
inv_payee_supplier_number ,
inv_payee_first_party_ref,
ext_bnk_acct_ownr_inv_prty_id,
ext_bnk_branch_inv_prty_id,
ext_bnk_acct_ownr_inv_prty_nme,
inv_payee_party_attr_cat,
inv_payee_supplier_attr_cat,
inv_payee_spplr_site_attr_cat,
inv_payee_supplier_site_name ,
inv_payee_spplr_site_alt_name,
inv_payee_supplier_id,
payer_party_number ,
payer_party_site_name ,
payer_legal_entity_name ,
payer_tax_registration_num ,
payer_le_registration_num ,
payer_party_id ,
payer_location_id ,
payer_party_attr_category ,
payer_le_attr_category ,
payer_abbreviated_agency_code ,
payer_federal_us_employer_id ,
int_bank_name ,
int_bank_number ,
int_bank_branch_number ,
int_bank_branch_name ,
int_eft_swift_code ,
int_bank_account_number ,
int_bank_account_name ,
int_bank_account_iban ,
int_bank_acct_agency_loc_code ,
int_bank_branch_party_id ,
int_bank_alt_name ,
int_bank_branch_alt_name ,
int_bank_account_alt_name ,
int_bank_account_num_elec ,
int_bank_branch_location_id ,
int_bank_branch_eft_user_num ,
payee_party_number ,
payee_party_name ,
payee_alternate_name ,
payee_address_concat ,
beneficiary_name ,
payee_party_attr_category ,
payee_spplr_site_attr_category ,
payee_supplier_site_name ,
payee_party_site_name ,
payee_addressee ,
payee_site_alternate_name ,
payee_supplier_number ,
payee_first_party_reference ,
payee_supplier_attr_category ,
payee_supplier_id ,
payee_tax_registration_num ,
payee_le_registration_num ,
ext_bank_name,
ext_bank_branch_name ,
ext_eft_swift_code ,
ext_bank_acct_pmt_factor_flag ,
ext_bank_acct_owner_party_id ,
ext_bank_branch_party_id ,
ext_bank_alt_name ,
ext_bank_branch_alt_name ,
ext_bank_account_alt_name ,
ext_bank_account_num_elec ,
ext_bank_branch_location_id ,
ext_bank_acct_owner_party_name ,
--remit_advice_delivery_method ,
--remit_advice_email ,
--remit_advice_fax ,
delivery_channel_format_value ,
declaration_exch_rate_type ,
declaration_format ,
payment_profile_acct_name ,
payment_profile_sys_name ,
payment_reason_format_value ,
bank_instruction1_format_value ,
bank_instruction2_format_value ,
org_name ,
int_bank_branch_rfc_identifier ,
payment_process_request_name ,
source_product ,
affects_rejection_level -- AWT Enh 16296267
)
VALUES
(
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_method_code(i),
IBY_PAYGROUP_PUB.pmtTable.payment_service_request_id(i),
IBY_PAYGROUP_PUB.pmtTable.process_type(i),
IBY_PAYGROUP_PUB.pmtTable.payment_status(i),
IBY_PAYGROUP_PUB.pmtTable.payments_complete_flag(i),
IBY_PAYGROUP_PUB.pmtTable.payment_function(i),
IBY_PAYGROUP_PUB.pmtTable.payment_amount(i),
IBY_PAYGROUP_PUB.pmtTable.payment_currency_code(i),
IBY_PAYGROUP_PUB.pmtTable.bill_payable_flag(i),
IBY_PAYGROUP_PUB.pmtTable.exclusive_payment_flag(i),
IBY_PAYGROUP_PUB.pmtTable.sep_remit_advice_req_flag(i),
IBY_PAYGROUP_PUB.pmtTable.internal_bank_account_id(i),
IBY_PAYGROUP_PUB.pmtTable.org_id(i),
IBY_PAYGROUP_PUB.pmtTable.org_type(i),
IBY_PAYGROUP_PUB.pmtTable.legal_entity_id(i),
IBY_PAYGROUP_PUB.pmtTable.declare_payment_flag(i),
IBY_PAYGROUP_PUB.pmtTable.delivery_channel_code(i),
IBY_PAYGROUP_PUB.pmtTable.ext_payee_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_instruction_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_profile_id(i),
IBY_PAYGROUP_PUB.pmtTable.pregrouped_payment_flag(i),
IBY_PAYGROUP_PUB.pmtTable.stop_confirmed_flag(i),
IBY_PAYGROUP_PUB.pmtTable.stop_released_flag(i),
IBY_PAYGROUP_PUB.pmtTable.stop_request_placed_flag(i),
IBY_PAYGROUP_PUB.pmtTable.created_by(i),
IBY_PAYGROUP_PUB.pmtTable.creation_date(i),
IBY_PAYGROUP_PUB.pmtTable.last_updated_by(i),
IBY_PAYGROUP_PUB.pmtTable.last_update_login(i),
IBY_PAYGROUP_PUB.pmtTable.last_update_date(i),
IBY_PAYGROUP_PUB.pmtTable.object_version_number(i),
IBY_PAYGROUP_PUB.pmtTable.payee_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.party_site_id(i),
IBY_PAYGROUP_PUB.pmtTable.supplier_site_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_reason_code(i),
IBY_PAYGROUP_PUB.pmtTable.payment_reason_comments(i),
IBY_PAYGROUP_PUB.pmtTable.payment_date(i),
IBY_PAYGROUP_PUB.pmtTable.anticipated_value_date(i),
IBY_PAYGROUP_PUB.pmtTable.declaration_amount(i),
IBY_PAYGROUP_PUB.pmtTable.declaration_currency_code(i),
IBY_PAYGROUP_PUB.pmtTable.discount_amount_taken(i),
IBY_PAYGROUP_PUB.pmtTable.payment_details(i),
IBY_PAYGROUP_PUB.pmtTable.bank_charge_bearer(i),
IBY_PAYGROUP_PUB.pmtTable.bank_charge_amount(i),
IBY_PAYGROUP_PUB.pmtTable.settlement_priority(i),
IBY_PAYGROUP_PUB.pmtTable.remittance_message1(i),
IBY_PAYGROUP_PUB.pmtTable.remittance_message2(i),
IBY_PAYGROUP_PUB.pmtTable.remittance_message3(i),
IBY_PAYGROUP_PUB.pmtTable.payment_reference_number(i),
IBY_PAYGROUP_PUB.pmtTable.paper_document_number(i),
IBY_PAYGROUP_PUB.pmtTable.bank_assigned_ref_code(i),
IBY_PAYGROUP_PUB.pmtTable.external_bank_account_id(i),
IBY_PAYGROUP_PUB.pmtTable.unique_remittance_identifier(i),
IBY_PAYGROUP_PUB.pmtTable.uri_check_digit(i),
IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_code(i),
IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_code(i),
IBY_PAYGROUP_PUB.pmtTable.bank_instruction_details(i),
IBY_PAYGROUP_PUB.pmtTable.payment_text_message1(i),
IBY_PAYGROUP_PUB.pmtTable.payment_text_message2(i),
IBY_PAYGROUP_PUB.pmtTable.payment_text_message3(i),
IBY_PAYGROUP_PUB.pmtTable.maturity_date(i),
IBY_PAYGROUP_PUB.pmtTable.payment_due_date(i),
IBY_PAYGROUP_PUB.pmtTable.document_category_code(i),
IBY_PAYGROUP_PUB.pmtTable.document_sequence_id(i),
IBY_PAYGROUP_PUB.pmtTable.document_sequence_value(i),
IBY_PAYGROUP_PUB.pmtTable.beneficiary_party(i),
IBY_PAYGROUP_PUB.pmtTable.stop_confirmed_by(i),
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_date(i),
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reason(i),
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reference(i),
IBY_PAYGROUP_PUB.pmtTable.stop_released_by(i),
IBY_PAYGROUP_PUB.pmtTable.stop_release_date(i),
IBY_PAYGROUP_PUB.pmtTable.stop_release_reason(i),
IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
IBY_PAYGROUP_PUB.pmtTable.stop_request_date(i),
IBY_PAYGROUP_PUB.pmtTable.stop_request_placed_by(i),
IBY_PAYGROUP_PUB.pmtTable.stop_request_reason(i),
IBY_PAYGROUP_PUB.pmtTable.stop_request_reference(i),
IBY_PAYGROUP_PUB.pmtTable.voided_by(i),
IBY_PAYGROUP_PUB.pmtTable.void_date(i),
IBY_PAYGROUP_PUB.pmtTable.void_reason(i),
IBY_PAYGROUP_PUB.pmtTable.remit_to_location_id(i),
IBY_PAYGROUP_PUB.pmtTable.completed_pmts_group_id(i),
IBY_PAYGROUP_PUB.pmtTable.attribute_category(i),
IBY_PAYGROUP_PUB.pmtTable.attribute1(i),
IBY_PAYGROUP_PUB.pmtTable.attribute2(i),
IBY_PAYGROUP_PUB.pmtTable.attribute3(i),
IBY_PAYGROUP_PUB.pmtTable.attribute4(i),
IBY_PAYGROUP_PUB.pmtTable.attribute5(i),
IBY_PAYGROUP_PUB.pmtTable.attribute6(i),
IBY_PAYGROUP_PUB.pmtTable.attribute7(i),
IBY_PAYGROUP_PUB.pmtTable.attribute8(i),
IBY_PAYGROUP_PUB.pmtTable.attribute9(i),
IBY_PAYGROUP_PUB.pmtTable.attribute10(i),
IBY_PAYGROUP_PUB.pmtTable.attribute11(i),
IBY_PAYGROUP_PUB.pmtTable.attribute12(i),
IBY_PAYGROUP_PUB.pmtTable.attribute13(i),
IBY_PAYGROUP_PUB.pmtTable.attribute14(i),
IBY_PAYGROUP_PUB.pmtTable.attribute15(i),
IBY_PAYGROUP_PUB.pmtTable.ext_branch_number(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_number(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_number(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_type(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_iban_number(i),
IBY_PAYGROUP_PUB.pmtTable.payee_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_address1(i),
IBY_PAYGROUP_PUB.pmtTable.payee_address2(i),
IBY_PAYGROUP_PUB.pmtTable.payee_address3(i),
IBY_PAYGROUP_PUB.pmtTable.payee_address4(i),
IBY_PAYGROUP_PUB.pmtTable.payee_city(i),
IBY_PAYGROUP_PUB.pmtTable.payee_postal_code(i),
IBY_PAYGROUP_PUB.pmtTable.payee_state(i),
IBY_PAYGROUP_PUB.pmtTable.payee_province(i),
IBY_PAYGROUP_PUB.pmtTable.payee_county(i),
IBY_PAYGROUP_PUB.pmtTable.payee_country(i),
IBY_PAYGROUP_PUB.pmtTable.remit_advice_delivery_method(i),
IBY_PAYGROUP_PUB.pmtTable.remit_advice_email(i),
IBY_PAYGROUP_PUB.pmtTable.remit_advice_fax(i),
IBY_PAYGROUP_PUB.pmtTable.address_source(i),
IBY_PAYGROUP_PUB.pmtTable.employee_address_code(i),
IBY_PAYGROUP_PUB.pmtTable.employee_person_id(i),
IBY_PAYGROUP_PUB.pmtTable.employee_address_id(i),
IBY_PAYGROUP_PUB.pmtTable.employee_payment_flag(i),
IBY_PAYGROUP_PUB.pmtTable.ext_inv_payee_id(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.inv_party_site_id(i),
IBY_PAYGROUP_PUB.pmtTable.inv_supplier_site_id(i),
IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_party(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address1(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address2(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address3(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address4(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_city(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_postal_code(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_state(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_province(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_county(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_country(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_le_reg_num(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_tax_reg_num(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address_concat(i),
IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_number(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_alternate_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_site_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_number(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_id(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_branch_inv_prty_id(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_nme(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_attr_cat(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_attr_cat(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_attr_cat(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_site_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_id(i),
IBY_PAYGROUP_PUB.pmtTable.payer_party_number(i),
IBY_PAYGROUP_PUB.pmtTable.payer_party_site_name(i),
IBY_PAYGROUP_PUB.pmtTable.payer_legal_entity_name(i),
IBY_PAYGROUP_PUB.pmtTable.payer_tax_registration_num(i),
IBY_PAYGROUP_PUB.pmtTable.payer_le_registration_num(i),
IBY_PAYGROUP_PUB.pmtTable.payer_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.payer_location_id(i),
IBY_PAYGROUP_PUB.pmtTable.payer_party_attr_category(i),
IBY_PAYGROUP_PUB.pmtTable.payer_le_attr_category(i),
IBY_PAYGROUP_PUB.pmtTable.payer_abbreviated_agency_code(i),
IBY_PAYGROUP_PUB.pmtTable.payer_federal_us_employer_id(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_number(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_number(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_eft_swift_code(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_number(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_iban(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_acct_agency_loc_code(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_num_elec(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_location_id(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_eft_user_num(i),
IBY_PAYGROUP_PUB.pmtTable.payee_party_number(i),
IBY_PAYGROUP_PUB.pmtTable.payee_party_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_alt_name(i), -- Bug 6175102
IBY_PAYGROUP_PUB.pmtTable.payee_address_concat(i),
IBY_PAYGROUP_PUB.pmtTable.beneficiary_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_party_atr_cat(i),
IBY_PAYGROUP_PUB.pmtTable.payee_spplr_site_attr_category(i),
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_site_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_party_site_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_addressee(i),
IBY_PAYGROUP_PUB.pmtTable.payee_site_alternate_name(i),
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_number(i),
IBY_PAYGROUP_PUB.pmtTable.payee_first_party_reference(i),
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_attr_category(i),
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_id(i),
IBY_PAYGROUP_PUB.pmtTable.payee_tax_registration_num(i),
IBY_PAYGROUP_PUB.pmtTable.payee_le_registration_num(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_eft_swift_code(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_pmt_factor_flag(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_party_id(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_alt_name(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_num_elec(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_location_id(i),
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_name(i),
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_delivery_method(i),
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_email(i),
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_fax(i),
IBY_PAYGROUP_PUB.pmtTable.delivery_channel_format_value(i),
IBY_PAYGROUP_PUB.pmtTable.declaration_exch_rate_type(i),
IBY_PAYGROUP_PUB.pmtTable.declaration_format(i),
IBY_PAYGROUP_PUB.pmtTable.payment_profile_acct_name(i),
IBY_PAYGROUP_PUB.pmtTable.payment_profile_sys_name(i),
IBY_PAYGROUP_PUB.pmtTable.payment_reason_format_value(i),
IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_format_value(i),
IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_format_value(i),
IBY_PAYGROUP_PUB.pmtTable.org_name(i),
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_rfc_identifier(i),
IBY_PAYGROUP_PUB.pmtTable.payment_process_request_name(i),
IBY_PAYGROUP_PUB.pmtTable.source_product(i),
IBY_PAYGROUP_PUB.pmtTable.affects_rejection_level(i) -- AWT Enh 16296267
);
print_debuginfo(l_module_name, l_module_name||':'|| ' While inserting payment with following context : ',FND_LOG.LEVEL_UNEXPECTED );
print_debuginfo(l_module_name, l_module_name||':Exception while inserting Payments');
END insertPayments;
PROCEDURE delete_paymentTab IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.delete_paymentTab';
paymentTab.payment_id.delete;
paymentTab.payment_method_code.delete;
paymentTab.payment_service_request_id.delete;
paymentTab.process_type.delete;
paymentTab.payment_status.delete;
paymentTab.payments_complete_flag.delete;
paymentTab.payment_function.delete;
paymentTab.payment_amount.delete;
paymentTab.payment_currency_code.delete;
paymentTab.bill_payable_flag.delete;
paymentTab.exclusive_payment_flag.delete;
paymentTab.sep_remit_advice_req_flag.delete;
paymentTab.internal_bank_account_id.delete;
paymentTab.org_id.delete;
paymentTab.org_type.delete;
paymentTab.legal_entity_id.delete;
paymentTab.declare_payment_flag.delete;
paymentTab.delivery_channel_code.delete;
paymentTab.ext_payee_id.delete;
paymentTab.payment_instruction_id.delete;
paymentTab.payment_profile_id.delete;
paymentTab.pregrouped_payment_flag.delete;
paymentTab.stop_confirmed_flag.delete;
paymentTab.stop_released_flag.delete;
paymentTab.stop_request_placed_flag.delete;
paymentTab.created_by.delete;
paymentTab.creation_date.delete;
paymentTab.last_updated_by.delete;
paymentTab.last_update_login.delete;
paymentTab.last_update_date.delete;
paymentTab.object_version_number.delete;
paymentTab.payee_party_id.delete;
paymentTab.party_site_id.delete;
paymentTab.supplier_site_id.delete;
paymentTab.payment_reason_code.delete;
paymentTab.payment_reason_comments.delete;
paymentTab.payment_date.delete;
paymentTab.anticipated_value_date.delete;
paymentTab.declaration_amount.delete;
paymentTab.declaration_currency_code.delete;
paymentTab.discount_amount_taken.delete;
paymentTab.payment_details.delete;
paymentTab.bank_charge_bearer.delete;
paymentTab.bank_charge_amount.delete;
paymentTab.settlement_priority.delete;
paymentTab.remittance_message1.delete;
paymentTab.remittance_message2.delete;
paymentTab.remittance_message3.delete;
paymentTab.payment_reference_number.delete;
paymentTab.paper_document_number.delete;
paymentTab.bank_assigned_ref_code.delete;
paymentTab.external_bank_account_id.delete;
paymentTab.unique_remittance_identifier.delete;
paymentTab.uri_check_digit.delete;
paymentTab.bank_instruction1_code.delete;
paymentTab.bank_instruction2_code.delete;
paymentTab.bank_instruction_details.delete;
paymentTab.payment_text_message1.delete;
paymentTab.payment_text_message2.delete;
paymentTab.payment_text_message3.delete;
paymentTab.maturity_date.delete;
paymentTab.payment_due_date.delete;
paymentTab.document_category_code.delete;
paymentTab.document_sequence_id.delete;
paymentTab.document_sequence_value.delete;
paymentTab.beneficiary_party.delete;
paymentTab.stop_confirmed_by.delete;
paymentTab.stop_confirm_date.delete;
paymentTab.stop_confirm_reason.delete;
paymentTab.stop_confirm_reference.delete;
paymentTab.stop_released_by.delete;
paymentTab.stop_release_date.delete;
paymentTab.stop_release_reason.delete;
paymentTab.stop_release_reference.delete;
paymentTab.stop_request_date.delete;
paymentTab.stop_request_placed_by.delete;
paymentTab.stop_request_reason.delete;
paymentTab.stop_request_reference.delete;
paymentTab.voided_by.delete;
paymentTab.void_date.delete;
paymentTab.void_reason.delete;
paymentTab.remit_to_location_id.delete;
paymentTab.completed_pmts_group_id.delete;
paymentTab.attribute_category.delete;
paymentTab.attribute1.delete;
paymentTab.attribute2.delete;
paymentTab.attribute3.delete;
paymentTab.attribute4.delete;
paymentTab.attribute5.delete;
paymentTab.attribute6.delete;
paymentTab.attribute7.delete;
paymentTab.attribute8.delete;
paymentTab.attribute9.delete;
paymentTab.attribute10.delete;
paymentTab.attribute11.delete;
paymentTab.attribute12.delete;
paymentTab.attribute13.delete;
paymentTab.attribute14.delete;
paymentTab.attribute15.delete;
paymentTab.ext_branch_number.delete;
paymentTab.ext_bank_number.delete;
paymentTab.ext_bank_account_name.delete;
paymentTab.ext_bank_account_number.delete;
paymentTab.ext_bank_account_type.delete;
paymentTab.ext_bank_account_iban_number.delete;
paymentTab.payee_name.delete;
paymentTab.payee_address1.delete;
paymentTab.payee_address2.delete;
paymentTab.payee_address3.delete;
paymentTab.payee_address4.delete;
paymentTab.payee_city.delete;
paymentTab.payee_postal_code.delete;
paymentTab.payee_state.delete;
paymentTab.payee_province.delete;
paymentTab.payee_county.delete;
paymentTab.payee_country.delete;
paymentTab.remit_advice_delivery_method.delete;
paymentTab.remit_advice_email.delete;
paymentTab.remit_advice_fax.delete;
paymentTab.address_source.delete;
paymentTab.employee_address_code.delete;
paymentTab.employee_person_id.delete;
paymentTab.employee_address_id.delete;
paymentTab.employee_payment_flag.delete;
paymentTab.inv_payee_party_id.delete;
paymentTab.inv_party_site_id.delete;
paymentTab.inv_supplier_site_id.delete;
paymentTab.inv_beneficiary_party.delete;
paymentTab.ext_inv_payee_id.delete;
paymentTab.inv_payee_name.delete;
paymentTab.inv_payee_address1.delete;
paymentTab.inv_payee_address2.delete;
paymentTab.inv_payee_address3.delete;
paymentTab.inv_payee_address4.delete;
paymentTab.inv_payee_city.delete;
paymentTab.inv_payee_postal_code.delete;
paymentTab.inv_payee_state.delete;
paymentTab.inv_payee_province.delete;
paymentTab.inv_payee_county.delete;
paymentTab.inv_payee_country.delete;
paymentTab.inv_payee_party_name.delete;
paymentTab.inv_payee_le_reg_num.delete;
paymentTab.inv_payee_tax_reg_num.delete;
paymentTab.inv_payee_address_concat.delete;
paymentTab.inv_beneficiary_name.delete;
paymentTab.inv_payee_party_number.delete;
paymentTab.inv_payee_alternate_name.delete;
paymentTab.inv_payee_site_alt_name.delete;
paymentTab.inv_payee_supplier_number.delete;
paymentTab.inv_payee_first_party_ref.delete;
paymentTab.ext_bnk_acct_ownr_inv_prty_id.delete;
paymentTab.ext_bnk_branch_inv_prty_id.delete;
paymentTab.ext_bnk_acct_ownr_inv_prty_nme.delete;
paymentTab.inv_payee_party_attr_cat.delete;
paymentTab.inv_payee_supplier_attr_cat.delete;
paymentTab.inv_payee_spplr_site_attr_cat.delete;
paymentTab.inv_payee_supplier_site_name.delete;
paymentTab.inv_payee_spplr_site_alt_name.delete;
paymentTab.inv_payee_supplier_id.delete;
END delete_paymentTab;
PROCEDURE delete_pmtTable IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.delete_pmtTable';
pmtTable.payment_id.delete;
pmtTable.payment_method_code.delete;
pmtTable.payment_service_request_id.delete;
pmtTable.process_type.delete;
pmtTable.payment_status.delete;
pmtTable.payments_complete_flag.delete;
pmtTable.payment_function.delete;
pmtTable.payment_amount.delete;
pmtTable.payment_currency_code.delete;
pmtTable.bill_payable_flag.delete;
pmtTable.exclusive_payment_flag.delete;
pmtTable.sep_remit_advice_req_flag.delete;
pmtTable.internal_bank_account_id.delete;
pmtTable.org_id.delete;
pmtTable.org_type.delete;
pmtTable.legal_entity_id.delete;
pmtTable.declare_payment_flag.delete;
pmtTable.delivery_channel_code.delete;
pmtTable.ext_payee_id.delete;
pmtTable.payment_instruction_id.delete;
pmtTable.payment_profile_id.delete;
pmtTable.pregrouped_payment_flag.delete;
pmtTable.stop_confirmed_flag.delete;
pmtTable.stop_released_flag.delete;
pmtTable.stop_request_placed_flag.delete;
pmtTable.created_by.delete;
pmtTable.creation_date.delete;
pmtTable.last_updated_by.delete;
pmtTable.last_update_login.delete;
pmtTable.last_update_date.delete;
pmtTable.object_version_number.delete;
pmtTable.payee_party_id.delete;
pmtTable.party_site_id.delete;
pmtTable.supplier_site_id.delete;
pmtTable.payment_reason_code.delete;
pmtTable.payment_reason_comments.delete;
pmtTable.payment_date.delete;
pmtTable.anticipated_value_date.delete;
pmtTable.declaration_amount.delete;
pmtTable.declaration_currency_code.delete;
pmtTable.discount_amount_taken.delete;
pmtTable.payment_details.delete;
pmtTable.bank_charge_bearer.delete;
pmtTable.bank_charge_amount.delete;
pmtTable.settlement_priority.delete;
pmtTable.remittance_message1.delete;
pmtTable.remittance_message2.delete;
pmtTable.remittance_message3.delete;
pmtTable.payment_reference_number.delete;
pmtTable.paper_document_number.delete;
pmtTable.bank_assigned_ref_code.delete;
pmtTable.external_bank_account_id.delete;
pmtTable.unique_remittance_identifier.delete;
pmtTable.uri_check_digit.delete;
pmtTable.bank_instruction1_code.delete;
pmtTable.bank_instruction2_code.delete;
pmtTable.bank_instruction_details.delete;
pmtTable.payment_text_message1.delete;
pmtTable.payment_text_message2.delete;
pmtTable.payment_text_message3.delete;
pmtTable.maturity_date.delete;
pmtTable.payment_due_date.delete;
pmtTable.document_category_code.delete;
pmtTable.document_sequence_id.delete;
pmtTable.document_sequence_value.delete;
pmtTable.beneficiary_party.delete;
pmtTable.stop_confirmed_by.delete;
pmtTable.stop_confirm_date.delete;
pmtTable.stop_confirm_reason.delete;
pmtTable.stop_confirm_reference.delete;
pmtTable.stop_released_by.delete;
pmtTable.stop_release_date.delete;
pmtTable.stop_release_reason.delete;
pmtTable.stop_release_reference.delete;
pmtTable.stop_request_date.delete;
pmtTable.stop_request_placed_by.delete;
pmtTable.stop_request_reason.delete;
pmtTable.stop_request_reference.delete;
pmtTable.voided_by.delete;
pmtTable.void_date.delete;
pmtTable.void_reason.delete;
pmtTable.remit_to_location_id.delete;
pmtTable.completed_pmts_group_id.delete;
pmtTable.attribute_category.delete;
pmtTable.attribute1.delete;
pmtTable.attribute2.delete;
pmtTable.attribute3.delete;
pmtTable.attribute4.delete;
pmtTable.attribute5.delete;
pmtTable.attribute6.delete;
pmtTable.attribute7.delete;
pmtTable.attribute8.delete;
pmtTable.attribute9.delete;
pmtTable.attribute10.delete;
pmtTable.attribute11.delete;
pmtTable.attribute12.delete;
pmtTable.attribute13.delete;
pmtTable.attribute14.delete;
pmtTable.attribute15.delete;
pmtTable.ext_branch_number.delete;
pmtTable.ext_bank_number.delete;
pmtTable.ext_bank_account_name.delete;
pmtTable.ext_bank_account_number.delete;
pmtTable.ext_bank_account_type.delete;
pmtTable.ext_bank_account_iban_number.delete;
pmtTable.payee_name.delete;
pmtTable.payee_address1.delete;
pmtTable.payee_address2.delete;
pmtTable.payee_address3.delete;
pmtTable.payee_address4.delete;
pmtTable.payee_city.delete;
pmtTable.payee_postal_code.delete;
pmtTable.payee_state.delete;
pmtTable.payee_province.delete;
pmtTable.payee_county.delete;
pmtTable.payee_country.delete;
pmtTable.remit_advice_delivery_method.delete;
pmtTable.remit_advice_email.delete;
pmtTable.remit_advice_fax.delete;
pmtTable.address_source.delete;
pmtTable.employee_address_code.delete;
pmtTable.employee_person_id.delete;
pmtTable.employee_address_id.delete;
pmtTable.employee_payment_flag.delete;
pmtTable.payer_party_number.delete;
pmtTable.payer_party_site_name.delete;
pmtTable.payer_legal_entity_name.delete;
pmtTable.payer_tax_registration_num.delete;
pmtTable.payer_le_registration_num.delete;
pmtTable.payer_party_id.delete;
pmtTable.payer_location_id.delete;
pmtTable.payer_party_attr_category.delete;
pmtTable.payer_le_attr_category.delete;
pmtTable.payer_abbreviated_agency_code.delete;
pmtTable.payer_federal_us_employer_id.delete;
pmtTable.int_bank_name.delete;
pmtTable.int_bank_number.delete;
pmtTable.int_bank_branch_number.delete;
pmtTable.int_bank_branch_name.delete;
pmtTable.int_eft_swift_code.delete;
pmtTable.int_bank_account_number.delete;
pmtTable.int_bank_account_name.delete;
pmtTable.int_bank_account_iban.delete;
pmtTable.int_bank_acct_agency_loc_code.delete;
pmtTable.int_bank_branch_party_id.delete;
pmtTable.int_bank_alt_name.delete;
pmtTable.int_bank_branch_alt_name.delete;
pmtTable.int_bank_account_alt_name.delete;
pmtTable.int_bank_account_num_elec.delete;
pmtTable.int_bank_branch_location_id.delete;
pmtTable.int_bank_branch_eft_user_num.delete;
pmtTable.int_bank_branch_rfc_identifier.delete;
pmtTable.payee_site_alternate_name.delete;
pmtTable.payee_supplier_number.delete;
pmtTable.payee_first_party_reference.delete;
pmtTable.payee_supplier_attr_category.delete;
pmtTable.payee_supplier_id.delete;
pmtTable.payee_tax_registration_num.delete;
pmtTable.payee_le_registration_num.delete;
pmtTable.payee_spplr_site_attr_category.delete;
pmtTable.payee_supplier_site_name.delete;
pmtTable.ext_bank_name.delete;
pmtTable.ext_bank_branch_name.delete;
pmtTable.ext_eft_swift_code.delete;
pmtTable.ext_bank_acct_pmt_factor_flag.delete;
pmtTable.ext_bank_acct_owner_party_id.delete;
pmtTable.ext_bank_branch_party_id.delete;
pmtTable.ext_bank_alt_name.delete;
pmtTable.ext_bank_branch_alt_name.delete;
pmtTable.ext_bank_account_alt_name.delete;
pmtTable.ext_bank_account_num_elec.delete;
pmtTable.ext_bank_branch_location_id.delete;
pmtTable.ext_bank_acct_owner_party_name.delete;
pmtTable.payee_address_concat.delete;
pmtTable.declaration_exch_rate_type.delete;
pmtTable.declaration_format.delete;
pmtTable.bank_instruction1_format_value.delete;
pmtTable.bank_instruction2_format_value.delete;
pmtTable.payment_profile_acct_name.delete;
pmtTable.payment_profile_sys_name.delete;
pmtTable.payment_reason_format_value.delete;
pmtTable.delivery_channel_format_value.delete;
pmtTable.payment_process_request_name.delete;
pmtTable.source_product.delete;
pmtTable.org_name.delete;
pmtTable.calling_app_id.delete;
pmtTable.call_app_pay_service_req_cd.delete;
pmtTable.dont_pay_flg.delete;
pmtTable.dont_pay_reason_cd.delete;
pmtTable.dont_pay_desc.delete;
pmtTable.inv_payee_party_id.delete;
pmtTable.inv_party_site_id.delete;
pmtTable.inv_supplier_site_id.delete;
pmtTable.inv_beneficiary_party.delete;
pmtTable.ext_inv_payee_id.delete;
pmtTable.inv_payee_name.delete;
pmtTable.inv_payee_address1.delete;
pmtTable.inv_payee_address2.delete;
pmtTable.inv_payee_address3.delete;
pmtTable.inv_payee_address4.delete;
pmtTable.inv_payee_city.delete;
pmtTable.inv_payee_postal_code.delete;
pmtTable.inv_payee_state.delete;
pmtTable.inv_payee_province.delete;
pmtTable.inv_payee_county.delete;
pmtTable.inv_payee_country.delete;
pmtTable.inv_payee_party_name.delete;
pmtTable.inv_payee_le_reg_num.delete;
pmtTable.inv_payee_tax_reg_num.delete;
pmtTable.inv_payee_address_concat.delete;
pmtTable.inv_beneficiary_name.delete;
pmtTable.inv_payee_party_number.delete;
pmtTable.inv_payee_alternate_name.delete;
pmtTable.inv_payee_site_alt_name.delete;
pmtTable.inv_payee_supplier_number.delete;
pmtTable.inv_payee_first_party_ref.delete;
pmtTable.ext_bnk_acct_ownr_inv_prty_id.delete;
pmtTable.ext_bnk_branch_inv_prty_id.delete;
pmtTable.ext_bnk_acct_ownr_inv_prty_nme.delete;
pmtTable.inv_payee_party_attr_cat.delete;
pmtTable.inv_payee_supplier_attr_cat.delete;
pmtTable.inv_payee_spplr_site_attr_cat.delete;
pmtTable.inv_payee_supplier_site_name.delete;
pmtTable.inv_payee_spplr_site_alt_name.delete;
pmtTable.inv_payee_supplier_id.delete;
END delete_pmtTable;
| updatePayments
|
| PURPOSE:
| Performs an update of all created payments from PLSQL
| table into IBY_PAYMENTS_ALL table.
|
| The created payments have already been inserted into
| IBY_PAYMENTS_ALL after grouping. So we only need to update
| certain fields of the payment that have been changed
| after the grouping was performed.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePayments(
p_paymentTab IN paymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.updatePayments';
|| ' were found to update IBY_PAYMENTS_ALL table.');
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = p_paymentTab(i).payment_status,
payment_amount = p_paymentTab(i).payment_amount,
discount_amount_taken = p_paymentTab(i).discount_amount_taken,
declare_payment_flag = p_paymentTab(i).declare_payment_flag,
declaration_amount = p_paymentTab(i).declaration_amount,
bank_charge_amount = p_paymentTab(i).bank_charge_amount,
separate_remit_advice_req_flag
= p_paymentTab(i).
separate_remit_advice_req_flag
WHERE
payment_id = p_paymentTab(i).payment_id
;
END updatePayments;
| updatePayments
|
| PURPOSE:
| Performs an update of all created payments from PLSQL
| table into IBY_PAYMENTS_ALL table.
|
| The created payments have already been inserted into
| IBY_PAYMENTS_ALL after grouping. So we only need to update
| certain fields of the payment that have been changed
| after the grouping was performed.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePayments
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.updatePayments';
UPDATE
IBY_PAYMENTS_ALL
SET
payment_status = p_paymentTab(i).payment_status,
payment_amount = p_paymentTab(i).payment_amount,
discount_amount_taken = p_paymentTab(i).discount_amount_taken,
declare_payment_flag = p_paymentTab(i).declare_payment_flag,
declaration_amount = p_paymentTab(i).declaration_amount,
bank_charge_amount = p_paymentTab(i).bank_charge_amount,
separate_remit_advice_req_flag
= p_paymentTab(i).
separate_remit_advice_req_flag
WHERE
payment_id = p_paymentTab(i).payment_id
;
UPDATE IBY_PAYMENTS_ALL
SET
payment_id= IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
payment_method_code= IBY_PAYGROUP_PUB.pmtTable.payment_method_code(i),
payment_service_request_id= IBY_PAYGROUP_PUB.pmtTable.payment_service_request_id(i),
process_type= IBY_PAYGROUP_PUB.pmtTable.process_type(i),
payment_status= IBY_PAYGROUP_PUB.pmtTable.payment_status(i),
payments_complete_flag= IBY_PAYGROUP_PUB.pmtTable.payments_complete_flag(i),
payment_function= IBY_PAYGROUP_PUB.pmtTable.payment_function(i),
payment_amount=IBY_PAYGROUP_PUB.pmtTable.payment_amount(i),
payment_currency_code =IBY_PAYGROUP_PUB.pmtTable.payment_currency_code(i),
bill_payable_flag= IBY_PAYGROUP_PUB.pmtTable.bill_payable_flag(i),
exclusive_payment_flag =IBY_PAYGROUP_PUB.pmtTable.exclusive_payment_flag(i),
--separate_remit_advice_req_flag=IBY_PAYGROUP_PUB.pmtTable.sep_remit_advice_req_flag(i),
internal_bank_account_id= IBY_PAYGROUP_PUB.pmtTable.internal_bank_account_id(i),
org_id =IBY_PAYGROUP_PUB.pmtTable.org_id(i),
org_type=IBY_PAYGROUP_PUB.pmtTable.org_type(i),
legal_entity_id= IBY_PAYGROUP_PUB.pmtTable.legal_entity_id(i),
declare_payment_flag= IBY_PAYGROUP_PUB.pmtTable.declare_payment_flag(i),
delivery_channel_code= IBY_PAYGROUP_PUB.pmtTable.delivery_channel_code(i),
ext_payee_id=IBY_PAYGROUP_PUB.pmtTable.ext_payee_id(i),
payment_instruction_id =IBY_PAYGROUP_PUB.pmtTable.payment_instruction_id(i),
payment_profile_id= IBY_PAYGROUP_PUB.pmtTable.payment_profile_id(i),
pregrouped_payment_flag=IBY_PAYGROUP_PUB.pmtTable.pregrouped_payment_flag(i),
stop_confirmed_flag=IBY_PAYGROUP_PUB.pmtTable.stop_confirmed_flag(i),
stop_released_flag =IBY_PAYGROUP_PUB.pmtTable.stop_released_flag(i),
stop_request_placed_flag =IBY_PAYGROUP_PUB.pmtTable.stop_request_placed_flag(i),
created_by=IBY_PAYGROUP_PUB.pmtTable.created_by(i),
creation_date =IBY_PAYGROUP_PUB.pmtTable.creation_date(i),
last_updated_by =IBY_PAYGROUP_PUB.pmtTable.last_updated_by(i),
last_update_login =IBY_PAYGROUP_PUB.pmtTable.last_update_login(i),
last_update_date= IBY_PAYGROUP_PUB.pmtTable.last_update_date(i),
object_version_number =IBY_PAYGROUP_PUB.pmtTable.object_version_number(i),
payee_party_id =IBY_PAYGROUP_PUB.pmtTable.payee_party_id(i),
party_site_id =IBY_PAYGROUP_PUB.pmtTable.party_site_id(i),
supplier_site_id =IBY_PAYGROUP_PUB.pmtTable.supplier_site_id(i),
payment_reason_code =IBY_PAYGROUP_PUB.pmtTable.payment_reason_code(i),
payment_reason_comments =IBY_PAYGROUP_PUB.pmtTable.payment_reason_comments(i),
payment_date =IBY_PAYGROUP_PUB.pmtTable.payment_date(i),
anticipated_value_date= IBY_PAYGROUP_PUB.pmtTable.anticipated_value_date(i),
declaration_amount= IBY_PAYGROUP_PUB.pmtTable.declaration_amount(i),
declaration_currency_code= IBY_PAYGROUP_PUB.pmtTable.declaration_currency_code(i),
discount_amount_taken= IBY_PAYGROUP_PUB.pmtTable.discount_amount_taken(i),
payment_details= IBY_PAYGROUP_PUB.pmtTable.payment_details(i),
bank_charge_bearer= IBY_PAYGROUP_PUB.pmtTable.bank_charge_bearer(i),
bank_charge_amount=IBY_PAYGROUP_PUB.pmtTable.bank_charge_amount(i),
settlement_priority= IBY_PAYGROUP_PUB.pmtTable.settlement_priority(i),
remittance_message1= IBY_PAYGROUP_PUB.pmtTable.remittance_message1(i),
remittance_message2=IBY_PAYGROUP_PUB.pmtTable.remittance_message2(i),
remittance_message3=IBY_PAYGROUP_PUB.pmtTable.remittance_message3(i),
payment_reference_number=IBY_PAYGROUP_PUB.pmtTable.payment_reference_number(i),
paper_document_number=IBY_PAYGROUP_PUB.pmtTable.paper_document_number(i),
bank_assigned_ref_code= IBY_PAYGROUP_PUB.pmtTable.bank_assigned_ref_code(i),
external_bank_account_id= IBY_PAYGROUP_PUB.pmtTable.external_bank_account_id(i),
unique_remittance_identifier= IBY_PAYGROUP_PUB.pmtTable.unique_remittance_identifier(i),
uri_check_digit= IBY_PAYGROUP_PUB.pmtTable.uri_check_digit(i),
bank_instruction1_code= IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_code(i),
bank_instruction2_code= IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_code(i),
bank_instruction_details= IBY_PAYGROUP_PUB.pmtTable.bank_instruction_details(i),
payment_text_message1= IBY_PAYGROUP_PUB.pmtTable.payment_text_message1(i),
payment_text_message2=IBY_PAYGROUP_PUB.pmtTable.payment_text_message2(i),
payment_text_message3=IBY_PAYGROUP_PUB.pmtTable.payment_text_message3(i),
maturity_date=IBY_PAYGROUP_PUB.pmtTable.maturity_date(i),
payment_due_date=IBY_PAYGROUP_PUB.pmtTable.payment_due_date(i),
document_category_code=IBY_PAYGROUP_PUB.pmtTable.document_category_code(i),
document_sequence_id=IBY_PAYGROUP_PUB.pmtTable.document_sequence_id(i),
document_sequence_value=IBY_PAYGROUP_PUB.pmtTable.document_sequence_value(i),
beneficiary_party=IBY_PAYGROUP_PUB.pmtTable.beneficiary_party(i),
stop_confirmed_by=IBY_PAYGROUP_PUB.pmtTable.stop_confirmed_by(i),
stop_confirm_date=IBY_PAYGROUP_PUB.pmtTable.stop_confirm_date(i),
stop_confirm_reason=IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reason(i),
stop_confirm_reference=IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reference(i),
stop_released_by=IBY_PAYGROUP_PUB.pmtTable.stop_released_by(i),
stop_release_date=IBY_PAYGROUP_PUB.pmtTable.stop_release_date(i),
stop_release_reason=IBY_PAYGROUP_PUB.pmtTable.stop_release_reason(i),
stop_release_reference=IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
stop_request_date=IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
stop_request_placed_by=IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
stop_request_reason=IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
stop_request_reference=IBY_PAYGROUP_PUB.pmtTable.stop_release_reference(i),
voided_by=IBY_PAYGROUP_PUB.pmtTable.voided_by(i),
void_date=IBY_PAYGROUP_PUB.pmtTable.void_date(i),
void_reason=IBY_PAYGROUP_PUB.pmtTable.void_reason(i),
remit_to_location_id=IBY_PAYGROUP_PUB.pmtTable.remit_to_location_id(i),
completed_pmts_group_id=IBY_PAYGROUP_PUB.pmtTable.completed_pmts_group_id(i),
attribute_category=IBY_PAYGROUP_PUB.pmtTable.attribute_category(i),
attribute1=IBY_PAYGROUP_PUB.pmtTable.attribute1(i),
attribute2=IBY_PAYGROUP_PUB.pmtTable.attribute2(i),
attribute3=IBY_PAYGROUP_PUB.pmtTable.attribute3(i),
attribute4=IBY_PAYGROUP_PUB.pmtTable.attribute4(i),
attribute5=IBY_PAYGROUP_PUB.pmtTable.attribute5(i),
attribute6=IBY_PAYGROUP_PUB.pmtTable.attribute6(i),
attribute7=IBY_PAYGROUP_PUB.pmtTable.attribute7(i),
attribute8=IBY_PAYGROUP_PUB.pmtTable.attribute8(i),
attribute9=IBY_PAYGROUP_PUB.pmtTable.attribute9(i),
attribute10=IBY_PAYGROUP_PUB.pmtTable.attribute10(i),
attribute11=IBY_PAYGROUP_PUB.pmtTable.attribute11(i),
attribute12=IBY_PAYGROUP_PUB.pmtTable.attribute12(i),
attribute13=IBY_PAYGROUP_PUB.pmtTable.attribute13(i),
attribute14=IBY_PAYGROUP_PUB.pmtTable.attribute14(i),
attribute15=IBY_PAYGROUP_PUB.pmtTable.attribute15(i),
ext_branch_number= IBY_PAYGROUP_PUB.pmtTable.ext_branch_number(i),
ext_bank_number= IBY_PAYGROUP_PUB.pmtTable.ext_bank_number(i),
ext_bank_account_name= IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_name(i),
ext_bank_account_number= IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_number(i),
ext_bank_account_type= IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_type(i),
ext_bank_account_iban_number= IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_iban_number(i),
payee_name= IBY_PAYGROUP_PUB.pmtTable.payee_name(i),
payee_address1= IBY_PAYGROUP_PUB.pmtTable.payee_address1(i),
payee_address2= IBY_PAYGROUP_PUB.pmtTable.payee_address2(i),
payee_address3= IBY_PAYGROUP_PUB.pmtTable.payee_address3(i),
payee_address4= IBY_PAYGROUP_PUB.pmtTable.payee_address4(i),
payee_city= IBY_PAYGROUP_PUB.pmtTable.payee_city(i),
payee_postal_code= IBY_PAYGROUP_PUB.pmtTable.payee_postal_code(i),
payee_state= IBY_PAYGROUP_PUB.pmtTable.payee_state(i),
payee_province= IBY_PAYGROUP_PUB.pmtTable.payee_province(i),
payee_county= IBY_PAYGROUP_PUB.pmtTable.payee_county(i),
payee_country= IBY_PAYGROUP_PUB.pmtTable.payee_country(i),
remit_advice_delivery_method= IBY_PAYGROUP_PUB.pmtTable.remit_advice_delivery_method(i),
remit_advice_email= IBY_PAYGROUP_PUB.pmtTable.remit_advice_email(i),
remit_advice_fax= IBY_PAYGROUP_PUB.pmtTable.remit_advice_fax(i),
address_source= IBY_PAYGROUP_PUB.pmtTable.address_source(i),
employee_address_code= IBY_PAYGROUP_PUB.pmtTable.employee_address_code(i),
employee_person_id= IBY_PAYGROUP_PUB.pmtTable.employee_person_id(i),
employee_address_id= IBY_PAYGROUP_PUB.pmtTable.employee_address_id(i),
employee_payment_flag= IBY_PAYGROUP_PUB.pmtTable.employee_payment_flag(i),
ext_inv_payee_id = IBY_PAYGROUP_PUB.pmtTable.ext_inv_payee_id(i),
inv_payee_party_id = IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_id(i),
inv_party_site_id = IBY_PAYGROUP_PUB.pmtTable.inv_party_site_id(i),
inv_supplier_site_id = IBY_PAYGROUP_PUB.pmtTable.inv_supplier_site_id(i),
inv_beneficiary_party = IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_party(i),
inv_payee_name = IBY_PAYGROUP_PUB.pmtTable.inv_payee_name(i),
inv_payee_address1 = IBY_PAYGROUP_PUB.pmtTable.inv_payee_address1(i),
inv_payee_address2 = IBY_PAYGROUP_PUB.pmtTable.inv_payee_address2(i),
inv_payee_address3 = IBY_PAYGROUP_PUB.pmtTable.inv_payee_address3(i),
inv_payee_address4 = IBY_PAYGROUP_PUB.pmtTable.inv_payee_address4(i),
inv_payee_city = IBY_PAYGROUP_PUB.pmtTable.inv_payee_city(i),
inv_payee_postal_code = IBY_PAYGROUP_PUB.pmtTable.inv_payee_postal_code(i),
inv_payee_state = IBY_PAYGROUP_PUB.pmtTable.inv_payee_state(i),
inv_payee_province = IBY_PAYGROUP_PUB.pmtTable.inv_payee_province(i),
inv_payee_county = IBY_PAYGROUP_PUB.pmtTable.inv_payee_county(i),
inv_payee_country = IBY_PAYGROUP_PUB.pmtTable.inv_payee_country(i),
inv_payee_party_name = IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_name(i),
inv_payee_le_reg_num = IBY_PAYGROUP_PUB.pmtTable.inv_payee_le_reg_num(i),
inv_payee_tax_reg_num = IBY_PAYGROUP_PUB.pmtTable.inv_payee_tax_reg_num(i),
inv_payee_address_concat = IBY_PAYGROUP_PUB.pmtTable.inv_payee_address_concat(i),
inv_beneficiary_name = IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_name(i),
inv_payee_party_number = IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_number(i),
inv_payee_alternate_name = IBY_PAYGROUP_PUB.pmtTable.inv_payee_alternate_name(i),
inv_payee_site_alt_name = IBY_PAYGROUP_PUB.pmtTable.inv_payee_site_alt_name(i),
inv_payee_supplier_number = IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_number(i),
inv_payee_first_party_ref = IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(i),
ext_bnk_acct_ownr_inv_prty_id =IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_id(i),
ext_bnk_branch_inv_prty_id =IBY_PAYGROUP_PUB.pmtTable.ext_bnk_branch_inv_prty_id(i),
ext_bnk_acct_ownr_inv_prty_nme =IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_nme(i),
inv_payee_party_attr_cat =IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_attr_cat(i),
inv_payee_supplier_attr_cat =IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_attr_cat(i),
inv_payee_spplr_site_attr_cat =IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_attr_cat(i),
inv_payee_supplier_site_name =IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_site_name(i),
inv_payee_spplr_site_alt_name =IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_alt_name(i),
inv_payee_supplier_id =IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_id(i),
payer_party_number =IBY_PAYGROUP_PUB.pmtTable.payer_party_number(i),
payer_party_site_name =IBY_PAYGROUP_PUB.pmtTable.payer_party_site_name(i),
payer_legal_entity_name =IBY_PAYGROUP_PUB.pmtTable.payer_legal_entity_name(i),
payer_tax_registration_num =IBY_PAYGROUP_PUB.pmtTable.payer_tax_registration_num(i),
payer_le_registration_num =IBY_PAYGROUP_PUB.pmtTable.payer_le_registration_num(i),
payer_party_id =IBY_PAYGROUP_PUB.pmtTable.payer_party_id(i),
payer_location_id =IBY_PAYGROUP_PUB.pmtTable.payer_location_id(i),
payer_party_attr_category =IBY_PAYGROUP_PUB.pmtTable.payer_party_attr_category(i),
payer_le_attr_category =IBY_PAYGROUP_PUB.pmtTable.payer_le_attr_category(i),
payer_abbreviated_agency_code =IBY_PAYGROUP_PUB.pmtTable.payer_abbreviated_agency_code(i),
payer_federal_us_employer_id =IBY_PAYGROUP_PUB.pmtTable.payer_federal_us_employer_id(i),
int_bank_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_name(i),
int_bank_number =IBY_PAYGROUP_PUB.pmtTable.int_bank_number(i),
int_bank_branch_number =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_number(i),
int_bank_branch_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_name(i),
int_eft_swift_code =IBY_PAYGROUP_PUB.pmtTable.int_eft_swift_code(i),
int_bank_account_number =IBY_PAYGROUP_PUB.pmtTable.int_bank_account_number(i),
int_bank_account_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_account_name(i),
int_bank_account_iban =IBY_PAYGROUP_PUB.pmtTable.int_bank_account_iban(i),
int_bank_acct_agency_loc_code =IBY_PAYGROUP_PUB.pmtTable.int_bank_acct_agency_loc_code(i),
int_bank_branch_party_id =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_party_id(i),
int_bank_alt_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_alt_name(i),
int_bank_branch_alt_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_alt_name(i),
int_bank_account_alt_name =IBY_PAYGROUP_PUB.pmtTable.int_bank_account_alt_name(i),
int_bank_account_num_elec =IBY_PAYGROUP_PUB.pmtTable.int_bank_account_num_elec(i),
int_bank_branch_location_id =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_location_id(i),
int_bank_branch_eft_user_num =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_eft_user_num(i),
payee_party_number =IBY_PAYGROUP_PUB.pmtTable.payee_party_number(i),
payee_party_name =IBY_PAYGROUP_PUB.pmtTable.payee_party_name(i),
payee_alternate_name =IBY_PAYGROUP_PUB.pmtTable.payee_alt_name(i),
payee_address_concat =IBY_PAYGROUP_PUB.pmtTable.payee_address_concat(i),
beneficiary_name =IBY_PAYGROUP_PUB.pmtTable.beneficiary_name(i),
payee_party_attr_category =IBY_PAYGROUP_PUB.pmtTable.payee_party_atr_cat(i),
payee_spplr_site_attr_category =IBY_PAYGROUP_PUB.pmtTable.payee_spplr_site_attr_category(i),
payee_supplier_site_name =IBY_PAYGROUP_PUB.pmtTable.payee_supplier_site_name(i),
payee_party_site_name =IBY_PAYGROUP_PUB.pmtTable.payee_party_site_name(i),
payee_addressee =IBY_PAYGROUP_PUB.pmtTable.payee_addressee(i),
payee_site_alternate_name =IBY_PAYGROUP_PUB.pmtTable.payee_site_alternate_name(i),
payee_supplier_number =IBY_PAYGROUP_PUB.pmtTable.payee_supplier_number(i),
payee_first_party_reference =IBY_PAYGROUP_PUB.pmtTable.payee_first_party_reference(i),
payee_supplier_attr_category =IBY_PAYGROUP_PUB.pmtTable.payee_supplier_attr_category(i),
payee_supplier_id =IBY_PAYGROUP_PUB.pmtTable.payee_supplier_id(i),
payee_tax_registration_num =IBY_PAYGROUP_PUB.pmtTable.payee_tax_registration_num(i),
payee_le_registration_num =IBY_PAYGROUP_PUB.pmtTable.payee_le_registration_num(i),
ext_bank_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_name(i),
ext_bank_branch_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_name(i),
ext_eft_swift_code =IBY_PAYGROUP_PUB.pmtTable.ext_eft_swift_code(i),
ext_bank_acct_pmt_factor_flag =IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_pmt_factor_flag(i),
ext_bank_acct_owner_party_id =IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_id(i),
ext_bank_branch_party_id =IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_party_id(i),
ext_bank_alt_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_alt_name(i),
ext_bank_branch_alt_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_alt_name(i),
ext_bank_account_alt_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_alt_name(i),
ext_bank_account_num_elec =IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_num_elec(i),
ext_bank_branch_location_id =IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_location_id(i),
ext_bank_acct_owner_party_name =IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_name(i),
--remit_advice_delivery_method =IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(i),
--remit_advice_email =IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(i),
--remit_advice_fax =IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(i),
delivery_channel_format_value =IBY_PAYGROUP_PUB.pmtTable.delivery_channel_format_value(i),
declaration_exch_rate_type =IBY_PAYGROUP_PUB.pmtTable.declaration_exch_rate_type(i),
declaration_format =IBY_PAYGROUP_PUB.pmtTable.declaration_format(i),
payment_profile_acct_name =IBY_PAYGROUP_PUB.pmtTable.payment_profile_acct_name(i),
payment_profile_sys_name =IBY_PAYGROUP_PUB.pmtTable.payment_profile_sys_name(i),
payment_reason_format_value =IBY_PAYGROUP_PUB.pmtTable.payment_reason_format_value(i),
bank_instruction1_format_value =IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_format_value(i),
bank_instruction2_format_value =IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_format_value(i),
org_name =IBY_PAYGROUP_PUB.pmtTable.org_name(i),
int_bank_branch_rfc_identifier =IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_rfc_identifier(i),
payment_process_request_name =IBY_PAYGROUP_PUB.pmtTable.payment_process_request_name(i),
source_product=IBY_PAYGROUP_PUB.pmtTable.source_product(i)
where payment_id = IBY_PAYGROUP_PUB.pmtTable.payment_id(i);
END updatePayments;
SELECT IBY_PAYMENTS_ALL_S.nextval INTO x_paymentID
FROM DUAL;
| updateDocsWithPaymentID
|
| PURPOSE:
| Updates individual documents in IBY_DOCS_PAYABLE_ALL table
| with payment ids from given PLSQL table.
|
| PARAMETERS:
| IN
| p_docsInPmtTab -
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updateDocsWithPaymentID(
p_docsInPmtTab IN docsInPaymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.updateDocsWithPaymentID';
|| 'payments/documents provided to update '
|| 'IBY_DOCS_PAYABLE_ALL table. Possible data '
|| 'corruption issue.');
* Update the documents. We cannot use bulk update here
* because the bulk update syntax does not allow us to
* reference individual fields of the PL/SQL record.
*
* By default, set the formatting payment it for a
* document same as it's payment id; the payment
* instruction creation program will update the
* formatting payment id for overflow documents.
*
* TBD: Is there any way to optimize this update?
*/
FOR i in p_docsInPmtTab.FIRST..p_docsInPmtTab.LAST LOOP
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Payment: '
|| p_docsInPmtTab(i).payment_id || ', document: '
|| p_docsInPmtTab(i).document_id);
UPDATE
IBY_DOCS_PAYABLE_ALL
SET
payment_id = p_docsInPmtTab(i).payment_id,
formatting_payment_id = p_docsInPmtTab(i).payment_id,
document_status = p_docsInPmtTab(i).document_status,
/*
* Document payment amount might have gotten
* changed during credit memo handling. So we
* need to update document amount from PLSQL
* table.
*/
payment_amount = p_docsInPmtTab(i).document_amount,
payment_curr_discount_taken = p_docsInPmtTab(i).pmt_curr_discount,
/*
* Bank charge amount and amount withheld might have
* been supplied by external hook.
*/
amount_withheld = p_docsInPmtTab(i).amount_withheld,
/*
* 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(
p_docsInPmtTab(i).document_status,
DOC_STATUS_CA_FAILED, 'N',
DOC_STATUS_RELN_FAIL, 'N',
DOC_STATUS_FAIL_BY_REJLVL, 'N',
DOC_STATUS_PAY_VAL_FAIL, 'N',
'Y'
)
WHERE
document_payable_id = p_docsInPmtTab(i).document_id
;
END updateDocsWithPaymentID;
| updateDocsWithPaymentID
|
| PURPOSE:
| Updates individual documents in IBY_DOCS_PAYABLE_ALL table
| with payment ids from given PLSQL table.
|
| PARAMETERS:
| IN
| p_docsInPmtTab -
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updateDocsWithPaymentID
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.updateDocsWithPaymentID';
* Update the documents. We cannot use bulk update here
* because the bulk update syntax does not allow us to
* reference individual fields of the PL/SQL record.
*
* By default, set the formatting payment it for a
* document same as it's payment id; the payment
* instruction creation program will update the
* formatting payment id for overflow documents.
*
* TBD: Is there any way to optimize this update?
*/
MERGE
INTO iby_docs_payable_all idpa
/* Oracle 10gR2 doesn't require WHEN NOT MATCHED */
USING iby_docs_in_pmt_gt idpt ON(idpt.document_payable_id = idpa.document_payable_id)
WHEN MATCHED THEN UPDATE
SET idpa.payment_id = idpt.payment_id,
idpa.formatting_payment_id = idpt.payment_id,
idpa.document_status = idpt.document_status,
idpa.payment_amount = idpt.document_amount,
idpa.payment_curr_discount_taken = idpt.payment_curr_discount_taken,
idpa.amount_withheld = idpt.amount_withheld,
idpa.straight_through_flag = DECODE(
idpt.document_status,
DOC_STATUS_CA_FAILED, 'N',
DOC_STATUS_RELN_FAIL, 'N',
DOC_STATUS_FAIL_BY_REJLVL, 'N',
DOC_STATUS_PAY_VAL_FAIL, 'N',
'Y'
);
END updateDocsWithPaymentID;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT
CURRENCY_CODE,
min_check_amount,
max_check_amount
INTO
l_ce_currency_code,
l_ce_min_pmt_ctrl,
l_ce_max_pmt_ctrl
FROM
CE_BANK_ACCOUNTS
WHERE
bank_account_id = IBY_PAYGROUP_PUB.pmtTable.internal_bank_account_id(p_trx_pmt_line_index)
;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* insert this record into
* the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
x_paymentTab(k).payment_id,
x_paymentTab(k).payment_status,
NULL,
x_paymentTab(k).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab
);
insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab,
x_errTokenTab);
* insert this record into
* the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
NULL,
x_paymentTab(i).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab
);
insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab,
x_errTokenTab);
| Inserts all successfuly validated payments into
| IBY_HOOK_PAYMENTS_TEMP. This is a temporary table that the
| calling app can access to see what payments have been
| created. The calling app can also update the payments / fail
| the payments in the temporary table, and the payment creation
| program will update it's payments accordingly.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performPreHookProcess(
p_cap_payreq_cd IN VARCHAR2,
p_cap_id IN NUMBER,
x_paymentTab IN OUT NOCOPY paymentTabType,
x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType,
x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.performPreHookProcess';
l_last_updated_bys t_last_updated_by;
l_last_update_dates t_last_update_date;
l_last_update_logins t_last_update_login;
IBY_HOOK_DOCS_IN_PMT_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
IBY_HOOK_DOCS_IN_PMT_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
IBY_HOOK_DOCS_IN_PMT_T.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
l_last_updated_by t_last_updt_by;
l_last_update_date t_last_updt_date;
l_last_update_login t_last_updt_login;
delete_paymentTab;
paymentTab.last_updated_by(l_succPayIndx) :=
x_paymentTab(i).last_updated_by;
paymentTab.last_update_date(l_succPayIndx) :=
x_paymentTab(i).last_update_date;
paymentTab.last_update_login(l_succPayIndx) :=
x_paymentTab(i).last_update_login;
* First delete any existing records in these
* temp tables so that they do not interfere with
* our processing.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Deleting existing records in '
|| 'hook tables .. ');
DELETE IBY_HOOK_DOCS_IN_PMT_T;
DELETE IBY_HOOK_PAYMENTS_T;
* These arrays will be used in the bulk insert.
*/
IF (x_hookDocsInPmtTab.COUNT > 0) THEN
FOR i in x_hookDocsInPmtTab.FIRST..x_hookDocsInPmtTab.LAST LOOP
l_payment_id(i)
:= x_hookDocsInPmtTab(i).payment_id;
l_last_updated_by(i)
:= NVL(x_hookDocsInPmtTab(i).last_updated_by, fnd_global.user_id);
l_last_update_date(i)
:= NVL(x_hookDocsInPmtTab(i).last_update_date, sysdate);
l_last_update_login(i)
:= NVL(x_hookDocsInPmtTab(i).last_update_login,
fnd_global.user_id);
/* insert documents */
/*
* Bulk insert records into IBY_HOOK_DOCS_IN_PMT_T using named
* columns. This will avoid any dependency in column order.
*/
IF (x_hookDocsInPmtTab.COUNT > 0) THEN
FORALL i in x_hookDocsInPmtTab.FIRST..x_hookDocsInPmtTab.LAST
INSERT INTO IBY_HOOK_DOCS_IN_PMT_T
(
payment_id,
document_payable_id,
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,
document_amount,
document_currency_code,
amount_withheld,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
pay_proc_trxn_type_code,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
payment_curr_discount_taken,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
VALUES
(
l_payment_id(i),
l_document_payable_id(i),
l_calling_app_id(i),
l_calling_app_doc_unique_ref1(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_document_amount(i),
l_document_currency_code(i),
l_amount_withheld(i),
l_dont_pay_flag(i),
l_dont_pay_reason_code(i),
l_dont_pay_description(i),
l_pay_proc_trxn_type_code(i),
l_internal_bank_account_id(i),
l_ext_payee_id(i),
l_payee_party_id(i),
l_party_site_id(i),
l_supplier_site_id(i),
l_org_id(i),
l_org_type(i),
l_external_bank_account_id(i),
l_payment_curr_discount_taken(i),
l_created_by(i),
l_creation_date(i),
l_last_updated_by(i),
l_last_update_date(i),
l_last_update_login(i),
l_object_version_number(i)
)
;
* Bulk insert records into IBY_HOOK_PAYMENTS_T using named
* columns. This will avoid any dependency in column order.
*/
-- FOR i in paymentTab.calling_app_id.FIRST .. paymentTab.calling_app_id.LAST
FORALL i in paymentTab.calling_app_id.FIRST .. paymentTab.calling_app_id.LAST
INSERT INTO IBY_HOOK_PAYMENTS_T
(
calling_app_id,
call_app_pay_service_req_code,
payment_service_request_id,
payment_id,
payment_amount,
payment_currency_code,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
discount_amount_taken,
payment_date,
bank_charge_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
VALUES
(
paymentTab.calling_app_id(i),
paymentTab.call_app_pay_service_req_cd(i),
paymentTab.payment_service_request_id(i),
paymentTab.payment_id(i),
paymentTab.payment_amount(i),
paymentTab.payment_currency_code(i),
paymentTab.dont_pay_flg(i),
paymentTab.dont_pay_reason_cd(i),
paymentTab.dont_pay_desc(i),
paymentTab.internal_bank_account_id(i),
paymentTab.ext_payee_id(i),
paymentTab.payee_party_id(i),
paymentTab.party_site_id(i),
paymentTab.supplier_site_id(i),
paymentTab.org_id(i),
paymentTab.org_type(i),
paymentTab.external_bank_account_id(i),
paymentTab.discount_amount_taken(i),
paymentTab.payment_date(i),
paymentTab.bank_charge_amount(i),
paymentTab.created_by(i),
paymentTab.creation_date(i),
paymentTab.last_updated_by(i),
paymentTab.last_update_date(i),
paymentTab.last_update_login(i),
paymentTab.object_version_number(i)
);
|| ' were inserted into global temp tables.'
);
| Inserts all successfuly validated payments into
| IBY_HOOK_PAYMENTS_TEMP. This is a temporary table that the
| calling app can access to see what payments have been
| created. The calling app can also update the payments / fail
| the payments in the temporary table, and the payment creation
| program will update it's payments accordingly.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performPreHookProcess(
p_cap_payreq_cd IN VARCHAR2,
p_ppr_id IN NUMBER,
l_prehook_cnt OUT NOCOPY NUMBER,
p_cap_id IN NUMBER
-- x_paymentTab IN OUT NOCOPY paymentTabType,
-- x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
-- x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.performPreHookProcess';
l_last_updated_bys t_last_updated_by;
l_last_update_dates t_last_update_date;
l_last_update_logins t_last_update_login;
IBY_HOOK_DOCS_IN_PMT_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
IBY_HOOK_DOCS_IN_PMT_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
IBY_HOOK_DOCS_IN_PMT_T.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
l_last_updated_by t_last_updt_by;
l_last_update_date t_last_updt_date;
l_last_update_login t_last_updt_login;
paymentTab.last_updated_by(l_succPayIndx) :=
x_paymentTab(i).last_updated_by;
paymentTab.last_update_date(l_succPayIndx) :=
x_paymentTab(i).last_update_date;
paymentTab.last_update_login(l_succPayIndx) :=
x_paymentTab(i).last_update_login;
* First delete any existing records in these
* temp tables so that they do not interfere with
* our processing.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Deleting existing records in '
|| 'hook tables .. ');
DELETE IBY_HOOK_DOCS_IN_PMT_T;
DELETE IBY_HOOK_PAYMENTS_T;
* Bulk insert records into IBY_HOOK_PAYMENTS_T using named
* columns. This will avoid any dependency in column order.
*/
-- FOR i in paymentTab.calling_app_id.FIRST .. paymentTab.calling_app_id.LAST
-- FORALL i in paymentTab.payment_id.FIRST .. paymentTab.payment_id.LAST
INSERT INTO IBY_HOOK_PAYMENTS_T
(
calling_app_id,
call_app_pay_service_req_code,
payment_service_request_id,
payment_id,
payment_amount,
payment_currency_code,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
discount_amount_taken,
payment_date,
bank_charge_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
)
(
SELECT
p_cap_id,
p_cap_payreq_cd,
payment_service_request_id,
payment_id,
payment_amount,
payment_currency_code,
'N',
null,
null,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
discount_amount_taken,
payment_date,
bank_charge_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
FROM
IBY_PAYMENTS_ALL
where
payment_status <> PAY_STATUS_REJECTED AND
PAYMENT_SERVICE_REQUEST_ID = p_ppr_id
);
INSERT INTO iby_hook_docs_in_pmt_t(
PAYMENT_ID,
DOCUMENT_PAYABLE_ID,
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,
DOCUMENT_AMOUNT,
DOCUMENT_CURRENCY_CODE,
AMOUNT_WITHHELD,
DONT_PAY_FLAG,
DONT_PAY_REASON_CODE,
DONT_PAY_DESCRIPTION,
PAY_PROC_TRXN_TYPE_CODE,
INTERNAL_BANK_ACCOUNT_ID,
EXT_PAYEE_ID,
PAYEE_PARTY_ID,
PARTY_SITE_ID,
SUPPLIER_SITE_ID,
ORG_ID,
ORG_TYPE,
EXTERNAL_BANK_ACCOUNT_ID,
PAYMENT_CURR_DISCOUNT_TAKEN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
SELECT
src.PAYMENT_ID,
src.DOCUMENT_PAYABLE_ID,
src.CALLING_APP_ID,
src.CALLING_APP_DOC_UNIQUE_REF1,
src.CALLING_APP_DOC_UNIQUE_REF2,
src.CALLING_APP_DOC_UNIQUE_REF3,
src.CALLING_APP_DOC_UNIQUE_REF4,
src.CALLING_APP_DOC_UNIQUE_REF5,
src.DOCUMENT_AMOUNT,
src.DOCUMENT_CURRENCY_CODE,
src.AMOUNT_WITHHELD,
src.DONT_PAY_FLAG,
src.DONT_PAY_REASON_CODE,
src.DONT_PAY_DESCRIPTION,
src.PAY_PROC_TRXN_TYPE_CODE,
src.INTERNAL_BANK_ACCOUNT_ID,
src.EXT_PAYEE_ID,
src.PAYEE_PARTY_ID,
src.PARTY_SITE_ID,
src.SUPPLIER_SITE_ID,
src.ORG_ID,
src.ORG_TYPE,
src.EXTERNAL_BANK_ACCOUNT_ID,
src.PAYMENT_CURR_DISCOUNT_TAKEN,
src.CREATED_BY,
src.CREATION_DATE,
src.LAST_UPDATED_BY,
src.LAST_UPDATE_DATE,
src.LAST_UPDATE_LOGIN,
src.OBJECT_VERSION_NUMBER
FROM iby_docs_in_pmt_gt src, iby_hook_payments_t ihpt
WHERE src.payment_id = ihpt.payment_id
AND src.document_status = DOC_STATUS_PAY_CREATED;
l_preHookPaymentTab(l_preHook_cntr).last_updated_by
:= PaymentTab.last_updated_by(i);
l_preHookPaymentTab(l_preHook_cntr).last_update_date
:= PaymentTab.last_update_date(i);
l_preHookPaymentTab(l_preHook_cntr).last_update_login
:= PaymentTab.last_update_login(i);
* tables. The calling app might have updated them.
*/
getAdjustedPaymentData(x_hookPaymentTab, x_hookDocsInPmtTab);
* Update our existing payments and documents data structures
* with the values from the hook - the external app may have adjusted
* some payment amounts for bank charges, tax withholding etc.
*
* These must be reflected into our existing data structures before
* we update the documents and payments tables with them.
*/
/* update payments */
FOR i in x_hookPaymentTab.FIRST .. x_hookPaymentTab.LAST LOOP
FOR j in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
IF (x_hookPaymentTab(i).payment_id = x_paymentTab(j).payment_id)
THEN
/*
* Copy from 'hook payments' array to original
* payments array.
*/
IF (x_paymentTab(j).payment_amount <>
x_hookPaymentTab(i).payment_amount) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Amount for payment '
|| x_hookPaymentTab(i).payment_id
|| ' was changed by calling app'
|| ' from ' || x_paymentTab(j).payment_amount
|| ' to ' || x_hookPaymentTab(i).payment_amount
);
* amount via the hook, update the bank charge
* amount attribute on the payment to reflect this.
*/
IF (x_paymentTab(j).bank_charge_amount <>
x_hookPaymentTab(i).bank_charge_amount) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Bank charge amount '
|| 'for payment '
|| x_hookPaymentTab(i).payment_id
|| ' was changed by calling app'
|| ' from ' || x_paymentTab(j).bank_charge_amount
|| ' to ' || x_hookPaymentTab(i).bank_charge_amount
);
* amount via the hook, update the discount
* amount attribute on the payment to reflect this.
*/
IF (x_paymentTab(j).discount_amount_taken <>
x_hookPaymentTab(i).discount_amount_taken) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Discount Amount Taken '
|| 'for payment '
|| x_hookPaymentTab(i).payment_id
|| ' was changed by calling app'
|| ' from ' || x_paymentTab(j).discount_amount_taken
|| ' to ' || x_hookPaymentTab(i).discount_amount_taken
);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
x_paymentTab(j).payment_id,
x_paymentTab(j).payment_status,
NULL,
x_paymentTab(j).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
/* update documents */
IF (x_hookDocsInPmtTab.COUNT > 0) THEN
FOR i in x_hookDocsInPmtTab.FIRST .. x_hookDocsInPmtTab.LAST LOOP
FOR j in x_docsInPmtTab.FIRST .. x_docsInPmtTab.LAST LOOP
IF (x_hookDocsInPmtTab(i).document_payable_id =
x_docsInPmtTab(j).document_id)
THEN
/*
* Copy from 'hook documents' array to original
* documents array.
*/
IF (x_docsInPmtTab(j).document_amount <>
x_hookDocsInPmtTab(i).document_amount) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'Amount for document '
|| x_hookDocsInPmtTab(i).document_payable_id
|| ' was changed by calling app'
|| ' from ' || x_docsInPmtTab(j).document_amount
|| ' to ' || x_hookDocsInPmtTab(i).document_amount
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT idpt.payment_id,
idpt.document_payable_id,
idpt.document_status,
idpt.calling_app_id,
idpt.calling_app_doc_unique_ref1,
idpt.calling_app_doc_unique_ref2,
idpt.calling_app_doc_unique_ref3,
idpt.calling_app_doc_unique_ref4,
idpt.calling_app_doc_unique_ref5,
idpt.pay_proc_trxn_type_code,
idpt.document_amount,
idpt.payment_grouping_number
FROM iby_docs_in_pmt_gt idpt
WHERE idpt.document_payable_id in (SELECT idpa.document_payable_id from iby_hook_docs_in_pmt_t idpa
WHERE idpa.dont_pay_flag = 'Y');
SELECT ihdp.document_payable_id,
ihdp.document_amount,
ihdp.amount_withheld,
ihdp.dont_pay_flag
FROM iby_hook_docs_in_pmt_t ihdp, iby_docs_in_pmt_gt idip
WHERE ihdp.document_payable_id = idip.document_payable_id
AND (ihdp.document_amount <> idip.document_amount
OR ihdp.amount_withheld <> idip.amount_withheld
OR ihdp.dont_pay_flag <> idip.dont_pay_flag);
select
payment_id,
payment_amount
from
iby_payments_all A
where payment_amount =
(select B.payment_amount from IBY_HOOK_PAYMENTS_T B where A.payment_id = B.payment_id
AND A.payment_service_request_id = B.payment_service_request_id)
;
SELECT
calling_app_id ,
call_app_pay_service_req_code ,
payment_service_request_id ,
payment_id ,
payment_amount ,
payment_currency_code ,
dont_pay_flag ,
dont_pay_reason_code ,
dont_pay_description ,
internal_bank_account_id ,
ext_payee_id ,
payee_party_id ,
party_site_id ,
supplier_site_id ,
org_id ,
org_type ,
external_bank_account_id ,
discount_amount_taken ,
payment_date ,
bank_charge_amount ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
object_version_number
FROM
IBY_HOOK_PAYMENTS_T
WHERE
call_app_pay_service_req_code=
p_cap_payreq_cd
;
l_preHookPaymentTab(l_preHook_cntr).last_updated_by
:= PaymentTab.last_updated_by(i);
l_preHookPaymentTab(l_preHook_cntr).last_update_date
:= PaymentTab.last_update_date(i);
l_preHookPaymentTab(l_preHook_cntr).last_update_login
:= PaymentTab.last_update_login(i);
UPDATE iby_hook_payments_t
SET payment_amount = 0
WHERE dont_pay_flag = 'Y';
* tables. The calling app might have updated them.
*/
-- getAdjustedPaymentData(x_hookPaymentTab);
SELECT COUNT(PAYMENT_ID)
INTO l_posthook_count
FROM IBY_HOOK_PAYMENTS_T
WHERE
payment_service_request_id =
(select payment_service_request_id
from iby_pay_service_requests where
call_app_pay_service_req_code =p_cap_payreq_cd
);
SELECT COUNT(*)
INTO l_docsingt_count
FROM iby_docs_in_pmt_gt
WHERE document_status = DOC_STATUS_PAY_CREATED;
UPDATE iby_hook_docs_in_pmt_t
SET dont_pay_flag = 'Y'
WHERE payment_id IN (select payment_id from iby_hook_payments_t where dont_pay_flag = 'Y');
UPDATE IBY_PAYMENTS_ALL A
SET (PAYMENT_AMOUNT, DISCOUNT_AMOUNT_TAKEN, bank_charge_amount)
=
(SELECT NVL(B.PAYMENT_AMOUNT,PAYMENT_AMOUNT),B.DISCOUNT_AMOUNT_TAKEN,B.bank_charge_amount
from IBY_HOOK_PAYMENTS_T B
where A.PAYMENT_ID= B.PAYMENT_ID)
;*/
UPDATE IBY_PAYMENTS_ALL A
SET PAYMENT_AMOUNT = (SELECT B.PAYMENT_AMOUNT
from IBY_HOOK_PAYMENTS_T B
where A.PAYMENT_ID= B.PAYMENT_ID);
UPDATE IBY_PAYMENTS_ALL A
SET
DISCOUNT_AMOUNT_TAKEN= (SELECT B.DISCOUNT_AMOUNT_TAKEN
from IBY_HOOK_PAYMENTS_T B
where A.PAYMENT_ID= B.PAYMENT_ID),
bank_charge_amount = (SELECT B.bank_charge_amount
from IBY_HOOK_PAYMENTS_T B
where A.PAYMENT_ID= B.PAYMENT_ID)
; */
WHEN MATCHED THEN UPDATE
SET ibpa.PAYMENT_AMOUNT = ihp.PAYMENT_AMOUNT,
ibpa.DISCOUNT_AMOUNT_TAKEN = ihp.DISCOUNT_AMOUNT_TAKEN,
ibpa.bank_charge_amount = ihp.bank_charge_amount;
UPDATE IBY_PAYMENTS_ALL A
SET PAYMENT_STATUS =
(SELECT PAY_STATUS_CA_FAILED FROM
IBY_HOOK_PAYMENTS_T B
WHERE A.PAYMENT_ID= B.PAYMENT_ID AND
B.DONT_PAY_FLAG ='Y')
;
WHEN MATCHED THEN UPDATE
SET ibpa.PAYMENT_STATUS = PAY_STATUS_CA_FAILED
;
select payment_amount
into l_hook_pmt_amt
from iby_hook_payments_t
where payment_id = hookPmtsTab(j).payment_id;
select payment_amount
into l_pmt_amt
from iby_payments_all
where payment_id = hookPmtsTab(j).payment_id;
|| ', Updated payment amount: '
|| IBY_PAYGROUP_PUB.pmtTable.payment_amount(l_trx_cbr_index));
UPDATE IBY_PAYMENTS_ALL
SET PAYMENT_STATUS = PAY_STATUS_CA_FAILED
WHERE PAYMENT_ID = hookPmtsTab(j).payment_id;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
hookPmtsTab(j).payment_id,
PAY_STATUS_CA_FAILED,
NULL,
hookPmtsTab(j).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
WHEN MATCHED THEN UPDATE
SET idpa.document_amount = idpt.document_amount,
idpa.amount_withheld = idpt.amount_withheld,
idpa.payment_curr_discount_taken = idpt.payment_curr_discount_taken;
UPDATE iby_docs_in_pmt_gt idpa
SET idpa.document_status = DOC_STATUS_CA_FAILED
WHERE idpa.document_payable_id IN (SELECT document_payable_id from iby_hook_docs_in_pmt_t idpt
WHERE idpt.dont_pay_flag = 'Y');
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT idpt.payment_id,
idpt.document_payable_id,
idpt.document_status,
idpt.calling_app_id,
idpt.calling_app_doc_unique_ref1,
idpt.calling_app_doc_unique_ref2,
idpt.calling_app_doc_unique_ref3,
idpt.calling_app_doc_unique_ref4,
idpt.calling_app_doc_unique_ref5,
idpt.pay_proc_trxn_type_code,
idpt.document_amount,
idpt.payment_grouping_number
FROM iby_docs_in_pmt_gt idpt
WHERE idpt.payment_grouping_number IS NOT NULL
AND idpt.document_status <> DOC_STATUS_RELN_FAIL
AND EXISTS (SELECT 1 FROM iby_docs_in_pmt_gt idpit WHERE idpit.payment_grouping_number IS NOT NULL
AND idpit.payment_grouping_number = idpt.payment_grouping_number
AND idpit.document_status <> DOC_STATUS_PAY_CREATED
AND idpit.document_status <> DOC_STATUS_RELN_FAIL)
ORDER BY idpt.payment_grouping_number, idpt.payment_id;
UPDATE iby_docs_in_pmt_gt idipt
SET idipt.document_status = DOC_STATUS_RELN_FAIL
WHERE idipt.payment_grouping_number = l_fail_dop_tbl(j).payment_grouping_number
AND idipt.document_status = DOC_STATUS_PAY_CREATED;
print_debuginfo(l_module_name, 'Updated docs '
|| 'failure: '
|| '. Related by '
|| l_fail_dop_tbl(j).payment_grouping_number
|| '.'
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT idpt.payment_id,
idpt.document_payable_id,
idpt.document_status,
idpt.calling_app_id,
idpt.calling_app_doc_unique_ref1,
idpt.calling_app_doc_unique_ref2,
idpt.calling_app_doc_unique_ref3,
idpt.calling_app_doc_unique_ref4,
idpt.calling_app_doc_unique_ref5,
idpt.pay_proc_trxn_type_code,
idpt.document_amount,
idpt.payment_grouping_number
FROM iby_docs_in_pmt_gt idpt
WHERE idpt.payment_grouping_number IS NOT NULL
AND idpt.document_status <> DOC_STATUS_RELN_FAIL
AND EXISTS (SELECT 1 FROM iby_docs_in_pmt_gt idpit WHERE idpit.payment_grouping_number IS NOT NULL
AND idpit.payment_grouping_number = idpt.payment_grouping_number
AND idpit.document_status <> DOC_STATUS_PAY_CREATED
AND idpit.document_status <> DOC_STATUS_RELN_FAIL)
ORDER BY idpt.payment_grouping_number, idpt.payment_id;
UPDATE iby_docs_in_pmt_gt idipt
SET idipt.document_status = DOC_STATUS_RELN_FAIL
WHERE idipt.payment_grouping_number = l_fail_dop_tbl(j).payment_grouping_number
AND idipt.document_status = DOC_STATUS_PAY_CREATED;
print_debuginfo(l_module_name, 'Updated docs '
|| 'failure: '
|| '. Related by '
|| l_fail_dop_tbl(j).payment_grouping_number
|| '.'
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
UPDATE IBY_PAYMENTS_ALL
SET PAYMENT_AMOUNT = PAYMENT_AMOUNT -
sdap_tbl(IBY_PAYGROUP_PUB.pmtTable.payment_id(k))
WHERE
PAYMENT_ID = IBY_PAYGROUP_PUB.pmtTable.payment_id(k);
l_sql_string := 'SELECT ' || p_pmtDetailsFormula || ' FROM '
|| 'IBY_DOCS_PAYABLE_ALL WHERE '
|| 'document_payable_id = :bind_var';
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_payreq_id
;
| Reads the data updated in the global temp tables by the calling app.
| The calling app would have updated the data when the external
| application hook was called. This method reads in the updated
| data.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE getAdjustedPaymentData(
x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType,
x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.getAdjustedPaymentData';
TYPE t_last_updated_by IS TABLE OF
IBY_HOOK_PAYMENTS_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_date IS TABLE OF
IBY_HOOK_PAYMENTS_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_login IS TABLE OF
IBY_HOOK_PAYMENTS_T.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;
SELECT
calling_app_id,
call_app_pay_service_req_code,
payment_service_request_id,
payment_id,
payment_amount,
payment_currency_code,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
discount_amount_taken,
payment_date,
bank_charge_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
FROM IBY_HOOK_PAYMENTS_T;
TYPE ty_last_updated_by IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE ty_last_update_date IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE ty_last_update_login IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
ll_last_updated_by ty_last_updated_by;
ll_last_update_date ty_last_update_date;
ll_last_update_login ty_last_update_login;
SELECT
payment_id,
document_payable_id,
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,
document_amount,
document_currency_code,
amount_withheld,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
pay_proc_trxn_type_code,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
payment_curr_discount_taken,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
FROM IBY_HOOK_DOCS_IN_PMT_T
ORDER BY document_payable_id;
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_object_version_number
;
x_hookPaymentTab(i).last_updated_by :=
l_last_updated_by(i);
x_hookPaymentTab(i).last_update_date :=
l_last_update_date(i);
x_hookPaymentTab(i).last_update_login :=
l_last_update_login(i);
ll_last_updated_by,
ll_last_update_date,
ll_last_update_login,
ll_object_version_number
;
x_hookDocsInPmtTab(i).last_updated_by :=
ll_last_updated_by(i);
x_hookDocsInPmtTab(i).last_update_date :=
ll_last_update_date(i);
x_hookDocsInPmtTab(i).last_update_login :=
ll_last_update_login(i);
| Reads the data updated in the global temp tables by the calling app.
| The calling app would have updated the data when the external
| application hook was called. This method reads in the updated
| data.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE getAdjustedPaymentData(
x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType
-- x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.getAdjustedPaymentData';
TYPE t_last_updated_by IS TABLE OF
IBY_HOOK_PAYMENTS_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_date IS TABLE OF
IBY_HOOK_PAYMENTS_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_login IS TABLE OF
IBY_HOOK_PAYMENTS_T.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;
SELECT
calling_app_id,
call_app_pay_service_req_code,
payment_service_request_id,
payment_id,
payment_amount,
payment_currency_code,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
discount_amount_taken,
payment_date,
bank_charge_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
FROM IBY_HOOK_PAYMENTS_T;
TYPE ty_last_updated_by IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE ty_last_update_date IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE ty_last_update_login IS TABLE OF
IBY_HOOK_DOCS_IN_PMT_T.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
ll_last_updated_by ty_last_updated_by;
ll_last_update_date ty_last_update_date;
ll_last_update_login ty_last_update_login;
SELECT
payment_id,
document_payable_id,
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,
document_amount,
document_currency_code,
amount_withheld,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description,
pay_proc_trxn_type_code,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
payment_curr_discount_taken,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
FROM IBY_HOOK_DOCS_IN_PMT_T
ORDER BY document_payable_id;*/
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_object_version_number
;
x_hookPaymentTab(i).last_updated_by :=
l_last_updated_by(i);
x_hookPaymentTab(i).last_update_date :=
l_last_update_date(i);
x_hookPaymentTab(i).last_update_login :=
l_last_update_login(i);
ll_last_updated_by,
ll_last_update_date,
ll_last_update_login,
ll_object_version_number
;
x_hookDocsInPmtTab(i).last_updated_by :=
ll_last_updated_by(i);
x_hookDocsInPmtTab(i).last_update_date :=
ll_last_update_date(i);
x_hookDocsInPmtTab(i).last_update_login :=
ll_last_update_login(i);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT idpt.payment_id,
idpt.document_payable_id,
idpt.document_status,
idpt.calling_app_id,
idpt.calling_app_doc_unique_ref1,
idpt.calling_app_doc_unique_ref2,
idpt.calling_app_doc_unique_ref3,
idpt.calling_app_doc_unique_ref4,
idpt.calling_app_doc_unique_ref5,
idpt.pay_proc_trxn_type_code,
idpt.document_amount,
idpt.payment_grouping_number
FROM iby_docs_in_pmt_gt idpt
WHERE idpt.payment_id = p_paymentId;
UPDATE iby_docs_in_pmt_gt
SET document_status = p_docStatus
WHERE document_payable_id = l_fail_dop_tbl(j).document_payable_id;
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT
advice.system_profile_code,
DECODE(advice.document_count_limit, NULL, -1,
advice.document_count_limit),
DECODE(advice.payment_details_length_limit, NULL, -1,
advice.payment_details_length_limit)
FROM
IBY_REMIT_ADVICE_SETUP advice
WHERE
remittance_advice_format_code IS NOT NULL;
SELECT
advice.system_profile_code,
DECODE(advice.document_count_limit, NULL, -1,
advice.document_count_limit),
DECODE(advice.payment_details_length_limit, NULL, -1,
advice.payment_details_length_limit),
pmt.payment_details,
pmt.payment_id,
COUNT(document_payable_id)
FROM
IBY_REMIT_ADVICE_SETUP advice,
IBY_PAYMENT_PROFILES ppp,
IBY_PAYMENTS_ALL pmt,
IBY_DOCS_PAYABLE_ALL docs
WHERE
remittance_advice_format_code IS NOT NULL
AND advice.SYSTEM_PROFILE_CODE = ppp.SYSTEM_PROFILE_CODE
AND ppp.PAYMENT_PROFILE_ID = pmt.PAYMENT_PROFILE_ID
AND docs.payment_id = pmt.payment_id
AND pmt.payment_status = PAY_STATUS_CREATED
AND pmt.payment_service_request_id = ppr_id
GROUP BY
advice.system_profile_code,
advice.document_count_limit,
advice.payment_details_length_limit,
pmt.payment_details,
pmt.payment_id
;
UPDATE IBY_PAYMENTS_ALL
SET separate_remit_advice_req_flag = 'Y'
WHERE
PAYMENT_ID =l_remitAdvicesSetupTab(j).payment_id;
UPDATE IBY_PAYMENTS_ALL
SET separate_remit_advice_req_flag = 'Y'
WHERE
PAYMENT_ID = l_remitAdvicesSetupTab(j).payment_id;
UPDATE IBY_PAYMENTS_ALL
SET separate_remit_advice_req_flag = 'Y'
WHERE
PAYMENT_ID = l_remitAdvicesSetupTab(j).payment_id;
SELECT count(*)
INTO l_successDocCountRec.success_docs_count
FROM iby_docs_in_pmt_gt
WHERE payment_id = x_paymentTab(i).payment_id
AND document_status = DOC_STATUS_PAY_CREATED;
| performDBUpdates
|
| PURPOSE:
| This is the top level method that is called by the
| payment creation program to:
| 1. insert payments to DB
| 2. update documents with payment id
| 3. update status of payment request
|
| This method will read the 'rejection level' system option
| and do updates accordingly.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performDBUpdates(
p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%type,
p_rej_level IN VARCHAR2,
p_review_pmts_flag IN VARCHAR2,
x_paymentTab IN OUT NOCOPY paymentTabType,
x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
x_allPmtsSuccessFlag IN OUT NOCOPY BOOLEAN,
x_allPmtsFailedFlag IN OUT NOCOPY BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2,
x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
trxnErrTokenTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.performDBUpdates';
* Update the status of the payments/documents
* as per the rejection level (if necessary).
*/
IF (p_rej_level = REJ_LVL_REQUEST) THEN
IF (l_allsuccess_flag = FALSE) THEN
/*
* This means that at least one payment in this
* payment request has failed.
*
* For 'request' rejection level:
* If any payment in the request fails validation,
* the entire payment request should be rejected;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
NULL,
x_paymentTab(i).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab,
NULL
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
NULL,
x_paymentTab(i).payment_id,
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab,
NULL
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* Update all the payments that are in PAY_STATUS_REJECTED
* status to PAY_STATUS_FAIL_VALID.
*/
FOR j in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
IF (x_paymentTab(j).payment_status = PAY_STATUS_REJECTED) THEN
x_paymentTab(j).payment_status :=
PAY_STATUS_FAIL_VALID;
/* not needed in here as all updates have happened */
-- updatePayments(x_paymentTab);
* Update the documents table by providing a payment id to
* each document.
*/
updateDocsWithPaymentID(x_docsInPmtTab);
IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_docErrorTab,
x_errTokenTab);
* 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_payreq_id || ' to ' || l_request_status || '.');
UPDATE
iby_pay_service_requests
SET
payment_service_request_status = l_request_status
WHERE
payment_service_request_id = p_payreq_id
;
END performDBUpdates;
| performDBUpdates
|
| PURPOSE:
| This is the top level method that is called by the
| payment creation program to:
| 1. insert payments to DB
| 2. update documents with payment id
| 3. update status of payment request
|
| This method will read the 'rejection level' system option
| and do updates accordingly.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performDBUpdates(
p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id%type,
p_rej_level IN VARCHAR2,
p_review_pmts_flag IN VARCHAR2,
-- x_paymentTab IN OUT NOCOPY paymentTabType,
-- x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
x_allPmtsSuccessFlag IN OUT NOCOPY BOOLEAN,
x_allPmtsFailedFlag IN OUT NOCOPY BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2,
x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
trxnErrTokenTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.performDBUpdates';
SELECT 'N'
INTO l_allsuccess_flag
from DUAL
WHERE EXISTS
(SELECT 1 FROM IBY_PAYMENTS_ALL
WHERE PAYMENT_STATUS NOT IN (PAY_STATUS_CREATED, PAY_STATUS_REMOVED)
AND payment_service_request_id =p_payreq_id
);
select count(payment_id)
into l_pmt_count
from iby_payments_all
where payment_service_request_id = p_payreq_id
AND payment_status <> PAY_STATUS_CREATED
;
SELECT 'N'
INTO l_allfailed_flag
from DUAL
WHERE EXISTS
(SELECT 1 FROM IBY_PAYMENTS_ALL
WHERE PAYMENT_STATUS = PAY_STATUS_CREATED
AND payment_service_request_id =p_payreq_id
);
* Update the status of the payments/documents
* as per the rejection level (if necessary).
*/
IF (p_rej_level = REJ_LVL_REQUEST) THEN
IF (l_allsuccess_flag = 'N') THEN
/*
* This means that at least one payment in this
* payment request has failed.
*
* For 'request' rejection level:
* If any payment in the request fails validation,
* the entire payment request should be rejected;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_status(i),
NULL,
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab,
l_triggering_pmt_id
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_status(i),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
l_payeeAwt.DELETE;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createErrorRecord(
TRXN_TYPE_PMT,
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
IBY_PAYGROUP_PUB.pmtTable.payment_status(i),
NULL,
IBY_PAYGROUP_PUB.pmtTable.payment_id(i),
NULL,
NULL,
NULL,
NULL,
NULL,
l_doc_err_rec,
x_errTokenTab,
l_triggering_pmt_id
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
* 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_payreq_id || ' to ' || l_request_status || '.');
* Update all the payments that are in PAY_STATUS_REJECTED
* status to PAY_STATUS_FAIL_VALID.
*/
FOR j in IBY_PAYGROUP_PUB.pmtTable.payment_id.FIRST .. IBY_PAYGROUP_PUB.pmtTable.payment_id.LAST LOOP
IF (IBY_PAYGROUP_PUB.pmtTable.payment_status(j) = PAY_STATUS_REJECTED) THEN
IBY_PAYGROUP_PUB.pmtTable.payment_status(j) :=
PAY_STATUS_FAIL_VALID;
updatePayments;
* Update the documents table by providing a payment id to
* each document.
*/
updateDocsWithPaymentID;
IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_docErrorTab,
x_errTokenTab);
* 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_payreq_id || ' to ' || l_request_status || '.');
UPDATE
iby_pay_service_requests
SET
payment_service_request_status = l_request_status
WHERE
payment_service_request_id = p_payreq_id
;
END performDBUpdates;
SELECT DISTINCT
val_asgn.validation_assignment_id,
val_asgn.val_assignment_entity_type,
val.validation_set_display_name,
val.validation_set_code,
val.validation_code_package,
val.validation_code_entry_point
FROM
IBY_VALIDATION_SETS_VL val,
IBY_VAL_ASSIGNMENTS val_asgn
WHERE
val.validation_set_code = val_asgn.validation_set_code
AND val.validation_level_code = 'PAYMENT'
AND (val_asgn.val_assignment_entity_type = 'METHOD'
AND val_asgn.assignment_entity_id =
p_payment_method_code
OR val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_asgn.assignment_entity_id =
to_char(p_int_bank_acct_id)
OR val_asgn.val_assignment_entity_type = 'FORMAT'
AND val_asgn.assignment_entity_id =
p_payment_format_code
OR val_asgn.val_assignment_entity_type = 'BANK'
AND val_asgn.assignment_entity_id =
to_char(p_bepid)
OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
AND val_asgn.assignment_entity_id =
p_transmit_protocol_code
)
AND NVL(val_asgn.inactive_date, sysdate+1) > sysdate
/*
* Fix for bug 4997133:
*
* Pick up all applicable validation sets that match
* the payment method and country code on this payment.
* (if payment method /country code on validation
* set is null, it means that the validation set is
* applicable to all payment methods / countries).
*/
AND (NVL(p_payment_method_code, '0') =
NVL(val_asgn.payment_method_code, '0') OR
val_asgn.payment_method_code IS NULL
)
AND (p_country= val_asgn.territory_code OR
val_asgn.territory_code IS NULL
)
;
* once per payment, delete all the error messages linked
* to the payments of this PPR, and reset the payment status
* of the failed payments in one shot.
*/
IF(resetErrtbl.EXISTS(p_payment_request_id)) THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'ResetPaymentErrors API is already called for this PPR');
SELECT cba.bank_account_id,
cb.country,
cb.bank_home_country,
cba.zero_amount_allowed
INTO l_int_bank_acct_id,
l_country,
l_bank_home_country,
l_allow_zero_pmt_flag
FROM CE_BANK_ACCOUNTS cba, CE_BANK_BRANCHES_V cb
WHERE cba.bank_branch_id = cb.branch_party_id
AND cba.bank_account_id = IBY_PAYGROUP_PUB.pmtTable.INTERNAL_BANK_ACCOUNT_ID(l_trx_valid_index);
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(l_trx_valid_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(l_trx_valid_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
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 = IBY_PAYGROUP_PUB.pmtTable.PAYMENT_PROFILE_ID(l_trx_valid_index) ;
print_debuginfo(l_module_name, 'Before doing a bulk insert to the cursor');
-- 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 ' ||IBY_PAYGROUP_PUB.pmtTable.INTERNAL_BANK_ACCOUNT_ID(l_trx_valid_index));
print_debuginfo(l_module_name, 'Before doing a bulk insert l_payment_format_cd is '||Nvl(l_payment_format_cd,0));
print_debuginfo(l_module_name, 'Before doing a bulk insert l_bepid is'||Nvl(l_bepid,0));
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);
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 'payments_created' 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)
*/
getRejectedDocs(p_payreq_id, l_rej_doc_id_list,
l_rej_doc_status_list);
| the executed select condition.
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
| XML generation from PLSQL is evolving rapidly.
|
| The code below uses DBMS_XMLQuery() to generate XML
| from a SELECT statement.
|
| DBMS_XMLQuery() uses Java code internally, and is slow.
|
| Better ways to generate XML are:
| 1. DBMS_XMLGEN
| DBMS_XMLGEN is a built-in package in C. It is fast. However,
| it is supported only in Oracle 9i and above.
|
| 2. SQLX
| This is the new emerging standard for SQL -> XML.
| It is both fast and easy. However, only Oracle 9i and
| above.
|
*---------------------------------------------------------------------*/
FUNCTION getXMLClob(
p_payreq_id IN VARCHAR2
)
RETURN CLOB
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getXMLClob';
* Select all docs that:
* 1. Have the given pay req id
* 2. Are not in 'payments_created' 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_id '
|| 'FROM IBY_DOCS_PAYABLE_ALL '
|| 'WHERE payment_service_request_id = :payreq_id '
|| 'AND document_status <> :doc_status '
|| 'AND payment_id IS NOT NULL';
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_PAY_CREATED AND
doc.payment_id IS NOT NULL
;
SELECT
NULL,
sysoptions.require_prop_pmts_review_flag
FROM
IBY_INTERNAL_PAYERS_ALL sysoptions
WHERE
sysoptions.org_id IS NULL
;
SELECT
sysoptions.payment_rejection_level_code,
NULL
FROM
IBY_INTERNAL_PAYERS_ALL sysoptions
WHERE
sysoptions.org_id IS NULL
;
* 'l_temp_discount_date'; Update the value of
* Update the value of 'l_temp_due_date'
* only if the new due date occurs
* earlier.
*/
IF (p_docsInPmtTab(j).pmt_due_date <
l_temp_due_date) THEN
l_temp_due_date := p_docsInPmtTab(j).
pmt_due_date;
SELECT
payment_method_code,
maturity_date_offset_days
FROM
IBY_PAYMENT_METHODS_VL
WHERE
support_bills_payable_flag = 'Y'
;
select min(discount_date)
into l_temp_discount_date
from iby_docs_in_pmt_gt
where payment_id = IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_mat_index)
AND discount_date is not null
AND discount_date >= IBY_PAYGROUP_PUB.pmtTable.payment_date(p_trx_mat_index);
select min(payment_due_date)
into l_temp_due_date from iby_docs_in_pmt_gt
where payment_id = IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_mat_index)
AND payment_due_date is not null
AND payment_due_date >= IBY_PAYGROUP_PUB.pmtTable.payment_date(p_trx_mat_index);
SELECT maturity_date_offset_days
INTO l_maturity_days_offset
FROM IBY_PAYMENT_METHODS_VL
WHERE support_bills_payable_flag = 'Y'
AND payment_method_code = p_pmt_method_code;
x_paymentTab(i).last_updated_by := fnd_global.user_id;
x_paymentTab(i).last_update_login := fnd_global.user_id;
x_paymentTab(i).last_update_date := sysdate;
SELECT
payment_service_request_status
INTO
l_payreq_status
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
payment_service_request_id = l_payreq_id;
SELECT
*
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = p_payment_request_id AND
/*
* For the rebuild:
* Only select payments that are in non-failed
* status, or payments that are soft failed
* ('FAILED_VALIDATION') status here. Do not
* select payments that are is 'REJECTED' status
* here as the underlying documents of rejected
* payments would have already been kicked back to AP.
*/
payment_status IN
(
PAY_STATUS_CREATED,
PAY_STATUS_MODIFIED,
PAY_STATUS_FAIL_VALID
)
;
SELECT
payment_id,
document_payable_id,
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,
payment_amount,
payment_currency_code,
payment_grouping_number,
document_status,
amount_withheld,
payment_due_date,
discount_date,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
payment_curr_discount_taken,
delivery_channel_code,
unique_remittance_identifier,
uri_check_digit,
inv_payee_party_id,
inv_party_site_id,
inv_supplier_site_id,
inv_beneficiary_party,
ext_inv_payee_id,
relationship_id
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
payment_service_request_id = p_payment_request_id AND
payment_id = p_payment_id AND
document_status IN
(
DOC_STATUS_PAY_CREATED,
DOC_STATUS_PAY_VAL_FAIL
)
;
SELECT
pmt.payment_id,
prof.declaration_option,
prof.dcl_only_foreign_curr_pmt_flag,
prof.declaration_curr_fx_rate_type,
prof.declaration_currency_code,
prof.declaration_threshold_amount
FROM
IBY_PAYMENTS_ALL pmt,
IBY_PAYMENT_PROFILES prof
WHERE
pmt.payment_id = p_payment_id AND
pmt.payment_profile_id = prof.payment_profile_id
;
SELECT
calling_app_id,
call_app_pay_service_req_code
INTO
l_ca_id,
l_ca_payreq_cd
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
PAYMENT_SERVICE_REQUEST_ID = p_payment_request_id
;
SELECT
*
FROM
IBY_PAYMENTS_ALL
WHERE
payment_service_request_id = p_payment_request_id AND
/*
* For the rebuild:
* Only select payments that are in non-failed
* status, or payments that are soft failed
* ('FAILED_VALIDATION') status here. Do not
* select payments that are is 'REJECTED' status
* here as the underlying documents of rejected
* payments would have already been kicked back to AP.
*/
payment_status IN
(
PAY_STATUS_CREATED,
PAY_STATUS_MODIFIED,
PAY_STATUS_FAIL_VALID
)
;
SELECT
pmt.payment_id,
prof.declaration_option,
prof.dcl_only_foreign_curr_pmt_flag,
prof.declaration_curr_fx_rate_type,
prof.declaration_currency_code,
prof.declaration_threshold_amount
FROM
IBY_PAYMENTS_ALL pmt,
IBY_PAYMENT_PROFILES prof
WHERE
pmt.payment_id = p_payment_id AND
pmt.payment_profile_id = prof.payment_profile_id
;
INSERT INTO iby_docs_in_pmt_gt(
payment_id,
document_payable_id,
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,
document_amount,
document_currency_code,
payment_grouping_number,
document_status,
amount_withheld,
payment_due_date,
discount_date,
internal_bank_account_id,
ext_payee_id,
payee_party_id,
party_site_id,
supplier_site_id,
org_id,
org_type,
external_bank_account_id,
payment_curr_discount_taken,
delivery_channel_code,
unique_remittance_identifier,
uri_check_digit,
created_by,
creation_date,
last_updated_by,
Last_update_date,
last_update_login,
object_version_number,
dont_pay_flag,
dont_pay_reason_code,
dont_pay_description
)
SELECT
idpa.payment_id,
idpa.document_payable_id,
idpa.calling_app_id,
idpa.calling_app_doc_unique_ref1,
idpa.calling_app_doc_unique_ref2,
idpa.calling_app_doc_unique_ref3,
idpa.calling_app_doc_unique_ref4,
idpa.calling_app_doc_unique_ref5,
idpa.pay_proc_trxn_type_code,
idpa.payment_amount,
idpa.payment_currency_code,
idpa.payment_grouping_number,
DOC_STATUS_PAY_CREATED,
idpa.amount_withheld,
idpa.payment_due_date,
idpa.discount_date,
idpa.internal_bank_account_id,
idpa.ext_payee_id,
idpa.payee_party_id,
idpa.party_site_id,
idpa.supplier_site_id,
idpa.org_id,
idpa.org_type,
idpa.external_bank_account_id,
idpa.payment_curr_discount_taken,
idpa.delivery_channel_code,
idpa.unique_remittance_identifier,
idpa.uri_check_digit,
idpa.created_by,
idpa.creation_date,
idpa.last_updated_by,
idpa.Last_update_date,
idpa.last_update_login,
idpa.object_version_number,
'N',
null,
null
FROM
IBY_DOCS_PAYABLE_ALL idpa, IBY_PAYMENTS_ALL ibpa
WHERE
idpa.payment_service_request_id = p_payment_request_id
AND idpa.payment_service_request_id = ibpa.payment_service_request_id
AND idpa.payment_id = ibpa.payment_id
AND idpa.document_status IN
(
DOC_STATUS_PAY_CREATED,
DOC_STATUS_PAY_VAL_FAIL
)
AND ibpa.payment_status IN
(
PAY_STATUS_CREATED,
PAY_STATUS_MODIFIED,
PAY_STATUS_FAIL_VALID
)
;
SELECT SUM(document_amount) into l_pmt_amount
FROM iby_docs_in_pmt_gt
WHERE payment_id = l_paymentTab(i).payment_id;
update iby_payments_all set payment_amount = l_pmt_amount,
payment_status = l_paymentTab(i).payment_status
where payment_id = l_paymentTab(i).payment_id;
SELECT
calling_app_id,
call_app_pay_service_req_code
INTO
l_ca_id,
l_ca_payreq_cd
FROM
IBY_PAY_SERVICE_REQUESTS
WHERE
PAYMENT_SERVICE_REQUEST_ID = p_payment_request_id
;
| insertAuditData
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertAuditData(
p_auditPmtTab IN paymentAuditTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertAuditData';
|| 'was provided to update IBY_PAYMENTS_ALL table. '
|| 'Exiting ..'
);
UPDATE
IBY_PAYMENTS_ALL
SET
payer_party_number = p_auditPmtTab(i).
payer_party_number,
payer_party_site_name = p_auditPmtTab(i).
payer_party_site_name,
payer_legal_entity_name = p_auditPmtTab(i).payer_legal_name,
payer_tax_registration_num = p_auditPmtTab(i).
payer_tax_reg_number,
payer_le_registration_num = p_auditPmtTab(i).
payer_le_reg_number,
payer_party_id = p_auditPmtTab(i).
payer_party_id,
payer_location_id = p_auditPmtTab(i).
payer_location_id,
payer_party_attr_category = p_auditPmtTab(i).
payer_party_attr_cat,
payer_le_attr_category = p_auditPmtTab(i).
payer_le_attr_cat,
payer_abbreviated_agency_code = p_auditPmtTab(i).
payer_abbrev_agency_code,
payer_federal_us_employer_id = p_auditPmtTab(i).
payer_us_employer_id,
int_bank_name = p_auditPmtTab(i).payer_bank_name,
int_bank_number = p_auditPmtTab(i).
payer_bank_number,
int_bank_branch_number = p_auditPmtTab(i).
payer_bank_branch_number,
int_bank_branch_name = p_auditPmtTab(i).
payer_bank_branch_name,
int_eft_swift_code = p_auditPmtTab(i).
payer_bank_swift_code,
int_bank_account_number = p_auditPmtTab(i).
payer_bank_acct_num,
int_bank_account_name = p_auditPmtTab(i).
payer_bank_acct_name,
int_bank_account_iban = p_auditPmtTab(i).
payer_bank_acct_iban,
int_bank_acct_agency_loc_code = p_auditPmtTab(i).
payer_bank_agency_loc_code,
int_bank_branch_party_id = p_auditPmtTab(i).
payer_bank_branch_party_id,
int_bank_alt_name = p_auditPmtTab(i).
payer_bank_alt_name,
int_bank_branch_alt_name = p_auditPmtTab(i).
payer_bank_branch_alt_name,
int_bank_account_alt_name = p_auditPmtTab(i).
payer_bank_alt_account_name,
int_bank_account_num_elec = p_auditPmtTab(i).
payer_bank_account_num_elec,
int_bank_branch_location_id = p_auditPmtTab(i).
payer_bank_branch_location_id,
int_bank_branch_eft_user_num = p_auditPmtTab(i).
payer_bank_branch_eft_user_num,
payee_party_number = p_auditPmtTab(i).
payee_party_number,
payee_party_name = p_auditPmtTab(i).payee_party_name,
payee_name = p_auditPmtTab(i).payee_name,
payee_alternate_name = p_auditPmtTab(i).payee_name_alternate, -- Bug 6175102
payee_address1 = p_auditPmtTab(i).payee_add_line_1,
payee_address2 = p_auditPmtTab(i).payee_add_line_2,
payee_address3 = p_auditPmtTab(i).payee_add_line_3,
payee_address4 = p_auditPmtTab(i).payee_add_line_4,
payee_city = p_auditPmtTab(i).payee_city,
payee_county = p_auditPmtTab(i).payee_county,
payee_province = p_auditPmtTab(i).payee_province,
payee_state = p_auditPmtTab(i).payee_state,
payee_country = p_auditPmtTab(i).payee_country,
payee_postal_code = p_auditPmtTab(i).
payee_postal_code,
payee_address_concat = p_auditPmtTab(i).
payee_address_concat,
beneficiary_name = p_auditPmtTab(i).
beneficiary_name,
payee_party_attr_category = p_auditPmtTab(i).
payee_party_attr_cat,
payee_spplr_site_attr_category = p_auditPmtTab(i).
payee_supplier_site_attr_cat,
payee_supplier_site_name = p_auditPmtTab(i).
payee_supplier_site_name,
payee_party_site_name = p_auditPmtTab(i).
payee_party_site_name,
payee_addressee = p_auditPmtTab(i).
payee_addressee,
payee_site_alternate_name = p_auditPmtTab(i).
payee_site_name_alternate,
payee_supplier_number = p_auditPmtTab(i).
payee_supplier_number,
payee_first_party_reference = p_auditPmtTab(i).
payee_first_party_ref,
payee_supplier_attr_category = p_auditPmtTab(i).
payee_supplier_attr_cat,
payee_supplier_id = p_auditPmtTab(i).
payee_supplier_id,
payee_tax_registration_num = p_auditPmtTab(i).
payee_tax_reg_number,
payee_le_registration_num = p_auditPmtTab(i).
payee_le_reg_number,
ext_bank_name = p_auditPmtTab(i).payee_bank_name,
ext_bank_number = p_auditPmtTab(i).
payee_bank_number,
ext_branch_number = p_auditPmtTab(i).
payee_bank_branch_number,
ext_bank_branch_name = p_auditPmtTab(i).
payee_bank_branch_name,
ext_bank_account_number = p_auditPmtTab(i).
payee_bank_acct_number,
ext_bank_account_name = p_auditPmtTab(i).
payee_bank_acct_name,
ext_bank_account_iban_number = p_auditPmtTab(i).
payee_bank_acct_iban,
ext_eft_swift_code = p_auditPmtTab(i).
payee_bank_swift_code,
ext_bank_account_type = p_auditPmtTab(i).
payee_bank_acct_type,
ext_bank_acct_pmt_factor_flag = p_auditPmtTab(i).
payee_bank_payment_factor_flag,
ext_bank_acct_owner_party_id = p_auditPmtTab(i).
payee_bank_owner_party_id,
ext_bank_branch_party_id = p_auditPmtTab(i).
payee_bank_branch_party_id,
ext_bank_alt_name = p_auditPmtTab(i).
payee_bank_name_alt,
ext_bank_branch_alt_name = p_auditPmtTab(i).
payee_bank_branch_name_alt,
ext_bank_account_alt_name = p_auditPmtTab(i).
payee_bank_alt_account_name,
ext_bank_account_num_elec = p_auditPmtTab(i).
payee_bank_electronic_acct_num,
ext_bank_branch_location_id = p_auditPmtTab(i).
payee_bank_branch_location_id,
ext_bank_acct_owner_party_name = p_auditPmtTab(i).
payee_bank_acct_owner_name,
remit_advice_delivery_method = p_auditPmtTab(i).
remit_advice_delivery_method,
remit_advice_email = p_auditPmtTab(i).
remit_advice_email,
remit_advice_fax = p_auditPmtTab(i).
remit_advice_fax,
/*
* Fix for bug 5522421:
*
* sra_delivery_method is the same as
* remit_advice_delivery_method and is
* being obsoleted.
*/
--sra_delivery_method = p_auditPmtTab(i).
-- remit_advice_delv_method,
delivery_channel_format_value = p_auditPmtTab(i).
delivery_channel_format,
declaration_exch_rate_type = p_auditPmtTab(i).
decl_curr_fx_rate_type,
declaration_format = p_auditPmtTab(i).
declaration_format,
payment_profile_acct_name = p_auditPmtTab(i).
payment_acct_profile_name,
payment_profile_sys_name = p_auditPmtTab(i).
payment_sys_profile_name,
payment_reason_format_value = p_auditPmtTab(i).
payment_reason_format,
bank_instruction1_format_value = p_auditPmtTab(i).
bank_instr1_format,
bank_instruction2_format_value = p_auditPmtTab(i).
bank_instr2_format,
org_name = p_auditPmtTab(i).org_name,
int_bank_branch_rfc_identifier = p_auditPmtTab(i).
payer_bank_branch_rfc_id,
payment_process_request_name = p_auditPmtTab(i).ppr_name,
source_product = p_auditPmtTab(i).source_product,
/*TPP-Start*/
inv_payee_name = p_auditPmtTab(i).inv_payee_name,
inv_payee_address1 = p_auditPmtTab(i).inv_payee_address1,
inv_payee_address2 = p_auditPmtTab(i).inv_payee_address2,
inv_payee_address3 = p_auditPmtTab(i).inv_payee_address3,
inv_payee_address4 = p_auditPmtTab(i).inv_payee_address4,
inv_payee_city = p_auditPmtTab(i).inv_payee_city,
inv_payee_postal_code = p_auditPmtTab(i).inv_payee_postal_code,
inv_payee_state = p_auditPmtTab(i).inv_payee_state,
inv_payee_province = p_auditPmtTab(i).inv_payee_province,
inv_payee_county = p_auditPmtTab(i).inv_payee_county,
inv_payee_country = p_auditPmtTab(i).inv_payee_country,
inv_payee_party_name = p_auditPmtTab(i).inv_payee_party_name,
inv_payee_le_reg_num = p_auditPmtTab(i).inv_payee_le_reg_num,
inv_payee_tax_reg_num = p_auditPmtTab(i).inv_payee_tax_reg_num,
inv_payee_address_concat = p_auditPmtTab(i).inv_payee_address_concat,
inv_beneficiary_name = p_auditPmtTab(i).inv_beneficiary_name,
inv_payee_party_number = p_auditPmtTab(i).inv_payee_party_number,
inv_payee_alternate_name = p_auditPmtTab(i).inv_payee_alternate_name,
inv_payee_site_alt_name = p_auditPmtTab(i).inv_payee_site_alt_name,
inv_payee_supplier_number = p_auditPmtTab(i).inv_payee_supplier_number,
inv_payee_first_party_ref = p_auditPmtTab(i).inv_payee_first_party_ref,
ext_bnk_acct_ownr_inv_prty_id = p_auditPmtTab(i).ext_bnk_acct_ownr_inv_prty_id,
ext_bnk_branch_inv_prty_id = p_auditPmtTab(i).ext_bnk_branch_inv_prty_id,
ext_bnk_acct_ownr_inv_prty_nme = p_auditPmtTab(i).ext_bnk_acct_ownr_inv_prty_nme,
inv_payee_party_attr_cat = p_auditPmtTab(i).inv_payee_party_attr_cat,
inv_payee_supplier_attr_cat = p_auditPmtTab(i).inv_payee_supplier_attr_cat,
inv_payee_spplr_site_attr_cat = p_auditPmtTab(i).inv_payee_spplr_site_attr_cat,
inv_payee_supplier_site_name = p_auditPmtTab(i).inv_payee_supplier_site_name,
inv_payee_spplr_site_alt_name = p_auditPmtTab(i).inv_payee_spplr_site_alt_name,
inv_payee_supplier_id = p_auditPmtTab(i).inv_payee_supplier_id
/*TPP-End*/
WHERE
payment_id = p_auditPmtTab(i).payment_id
;
END insertAuditData;
SELECT
payer_party.party_number, -- payer party number
'', -- payer party site name
payer.name, -- payer name
IBY_FD_EXTRACT_GEN_PVT. -- payer tax reg number
Get_FP_TaxRegistration
(
payer.legal_entity_id
),
payer.registration_number, -- payer le reg number
payer.party_id, -- payer_party_id
payer.location_id, -- payer_location_id
payer_party.attribute_category, -- payer party attr category
payer.attribute_category, -- payer LE attr category -- 10
'', -- payer abbrev agency code
'' -- payer federal us employer id
FROM
XLE_FIRSTPARTY_INFORMATION_V payer,
HZ_PARTIES payer_party
WHERE
/* payer */
IBY_PAYGROUP_PUB.pmtTable.legal_entity_id(l_trx_index) = payer.legal_entity_id
AND payer.party_id = payer_party.party_id
;
SELECT
/* 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.bank_account_num, -- payer bank account num
iba.bank_account_name, -- payer bank account name
iba.iban_number, -- payer bank acct iban number -- 20
iba.agency_location_code, -- payer bank agency location code
iba_bnk_branch.branch_party_id, -- payer bank branch party id
iba_bnk_branch.bank_name_alt, -- payer bank alt name
iba_bnk_branch.bank_branch_name_alt, -- payer bank branch alt name
iba.bank_account_name_alt, -- payer bank alt account name
iba.bank_account_num_electronic, -- payer bank account num electronic
iba_branch_party_site.location_id, -- payer bank branch location id
iba_bnk_branch.eft_user_number, -- payer bank branch eft user num
/* RFC */
rfc_ca.class_code
FROM
/* Payer bank */
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
HZ_PARTY_SITES iba_branch_party_site,
/* RFC */
HZ_CODE_ASSIGNMENTS rfc_ca
WHERE
/* payer bank */
IBY_PAYGROUP_PUB.pmtTable.internal_bank_account_id(l_trx_index) = iba.bank_account_id
AND iba_bnk_branch.branch_party_id = iba.bank_branch_id
/* payer bank location */
AND iba_bnk_branch.branch_party_id = iba_branch_party_site.party_id(+)
/* RFC related */
AND rfc_ca.owner_table_name(+) = 'HZ_PARTIES'
AND rfc_ca.class_category(+) = 'RFC_IDENTIFIER'
AND rfc_ca.owner_table_id(+) = iba.bank_branch_id
;
SELECT
/* VENDOR RELATED */
vendor_site.vendor_site_code, -- payee supplier site name
vendor_site.vendor_site_code_alt, -- payee site alternate name
vendor.segment1, -- payee supplier number
vendor.customer_num, -- payee first party reference -- 50
vendor.attribute_category, -- payee supplier attr category
vendor.vendor_id, -- payee supplier id
decode(upper(vendor.vendor_type_lookup_code),
'EMPLOYEE',null
,
'CONTRACTOR',decode(vendor.organization_type_lookup_code,
'INDIVIDUAL',vendor.individual_1099,
'FOREIGN INDIVIDUAL',vendor.individual_1099,
'PARTNERSHIP',vendor.individual_1099,
'FOREIGN PARTNERSHIP',vendor.individual_1099,
vendor.num_1099),
vendor.num_1099), -- payee Tax Identification Number
vendor_site.attribute_category -- vendor site attr category
FROM
/* Vendor related */
AP_SUPPLIERS vendor,
AP_SUPPLIER_SITES_ALL vendor_site
-- PER_PEOPLE_X per
WHERE
/* vendor related */
IBY_PAYGROUP_PUB.pmtTable.payee_party_id(l_trx_index) = vendor.party_id(+)
AND IBY_PAYGROUP_PUB.pmtTable.supplier_site_id(l_trx_index) = vendor_site.vendor_site_id(+)
AND vendor.vendor_id = vendor_site.vendor_id
-- AND IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = per.person_id(+)
;
SELECT hzp.jgzz_fiscal_code
FROM HZ_PARTIES hzp
WHERE
IBY_PAYGROUP_PUB.pmtTable.payee_party_id(l_trxn_index) = hzp.party_id;
SELECT
payee.party_number, -- payee party number
DECODE(IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index),
NULL,payee.party_name,
Get_Employee_Full_Name(IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index),payee.party_id,payee.party_name)), -- payee party name -- 30
DECODE(IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index),
NULL,payee.party_name,
Get_Employee_Full_Name(IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index),payee.party_id,payee.party_name)), -- payee name
DECODE( -- payee alternate name
payee.party_type,
'ORGANIZATION',
payee.organization_name_phonetic,
'PERSON',
payee.person_first_name_phonetic
|| ' '
|| payee.person_last_name_phonetic
),
DECODE (IBY_PAYGROUP_PUB.pmtTable.beneficiary_party(l_trx_index),
NULL,
payee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=IBY_PAYGROUP_PUB.pmtTable.beneficiary_party(l_trx_index)
)
), -- beneficiary name
payee.attribute_category -- payee party attr category
FROM
HZ_PARTIES payee
WHERE
IBY_PAYGROUP_PUB.pmtTable.payee_party_id(l_trx_index) = payee.party_id
;
SELECT
payee_site.attribute_category, -- payee supplier site attr category
payee_site.party_site_name, -- payee party site name
payee_site.addressee
FROM
HZ_PARTY_SITES payee_site
WHERE
/* payment related */
IBY_PAYGROUP_PUB.pmtTable.party_site_id(l_trx_index) = payee_site.party_site_id(+)
;
SELECT
/* payee add line1 */
per_addr_prov.address_line1 add_line1,
/* payee add line2 */
per_addr_prov.address_line2 add_line2,
/* payee add line3 */
per_addr_prov.address_line3 add_line3,
/* payee add line4 */
null add_line4,
/* payee city */
per_addr_prov.town_or_city city,
/* payee county */
(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),
''),
'')) county,
/* payee province */
(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),
'')
) )province,
/* payee state */
DECODE(per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')) state,
/* payee country */
per_addr_prov.country country,
/* payee postal code */
per_addr_prov.postal_code postal_code,
/* payee address concat */
( 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)
add_concat
FROM
/* Employee address related */
PER_ADDRESSES per_addr_prov
WHERE
IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = 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);
SELECT
/* payee add line1 */
per_loc.address_line_1 add_line1,
/* payee add line2 */
per_loc.address_line_2 add_line2,
/* payee add line3 */
per_loc.address_line_3 add_line3,
/* payee add line4 */
null add_line4,
/* payee city */
per_loc.town_or_city city,
/* payee county */
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),
''),
'') county,
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),
'')
) province,
/* payee state */
DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')) state,
/* payee country */
per_loc.country country,
/* payee postal code */
per_loc.postal_code postal_code,
/* payee address concat */
(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) add_concat
FROM
/* Employee address related */
HR_LOCATIONS per_loc,
PER_ALL_ASSIGNMENTS_F per_assgn
WHERE
IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = 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
) ;
SELECT
/* payee add line1 */
per_addr.address_line1 add_line1,
/* payee add line2 */
per_addr.address_line2 add_line2,
/* payee add line3 */
per_addr.address_line3 add_line3,
/* payee add line4 */
null add_line4,
/* payee city */
per_addr.town_or_city city,
/* payee county */
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(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(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')) state,
/* payee country */
per_addr.country country,
/* payee postal code */
per_addr.postal_code postal_code,
/* payee address concat */
(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
FROM
/* Employee address related */
PER_ADDRESSES per_addr
WHERE
IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = 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);
SELECT
/* payee add 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 */
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_2,
per_addr.address_line2)
add_line2,
/* payee add 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 */
null
add_line4,
/* payee city */
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.town_or_city,
per_addr.town_or_city)
city,
/* payee county */
(
--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 */
(
--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 */
(
--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 */
(
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.country,
per_addr.country
)
)
country,
/* payee 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 */
(
-- 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
FROM
HR_LOCATIONS per_loc,
PER_ADDRESSES per_addr,
PER_ALL_ASSIGNMENTS_F per_assgn
WHERE
IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = 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 IBY_PAYGROUP_PUB.pmtTable.employee_person_id(l_trx_index) = 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(+)
)
;
SELECT
( payee_loc.address1
) add_line1,
/* payee add line2 */
( payee_loc.address2
) add_line2,
/* payee add line3 */
(payee_loc.address3
) add_line3,
/* payee add line4 */
(payee_loc.address4
) add_line4,
/* payee city */
( payee_loc.city
) city,
/* payee county */
( payee_loc.county
) county,
/* payee province */
(payee_loc.province
) province,
/* payee state */
( payee_loc.state
) state,
/* payee country */
(payee_loc.country
) country,
/* payee postal code */
( payee_loc.postal_code
) postal_code,
/* payee address concat */
(
payee_loc.address1
|| ', '
|| payee_loc.address2
|| ', '
|| payee_loc.address3
|| ', '
|| payee_loc.city
|| ', '
|| payee_loc.state
|| ', '
|| payee_loc.country
|| ', '
|| payee_loc.postal_code
) add_concat
FROM
HZ_LOCATIONS payee_loc
WHERE
IBY_PAYGROUP_PUB.pmtTable.remit_to_location_id(l_trx_index) = payee_loc.location_id(+)
;
SELECT
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
/*
* Fix for bug 5586882:
*
* Populate the external bank account using
* IBY_EXT_BANK_ACCOUNTS.bank_account_num
* so that we show the unmasked bank account number
*/
ext_ba_table.bank_account_num, -- payee bank account number
eba.bank_account_name, -- payee bank account name -- 60
eba.iban_number, -- payee bank account IBAN
eba.eft_swift_code, -- payee bank swift code
eba.bank_account_type, -- payee bank account type
eba.payment_factor_flag, -- payee bank payment factor flag
eba.primary_acct_owner_party_id, -- payee bank owner party id
eba.branch_party_id, -- payee bank branch party id
eba_bank_branch.bank_name_alt, -- payee bank name alt
eba_bank_branch.bank_branch_name_alt,-- payee bank branch name alt
eba.alternate_account_name, -- payee bank alt account name
eba.bank_account_num_electronic, -- payee bank electronic acct num -- 70
eba.bank_branch_address_id, -- payee bank branch location id
eba.primary_acct_owner_name -- payee bank primary acct owner name
FROM
/* Payee bank */
IBY_EXT_BANK_ACCOUNTS_V eba,
IBY_EXT_BANK_ACCOUNTS ext_ba_table,
CE_BANK_BRANCHES_V eba_bank_branch
WHERE
/* payee bank */
IBY_PAYGROUP_PUB.pmtTable.external_bank_account_id(l_trx_index) = 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 ext_ba_table.ext_bank_account_id(+) = eba.ext_bank_account_id
;
SELECT EBA_BANK_BRANCH.BANK_NAME ,
EBA_BANK_BRANCH.BANK_NUMBER ,
EBA_BANK_BRANCH.BRANCH_NUMBER ,
EBA_BANK_BRANCH.BANK_BRANCH_NAME ,
EBA.BANK_ACCOUNT_NUM ,
EBA.BANK_ACCOUNT_NAME ,
EBA.IBAN IBAN_NUMBER,
EBA_BANK_BRANCH.EFT_SWIFT_CODE,
EBA.BANK_ACCOUNT_TYPE,
EBA.PAYMENT_FACTOR_FLAG,
ow.account_owner_party_id PRIMARY_ACCT_OWNER_PARTY_ID,
EBA.BRANCH_ID BRANCH_PARTY_ID,
EBA_BANK_BRANCH.BANK_NAME_ALT,
EBA_BANK_BRANCH.BANK_BRANCH_NAME_ALT,
EBA.BANK_ACCOUNT_NAME_ALT ALTERNATE_ACCOUNT_NAME,
EBA.BANK_ACCOUNT_NUM_ELECTRONIC,
hzps.LOCATION_ID BANK_BRANCH_ADDRESS_ID,
hzp.party_name PRIMARY_ACCT_OWNER_NAME
FROM IBY_EXT_BANK_ACCOUNTS EBA,
CE_BANK_BRANCHES_V EBA_BANK_BRANCH,
IBY_ACCOUNT_OWNERS OW,
HZ_PARTIES hzp,
HZ_PARTY_SITES hzps
WHERE IBY_PAYGROUP_PUB.pmtTable.external_bank_account_id(l_trx_index) = 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.account_owner_party_id = hzp.party_id(+)
AND ow.primary_flag(+) = 'Y'
AND NVL(ow.end_date,SYSDATE+10) >SYSDATE
AND EBA.BRANCH_ID = hzps.party_id(+)
AND hzps.identifying_address_flag(+) = 'Y';
SELECT
/* REMITTANCE ADVICE RELATED */
remit_advice.remit_advice_delivery_method, -- remittance advice delv method
'', -- remittance advice email
'',
/* DECLARATION REPORT RELATED */
prof.declaration_curr_fx_rate_type,
'', -- declaration format
/* BANK INSTRUCTION */
bank_instr1.format_value,
bank_instr2.format_value,
/* PROFILE RELATED */
prof.payment_profile_name, -- account profile name -- 80
prof.system_profile_name -- system profile name
FROM
/* Profile related */
IBY_PAYMENT_PROFILES prof,
/* Remit advice related */
IBY_REMIT_ADVICE_SETUP remit_advice,
/* Bank instruction related */
IBY_BANK_INSTRUCTIONS_VL bank_instr1,
IBY_BANK_INSTRUCTIONS_VL bank_instr2
WHERE
/* profile and remit advice */
IBY_PAYGROUP_PUB.pmtTable.payment_profile_id(l_trx_index) = prof.payment_profile_id
AND prof.system_profile_code = remit_advice.system_profile_code
/* bank instruction */
AND prof.bank_instruction1_code = bank_instr1.bank_instruction_code(+)
AND prof.bank_instruction2_code = bank_instr2.bank_instruction_code(+);
SELECT
/* REQUEST */
payreq.call_app_pay_service_req_code,-- ppr name
fnd_app.application_name -- source product -- 88
FROM
/* Payment service request related */
IBY_PAY_SERVICE_REQUESTS payreq,
FND_APPLICATION_ALL_VIEW fnd_app
WHERE
/* payment service request related */
payreq.payment_service_request_id = IBY_PAYGROUP_PUB.pmtTable.payment_service_request_id(l_trx_index)
AND fnd_app.application_id = payreq.calling_app_id
;
SELECT
/* DELIVERY CHANNEL RELATED */
deliv.format_value
FROM
/* Delivery channel related */
IBY_DELIVERY_CHANNELS_VL deliv
WHERE
/* delivery channel related */
IBY_PAYGROUP_PUB.pmtTable.delivery_channel_code(l_trx_index) = deliv.delivery_channel_code(+)
;
SELECT
org.name
FROM
HR_ALL_ORGANIZATION_UNITS org
where
IBY_PAYGROUP_PUB.pmtTable.org_id(l_trx_index) = org.organization_id;
SELECT
le.name
FROM
xle_entity_profiles le
where
IBY_PAYGROUP_PUB.pmtTable.org_id(l_trx_index) = le.legal_entity_id;
SELECT
/*TPP-Start*/
invpayee.party_name inv_payee_name,
invpayee.party_name inv_payee_party_name,
--13777562: PAYEE/LEGALENTITYREGISTRATIONNUMBER FOR REFUNDS IS MISSING
CASE
WHEN (NVL(IBY_PAYGROUP_PUB.pmtTable.supplier_site_id(l_trx_org_index),-99)>0)
THEN (DECODE(upper(invvendor.vendor_type_lookup_code),
'CONTRACTOR',DECODE(invvendor.organization_type_lookup_code,
'INDIVIDUAL',invvendor.individual_1099,
'FOREIGN INDIVIDUAL',invvendor.individual_1099,
'PARTNERSHIP',invvendor.individual_1099,
'FOREIGN PARTNERSHIP',invvendor.individual_1099,
invvendor.num_1099),
invvendor.num_1099))
ELSE invpayee.jgzz_fiscal_code
END inv_payee_le_reg_num,
--13777562 Changes done
/*Bug 13540717 Modifying the argument of Get_Payee_TaxRegistration*/
IBY_FD_EXTRACT_GEN_PVT.
Get_Payee_TaxRegistration
(invpayee.party_id,invvendor_site.vendor_site_id)
inv_payee_tax_reg_num,
DECODE (l_inv_beneficiary_party,
NULL,
invpayee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=l_inv_beneficiary_party
)
) inv_beneficiary_name,
invpayee.party_number inv_payee_party_number,
invpayee.organization_name_phonetic inv_payee_alternate_name,
invvendor_site.vendor_site_code_alt inv_payee_site_alt_name,
invvendor.segment1 inv_payee_supplier_number,
invvendor.customer_num inv_payee_first_party_ref,
invpayee.attribute_category inv_payee_party_attr_cat,
invvendor.attribute_category inv_payee_supplier_attr_cat,
NVL(invvendor_site.attribute_category,invpayee_site.attribute_category) inv_payee_spplr_site_attr_cat,
invvendor_site.vendor_site_code inv_payee_supplier_site_name,
invvendor_site.vendor_site_code_alt inv_payee_spplr_site_alt_name,
invvendor.vendor_id inv_payee_supplier_id
/*TPP-End*/
INTO
IBY_PAYGROUP_PUB.pmtTable.inv_payee_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_le_reg_num(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_tax_reg_num(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_number(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_alternate_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_site_alt_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_number(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_attr_cat(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_attr_cat(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_attr_cat(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_site_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_alt_name(l_trx_org_index),
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_id(l_trx_org_index)
FROM
/* Payee */
HZ_PARTIES invpayee,
HZ_PARTY_SITES invpayee_site,
AP_SUPPLIERS invvendor,
AP_SUPPLIER_SITES_ALL invvendor_site
WHERE
/*Bug 13540717 --Start Modify*/
/* inv payee */
l_inv_payee_party_id = invpayee.party_id
/* inv payee site */
AND invpayee.party_id = invpayee_site.party_id(+)
AND nvl(l_inv_party_site_id,-999) = invpayee_site.party_site_id(+)
/* inv vendor related */
AND invpayee.party_id = invvendor.party_id(+)
AND nvl(invvendor.vendor_id,-999) = invvendor_site.vendor_id(+)
AND nvl(l_inv_supplier_site_id, -999) = invvendor_site.vendor_site_id(+); /*Bug 7323072*/
SELECT
/* PAYMENT REASON */
pmt_reason.format_value
FROM
/* Payment reason related */
IBY_PAYMENT_REASONS_VL pmt_reason
WHERE
/* payment reason */
IBY_PAYGROUP_PUB.pmtTable.payment_reason_code(l_trx_index) = pmt_reason.payment_reason_code(+)
;
SELECT
payment_id,
payment_method_code,
payment_service_request_id,
process_type,
payment_status,
payments_complete_flag,
payment_function,
payment_amount,
payment_currency_code,
bill_payable_flag,
exclusive_payment_flag,
separate_remit_advice_req_flag,
internal_bank_account_id,
org_id,
org_type,
legal_entity_id,
declare_payment_flag,
delivery_channel_code,
ext_payee_id,
payment_instruction_id,
payment_profile_id,
pregrouped_payment_flag,
stop_confirmed_flag,
stop_released_flag,
stop_request_placed_flag,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
object_version_number,
payee_party_id,
party_site_id,
supplier_site_id,
payment_reason_code,
payment_reason_comments,
payment_date,
anticipated_value_date,
declaration_amount,
declaration_currency_code,
discount_amount_taken,
payment_details,
bank_charge_bearer,
bank_charge_amount,
settlement_priority,
remittance_message1,
remittance_message2,
remittance_message3,
payment_reference_number,
paper_document_number,
bank_assigned_ref_code,
external_bank_account_id,
unique_remittance_identifier,
uri_check_digit,
bank_instruction1_code,
bank_instruction2_code,
bank_instruction_details,
payment_text_message1,
payment_text_message2,
payment_text_message3,
maturity_date,
payment_due_date,
document_category_code,
document_sequence_id,
document_sequence_value,
beneficiary_party,
stop_confirmed_by,
stop_confirm_date,
stop_confirm_reason,
stop_confirm_reference,
stop_released_by,
stop_release_date,
stop_release_reason,
stop_release_reference,
stop_request_date,
stop_request_placed_by,
stop_request_reason,
stop_request_reference,
voided_by,
void_date,
void_reason,
remit_to_location_id,
completed_pmts_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
ext_branch_number,
ext_bank_number,
ext_bank_account_name,
ext_bank_account_number,
ext_bank_account_type,
ext_bank_account_iban_number,
payee_name,
payee_address1,
payee_address2,
payee_address3,
payee_address4,
payee_city,
payee_postal_code,
payee_state,
payee_province,
payee_county,
payee_country,
remit_advice_delivery_method,
remit_advice_email,
remit_advice_fax,
address_source,
employee_address_code,
employee_person_id,
employee_address_id,
employee_payment_flag,
ext_inv_payee_id ,
inv_payee_party_id ,
inv_party_site_id ,
inv_supplier_site_id ,
inv_beneficiary_party ,
inv_payee_name ,
inv_payee_address1 ,
inv_payee_address2 ,
inv_payee_address3 ,
inv_payee_address4 ,
inv_payee_city ,
inv_payee_postal_code ,
inv_payee_state ,
inv_payee_province ,
inv_payee_county ,
inv_payee_country ,
inv_payee_party_name ,
inv_payee_le_reg_num,
inv_payee_tax_reg_num,
inv_payee_address_concat ,
inv_beneficiary_name ,
inv_payee_party_number ,
inv_payee_alternate_name ,
inv_payee_site_alt_name,
inv_payee_supplier_number ,
inv_payee_first_party_ref,
ext_bnk_acct_ownr_inv_prty_id,
ext_bnk_branch_inv_prty_id,
ext_bnk_acct_ownr_inv_prty_nme,
inv_payee_party_attr_cat,
inv_payee_supplier_attr_cat,
inv_payee_spplr_site_attr_cat,
inv_payee_supplier_site_name ,
inv_payee_spplr_site_alt_name,
inv_payee_supplier_id,
-- start of auditPaymentData fields
payer_party_number ,
payer_party_site_name ,
payer_legal_entity_name ,
payer_tax_registration_num ,
payer_le_registration_num ,
payer_party_id ,
payer_location_id ,
payer_party_attr_category ,
payer_le_attr_category ,
payer_abbreviated_agency_code ,
payer_federal_us_employer_id ,
int_bank_name ,
int_bank_number ,
int_bank_branch_number ,
int_bank_branch_name ,
int_eft_swift_code ,
int_bank_account_number ,
int_bank_account_name ,
int_bank_account_iban ,
int_bank_acct_agency_loc_code ,
int_bank_branch_party_id ,
int_bank_alt_name ,
int_bank_branch_alt_name ,
int_bank_account_alt_name ,
int_bank_account_num_elec ,
int_bank_branch_location_id ,
int_bank_branch_eft_user_num ,
payee_party_number ,
payee_party_name ,
payee_alternate_name ,
payee_address_concat ,
beneficiary_name ,
payee_party_attr_category ,
payee_spplr_site_attr_category ,
payee_supplier_site_name ,
payee_party_site_name ,
payee_addressee ,
payee_site_alternate_name ,
payee_supplier_number ,
payee_first_party_reference ,
payee_supplier_attr_category ,
payee_supplier_id ,
payee_tax_registration_num ,
payee_le_registration_num ,
ext_bank_name,
ext_bank_branch_name ,
ext_eft_swift_code ,
ext_bank_acct_pmt_factor_flag ,
ext_bank_acct_owner_party_id ,
ext_bank_branch_party_id ,
ext_bank_alt_name ,
ext_bank_branch_alt_name ,
ext_bank_account_alt_name ,
ext_bank_account_num_elec ,
ext_bank_branch_location_id ,
ext_bank_acct_owner_party_name ,
--remit_advice_delivery_method ,
--remit_advice_email ,
--remit_advice_fax ,
delivery_channel_format_value ,
declaration_exch_rate_type ,
declaration_format ,
payment_profile_acct_name ,
payment_profile_sys_name ,
payment_reason_format_value ,
bank_instruction1_format_value ,
bank_instruction2_format_value ,
org_name ,
int_bank_branch_rfc_identifier ,
payment_process_request_name ,
source_product
FROM
IBY_PAYMENTS_ALL
where payment_service_request_id =ppr_id;
IBY_PAYGROUP_PUB.pmtTable.last_updated_by,
IBY_PAYGROUP_PUB.pmtTable.last_update_login,
IBY_PAYGROUP_PUB.pmtTable.last_update_date,
IBY_PAYGROUP_PUB.pmtTable.object_version_number,
IBY_PAYGROUP_PUB.pmtTable.payee_party_id,
IBY_PAYGROUP_PUB.pmtTable.party_site_id,
IBY_PAYGROUP_PUB.pmtTable.supplier_site_id,
IBY_PAYGROUP_PUB.pmtTable.payment_reason_code,
IBY_PAYGROUP_PUB.pmtTable.payment_reason_comments,
IBY_PAYGROUP_PUB.pmtTable.payment_date,
IBY_PAYGROUP_PUB.pmtTable.anticipated_value_date,
IBY_PAYGROUP_PUB.pmtTable.declaration_amount,
IBY_PAYGROUP_PUB.pmtTable.declaration_currency_code,
IBY_PAYGROUP_PUB.pmtTable.discount_amount_taken,
IBY_PAYGROUP_PUB.pmtTable.payment_details,
IBY_PAYGROUP_PUB.pmtTable.bank_charge_bearer,
IBY_PAYGROUP_PUB.pmtTable.bank_charge_amount,
IBY_PAYGROUP_PUB.pmtTable.settlement_priority,
IBY_PAYGROUP_PUB.pmtTable.remittance_message1,
IBY_PAYGROUP_PUB.pmtTable.remittance_message2,
IBY_PAYGROUP_PUB.pmtTable.remittance_message3,
IBY_PAYGROUP_PUB.pmtTable.payment_reference_number,
IBY_PAYGROUP_PUB.pmtTable.paper_document_number,
IBY_PAYGROUP_PUB.pmtTable.bank_assigned_ref_code,
IBY_PAYGROUP_PUB.pmtTable.external_bank_account_id,
IBY_PAYGROUP_PUB.pmtTable.unique_remittance_identifier,
IBY_PAYGROUP_PUB.pmtTable.uri_check_digit,
IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_code,
IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_code,
IBY_PAYGROUP_PUB.pmtTable.bank_instruction_details,
IBY_PAYGROUP_PUB.pmtTable.payment_text_message1,
IBY_PAYGROUP_PUB.pmtTable.payment_text_message2,
IBY_PAYGROUP_PUB.pmtTable.payment_text_message3,
IBY_PAYGROUP_PUB.pmtTable.maturity_date,
IBY_PAYGROUP_PUB.pmtTable.payment_due_date,
IBY_PAYGROUP_PUB.pmtTable.document_category_code,
IBY_PAYGROUP_PUB.pmtTable.document_sequence_id,
IBY_PAYGROUP_PUB.pmtTable.document_sequence_value,
IBY_PAYGROUP_PUB.pmtTable.beneficiary_party,
IBY_PAYGROUP_PUB.pmtTable.stop_confirmed_by,
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_date,
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reason,
IBY_PAYGROUP_PUB.pmtTable.stop_confirm_reference,
IBY_PAYGROUP_PUB.pmtTable.stop_released_by,
IBY_PAYGROUP_PUB.pmtTable.stop_release_date,
IBY_PAYGROUP_PUB.pmtTable.stop_release_reason,
IBY_PAYGROUP_PUB.pmtTable.stop_release_reference,
IBY_PAYGROUP_PUB.pmtTable.stop_request_date,
IBY_PAYGROUP_PUB.pmtTable.stop_request_placed_by,
IBY_PAYGROUP_PUB.pmtTable.stop_request_reason,
IBY_PAYGROUP_PUB.pmtTable.stop_request_reference,
IBY_PAYGROUP_PUB.pmtTable.voided_by,
IBY_PAYGROUP_PUB.pmtTable.void_date,
IBY_PAYGROUP_PUB.pmtTable.void_reason,
IBY_PAYGROUP_PUB.pmtTable.remit_to_location_id,
IBY_PAYGROUP_PUB.pmtTable.completed_pmts_group_id,
IBY_PAYGROUP_PUB.pmtTable.attribute_category,
IBY_PAYGROUP_PUB.pmtTable.attribute1,
IBY_PAYGROUP_PUB.pmtTable.attribute2,
IBY_PAYGROUP_PUB.pmtTable.attribute3,
IBY_PAYGROUP_PUB.pmtTable.attribute4,
IBY_PAYGROUP_PUB.pmtTable.attribute5,
IBY_PAYGROUP_PUB.pmtTable.attribute6,
IBY_PAYGROUP_PUB.pmtTable.attribute7,
IBY_PAYGROUP_PUB.pmtTable.attribute8,
IBY_PAYGROUP_PUB.pmtTable.attribute9,
IBY_PAYGROUP_PUB.pmtTable.attribute10,
IBY_PAYGROUP_PUB.pmtTable.attribute11,
IBY_PAYGROUP_PUB.pmtTable.attribute12,
IBY_PAYGROUP_PUB.pmtTable.attribute13,
IBY_PAYGROUP_PUB.pmtTable.attribute14,
IBY_PAYGROUP_PUB.pmtTable.attribute15,
IBY_PAYGROUP_PUB.pmtTable.ext_branch_number,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_number,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_name,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_number,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_type,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_iban_number,
IBY_PAYGROUP_PUB.pmtTable.payee_name,
IBY_PAYGROUP_PUB.pmtTable.payee_address1,
IBY_PAYGROUP_PUB.pmtTable.payee_address2,
IBY_PAYGROUP_PUB.pmtTable.payee_address3,
IBY_PAYGROUP_PUB.pmtTable.payee_address4,
IBY_PAYGROUP_PUB.pmtTable.payee_city,
IBY_PAYGROUP_PUB.pmtTable.payee_postal_code,
IBY_PAYGROUP_PUB.pmtTable.payee_state,
IBY_PAYGROUP_PUB.pmtTable.payee_province,
IBY_PAYGROUP_PUB.pmtTable.payee_county,
IBY_PAYGROUP_PUB.pmtTable.payee_country,
IBY_PAYGROUP_PUB.pmtTable.remit_advice_delivery_method,
IBY_PAYGROUP_PUB.pmtTable.remit_advice_email,
IBY_PAYGROUP_PUB.pmtTable.remit_advice_fax,
IBY_PAYGROUP_PUB.pmtTable.address_source,
IBY_PAYGROUP_PUB.pmtTable.employee_address_code,
IBY_PAYGROUP_PUB.pmtTable.employee_person_id,
IBY_PAYGROUP_PUB.pmtTable.employee_address_id,
IBY_PAYGROUP_PUB.pmtTable.employee_payment_flag,
IBY_PAYGROUP_PUB.pmtTable.ext_inv_payee_id,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_id,
IBY_PAYGROUP_PUB.pmtTable.inv_party_site_id,
IBY_PAYGROUP_PUB.pmtTable.inv_supplier_site_id,
IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_party,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address1,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address2,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address3,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address4,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_city,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_postal_code,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_state,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_province,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_county,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_country,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_le_reg_num,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_tax_reg_num,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_address_concat,
IBY_PAYGROUP_PUB.pmtTable.inv_beneficiary_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_number,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_alternate_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_site_alt_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_number,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_first_party_ref,
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_id,
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_branch_inv_prty_id,
IBY_PAYGROUP_PUB.pmtTable.ext_bnk_acct_ownr_inv_prty_nme,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_party_attr_cat,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_attr_cat,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_attr_cat,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_site_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_spplr_site_alt_name,
IBY_PAYGROUP_PUB.pmtTable.inv_payee_supplier_id,
-- start of auditPaymentdata fields
IBY_PAYGROUP_PUB.pmtTable.payer_party_number,
IBY_PAYGROUP_PUB.pmtTable.payer_party_site_name,
IBY_PAYGROUP_PUB.pmtTable.payer_legal_entity_name,
IBY_PAYGROUP_PUB.pmtTable.payer_tax_registration_num,
IBY_PAYGROUP_PUB.pmtTable.payer_le_registration_num,
IBY_PAYGROUP_PUB.pmtTable.payer_party_id,
IBY_PAYGROUP_PUB.pmtTable.payer_location_id,
IBY_PAYGROUP_PUB.pmtTable.payer_party_attr_category,
IBY_PAYGROUP_PUB.pmtTable.payer_le_attr_category,
IBY_PAYGROUP_PUB.pmtTable.payer_abbreviated_agency_code,
IBY_PAYGROUP_PUB.pmtTable.payer_federal_us_employer_id,
IBY_PAYGROUP_PUB.pmtTable.int_bank_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_number,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_number,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_name,
IBY_PAYGROUP_PUB.pmtTable.int_eft_swift_code,
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_number,
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_iban,
IBY_PAYGROUP_PUB.pmtTable.int_bank_acct_agency_loc_code,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_party_id,
IBY_PAYGROUP_PUB.pmtTable.int_bank_alt_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_alt_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_alt_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_account_num_elec,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_location_id,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_eft_user_num,
IBY_PAYGROUP_PUB.pmtTable.payee_party_number,
IBY_PAYGROUP_PUB.pmtTable.payee_party_name,
IBY_PAYGROUP_PUB.pmtTable.payee_alt_name, -- Bug 6175102
IBY_PAYGROUP_PUB.pmtTable.payee_address_concat,
IBY_PAYGROUP_PUB.pmtTable.beneficiary_name,
IBY_PAYGROUP_PUB.pmtTable.payee_party_atr_cat,
IBY_PAYGROUP_PUB.pmtTable.payee_spplr_site_attr_category,
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_site_name,
IBY_PAYGROUP_PUB.pmtTable.payee_party_site_name,
IBY_PAYGROUP_PUB.pmtTable.payee_addressee,
IBY_PAYGROUP_PUB.pmtTable.payee_site_alternate_name,
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_number,
IBY_PAYGROUP_PUB.pmtTable.payee_first_party_reference,
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_attr_category,
IBY_PAYGROUP_PUB.pmtTable.payee_supplier_id,
IBY_PAYGROUP_PUB.pmtTable.payee_tax_registration_num,
IBY_PAYGROUP_PUB.pmtTable.payee_le_registration_num,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_name,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_name,
IBY_PAYGROUP_PUB.pmtTable.ext_eft_swift_code,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_pmt_factor_flag,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_id,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_party_id,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_alt_name,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_alt_name,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_alt_name,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_account_num_elec,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_branch_location_id,
IBY_PAYGROUP_PUB.pmtTable.ext_bank_acct_owner_party_name,
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_delivery_method,
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_email,
--IBY_PAYGROUP_PUB.pmtTable.remit_advice_fax,
IBY_PAYGROUP_PUB.pmtTable.delivery_channel_format_value,
IBY_PAYGROUP_PUB.pmtTable.declaration_exch_rate_type,
IBY_PAYGROUP_PUB.pmtTable.declaration_format,
IBY_PAYGROUP_PUB.pmtTable.payment_profile_acct_name,
IBY_PAYGROUP_PUB.pmtTable.payment_profile_sys_name,
IBY_PAYGROUP_PUB.pmtTable.payment_reason_format_value,
IBY_PAYGROUP_PUB.pmtTable.bank_instruction1_format_value,
IBY_PAYGROUP_PUB.pmtTable.bank_instruction2_format_value,
IBY_PAYGROUP_PUB.pmtTable.org_name,
IBY_PAYGROUP_PUB.pmtTable.int_bank_branch_rfc_identifier,
IBY_PAYGROUP_PUB.pmtTable.payment_process_request_name,
IBY_PAYGROUP_PUB.pmtTable.source_product
;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
IBY_PAYGROUP_PUB.pmtTable.payment_id(p_trx_pmt_line_index),
IBY_PAYGROUP_PUB.pmtTable.payment_status(p_trx_pmt_line_index),
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
IBY_PAYGROUP_PUB.pmtTable.last_updated_by(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_login(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_date(l_trx_pmt_index) := sysdate;
IBY_PAYGROUP_PUB.pmtTable.last_updated_by(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_login(l_trx_pmt_index) := fnd_global.user_id;
IBY_PAYGROUP_PUB.pmtTable.last_update_date(l_trx_pmt_index) := sysdate;