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 a variety of sources, such as an Oracle Application.

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.

• To get the full list of the public view objects that you can use and the attributes that they contain, download the XLS file from 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.

1. Download and open the XLS file.

2. Filter the Attribute column for CustomerName.

   Assume you get these results.

   Path	Attribute
   oracle.apps.hcm.profiles.core.publicView.personIndexSearch.SpecialProjectsPVO	CustomerName
   oracle.apps.scm.assetTracking.assetGroups.publicView.GroupAttributeKeyValuePVO	CustomerName
   oracle.apps.scm.shipping.pickRelease.publicView.PickWaveSelectionPVO	CustomerName
   oracle.apps.scm.inventory.materialAvailability.reservations.publicView.WorkOrderSupplyReservationPVO	CustomerName
   oracle.apps.scm.inventory.materialAvailability.reservations.publicView.WorkOrderSupplyMfgReservationPVO	CustomerName
   oracle.apps.scm.doo.workbench.publicViewEcsf.view.OrchestrationOrderPVO	CustomerName
   oracle.apps.financials.collections.correspondence.publicViewEcsf.CollCorrespondencePVO	CustomerName
   oracle.apps.financials.collections.transaction.publicViewEcsf.CollDelinquencyPVO	CustomerName

3. Look for the most likely path.

   The term workbench means order fulfillment. Out of all your results, oracle.apps.scm.doo.workbench seems like the most likely path.

4. Look for the most likely PVO. Order Management used to use the phrase orchestration order. It now uses sales order. They mean the same thing. OrchestrationOrderPVO seems like the most likely PVO.

   Here's what you use for this example.

   Attribute	Description
   Path	oracle.apps.scm.doo.workbench.publicViewEcsf.view.OrchestrationOrderPVO
   View Object	OrchestrationOrderPVO
   Attribute	CustomerName

Other Filters

You can also use Excel's advanced search to search the Path column for rows that contain the HeaderPVO value and that also contain the oracle.apps.scm.doo value.

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
Object getItem(Long itemId, Long orgId) {	Define a local object that your code can call. This object will contain data from the public view object. Object getItem. Define an object named getItem. Long itemId, Long orgId. Specify itemId and orgId as Long data type strings. These strings will contain data from the public view object.
def itemPVO = context.getViewObject("context")	Create an instance of the public view object for the item, then store it in the itemPVO variable. Use this format. itemPVO. Variable that will contain the instance. context.getViewObject. Use the getViewObject method to get the view object according to the context. "context". Context to use when getting the view object. For example, reference the ItemPVO public view object. def itemPVO = context.getViewObject("oracle.apps.scm.productModel.items.publicView.ItemPVO") where oracle.apps.scm.productModel.items is the context. It specifies to get a product model named items from supply chain management.
def vc = itemPVO.createViewCriteria();	Define the variable that will store details for the public view object. vc. Variable that stores details for the view criteria object. View criteria is a filter that determines the data that the public view object returns. itemPVO. Name of the public view object that contains the data you must get and filter. createViewCriteria. Method that creates the view criteria object. It defines vc as a list that contains one or more rows.
def vcrow = vc.createViewCriteriaRow();	Define a variable for the row. vcrow. Variable that stores details for rows of the view criteria object. vc.createViewCriteriaRow. Use the createViewCriteriaRow method to create a row in variable vc.
vcrow.setAttribute("InventoryItemId", itemId);	Populate variable vcrow with data from the public view object. setAttribute. Method that sets the value for the attribute in the row. InventoryItemId. Attribute in the public view object. This example gets the value that uniquely identifies the item in inventory. itemId. Variable that the extension code defines. This code gets the value of attribute InventoryItemId on the order line from the public view object, then stores it in itemId.
vcrow.setAttribute("OrganizationId", orgId);	Populate variable vcrow with data from the public view object. setAttribute. Method that sets the value for the attribute in the row. OrganizationId. Attribute in the public view object. This example gets the value that uniquely identifies the organization that ordered the item. orgId. Variable that the extension code defines. This code gets the value of the OrganizationId attribute on the order header from the public view object, then stores it in orgId.
def rowset = itemPVO.findByViewCriteria(vc, -1);	Get the iterator that contains the number of rows that meet the criteria and store it in local variable rowset. rowset. Variable that stores the iterator. findByViewCriteria. Method that searches the public view object according to criteria that you define. vc. Contains the data that the public view object returns. -1. Return all rows that match the view criteria. You can also use a positive integer to get a subset of rows. For example, use vc, 3 to get the first three rows that match the criteria. If only two rows match the criteria, then the method returns only these two rows.
def item = rowset.first()	Define a variable named item.
return item	The item variable can now access all attributes in ItemPVO. return returns variable item so you can reference item from your extension to get details from ItemPVO.

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 Public View Object

The customer master in Oracle Trading Community Architecture (TCA) might include descriptive flexfields. However, public view objects that contain customer account site details, such as PartyPVO, might not include these descriptive flexfields. Use this technique to get them.

Note

1. 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.

2. Reference the publicFlex public view object to reference the descriptive flexfield. In this example, you reference publicFlex for custAccountSite.

   oracle.apps.cdm.foundation.parties.publicFlex.custAccountSite.view.CustAccountSiteInformationVO

3. Use the structure 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're looking 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. Its 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.

1. Go to Tables and Views for Oracle SCM Cloud.

2. In the table of contents, click Order Management.

3. 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.

4. 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.

5. Run an SQL to get the freight charge.