[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*-- selection criteria --*/
p_payment_profile_id IN IBY_PAYMENTS_ALL.
payment_profile_id%TYPE,
p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.
calling_app_id%TYPE,
p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
call_app_pay_service_req_code
%TYPE,
p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
payment_service_request_id
%TYPE,
p_internal_bank_account_id IN IBY_PAYMENTS_ALL.
internal_bank_account_id%TYPE,
p_payment_currency IN IBY_PAYMENTS_ALL.
payment_currency_code%TYPE,
p_le_id IN IBY_PAYMENTS_ALL.
legal_entity_id%TYPE,
p_org_id IN IBY_PAYMENTS_ALL.org_id%TYPE,
p_org_type IN IBY_PAYMENTS_ALL.org_type%TYPE,
p_payment_from_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
p_payment_to_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
/*-- single payments / batch flow identifier --*/
p_single_pmt_flow_flag IN VARCHAR2 DEFAULT 'N',
/*-- out params --*/
x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME
|| '.createPaymentInstructions';
* PLSQL table is used in inserting a row into the IBY_PAY_INSTRUCTIONS_ALL
* table.
*
* Since the IBY_PAY_INSTRUCTIONS_ALL table does not contain a payment id,
* a separate data structure is needed to keep track of the payments
* that are part of a payment instruction. This information is tracked
* in the pmtsInInstrTabType table. The rows in pmtsInInstrTabType are
* used to update the rows in IBY_PAYMENTS_ALL table with payment instruction
* ids.
*
* l_instrTab l_pmtsInInstrTab
* (insert into IBY_PAY_INSTRUCTIONS_ALL) (update IBY_PAYMENTS_ALL)
* /--------------------------------------\ /-------------\
* |Payment |Payment|..|Instr |Payment|..| |Payment |Pmt |
* |Instr Id|Profile|..|Status |Count |..| |Instr Id|Id |
* | |Id |..| | |..| | | |
* |--------------------------------------| |-------------|
* | 4000 | 10| |CREATED| 3| | | 4000 | 501|
* | | | | | | | | 4000 | 504|
* | | | | | | | | 4000 | 505|
* |--------|-------|--|-------|-------|--| |--------|----|
* | 4001 | 12| |CREATED| 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.
*/
l_pmtInstrRec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
print_debuginfo(l_module_name, 'Invalid selection '
|| 'criteria provided; org id has been provided '
* available payments) in chunks; add only non-null selection
* selection criteria:
*
* Some of these criteria can be null, as they are optional.
* In such a case, do not put that criteria in the WHERE
* clause (because then you will not get any payments).
* Instead, build up the SQL statement in chunks using
* only the non-null selection parameters, and use this
* chunk in the WHERE clause.
*
* We want to pick up all payments that match the provided
* selection criteria. If all selection criteria are set
* to 'null', it means that user wants to select all pending
* payments.
*
* Because the WHERE clause is dynamic, we have to use
* a dynamic cursor (REF CURSOR).
*/
/*
* Important Technical Note:
* -------------------------
* This SELECT statement uses SKIP LOCKED syntax.
* This is an undocumented feature of Oracle, that
* will only select unlocked rows.
*
* When there are multiple instances of the payment
* instruction program running concurrently, we need to
* make sure that no two instances are operating on the
* same rows. So we lock the rows that each instance
* picks up by using SELECT .. FOR UPDATE syntax.
*
* Now, suppose the second instance of the payment instruction
* creation program is invoked concurrently, it will also
* attempt to pick up all rows that match the provided
* selection criteria. If even one row in the selection
* maps to a row that has already been picked up by the
* first instance of the payment instruction creation
* program, then the second instance has to wait till the
* first instance completes (because the rows were locked).
*
* We want the second instance to only pick up the rows that
* were not already selected by the first instance (i.e.,
* pick up only unlocked rows). This is accomplished by the
* SELECT .. FOR UPDATE SKIP LOCKED syntax.
*
* With this approach, it is possible to have multiple
* instances of the payment instruction creation running
* concurrently and each operating on it's own data.
*
* Though the SKIP LOCKED feature is undocumented, we have
* received permission from the performance team to
* use it.
*/
/*
* Note I:
* Since this is a 'select for update', we cannot select
* from views. We have to use the underlying base tables
* instead. That's why we select from the base tables
* for the payment profile.
*/
/*
* Note II:
* Debugging this select can be tricky. Sometimes the
* select will return no rows even when the matching rows
* are present in the table; this is because of the SKIP
* locked even if they match the selection criteria.
*
* To debug the select statement, comment out the skip
* locked syntax.
*/
l_cursor_stmt :=
'SELECT '
|| 'prq.call_app_pay_service_req_code, '
|| 'prq.calling_app_id, '
|| 'prq.payment_service_request_id, '
|| 'pmts.payment_id, '
|| 'pmts.internal_bank_account_id, '
|| 'pmts.payment_profile_id, '
|| 'pmts.org_id, '
|| 'pmts.org_type, '
|| 'pmts.legal_entity_id, '
|| 'pmts.payment_currency_code, '
|| 'pmts.payment_amount, '
|| 'pmts.payment_date, '
|| 'pmts.payment_function, '
|| 'pmts.payment_reason_code, '
|| 'pmts.payment_reason_comments, '
|| 'NVL(LENGTH(pmts.payment_details), 0), '
|| 'pmts.bill_payable_flag , '
|| 'pmts.payment_service_request_id, '
|| 'rfc_ca.class_code, '
|| 'pmts.payment_method_code, '
|| 'icr.group_by_payment_date, '
|| 'icr.group_by_payment_currency, '
|| 'icr.group_by_max_payments_flag, '
|| 'icr.max_payments_per_instruction, '
|| 'icr.group_by_internal_bank_account, '
|| 'icr.group_by_max_instruction_flag, '
|| 'icr.max_amount_per_instr_value, '
|| 'icr.max_amount_per_instr_curr_code, '
|| 'icr.max_amount_fx_rate_type, '
|| 'icr.group_by_pay_service_request, '
|| 'icr.group_by_legal_entity, '
|| 'icr.group_by_organization, '
|| 'icr.group_by_payment_function, '
|| 'icr.group_by_payment_reason, '
|| 'icr.group_by_bill_payable, '
|| 'icr.group_by_pay_service_request, '
|| 'icr.group_by_rfc , '
|| 'icr.group_by_payment_method '
|| 'FROM '
|| 'IBY_PAYMENTS_SEC_V pmts, '
|| 'IBY_INSTR_CREATION_RULES icr, '
|| 'IBY_PAY_SERVICE_REQUESTS prq, '
|| 'IBY_SYS_PMT_PROFILES_B sppf, '
|| 'IBY_ACCT_PMT_PROFILES_B appf, '
|| 'HZ_PARTIES branch_party, '
|| 'HZ_CODE_ASSIGNMENTS rfc_ca, '
|| 'CE_BANK_ACCOUNTS bank_accts '
|| 'WHERE '
|| 'pmts.payment_status = :pmt_status AND '
|| 'sppf.processing_type = :processing_type AND '
|| 'pmts.payment_service_request_id = '
|| 'prq.payment_service_request_id AND '
|| 'prq.payment_service_request_status = :req_status AND '
|| 'pmts.payment_profile_id = appf.payment_profile_id AND '
|| 'sppf.system_profile_code = '
|| 'appf.system_profile_code(+) AND '
|| 'appf.system_profile_code = icr.system_profile_code(+) AND '
|| 'rfc_ca.owner_table_name(+) = :table_name AND '
|| 'rfc_ca.class_category(+) = :category AND '
|| 'rfc_ca.owner_table_id(+) = branch_party.party_id AND '
|| 'branch_party.party_id = bank_accts.bank_branch_id AND '
|| 'bank_accts.bank_account_id = '
|| 'pmts.internal_bank_account_id '
|| NVL (l_sql_chunk, 'AND 1=1 ')
|| 'ORDER BY '
|| 'pmts.payment_profile_id, ' -- |
|| 'pmts.payment_date, ' -- |
|| 'pmts.payment_currency_code, ' -- |
|| 'pmts.internal_bank_account_id, ' -- | Ensure that the grouping
|| 'pmts.legal_entity_id, ' -- | logic below follows the
|| 'pmts.org_id, ' -- | same order as this
|| 'pmts.org_type, ' -- | order by clause; else,
|| 'FOR UPDATE of pmts.payment_id, prq.payment_service_request_id SKIP LOCKED '
;
/* Modified the for update clause for the bug 7261651*/
/*
* Print the cursor statement for debug purposes.
*/
print_debuginfo(l_module_name, 'Dynamic cursor statement: ');
|| 'provided selection criteria: '
|| l_instrGrpCriTab.COUNT
);
|| 'of the selected tables were locked causing '
|| 'the select to exit due to NOWAIT clause. '
|| 'You might want to try payment instruction '
|| 'creation again later.'
);
* We have just fetched a new payment from the selection.
* We will either insert this payment into a new instruction or
* we will be inserting this payment into the currently running
* payment instruction.
*
* In either case, we need to insert this pmt into an instruction.
* So pre-populate the instruction record with attributes of
* this payment. This is because the instruction takes on the
* attributes of it's constituent payments.
*
* 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_pmtInstrRec.payment_profile_id := curr_profile_id;
* selected grouping rules.
*
* It is necessary to pre-fill user defined grouping
* attributes before the grouping rules are triggered
* because we don't know which user defined grouping rules
* are going to get triggered first, and once a rule is
* triggered all rules below it are skipped. So it is too
* late to populate grouping attributes within the grouping
* rule itself.
*/
IF (l_int_bank_acct_flag = 'Y') THEN
l_pmtInstrRec.internal_bank_account_id := curr_int_bank_acct_id;
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
true, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
false, l_instr_id,
l_pmtsInInstrTab, l_pmtsInInstrRec,
l_pmts_in_instr_count, l_instr_amount);
* We just finished inserting a payment into an
* instruction. Therefore, the instruction id
* is available now.
*
* For each payment in this instruction, store the
* payment function and org, if unique.
*
* This information will be used by the UI in
* restricting user access.
*/
deriveDistinctAccessTypsForIns(
l_instr_id,
curr_pmt_function,
curr_org_id,
curr_org_type,
l_pmtFxAccessTypesTab,
l_orgAccessTypesTab
);
* Similarly, update the payments table by providing a
* payment instruction id to each selected payment.
*/
performDBUpdates(l_pmtInstrTab, l_pmtsInInstrTab, l_docErrorTab,
l_errTokenTab, l_profile_map, x_return_status);
* Insert the distinct payment functions and orgs that
* were found in the created payment instructions. These
* will be used for limiting UI access to users.
*/
insertDistinctAccessTypsForIns(l_pmtFxAccessTypesTab,
l_orgAccessTypesTab);
* have been inserted / updated. This is because you cannot
* 'rollback' a business event once raised.
*/
raiseBizEvents(l_pmtInstrTab);
* Update the payments table with the generated
* document sequence numbers.
*/
updatePmtsWithSeqNum(l_sort_pmt_tab);
* Update the payments table with the generated
* payment reference numbers.
*/
updatePmtsWithPmtRef(l_sort_pmt_tab);
| insertPmtIntoInstruction
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertPmtIntoInstruction(
x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
p_newPmtInstrFlag IN BOOLEAN,
x_currentPmtInstrId IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL.
payment_instruction_id%TYPE,
x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
x_pmtsInPmtInstrRec IN OUT NOCOPY pmtsInPmtInstrRecType,
x_pmtsInPmtInstrCount IN OUT NOCOPY NUMBER,
x_instrAmount IN OUT NOCOPY NUMBER
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPmtIntoInstruction';
* 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_newPmtInstrFlag = true) THEN
/*
* This is a new payment; Get an id for this payment
* incoming payment as a constituent, and insert this
* record into the PLSQL payments table.
*/
x_pmtInstrRec.payment_instruction_id := x_currentPmtInstrId;
print_debuginfo(l_module_name, 'Inserted payment: '
|| x_pmtsInPmtInstrRec.payment_id || ' into new payment '
|| 'instruction: '
|| x_currentPmtInstrId);
* instruction to this payment, and insert the payment
* into the payments array.
*/
x_pmtsInPmtInstrRec.pay_instr_id := x_pmtInstrRec.
payment_instruction_id;
* intitialize it by inserting a dummy record. This dummy
* record will get overwritten below.
*/
IF (x_pmtInstrTab.COUNT = 0) THEN
getNextPaymentInstructionID(x_currentPmtInstrId);
* Insert the first record into the table. This
* is a dummy record.
*/
x_pmtInstrTab(x_pmtInstrTab.COUNT + 1) := x_pmtInstrRec;
* PLSQL payment instructions table with the updated record.
*/
x_pmtInstrTab(x_pmtInstrTab.COUNT) := x_pmtInstrRec;
print_debuginfo(l_module_name, 'Inserted payment: '
|| x_pmtsInPmtInstrRec.payment_id
|| ' into existing payment instruction: '
|| x_currentPmtInstrId);
* instruction to this payment, and insert the payment
* into the payments array.
*/
x_pmtsInPmtInstrRec.pay_instr_id := x_pmtInstrRec.
payment_instruction_id;
END insertPmtIntoInstruction;
| insertPaymentInstructions
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertPaymentInstructions(
p_payInstrTab IN pmtInstrTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPaymentInstructions';
TYPE t_last_updated_by IS TABLE OF
IBY_PAY_INSTRUCTIONS_ALL.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_date IS TABLE OF
IBY_PAY_INSTRUCTIONS_ALL.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE t_last_update_login IS TABLE OF
IBY_PAY_INSTRUCTIONS_ALL.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;
|| 'instructions were found to update '
|| 'IBY_PAY_INSTRUCTIONS_ALL table.'
|| ' Possible data corruption issue.');
l_last_updated_by(i)
:= NVL(p_payInstrTab(i).last_updated_by, fnd_global.user_id);
l_last_update_date(i)
:= NVL(p_payInstrTab(i).last_update_date, sysdate);
l_last_update_login(i)
:= NVL(p_payInstrTab(i).last_update_login, fnd_global.user_id);
INSERT INTO IBY_PAY_INSTRUCTIONS_ALL
(
payment_instruction_id,
payment_profile_id,
process_type,
payment_instruction_status,
payments_complete_code,
generate_sep_remit_advice_flag,
remittance_advice_created_flag,
regulatory_report_created_flag,
bill_payable_flag,
legal_entity_id,
payment_count,
positive_pay_file_created_flag,
print_instruction_immed_flag,
transmit_instr_immed_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
internal_bank_account_id,
pay_admin_assigned_ref_code,
transmission_date,
acknowledgement_date,
comments,
bank_assigned_ref_code,
org_id,
org_type,
payment_date,
payment_currency_code,
payment_service_request_id,
payment_function,
payment_reason_code,
rfc_identifier,
payment_method_code,
payment_reason_comments,
payment_document_id,
printer_name,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
(
l_payment_instruction_id(i),
l_payment_profile_id(i),
l_process_type(i),
l_payment_instruction_status(i),
l_payments_complete_code(i),
l_gen_sep_remit_advice_flag(i),
l_remit_advice_created_flag(i),
l_regul_rpt_created_flag(i),
l_bill_payable_flag(i),
l_legal_entity_id(i),
l_payment_count(i),
l_pos_pay_file_created_flag(i),
l_print_instr_immed_flag(i),
l_transmit_instr_immed_flag(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),
l_internal_bank_account_id(i),
l_pay_admin_assigned_ref_code(i),
l_transmission_date(i),
l_acknowledgement_date(i),
l_comments(i),
l_bank_assigned_ref_code(i),
l_org_id(i),
l_org_type(i),
l_payment_date(i),
l_payment_currency_code(i),
l_payment_service_request_id(i),
l_payment_function(i),
l_payment_reason_code(i),
l_rfc_identifier(i),
l_pmt_method_code(i),
l_payment_reason_comments(i),
l_payment_document_id(i),
l_printer_name(i),
l_attribute_category(i),
l_attribute1(i),
l_attribute2(i),
l_attribute3(i),
l_attribute4(i),
l_attribute5(i),
l_attribute6(i),
l_attribute7(i),
l_attribute8(i),
l_attribute9(i),
l_attribute10(i),
l_attribute11(i),
l_attribute12(i),
l_attribute13(i),
l_attribute14(i),
l_attribute15(i)
)
;
END insertPaymentInstructions;
| updatePaymentInstructions
|
| PURPOSE:
| Performs an update of all created instructions from PLSQL
| table into IBY_PAY_INSTRUCTIONS_ALL table.
|
| The created instructions have already been inserted into
| IBY_PAY_INSTRUCTIONS_ALL after grouping (and before validation).
| So we only need to update certain fields of the instruction
| that have been changed after the grouping was performed.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePaymentInstructions(
p_payInstrTab IN pmtInstrTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePaymentInstructions';
|| ' were found to update IBY_PAY_INSTRUCTIONS_ALL table.');
UPDATE
IBY_PAY_INSTRUCTIONS_ALL
SET
payment_instruction_status =
p_payInstrTab(i).payment_instruction_status,
generate_sep_remit_advice_flag =
p_payInstrTab(i).generate_sep_remit_advice_flag
WHERE
payment_instruction_id = p_payInstrTab(i).payment_instruction_id
;
END updatePaymentInstructions;
SELECT
IBY_PAY_INSTRUCTIONS_ALL_S.NEXTVAL
INTO
x_pmtInstrID
FROM
DUAL
;
| updatePmtsWithInstructionID
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePmtsWithInstructionID(
p_pmtsInPayInstTab IN pmtsInPmtInstrTabType
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePmtsWithInstructionID';
|| 'payments provided to update '
|| 'IBY_PAYMENTS_ALL table. Possible data '
|| 'corruption issue.');
* Update the payments. We cannot use bulk update here
* because the bulk update syntax does not allow us to
* reference individual fields of the PL/SQL record.
*
* TBD: Is there any way to optimize this update?
*/
FOR i in p_pmtsInPayInstTab.FIRST..p_pmtsInPayInstTab.LAST LOOP
print_debuginfo(l_module_name, 'Instruction: '
|| p_pmtsInPayInstTab(i).pay_instr_id || ', payment: '
|| p_pmtsInPayInstTab(i).payment_id);
UPDATE
IBY_PAYMENTS_ALL
SET
payment_instruction_id = p_pmtsInPayInstTab(i).pay_instr_id,
paper_document_number = p_pmtsInPayInstTab(i).check_number,
payment_status = p_pmtsInPayInstTab(i).payment_status,
/*
* Fix for bug 5467767:
*
* The payer abbreviated agency code and payer
* federal employer number need to be populated
* on the payment as these are required by some
* formats.
*
* Populate them here because the functions that
* retrieve these values need the payment instruction
* id as an input param.
*/
payer_abbreviated_agency_code =
IBY_FD_EXTRACT_GEN_PVT.
Get_Abbreviated_Agency_Code(
p_pmtsInPayInstTab(i).pay_instr_id),
payer_federal_us_employer_id =
IBY_FD_EXTRACT_GEN_PVT.
Get_FEIN(
p_pmtsInPayInstTab(i).pay_instr_id)
WHERE
payment_id = p_pmtsInPayInstTab(i).payment_id
;
END updatePmtsWithInstructionID;
select_clause VARCHAR(4000);
SELECT sysprf.logical_grouping_mode
, sysprf.system_profile_code
INTO l_grouping_mode
, l_payment_profile_code
FROM IBY_SYS_PMT_PROFILES_B sysprf
, IBY_ACCT_PMT_PROFILES_B actprf
WHERE actprf.PAYMENT_PROFILE_ID = x_pmtInstrTab(i).payment_profile_id
AND actprf.SYSTEM_PROFILE_CODE = sysprf.system_profile_code;
SELECT PAYMENT_ID BULK COLLECT
INTO t_payment_id
FROM IBY_PAYMENTS_ALL
WHERE PAYMENT_INSTRUCTION_ID = l_payment_instruction_id;
UPDATE IBY_PAYMENTS_ALL
SET LOGICAL_GROUP_REFERENCE = t_logical_group_reference(k)
WHERE payment_id = t_payment_id(k) ;
UPDATE IBY_PAYMENTS_ALL
SET logical_group_reference = l_logical_group_reference
WHERE payment_instruction_id = l_payment_instruction_id;
SELECT nvl(group_by_legal_entity, 'N'),
nvl(group_by_payment_method, 'N'),
nvl(group_by_payment_date, 'N'),
nvl(group_by_internal_bank_account, 'N')
INTO l_group_by_legal_entity,
l_group_by_payment_method,
l_group_by_payment_date,
l_group_by_internal_bank_acct
FROM IBY_PMT_LOGICAL_GRP_RULES
WHERE SYSTEM_PROFILE_CODE = l_payment_profile_code;
select_clause := 'SELECT PAYMENT_ID
, legal_entity_id
, PAYMENT_METHOD_CODE
, PAYMENT_DATE
, INTERNAL_BANK_ACCOUNT_ID'
;
print_debuginfo(l_module_name, 'select_clause: '
|| select_clause);
EXECUTE IMMEDIATE select_clause
|| into_clause
|| from_clause
|| where_clause
|| order_clause;
EXECUTE IMMEDIATE select_clause
|| from_clause
|| where_clause
|| order_clause
BULK COLLECT INTO t_payment_id
, t_legal_entity_id
, t_payment_method_code
, t_payment_date
, t_internal_bank_account_id
;
UPDATE IBY_PAYMENTS_ALL
SET logical_group_reference = t_logical_group_reference(j)
WHERE payment_id = t_payment_id(j);
insertPaymentInstructions(x_pmtInstrTab);
updatePmtsWithInstructionID(x_pmtsInPmtInstrTab);
| performDBUpdates
|
| PURPOSE:
|
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performDBUpdates(
x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
trxnErrTokenTabType,
p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
* Update the payments table by providing a instruction id to
* each payment.
*/
updatePmtsWithInstructionID(x_pmtsInPmtInstrTab);
* Update individual payments with their document sequence numbers
* and payment references.
*/
updatePmtsWithSeqNumPmtRef(l_sorted_pmts_tab);
* table just before validations. Now update these
* payment instructions with any additional information.
*
* E.g., the payment instruction could have been failed because
* the document sequencing API call failed. Therefore, the
* payment instruction status needs to be updated.
*/
updatePaymentInstructions(x_pmtInstrTab);
IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_docErrorTab,
x_errTokenTab);
END performDBUpdates;
SELECT DISTINCT
val_asgn.validation_assignment_id,
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_PAY_INSTRUCTIONS_ALL pmt_instr,
IBY_PAYMENT_PROFILES prof,
IBY_TRANSMIT_CONFIGS_VL txconf,
IBY_TRANSMIT_PROTOCOLS_VL txproto,
IBY_PAYMENTS_SEC_V pmt
WHERE
pmt_instr.payment_instruction_id = p_instr_id
AND pmt.payment_instruction_id = pmt_instr.payment_instruction_id
AND val.validation_set_code = val_asgn.validation_set_code
AND val.validation_level_code = 'DISBURSEMENT_INSTRUCTION'
AND (val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_asgn.assignment_entity_id =
pmt_instr.internal_bank_account_id
OR val_asgn.val_assignment_entity_type = 'FORMAT'
AND val_asgn.assignment_entity_id =
prof.payment_format_code
OR val_asgn.val_assignment_entity_type = 'BANK'
AND val_asgn.assignment_entity_id =
prof.bepid
OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
AND val_asgn.assignment_entity_id =
txconf.transmit_protocol_code
OR val_asgn.val_assignment_entity_type = 'METHOD'
AND val_asgn.assignment_entity_id =
pmt.payment_method_code
)
AND pmt_instr.payment_profile_id = prof.payment_profile_id(+)
AND prof.transmit_configuration_id = txconf.transmit_configuration_id(+)
AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
AND NVL(val_asgn.inactive_date, sysdate+1) > sysdate
;
SELECT DISTINCT
val_asgn.validation_assignment_id,
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_PAY_INSTRUCTIONS_ALL pmt_instr,
IBY_PAYMENT_PROFILES prof,
IBY_TRANSMIT_CONFIGS_VL txconf,
IBY_TRANSMIT_PROTOCOLS_VL txproto,
IBY_TRANSACTION_ERRORS txerrors
WHERE
pmt_instr.payment_instruction_id = p_instr_id
AND val.validation_set_code = val_asgn.validation_set_code
AND val.validation_level_code = 'DISBURSEMENT_INSTRUCTION'
AND (val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
AND val_asgn.assignment_entity_id =
pmt_instr.internal_bank_account_id
OR val_asgn.val_assignment_entity_type = 'FORMAT'
AND val_asgn.assignment_entity_id =
prof.payment_format_code
OR val_asgn.val_assignment_entity_type = 'BANK'
AND val_asgn.assignment_entity_id =
prof.bepid
OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
AND val_asgn.assignment_entity_id =
txconf.transmit_protocol_code
)
AND pmt_instr.payment_profile_id = prof.payment_profile_id(+)
AND prof.transmit_configuration_id = txconf.transmit_configuration_id(+)
AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
AND NVL(val_asgn.inactive_date, sysdate+1) > sysdate
/*
* Fix for bug 5206725:
*
* The set of conditions below will filter out validation sets
* that have already been overridden by the user.
*/
AND txerrors.transaction_type = 'PAYMENT_INSTRUCTION'
AND txerrors.transaction_id = p_instr_id
AND txerrors.error_type = 'VALIDATION'
AND txerrors.validation_set_code = val.validation_set_code
AND txerrors.do_not_apply_error_flag = 'N'
;
* Update the payment instruction status if there
* were no errors.
*/
IF (x_docErrorTab.COUNT = 0) THEN
/*
* When a payment instruction enters this method,
* it will be in CREATION_ERROR status. If after validation,
* we find that no error messages were generated, then
* we should update the status of this payment instruction
* to CREATED.
*/
print_debuginfo(l_module_name, 'Error count is zero '
|| 'indicating that this payment instruction passed '
|| 'all validations. '
|| 'Setting instruction status to CREATED.'
);
* However, make sure to delete the PLSQL table of
* errors. These errors were generated by the validation
* sets and would have already been written into the
* IBY_TRANSACTION_ERRORS table.
*
* If we don't clear out these errors here, the PICP
* will try to insert these errors again in
* performDBUpdates(..) and will fail with a unique
* constraint violation because the errors already
* exist.
*/
x_docErrorTab.DELETE;
SELECT
*
FROM
IBY_TRANSACTION_ERRORS
WHERE
transaction_id = p_instr_id AND
transaction_type = TRXN_TYPE_INSTR AND
error_status <> 'INACTIVE'
;
* No need to insert an error message here.
* The payment instruction validation set
* would have already inserted an error message
* when it applied the validation on the
* payment instruction.
*/
END IF; -- result <> 0
* the PICP will use this errors list to insert into the
* transaction errors table and will run into a primary
* key violation error.
*/
IF (p_isReval = TRUE) THEN
OPEN c_instr_errors(x_pmtInstrRec.payment_instruction_id);
SELECT
IBY_EVENT_KEY_S.NEXTVAL
INTO
l_event_key
FROM
DUAL
;
|| 'insert has occured). Aborting program ..',
FND_LOG.LEVEL_UNEXPECTED
);
| 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_pay_instruction_id IN VARCHAR2,
p_instruction_status IN VARCHAR2
)
RETURN VARCHAR2
IS
l_module_name VARCHAR2(200) := G_PKG_NAME || '.getXMLClob';
* Select the given payment instruction from the database;
l_sql := 'SELECT payment_instruction_id '
|| 'FROM IBY_PAY_INSTRUCTIONS_ALL '
|| 'WHERE payment_instruction_id = :p_ins_id '
|| 'AND payment_instruction_status <> :p_ins_status';
* as the user will be selecting these from a lookup.
*/
retrieveSortingOptions(x_pmtInstrTab, l_sort_options_tab);
* failed payment instructions can be updated in
* the database.
*/
markFailedInstructions(x_pmtInstrTab, l_sort_pmt_tab);
* Select the payment reference information from
* the IBY_PAYMENT_REFERENCES table.
*/
SELECT
NVL(last_used_ref_number, -1)
INTO
l_last_used_ref_num
FROM
IBY_PAYMENT_REFERENCES
FOR UPDATE
;
* If we have sufficient number of payment references, update
* the last used ref number and commit. So that other
* concurrent instances, now get the updated last
* used ref number.
*/
UPDATE
IBY_PAYMENT_REFERENCES
SET
last_used_ref_number = l_anticipated_last_ref_num
;
SELECT
count(*),
payment_id
FROM
IBY_DOCS_PAYABLE_ALL
WHERE
document_status = DOC_STATUS_PAY_CREATED
GROUP BY
payment_id
;
SELECT
NVL(document_count_limit, -1),
NVL(payment_details_length_limit, -1)
INTO
l_docs_limit,
l_pmt_details_len_limit
FROM
IBY_REMIT_ADVICE_SETUP
WHERE
system_profile_code = l_profile_code
AND remittance_advice_format_code IS NOT NULL
;
SELECT
system_profile_code,
sort_option_1,
sort_order_1,
sort_option_2,
sort_order_2,
sort_option_3,
sort_order_3
FROM
IBY_INSTR_CREATION_RULES;
| updatePmtsWithSeqNumPmtRef
|
| PURPOSE:
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePmtsWithSeqNumPmtRef(
p_sortedPmtsTab IN sortedPmtTabType
)
IS
BEGIN
IF (p_sortedPmtsTab.COUNT = 0) THEN
RETURN;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_reference_number = p_sortedPmtsTab(i).payment_ref,
document_category_code = p_sortedPmtsTab(i).doc_cat_code,
document_sequence_id = p_sortedPmtsTab(i).sequence_id,
document_sequence_value = p_sortedPmtsTab(i).sequence_number
WHERE
payment_id = p_sortedPmtsTab(i).payment_id
;
END updatePmtsWithSeqNumPmtRef;
| updatePmtsWithSeqNum
|
| PURPOSE:
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePmtsWithSeqNum(
p_sortedPmtsTab IN sortedPmtTabType
)
IS
BEGIN
IF (p_sortedPmtsTab.COUNT = 0) THEN
RETURN;
UPDATE
IBY_PAYMENTS_ALL
SET
document_category_code = p_sortedPmtsTab(i).doc_cat_code,
document_sequence_id = p_sortedPmtsTab(i).sequence_id,
document_sequence_value = p_sortedPmtsTab(i).sequence_number
WHERE
payment_id = p_sortedPmtsTab(i).payment_id
;
END updatePmtsWithSeqNum;
| updatePmtsWithPmtRef
|
| PURPOSE:
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE updatePmtsWithPmtRef(
p_sortedPmtsTab IN sortedPmtTabType
)
IS
BEGIN
IF (p_sortedPmtsTab.COUNT = 0) THEN
RETURN;
UPDATE
IBY_PAYMENTS_ALL
SET
payment_reference_number = p_sortedPmtsTab(i).payment_ref
WHERE
payment_id = p_sortedPmtsTab(i).payment_id
;
END updatePmtsWithPmtRef;
| insertDistinctAccessTypsForIns
|
| PURPOSE:
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertDistinctAccessTypsForIns(
p_pmtFxAccessTypesTab IN distinctPmtFxAccessTab,
p_orgAccessTypesTab IN distinctOrgAccessTab
)
IS
TYPE t_object_id IS TABLE OF
NUMBER(15)
INDEX BY BINARY_INTEGER;
INSERT INTO IBY_PROCESS_FUNCTIONS
(
object_id,
object_type,
payment_function
)
VALUES
(
l_object_id(i),
l_object_type(i),
l_payment_function(i)
)
;
INSERT INTO IBY_PROCESS_ORGS
(
object_id,
object_type,
org_id,
org_type
)
VALUES
(
l_object_id(j),
l_object_type(j),
l_org_id(j),
l_org_type(j)
)
;
END insertDistinctAccessTypsForIns;
SELECT
document_sequence_value
INTO
l_seq_num
FROM
IBY_PAYMENTS_ALL
WHERE
payment_instruction_id = p_instr_id AND
ROWNUM = 1
;
SELECT
payment_reference_number
INTO
l_pmt_ref
FROM
IBY_PAYMENTS_ALL
WHERE
payment_instruction_id = p_instr_id AND
ROWNUM = 1
;
* Pad null values in the SELECT statement for the fields
* that will be filled up later.
*/
/* Perf Bug 5872977 */
IF l_sql_chunk is NOT NULL THEN
l_cursor_stmt :=
'SELECT '
|| 'pmt.payment_id, '
|| 'NVL (pmt.payment_reference_number, -1), '
|| 'pmt.payment_instruction_id, '
|| 'ins.payment_instruction_status, '
|| 'req.calling_app_id, '
|| 'pmt.legal_entity_id, '
|| 'pmt_mthds.document_category_code, '
|| 'pmt.payment_date, '
|| 'NULL, ' /* ledger id */
|| 'NULL, ' /* sequence number */
|| 'NULL ' /* sequence id */
|| 'FROM '
|| 'IBY_PAYMENTS_ALL pmt, '
|| 'IBY_PAY_SERVICE_REQUESTS req, '
|| 'IBY_PAYMENT_METHODS_VL pmt_mthds, '
|| 'IBY_PAY_INSTRUCTIONS_ALL ins, '
|| 'HZ_PARTIES payee, '
|| 'HZ_LOCATIONS payee_location, '
|| 'IBY_EXT_BANK_ACCOUNTS payee_bank ' -- Bug 5872977
|| 'WHERE '
|| ' pmt.payment_instruction_id = :instr_id '
|| 'AND pmt.payment_status = :pmt_status '
|| 'AND pmt.payment_instruction_id = '
|| ' ins.payment_instruction_id '
|| 'AND pmt.payment_service_request_id = '
|| ' req.payment_service_request_id '
|| 'AND pmt.payment_method_code = '
|| ' pmt_mthds.payment_method_code '
|| 'AND pmt.payee_party_id = payee.party_id '
|| 'AND pmt.remit_to_location_id = '
|| ' payee_location.location_id(+) '
|| 'AND pmt.external_bank_account_id = '
|| ' payee_bank.ext_bank_account_id(+) '
|| NVL (l_sql_chunk, 'AND 1=1 ')
;
'SELECT '
|| 'pmt.payment_id, '
|| 'NVL (pmt.payment_reference_number, -1), '
|| 'pmt.payment_instruction_id, '
|| 'ins.payment_instruction_status, '
|| 'req.calling_app_id, '
|| 'pmt.legal_entity_id, '
|| 'pmt_mthds.document_category_code, '
|| 'pmt.payment_date, '
|| 'NULL, ' /* ledger id */
|| 'NULL, ' /* sequence number */
|| 'NULL ' /* sequence id */
|| 'FROM '
|| 'IBY_PAYMENTS_ALL pmt, '
|| 'IBY_PAY_SERVICE_REQUESTS req, '
|| 'IBY_PAYMENT_METHODS_VL pmt_mthds, '
|| 'IBY_PAY_INSTRUCTIONS_ALL ins, '
|| 'HZ_PARTIES payee '
|| 'WHERE '
|| ' pmt.payment_instruction_id = :instr_id '
|| 'AND pmt.payment_status = :pmt_status '
|| 'AND pmt.payment_instruction_id = '
|| ' ins.payment_instruction_id '
|| 'AND pmt.payment_service_request_id = '
|| ' req.payment_service_request_id '
|| 'AND pmt.payment_method_code = '
|| ' pmt_mthds.payment_method_code '
|| 'AND pmt.payee_party_id = payee.party_id '
;
SELECT
payment_profile_id
INTO
l_pmt_instr_profile
FROM
IBY_PAY_INSTRUCTIONS_ALL
WHERE
payment_instruction_id = l_pmt_instr_id
;
SELECT
icr.system_profile_code,
icr.sort_option_1,
icr.sort_order_1,
icr.sort_option_2,
icr.sort_order_2,
icr.sort_option_3,
icr.sort_order_3
FROM
IBY_INSTR_CREATION_RULES icr,
IBY_PAYMENT_PROFILES prof
WHERE
prof.payment_profile_id = p_profile_id AND
icr.system_profile_code = prof.system_profile_code
;
x_pmtInstrTab(i).last_updated_by := fnd_global.user_id;
x_pmtInstrTab(i).last_update_login := fnd_global.user_id;
x_pmtInstrTab(i).last_update_date := sysdate;