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.

As a DBA, have you ever had an issue you suspected may be a bug and have been asked to generate “test data” for Oracle development?  We ran into this at a client recently and found a cool 11g utility (at least I *think* it’s new :)) to get Oracle Support the data they need to reproduce the issue in-house.

The situation had to do with optimizer_features_enable being set to 11.1.0.7 in a database recently upgraded form 9.2.0.5 in an Oracle eBusiness Suite environment (11.5.10.2) that utilized Oracle Reports heavily.  Our problem was specific to a custom Report that had optimizer hints embedded in the main query.  With optimizer_features_enable set to 11.1.0.7, the report retrieved no rows.  With it set to 9.2.0.x or 10.2.0.x, it returned the proper number of rows.  Based on this data discrepancy, we felt it safe to set optimizer_features_enable < 11.1.0.7 across the board, as we didn’t know the scope of the issue.

During our work on an SR with Oracle, they asked for us to send the query, all the versions and optimizer settings, as well as an export of all the tables involved in the query with exported optimizer statistics.  Our problem was several-fold; first, the query was complex and had many embedded views, so we didn’t really want to spend time deconstructing it to get a comprehensive list of tables to export.  Second, the underlying tables were very large (hundreds of millions of rows for a few of them) and we didn’t have disk space or quite frankly, time, to export all the tables in their entirety.

Enter DBMS_SQLDIAG …

Using DBMS_SQLDIAG and 11g Data Pump features, we were able to quickly generate a complete test case to export a subset of the rows from all the impacted tables, very quickly and with minimal disk space requirements.  Here’s what we did:

1. Grabbed the offending query from a TKPROF’d trace file
2. Used this syntax to generate a test case:

declare
tc_out clob;
begin
dbms_sqldiag.export_sql_testcase(directory=>’<directory>’,
sql_text=>’<SQL Text>’,
testcase => tc_out,
exportdata=>TRUE,
samplingpercent=>1);
end;
/

In the above example, note the following:

• <directory> is a valid directory - check DBA_DIRECTORIES
• <SQL Text> is the SQL statement from the TKPROF output
• exportdata=>TRUE tells DBMS_SQLDIAG to export the data from the base tables
• samplingpercent=>1 tells Data Pump to use a 1% sampling size.  This was important to limit the number of rows

After executing, a number of XML, log, and Data Pump export dumps are generated to <directory> and available to upload to the SR!

One additional step we took was to export table statistics for all the tables involved in the query.  For this, we looked in the log file for all tables export and used DBMS_STATS.EXPORT_TABLE_STATS to export segment statistics.

The obvious benefit here is that it enabled us to continue working on the SR, but some other possible applications of DBMS_SQLDIAG could be for internal testing purposes, testing functionality/performance across versions of Oracle without a complicated upgrade, regression testing, and so forth.

Most organizations leave their mission critical systems vulnerable to security threats and performance degradation. Recent studies show that organizational constraints are at the core of this issue. These constraints range from organizational policies, budgets and staff availability and even competency.

26 percent of organizations apply critical security and performance patches as they are released, 20 percent will eventually apply 6 to 9 months later while another 20 percent have no requirement at all to apply them. That leaves many companies data unprotected.

There are several ways to combat these constraints. One way is gain support from your organization. This can come in the form of support from executives, security / audit team and other critical policy makers. By establishing new and or updating existing security policies, and as a critical component, patching, these guidelines and timeliness of patching removes some of these constraints. A second way to protect and improve data integrity is to use technology. By keeping current on latest software, technology can help you reduce time and budget constraints of getting current applications and databases tested and allow your team to proactively assess the impact to your enterprise.

Centroid believes in 2 degrees of continuous change instead of wide arcs of re-activeness. These small degrees of change allows for continuous improvement across the organization. Whether it be through technology or process slight proactive adjustments make the enterprise perform at a higher degree then you can ever accomplish by large scale reactive measures.

Centroid is in the business of Future Proofing Oracle environments. In fact, Centroid is the leader in Future Proofing your Business and Technology and no one does it better. No one.

Often, in Oracle E-Business Suite Release 12 (R12) during an implementation or after patching, the pre-compiled jsp’s need to be recompiled because you aren’t seeing the change you expect. You can selectively recompile jsp’s using this program, but I like to just blow away everything and start fresh. Support documentation states “AD utilities to perform this action, for example after patches are applied which replaced 1 or more JSP pages” but I frequently have to use this tool manually after patching to see the expected results.

This is my go-to first step after patching or configuration changes that aren’t giving me what I expect.

My process is as follows:

1. Bring down Apache with adapcctl.sh in $INST_TOP/admin/scripts

[appldemo@demoR12 ~]$ $INST_TOP/admin/scripts/adapcctl.sh stop

2. Clear the cache directory by renaming it and recreating it.

[appldemo@demoR12 ~]$ mv $COMMON_TOP/_pages $COMMON_TOP/_pages_07DEC09 [appldemo@demoR12 ~]$ mkdir $COMMON_TOP/_pages

3. Start ojspCompile with the parameters –flush –compile -p [number of parallel processes]

[appldemo@demoR12 ~]$ $FND_TOP/patch/115/bin/ojspCompile.pl –flush –compile -p 8

4. Confirm the process completes successfully

starting…(compiling all) using 10i internal ojsp ver: 10 synchronizing dependency file: loading deplist…7983 enumerating jsps…7983 updating dependency…0 initializing compilation: eliminating children…5894 (-2089) translating and compiling: translating jsps…5894/5894 in 10m29s compiling jsps…5894/5894 in 44m27s Finished!

5. Almost Done! – Just Restart Apache!

[appldemo@demoR12 ~]$ $INST_TOP/admin/scripts/adapcctl.sh start

Wallet Setup Error — Could not open file …./wallet/ewallet.p12 to read data

Post Clone steps that need to be taken into account for R12 Payments to function properly.
After you clone an environment (…assuming that source system has the wallet setup for payment instructions) you need to do the following:

1. Copy the wallet files (*.p12 and *.sso) from source clone to the new cloned instance.  Note the directory the files are placed into.  In our case we put into $CUSTOM_TOP/wallet directory.
2. Rebuild the wallet file
3. Validate the wallet file has the proper permissions
4. Null the two columns: SYS_KEY_FILE_LOCATION and SYS_KEY_HASH in table IBY_SYS_SECURITY_OPTIONS
5. Log into Oracle EBS as Payments Setup Administrator.  Navigate to: Payments Setup –> Shared Setup –> System Security Options –> Wallet Setup.
6. You can now set the wallet in the UI with the ewallet.p12 file in the new path (note path from step 1 above).  The cwallet.sso is then generated in file system.

The next time you check the Wallet File setting in UI, it will be pointing to sso file.

IFRS…Global Accounting Standards are changing

IFRS -  International Financial Reporting Standards is a series of accounting standards, developed by the International Accounting Standards Board (IASB), that is becoming the global standard for the preparation of public company financial statements.

IFRS focuses on the standardization of financial reporting standards across international borders. This standardized reporting practice provides a uniform view of a corporations accounting statements. Companies with international subsidiaries can use consistent cross-company financial reporting.

More than 12,000 companies in approximately 113 nations have adopted IFRS, including listed companies in the European Union.  Other countries, including Canada and India, are expected to transition to IFRS by 2011.  Mexico plans to adopt IFRS for all listed companies starting in 2012.  Other countries, such as Japan and Mexico, have plans to converge (eliminate significant differences) their national standards to meet IFRS.

Over the years Enterprise applications such as Oracle eBusiness, Peoplesoft, and SAP, have evolved to embed more and more, of the local accounting standards and regulations. Embedding functionality to support GAPP, SOX, etc.

Whilst these applications are exceptional an keeping up with the continually changing landscape of Financial/Accounting regulations, conforming to IFRS has may people befuddled.

IFRS represents a series of guidelines, unlike GAAP, which provides exceptional detail in accounting practices. GAAP consists of volumes of detailed instructions, whilst OIFRS can be consolidated down to the size of a paperback novel.

As these guidelines are incorporated and consolidated into the current GAAP construct, companies face what seems like an impossible task on once again changing their accounting practices to ensure compliance.

Oracle eBusiness Release 12 introduces the concept of Sub Ledger Accounting (SLA). Sub Ledger Accounting provides a interim step to translate, and derive accounting logic, prior to recording GL Entries. Whilst SLA continues to be a buzz word, in the Oracle community, it’s regarded with both awe, and disdain. However, SLA proves to provide the most expedient  roadmap for corporations to start converting to IFRS.

SLA introduces a very powerful feature called Multiple Representation : A Single accounting entry can be represented for multiple reporting requirement. Organizations are able to have a primary ledger to represent primary accounting requirements such as GAAP. Accounting representations can be created to represent additional/secondary reporting requirement such as IFRS. Each secondary ledger can have a different subledger accounting method.

As IFRS becomes more widely adopted, expect to see greater adoption of SLA as a path to achieving global accounting compliance.

Recently we upgraded our R12 instance from 12.0.6 to 12.1.1 and encountered several unique errors in Order Management.

The new issues prevent an order line from being created and or order lines from being updates.  The error message presented is: You Are Not Allowed To Create Order Lines.

According to metalink the environment needs patch: 6958955 applied.  Apparently this patch has a sql script ontup251.sql in it that needs to run.  Metalink as of this posting did not have this patch available for download.  Oracle analysts then recommended applying another patch that had the ontup251.sql script in it.

The patch that was recommended to apply was 8310984, which is not ported to 12.1.1 yet.

So how do you fix the issue above?  Simple.  Login into Oracle Applications as Order Management Super User and navigate as follows: Navigated to Setups–> Rules –> Security –> Processing Constraints. Query up Application “Order Management” and Entity “Order Line”.  Once results come back, then query operation only of “Create”. There is a seeded record that has an issue. This record had operation of Create, a User action of “Not Allowed” and the Enabled checkbox was on, and the System checkbox was off. The Condition was “Any” BUT THERE was no validation entity nor any Validation Template. Thus the issue.  Since this was not a system generated constraint and the validation condition was null, we disabled the constraint.  This then allowed us to create an order line.

In addition to the above there is also an “Update” operation that is also null.  However this record is a system generated record.  In our case it was record 87 of 89 “update” operations.  We made a duplicate condition to another update record and saved.  Once this happened we were able to create new lines and update existing lines in OM.

We know this is not the long term solution.  We have evaluated running ontup251.sql script to drop and rebuild the constraint validation packages but at this time we decided against it until Oracle provides correct patch and requests that the script can be run in isolation.

Oracle eBusiness Release 12 introduces the concept of Subledger Accounting (SLA). Subledger Accounting provides an interim step to translate, and derive accounting logic, prior to recording GL entries. SLA is designed to provide an extremely flexible solution to facilitate the accounting needs of a corporation. Whilst SLA continues to be a buzz word, in the Oracle community, it’s regarded with both awe, and disdain.

In the old days, Oracle provided Auto-Accounting, and Account Generator as the options available for deriving accounts. Both were somewhat limited. Any accounting logic outside of a set of predefined rules required customization. Splitting a single accounting entry into multiple accounts required major customizations.

The transition from the individual sub-ledgers to the General Ledger was a single step process. Accounting entries were derived/recorded in the sub ledger, once the entries where completed, they were posted to the General Ledger.

The major concepts within SLA are :

• Rule Based Accounting Engine : With the introduction of SLA, an additional layer has been created. Accounting entries were derived/recorded in the sub ledger, once the entries where completed, they are posted to SLA. During the posting to SLA, the SLA engine derives the accounting entries based on the rules established within SLA. The SLA engine has the ability to derive each segment of the chart of account from any defined location with the Enterprise. The rules can be defined based on pre-populated data, or customized using PL*SQL hooks to the database.
• Multiple Representation : A Single accounting entry can be represented for multiple reporting requirement. Organizations are able to have a primary ledger to represent primary accounting requirements such as GAAP. Accounting representations can be created to represent additional/secondary reporting requirement such as IFRS. Each secondary ledger can have a different subledger accounting method.
• Straight Through accounting processing : Subledger Journal entries can be posted to the GL directly form the transactions screen

SLA whilst on the surface seems to be the holy grail for managing accounting processes, it does hold some drawbacks:

• Visibility of Accounts : Accounting entries generated via the rules engine are only visible in the SLA. The data in the sub ledgers (INV, RA, AP etc.) may not represent the same set of accounts as within SLA
• Debugging : Due to the flexibility of the rules engine, debugging accounting derivations can be a complex and involved process. The rules engine provides both configuration, and SQL based derivation rules. Requiring a combination of functional, and technical knowledge to debug issues.
• Adoption : Industry knowledge of SLA is still very limited, outside of the marketing and sales material. Educational, and professional services knowledge of SLA is sparse, making deployment of SLA a risk for many organizations.

As knowledge of SLA expands, and the continuing changes in accounting practices force more and more corporations to abide by Nations, and International accounting practice, SLA will prove a valuable asset. Oracle is well poised with SLA to meet the future reporting needs.

Deferred COGS is a new feature introduced in Release 12. The basic fundamental behind the enhancement is that the COGS is now directly matched to the Revenue.

Prior to this enhancement, the value of goods shipped from inventory were expensed to COGS upon ship confirm, despite the fact that revenue may not yet have been earned on that shipment. With this enhancement, the value of goods shipped from inventory will be put in a Deferred COGS account. As percentages of Revenue are recognized, a matching percentage of the value of goods shipped from inventory will be moved from the Deferred COGS account to the COGS account, thus synchronizing the recognition of revenue and COGS in accordance with the recommendations of generally accepted accounting principles.

Whist this helps solve some key accounting issues, There are some key issues one needs to be aware of:

• Currently Deferred COGS accounting cannot be turned off in release 12.

• The activity of recording COGS recognition is now a multi-step process

• Run AR Revenue Recognition, and Submit Accounting Processes

• Run a set of concurrent processes in Cost Manager to record Sales Order and revenue recognition transactions and to create and cost COGS recognition transactions. These COGS recognition transactions adjust deferred and earned COGS in an amount that synchronizes the % of earned COGS to earned revenue on Sales Order shipment lines.

• Record Order Management Transactions: records new sales order transaction activity such as shipments and RMA returns in Oracle Order Management.

• Collect Revenue Recognition Information: determines the percentage of recognized or earned revenue related to invoiced sales order shipment lines in Oracle Receivables.

• Generate COGS Recognition Events: creates and costs COGS recognition events for new sales order shipments/returns and changes in revenue recognition and credits for invoiced sales order shipment lines.

The end result of these activities is a series of COGS Recognition Material Distributions. However these distributions will not be visible on the Material Transaction screen, unless the ‘Include Logical Transaction’ checkbox is checked.