Use SQL to Query Order Management Data

Use SQL to get data from the Order Management database, then analyze it.

Get details from the Order Management database to do a variety of administration tasks.

Verify the data format and values you must use during order import or with a web service.
Troubleshoot problems that happen during upgrades or other administrative set up.
Save data into a data management tool of your choice so you can analyze it.

You create a data model in Oracle Business Intelligence and use it to query the Order Management database.

Assume you must query the database to get a list of users so you can identify the users who are active and the ones who aren't.

Summary of the Steps

Create and run query.
Create report.

This topic uses example values. You might need different values, depending on your business requirements.

Create and Run Query

Make sure you have the privileges that you need to develop in Oracle Analytics Publisher.

You need these privileges so you can create a data model in Oracle Business Intelligence.
Go to the Reports and Analytics work area.

For details, see Use Reports and Analytics with Order Management.
On the Reports and Analytics page, click Browse Catalog.

Your browser opens a new tab for Oracle Business Intelligence.
In Oracle Business Intelligence, click Home.

Create the data model.

In the Create area, under Published Reporting, click More > Data Model.

You use a data model to set up the SQL you use to query the Oracle database. You can reuse the data model for different SQL queries. It isn't necessary to create and save a separate data model for each SQL query.
On the Diagram tab, click New Data Set > SQL Query.

In the New Data Set SQL Query dialog, set the values.

Attribute	Value
Name	My SQL
Data Source	ApplicationDB_FSCM (Default) FSCM means Fusion Supply Chain Management.
Type of SQL	Standard SQL
SQL Query	`Select pu.user_id, pu.active_flag, pu.start_date, pu.end_date, pu.username, pur.role_id, pur.role_guid Select pu.user_id, pu.active_flag, pu.start_date, pu.end_date, pu.username, pur.role_id, pur.role_guid from per_users pu, per_user_roles pur, per_user_history puh` You can enter some other query. For example, to get all order headers, enter this: `SELECT * FROM fusion.DOO_HEADERS_ALL` To get all order lines, enter this: `SELECT * FROM fusion.DOO_LINES_ALL`

For example:

Click OK.

The Diagram tab displays the attributes that your SQL defined.
Click View Data, set Rows to 200, click View, click Table View, then examine the output.

In the upper-right corner, click Save, then save the output in My Folders.

Create Report

On the Data tab, click Save as Sample Data > OK.
In the upper-right corner, click Create Report.

The Create Report wizard displays for your data model.
Click Next.
On the Select Layout step, enable the Landscape option, enable the Table option, then click Next.
On the Create Table step, adjust the layout to fit your needs, then click Next.

For example, drag and drop columns from the data source to the table.
On the Save Report step, enable the View Report option, then click Finish.
Save the report, name it, such as SQLReport, then examine the results on the report output that displays.

As an option, save the result to a file type of your choice.

Add a Field

You can't extend the predefined data model for Order Management. Instead, you can replace a field in the report output with the one you need.

Order Management uses the term item to describe the product your customer buys. The AS54888 Computer is an example of an item. The predefined data model uses the INVENTORY_ITEM_ID column to store the value that identifies the item. In this example, you replace INVENTORY_ITEM_ID with ITEM_NUMBER so your report output is more consistent with this usage.

replacing INVENTORY_ITEM_ID with ITEM_NUMBER

Do steps 1 through 5 from earlier in this topic, except use these values in the New Data Set SQL Query dialog.

Attribute	Value
Name	Add a Column
Data Source	ApplicationDB_FSCM (Default)
Type of SQL	Standard SQL
SQL Query	`SELECT * FROM fusion.DOO_LINES_ALL`

Click Structure > Table View.
Locate the row that contains INVENTORY_ITEM_ID in the XML Tag Name column.

Modify values in the row you located.

Attribute	Old Value	New Value
XML Tag Name	INVENTORY_ITEM_ID	ITEM_NUMBER
Display Name	INVENTORY_ITEM_ID	Item Number

Click View Data, set Rows to 200, click View, click Table View, then examine the output.

Example SQL Queries

For a complete list of examples, see SCM SQL Repository (Doc ID 2190295.1).

Sales Orders in Not Started Status or Processing Status and There Are No Exceptions

This problem typically happens when the SOA server (Service Oriented Architecture) becomes unstable or overloaded and the SOA transactions time out. Use a query to identify sales orders that remain in Not Started Status or Processing Status but that don't have any exceptions.

SELECT f.last_update_date,
h.source_order_number,
l.display_line_number,
f.fulfill_line_number,
f.status_code,
f.fulfill_line_id
FROM doo_headers_all h,
doo_fulfill_lines_all f,
doo_lines_all l
WHERE h.header_id =l.header_id
AND h.submitted_flag='Y'
AND l.line_id =f.line_id
AND f.open_flag ='Y'
AND f.creation_date>= sysdate -- Use the date range to meet your needs
AND f.status_code IN ('NOT_STARTED') -- Get orders only in NOT_STARTED status
AND NOT EXISTS
(SELECT OrchestrationGroupEO.TRANSACTION_ENTITY_ID
FROM DOO_WAIT_TASK_DETAILS WaitTaskDetailsEO,
DOO_ORCHESTRATION_GROUPS OrchestrationGroupEO
WHERE WaitTaskDetailsEO.GROUP_ID = OrchestrationGroupEO.GROUP_ID
AND WaitTaskDetailsEO.STATUS_CODE='Active'
AND OrchestrationGroupEO.STATUS ='ACTIVE'
AND TRANSACTION_ENTITY_NAME ='DOO_ORDER_FLINES_V'
AND transaction_entity_id =f.fulfill_line_id
)
ORDER BY f.source_order_number;

Use the Force Unlock action to recover these sales orders. For details, see Recover Sales Orders That Are Locked or Not Started.

Find the reservations that have finished for an item or list of items in your sales order.

SELECT  iop.organization_code org ,
        ir.reservation_id         ,
        dfa.header_id             ,
        dfa.source_order_number   ,
        ir.back_to_back_flag      ,
        ir.staged_flag            ,
        esi.item_number           ,
        ir.reservation_quantity   ,
        ir.creation_date
FROM    inv_reservations ir    ,
        inv_org_parameters iop ,
        egp_system_items_b esi ,
        doo_fulfill_lines_all dfa
WHERE   iop.organization_id              =ir.organization_id
        AND esi.inventory_item_id        =ir.inventory_item_id
        AND esi. organization_id         =ir.organization_id
        AND ir.source_fulfillment_line_id=dfa.fulfill_line_id
        AND dfa.inventory_item_id        =ir.inventory_item_id
        AND ir.demand_source_type_id     ='2' /*Data in Order Management comes predefined to use a demand_source_type_id of 2 for the sales order.*/
ORDER BY dfa.source_order_number;