Import Inventory Item in EBS R12

To create inventory items using oracle standard APIs please follow the below steps.

• Fetch the Oracle provided Template Id for creating purchasing Item.

         SELECT template_id
            FROM apps.MTL_ITEM_TEMPLATES
            WHERE template_name='Purchased Item';

• Insert the Inventory Item details into Item interface table with above fetched Template id.

        INSERT
           INTO apps.MTL_SYSTEM_ITEMS_INTERFACE
           (
             process_flag,
             set_process_id,
             transaction_type,
             organization_id,
             segment1,
             description,
             PRIMARY_UNIT_OF_MEASURE,
             LIST_PRICE_PER_UNIT,
             TEMPLATE_ID
             )
            VALUES
            (
             1,
             1,
            'CREATE',
             204,              -- Organization ID
            'Coal',            -- Name of the Item
            'Mining Coal', -- Item Description
            'Each',            -- Primary UOM of the item
            1000,             -- Price 
            123                -- Template Id SELECT template_id FROM MTL_ITEM_TEMPLATES WHERE template_name='Purchased Item'
             );

• After inserting run the following concurrent program which creates the corresponding Inventory Items.

          DECLARE
             v_request_id number;
          BEGIN
              apps.FND_GLOBAL.APPS_INITIALIZE(1318,50583,401);
              apps.MO_GLOBAL.SET_POLICY_CONTEXT('S','204');
              v_request_id := apps.Fnd_Request.submit_request
                       (
                         application => 'INV',
                         program     => 'INCOIN',
                         description => NULL,
                         start_time  => SYSDATE,
                         sub_request => FALSE,
                         argument1 => 204,  -- Organization id
                         argument2 => 1,    -- All organizations
                         argument3 => 1,    -- Validate Items
                         argument4 => 1,    -- Process Items
                         argument5 => 1,    -- Delete Processed Rows
                         argument6 => NULL, -- Process Set (Null for All)
                         argument7 => 1,    -- Create or Update Items
                         argument8 => 1     -- Gather Statistics
                       );
              IF ( v_request_id = 0 ) THEN
                    dbms_output.put_line( 'Item Import Program Not Submitted');
              ELSE
                dbms_output.put_line( 'Item Imported Successfully');
              END IF;
            
             END;

Blanket Purchasing Agreement & Requisition Creation from iProcurement in EBS

To create a Requisition from iProcurement in EBS follow the below mentioned steps.

• As iProcurement doesn't provide the options to add a supplier,supplier site, line type and price at the time of creating Requisition, we need to create a Blanket PO Agreement where we can mention all the above mentioned details with the corresponding Item.
• Create a Blanket PO Agreement(BPA) as mentioned in below snapshots.Navigation : Purchasing Vision Operations --> Purchase Order. Fill all the required data and Type should be Blanket Purchase Agreement. 

• Navigate to the iProcurement -> iProcurement Home page. Search the Blanket Purchasing Agreement and then click on to Add to cart Button. It adds the corresponding item to cart. Here we can search multiple BPA and add them to cart. Then click the View cart and Checkout button. 

•   Change the quantity, Need by date, Requester etc information as per requirement and click on Submit button. It submits the requisition for approval and shows the status of the requisition.

• After submission of the inforamtion it will shows the status and created requisition number.

• We can checkout the same requisition by clicking the Requisition tab. It will shows all the requisitions created so far on EBS.

BPEL DB Adapter Polling With Update Sequencing Table

Sometimes we required to poll a table depending upon last polled date time or last polled sequence id. To handle this scenario BPEL DB adapter gives after poll option as given below,

• Update a Sequencing Table
• Update an External Sequencing Table on a Different Database
• Update a Sequencing File

To use the Update a Sequencing Table need to create another table which minimum have two columns. One contains the polling table name and 2nd column contain the last value(ID/Last fetch date). Please follow the below snapshots.

• Create a BPEL process and use a DB adapter to poll the corresponding database records.

• Choose the Table you want to poll.

• After Polling choose the option as Update a Sequencing Table.

• Fill the Details of Sequencing table as mentioned in the comments of the below snapshot.

• The above configuration generates the Polling DB adapter SQL query as given below.

• Now this DB adapter is ready to poll the records depending upon last modify date.

Also see : fetch-validate-activity-log-message

WLST Script : SOA Process Deployment

We can deploy a SOA process using the following ways.

• JDeveloper by connecting to corresponding Weblogic Server
• ANT Script
• WLST Script (Web Logic Scripting Tool)
• Weblogic Enterprise manager by accessing the SAR file
• Hudson tool

WLST Script :

It is command-line scripting interface which can be used to manage and monitor Weblogic server instances and domains. We can execute WLST commands one by one on the command line, it will display the corresponding results. Otherwise we can club all the commands into a python (.py) file and execute it programatically i.e we can use loops,conditions etc.

We can use this interface in two different modes as mentioned below.

• Online Mode : Connected to a running weblogic administration server
• Offline Mode : Not connecting to administration server   

To connect to WLST interface online we need to execute the below steps.

• Goto : cd /u01/Middleware/oracle_soa1/common/bin/
• Execute command :     ./wlst.sh
• It will connect to the WLST interface in offline mode.
• Execute command :    connect('username','password',"t3://host:7001");
• Now it will connected to the Weblogic admin server with Online mode.

We can use the following command in Online mode to deploy SOA processes and SOA MDS.

Export MDS : 

exportMetadata(application='soa-infra',server='soa_server1',toLocation='/u01/temp1/MDSData', docs='/apps/**');

Import MDS : 

importMetadata(application='soa-infra', server='soa_server1',fromLocation='/var/tmp/MDSData',docs="/apps/**");

Plan File Generate :               

sca_generatePlan("/var/tmp/MMpoRequest.xml",composite="/var/RequestorABCS/composite.xml",overwrite=true);

Plan File Validate : 

sca_validatePlan("/u01/temp1/myValRep.xml", "/u01/myplan1.xml",composite="/u01/RequestorABCS/composite.xml");
sca_validatePlan("/u01/temp1/myValRep.xml", "/u01/myplan1.xml", sar="/u01/ProivderABCS_rev1.0.jar");

Deploy Composite : 
sca_deployComposite("http://wlsHost:8001","/u01/RequestorABCS/deploy/RequestorABCS_rev1.0.jar",overwrite=true, user="weblogic",password="admin123",forceDefault=true,partition="default",configplan="/u01/myplan1.xml");

Undeploy Composite :

sca_undeployComposite("http://wlsHost:8001","VarSensor", "1.0", partition='POC');

We can use a configplan file at the time of deploying a SOA process which depends upon another wsdl deployed on a WLS server and we try to deploy it on a different server which not same as the calling wlst resides. The plan file search the location of the wsdl location and replaces with the current deploying wls server location. We can create a single plan file for multiple soa processes.

Deploy Multiple SOA processes using WLST:

For this we need to create a soa bundle SAR file at first by using following steps on Jdeveloper.

Choose those projects which are you need to bundle from the application projects.

Now again go to the Application tab-> Deploy. Here you will get the created SOA Bundle file.

We can deploy the SOA bundle file directly on application server or to the SOA Bundle file(It will create a corresponding JAR file on the Application->Deploy folder. This jar file can be deploy to a remote server using WLST script.).

Also Check : Generate Config Plan File For SOA Composite



Happy Coding :)

Fetch and Display BPEL Validate Activity Fault Message

On using Validate Activity for a BPEL variable, if the values are failed against corresponding XSD it always raises InvalidVariable Runtime Exception, but the actual message is shown on the Audit Trail on Enterprise Manager.

To get the Detail message about the fault which element value or rule is failed against the XSD please follow the below steps.

• Create Mediator whose source type is the message is the corresponding validator XSD type.   
• Pass the variable which you want to validate as input to the mediator.
• Check the Validate XSD option on the  mediator routing rule.

• 
• It will validate the input message against the xsd and raises RemoteFault.
• After that we need to capture the RemoteFault with Catch block. 

        

• Create a Fault variable in the Catch block. It will include Runtime.wsdl automatically into the project.
• Change the Variable type as Rutime.wsdl message type as {http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage.

• Now on executing the BPEL process if any Validation fail the Fault variable part Detail has the actual message.