Oracle Business Applications

Further to this post on this error. Here is another reason why this error occurs. If you happened to use the seeded accounting rule called Immediate in your transaction (be it in Service Contract or Sales Order) you need to make sure that the period type used in ledger or set of books matches to the Period type used in Immediate accounting rule.

As you can see below, Immediate accounting rule comes with period type called Month (this cannot be changed). Say in your implementation you do not use Month as your period type but something else like 4-4-5 or Monthly.  In that case, if you bill a contract or invoice an order line with Immediate accounting rule, you are bound to get this error when you run autoinvoice import.

To get around this, you need to define your own accounting rule with your period type that matches to your ledger. When use that accounting rule, phew, that error goes away.

This is a good feature some of the industries can use to manage profitability of service or repairs. Especially industries where the service is resource and time intensive, it is good to track profitability of each service request.

This functionality is not integrated with GL in the form of journal entries yet. It just comes in the form of a report which can be run from the service request screen.

One of the limitation of this feature (as documented) is that this functionality only works with Standard Costing Method. Disappointed, I started tinkering to make this to work with average costing.  Essentially there are two issues in how the cost was sourced.
First issue is that org_id (operating unit) was passed instead of organization_id (inventory organization) to get the cost. If the operating unit is also classified as inventory org and you define cost for the item then there is no issue. But it is hardly true in real world (operating unit also classified as Inventory organization).

Second one is (not essentially a bug), costing method is not passed to the cst_cost_api to get the cost. When you do not pass this API simply assumes standard costing method. My argument is, if we know the organization to get the cost for the item, how hard it is to get the costing method and pass it to this API?

Once I fixed these two in the cs_cost_details_pvt (of course it is standard code), it simply works.

We just need to make sure to update the cost of these non-ttransactable items using average cost update process.

You can see the presentation here.

Back in 2009, I needed WIP completion transaction also be part of the Asset Creation process for depreciable items in Asset Tracking. The base was either R12.0.4 or R12.0.6, i don’t remember. But traditionally, for depreciable items,  following transactions are supported to create an asset in FA while the asset is still in Inventory.

1. Miscellaneous Receipt
2. Account Alias Receipt
3. Account Receipt
4. PO Receipt into Inventory
5. PO Receipt into Projects
6. Physical Inventory (Receipts)
7. Cycle Counting (Receipts)

But there are companies that manuafacture products and they want to track them in FA. They typically get into inventory with WIP completion transaction. As you can see this transaction is not in the list above.

Recently, to my surprise, I found this patch 7489949 (which was released in 2008) that includes WIP completion also into supported transactions for asset creation. To add to my surprise, support had no clue when they were asked for solution at that time. Of course we came a long way.

BTW, it is included in base release of R12.1.1.

Recently had a situation. An order was shipped with two lines at zero price and one line at some price. This order’s invoice was eligible for a revenue contingency which dictates that the the revenue will be recognized only upon customer pays. As a result of which all the lines are waiting in the Deferred or Unearned revenue bucket till the customer paid.

Eventually customer paid the invoice but two lines which are at zero price in the invoice never got out of unearned revenue (one line which has price did get recognized for revenue). What is the issue here? Issue is not as much as with Revenue but with COGS. Since COGS recognition depends on the revenue recognition it never got recognized since there was no revenue. As a result, cost of this order line continues to be in Deferred COGS (No revenue, No COGS and Deferred Revenue equals Deferred COGS and that makes sense). But we need COGS to be recognized.

But why is the revenue not getting recognized for the lines with Zero price? It is not that hard to guess. Revenue Adjustment can take place only if there is some value to the invoice line (R12.0.6 base). Eventually ended up getting a patch 7454302. Looks like this may solve the issue!

It is weird to see why I cannot unapply a credit memo that I created by crediting the invoice. But I can do so if I create on-account credit first and then apply that to an invoice of your choice.

Case1:

1. Pull up an invoice in AR
2. Credit the invoice from actions and complete it.
3. Now query up the credit memo
4. From Actions try looking at Applications where we usually unapply the invoice.
5. Applications option is disabled hence I cannot unapply the invoice from Credit Memo.

Case2:

1. Create an invoice and complete it
2. Create a credit memo and complete it
3. While you are still in credit memo choose actions–>applications
4. Apply the invoice you have created in the first step and save
5. From the same credit memo you can now navigate to applications and unapply the invoice.

What is the argument here?

1. Is tax calculation is driven off of the original invoice’s tax amount? Does not hold good as I have not see that. Probably configurable.
2. Is accounting driven off of the original invoice? With SLA this is pretty much useless unless you are using standard accounting. I will be a little surprised with that if anyone still using standard accounting after SLA is in place.

Or anything that I am missing that some of you can enlighten me.

After this letter (The Death Of Tech Genius) got published in October 22nd issue of Economist, the magizine rightly published this Obituary on Dennis Ritchie. As authors of these posts said, there would not have been computing world without Dennis Ritchie, who invented C programming language. We should mourn the loss of Dennis as much we did for Steve Jobs. RIP, Dennis.

Common wisdom is that transaction numbers in AR are decided by the batch source. But what if you wanted to have numbering specific to transaction type of these transactions that use the same batch source (invoice should have one sequence number and Credit Memos should have different sequences and so on)? Also some users of Oracle AR in different countries need to follow the sequence numbers specific to periods (every year or every month I want follow different sequence numbering). Well, here is the presentation that gives you simple steps to achieve the same. Hope this is useful.

I wish you all a happy and prosperous year 2011!

A nice video by Epipheo Studios explaining ERP in just over 3 mins. You can see their complete portfolio here.

Every country has its own address style. For example in Ireland there is no need for the maintaining the postal code but at the same time it is required if the country is UK. So when you maintaining the customers and suppliers in a global instance we need to have that flexibility to maintain addresses in that country’s address format. This is not new but is there from, as far as I remember, 10.7 GUI. This is achieved by a feature called Flexible Address Format.

There are simply three steps to it:

1. Create country specific address format.
2. Add this style to the lookup
3. Assign this style to the country that is needs this style.

The first step is nothing but creating a context in a descriptive flexfield called Address. This is under application Receivables. Query up that DFF add your ISO country code as the context. And then add necessary segments as per your needs under that context. You can make fields as required or not while defining your segments, which is a standard feature.

In the next step, you need to add this address style (context) as a lookup code in a lookup type called ADDRESS_STYLE. Unfortunately this is not available under receivables lookups with in receivables manager responsibility. You have to access this in Application Developer–>Application–>Lookups–>Application Object Library. Query the above mentioned lookup type and add the lookup code as your ISO country code (which is same as your context).

In the third step we assign this address style to the country of your choice. The reason we did the second step is to accomplish this third step. For some reason the LOV of address style in this step is from the lookup codes and not directly from the contexts of the DFF Address that we used in the first step.

Once you assign your address style to the country code of your choice, the address segments will show up the way you have defined under that country context in the DFF.

A good part of SLA functionality is deriving correct account based on business rules. This functionality is called defining account derivation rules. The outcome of accounting derivation rule sometimes is an entire account (famously known as code combination) or simply a segment in the code combination. If only a segment is derived then each of the segment in the chart of accounts is weaved together just like the way it is done in workflow account generator. Take for example revenue account. Oracle gives us a default account from the transaction in AR which is generated using Auto Accounting. What we can do is, take that account and plant a specific segment, say, natural account under specific condition. Also the whole account can be replaced with a new one under specific conditions.

The difference between workflow account generator and SLA accounting derivation rules is that, the conditions under which the account or a segment derived reside in code in first one but are visible to the business users in later one. They understand the conditions under which an account is derived because it is configured in the screens and not sitting some cryptic code. Also little room for bugs, so to speak.

Usually we generate the account for either credit or debit line of a journal entry. An accounting entry, at a minimum, has a debit and credit. These are called are journal lines (a debit line and a credit line). An accounting derivation rule is for a specific journal line. Going a little further this journal entry is for a transaction. This transaction event is called event class. These two make up the heart of SLA setups.

Taking the same example of revenue account, revenue account is a journal line. Revenue is one side or one line in an accounting entry. The other usually is a receivables account (in case of AR transaction). These two together make a journal entry. And this journal entry belongs to an event class called Invoice (in case it is invoice).But if you have observed the line definitions under invoice event class you might have noticed way more than these two lines. That is because each of them is used in different ways depending on the nature of transaction.

Implementation: What is in it? What kind of work is involved?

Traditionally we have accounting in each application or a process flow is taken care of by individual specialist in that area. For example an AR specialist used to get the accounting needs implemented as per the requirement and cost management or order management specialist gets the cost accounting right. So a business process owner for accounting has to run around each of these specialists to get the accounting taken care of. Also prior to SLA the functionality provided in Oracle is rigid in some application areas and very flexible in a number of other areas. To cater to these flexible needs a number of tools were also available like Workflow account generators, Post or Pre hooks or some setups to generate accounting based on business rules.

SLA is a game changer from that perspective. We can throw away workflow accounting generator and post or pre-hooks coding. And also one specialist, who understands enterprise accounting and business needs of accounting department, can implement complete SLA solution in all applications and processes. This is new and emerging consulting role.

Implementing SLA involves writing rules to arrive at accurate accounting under specific conditions for a specific transaction event. To achieve what you need, we may need to write some coding in custom sources but in general that is far simpler than workflow. All you need to know if writing simple pl/sql code.

But for a SLA specialist the challenge is in understanding transactions and event class is mapping to these transactions. Sometimes it is self explanatory. An event class like Sales Order issue in Cost Management or Invoice in AR tells you that these are for COGS and Revenue respectively. But not all are so straight forward. But if you have knowledge about the transactions performed in each application areas and some technical background it is pretty easy to get there.

Apart from generating accounts there are a lot of other features in SLA. We will explore them in further posts.

Usual wisdom till R11i is that install base gets updated or created as customer product when we ship the product. This shipping must go through the inventory transaction (remember that we can ship no inventory items as well) in order for the IB to be updated or created. But to achieve the same for the order lines that do not go through shipping and inventory (like Bill Only lines with fulfillment), we used to extend Order line workflow (supported way) to plug in a function that sends a message to update or create instance. This used to be upon fulfillment.

Stating R12 this is a seeded functionality. The same function that is given to customize the workflow is added to the order line fulfillment code. Meaning all bill only lines (for that matter any line workflow that has fulfill node and item is IB trackable) automatically create or update the install base upon fulfillment. But again this is controlled by a profile option (OM: Automatically Interface Lines to IB on Fulfillment). You can control this behavior by setting this profile option value as Yes or No.

May be we can measure earthquake in scales and numbers, but disaster that ensues is something that cannot be measured in any scale. It is time to come together and help.

Please help the victims of Haiti earthquake. Here is the most reliable way, that is just announced by the President, which ensures your contributions reach actual victims. Please visit and contribute.

http://clintonbushhaitifund.org/

We all know SLA is rule driven to derive accounts for create accounting process. If you are not using standard (seeded) accounting rules, but have created a new one with a new application accounting method for a specific application, how create accounting program uses these rules to derive correct accounting?

We talked about SLA earlier here. If you follow the earlier article, we are talking about using application accounting method (custom) to derive an account for a specific journal line type (debit or credit). To achieve custom accounting you assign this accounting derivation rule to this journal line type. These journal line types roll into event classes (invoices or deposits) and entities (AR transactions) to form Application Specific Accounting Method. This accounting method is assigned to an application of interest where this desired accounting is expected. This accounting method has to be validated (Validation Program) for this to be used.

This validation process actually creates a database package where all these rules are coded and maintained. To identify the package you can use this simple script which gives you the package name. Parameters to this package have to be sourced from the table XLA_PRODUCT_RULES_B. This tables stores the application accounting definitions that we talked about earlier. For example you want to get the custom application accounting method you have created for Receivables, you can simply use this SQL to get that.

Pass the values from the above SQL to this simple function get that package. If you want to see standard rules, change product rule type code from C to S. If you have more than one product rule code (application accounting method), you should know which one you are looking for. If you open this database package your technical eyes can see what is happening in create accounting program.

select application_id,
product_rule_code,
product_rule_type_code,product_rule_hash_id from xla_product_rules_b
where application_id=222 –Receivables
and product_rule_type_code=’C';

DECLARE
c_package_name CONSTANT VARCHAR2 (30) := ‘XLA_$id1$_AAD_$id2$_$id3$_PKG’;
l_package_name VARCHAR2 (1000);

FUNCTION getpackagename (
p_application_id IN NUMBER,
p_product_rule_type_code IN VARCHAR2,
p_product_rule_hash_id IN NUMBER
)
RETURN VARCHAR2
IS
l_name VARCHAR2 (30);
l_hashapplication VARCHAR2 (30);
l_hashrulecode VARCHAR2 (30);
l_log_module VARCHAR2 (240);

BEGIN
l_hashapplication := LPAD (SUBSTR (TO_CHAR (ABS (p_application_id)), 1, 5), 5, ’0′);
l_hashrulecode := LPAD (SUBSTR (TO_CHAR (p_product_rule_hash_id), 1, 6), 6, ’0′);
l_name := c_package_name;
l_name := REPLACE (l_name, ‘$id1$’, l_hashapplication);
l_name := REPLACE (l_name, ‘$id2$’, p_product_rule_type_code);
l_name := REPLACE (l_name, ‘$id3$’, l_hashrulecode);
RETURN l_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END getpackagename;
BEGIN
l_package_name :=
getpackagename (p_application_id => 222,–application ID
p_product_rule_type_code => ‘C’, –Rule type
p_product_rule_hash_id => 18 –Hash value
);
DBMS_OUTPUT.put_line (l_package_name);
END;