R12 Customer Bank Accounts

- Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored

IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
HZ_CONTACT_POINTS
HZ_ORG_CONTACT
HZ_ORG_CONTACT_ROLES

• The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

• The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name customer_name
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id
AND cust.party_id = cust_acct.party_id;

Join condition between RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL

Here is the query to find link of Sales Orders with their corresponding Invoices

/************************************************************************** *PURPOSE: To find link of Sales Orders with their corresponding Invoices * *AUTHOR: Shailender Thallam * **************************************************************************/ SELECT ooh.order_number, ool.line_number, ool.ordered_item, ool.ordered_quantity * ool.unit_selling_price, rcta.trx_number invoice_number, rcta.trx_date, rctl.line_number, rctl.unit_selling_price, ooh.org_id FROM oe_order_headers_all ooh, oe_order_lines_all ool, ra_customer_trx_all rcta, ra_customer_trx_lines_all rctl WHERE ooh.header_id = ool.header_id AND rcta.interface_header_context = 'ORDER ENTRY' AND rctl.interface_line_context = 'ORDER ENTRY' AND rctl.interface_line_attribute1 = TO_CHAR (ooh.order_number) AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id) AND rctl.customer_trx_id = rcta.customer_trx_id AND ooh.order_number = NVL (:p_order_number, ooh.order_number) --AND ooh.org_id=nvl(:p_org_id,ooh.org_id) ;

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 , idpa.po_number , idpa.document_description , ip.payment_id , ip.payment_service_request_id , ip.payment_instruction_id , ip.paper_document_number , ip.payment_amount , ip.int_bank_number , ip.int_bank_branch_name , ip.int_bank_branch_number , ip.int_bank_account_name , ip.payer_legal_entity_name , ip.org_name , ip.payee_party_name , ip.payee_address_concat , ip.payee_supplier_number , ip.employee_person_id , (SELECT employee_number FROM apps.per_all_people_f papf WHERE papf.person_id = ip.employee_person_id AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date ) employee_number, ip.employee_payment_flag , iupd.date_used , iupd.document_use , ieba.ext_bank_account_id , ieba.country_code , ieba.bank_account_name , ieba.bank_account_num , hzb.party_id bank_id , hzb.party_name bank_name , hzbb.party_id bank_branch_id , hzbb.party_name bank_branch_name , hzbb.address1 , hzbb.address2 , hzbb.address3 , hzbb.city , cpd.payment_document_id , cpd.payment_doc_category , cpd.payment_document_name , cpd.format_code , cpd.first_available_document_num , cpd.last_available_document_number FROM apps.ap_invoices_all invh , apps.iby_docs_payable_all idpa , apps.iby_payments_all ip , apps.iby_used_payment_docs iupd , apps.iby_ext_bank_accounts ieba , apps.hz_parties hzb , apps.hz_parties hzbb , apps.ce_payment_documents cpd WHERE 1 = 1 AND idpa.calling_app_doc_ref_number = invh.invoice_num AND idpa.calling_app_doc_unique_ref2= invh.invoice_id AND idpa.payment_id = ip.payment_id AND ip.paper_document_number = iupd.used_document_number AND ip.external_bank_account_id = ieba.ext_bank_account_id AND ieba.bank_id = hzb.party_id AND ieba.branch_id = hzbb.party_id AND iupd.payment_document_id = cpd.payment_document_id AND ip.payment_process_request_name = 'test - 1';

SELECT aisc.checkrun_name, aisc.checkrun_id, apt.template_name, iapp.system_profile_code, aisc.STATUS, aisc.* FROM apps.ap_payment_templates apt, apps.iby_acct_pmt_profiles_b iapp, apps.ap_inv_selection_criteria_all aisc WHERE 1 = 1 AND apt.payment_profile_id = iapp.payment_profile_id AND apt.template_id = aisc.template_id AND TRUNC(sysdate) <= TRUNC(NVL(apt.inactive_date,sysdate)) AND TRUNC(sysdate) <= TRUNC(NVL(iapp.inactive_date,sysdate)) AND iapp.payment_profile_id = aisc.payment_profile_id --AND aisc.checkrun_id = 10137 --p_checkrun_id AND aisc.checkrun_name = 'A Test 20th June';

What is MO_GLOBAL.INIT and When to Use MO_GLOBAL.INIT?

Before reading this post, if you are new to Multi Org and MOAC then read: MOAC – Oracle Apps ORG_ID, Multi Org Concept

What is MO_GLOBAL.INIT ?

MO_GLOBAL.INIT will read the “MO: Operating Unit” and “MO: Security Profile” profile option values from the current context (responsibility/user) and will initialize access to Multiple Organizations.

How does MO_GLOBAL.INIT initialize access to Multiple Organizations?

MO_GLOBAL.INIT will be executed in multi org environment, this API takes application short name as input and finds values for “MO: Operating Unit” and “MO: Security Profile” in the current context(responsibility/user). If the application being initialized is turned on with Multi-Org Access Control then access will be allowed for all the Operating Units under the security profile. (Note: Query for table FND_MO_PRODUCT_INIT to know if an application is turned on for multiple organizations access or not)

What if MO Profiles are not configured

If “MO: Security Profile” is configured then “MO: Operating Unit” is ignored, If “MO: Security Profile” isnot configured and “MO: Operating Unit” is configured then the application being initialized is granted access only to a particular organization configured in “MO: Operating Unit”.

If both “MO: Operating Unit” and “MO: Security Profile” are not configured then its a critical error and MOAC will not function.

When should we use MO_GLOBAL.INIT?

1. MO_GLOBAL.INIT should be used in Multi Org environment
2. MO_GLOBAL.INIT will be called every time after logging into the application or whenever there is a switch in context/responsibility

Sample Usage of MO_GLOBAL.INIT

--Use this before API call mo_global.init ('AR');

How to Display Leading Zeros in XMLP Report – Excel Output

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ‘007’ excel displays it as ‘7’, excel display it as ‘007’ only if the column is set in text format. You can see the difference in the below screenshot

Please go through the below URL for an example to generate an XMLP report:
http://oracleappsdna.com/2013/07/plsql-script-to-generate-xml-tags-for-xmlp-report/

In the example mentioned in the above URL you could see the output as shown in the below screenshot

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number( Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

1. Open the RTF Template in MS Word.
2. Go to Data -> Load XML Data.
3. Once the data is loaded successfully, double click on that field.
4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

After following the above said steps, save the template and preview the output which is as shown below

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.

Credits:- Thanks to my friend Kiran Reddy for helping me to get this knowledge.

UPDATE: on 20-OCT-2015

Method 4

Here is another way to set format style ‘Force LTR’ in a programmatic way to prevent Excel from suppressing leading zeros:

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">
<?XML_ELEMENT?>
</fo:bidi-override>

URL to Open Invoice in Oracle Core Applciations

Requirement: Provide a URL from an Invoice Report that will open the invoice in Oracle Core Applications

Pre-Requisites: Using a view based reporting solution, the idea is that when an Accounts Payables runs a report, is able to launch a link that opens the invoice in the Core Application.

Oracle APIs Used:

1. FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL – This allows the generation of the URL based on a FUNCTION_ID, APPLICATION_ID, RESPONSIBILITY_ID, SECURITY_GROUP_ID. This function has other parameters that can be used as required.
2. To gather the required parameters:
   1. FUNCTION_ID; the FND_FUNCTION.GET_FUNCTION_ID – This API gets the function_id for ‘AP_APXINWKB_SUMMARY_VIEW’ function.
   2. APPLICATION_ID, RESPONSIBILITY_ID, SECURITY_GROUP_ID – Please refer to the security initialization blog post to see how to gather these details.
3. To view a list of all the available functions:

1 2 3 4 5

SELECT function_id, function_name, application_id, parameters from fnd_form_functions;

Final Solution – created a basic invoices query:

1	SELECT invoice_num from ap_invoices_all

Now adding the API to generate the URL:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT invoice_num, '<a href="' || apps.FND_RUN_FUNCTION. get_run_function_url ( apps.fnd_function. get_function_id ('AP_APXINWKB_SUMMARY_VIEW'), 200, 50554, 0, 'INVOICE_ID=' || api.invoice_id) || '"> view_inv</a>' view_inv FROM ap_invoices_All aia;

Make sure you pass the correct parameters into each API to view the result. Click on the URL and it will launch the respective invoice in Oracle Core Applications. You can now reference this API call in any query that has invoice_id available to be passed.