Guidelines for Using Extensions to Get Data from Oracle Applications
Use a public view object (PVO) in your order management extension to get data from an Oracle Application.
Each public view object uses a path:
Note
-
Use the getViewObject method.
-
Most of the view objects that you need are in the oracle.apps.scm path. In some cases you might need to go outside of oracle.apps.scm. For example, use oracle.apps.prc to access purchase order data from Oracle Purchasing.
-
For example, you can use a public view object to look up an item category in the Product Information Management work area or a purchase order in Oracle Purchasing. This example uses
oracle.apps.scm.productModel.items.publicView.ItemPVO
to get details about an item from Product Information Management. -
public
means that the view object makes its data available to other Oracle Applications.
You can use a public view object in an order management extension to get data from an Oracle application, but you must use only the public view objects that Oracle supports. To get the full list of them, see Public View Objects in Oracle Applications Cloud (Document ID 2386411.1).
Filter Your PVOs
Filter your search in the XLS file that you download.
Assume you want the PVO that filters data according to the customer name on the order header.
-
Download and open the XLS file.
- Use Excel's advanced filter to filter the columns in the XLS file.
Path
Attribute
Contains foundation.parties.publicView
The Customer Name is party data, so you use this filter because Order Management gets its party data from
foundation.parties
. For details about party data, see Overview of Displaying Customer Details on Sales Orders.Equals PartyName
-
Assume you get these results.
Path
Attribute
oracle.apps.cdm.foundation.parties.publicView.analytics.CustomerFactPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.PartyPersonPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.PartyOrganizationPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.CustomerContactPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.CustomerSecuredPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.CustomerPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.PartyGroupPVO PartyName oracle.apps.cdm.foundation.parties.publicView.analytics.PartyUsagePVO PartyName oracle.apps.cdm.foundation.parties.publicView.bicc.PartyExtractPVO PartyName oracle.apps.cdm.foundation.parties.publicView.bicc.AdditionalPartyNameExtractPVO PartyName oracle.apps.cdm.foundation.parties.publicView.relationships.OrganizationContactPVO PartyName oracle.apps.cdm.foundation.parties.publicView.core.PartyPVO PartyName oracle.apps.cdm.foundation.parties.publicModel.export.view.core.AdditionalPartyNameExpPVO PartyName oracle.apps.cdm.foundation.parties.publicModel.export.view.core.PartyExpPVO PartyName -
Look for the most likely path. The results contain a variety of usages, such as analytics, bicc, relationships, export, and core. You're interested in the core usage, so look for the path that includes
core
. -
Here's what you would use for this example.
Path
Attribute
oracle.apps.cdm.foundation.parties.publicView.core.PartyPVO
PartyName
Other Filters
Oracle Applications might use the abbreviation FOM (Fusion Order Management) or DOO
(Distributed Order Orchestration) to refer to Oracle Order Management. To find objects
that are relevant to Oracle Order Management, you can also filter the Path column for
rows that contain fom
or doo
.
Specify the Path
The path identifies the public view object. The nomenclature for each PVO can be different. Here's one example:
productFamily.functionalArea.publicView.usage.PVOname
Consider an example usage:
oracle.apps.scm.maintenanceManagement.maintProgram.publicView.analytics.MaintenanceForecastFactPVO
where
oracle.apps.scm
identifies the Supply Chain Management product family.maintenanceManagement
identifies the Maintenance Management functional area.maintProgram
identifies a component in the Maintenance Management functional area.MaintenanceForecastFactPVO
identifies the name of the public view object.analytics
identifies how you can use this PVO.
Here are the usages.
Usage |
Description |
---|---|
analytics |
Use with a report. |
bicc |
Use with Business Intelligence Cloud Connector. |
core |
Manage data from the core Oracle Applications. |
export |
Import or export data. |
relationships |
Manage a relationship between objects. |
Typical View Objects
Here are some of the public view objects that you typically use in an Order Management implementation.
Data |
View Object Name |
---|---|
Sales Order |
HeaderPVO FulfillLinePVO |
Item |
ItemPVO ItemCategoryPVO |
Customer |
PartyPVO PartySitePVO LocationPVO |
Customer Account |
CustomerAccountPVO CustomerAccountSitePVO CustomerAccountSiteUsePVO |
Receivable |
TransactionTypePVO |
Here are some of the more common paths to the various applications that you might need to access.
Application or Work Area |
Path |
---|---|
Product Information Management |
oracle.apps.scm.productModel |
Order Management |
oracle.apps.scm.doo |
Pricing Administration |
oracle.apps.scm.pricing |
Inventory Management |
oracle.apps.scm.inventory |
Work Execution |
oracle.apps.scm.commonWorkExecution |
Shipping |
oracle.apps.scm.shipping |
Purchasing |
oracle.apps.prc |
Code It
Note
Code |
Description |
---|---|
|
Define a local object that your code can call. This object will contain data from the public view object.
|
|
Create an instance of the public view object for the item, then store it in the itemPVO variable. Use this format:
For example, reference the ItemPVO public view object:
where
|
|
Define the variable that will store details for the public view object.
|
|
Define a variable for the row.
|
|
Populate the
|
|
Populate the
|
|
Get the iterator that contains the number of rows that meet the criteria
and store it in the
|
|
Define a variable named |
|
The |
Here's the entire code without comments.
Object getItem(Long itemId, Long orgId) {
def itemPVO = context.getViewObject(
"oracle.apps.scm.productModel.items.publicView.ItemPVO");
def vc = itemPVO.createViewCriteria();
def vcrow = vc.createViewCriteriaRow();
vcrow.setAttribute("InventoryItemId", itemId);
vcrow.setAttribute("OrganizationId", orgId);
def rowset = itemPVO.findByViewCriteria(vc, -1);
def item = rowset.first();
return item;
}
Get Descriptive Flexfields from a View Object
The customer master in Oracle Trading Community Architecture (TCA) might include descriptive flexfields. However, public view objects that contain site details for the customer account, such as PartyPVO, might not include these descriptive flexfields. Use this technique to get them.
Note
-
Go to the Manage Customer Account Site Descriptive Flexfields page, locate the descriptive flexfield that you must access, click Download Flexfield Archive, then examine the structure that it uses.
-
Reference the publicFlex view object to reference the descriptive flexfield. In this example, you reference publicFlex for custAccountSite.
oracle.apps.cdm.foundation.parties.publicFlex.custAccountSite.view.CustAccountSiteInformationVO
-
Use the structure that you examined when you downloaded the archive to help determine how to specify the view criteria.
Here's the entire code.
def siteFlexPVO = context.getViewObject
("oracle.apps.cdm.foundation.parties.publicFlex.
custAccountSite.view.CustAccountSiteInformationVO");
def vc = siteFlexPVO.createViewCriteria();
def vcrow = vc.createViewCriteriaRow();
vc.add(vcrow);
def rowset = siteFlexPVO.findByViewCriteriaWithBindVars
(vc, -1, new String [0], new String [0]);
def custDFF = rowset.first();
String values = custDFF.getAttribute("Salesperson")
Get Data From Sales Orders That Aren't in the Current Sales Order
You can use HeaderPVO to access order header data and FulfillLinePVO to access order line or fulfillment line data. For example, if the Order Entry Specialist sets the purchase order number on a sales order, then make sure some other sales order doesn't already use this purchase order number. Do this validation when the Order Entry Specialist submits the sales order.
Here are some more examples.
-
If a new sales order includes return lines, then get the order type from the original sales order and use it to set the order type on each return line.
-
If order revision 2 increases the total price of the sales order by 10% or more over the total price on order revision 1, then don't allow the revision. For example, version 1 of sales order x is a complete and separate sales order from version 2 of sales order x. Use a public view object to get data from a version that isn't the current version.
Get Details about Tables, Views, and Public View Objects
If you need to use a public view object that contains details for sales order headers and order lines, then have a look at the documentation. Go to Oracle Fusion Cloud SCM: Tables and Views for SCM, expand Order Management > Tables, then click one of:
-
DOO_HEADERS_ALL
-
DOO_LINES_ALL
-
DOO_FULFILL_LINES_ALL
Get Details for Charges
Assume you're creating a sales order report and need to include freight charges on the report. You have a sales order that's in Awaiting Shipping status or Awaiting Billing status. You view the order in a fulfillment view. The Your Price column on the fulfillment line contains a value for the Sale Price attribute but the Freight attribute is empty. It's also empty in Oracle Shipping.
You use SQL to examine the DOO_LINES_ALL table and the DOO_FULLFILL_LINES_ALL table, but no luck there either.
You can use the documentation to identify the table and column that has the freight charge.
Try it.
-
In the table of contents, click Order Management.
-
Press CTRL+F, type in the word
freight
, then press ENTER.The search returns all results starting at the beginning of the Order Management chapter.
-
Click the down arrow until find what you're looking for.
In this example, keep clicking until you see the DOO_ORDER_CHARGES link in the table of contents turn green, and that the CHARGE_SUBTYPE_CODE column contains a relevant description.
Subtype of a given charge type. Examples could include, within Shipping Related Charges: Freight Charge, Shipping Insurance, within Special Charges: Suspend Charge, Resumption Charge)
You might see CHARGE_SUBTYPE_CODE in other tables, such as DOO_FS_CHARGES_INT, but in most cases you use the DOO_ORDER_CHARGES table.
-
Run an SQL to get the freight charge.