Category Archives: Payments

Query to list all Documents and their information under a Payment Process Request

This query list all payment documents under a payment batch with detailed invoice and Supplier/Employee information. /********************************************************************** *PURPOSE: Query to list all Documents and their information under a Payment Process Request *AUTHOR: Shailender Thallam ***********************************************************************/ SELECT ip.payment_process_request_name, invh.invoice_id , invh.vendor_id , invh.invoice_num , invh.invoice_amount , invh.amount_paid , idpa.payment_date , idpa.document_type , idpa.payment_amount , idpa.payment_method_code ,… Read More »

Query to list Supplier Bank, Bank Branch, Bank Account information

/********************************************************************** *PURPOSE: Query to list Supplier Bank, Bank Branch, Bank Account information *AUTHOR: Shailender Thallam ***********************************************************************/ SELECT owners.account_owner_party_id, asp.segment1 vendor_num, asp.vendor_name, (SELECT NAME FROM apps.hr_operating_units hou WHERE 1 = 1 AND hou.organization_id = asa.org_id ) ou_name, asa.vendor_site_code, ieb.country_code, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name, cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code, ieb.bank_account_num, ieb.currency_code, ieb.iban, ieb.foreign_payment_use_flag, ieb.bank_account_name_alt FROM apps.iby_pmt_instr_uses_all instrument, apps.iby_account_owners owners, apps.iby_external_payees_all… Read More »

Query to list Bank, Bank Branch, Bank Account information Operating Unit wise

/********************************************************************** *PURPOSE: Query to list all Bank, Bank Branch, Bank Account information * Operating Unit wise *AUTHOR: Shailender Thallam ***********************************************************************/ SELECT cba.bank_account_name , cba.bank_account_id, cba.bank_account_name_alt, cba.bank_account_num , cba.multi_currency_allowed_flag , cba.zero_amount_allowed , cba.account_classification , bb.bank_name , cba.bank_id, bb.bank_number, bb.bank_branch_type , bb.bank_branch_name , cba.bank_branch_id , bb.bank_branch_number , bb.eft_swift_code , bb.description BANK_DESCRIPTION, cba.currency_code , bb.address_line1, bb.city, bb.county, bb.state,… Read More »

XML Data for Positive Pay

Unlike check printing, Positive Pay XML data is NOT present in table IBY_TRXN_DOCUMENTS. To get XML data for Positive Pay, we need to attach ‘Extract Identity‘ XMLP Template to Payment Format and run PPR process. Output of ‘Positive Pay File‘ concurrent request will be the XML data of that particular PPR run. Using this XML… Read More »

XML Data of AP Check Payments – Format Payments Instructions in R12

Generally after running Payment Process Request, we can get the XML data in the log file of ‘Format Payment Instructions with Text Output’ using which we can develop/debug the rtf/eText template using XML Publisher. Not only in the log file but also the XML data is stored in IBY_TRXN_DOCUMENTS table. Below is the SQL Statement… Read More »

Customizing Oracle Payments XML extract to include Custom XML Tags

Oracle has provided an extensible user hook “IBY_FD_EXTRACT_EXT_PUB” to add custom tags as part of seeded payment XML extract generated by Oracle Payment process. This hook allows custom elements to be created at following levels. Instruction Payment Document Payable Document Payable Line Payment Process Request You cannot customize the package specification, but package body contains… Read More »