Oracle SOA Suite 11g - Sample SAMPLE NAME : adapters-apps-101-OrderImportConcurrentProgram COMPONENT : Oracle Applications Adapter for Open Interface Tables andConcurrent Program. OVERVIEW Oracle Applications provides a set of Open Interface Tables to insert and update data in Oracle Applications. Interface tables are intermediate tables into which the data is inserted first. Once the data gets inserted into the interface tables, the data is validated, and then transferred to the base tables. Base tables are real application tables that reside in the application database. The data that resides in the interface tables is transferred to the base tables using concurrent programs. A concurrent program is an instance of an executionfile, along with parameter definitions and incompatibilities. Concurrent programs are scheduled in Oracle Applications to move data from interface tables to base tables. These programs perform the application-level checks and run validation before inserting data into base tables. The Oracle Adapter for Oracle Applications exposes both Open Interface Tables and Concurrent Programs as Web Services that can inturnbe invoked from a BPEL process. This example showcases insert of Sales Order data into Oracle Applications using BPEL Process Manager and Oracle Adapter for Oracle Applications. The Adapter exposes the following operations as Web Services: • Order data is inserted into Oracle's Order Management Open Interface tables. • The Order Import concurrent program is then invoked to further validate and import the orders PROVIDED FILES • README.txt • adapters-apps-101-OrderImportConcurrentProgram.zip-Files needed for the sample. o Artifacts\ Input\ Oracle SOA Suite 11g - Sample • order_data.xml- Payload file to be read by the File Adapter • cp_data.xml- CP parameters file to be read by the File Adapter sql\ • XX_BPEL_FND_REQUEST_WRAPPER.sql- Creates wrapper package for the Concurrent Program o Docs adapters-apps-101-OrderImportConcurrentProgram.pdf--Instructions o OrderImportConcurrentProgram-App.zip– JDeveloper Application and project o README.txt STEP BY STEP INSTRUCTIONS RUNNING THE PREBUILT SAMPLE • Set up the wrapper package on target DB (The database on which you intend to run this sample) by executing XX_BPEL_FND_REQUEST_WRAPPER.sql as APPS/APPS. • Edit the input file order_data.xml.Replace the order_id_01with a non-default value • Make sure that the input files exist in the path mentioned in the composite.xml • Build the sample using JDeveloper. • Deploy the BPEL process adapters-apps-101-OrderImportConcurrentProgram to the weblogic server • Invoke the BPEL Process. • Verify that the sample is executed successfully. VERIFYING EXECUTION INSTALLING & CONFIGURING • Configure the data source in the weblogic server. RUNNING THE SAMPLE • Launch JDeveloperand start weblogicserver. • Open the application OrderImportConcurrentProgram-App.jws • Drill down further to the .bpelfile and double-click to open it. Oracle SOA Suite 11g - Sample • Navigate to composite.xmlin the project adapters-apps-101-OrderImportConcurrentProgram. • Edit composite.xml to specify the physical directory for the File Adapter – synchronous read operation getOrderDetails and getCPDetails. /usr/tmp Deploy the BPEL Process • Right click the project ‘adapters-apps-101-OrderImportConcurrentProgram’ under projects in the Application Navigator. • Select Deploy -> adapters-apps-101-OrderImportConcurrentProgram->’serverConnection’ • Select the sample and click on Test to test the process. Oracle SOA Suite 11g - Sample • Enter a random string ‘test’ as input and click Testwebservice. • Click on the BPEL process name in the left pane to see the instances. • Click on the latest Instance ID under Instancestab to see the process flow trace. Oracle SOA Suite 11g - Sample VERIFYING EXECUTION • Navigate to Oracle Applications forms to check the status of the Concurrent Program execution. • The creation of purchase order could also be checked using Oracle Forms Application. • Log on to Oracle Self service Application on the target database as Operations/welcome Oracle SOA Suite 11g - Sample • Choose responsibility Order Management Super User, Vision Operations (USA)from the left panel. • Choose Sales Order. Oracle forms application would open up. Oracle SOA Suite 11g - Sample • Press F11to search for Order. In Customer PO field enter order_id_01 (order id present in xml file) and press CTRL+F11keys • Details of newly created order with Order id = 'order_id_01' could be seen. Press on Line items tab. • Check the quantity and item type of the order is same as that present in xml file • The following query also can be used from the backend to check if the order is created Select orig_sys_document_ref from oe_order_headers_all where orig_sys_document_ref = ‘order_id_01'; BUILDING THE SAMPLE PROCESS FLOW • Receive activity – A dummy input from the BPEL Console triggers this. Oracle SOA Suite 11g - Sample • An invoke activity that points to a File Adapter – Performs synchronous Read operation to read the “Purchase Order” from the input file. • An invoke activity that points to an Oracle Applications Adapter – Inserts data into Open Interface tables. • An assign activity -- This sets the output of the File Adapter’s Sync Read service to the input of the Oracle Applications Adapter’s Open Interface service. • An invoke activity that points to the associated Oracle Applications Adapter – Invokes the Concurrent Program service. • An invoke activity that points to a File Adapter – Performs synchronous Read operation to read the payload for the Concurrent Program from the input file. • An assign activity to set the payload of the Concurrent Program service. • Set Apps Adapter Context - Set Apps Adapter contextfor the invocation of the Concurrent Program Service DESIGN THE BPEL PROCESS Launch JDeveloper. Create a service Connection • Select view->Database Navigator. • Right click on IDE Connections and select New Connection to create a new database connection. • Enter the following values in the Create Database Connectionwindow. Oracle SOA Suite 11g - Sample • Click OK. Create a New Application • Click New Applicationin the Application Navigator. • Enter OrderImportConcurrentProgram-Appas the Application Name. • Select SOA Applicationin the Application Template. • Click Next Oracle SOA Suite 11g - Sample • Enter adapters-apps-101-OrderImportConcurrentProgramas the Project Name • Select SOAfrom the Project Technologies. • Click Next Oracle SOA Suite 11g - Sample • Select Composite With BPEL from the Composite Template. • Click Finish • Enter OrderImportConcurrentProgramas the name of the BPEL Process. • Click OK Oracle SOA Suite 11g - Sample • Select Asynchronous BPEL Processas the Template. Create Oracle Apps Adapter Partner Link for Open Interfaces • Click BPEL servicesin the Component palate • Click Oracle Applications, drag-and-drop it onto the right ‘Partner Links’ swim lane. Oracle SOA Suite 11g - Sample • Click Nextif the welcome page appears • Enter InsertOrderas the Service Name. • Click Next. • Use icon to select a database connection already created. • Click Next. Oracle SOA Suite 11g - Sample • This opens the window IREP File not present. • Click Noto select interface using the live connection • Click Nextin the Operationpage of the wizard. This opensOracle Applications Module Browser Oracle SOA Suite 11g - Sample • Navigate to Order Management Suite (OM_PF)->Order Management (ONT)-> Sales Order (ONT_SALES_ORDER) -> OpenInterfaces ->Order Management Sales Orders Open Interface -> Tables • Select OE_HEADERS_IFACE_ALL Oracle SOA Suite 11g - Sample • Click OK. . • Click Get Objectto open ‘Oracle Applications Module Browser’ • Navigate to Order Management Suite (OM_PF)->Order Management (ONT)-> Sales Order (ONT_SALES_ORDER) -> OpenInterfaces ->Order Management Sales Orders Open Interface -> Tables Oracle SOA Suite 11g - Sample • Select OE_LINES_IFACE_ALL • Click OK. • Click Next. • Select Insertoperation in the Operation Typepage. • Click Next. • Select OE_HEADER_IFACE_ALLas the root database table. Oracle SOA Suite 11g - Sample • Select the primary key fields as shown in the figure. You can also select multiple fields • Define the relationship between the header and the detail table. Select the parent and child tables. Oracle SOA Suite 11g - Sample • Select the mapping type (1:M, 1:1, or 1:1 with Foreign Key on Child Table). • Associate the foreign key fields to the primary keyfields. • Enter a name for the relationship you are creating.It is optional to specify a name. By default, a name is generated for the relationship. • Click OK. • Click Next. Oracle SOA Suite 11g - Sample • Click Next in the Attribute Filteringpage • Select the defaults and click Nextin the Advanced Optionspage. • Click Finishin the Finishpage. Oracle SOA Suite 11g - Sample Invoke the Partner link • Select BPEL Activities and Componentsin the component palette. • Drag and drop Invokeactivity to point to the above partner link. Oracle SOA Suite 11g - Sample • Drag the arrow of Invokeactivity and link to the InsertOrderPartnerlink • Change the Name of the Invoke activity to Invoke. Oracle SOA Suite 11g - Sample • Click on to create the Input Variable. • Leave the default name for the variable and click OK. • Click Applyand OK. Oracle SOA Suite 11g - Sample Create a File Adapter Partner Link. • Click BPEL Servicesfrom the component palette. • Click File Adapterand drag-and-drop it onto the right Partner Links swim lane. • Click Nextin the welcome page of the wizard. • Enter getOrderDetailsas the service name. • Click Next. • Select defaults in the Adapter Interfacepage Oracle SOA Suite 11g - Sample • Click Next. • Select the radio button Synchronous Read Filein the Operation page and click Next. • Uncheck Delete Files after successful retrieval. • Select Logical Name. • Enter the Directory for Incoming Files (logical Name) as inputDir • Click Next Oracle SOA Suite 11g - Sample • Enter the file Name as order_data.xmland click Next • In the Messagespage, click to select the schema. • Select oeHeadersIfaceAllCollectionfrom the Type Chooser. • Click OK. • Click Nextin the Messages page. Oracle SOA Suite 11g - Sample • Click Finishin the Finish page of the wizard. • Click Applyand then OKto complete the creation of File Adapter – Synchronous Read Operation Partner Link. Invoke the File Adapter Partner Link. • Select BPEL Activities and Componentsin the component palette. • Click the Invokeactivity and drag- and-drop it below the receiveInput. Oracle SOA Suite 11g - Sample • Drag the arrow mark of Invoke activity and link it to the getOrderDetailsPartner link. • Name the invoke activity as Invoke • Click on for Inputand Outputto create Input and Output variables respectively. Oracle SOA Suite 11g - Sample • Click Applyand then OKto complete the invoke activity. Oracle SOA Suite 11g - Sample Create Oracle Applications Partner Link for Concurrent Program • Click BPEL servicesin the Component palette • Drag-and-drop Oracle Applicationsonto the right ‘Partner Links’ swim lane. • Click Next in the welcome page of the wizard. • Enter Service Name as ImportOrderCPin the Service Name page. • Select the connection using the browse and click Next • Click Nofor IREP File not present window to use live connection. • Click Nextin the Operation Page. Oracle SOA Suite 11g - Sample • In the OAM browser, navigate to Order Management Suite (OM_PF)->Order Management (ONT)-> Sales Order (ONT_SALES_ORDER) ->OpenInterfaces ->Order Management Sales Orders Open Interface -> ConcurrentPrograms • Select the concurrent Program OEOIMP. • Click OK. • Click Nextin the Application Interface Page. • Click Finishin the Finish page of the wizard. Oracle SOA Suite 11g - Sample • Click Applyand then click Okto complete the creation of Partner Link. • Drag-and-drop Invokeactivity above the callbackclient • Now link the Invoke activity to the ImportOrderCPpartner Link. • Name the Invoke activity as Invoke. Oracle SOA Suite 11g - Sample • Click on for Input and Output to create Input and Output variables respectively. • Click Applyand then OK. Oracle SOA Suite 11g - Sample Create a File Adapter Partner Link. • Drag-and-drop File Adapteronto the right Partner Links swim lane. • Click Next in the welcome page of the wizard. • Enter getCPDetailsas the service name • Click Next. Oracle SOA Suite 11g - Sample • Select defaults in the Adapter Interfacepage and click Next. • Select the radio button Synchronous Read Filein the Operation page and click Next. • Uncheck Delete Files after successful retrieval. • Select Logical Name. • Enter the Directory for Incoming Files (logical Name) as inputDir. • Click Next Oracle SOA Suite 11g - Sample • Enter the file Name as ‘cp_data.xml’. • In the Messages page, click to select the schema. • Select InputParametersfrom the Type Chooser. • Click OK. Oracle SOA Suite 11g - Sample • Click Nextin the Messages page. Oracle SOA Suite 11g - Sample • Click Finishin the Finish page of the wizard. • Click Applyand then click Okto complete the creation of Partner Link. • Drag-and-drop Invokeactivity below the first invoke activity and link it to the above partner link. Oracle SOA Suite 11g - Sample • Create the input and output variables. Oracle SOA Suite 11g - Sample • Click Applyand then Click OK Configure Assign Activities. • Click the Assign activity from the BPEL Activities and Componentslist. • Drag and drop the Assign activity in between secondand third invoke activities set the output of the File Adapter – Synchronous Read operationto the input of the Oracle Applications Adapter – Open Interfaceoperation • Right click the Assign and choose Edit • Click the Generaltab and Name the Assign activity as SetOrderDetails. • Click the Copy Operationtab and select Copy Operation. Oracle SOA Suite 11g - Sample • Set the output of File Adapter to the input of InsertOrderpartnerlink. • Click OK. Oracle SOA Suite 11g - Sample • Click Applyand OKin the Assign Activity. • Drag-and-drop another Assignactivity above the Invoke activity for ImportOrderCP. • Click the Generaltab and Name the Assign activity as SetCPDetails. • Click the Copy Operationtab and select Copy Operation. Oracle SOA Suite 11g - Sample Set Apps Adapter Context • Right-click the Invokeactivity of ImportOrderCPand select Edit. • Click Propertiestab to set the Header properties. • Select jca.apps.Usernameand click to assign a value to it. Oracle SOA Suite 11g - Sample • Select the radio button Expression. • Enter ‘operations’ in the text box • Click OK. • Similarly enter 'Order Management Super User, Vision Operations (USA)' for jca.apps.Responsibility. • Click OK Oracle SOA Suite 11g - Sample • Enter value as 204for jca.apps.ORG_ID. • Click OK. Oracle SOA Suite 11g - Sample • Click Apply • Click OK. • The end-to-end BPEL process is shown in the figure. Oracle SOA Suite 11g - Sample • Design tab of Composite.xmllooks as shown in the figure. Oracle SOA Suite 11g - Sample • Click the Source tab of Composite.xml • Enter a value of the physical directory for the property inputDirfor the references getOrderDetails and getCPDetails e.g. /usr/tmp Oracle SOA Suite 11g - Sample