What is ASO_ORDER_FEEDBACK_T?

 This articles provides insight into the functionality behind the table ASO_ORDER_FEEDBACK_T and information on how we regained 100GB space by performing certain tasks.

My client is into four years of implementation with a lot of applications implemented in ERP and CRM areas. While performing database audit, our DBA team found that the table ASO_ORDER_FEEDBACK_T was growing at an alarming rate (size was close to 100GB). They wanted the to understand the functionality behind this table and recommendations, if any, can be made to contain the growth. So, before we took any action we had to perform research and recommend an action plan to manage the growth. Here is the simple summary of what we found.

Functional Analysis

As a standard feature in Order Management there is a queue generated whenever a sales order is booked. This table holds the queue which has complete information about a sales order. This is a good feature if we want to execute an action in some other application as soon the order is booked. As a seeded funcationality this queue table holds information which is used by some applications with in Oracle Applications. But as an extension we can always use this queue if we want to communicate with any application of our choice. As an example if we have some custom shipping application and we want to send messages to that system as soon the order is booked, we can make use of this feature. Here is how it works.

This queue is built as soon the order is booked which happens in the private api that is used to create orders: oe_order_pvt.process_order. This in turn calls the package  oe_service_util.notify_oc which builts the queue. This simple article addresses the following questions:

1. Who subscribed to this message?
2. Who consumes this message queue?
3. Can we turn off this so that we can contain this table’s growth?
4. What is the impact of turning this off?
5. Can we add our own application get messages from order?

Subscribers

Subscribers to this message can be located in the look up type ASO_ORDER_FEEDBACK_CRM_APPS. All the active lookup codes (enabled flag is checked and end date is less than or equal to today’s date) in this look up type are the subscribers. The number of queues built in this table for each order is equal to the number of active subscribers.You can imagine the growth of this table if you are booking a large volume of the orders on a daily basis and have no consumers for this message based on your business needs. Oracle Applications come with four standard subscribers for this queue that get auto installed: CN (Incentive Compensation), CS (Cusotmer Service and actually used by Install Base), OKS (Service Contracts) and OZF (Funds & Budgets or popularly known as Trade Management as well as Oracle Marketing).

Who Consumes these messages?

The following programs and packages consume the queue when you run the programs. If you do not run these programs obviously the queue grows as soon an order is booked and never gets dequeued.

Program Name	Application	Package Name	Consumes..
Process Old Order Lines – Fulfillable only	Install Base	csi_order_fulfill_pub.Process_old_order_lines	CS Queue
Funds Accrual Engine	Funds & Budgets	ozf_accrual_engine.get_message	OZF Queue
AMS-TM : Funds Accrual Engine	Oracle Marketing	ams_accrual_engine.get_message	OZF Queue
Collect Orders	Oracle Sales Compensation	cn_collect_orders.collect	CN Queue
Service Contracts Order Capture Integration	Oracle Service Contracts	oks_ocint_pub.oc_interface	OKS Queue

• CS or Install Base queue, when consumed, creates/updates the install base of old fulfilled order lines that have been fulfilled prior to 11.5.6 upgrade. One time usage.

• Between Oracle Marketing and Fund & Budgets, the OZF queue is consumed, depending on which program and version of applicatons is used. This basically eats into the budgeted funds of the marketing campaign allocated in these applications. These accruals come as price adjustments or promotions from order management. Please make sure you take an expert’s opinion in these applications for more informaton.

• CN queue is consumed when you collect orders to pay commission for the orders that are booked. This obviously suggests of a business process where booked orders are used as a source to pay commisssion to the sales force.

• When OKS queue is consumed, it creates a service contract (Service Agreement or Extended Warranty or Warranty depending on the profile option OKS: OM interface Contract Category setup) from order management when a service item is associated to a line that creates/updates the install base. The association of Service Item to the Inventory Items that is trackable in Install Base is done using Service tab in the order lines.

Please refer to respective product documentation for more information on these programs and their functionality.

How to generate a custom queue?

All we need to do is to subscribe your application to this queue. To achieve we enter our custom application name (for example SHIP)  as a lookup code in the lookup type ASO_ORDER_FEEDBACK_CRM_APPS. This will generate the queue into this code as soon the order is booked. To consume this we can use the package aso_order_feedback_pub.get_notice with this application name (SHIP) to read the queue followed by actual work we want to do using the information available in this queue like shipping a custom application.

How to contain the growth of this table if we do not have any consuming application and what is the impact?

Obviously the simplest way to contain this is by disabling the subscribers in the lookup type.But before doing so we should make sure we have no need to run the programs that are mentioned above, otherwise it can cause issues with a number of applications. If we have no business need to run the program, we can safely disable them. This will stop further growth though you are entering new orders and booking them.

What do we do with my existing data in the queue table if we do not have any need to run the programs?

Please refer to the metalink note 181410.1 for more information on this.

What we did?

Following the instructions in the above note, first we dropped the queue. Then after careful analysis we disabled the subscribers to this queue as we do not use them. Then we recreated the queue.