User Guide 

BBB Intelligence for Oracle Financials R11i
 

Last updated on September 6, 2007

1. What is BBB Intelligence?

Oracle Order Management is an incredibly flexible and functional module for meeting 90% of the order processing needs of most companies. Unfortunately, its design focuses primarily on transacting orders, not reporting on orders. Thus, by most end user standards, it is poorly equipped to meet the most basic financial reporting requirements.

Two primary areas are not addressed by the Order Management module’s vanilla functionality. First, transaction audit detail is not provided. If a change is made to an order or order line, the system simply overwrites the old value with the new value. When you run a bookings report for an order after having changed a line’s price from $10 to $20, there is no visibility to the fact that the line had once had been priced at $10. This lack of change history makes it impossible to generate accurate historical bookings figures. For example, when trying to calculate net bookings for a designated period, the totals on a bookings report that is run on February 1st for the month of January may not reconcile to the totals of the same report run for the same period the very next day. This is because Oracle Order Management allows users to update the value of booked lines without recording the net value change and the date the change occurred. Therefore, a $2,000 order booked on January 31st, could suddenly become a $5,000 order during February, yet still maintain it’s January 31st booked date. Most companies would like to see the system reflect a $2,000 booking for January and a net change booking of an additional $3,000 for February.

Second, Order Management does not provide a mechanism to easily access meaningful and comprehensive summary and detail order information from bookings to revenue recognition online with fully integrated user interfaces or reports. Many reports are provided by the Order Management module, however they are typically insufficient to answer key business questions, are provided in formats that are difficult to get to the bottom line or do not allow the user to sort and/or filter data as needed. Questions like the following are often impossible or extremely time consuming to answer:

  • Which sales territory had the greatest net bookings last month? 

  • How much did Joe Smith book last August? 

  • What was the ratio of booked returns to orders? Bookings to billings? 

  • How much did we book yesterday? By Customer? By Territory? By Salesperson? By Product Category? 

  • What’s the net booking trend for the week? 

  • How many times did the order change before it shipped? Who changed it and when?

  • What’s our revenue backlog by item category for the current month? Last month?. 


For these reasons, companies spend months and hundreds of thousands of dollars developing, maintaining and/or implementing custom solutions, data warehouses and/or OLAP tools focused exclusively on the area we like to call BBB or Bookings, Backlog and Billings.

To provide companies with immediate functionality to fill these gaps, reduce IT expenses and alleviate month end headaches for financial analysts we have developed BBB Intelligence for Oracle Financials. BBB Intelligence may be broken down into two components: The Booking Transaction History Module and the BBB Report Suite. 
The BTH Engine
BTH engine
commissions revenue backlog shipments bookings
Bookings Backlog Shipments Revenue Commissions
View Sample Reports

2. Who will Benefit?

For anyone whose job depends on fast, reliable, flexible and accurate reporting for Bookings, Backlog, Shipments, Invoices and Revenue, this solution is a must have. 

The BTH module provides the corner stone of the solution. By capturing and storing changes to bookings, users can view and report on order changes as they occur. With historical booking transactions, net bookings can be accurately calculated for any period. Accurate net bookings is essential for calculating accurate historical backlog. Additionally, the BTH module maintains a complete history of what orders changed, when, by how much, by who and optionally why the change was made. The BTH provides an excellent source of order metrics, which can provide a better understanding of activities in the order entry cycle. For example, you can determine which customers are requesting multiple order changes, how often sales person assignments are modified, and who is making the most changes to orders, etc. 

The BBB Report Suite provides financial analysts, managers and controllers with reports that can be submitted from the concurrent manager and business views for use with Oracle Discoverer to deliver immediate figures on the financial health of the company. All the reports provide flexible filter and sort options that allow you to view your BBB data by critical order attributes like Customer, Territory, Sales Person, Order, Invoice and Item Category. All the reports and views are engineered with a layer of logic that allows users to distinguish between those orders and lines which are bound for AR are those which are not.
3. Understanding the BTH Engine

Account Set Processing 

In order to accurately track valuation changes to booked orders, the BTH engine utilizes mechanism that should be familiar to accountants and financial analysts called “account set processing”. Essentially, the BTH engine transforms your Order Management module into a sub ledger by creating double entry journals of debits and credits every time a change is made to a booked order that affects its value with respect to date and salesperson. Thus, the change in order value becomes the amount of the debit or credit and salesperson takes the place of an account. See the example below. 

Axis verses Value Columns

Another concept that is important to understand when using the BTH engine is Axis verses Value columns. Based on your requirements for tracking and reporting changes to bookings, the BTH engine can configured specifically to meet your needs. To fully understand how to custom configure the BTH engine, and how changes to the configuration will impact your data and reports, you must understand the roles these two column types play and how to set them up.

An Axis column is one that is tracked to group or bucket net bookings. For example, booked date and salesperson are pre-configured in the BTH engine to function as Axis columns. This means that the BTH engine will store transaction data in the BTH table so that a net booking figure can be derived for any date, salesperson or combination of the two. Any non-value impacting column that is within or has a foreign key reference to the orders, lines or sales credits tables may be set up as an Axis column. To initialize a new axis column you must do the following: (see Using the Online Forms)

1. Register the column’s table in the Define BTH Tables form.
2. Set the Trigger Flag to “Yes”
3. Assign the column to a BTH Column in the Define BTH Columns form.
4. Set the Trigger Flag to “Yes”
5. Re-Compile the engine by clicking the COMPILE button.
6. Check for errors.

A Value column is one that directly affects the value of an order when changed. These columns have already been pre-configured in the BTH engine to trigger the creation of an account set transaction whenever they are modified. They are ordered quantity, cancelled quantity, selling price, currency code and currency conversion rate.


4. An Example of Booking Transaction History

By default, the BTH engine tracks all transactions which affect the value of an order or sales credit and records the date and time the transaction took place. If you book an order on the 31st for $5,000, and the following day change the sales person for the associated sales credit on the order, the BTH will reflect the original $5,000 booking on the 31st. It will then show a negative booking of $5,000 on the 1st followed by a new booking of $5,000 on the 1st for the new sales person. Thus the net bookings for the first sales person on the 31st is $5,000 and -$5,000 on the 1st, which net to $0. The new sales person will be allocated with a $5,000 booking on the 1st. Oracle Order Management will not and can not do this!

With BTH:

      Extended Sales    
Line Book Date Qty Price Amount Person Percent Trx ID
1* 31-Jan 1 $5,000 $5,000 Tom 100% 1
1 1-Feb -1 $5,000 ($5,000) Tom 100% 2
1 1-Feb 1 $5,000 $5,000 Mary 100% 3


Note: Here you see the flow of bookings credit go from Tom to Mary.

“*” (Denotes the state of the line as originally booked.)

Without BTH:

  Extended Sales
Line Book Date

Qty

 Price Amount Person Percent
1 31-Jan 1 $5,000 $5,000 Mary 100%

Note: This incorrectly reflects a $5,000 booking credited to Mary on 31-Jan.

Because BTH transactions are processed in real time, reports can be run at any time, consistently giving accurate results when run over any period. In addition, because the information is stored in your APPS database just like any other table that is used by Oracle Financials, it is readily available for use with custom reports, ad hoc queries, data warehouse interfaces, query tools, etc… Results become consistent, accurate and timely.

5. How do I access BBB Intelligence?

BBB Intelligence is designed to be fully integrated with your Oracle Financials installation. It is a registered application in AOL just like any other module within Oracle Financials. The online forms and concurrent reports and programs can be installed into any responsibility or menu you wish. As a default, we install the forms and reports under the Order Management Super User responsibility. Under the main menu, you will see a menu option called “Booking Transaction History”. Beneath this menu, you will find 3 forms:

  • View BTH by Order

  • Define BTH Tables

  • Define BTH Columns

6. Using the Online Forms

View BTH by Order

The View Booking Transaction History Screen (View Only)

This read only form provides on-line visibility of changes to booked orders, calculates the net change from the original booked value and the current value, drills down to related return activity and drills down to PTO/ATO model options.

Order Zone

The Order Zone allows the user to select the order number that will be viewed. Do not enter query mode to select an order. Simply enter the order number and click on the find button. The form will then query the order lines automatically.

Order Zone Fields

Order - The order field shows the current order number that is being viewed. To change to a new order, simply enter a new order number, and then click the find button. You do not have to use enter query or execute query. After the field is entered, and you have clicked the find button, the order information will automatically be queried.

Sold To Customer - The sold to customer name is displayed for the current order. This is a view only field.

Customer No. - The customer number is displayed for the current order. This is a view only field.

Currency - The transaction currency is displayed for the current order.

Order Type - This field displays the order type (order level transaction type) for the current order. This is a view only field.

Show Children - The Show Children field determines whether the query will return parent and children lines or only parent (i.e. Model) lines. If “Yes” is selected, all order lines will be shown in the next block. If “No” is chosen, only parent lines will be displayed. The default is “Yes”. (mandatory)


BTH Transactions Zone

The BTH Transactions Zone displays change details for the selected order. The information may be queried by line number or creation date.

BTH Transactions Zone Fields

Drill Down - Click this field to view any details for this line. Details may include configuration options for the line or a linked return line.

Line - The line number for the current record (concatenates line number, shipment number and option number). In addition, a line may have an ‘*’, a ‘+’, or both. If an ‘*’ is displayed, then this record represents the line as it was originally booked. If a ‘+’ is displayed, then there is either child line or return line linked to this line. To view the additional information, simply click the “Drill Down” (DD) button to the left of the line field. This field may be queried.

Date – The date when transaction was created and/or the order line was booked or modified. This field may be queried.

Item - Part number for the current line. This is a view only field.

Ord Quantity - Quantity ordered for the current line. If the quantity is negative, then this line is showing the “out” transaction of an account set transaction. This could be caused by a cancellation or a change in price or ordered quantity. For every order line change, there will be a corresponding “in” and “out” transaction in the View Booking Transaction History screen. We call this “account set processing” since it is similar to making a debit and credit in an accounting journal entry. For example, today I enter an order line with an order quantity of 5. Tomorrow, I change the quantity to 4. If I view the Booking Transaction History screen, I will see one line with a quantity of 5 with today’s date, one line with a quantity of -5 with tomorrow’s date, and finally a third line with a quantity of 4 with tomorrow’s date. These “in” and “out” transactions are important because they allow us to see all transactions that have occurred for a specific period of time for a particular Sales Credit. This is a view only field.

Canc Quantity - Quantity canceled for the current line. If the quantity is negative, then this record is showing an “out” transaction for the order line. For every order line change, there will be a corresponding “in” and “out” transaction in the Booking Transaction History screen. For example, today I book a line with a quantity of 3. Tomorrow, I cancel the line. If I view the Booking Transaction History screen, I will see one record with an ordered quantity of 3 and a canceled quantity of null with today’s date. The second record will show an ordered quantity of –3 and a canceled quantity of null with tomorrow’s date. This transaction is “backing out” the original booking. Finally, the third record will show an ordered quantity of 0 and a canceled quantity of 3 with tomorrow’s date. These “in” and “out” transactions are important because they allow us to see all transactions that have occurred for a specific period of time for a particular Sales Credit. This is a view only field.

Selling Price - This is the selling price for the current line. This is a view only field.

Sales Rep - This is the salesperson assigned to the associated sales credit for the current transaction line. A single order line may show up multiple times in the Booking Transaction History screen if there is a sales credit split. Take note of the “%” field which indicates the split percentage allocated to the sales person for this line. This is a view only field.

Quota – This shows whether the sales credit for the line is eligible for quota or not. Every line must have a total sum of 100% quota assigned to one or more salespersons. However, Oracle Order Management will allow any percent of non-quota sales credits to be assigned to the line. Non-quota sales credits may be used by commissions managers to track non-primary salespeople that contributed to a sale in addition to maintaining accurate revenue bearing sales credits that affect quota for primary salespeople. This is a view only field.

% - The percent of the order line for which the salesperson is receiving credit. The sum of the quota percent for a given line must add up to 100%. The percentage of non-quota sales credits is unlimited. This is a view only field.

[ ] - This symbol represents that a descriptive flexfield exists for this zone. Descriptive Flexfields allow users to capture user-defined information for a record. To view this information, simply tab over to this field and a window will automatically pop open if the descriptive flexfield is enabled. (For more information on enabling a descriptive flexfield, see your Oracle Applications Developer manual.) This is a view only field.

Returns/Children Zone

The Returns/Children zone of the Booking Transaction History screen shows all child and return activity against a particular line. To get to this zone, click on the “Drill Down” (DD) button for any transaction line that has a ‘+’ in the Line Number field. You may subsequently drill down again for any record that contains a ‘+’ in the Line Number field.

Return Zone Fields

The fields are identical to the previous zone.

Define BTH Tables

The Define BTH Tables Screen

BTH Table Zone 

This form displays the pre-defined tables that are used by the BTH engine. It may also be used to register user selected tables for use with the BTH engine. Each table must have a direct or indirect foreign key reference to OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, or OE_SALES_CREDITS. 

There are two reasons for registering a table in this form.

1. To track changes to a column value on the table.
This type of table should be defined as Trigger Based = Yes.
2. To reference a column value on the table at the time of another column’s change.
This type table of table should be defined as Trigger Based = No.

BTH Table Zone Fields
Table Name - Enter the name of the table that will be used by the BTH engine.
(mandatory)

View Name - For multi-org releases, enter the name of the view that represents the table. (optional)

Prefix - Assign a unique prefix to each table. It is generally good to use the first letter of each of the words in the table name. For example, OOH is used for OE_ORDER_HEADERS_ALL, and OOL for OE_ORDER_LINES_ALL. Do not use Oracle reserved words, such as DESC or ABS. (mandatory)

Primary Key - Enter the primary key of the table. This field only needs to be entered if the table will be defined as a trigger based table. (optional)

Join Text - Enter the SQL text that joins this table to header_id, sales_credit_id or line_id. Begin the text with FROM. DO NOT enter the SELECT or INTO clauses, or put a semi-colon (;) at the end of the statement. Single quotation marks may not be used. The BTH engine will automatically take care of creating the correct SELECT piece of the statement. You may use the built in parameters P_HEADER_ID, P_SALES_CREDIT_ID, and/or P_LINE_ID in order to make the connection to the desired tables. (optional) 
For example, if you wanted to enable tracking on the RA_CUSTOMERS table, the Join Text might look like this:

FROM   RA_CUSTOMERS       A,
       OE_ORDER_HEADERS   B
WHERE  B.HEADER_ID = P_HEADER_ID
AND    B.SOLD_TO_ORG_ID = A.CUSTOMER_ID

Warning: You may NOT directly reference a trigger based table in the Join Text of its own definition. This will result in a table mutation error. 


Cursor Text - The Cursor Text field is used for trigger based tables that require a one to many relationship with another table. This feature is used primarily for the relationship of OE_ORDER_LINES_ALL to OE_SALES_CREDITS. The entire SQL statement must be entered. Additionally, line_id and sales_credit_id must be referenced by the alias LINE_ID and SALES_CREDIT_ID, even if P_LINE_ID and P_SALES_CREDIT_ID are used. The current table may not call itself. Single quotation marks may not be used. (optional) The following example was taken from the seed data for the OE_ORDER_LINES_ALL table:

SELECT P_LINE_ID LINE_ID,A.SALES_CREDIT_ID
FROM   SO_SALES_CREDITS A
WHERE  A.HEADER_ID = P_HEADER_ID
AND   (A.LINE_ID = P_LINE_ID OR
      (A.LINE_ID IS NULL
AND   NOT EXISTS (SELECT 1
      FROM OE_SALES_CREDITS C,
      OE_SALES_CREDIT_TYPES D
      WHERE C.HEADER_ID = P_HEADER_ID
      AND C.LINE_ID = P_LINE_ID
      AND C.SALES_CREDIT_TYPE_ID = D.SALES_CREDIT_TYPE_ID
      AND D.QUOTA_FLAG = CHR(89))))

Trigger Based - Enter Yes or No to declare whether this table will track changes to column values in the table, thereby generating new records when a change is made, or whether column values in this table will be captured as reference data when a change is made to tracked column. OE_LINES_ALL is set to “Yes” because we want to create a new record in the history table whenever one of the tracked OE_LINES_ALL columns is modified. RA_TERRITORIES is set to “No”, because we only want to reference territory information when another column is changed from another table. (mandatory)

Trigger Condition - Enter a trigger condition for trigger based tables if specific criteria must be met to allow the table trigger to execute. For example, the OE_ORDER_LINES_ALL table is pre-configured to only execute its trigger if the order line is booked. (optional)

Error Code - Enter a unique numerical error code. This is used during the error handling routines and will alert the user if a problem has occurred with this specific table. (mandatory)

Define BTH Columns

The Define BTH Columns Screen

This form is used for setting the BTH Engine Parameters, Compiling the Engine to initialize configuration changes and for defining both columns to be tracked and/or referenced in the BTH table.

Engine Parameters Zone

The Engine Parameters Zone allows the user to define the frequency at which the BTH engine will write changes to the BTH table.

Engine Parameters Zone Fields

Summary Period - Summary period can be set to “ALL” or “FILTER”. This field regulates whether changes to tracked columns will be inserted as distinct transaction records or whether changes will merely cause an update of the existing transaction record. If “ALL” is selected, every change that affects a tracked column will be written to the history table, with an account set “in and out” transaction record. If “FILTER” is selected, the engine will use the frequency parameters to determine how often to insert records to account for changes verses updating the existing BTH record. (mandatory)

Frequency - Determines the time window for use by the summary period filter. Time windows may be set up by minute, hours, days, weeks, or months. When set, only changes that occur outside of the time window set by the frequency will produce an account set transaction, otherwise the BTH record will be updated. (optional)

For Example, you may set the frequency to only insert new BTH records every 5 minutes. This means that if a change to the order is made within 5 minutes after booking it, the BTH engine will not “track” the change by writing a complete change history, but merely overwrite the change to eliminate unnecessary records. This may be useful if tracking every change is undesirable because it creates too many BTH records and/or the company is only really interested in net changes per hour or day, etc. 


Compile Zone

The Compile Zone allows the user to re-compile the BTH engine. This is done to initialize any changes that have been made to the overall BTH configuration, like adding or removing tables or columns, and/or changing engine parameters. This is intended for use by IT personnel and is equipped with an error viewing feature.

Compile Zone Fields

Compile - The compile button re-compiles the BTH engine when clicked. WARNING: Use this field carefully! Only compile when a table or column definition has been modified. Immediately after compiling, go to the Errors screen to check for compilation errors.

Show Errors – This button opens the Errors screen, allowing the user to validate that compilation was successful and if not, what objects are erring and why.

Total Number of Errors – This field displays the total number of errors in the BTH error table. 

BTH Column Assignments Zone

The BTH Column Assignments Zone allows the user to define which columns will be used for transaction history. There are two types of columns that may be defined, Tracked or Referenced. A tracked column, when modified in the database, causes the BTH engine to insert or update BTH records. A reference column, when changed, causes no action by the engine. Its value, however, is stored in the BTH table when a tracked column is modified. 

For a column to be tracked, it’s table must be defined as a “Trigger Based” table and it must be defined in BTH column assignments with a “Trigger Flag” set to “Yes”. Otherwise, the BTH engine will treat it as a referenced column.

The BBB Intelligence module comes pre-configured with seeded BTH column assignments to track any change that affects the value of an order. However, users may create, delete or modify user defined column assignments as desired. The pre-configured seed data cannot be modified.

BTH Column Assignments Zone Fields

Source Table Name - Select the table name of the column you wish to capture. The table must be defined in the Define Table Definitions form before it can be used. (mandatory)

Source Column Name - Select the column name you wish to capture. (mandatory)

User Column Name - Enter a column name that is understandable to the users. This may be used on reports to give a cryptic column name, like “ATTRIBUTE1” a user friendly name. For example, if ATTRIBUTE9 in the RA_SALESREPS table stores the salespersons age, name the User Column Name “Sales Person Age”. (mandatory)

BTH Column - This field determines where in the BTH table the captured value will be stored. Columns named ATTRIBUTES1 - 20 are provided to allow users to configure the engine to capture additional column values as desired. (mandatory)

Trigger Flag - Set the Trigger Flag to “Yes” if the column will be tracked (creates an account set transaction when a change occurs), or “No” if it is only to be referenced (simply captures the column value whenever a tracked column is modified). (mandatory)

Grouping of Errors Zone

Grouping of Errors Zone

This zone displays a count of database errors returned by the BTH engine during compilation or during transaction generation grouped by date, procedure name and error message. You can drill down to view the error details.

Grouping of Errors Zone Fields (View Only)

Drill Down – Click this button to drill down to error details.

Count – This field displays a count of errors for the indicated date, procedure name and error message.

Date – This field displays the error date.

Procedure Name – This field displays the name of the procedure that raised the error.

Message Name – This field displays the type of error that was raised.

7. Understanding the Information

When you run a report, or view the booking transaction history information on-line, you may see multiple records corresponding to a single order line. This will either reflect the fact that a line has multiple salesperson splits, or that a change has occurred. 

The Booking Transaction History engine works by recording ledger like debit and credit entries against each sales order line as changes are made. We commonly refer to this as “account set processing”. When a new sales order line is booked in Order Management, one or many records may be inserted into the history table, depending on the number of salesperson splits. For example, a user enters a new order line for $100, with a sales credit of 35% allocated to Tom, and 65% allocated to Mary. 

In the Order Lines zone, we see:

Line Date Qty Price Amount
1 31-Jan 1 $100 $100


(Remember that we have assigned two sales credits, Tom and Mary, to the order header.)

In the Oracle Order Level Sales Credits zone, we see:

Sales Person Percent
Tom 35%
Mary 65%


In the Booking Transaction History screen we see a combination of the two:

           
Line Book Date Qty Price Sales Person Percent Trx ID
1* 31-Jan 1 $100 Tom 35% 1
1* 31-Jan 1 $100 Mary 65% 2


Note that the booked value for Tom is $35, $100*35%, and $65 for Mary.

Now, tomorrow, we decide that Tom should not receive any credit, but instead it should be given to the Northeast Region. In the Oracle’s Order Line Sales Credit screen, we simply delete the sales credit for Tom and insert a sales credit for the Northeast Region.

In the Oracle Sales Credits Screen we see:

Sales Person Percent
Northeast Region 35%
Mary 65%


(Notice that we have lost all record that a credit used to be allocated to Tom!)

In the Booking Transaction History screen we now see:

           
Line Book Date Qty Price Sales Person Percent Trx ID
1* 31-Jan 1 $100 Tom 35% 1
1* 31-Jan 1 $100 Mary 65% 2
1 1-Feb -1 $100 Tom 35% 3
1 1-Feb 1 $100 Northeast Region 35% 4


An ‘*’ next to the line number indicates that the record represents the original booked order line, recording all the values as they were when the line was first booked. You can very quickly scan an order to see which lines have been changed by simply looking for lines without asterisks (*).

A ‘+’ next to a line number indicates that there are either child configuration lines or return lines associated with that order line. To drill down, simply click the Drill Down button to the left of the line number with a ‘+’ and the additional information will be displayed in a new window.

8. Running the BBB Suite of Reports

Note: The BBB Suite of Reports have been de-supported as of 1/1/2005. This information is provided for reference purposes only.


To access the Run Reports screen, follow the navigation path in Order Management:

+ Reports, Requests
   - Run Reports


 

9. Key Report Functionality

Accounting Affect Flag 
The accounting affect flag is an attribute in Oracle Receivables which is assigned to each Transaction Type. It is represented on the Define Transaction Types form as the “Open Receivable” field. It determines whether customer transactions created within the transaction type will create a receivables balance. It is offered as a parameter for all BBB reports to allow the user to include or exclude orders and/or invoices that are associated with non receivable generating transactions.

Billable Flag
The AR Bound or Receivables Bound flag denotes whether an Order Management order line or return line is destined to be or has been interfaced to Oracle Receivables to generate an invoice or credit memo. It is provided on all BBB reports to allow the user to include or exclude those order lines that will impact revenue and or receivables. For example, if a user runs a backlog report with the “Billable Flag” parameter set to “No”, they will fetch only orders like internal orders or demo orders which do not, have not and will not interface to Receivables. If they leave this parameter blank, they will select all booked backlog lines. If the parameter is set to “Yes”, the report will only show lines which have or are destined to interface to Receivables.

Order Type Sets
Order Management uses Order Types/Transaction Types to direct orders and/or order lines through particular actions, processes and/or account generation mechanisms. It is often convenient and sometimes necessary to filter the data of a report by one or more order types. In order to provide maximum filter flexibility when running reports, we introduced the Order Type Set. An Order Type Set is a value set construct whereby the user may assign one or many order types to a single set or group. This set can then be used to conveniently and expeditiously submit a BBB report for all order types within that group. Alternatively, it can be used to exclude all order types within the set for a particular submission of a BBB report. 

Multi-Org Compliance
Every BBB Intelligence report is designed to be multi-org compatible, regardless of whether the report is run from the AOL Concurrent Manager or from the Oracle Reports designer. When a report is run from the AOL concurrent manager, the “SRW INIT” user exit is executed to activate the sharing of AOL libraries by the report as well as initialize the client info database parameter. This will restrict the views used in the report to access only the operating unit that corresponds to the Application Responsibility from which the report was submitted.

Multi-Currency Support
Every BBB Intelligence report displays currency values in the base currency. The base currency is the currency associated with the Set of Books for the given Operating Unit. Where space allows, reports will show the transaction currency code. Standard Oracle Application currency conversion mechanisms are employed to calculate and display formatted currency values on all reports.

Item Category Flexfield Support
Almost all the BBB reports display item categories for each record. Many of the reports provide a value by item category summary at the end of the report. For maximum reporting flexibility, the user may determine from which category set to derive the item’s category value at the time of report submission. Some reports also provide a category range selection parameter, which enables the user to run the report for a range of categories and/or individual category segment values.

RA Territory Flexfield Support
Almost every report provides the user with the ability to run a report for a given territory, and displays the full flexfield value for each record on the report. Additionally, the EXPORT reports create a distinct column for each segment in the flexfield so that the values for each segment can easily and independently be sorted in the spreadsheet.

Export to Spreadsheet Support
All of the BBB Intelligence reports may be exported from the Oracle Applications report viewing browser to an Excel spreadsheet. However, to accommodate clean formatting and a lack of truncation, we have designed several of our reports specifically for EXPORT. They cannot be printed because their extended width would not fit on a single page. 

10. BTH and BBB Reports

The BBB Report Suite is comprised of 15 BBB reports, 4 BTH reports and 1 pl/sql program. BBB reports are designed to provide line level detail to item category level summary information across the entire order life cycle, from booking to revenue recognition. BTH reports are designed to provide detail and summary level audit history. They are the reports that reveal all changes made to a booked order and provide a net change value that calculates the difference between an order’s original booked value and it’s value on any subsequent date. The pl/sql program populates the BBB summary table that is used by the BBB Revenue Backlog Summary Report. This summary table architecture facilitates optimal runtime performance of the BBB Revenue Backlog Summary Report.

Bookings

Backlog

Shipments

Commissions

Invoices

Revenue

Absolute BBB Revenue Recognition Detail Report
Absolute BBB Unearned Revenue by Customer and Period Recognized Report
Absolute BBB Unearned Revenue by Customer and Period Recognized Report for EXPORT
Absolute Populate BBB Revenue Summary Program

Report Descriptions

  1. Absolute BTH Booking Details by Customer/Order 

    Filename: BTH_COD.rdf

    Parameters 

    Name  Req'd Description
    Start Date Yes Select order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select order/return lines with a booked date lesser than or equal to this date.
    Sold to Customer  No Select booked orders/returns for this Sold to Customer Name.
    Order Number No  Select booked orders/returns for this Order or Return number.
    Accounting Affect Flag No Select orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Quota Flag No Select orders/returns that affect quota (sales credit commission and revenue.)
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides transaction details from the Booking Transaction History table. It is designed to show changes to booked orders grouped by or for a selected Customer and/or Order Number. It provides all changes in quantity, price, sales credit and sales credit percent on the order over it’s life and calculates the current booked value, original booked value and net change in bookings for the period selected.

  2. Absolute BTH Booking Details by Territory/Sales Person

    Filename: BTH_TSD.rdf

    Parameters

    Name 

    Req'd

    Description

    Start Date

    Yes

    		 Select order/return lines with a booked date greater than or equal to this date.

    End Date

    Yes 

    		 Select order/return lines with a booked date lesser than or equal to this date.

    Territory

    No

    Select booked orders/returns for this Sales Territory Flexfield.

    Sales Person

    No

    Select booked orders/returns credited to this Sales Person.

    Accounting Affect Flag

    No

    		 Select orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.

    Billable Flag

    No

    		 Select orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.

    Quota Flag

    No

    		 Select orders/returns that affect quota (sales credit commission and revenue.)

    Order Type Set - Include

    No

    		 Include orders/returns for a specific order type or set of order types.

    Order Type Set - Exclude

    No

    		 Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides transaction details from the Booking Transaction History table. It is designed to show changes to booked orders grouped by or for a selected Territory Flexfield and/or Sales Person. It provides all changes in quantity, price, sales credit and sales credit percent on the order over it’s life and calculates the current booked value, original booked value and net change in bookings for the period selected.

  3. Absolute BTH Booking Summary by Customer

    Filename: BTH_CS.rdf

    Parameters 

    Name  Req'd Description
    Start Date Yes Select order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select order/return lines with a booked date lesser than or equal to this date.
    Sold to Customer  No Select booked orders/returns for this Sold to Customer Name.
    Accounting Affect Flag No Select orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Quota Flag No Select orders/returns that affect quota (sales credit commission and revenue.)
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides a transaction summary of the Booking Transaction History table. It is designed to show net changes to booked orders grouped and summarized by Customer. It accounts for all changes in quantity, price, sales credit and sales credit percent on booked orders over their life and calculates the current booked value, original booked value and net change in bookings for the period selected.

  4. Absolute BTH Booking Summary by Territory/Sales Person

    Filename: BTH_TSS.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select order/return lines with a booked date lesser than or equal to this date.
    Territory No

    Select booked orders/returns for this Sales Territory Flexfield.
    Sales Person No

    Select booked orders/returns credited to this Sales Person.
    Accounting Affect Flag No Select orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Quota Flag No Select orders/returns that affect quota (sales credit commission and revenue.)
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides a transaction summary of the Booking Transaction History table. It is designed to show net changes to booked orders grouped and summarized by or for a selected Territory Flexfield and/or Sales Person. It accounts for all changes in quantity, price, sales credit and sales credit percent on booked orders over their life and calculates the current booked value, original booked value and net change in bookings for the period selected.

  5. Absolute BBB Booking Detail Report

    Filename: BBB_BKD.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select order/return lines with a booked date lesser than or equal to this date.
    Territory No

    Select booked orders/returns for this Sales Territory Flexfield.
    Sales Person No

    Select booked orders/returns credited to this Sales Person.
    Accounting Affect Flag No Select booked orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Category Set Yes Select the category set from which to display the item category flexfield structure for the selected order/return lines.
    Item Category Low No Select booked orders/returns having this item category flexfield or an individual segment or segments in this flexfield of equivalent or greater alphanumeric value.
    Item Category High No Select booked orders/returns having this item category flexfield or an individual segment or segments in this flexfield of equivalent or lesser alphanumeric value.
    Show Item Column? Yes Optionally display the item column.
    Show Returns? No Include (null), exclude (No) or select return lines only (Yes).
    Order Number No Select booked orders/returns for this Order or Return number.
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides booking details for a range of book dates. Details are provided at the order line level and summarized by item category at the end of the report. This report may be submitted for printing or for export to a spreadsheet to facilitate customized ordering, sorting and calculations.

  6. Absolute BBB Booking Matrix Summary by Territory/Item Category Report

    Filename: BBB_BKMS.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select order/return lines with a booked date lesser than or equal to this date.
    Territory No

    Select booked orders/returns for this Sales Territory Flexfield.
    Sales Person No

    Select booked orders/returns credited to this Sales Person.
    Accounting Affect Flag No Select booked orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Show Details? Yes Show the supporting details for this report following the matrix summary.
    Category Set Yes Select the category set from which to display the item category flexfield structure for the selected order/return lines.
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides a net bookings matrix summary for a date range of book dates. The axis for this report are Territory and Item Category. Both booking value and quantity are calculated and displayed for each intersection of Territory and Item Category. This report is an excellent resource for product and/or commissions managers wishing to get a quick snapshot of bookings by attributes meaningful to their objectives.

  7. Absolute BBB Backlog Detail Report

    Filename: BBB_BLD.rdf

    Parameters
    Name  Req'd Description
    Created by Yes Select booked orders/returns that were created by this user.
    Show Revenue Backlog? Yes  Select the backlog derivation method you wish to use for this submission. "Yes" provides Revenue Backlog: Bookings less Earned Revenue. "No" provides Fulfillment Backlog: Booked Lines less Fulfilled (Shipped or Invoiced) Lines.
    Reflect GL Posted Trx Only? Yes If Show Revenue Backlog is selected, then you may determine whether all revenue or only posted revenue shall be used to consume backlog for this submission of the report.
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Show Zero Value Lines? No Optionally include or exclude the display lines that have zero amount values.
    Category Set Yes Select the category set from which to display the item category flexfield structure for the selected order/return lines.
    Item Category Low No Select booked orders/returns having this item category flexfield or an individual segment or segments in this flexfield of equivalent or greater alphanumeric value.
    Item Category High No Select booked orders/returns having this item category flexfield or an individual segment or segments in this flexfield of equivalent or lesser alphanumeric value.
    Show Returns? No Include (null), exclude (No) or select return lines only (Yes).
    Order Number No Select booked orders/returns for this Order or Return number.
    Show Column Headings  Yes Optionally toggle the display of column headings on the report for all but the first page.
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.


    Function/Description

    This report provides Fulfillment Backlog or Revenue Backlog detail, with several filter options. Fulfillment Backlog is defined as bookings less shipments/return related material receipts. For non-shippable order lines or non-receivable return lines, the quantity invoiced or credited is used to consume the fulfillment backlog. Revenue Backlog is defined as bookings less revenue recognized. This dictates that only orders that are eligible for revenue recognition are included when this option is selected. For an order line to be consumed from the Revenue Backlog, it must first invoice and then its value must be distributed to an EARNED revenue distribution line with a GL date less than or equal to the current date.

  8. Absolute BBB Margin Fulfillment Backlog Report

    Filename: BBB_MBK.rdf

    Parameters
    Name  Req'd Description
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Category Set Yes 
    For Export Yes 
    Show Column Headings Yes 
    Order Number No Select booked orders/returns for this Order or Return number.
    Item Category Low No
    Item Category High No
    Territory Low No
    Territory High No
    Group Option1 No
    Group Option2 No
    Group Option3 No
    Group Option4 No
    Account Low No
    Account  High No
    Show Summary Only Yes 
    Order Type Set Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides....

  9. Absolute BBB Revenue Backlog Summary Report

    Filename: BBB_RBLS.rdf

    Parameters
    Name  Req'd Description
    Period From Yes Select order/return/invoice and memo lines with a transaction date greater than or equal to this period.
    Period To Yes  Select order/return/invoice and memo with a transaction date lesser than or equal to this period.
    Show Period Name? Yes
    Show Shipment Summary? Yes
    Show Order Category? Yes
    Show Backlog Status? Yes
    Category Set Yes
    Item Category Low No
    Item Category High No
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.


    Function/Description

    This report is the crown jewel of the BBB reports. It provides a comprehensive summary of all BBB transactions for a period by item category to calculate ending revenue backlog. It allows the user to break down the summary of transactions by Shippable Status, Order Category and Backlog Status. It calculates the beginning backlog of the period, then adds net bookings, subtracts revenue recognized and summarizes ending backlog by item category. This is the report that your CFO or Controller is looking for to get a real time month end summary of bookings, shipments, revenue and ending revenue backlog with just a click of the mouse! 

  10. Absolute BBB Margin Shipment Report

    Filename: BBB_MSH.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select order/return lines with a shipped date greater than or equal to this date.
    End Date Yes Select order/return lines with a shipped date lesser than or equal to this date.
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    For Export Yes
    Show Column Headings Yes
    Order Number No Select booked orders/returns for this Order or Return number.
    Account Low No
    Account  High No
    Category Set Yes
    Item Category Low No
    Item Category High No
    Territory Low No
    Territory High No
    Group Option1 No
    Group Option2 No
    Group Option3 No
    Group Option4 No
    Show Summary Only Yes
    Order Type Set Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides TBW...

  11. Absolute BBB Shipment Detail Report

    Filename: BBB_SHD.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select order/return lines with a shipped date greater than or equal to this date.
    End Date Yes  Select order/return lines with a shipped date lesser than or equal to this date.
    Territory No
    Sales Person No
    Accounting Affect Flag No Select shipped orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Show Item Column? Yes
    Show Column Headings? Yes
    Order by Columns (i.e. 1,2,3) No
    Include Return Receipts? Yes
    Category Set Yes
    Order Number No Select booked orders/returns for this Order or Return number.
    Order Type Set - Include No Include orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides line level detail for a range of shipment dates. It only retrieves order lines that have been shipped in the system or return lines that have been received in the system. It does not reflect line items defined in inventory non-shippable, such as service or maintenance. This report may be submitted for printing or for export to a spreadsheet to facilitate customized ordering, sorting and calculations.

  12. Absolute BBB Commission Basis Detail Report

    Filename: BBB_CBD.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with an invoiced date greater than or equal to this date.
    End Date Yes  Select invoice/memo lines with an invoiced date lesser than or equal to this date.
    Territory No
    Sales Person No
    Accounting Affect Flag No Select booked orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Show Report Headings? Yes
    Quota Flag No
    Category Set Yes
    Order Type Set - Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.


    Function/Description

    This report provides AR customer transaction (invoice/credit memo) detail for a range of invoice dates. It is designed to show the associated bookings and booked date from Order Management for each invoice line. It provides a powerful reporting tool to commissions analysts and managers who must track invoice revenue in terms of bookings. The report may be run for a given territory or salesperson.

  13. Absolute BBB Book to Bill Detail Report

    Filename: BBB_BTBD.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with an invoiced date and order/return lines with a booked date greater than or equal to this date.
    End Date Yes  Select  invoice/memo lines with an invoiced date and order/return lines with a booked date lesser than or equal to this date.
    Territory No
    Sales Person No
    Billable Flag No Select booked orders/returns that will create a customer transaction in AR. This attribute is influenced by a combination of item attribute and transaction type configuration settings.
    Accounting Affect Flag No Select booked orders/returns that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Category Set Yes
    Order Type Set - Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report retrieves net bookings for the date range by booked date and invoices for the date range by invoice date to provide a detailed comparison of bookings and billings for a date range. At the end of the report, book to bill ratios are provided for the date range in terms of value and quantity.

  14. Absolute BBB Margin Invoice Report

    Filename: BBB_MIV.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with an invoiced date greater than or equal to this date.
    End Date Yes Select invoice/memo lines with an invoiced date lesser than or equal to this date.
    Order Number No Select invoices/memos for this Order or Return number.
    Group Option1 No
    Group Option2 No
    Group Option3 No
    Group Option4 No
    Territory Low No
    Territory High No
    Category Set Yes
    Item Category Low No
    Item Category High No
    Account Low No
    Account  High No
    Show Summary Only? Yes
    Show Column Headings? Yes
    For Export Yes
    Order Type Set Exclude No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set Include No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides....

  15. Absolute BBB Serial Number Invoice Detail Report for EXPORT

    Filename: BBB_SNID.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with an invoiced date greater than or equal to this date.
    End Date Yes  Select invoice/memo lines with an invoiced date lesser than or equal to this date.
    Territory No
    Sales Person No
    Accounting Affect Flag No Select invoices that will create a receivable in AR. This attribute is maintained on the AR Transaction type.
    Show Column Headings? Yes
    Order by Columns (i.e. 1,2,3) No
    Show Non-Shippable Lines? Yes
    Order Number No Select invoices/memos for this Order or Return number.
    Order Type Set - Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides line level invoice transaction detail for a range of invoice dates. It is designed to tie invoice line items back to shipment serial numbers for customer support and support billing purposes. It is designed for export to a spreadsheet only and may not be submitted for printing.

  16. Absolute BBB Revenue Recognition Detail Report

    Filename: BBB_RRD.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with a GL date greater than or equal to this date.
    End Date Yes Select invoice/memo lines with a GL date lesser than or equal to this date.
    Territory No
    Sales Person No
    GL Posted Txns Only? Yes
    Show Item Column? Yes
    Show Column Headings? Yes
    Order by Columns (i.e. 1,2,3) No
    Show Periods in Summary? No
    Account Low No
    Account High No
    For Export Yes
    Order Number No Select invoices/memos for this Order or Return number.
    Order Type Set – Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set – Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides order/invoice line level revenue recognized detail for a GL date range. It can be used as a drill down to provide supporting details for the REV RECOGINIZED section of the BBB Backlog Revenue Summary Report. This report may be submitted for printing or for export to a spreadsheet to facilitate customized ordering, sorting and calculations.

  17. Absolute BBB Unearned Revenue by Customer and Period Recognized Report

    Filename: BBB_URS.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with a GL date greater than or equal to this date.
    End Date Yes  Select invoice/memo lines with a GL date lesser than or equal to this date.
    Territory No
    Sales Person No
    Account Low No
    Account High No
    Order Type Set - Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides a matrix of unearned revenue by customer and period for a range of GL dates. It is designed to be run for future dates to provide a forecast of revenue to be recognized in the future for such order line items as service and maintenance, which are often recognized monthly across one or more years.

  18. Absolute BBB Unearned Revenue by Customer and Period Recognized Report for EXPORT

    Filename: BBB_URES.rdf

    Parameters
    Name  Req'd Description
    Start Date Yes Select invoice/memo lines with a GL date greater than or equal to this date.
    End Date Yes  Select invoice/memo lines with a GL date lesser than or equal to this date.
    Territory No
    Sales Person No
    Account Low No
    Account High No
    Order Type Set - Include No Include invoices/memos/orders/returns for a specific order type or set of order types.
    Order Type Set - Exclude No Exclude invoices/memos/orders/returns for a specific order type or set of order types.

    Function/Description

    This report provides a matrix of unearned revenue by customer and period for a range of GL dates. It is designed to be run for future dates to provide a forecast of revenue to be recognized in the future for such order line items as service and maintenance, which are often recognized monthly across one or more years. This report is designed specifically for export to a spreadsheet to facilitate customized ordering, sorting and calculations and may not be printed.

  19. Absolute Populate BBB Revenue Summary Program

    Filename: BBB_PRBS.sql

    Parameters
    Name  Req'd Description
    Period From Yes Select order/return/invoice and memo lines with a transaction date greater than or equal to this period.
    Period To Yes Select order/return/invoice and memo lines with a transaction date lesser than or equal to this period.
    Close Periods in Range? Yes
    GL Posted AR Trxs Only? Yes

    Function/Description

    This program populates the summary table used by the BBB Revenue Backlog Summary Report. It may be run on an ad hoc basis, prior to running the BBB Revenue Backlog Summary Report, or it may be scheduled to run daily, weekly or monthly as required. This program along with the summary table is designed to facilitate optimal performance of the BBB Revenue Backlog Summary Report.

11. BBB Business Views

Click here for column level details on all views

Specific to each database instance:
SOB = Set of Books
LC = Language Code
Views Decription
ABSL_BK_LN_<SOB|LC>_V Booked Lines. This view contains booked order and return lines by book or booking change date.
ABSL_BTH_TRX_<SOB|LC>_V Booking Transaction History Transactions. This view contains a transaction for each change to an order or return affecting order value and/or sales credit allocation.
ABSL_FBL_LN_<SOB|LC>_V Fulfillable Backlog Lines. This view includes shippable order lines that have not shipped and non-shippable, invoiceable lines that have not invoiced. Likewise, it includes receipt required returns that have not been received, and credit only returns that have not created credit memos. 
ABSL_SBL_LN_<SOB|LC>_V Shippable Backlog Lines. This view includes only shippable order lines or receipt required return lines that have not shipped or been received, respectively.
ABSL_SHP_LN_<SOB|LC>_V Shipped Lines. This view includes order lines that have been shipped and returns that have been received, whether they are invoiceable or not.
ABSL_FF_LN_<SOB|LC>_V Fulfilled Lines. This view includes shippable order lines that been shipped, non-shippable order lines that have been invoiced, receipt required return lines that have been received and credit only return lines that have been credited.
ABSL_INV_LN_<SOB|LC>_V Invoice Lines. This view includes sales order and return lines that have been invoiced or credited, respectively, and manual customer transactions such as Invoices, Credit Memos and Debit Memos.
ABSL_REV_LN_<SOB|LC>_V  Revenue Recognized (Earned) Lines. This view includes invoice and credit memo distributions for earned revenue associated with sales orders and returns.
ABSL_REV_SUM_<SOB|LC>_V Backlog Revenue Summary. This view provides a summary of Beginning Backlog, Bookings, Shipments, Revenue Activity and Ending Backlog for a given period to illustrate the flow and conversion of Potential or Booked Revenue into Actual or Recognized Revenue.
ABSL_UNEARN_<SOB|LC>_V  Unearned Revenue Lines. This view includes invoice and credit memo distributions for unearned revenue associated with sales orders and returns.
ABSL_GL_ACCT_<SOB|LC>_V GL Accounts. Provides the account flexfield, individual segment columns with user defined names, descriptions, DFF values and code_combination_id to join to transaction tables.
ABSL_MGN_BK_<SOB|LC>_V Margin Booked Lines. This view provides revenue and cost for booked order lines.
ABSL_MGN_SH_<SOB|LC>_V Margin Shipment Lines. This view provides revenue, cost and inter-company price for shipped order lines.
ABSL_MGN_FF_<SOB|LC>_V Margin Fulfillment Lines. This view provides revenue, cost and inter-company price for shipped and/or invoiced order lines.
ABSL_MGN_IN_<SOB|LC>_V Margin Invoice Lines. This view provides revenue, cost and inter-company price for invoiced order lines.
ABSL_MGN_BL_<SOB|LC>_V Margin Backlog Lines. This view provides revenue, cost and inter-company price for fulfillable backlog order lines.
ABSL_ITEMCAT_<category_set_name>_V  Item Categories. This view provides the item category flexfield, individual segment columns with user defined names and category_id to join to transaction tables. A separate view will be created for each category set defined by the user.
ABSL_O_HOLDS_<SOB|LC>_V Order and Line Holds.  This view provides all the relevant details associated with a hold. Can be run stand alone or joined by line_id to any transaction table.. 

 

12. Sales Territory Flexfield Based Record Level Security

BBBi includes a feature that addresses the issue of contextually restricting data access, known as Record Level Security. This essentially means that access to data through pre-defined Oracle Reports or Discoverer folders can be controlled, based on the user or responsibility. It is implemented by assigning Oracle users or Responsibilities to a specific “Sales Territory Flexfield”, and allowing them to only access records which contain that Territory. For instance, a sales person assigned to the Territory ‘AMERICAS’ would view a report with output filtered to only show records for that Territory. Additionally we can define a master-detail relationship to map many Territories to a single Territory Set, so that a sales manager or other executive can access records for all the territories
under his/her management.

When a user accesses a Discoverer folder or Oracle Report with Record Level Security
enabled, the Territory that he/she is assigned, is determined by a User or Responsibility
based profile option that has been created for this purpose. The “security level”
assigned in the profile option is cross referenced within the Report at runtime using a
specialized BBBi function, which is added to the WHERE clause of the SQL within the
Report. This function validates the profile option against the Territory assigned to every
Record fetched by the Report. Only those records for the Territory that match the user’s
assigned Territory would be selected for display.

For more information on implementing this feature, click here.

 

13. Technical Notes

Note: BBB Intelligence is CEMLI certified. CEMLI is Oracle's published guidelines for developing and implementing custom extensions to Oracle Applications.

ER Diagram Integration Diagram Database Schema Diagram All Database Objects by Type

ER Diagram

Integration Diagram
 

Database Schema Diagram

BBB Intelligence does not update, insert or delete any Oracle Base tables. It does not modify any Oracle Application code, forms, reports, procedures, triggers or views. It is designed to utilize Oracle's standard tables and code without impacting it. BBBi stores all it's data within it's own set of transaction and setup tables. 

All Database Objects by Type

Note: This list is simply to provide an example of the number and type of actual BBB objects.

OBJECT_NAME

OBJECT_TYPE

OWNER

ABSL_ACCT_RULE_F1

FUNCTION

<ABSL>  

ABSL_AR_BOUND_F2

FUNCTION

<ABSL>  

ABSL_BTH_LINE_FILTER

FUNCTION

<ABSL>  

ABSL_CAT_FLEX_F

FUNCTION

<ABSL>  

ABSL_FLEX_BUILDER

FUNCTION

<ABSL>  

ABSL_GL_RATE_CONV_F1

FUNCTION

<ABSL>  

ABSL_BBB_SUMMARY_N1

INDEX

<ABSL>

ABSL_BBB_SUMMARY_N2

INDEX

<ABSL>

ABSL_BBB_SUMMARY_N3

INDEX

<ABSL>

ABSL_BTH_COLUMN_DEFS_P1

INDEX

<ABSL>

ABSL_BTH_ERRORS_P1

INDEX

<ABSL>

ABSL_BTH_PARAMETERS_P1

INDEX

<ABSL>

ABSL_BTH_SALES_CREDITS_I1

INDEX

<ABSL>

ABSL_BTH_SALES_CREDITS_I2

INDEX

<ABSL>

ABSL_BTH_SALES_CREDITS_P1

INDEX

<ABSL>

ABSL_BTH_TABLE_DEFS_P1

INDEX

<ABSL>

ABSL_BTH_TEMP_TRX_P1

INDEX

<ABSL>

ABSL_BTH_TRX_I1

INDEX

<ABSL>

ABSL_BTH_TRX_I2

INDEX

<ABSL>

ABSL_BTH_TRX_I3

INDEX

<ABSL>

ABSL_BTH_TRX_I4

INDEX

<ABSL>

ABSL_BTH_TRX_I5

INDEX

<ABSL>

ABSL_BTH_TRX_I6

INDEX

<ABSL>

ABSL_BTH_TRX_P1

INDEX

<ABSL>

ABSL_SALESREP_TERRITORIES_U2

INDEX

<ABSL>  

ABSL_BTH_DEL_LINE

PROCEDURE

<ABSL>  

ABSL_BTH_MAIN_SOH

PROCEDURE

<ABSL>  

ABSL_BTH_MAIN_SOL

PROCEDURE

<ABSL>  

ABSL_BTH_MAIN_SSC

PROCEDURE

<ABSL>  

ABSL_BTH_MAKE

PROCEDURE

<ABSL>  

ABSL_BTH_NEW_SOH

PROCEDURE

<ABSL>  

ABSL_BTH_NEW_SOL

PROCEDURE

<ABSL>  

ABSL_BTH_NEW_SSC

PROCEDURE

<ABSL>  

ABSL_BTH_OUT_LINE

PROCEDURE

<ABSL>  

ABSL_CAT_VIEW_BUILD

PROCEDURE

<ABSL>  

ABSL_DO_SQL

PROCEDURE

<ABSL>  

ABSL_INSERT_BTH_ERRORS

PROCEDURE

<ABSL>  

ABSL_BTH_ERRORS_S

SEQUENCE

<ABSL>

ABSL_BTH_TEMP_TRX_S

SEQUENCE

<ABSL>

ABSL_BTH_TRX_S

SEQUENCE

<ABSL>

ABSL_BBB_SUMMARY

SYNONYM

APPS  

ABSL_BTH_COLUMN_DEFS

SYNONYM

APPS

ABSL_BTH_ERRORS

SYNONYM

APPS

ABSL_BTH_ERRORS_S

SYNONYM

APPS

ABSL_BTH_GENERATOR_RUNS

SYNONYM

APPS

ABSL_BTH_LOOKUPS

SYNONYM

APPS

ABSL_BTH_PARAMETERS

SYNONYM

APPS

ABSL_BTH_SALES_CREDITS

SYNONYM

APPS

ABSL_BTH_TABLE_DEFS

SYNONYM

APPS

ABSL_BTH_TEMP_TRX

SYNONYM

APPS

ABSL_BTH_TEMP_TRX_S

SYNONYM

APPS

ABSL_BTH_TRX

SYNONYM

APPS

ABSL_BTH_TRX_S

SYNONYM

APPS

ABSL_BK_LN_ALL_V

SYNONYM

APPS  
ABSL_BTH_TRX_ALL_V

SYNONYM

APPS

ABSL_FBL_LN_ALL_V

SYNONYM

APPS

ABSL_SBL_LN_ALL_V

SYNONYM

APPS

ABSL_SHP_LN_ALL_V

SYNONYM

APPS

ABSL_FF_LN_ALL_V

SYNONYM

APPS

ABSL_INV_LN_ALL_V

SYNONYM

APPS

ABSL_REV_LN_ALL_V 

SYNONYM

APPS

ABSL_UNEARN_ALL_V 

SYNONYM

APPS

ABSL_GL_ACCT_ALL_V

SYNONYM

APPS

ABSL_MGN_SH_ALL_V

SYNONYM

APPS

ABSL_MGN_FF_ALL_V

SYNONYM

APPS

ABSL_MGN_IN_ALL_V

SYNONYM

APPS

ABSL_MGN_BL_ALL_V

SYNONYM

APPS

ABSL_ITEMCAT_<category_set_name>_V 

SYNONYM

APPS

ABSL_O_HOLDS_ALL_V

SYNONYM

APPS

ABSL_BBB_SUMMARY

TABLE

<ABSL>

ABSL_BTH_COLUMN_DEFS

TABLE

<ABSL>

ABSL_BTH_ERRORS

TABLE

<ABSL>

ABSL_BTH_GENERATOR_RUNS

TABLE

<ABSL>

ABSL_BTH_LOOKUPS

TABLE

<ABSL>

ABSL_BTH_PARAMETERS

TABLE

<ABSL>

ABSL_BTH_SALES_CREDITS

TABLE

<ABSL>

ABSL_BTH_TABLE_DEFS

TABLE

<ABSL>

ABSL_BTH_TEMP_TRX

TABLE

<ABSL>

ABSL_BTH_TRX

TABLE

<ABSL>

ABSL_BTH_SOH

TRIGGER

<ABSL>

ABSL_BTH_SOL

TRIGGER

<ABSL>

ABSL_BTH_SSC

TRIGGER

<ABSL>

ABSL_BK_LN_ALL_V

VIEW

<ABSL>

ABSL_BTH_TRX_ALL_V

VIEW

<ABSL>

ABSL_FBL_LN_ALL_V

VIEW

<ABSL>

ABSL_SBL_LN_ALL_V

VIEW

<ABSL>

ABSL_SHP_LN_ALL_V

VIEW

<ABSL>

ABSL_FF_LN_ALL_V

VIEW

<ABSL>

ABSL_INV_LN_ALL_V

VIEW

<ABSL>

ABSL_REV_LN_ALL_V 

VIEW

<ABSL>

ABSL_UNEARN_ALL_V 

VIEW

<ABSL>

ABSL_GL_ACCT_ALL_V

VIEW

<ABSL>

ABSL_MGN_SH_ALL_V

VIEW

<ABSL>

ABSL_MGN_FF_ALL_V

VIEW

<ABSL>

ABSL_MGN_IN_ALL_V

VIEW

<ABSL>

ABSL_MGN_BL_ALL_V

VIEW

<ABSL>

ABSL_ITEMCAT_<category_set_name>_V 

VIEW

<ABSL>

ABSL_O_HOLDS_ALL_V

VIEW

<ABSL>

14. Table Definitions

Tables Description
ABSL_BTH_TRX Booking Transaction History. This table is a repository for all mandatory and user requested bookings information. The table may contain single or multiple records for each order line, depending on the number of salesrep splits, salesrep changes, or line changes that have occurred. For each new booking change, a copy of the current order line/sales credit information is inserted with a negative ordered quantity, and a new record is written with the new or updated order line values. 
ABSL_BTH_TABLE_DEFS BTH Table Definitions. This table contains a registry of all tables used by the booking transaction history engine. The BTH engine procedure selects records from this table to dynamically build triggers and procedures that are used to track changes to sales orders. This table is maintained by the Define BTH Table Definitions User Form.
ABSL_BTH_COLUMN_DEFS   BTH Column Definitions. This table contains a registry of all table columns used by the booking transaction history engine. The BTH engine procedure selects records from this table to dynamically build triggers and procedures that are used to track changes to sales orders. Only columns registered in this table may be tracked. Before column record may be inserted into this table, it's associated table must be inserted into the ABSL_BTH_TABLE_DEFS table. This table is maintained by the Define BTH Column Definitions User Form.
ABSL_BTH_LOOKUPS   BTH Lookups. This table contains lookup codes and descriptions for parameters and options used within the BBBi solution.
ABSL_BTH_PARAMETERS   BTH Parameters. This table contains a single record which governs the summary period and execution method of the BTH engine. It is maintained by the Define BTH Column Definitions User Form.
ABSL_BTH_ERRORS   BTH Errors. This table stores all runtime, object and compilation errors related to BBBi database objects.
ABSL_BBB_SUMMARY   BBB Summary. This table stores a summary of backlog, bookings and earned revenue transactions by period for use with the Revenue Backlog Concurrent Report and the ABSL_BBB_SUM_V database view. It must be periodically populated using the concurrent program Populate Revenue Backlog Summary Program.
ABSL_BTH_GENERATOR_RUNS   **This table is obsolete.**
ABSL_BTH_SALES_CREDITS   BTH Sales Credits. This table is a mirror image of OE_SALES_CREDITS. It is used by the BTH trigger on the OE_SALES_CREDITS table to select associated records while the OE_SALES_CREDITS table is mutating.
ABSL_BTH_TEMP_TRX   BTH Temp Trx. This table is used to insert a record of the current BTH transaction if the BTH engine should encounter an error whereby it cannot update the ORIGINAL_FLAG on the absl_bth_trx table.
ABSL_FLOW_FLAGS Flow Flags. This table contains a matrix of header and line level transaction types indicating eligibility to the following associated workflows: Invoice Interface, Ship Confirm and RMA Receipt.
Send Feedback About This Page
Home | Company | Products | Services | Customers | News & Events | Contact
 
 Questions or Comments? Send email to webmaster@absolute-tech.com. © 2002-2004 Absolute Technologies Inc. All Rights Reserved.