[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
* 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.
*/
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);
* Update the payments table with audit data before
* calling central bank reporting or payment validations.
*
* This is because the customer could have implemented
* custom central bank reporting or custom payment
* validations that could depend upon the denormalized
* payment attributes being present in the the payments
* table.
*/
/*
* Along with the payment we insert the audit data for the
* payment as well. These are denormalized data from payment
* related tables like payee, payer, payee bank, payer bank
* etc.
*
* This information is also used by the extract and format
* logic.
*/
auditPaymentData(l_paymentTab);
* Therefore, update the payments before calling
* payment validations.
*
* Fix for bug 5935493:
*
* After central bank reporting is completed,
* the declare payments flag could be set on some/all
* payments by the hook. Therefore, we need to
* update the payments in IBY_PAYMENTS_ALL table
* so that this flag is accessible for validation.
*/
updatePayments(l_paymentTab);
* 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);
* 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_docsInPmtTab, 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.
*/
raiseBizEvents(p_payment_request_id, l_ca_payreq_cd, l_ca_id,
l_rejection_level, l_review_pmts_flag,
l_all_pmts_success_flag, l_all_pmts_failed_flag);
* 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
;
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);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, true, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
insertDocIntoPayment(l_paymentRec, x_paymentTab,
l_calc_doc_detail, false, l_payment_id,
x_docsInPmtTab, l_docsInPmtRec,
l_docs_in_pmt_count);
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;
| 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_site_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;
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;
| 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;
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
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;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
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(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
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(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
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(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
l_error_code,
FND_MESSAGE.get,
l_doc_err_rec
);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
SELECT
min_check_amount,
max_check_amount
INTO
l_ce_min_pmt_ctrl,
l_ce_max_pmt_ctrl
FROM
CE_BANK_ACCOUNTS
WHERE
bank_account_id = x_paymentTab(i).internal_bank_account_id
;
* an error record and insert this record
* into the errors table.
*/
IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
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(
x_paymentTab(i).payment_id,
x_paymentTab(i).payment_status,
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.
*/
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.'
);
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
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
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
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
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);
IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
l_doc_err_rec, x_docErrorTab, x_errTokenTab);
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);
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;
| 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,
l_triggering_pmt_id
);
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;
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.
*/
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,
IBY_PAYMENTS_ALL pmts,
IBY_SYS_PMT_PROFILES_B sys_prof,
IBY_ACCT_PMT_PROFILES_B acct_prof,
IBY_TRANSMIT_CONFIGS_B txconf,
IBY_TRANSMIT_PROTOCOLS_B txproto,
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_branch
WHERE
pmts.payment_id = p_payment_id
AND pmts.internal_bank_account_id = iba.bank_account_id
AND iba_branch.branch_party_id = iba.bank_branch_id
AND 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 =
pmts.payment_method_code
OR val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_asgn.assignment_entity_id =
pmts.internal_bank_account_id
OR val_asgn.val_assignment_entity_type = 'FORMAT'
AND val_asgn.assignment_entity_id =
sys_prof.payment_format_code
OR val_asgn.val_assignment_entity_type = 'BANK'
AND val_asgn.assignment_entity_id =
sys_prof.bepid
OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
AND val_asgn.assignment_entity_id =
txconf.transmit_protocol_code
)
AND pmts.payment_profile_id = acct_prof.payment_profile_id(+)
AND acct_prof.transmit_configuration_id
= txconf.transmit_configuration_id(+)
AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
AND sys_prof.system_profile_code(+) = acct_prof.system_profile_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(pmts.payment_method_code, '0') =
NVL(val_asgn.payment_method_code, '0') OR
val_asgn.payment_method_code IS NULL
)
AND (iba_branch.country = val_asgn.territory_code OR
val_asgn.territory_code IS NULL
)
;
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'
;
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
/* PAYMENT RELATED */
pmt.payment_id, -- 01
/* PAYER */
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 SPECIAL FIELDS */
'', -- payer abbrev agency code
'', -- payer federal us employer id
/* 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
/* PAYEE */
payee.party_number, -- payee party number
/* Fix for bug 7391524
* Full Name when Payee is an Employee is fetch from HR tables
* This is just as a temporary fix, until TCA overcomes it architecture limitations
*/
DECODE(pmt.employee_person_id,
NULL,payee.party_name,
Get_Employee_Full_Name(pmt.employee_person_id,pmt.payee_party_id,payee.party_name)) payee_party_name, -- payee party name
/*
* Fix for bug 5466979:
*
* Payee name is always the same as the payee party name.
*/
DECODE(pmt.employee_person_id,
NULL,payee.party_name,
Get_Employee_Full_Name(pmt.employee_person_id,pmt.payee_party_id,payee.party_name)) payee_name, -- payee name
DECODE( -- payee alternate name
payee.party_type,
'ORGANIZATION',
payee.organization_name_phonetic,
'PERSON',
TRIM(payee.person_first_name_phonetic
|| ' '
|| payee.person_last_name_phonetic)
),
/* PAYEE ADDRESS */
payee_addr.add_line1, -- payee add line1
payee_addr.add_line2, -- payee add line2
payee_addr.add_line3, -- payee add line3
payee_addr.add_line4, -- payee add line4
payee_addr.city, -- payee city
payee_addr.county, -- payee county
payee_addr.province, -- payee province
payee_addr.state, -- payee state -- 40
payee_addr.country, -- payee country
payee_addr.postal_code, -- payee postal code
payee_addr.add_concat, -- payee address concat
/*
* Fix for bug 5466979:
*
* Beneficiary name should be the same as the payee party name
* except when beneficiary party is not null.
*
* If beneficiary party is not null, the beneficiary name
* should be the hz_parties.party_name corresponding to the
* beneficiary_party.
*/
DECODE (pmt.beneficiary_party,
NULL,
payee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=pmt.beneficiary_party
)
), -- beneficiary name
payee.attribute_category, -- payee party attr category
payee_site.attribute_category, -- payee supplier site attr category
payee_site.party_site_name, -- payee supplier site name
/* VENDOR RELATED */
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
/* PAYEE SPECIAL FIELDS */
IBY_FD_EXTRACT_GEN_PVT. -- payee tax registration number
Get_Payee_TaxRegistration
(vendor.vendor_id,pmt.supplier_site_id),
/*
* Fix for bug 5468251:
*
* Use AP_SUPPLIERS.NUM_1099 for the payee
* LE registration number.
*/
/*
* Fix for bug 5475920:
*
* Payments can be made for employees or suppliers.
*
* For employees, the payee le reg num (i.e., taxpayer id)
* is stored in PER_PEOPLE_X.
*
* For suppliers, the payee le reg num (i.e., taxpayer id)
* is stored in AP_SUPPLIERS.
*
* Switch between these two tables depending upon payee type
* to get the payee le reg num
*/
/*
* Fix for bug 5501968:
*
* In R12, Federal supports thid party payments i.e., payments
* to third parties who may not be employees / suppliers.
*
* For this reason, we cannot assume that tax payer id for
* all non-employees will be stored in AP_SUPPLIERS.
*
* However, we know that all third parties / suppliers will
* have a row in HZ_PARTIES. Therefore, pick up the taxpayer
* id from HZ_PARTIES as this always works.
*/
/*
* Fix for bug 5501968:
*
* In R12, Federal supports thid party payments i.e., payments
*/
decode(upper(vendor.vendor_type_lookup_code),
'EMPLOYEE', per.national_identifier,
'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
/* PAYEE BANK */
eba.bank_name, -- payee bank name
eba.bank_number, -- payee bank number
eba.branch_number, -- payee bank branch number
eba.bank_branch_name, -- payee bank branch name
/*
* 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
/* REMITTANCE ADVICE RELATED */
remit_advice.remit_advice_delivery_method, -- remittance advice delv method
'', -- remittance advice email
'', -- remittance advice fax
/* DELIVERY CHANNEL RELATED */
deliv.format_value,
/* DECLARATION REPORT RELATED */
prof.declaration_curr_fx_rate_type,
'', -- declaration format
/* PROFILE RELATED */
prof.payment_profile_name, -- account profile name -- 80
prof.system_profile_name, -- system profile name
/* PAYMENT REASON */
pmt_reason.format_value,
/* BANK INSTRUCTION */
bank_instr1.format_value,
bank_instr2.format_value,
/* ORG */
org.name,
/* RFC */
rfc_ca.class_code,
/* REQUEST */
payreq.call_app_pay_service_req_code,-- ppr name
fnd_app.application_name, -- source product -- 88
/*TPP-Start*/
invpayee.party_name inv_payee_name,
null inv_payee_address1,
null inv_payee_address2,
null inv_payee_address3,
null inv_payee_address4,
null inv_payee_city,
null inv_payee_postal_code,
null inv_payee_state,
null inv_payee_province,
null inv_payee_county,
null inv_payee_country,
invpayee.party_name inv_payee_party_name,
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) inv_payee_le_reg_num,
IBY_FD_EXTRACT_GEN_PVT.
Get_Payee_TaxRegistration (invvendor.vendor_id,pmt.inv_supplier_site_id)
inv_payee_tax_reg_num,
null inv_payee_address_concat,
DECODE (pmt.inv_beneficiary_party,
NULL,
invpayee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=pmt.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,
null ext_bnk_acct_ownr_inv_prty_id,
null ext_bnk_branch_inv_prty_id,
null ext_bnk_acct_ownr_inv_prty_nme,
invpayee.attribute_category inv_payee_party_attr_cat,
invvendor.attribute_category inv_payee_supplier_attr_cat,
invpayee_site.attribute_category inv_payee_spplr_site_attr_cat,
invpayee_site.party_site_name 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*/
FROM
/* Payment related */
IBY_PAYMENTS_ALL pmt,
/* Profile related */
IBY_PAYMENT_PROFILES prof,
/* Payer */
XLE_FIRSTPARTY_INFORMATION_V payer,
HZ_PARTIES payer_party,
/* Payer bank */
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
HZ_PARTY_SITES iba_branch_party_site,
/* Payee */
HZ_PARTIES payee,
HZ_PARTIES invpayee,
/* Vendor related */
AP_SUPPLIERS vendor,
AP_SUPPLIER_SITES_ALL vendor_site,
AP_SUPPLIERS invvendor,
AP_SUPPLIER_SITES_ALL invvendor_site,
/* Employee related */
PER_PEOPLE_X per,
/* Payee site */
HZ_PARTY_SITES payee_site,
HZ_PARTY_SITES invpayee_site,
/* Payee bank */
IBY_EXT_BANK_ACCOUNTS_V eba,
IBY_EXT_BANK_ACCOUNTS ext_ba_table,
CE_BANK_BRANCHES_V eba_bank_branch,
/* Remit advice related */
IBY_REMIT_ADVICE_SETUP remit_advice,
/* Payment service request related */
IBY_PAY_SERVICE_REQUESTS payreq,
FND_APPLICATION_ALL_VIEW fnd_app,
/* Delivery channel related */
IBY_DELIVERY_CHANNELS_VL deliv,
/* Payment reason related */
IBY_PAYMENT_REASONS_VL pmt_reason,
/* Bank instruction related */
IBY_BANK_INSTRUCTIONS_VL bank_instr1,
IBY_BANK_INSTRUCTIONS_VL bank_instr2,
/* Org related */
HR_ALL_ORGANIZATION_UNITS org,
/* RFC */
HZ_CODE_ASSIGNMENTS rfc_ca,
/*
* Fix for bug 5928084:
*
* Derive address of employee type payees using
* PER_ADDRESSES or HR_LOCATIONS.
*
* The select statement below dynamically creates the payee
* address table. The dynamically created table is named
* as payee_add. Columns from this table are referenced in the
* outer select.
*
* The payee address can be either a
* - supplier address, or
* - employee address
*
* We decide whether we should pick up supplier addresses or
* employee addresses using the 'address source' field on the
* payment.
*
* 'address source' can contain two values
* - TCA: supplier address
* - HR : employee address
*
* Supplier addresses are present in TCA and are located using
* remit to location id column on the payment.
*
* Employee addresses can be in either
* - PER_ADDRESSES (home addresses)
* - HR_LOCATIONS (office addresses)
* We use the 'employee address code' field on the payment to
* decide which table to use.
*
* The select below uses decode clause to perform the
* if else logic to retrieve the address from the right
* column.
*
* Roughly, the decode clauses are used to effect the following
* logic -
*
* for each add field:
* if (pmt.address_source = 'TCA')
* {
* -- get the address field from HZ_LOCATIONS --
* }
* else
* {
* -- this is an employee address --
* if (pmt.employee_address_code = 'HOME')
* {
* -- get the address field from PER_ADDRESSES --
*
* The select logic for this has been lifted from
* AP's expense report import program.
* }
* else
* {
* -- get the address field from HR_LOCATIONS --
*
* The select logic for this has been lifted from
* /patch/115/sql/apwexptb.pls.
* }
* }
*/
(
SELECT
/* payee add line1 */
DECODE(
pmt.address_source,
-- supplier address line 1
'TCA', payee_loc.address1,
-- employee add line 1
DECODE
(
pmt.employee_address_code,
-- employee home addr line 1
'HOME', per_addr.address_line1,
-- employee office addr line 1
'OFFICE',per_loc.address_line_1,
-- employee provisional addr line 1
'PROVISIONAL', per_addr_prov.address_line1,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1,
per_addr.address_line1)
)
) add_line1,
/* payee add line2 */
DECODE(
pmt.address_source,
-- supplier address line 2
'TCA', payee_loc.address2,
-- employee add line 2
DECODE
(
pmt.employee_address_code,
-- employee home addr line 2
'HOME', per_addr.address_line2,
-- employee office addr line 2
'OFFICE',per_loc.address_line_2,
-- employee provisional addr line 2
'PROVISIONAL', per_addr_prov.address_line2,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_2,
per_addr.address_line2)
)
) add_line2,
/* payee add line3 */
DECODE(
pmt.address_source,
-- supplier address line 3
'TCA', payee_loc.address3,
-- employee add line 3
DECODE
(
pmt.employee_address_code,
-- employee home addr line 3
'HOME', per_addr.address_line3,
-- employee office addr line 3
'OFFICE',per_loc.address_line_3,
-- employee provisional addr line 3
'PROVISIONAL', per_addr_prov.address_line3,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_3,
per_addr.address_line3)
)
) add_line3,
/* payee add line4 */
DECODE(
pmt.address_source,
-- supplier address line 4
'TCA', payee_loc.address4,
-- employee home/office addr line 4 (not available)
null
) add_line4,
/* payee city */
DECODE(
pmt.address_source,
-- supplier city
'TCA', payee_loc.city,
-- employee city
DECODE
(
pmt.employee_address_code,
-- employee home city
'HOME', per_addr.town_or_city,
-- employee office city
'OFFICE', per_loc.town_or_city,
-- employee provisional city
'PROVISIONAL', per_addr_prov.town_or_city,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.town_or_city,
per_addr.town_or_city)
)
) city,
/* payee county */
DECODE(
pmt.address_source,
-- supplier county
'TCA', payee_loc.county,
-- employee county
(
DECODE(
pmt.employee_address_code,
-- employee home county
'HOME',
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''),
-- employee office county
'OFFICE',
DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
-- employee provisional county
'PROVISIONAL',
DECODE(
per_addr_prov.style,
'US', NVL(per_addr_prov.region_1, ''),
'US_GLB', NVL(per_addr_prov.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
''),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''))
)
)
) county,
/* payee province */
DECODE(
pmt.address_source,
-- supplier province
'TCA', payee_loc.province,
-- employee province
(
DECODE(
pmt.employee_address_code,
-- employee home province
'HOME',
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
),
-- employee office province
'OFFICE',
DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
-- employee provisional province
'PROVISIONAL',
DECODE(per_addr_prov.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr_prov.region_1, ''),
'CA_GLB', NVL(per_addr_prov.region_1, ''),
'JP', NVL(per_addr_prov.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
'')
),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
))
)
)
) province,
/* payee state */
DECODE(
pmt.address_source,
-- supplier state
'TCA', payee_loc.state,
-- employee state
(
DECODE(
pmt.employee_address_code,
-- employee home state
'HOME',
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')),
-- employee office state
'OFFICE',
DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
-- employee provisional state
'PROVISIONAL',
DECODE(per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')))
)
)
) state,
/* payee country */
DECODE(
pmt.address_source,
-- supplier country
'TCA', payee_loc.country,
-- employee country
(
DECODE(
pmt.employee_address_code,
-- employee home country
'HOME', per_addr.country,
-- employee office country
'OFFICE',per_loc.country,
-- employee provisional country
'PROVISIONAL', per_addr_prov.country,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.country,
per_addr.country
)
)
)
) country,
/* payee postal code */
DECODE(
pmt.address_source,
-- supplier postal code
'TCA', payee_loc.postal_code,
-- employee postal code
(
DECODE(
pmt.employee_address_code,
-- employee home postal code
'HOME', per_addr.postal_code,
-- employee office postal code
'OFFICE',per_loc.postal_code,
-- employee provisional postal code
'PROVISIONAL', per_addr_prov.postal_code,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.postal_code,
per_addr.postal_code
)
)
)
) postal_code,
/* payee address concat */
DECODE(
pmt.address_source,
-- supplier address concat
'TCA',
payee_loc.address1
|| ', '
|| payee_loc.address2
|| ', '
|| payee_loc.address3
|| ', '
|| payee_loc.city
|| ', '
|| payee_loc.state
|| ', '
|| payee_loc.country
|| ', '
|| payee_loc.postal_code,
-- employee address concat
(
DECODE(
pmt.employee_address_code,
-- employee home address concat
'HOME',
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code,
-- employee office address concat
'OFFICE',
per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
-- employee provisional address concat
'PROVISIONAL',
per_addr_prov.address_line1
|| ', '
|| per_addr_prov.address_line2
|| ', '
|| per_addr_prov.address_line3
|| ', '
|| per_addr_prov.town_or_city
|| ', '
|| DECODE(
per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')
)
|| ', '
|| per_addr_prov.country
|| ', '
|| per_addr_prov.postal_code,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code)
)
)
) add_concat
FROM
IBY_PAYMENTS_ALL pmt,
/* Employee address related */
HR_LOCATIONS per_loc,
PER_ADDRESSES per_addr,
PER_ALL_ASSIGNMENTS_F per_assgn,
PER_ADDRESSES per_addr_prov,
/* Supplier address related */
HZ_LOCATIONS payee_loc
WHERE
pmt.payment_id = p_payment_id
AND pmt.employee_person_id = per_addr.person_id(+)
AND per_addr.primary_flag(+) = 'Y'
AND SYSDATE BETWEEN
per_addr.date_from(+)
AND NVL(per_addr.date_to(+), SYSDATE+1)
AND pmt.employee_person_id = per_addr_prov.person_id(+)
AND per_addr_prov.address_type(+) = 'M'
AND SYSDATE BETWEEN
per_addr_prov.date_from(+)
AND NVL(per_addr_prov.date_to(+), SYSDATE+1)
AND pmt.employee_person_id = per_assgn.person_id(+)
AND per_assgn.location_id = per_loc.location_id(+)
AND per_assgn.primary_flag(+) = 'Y'
AND (TRUNC(SYSDATE) BETWEEN
per_assgn.effective_start_date(+)
AND per_assgn.effective_end_date(+)
)
AND pmt.remit_to_location_id = payee_loc.location_id(+)
) payee_addr
WHERE
/* payment related */
pmt.payment_id = p_payment_id
/* payer */
AND pmt.legal_entity_id = payer.legal_entity_id
AND payer.party_id = payer_party.party_id
/* payer bank */
AND pmt.internal_bank_account_id = 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(+)
/* payee */
AND pmt.payee_party_id = payee.party_id
/* payee site */
AND pmt.party_site_id = payee_site.party_site_id(+)
/* inv payee */
AND pmt.inv_payee_party_id = invpayee.party_id(+)
/* inv payee site */
AND pmt.inv_party_site_id = invpayee_site.party_site_id(+)
/* vendor related */
AND pmt.payee_party_id = vendor.party_id(+)
AND pmt.supplier_site_id = vendor_site.vendor_site_id(+)
AND vendor.vendor_id = vendor_site.vendor_id /*Bug 7323072*/
/* inv vendor related */
AND pmt.inv_payee_party_id = invvendor.party_id(+)
AND pmt.inv_supplier_site_id = invvendor_site.vendor_site_id(+)
AND nvl(vendor.vendor_id,-99) = nvl(vendor_site.vendor_id,-99) /*Bug 7323072*/
/* employee related */
AND pmt.employee_person_id = per.person_id(+)
/* payee bank */
AND pmt.external_bank_account_id = eba.ext_bank_account_id(+)
AND eba.bank_party_id = eba_bank_branch.bank_party_id(+)
AND eba.branch_party_id = eba_bank_branch.branch_party_id(+)
/*
* Fix for bug 5658982:
*
* We want to use outer join with the eba table because the
* ext bank account id is optional on the payment.
*
* For the same reason, we must use outer join with
* ext_ba_table table also.
*/
AND ext_ba_table.ext_bank_account_id(+) = eba.ext_bank_account_id
/* profile and remit advice */
AND pmt.payment_profile_id = prof.payment_profile_id
AND prof.system_profile_code = remit_advice.system_profile_code
/* payment service request related */
AND payreq.payment_service_request_id = pmt.payment_service_request_id
AND fnd_app.application_id = payreq.calling_app_id
/* delivery channel related */
AND pmt.delivery_channel_code = deliv.delivery_channel_code(+)
/* payment reason */
AND pmt.payment_reason_code = pmt_reason.payment_reason_code(+)
/* bank instruction */
AND prof.bank_instruction1_code = bank_instr1.bank_instruction_code(+)
AND prof.bank_instruction2_code = bank_instr2.bank_instruction_code(+)
/* Org related */
AND pmt.org_id = org.organization_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
/* PAYMENT RELATED */
pmt.payment_id, -- 01
/* PAYER */
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 SPECIAL FIELDS */
'', -- payer abbrev agency code
'', -- payer federal us employer id
/* 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
/* PAYEE */
payee.party_number, -- payee party number
/* Fix for bug 7391524
* Full Name when Payee is an Employee is fetch from HR tables
* This is just as a temporary fix, until TCA overcomes it architecture limitations
*/
DECODE(pmt.employee_person_id,
NULL,payee.party_name,
Get_Employee_Full_Name(pmt.employee_person_id,pmt.payee_party_id,payee.party_name)) payee_party_name, -- payee party name
/*
* Fix for bug 5466979:
*
* Payee name is always the same as the payee party name.
*/
DECODE(pmt.employee_person_id,
NULL,payee.party_name,
Get_Employee_Full_Name(pmt.employee_person_id,pmt.payee_party_id,payee.party_name)) payee_name, --payee name
DECODE( -- payee alternate name
payee.party_type,
'ORGANIZATION',
payee.organization_name_phonetic,
'PERSON',
TRIM(payee.person_first_name_phonetic
|| ' '
|| payee.person_last_name_phonetic)
),
/* PAYEE ADDRESS */
payee_addr.add_line1, -- payee add line1
payee_addr.add_line2, -- payee add line2
payee_addr.add_line3, -- payee add line3
payee_addr.add_line4, -- payee add line4
payee_addr.city, -- payee city
payee_addr.county, -- payee county
payee_addr.province, -- payee province
payee_addr.state, -- payee state -- 40
payee_addr.country, -- payee country
payee_addr.postal_code, -- payee postal code
payee_addr.add_concat, -- payee address concat
/*
* Fix for bug 5466979:
*
* Beneficiary name should be the same as the payee party name
* except when beneficiary party is not null.
*
* If beneficiary party is not null, the beneficiary name
* should be the hz_parties.party_name corresponding to the
* beneficiary_party.
*/
DECODE (pmt.beneficiary_party,
NULL,
payee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=pmt.beneficiary_party
)
), -- beneficiary name
payee.attribute_category, -- payee party attr category
payee_site.attribute_category, -- payee supplier site attr category
payee_site.party_site_name, -- payee supplier site name
/* VENDOR RELATED */
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
/* PAYEE SPECIAL FIELDS */
IBY_FD_EXTRACT_GEN_PVT. -- payee tax registration number
Get_Payee_TaxRegistration
(vendor.vendor_id,pmt.supplier_site_id),
/*
* Fix for bug 5468251:
*
* Use AP_SUPPLIERS.NUM_1099 for the payee
* LE registration number.
*/
/*
* Fix for bug 5475920:
*
* Payments can be made for employees or suppliers.
*
* For employees, the payee le reg num (i.e., taxpayer id)
* is stored in PER_PEOPLE_X.
*
* For suppliers, the payee le reg num (i.e., taxpayer id)
* is stored in AP_SUPPLIERS.
*
* Switch between these two tables depending upon payee type
* to get the payee le reg num
*/
/*
* Fix for bug 5501968:
*
* In R12, Federal supports thid party payments i.e., payments
* to third parties who may not be employees / suppliers.
*
* For this reason, we cannot assume that tax payer id for
* all non-employees will be stored in AP_SUPPLIERS.
*
* However, we know that all third parties / suppliers will
* have a row in HZ_PARTIES. Therefore, pick up the taxpayer
* id from HZ_PARTIES as this always works.
*/
/*
* Fix for bug 5501968:
*
* In R12, Federal supports thid party payments i.e., payments
*/
decode(upper(vendor.vendor_type_lookup_code),
'EMPLOYEE', per.national_identifier,
'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
/* PAYEE BANK */
eba.bank_name, -- payee bank name
eba.bank_number, -- payee bank number
eba.branch_number, -- payee bank branch number
eba.bank_branch_name, -- payee bank branch name
/*
* 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
/* REMITTANCE ADVICE RELATED */
remit_advice.remit_advice_delivery_method, -- remittance advice delv method
'', -- remittance advice email
'', -- remittance advice fax
/* DELIVERY CHANNEL RELATED */
deliv.format_value,
/* DECLARATION REPORT RELATED */
prof.declaration_curr_fx_rate_type,
'', -- declaration format
/* PROFILE RELATED */
prof.payment_profile_name, -- account profile name -- 80
prof.system_profile_name, -- system profile name
/* PAYMENT REASON */
pmt_reason.format_value,
/* BANK INSTRUCTION */
bank_instr1.format_value,
bank_instr2.format_value,
/* LE */
le.name,
/* RFC */
rfc_ca.class_code,
/* REQUEST */
payreq.call_app_pay_service_req_code,-- ppr name
fnd_app.application_name, -- source product -- 88
/*TPP-Start*/
invpayee.party_name inv_payee_name,
null inv_payee_address1,
null inv_payee_address2,
null inv_payee_address3,
null inv_payee_address4,
null inv_payee_city,
null inv_payee_postal_code,
null inv_payee_state,
null inv_payee_province,
null inv_payee_county,
null inv_payee_country,
invpayee.party_name inv_payee_party_name,
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) inv_payee_le_reg_num,
IBY_FD_EXTRACT_GEN_PVT.
Get_Payee_TaxRegistration (invvendor.vendor_id,pmt.inv_supplier_site_id)
inv_payee_tax_reg_num,
null inv_payee_address_concat,
DECODE (pmt.inv_beneficiary_party,
NULL,
invpayee.party_name,
(SELECT
benef_party.party_name
FROM
HZ_PARTIES benef_party
WHERE
benef_party.party_id=pmt.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,
null ext_bnk_acct_ownr_inv_prty_id,
null ext_bnk_branch_inv_prty_id,
null ext_bnk_acct_ownr_inv_prty_nme,
invpayee.attribute_category inv_payee_party_attr_cat,
invvendor.attribute_category inv_payee_supplier_attr_cat,
invpayee_site.attribute_category inv_payee_spplr_site_attr_cat,
invpayee_site.party_site_name 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*/
FROM
/* Payment related */
IBY_PAYMENTS_ALL pmt,
/* Profile related */
IBY_PAYMENT_PROFILES prof,
/* Payer */
XLE_FIRSTPARTY_INFORMATION_V payer,
HZ_PARTIES payer_party,
/* Payer bank */
CE_BANK_ACCOUNTS iba,
CE_BANK_BRANCHES_V iba_bnk_branch,
HZ_PARTY_SITES iba_branch_party_site,
/* Payee */
HZ_PARTIES payee,
HZ_PARTIES invpayee,
/* Vendor related */
AP_SUPPLIERS vendor,
AP_SUPPLIER_SITES_ALL vendor_site,
/* Inv Vendor related */
AP_SUPPLIERS invvendor,
AP_SUPPLIER_SITES_ALL invvendor_site,
/* Employee related */
PER_PEOPLE_X per,
/* Payee site */
HZ_PARTY_SITES payee_site,
HZ_PARTY_SITES invpayee_site,
/* Payee bank */
IBY_EXT_BANK_ACCOUNTS_V eba,
IBY_EXT_BANK_ACCOUNTS ext_ba_table,
CE_BANK_BRANCHES_V eba_bank_branch,
/* Remit advice related */
IBY_REMIT_ADVICE_SETUP remit_advice,
/* Payment service request related */
IBY_PAY_SERVICE_REQUESTS payreq,
FND_APPLICATION_ALL_VIEW fnd_app,
/* Delivery channel related */
IBY_DELIVERY_CHANNELS_VL deliv,
/* Payment reason related */
IBY_PAYMENT_REASONS_VL pmt_reason,
/* Bank instruction related */
IBY_BANK_INSTRUCTIONS_VL bank_instr1,
IBY_BANK_INSTRUCTIONS_VL bank_instr2,
/* Legal Entity related */
xle_entity_profiles le,
/* RFC */
HZ_CODE_ASSIGNMENTS rfc_ca,
/*
* Fix for bug 5928084:
*
* Derive address of employee type payees using
* PER_ADDRESSES or HR_LOCATIONS.
*
* The select statement below dynamically creates the payee
* address table. The dynamically created table is named
* as payee_add. Columns from this table are referenced in the
* outer select.
*
* The payee address can be either a
* - supplier address, or
* - employee address
*
* We decide whether we should pick up supplier addresses or
* employee addresses using the 'address source' field on the
* payment.
*
* 'address source' can contain two values
* - TCA: supplier address
* - HR : employee address
*
* Supplier addresses are present in TCA and are located using
* remit to location id column on the payment.
*
* Employee addresses can be in either
* - PER_ADDRESSES (home addresses)
* - HR_LOCATIONS (office addresses)
* We use the 'employee address code' field on the payment to
* decide which table to use.
*
* The select below uses decode clause to perform the
* if else logic to retrieve the address from the right
* column.
*
* Roughly, the decode clauses are used to effect the following
* logic -
*
* for each add field:
* if (pmt.address_source = 'TCA')
* {
* -- get the address field from HZ_LOCATIONS --
* }
* else
* {
* -- this is an employee address --
* if (pmt.employee_address_code = 'HOME')
* {
* -- get the address field from PER_ADDRESSES --
*
* The select logic for this has been lifted from
* AP's expense report import program.
* }
* else
* {
* -- get the address field from HR_LOCATIONS --
*
* The select logic for this has been lifted from
* /patch/115/sql/apwexptb.pls.
* }
* }
*/
(
SELECT
/* payee add line1 */
DECODE(
pmt.address_source,
-- supplier address line 1
'TCA', payee_loc.address1,
-- employee add line 1
DECODE
(
pmt.employee_address_code,
-- employee home addr line 1
'HOME', per_addr.address_line1,
-- employee office addr line 1
'OFFICE',per_loc.address_line_1,
-- employee provisional addr line 1
'PROVISIONAL', per_addr_prov.address_line1,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1,
per_addr.address_line1)
)
) add_line1,
/* payee add line2 */
DECODE(
pmt.address_source,
-- supplier address line 2
'TCA', payee_loc.address2,
-- employee add line 2
DECODE
(
pmt.employee_address_code,
-- employee home addr line 2
'HOME', per_addr.address_line2,
-- employee office addr line 2
'OFFICE',per_loc.address_line_2,
-- employee provisional addr line 2
'PROVISIONAL', per_addr_prov.address_line2,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_2,
per_addr.address_line2)
)
) add_line2,
/* payee add line3 */
DECODE(
pmt.address_source,
-- supplier address line 3
'TCA', payee_loc.address3,
-- employee add line 3
DECODE
(
pmt.employee_address_code,
-- employee home addr line 3
'HOME', per_addr.address_line3,
-- employee office addr line 3
'OFFICE',per_loc.address_line_3,
-- employee provisional addr line 3
'PROVISIONAL', per_addr_prov.address_line3,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_3,
per_addr.address_line3)
)
) add_line3,
/* payee add line4 */
DECODE(
pmt.address_source,
-- supplier address line 4
'TCA', payee_loc.address4,
-- employee home/office addr line 4 (not available)
null
) add_line4,
/* payee city */
DECODE(
pmt.address_source,
-- supplier city
'TCA', payee_loc.city,
-- employee city
DECODE
(
pmt.employee_address_code,
-- employee home city
'HOME', per_addr.town_or_city,
-- employee office city
'OFFICE', per_loc.town_or_city,
-- employee provisional city
'PROVISIONAL', per_addr_prov.town_or_city,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.town_or_city,
per_addr.town_or_city)
)
) city,
/* payee county */
DECODE(
pmt.address_source,
-- supplier county
'TCA', payee_loc.county,
-- employee county
(
DECODE(
pmt.employee_address_code,
-- employee home county
'HOME',
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''),
-- employee office county
'OFFICE',
DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
-- employee provisional county
'PROVISIONAL',
DECODE(
per_addr_prov.style,
'US', NVL(per_addr_prov.region_1, ''),
'US_GLB', NVL(per_addr_prov.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
''),
''),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(
per_loc.style,
'US', NVL(per_loc.region_1, ''),
'US_GLB', NVL(per_loc.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
''),
''),
DECODE(
per_addr.style,
'US', NVL(per_addr.region_1, ''),
'US_GLB', NVL(per_addr.region_1, ''),
'IE', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'IE_GLB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
'GB', NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
''),
''))
)
)
) county,
/* payee province */
DECODE(
pmt.address_source,
-- supplier province
'TCA', payee_loc.province,
-- employee province
(
DECODE(
pmt.employee_address_code,
-- employee home province
'HOME',
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
),
-- employee office province
'OFFICE',
DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
-- employee provisional province
'PROVISIONAL',
DECODE(per_addr_prov.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr_prov.region_1, ''),
'CA_GLB', NVL(per_addr_prov.region_1, ''),
'JP', NVL(per_addr_prov.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr_prov.style,
per_addr_prov.region_1),
'')
),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_loc.region_1, ''),
'CA_GLB', NVL(per_loc.region_1, ''),
'JP', NVL(per_loc.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_loc.style,
per_loc.region_1),
'')
),
DECODE(per_addr.style,
'US', '',
'US_GLB', '',
'IE', '',
'IE_GLB', '',
'GB', '',
'CA', NVL(per_addr.region_1, ''),
'CA_GLB', NVL(per_addr.region_1, ''),
'JP', NVL(per_addr.region_1, ''),
NVL(ap_web_db_expline_pkg.
getcountyprovince(
per_addr.style,
per_addr.region_1),
'')
))
)
)
) province,
/* payee state */
DECODE(
pmt.address_source,
-- supplier state
'TCA', payee_loc.state,
-- employee state
(
DECODE(
pmt.employee_address_code,
-- employee home state
'HOME',
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')),
-- employee office state
'OFFICE',
DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
-- employee provisional state
'PROVISIONAL',
DECODE(per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')),
--address code not specified
decode(per_addr.address_id,
NULL,DECODE(per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')),
DECODE(per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')))
)
)
) state,
/* payee country */
DECODE(
pmt.address_source,
-- supplier country
'TCA', payee_loc.country,
-- employee country
(
DECODE(
pmt.employee_address_code,
-- employee home country
'HOME', per_addr.country,
-- employee office country
'OFFICE',per_loc.country,
-- employee provisional country
'PROVISIONAL', per_addr_prov.country,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.country,
per_addr.country
)
)
)
) country,
/* payee postal code */
DECODE(
pmt.address_source,
-- supplier postal code
'TCA', payee_loc.postal_code,
-- employee postal code
(
DECODE(
pmt.employee_address_code,
-- employee home postal code
'HOME', per_addr.postal_code,
-- employee office postal code
'OFFICE',per_loc.postal_code,
-- employee provisional postal code
'PROVISIONAL', per_addr_prov.postal_code,
--address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.postal_code,
per_addr.postal_code
)
)
)
) postal_code,
/* payee address concat */
DECODE(
pmt.address_source,
-- supplier address concat
'TCA',
payee_loc.address1
|| ', '
|| payee_loc.address2
|| ', '
|| payee_loc.address3
|| ', '
|| payee_loc.city
|| ', '
|| payee_loc.state
|| ', '
|| payee_loc.country
|| ', '
|| payee_loc.postal_code,
-- employee address concat
(
DECODE(
pmt.employee_address_code,
-- employee home address concat
'HOME',
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code,
-- employee office address concat
'OFFICE',
per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
-- employee provisional address concat
'PROVISIONAL',
per_addr_prov.address_line1
|| ', '
|| per_addr_prov.address_line2
|| ', '
|| per_addr_prov.address_line3
|| ', '
|| per_addr_prov.town_or_city
|| ', '
|| DECODE(
per_addr_prov.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr_prov.region_2, '')
)
|| ', '
|| per_addr_prov.country
|| ', '
|| per_addr_prov.postal_code,
-- address code not specified
DECODE (per_addr.address_id,
NULL, per_loc.address_line_1
|| ', '
|| per_loc.address_line_2
|| ', '
|| per_loc.address_line_3
|| ', '
|| per_loc.town_or_city
|| ', '
|| DECODE(
per_loc.style,
'CA', '',
'CA_GLB', '',
NVL(per_loc.region_2, '')
)
|| ', '
|| per_loc.country
|| ', '
|| per_loc.postal_code,
per_addr.address_line1
|| ', '
|| per_addr.address_line2
|| ', '
|| per_addr.address_line3
|| ', '
|| per_addr.town_or_city
|| ', '
|| DECODE(
per_addr.style,
'CA', '',
'CA_GLB', '',
NVL(per_addr.region_2, '')
)
|| ', '
|| per_addr.country
|| ', '
|| per_addr.postal_code)
)
)
) add_concat
FROM
IBY_PAYMENTS_ALL pmt,
/* Employee address related */
HR_LOCATIONS per_loc,
PER_ADDRESSES per_addr,
PER_ALL_ASSIGNMENTS_F per_assgn,
PER_ADDRESSES per_addr_prov,
/* Supplier address related */
HZ_LOCATIONS payee_loc
WHERE
pmt.payment_id = p_payment_id
AND pmt.employee_person_id = per_addr.person_id(+)
AND per_addr.primary_flag(+) = 'Y'
AND SYSDATE BETWEEN
per_addr.date_from(+)
AND NVL(per_addr.date_to(+), SYSDATE+1)
AND pmt.employee_person_id = per_addr_prov.person_id(+)
AND per_addr_prov.address_type(+) = 'M'
AND SYSDATE BETWEEN
per_addr_prov.date_from(+)
AND NVL(per_addr_prov.date_to(+), SYSDATE+1)
AND pmt.employee_person_id = per_assgn.person_id(+)
AND per_assgn.location_id = per_loc.location_id(+)
AND per_assgn.primary_flag(+) = 'Y'
AND (TRUNC(SYSDATE) BETWEEN
per_assgn.effective_start_date(+)
AND per_assgn.effective_end_date(+)
)
AND pmt.remit_to_location_id = payee_loc.location_id(+)
) payee_addr
WHERE
/* payment related */
pmt.payment_id = p_payment_id
/* payer */
AND pmt.legal_entity_id = payer.legal_entity_id
AND payer.party_id = payer_party.party_id
/* payer bank */
AND pmt.internal_bank_account_id = 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(+)
/* payee */
AND pmt.payee_party_id = payee.party_id
/* payee site */
AND pmt.party_site_id = payee_site.party_site_id(+)
/* inv payee */
AND pmt.inv_payee_party_id = invpayee.party_id(+)
/* inv payee site */
AND pmt.inv_party_site_id = invpayee_site.party_site_id(+)
/* vendor related */
AND pmt.payee_party_id = vendor.party_id(+)
AND pmt.supplier_site_id = vendor_site.vendor_site_id(+)
AND vendor.vendor_id = vendor_site.vendor_id /*Bug 7323072*/
/* inv vendor related */
AND pmt.inv_payee_party_id = invvendor.party_id(+)
AND pmt.inv_supplier_site_id = invvendor_site.vendor_site_id(+)
AND nvl(vendor.vendor_id,-99) = nvl(vendor_site.vendor_id,-99) /*Bug 7323072*/
/* employee related */
AND pmt.employee_person_id = per.person_id(+)
/* payee bank */
AND pmt.external_bank_account_id = eba.ext_bank_account_id(+)
AND eba.bank_party_id = eba_bank_branch.bank_party_id(+)
AND eba.branch_party_id = eba_bank_branch.branch_party_id(+)
/*
* Fix for bug 5658982:
*
* We want to use outer join with the eba table because the
* ext bank account id is optional on the payment.
*
* For the same reason, we must use outer join with
* ext_ba_table table also.
*/
AND ext_ba_table.ext_bank_account_id(+) = eba.ext_bank_account_id
/* profile and remit advice */
AND pmt.payment_profile_id = prof.payment_profile_id
AND prof.system_profile_code = remit_advice.system_profile_code
/* payment service request related */
AND payreq.payment_service_request_id = pmt.payment_service_request_id
AND fnd_app.application_id = payreq.calling_app_id
/* delivery channel related */
AND pmt.delivery_channel_code = deliv.delivery_channel_code(+)
/* payment reason */
AND pmt.payment_reason_code = pmt_reason.payment_reason_code(+)
/* bank instruction */
AND prof.bank_instruction1_code = bank_instr1.bank_instruction_code(+)
AND prof.bank_instruction2_code = bank_instr2.bank_instruction_code(+)
/* Org related */
AND pmt.org_id = le.legal_entity_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
;
* Update the IBY_PAYMENTS_ALL table using the retrieved
* audit information.
*/
insertAuditData(l_pmtAuditTab);
| 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_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;