Use Extensions to Get Data from Oracle Applications

Write an order management extension that calls an API on the context object so it queries the table and accesses the public view object that the table contains.

If you deploy into Oracle Applications, then you can't programmatically access the Oracle database. You can't use PL/I (Programming Language One), SQL (Structured Query Language), or JDBC (Java Database Connectivity) to access data in other application tables. Write an extension instead.

Here's an example extension that accesses a public view object.

Code	Description
`def itemPVO = context.getViewObject("oracle.apps.scm.productModel.items.publicView.ItemPVO");`	Create an instance of the public view object for the item. You must specify the entire name of the public view object. This code uses each public view object as a row iterator, so the methods on the RowIterator interface are also available on each public view object. You can use the RowSetIterator method to access a method that gets the rows that the view object query returns, and to navigate the row set for the public view object. Have a look at the ViewObject method. For details, see Methods That You Can Use with Order Management Extensions.
`def item = itemPVO.first();`	Access the first item.

Code

Description

def itemPVO = context.getViewObject("oracle.apps.scm.productModel.items.publicView.ItemPVO");

Create an instance of the public view object for the item. You must specify the entire name of the public view object.

This code uses each public view object as a row iterator, so the methods on the RowIterator interface are also available on each public view object. You can use the RowSetIterator method to access a method that gets the rows that the view object query returns, and to navigate the row set for the public view object. Have a look at the ViewObject method. For details, see Methods That You Can Use with Order Management Extensions.

def item = itemPVO.first();

Access the first item.

Here are the public view objects that you typically use.

Data	View Object Name
Sales Order	HeaderPVO FulfillLinePVO
Item	ItemPVO ItemCategoryPVO
Customer	PartyPVO PartySitePVO LocationPVO
Customer Account	CustomerAccountPVO CustomerAccountSitePVO CustomerAccountSiteUsePVO
Receivable	TransactionTypePVO

Caution: You must use only the public view objects that Oracle supports. For details, see Guidelines for Using Extensions to Get Data from Oracle Applications.

Use View Criteria Objects to Filter Results

The example above is only for demonstration purposes. It's too simplistic for practical use because it gets arbitrary items from the items table. It's more likely that you must query for an item or a set of items that match a criterion. To support SQL, your extension must provide a WHERE clause that only selects rows that match a criterion. Use the ViewCriteria object for this purpose.

An order management extension can create a view criteria object that adds filtering logic to the public view object before it gets data from these objects. A view criteria object is a filter that you create and apply programmatically to a view object. Order Management converts each of these filters into a WHERE clause when your extension runs the query that you define in the public view object.

You use the view criteria row object to create a view criteria object. The view criteria object contains the attribute names and attribute values that become part of the WHERE clause.

In this example, you will create an extension that:

Creates a view criteria and uses it to query a public view object.
Queries the item master for an item according to the item Id and inventory organization Id.
Examines the HazardousMaterialFlag attribute on the item.
If the HazardousMaterialFlag attribute flags the item as hazardous, then the extension sets the Shipping Instruction flexfield context segment to indicate that the item needs hazardous handling.

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

Use view criteria objects to filter results:

Get the item.

Code	Description
`def lines = header.getAttribute("Lines");`	Get the row set for the order lines.
`while( lines.hasNext() ) {`	Determine whether we must process more order lines.
`def line = lines.next();`	Get the next line and assign it to the variable line.
`def inventoryItemId = line.getAttribute("ProductIdentifier");`	Get the inventory item Id for the item from the order line that the Order Entry Specialist selected.
`def orgId = line.getAttribute("InventoryOrganizationIdentifier");`	Get the organization for the item from the order line that the Order Entry Specialist selected.
`def item = getItem(inventoryItemId, orgId);`	Get the item. Use the item Id and the organization Id to call the getItem method.
`String hazardous = item.getAttribute("HazardousMaterialFlag");`	Get the HazardousMaterialFlag attribute from the item.
`if( "Y".equals(hazardous) ) {`	Determine whether HazardousMaterialFlag flags the item as hazardous.
`def packShipInstruction = line. getOrCreateContextRow("PackShipInstruction");`	Get the row for the extensible flexfield context named PackShipInstruction.
`packShipInstruction.setAttribute("_ShippingInstruction", "Hazardous Handling Required.");`	Set the Shipping Instruction context segment.

Define the public view object.

Code	Description
`Object getItem(Long itemId, Long orgId) {` `def itemPVO = context.getViewObject("oracle.apps.` `scm.productModel.items.publicView.ItemPVO");`	Create an instance of the public view object for the item.
`def vc = itemPVO.createViewCriteria();`	Create the view criteria object.
`def vcrow = vc.createViewCriteriaRow();`	Create the view criteria row.
`vcrow.setAttribute("InventoryItemId", itemId);`	Set the inventory item attribute so you can include it in the filter condition, and set the value that you will use to compare to this attribute.
`vcrow.setAttribute("OrganizationId", orgId);`	Set the organization attribute so you can include it in the filter condition, and set the value that you will use to compare to this attribute.
`def rowset = itemPVO.findByViewCriteria(vc, -1);`	Define the view criteria that filters the rows when you query the public view object.
`def item = rowset.first();`	Get the first item row that meets the condition.

This code uses these parameters:

param itemId. Inventory item Id that identifies the item.
param orgId. Inventory organization Id that identifies the organization that owns of the item.

Code Without Comments

Here's the entire code for this example with no comments.

def lines = header.getAttribute("Lines");                                                             
while( lines.hasNext() ) {                                                                            
  def line = lines.next();                                                                            
  def inventoryItemId = line.getAttribute("ProductIdentifier");                                       
  def orgId = line.getAttribute("InventoryOrganizationIdentifier");                                   
  def item = getItem(inventoryItemId, orgId);                                                         
  String hazardous = item.getAttribute("HazardousMaterialFlag");                                      
  if( "Y".equals(hazardous) ) {                                                                       
def packShipInstruction = line. getOrCreateContextRow("PackShipInstruction");                     
packShipInstruction.setAttribute("_ShippingInstruction", "Hazardous Handling Required.");         
  } 
}

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;
}

Example

This example calls a web service that does a credit check.

import oracle.apps.scm.doo.common.extensions.ValidationException;
import oracle.apps.scm.doo.common.extensions.Message;

def poNumber = header.getAttribute("CustomerPONumber");

if (poNumber != "PMC TEST") return;

//get attribute to populate in the payload
String customer = header.getAttribute("BillToCustomerName");
Long accountId = header.getAttribute("BillToCustomerIdentifier");
BigDecimal amount = new BigDecimal(1000);

//prepare the payload
String payLoad = "<ns1:creditChecking xmlns:ns1=\"http://xmlns.oracle.com/apps/financials/receivables/creditManagement/creditChecking/creditCheckingService/types/\">" +
    "<ns1:request xmlns:ns2=\"http://xmlns.oracle.com/apps/financials/receivables/creditManagement/creditChecking/creditCheckingService/\">" +
    "<ns2:CustomerName>" + customer + "</ns2:CustomerName>" +
    "<ns2:CustomerAccountNumber>" + accountId + "</ns2:CustomerAccountNumber>" +
    "<ns2:RequestType>Authorization</ns2:RequestType>" +
    "<ns2:PriceType>ONE_TIME</ns2:PriceType>" +
    "<ns2:RecurrencePeriod></ns2:RecurrencePeriod>" +
    "<ns2:RequestAuthorizationAmount currencyCode=\"USD\">" + amount + "</ns2:RequestAuthorizationAmount>" +
    "<ns2:RequestAuthorizationCurrency>USD</ns2:RequestAuthorizationCurrency>" +
    "<ns2:ExistingAuthorizationNumber></ns2:ExistingAuthorizationNumber>" +
    "<ns2:Requestor>ar_super_user</ns2:Requestor>" +
    "</ns1:request>" +
    "</ns1:creditChecking>";

//Use the CreditCheckService web service connector to call the Check Credit service. Use the Manage External Interface Web Service Details task to set up the connector. This is a secured service,
//so we're using a message protection policy. We use the https URL of the service to register it.
def response = context.invokeSoapService("CreditCheckService", payLoad);

//Print a debug message. Append the entire response to the shipping instructions attribute.
//To avoid performance problems, you must comment out all debug statements in your production environment.
debug(response.getSoapBody().getTextContent());

//The response XML that the Credit Check service sends contains an element named Response. A YES value in the response means credit check passed. So, we extract the contents of the Response tag. The following XML API returns all tags
//that include the name Response in a NodeList element. We are expecting only one element in our XML response.
def nodeList = response.getSoapBody().getElementsByTagNameNS("*", "Response");

//Print out the lenght of the node list.
debug(nodeList.getLength());

//Get the first element that contains the name Response. We are expecting only one response. Then get its text content
String ccResponse = nodeList.item(0).getTextContent();

debug(ccResponse);

//Determine whether credit check passed.
if (ccResponse != 'YES') {
    //Credit check failed, so we a warning validation exception.
    throw new ValidationException(new Message(Message.MessageType.WARNING, "Credit check failed."));
} else {
    //Credit check passed.
    //Write the credit check response in an extensible flexfield.
    def psiContext = header.getOrCreateContextRow("ComplianceDetails");
    psiContext.setAttribute("_ComplianceInfo", ccResponse);
}

/**
 * Append the message that we received into the Shipping Instructions attribute. Use this method only for debugging purposes.
 */
void debug(def msg) {
    String si = header.getAttribute("ShippingInstructions");
    header.setAttribute("ShippingInstructions", si + ", " + msg.toString());
}