In R12 the formatting of payments (Checks, ACH, wires) and the output of related documents (positive pay, Separate Remittance Advice) is done by a Java concurrent programs that generate XML output which is then formatted by BI Publisher.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory.

The package allows custom elements to be created at five levels within the payment XML extract. You cannot customize the package specification, but the package body contains stubbed functions that you can customize.  The five functions are as follows:

Instruction - FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at instruction level and run only once for the instruction.

Payment - FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

Document Payable - FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

Document Payable Line - FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE
This function allows XML element to be introduced at document payable level and run once for each payment process request.

Here are two examples where I utilized this extension:

1) Last Day of Period for Positive Pay file:

The Positive Pay File only provides the tree <PositivePayDataExtract> with <OutboundPayment> for each payment that it captures. In order to add data to the positive pay file XML  you must use the function IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg.

2) Count of ACH Transmissions per day:

This count was needed once per payment instruction so I utilized IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg to insert the new xml element at the bottom of </OutboundPaymentInstruction> (the root of the XML for payment batches). This funciton also appears in the XML stream for separate remittance advice, checks, etc.

Here is a screen shot of how we coded the Get_Ins_Ext_Agg:

IBY_FD_EXTRACT_EXT_PUB.Get_Ins_Ext_Agg

I recently received a requirement to account for the entire deferred revenue amount of a sale, then recognize the revenue over time, based on consumption or clicks from a customer. Oracle Receivables has two “commitment” transaction classes to support this type of requirement.

The two commitment transaction classes are:

Guarantees: as you associate invoice transactions the amount due “moves” from the guarantee to the invoice associated to it.

Deposits: as you associate invoice transactions the amount due on the invoice is reduced or wiped out and all cash, collections, and aging can be tracked on the single deposit.

In my situation Deposits is the way to go for the following reasons:

One bill to the customer is required; we are billing the entire amount of the “deal” using installment terms (example: 25% of invoice every 30 days). Opposed to guarantees where there is an amount due on each invoice associated to the guarantee (not acceptable in my case).

Also, cash can be applied to the deposit, guarantees do not allow cash applications.

Finally, aging can be “centralized” as the deposit and installment payments are managed and reported under a single transaction. The impression invoice transactions are simply recognizing revenue in my model, in a supported, auditable way.

Accounting Flow for my setup:

1. Create the Deposit with installment terms
Example: $400K, $100K due at 30, 60, 90, 120 days.

Note: Detailed aging would show 4 installments of $100k due every 30 days.

2. Create a cash receipt applied to first installment.
Example: $100K paid for first amount due.

3. Create the impressions Invoice to recognize revenue - Linking it to the Deposit commitment Invoice.
Example: $90K in impressions delivered

Balance goes to $0 once associated to the deposit which has enough remaining dollars to cover entire invoice.

We have been using jdeveloper 10g to create new OA Framework forms for our R12 Oracle EBS applications 12.0.6 with database version of 10.2.0.4 without issues.

We then upgraded our database to 11.1.0.7.0 and EBS to 12.1.1.  That is when we encountered an issue with prior custom OA forms in EBS.  oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.IllegalAccessError, msg=oracle/jdbc/driver/OracleCallableStatement.

This issue/error is specifically related to the OracleCallableStatement used in 11g.  With 11g certain features/libraries are not available.  The Oracle Database 10g Release 2 (10.2) will be the last major release to offer the following feature/libraries: classes12.jar, oracle.jdbc.drivers.* and OracleConnectionCacheImpl. In other words these features/libraries will not be available in the next major database release (for example 11g).

Since our java code was using an OracleCallableStatement and also using the oracle.jdbc.drivers on the 11g database we encountered the error above.  This means that public Oracle JDBC driver classes that were referenced by oracle.jdbc.driver package must be referenced by using the oracle.jdbc package . Therefore, Java programs must not import public classes that  have belonged to the oracle.jdbc.driver package - but import  those classes from the  oracle.jdbc package.

Since our code was using “import oracle.jdbc.driver*” we had to replace it with import oracle.jdbc.*

Once we replaced the above statement; the OracleCallableStatement executed as expected.

After back and forth volleys with Oracle Support in getting Configurator to work correctly in R12 with SSL and load balancers (as well as great support from our DBA Team) I thought it would be good to share what is missing from some documentation within metalink (at least at time of this post) and hope it saves you time in the setups.

1) Review Section 4.4.3.1 AltBatchValidateURL, Oracle® Configurator Implementation Guide, Release 11i, Part No. B13604-03 and also Oracle® Configurator Implementation Guide Release 12 Part No. B28682-02 page 4-15.  Note that the R12 documentation should be updated as the URL needs to be slightly different for R12 as in step 2 below.  This is what caused our issue!

2) Check the value of the setting in cz_db_settings for AltBatchValidateURL. Use this statement to check it:
SELECT * from cz_db_settings where upper(setting_id) = ‘ALTBATCHVALIDATEURL’   If no rows are returned then run the following sql insert statement:
INSERT INTO cz_db_settings (setting_id, section_name, data_type, value, desc_text) VALUES (’AltBatchValidateURL’,’ORAAPPS_INTEGRATE’,4,’http://hostname:portnum/OA_HTML/configurator/UiServlet’,’Non-secure URL’)

If a row is returned then validate that the value is correct based on the above url.  If not then you can update the cz_db_settings table as follows: update cz_db_settings
set value = ‘http://hostname:portnum/OA_HMTL/configurator/UiServlet’ where setting_id = ‘AltBatchValidateURL’

3) Validate the the profile option for “BOM:Configurator URL of UI Manager” is set to: http://hostname:portnum/OA_HTML/configurator/UiServlet

Hopefully the R12 documentation is updated in the near future to guide others in the process.

With the release of R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC) . MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

The Set Up is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

How Does the R12 MOAC Defaulting Rules Work?

1. If the profile option “MO: Security Profile” is not set, then “MO:
   Operating Unit” value is used as the default Operating Unit even if “MO:
   Default Operating Unit” profile is set to a different value.
2. If the profile option “MO: Security Profile” is set and gives access to
   one Operating Unit, the default Operating Unit will return this value even if
   “MO: Default Operating Unit” is set to a different value.
3. If the profile option “MO: Security Profile” is set and gives access to
   multiple Operating Units, then the profile value “MO: Default Operating Unit”
   if set is validated against the list of Operating Units in “MO: Security
   Profile”. If the Operating Unit is included in the security profile then it
   is returned as the default value. Otherwise there is no Operating Unit
   default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
   set, then there is no default Operating Unit.

What is the impact to you?
With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_id(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

Pre R12 Multi-Org Features

• Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
• A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features

• Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
• A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Pre-R12 you could set your SQL session context for multi-org with the following:

BEGIN
dbms_application_info.set_client_info(101);
END;

• In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

In R12 you can set your SQL session context for a single OU with the following:

BEGIN
execute mo_global.set_policy_context(’S',101);
END;

• The ‘S’ means Single Org Context
• 101 is the ORG_ID you want set

Also In R12 you can set your SQL session context for multiple OU’s with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;

• 111 is the Security Profile you want to use
• ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them

select psp.SECURITY_PROFILE_NAME, psp.SECURITY_PROFILE_ID, hou.NAME, hou.ORGANIZATION_ID                from PER_SECURITY_PROFILES psp, PER_SECURITY_ORGANIZATIONS pso, HR_OPERATING_UNITS hou            where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID  and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

Oracle has implemented a new parameter on the Concurrent Parameters form to control how to handle Operating Units. The real purpose of this tip is to explain this parameter / new field “Operating Unit Mode” at the concurrent program definition level. For more detail on Multiple Organization Access Control’s “MOAC” impact to custom code and its features you can review Metalink note: 420787.1 and also review Oracle Applications Multiple Organizations Implementation Guide R12.

Where to locate the new field
Within the concurrent program definition, a new field “Operating Unit Mode” is added in the OA Framework pages. A user should have the responsibility “System Administration” assigned to them. This is the web version of System Administrator. To locate this new field Navigate to Concurrent programs. It will open up web page: Concurrent Processing. Query up the concurrent program you want to view and or change the value of Operating Unit Mode. Click on the update button. On the tab “Request” there is a LOV “Operating Unit Mode”. This LOV has three values:

1. NULL – default setting
2. Single – run only for a specific Operating Unit specified by ‘MO: Operating Unit’ profile option
3. Multi – run for multiple Operating Units based on the ‘MO: Security Profile’ profile option

This Operating Unit Mode parameter is used to identify:

• How the program executes the multiple organizations initialization
• When to display Operating Unit prompt in the Submit Requests window and Schedule Requests window.

This impacts how the Submit Requests form evaluates Concurrent Request Parameter List of Values. If you don’t see what you want try changing this setting.
If the Operating Unit Mode field is set to either Single or Multiple then the multiple organizations context is automatically initialized by the concurrent program. The user can also select a value from the operating unit field’s list of values when the mode is Single. The value of the “Operating Unit Mode” must be Single for a majority of the existing operating unit context sensitive reports.

Single Organization Reports
The operating unit mode for single organization reports are flagged as ‘SINGLE’ in the Define Concurrent Programs page. The parameter – Operating Unit is available for single request and request sets. You cannot enter any value in this field if the Operating Unit mode is Multiple or none. When submitting the report, the concurrent program captures the current organization specified in the Operating Unit parameter.

Cross Organization Reports
The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE’ in the Define Concurrent Programs page. At runtime, multiple organizations initialization populates the temporary table with one or multiple operating units depending on the access control status of the product that owns the cross organization report.