API to Cancel Sales order

Below API block is used for the Sales order cancellation.

declare
      v_api_version_number NUMBER := 1;
      v_return_status      VARCHAR2(2000);
      v_msg_count          NUMBER;
      v_msg_data           VARCHAR2(2000);
 
      -- IN Variables --
      v_header_rec         oe_order_pub.header_rec_type;
      v_line_tbl           oe_order_pub.line_tbl_type;
      v_action_request_tbl oe_order_pub.request_tbl_type;
      v_line_adj_tbl       oe_order_pub.line_adj_tbl_type;
 
      -- OUT Variables --
      v_header_rec_out             oe_order_pub.header_rec_type;
      v_header_val_rec_out         oe_order_pub.header_val_rec_type;
      v_header_adj_tbl_out         oe_order_pub.header_adj_tbl_type;
      v_header_adj_val_tbl_out     oe_order_pub.header_adj_val_tbl_type;
      v_header_price_att_tbl_out   oe_order_pub.header_price_att_tbl_type;
      v_header_adj_att_tbl_out     oe_order_pub.header_adj_att_tbl_type;
      v_header_adj_assoc_tbl_out   oe_order_pub.header_adj_assoc_tbl_type;
      v_header_scredit_tbl_out     oe_order_pub.header_scredit_tbl_type;
      v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
      v_line_tbl_out               oe_order_pub.line_tbl_type;
      v_line_val_tbl_out           oe_order_pub.line_val_tbl_type;
      v_line_adj_tbl_out           oe_order_pub.line_adj_tbl_type;
      v_line_adj_val_tbl_out       oe_order_pub.line_adj_val_tbl_type;
      v_line_price_att_tbl_out     oe_order_pub.line_price_att_tbl_type;
      v_line_adj_att_tbl_out       oe_order_pub.line_adj_att_tbl_type;
      v_line_adj_assoc_tbl_out     oe_order_pub.line_adj_assoc_tbl_type;
      v_line_scredit_tbl_out       oe_order_pub.line_scredit_tbl_type;
      v_line_scredit_val_tbl_out   oe_order_pub.line_scredit_val_tbl_type;
      v_lot_serial_tbl_out         oe_order_pub.lot_serial_tbl_type;
      v_lot_serial_val_tbl_out     oe_order_pub.lot_serial_val_tbl_type;
      v_action_request_tbl_out     oe_order_pub.request_tbl_type;
 
      v_user_id      NUMBER := apps.fnd_global.user_id;
      v_resp_id      NUMBER := apps.fnd_global.resp_id;
      v_resp_appl_id NUMBER := apps.fnd_global.resp_appl_id;
      v_org_id       NUMBER;
   BEGIN
 
      -- Setting the Enviroment --
     
      v_user_id      := 43867;
      v_resp_id      := 56633;
      v_resp_appl_id := 660;
      v_org_id       := 116919;
 
      mo_global.init('ONT');
      fnd_global.apps_initialize(user_id => v_user_id, resp_id => v_resp_id,
                                 resp_appl_id => v_resp_appl_id);
      mo_global.set_policy_context('S', v_org_id);
 
      FOR I IN (SELECT oh.header_id order_header_id
                FROM   oe_order_headers_all oh
                WHERE  oh.order_number IN ( 145054061)
                AND    oh.org_id = v_org_id)
      LOOP
        BEGIN
              -- CANCEL HEADER --
              Dbms_Output.put_line ('order header id : ' || i.order_header_id);
          v_header_rec                := oe_order_pub.g_miss_header_rec;
          v_header_rec.operation      := OE_GLOBALS.G_OPR_UPDATE;
          v_header_rec.header_id      := i.order_header_id;
          v_header_rec.cancelled_flag := 'Y';
          v_header_rec.change_reason  := 'Cancel for Test';
     
          dbms_output.put_line( 'Starting cancel of API');
     
          -- CALLING THE API TO CANCEL AN ORDER --
     
          OE_ORDER_PUB.PROCESS_ORDER(p_api_version_number => v_api_version_number,
                                     p_header_rec => v_header_rec,
                                     p_line_tbl => v_line_tbl,
                                     p_action_request_tbl => v_action_request_tbl,
                                     p_line_adj_tbl => v_line_adj_tbl
                                     -- OUT variables
                                    , x_header_rec => v_header_rec_out,
                                     x_header_val_rec => v_header_val_rec_out,
                                     x_header_adj_tbl => v_header_adj_tbl_out,
                                     x_header_adj_val_tbl => v_header_adj_val_tbl_out,
                                     x_header_price_att_tbl => v_header_price_att_tbl_out,
                                     x_header_adj_att_tbl => v_header_adj_att_tbl_out,
                                     x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out,
                                     x_header_scredit_tbl => v_header_scredit_tbl_out,
                                     x_header_scredit_val_tbl => v_header_scredit_val_tbl_out,
                                     x_line_tbl => v_line_tbl_out,
                                     x_line_val_tbl => v_line_val_tbl_out,
                                     x_line_adj_tbl => v_line_adj_tbl_out,
                                     x_line_adj_val_tbl => v_line_adj_val_tbl_out,
                                     x_line_price_att_tbl => v_line_price_att_tbl_out,
                                     x_line_adj_att_tbl => v_line_adj_att_tbl_out,
                                     x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out,
                                     x_line_scredit_tbl => v_line_scredit_tbl_out,
                                     x_line_scredit_val_tbl => v_line_scredit_val_tbl_out,
                                     x_lot_serial_tbl => v_lot_serial_tbl_out,
                                     x_lot_serial_val_tbl => v_lot_serial_val_tbl_out,
                                     x_action_request_tbl => v_action_request_tbl_out,
                                     x_return_status => v_return_status,
                                     x_msg_count => v_msg_count,
                                     x_msg_data => v_msg_data);
     
          dbms_output.put_line( 'Completion of API');
     
          IF v_return_status = fnd_api.g_ret_sts_success THEN
             COMMIT;
             dbms_output.put_line(
                               'Order Cancellation Success : ' ||
                                v_header_rec_out.header_id);
          else
             dbms_output.put_line(
                               'order cancellation failed:' || v_msg_data);
             rollback;
             for i in 1 .. v_msg_count
             loop
                v_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'f');
                dbms_output.put_line( i || ') ' || v_msg_data);
             end loop;
          end if;
        exception
          when others then
             dbms_output.put_line('error in cancel_sales_order_api. reason is :' || sqlerrm);       
        end;
      end loop;
   exception
      when others then
         dbms_output.put_line('error in block. reason is :' || sqlerrm);
     
   end ;

How To Setup Oracle Business Event

The Oracle Workflow Business Event System is an application service that leverages the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems. The Business Event System consists of the Event Manager and workflow process event activities.

The Event Manager contains a registry of business events, systems, named communication agents within those systems, and subscriptions indicating that an event is significant to a particular system. Events can be raised locally or received from an external system or the local system through AQ. When a local event occurs, the subscribing code is executed in the same transaction as the code that raised the event, unless the subscriptions are deferred.

Subscriptions can include the following types of processing:

• Executing custom code on the event information
• Sending event information to a workflow process
• Sending event information to other queues or systems
• Sending a notification based on the event information
• Sending or receiving an Oracle XML Gateway message
• Invoking a business process execution language (BPEL) process or other Web service

The uses of the Business Event System include:

• System integration messaging hubs - Oracle Workflow with the Business Event System can serve as a messaging hub for complex system integration scenarios. The Event Manager can be used to "hard-wire" routing between systems based on event and originator. Workflow process event activities can be used to model more advanced routing, content-based routing, transformations, error handling, and so on.
• Distributed applications messaging - Applications can supply generate and receive event message handlers for their business entities. For example, message handlers can be used to implement master/copy replication for distributed applications.
• Message-based system integration - You can set up subscriptions which cause messages to be sent from one system to another when business events occur. In this way, you can use the Event Manager to implement point-to-point messaging integration.
• Business event-based workflow processes - You can develop sophisticated workflow processes that include advanced routing or processing based on the content of business events.
• Non-invasive customization of packaged applications - Analysts can register interesting business events for their internet or intranet applications. Users of those applications can register subscriptions to those events to trigger custom code or workflow processes.

 Lets see in details, how to set up the business event.     

      

Go to system administrator responsibility.

Setup Event:

Navigate to workflow à Administrator workflowà Business events

Search for the below business event.

If event status is not enabled, then go to event details and make it enable.

oracle.apps.wsh.delivery.gen.shipconfirmed

Setup event subscription: 

Click on subscription.

                Click on create subscription. Give below details.

System: <System name from lookup>

Source type: Local

Event filter: oracle.apps.wsh.delivery.gen.shipconfirmed

Phase: 105 (enter some number greater than 100)

Status: Enabled

Rule Data: Key

Action Type: Custom

On Error: Stop and Rollback

Click on next.

PL/SQL Rule Function: WF_RULE.DEFAULT_RULE

Out agent: WF_BPEL_QAGENT@<System name>

Priority: Normal

Owner Name: Shipping Execution

Owner Tag: WSH

Description: Subscription for enqueuing event in WF_BPEL_Q

Apply and save the changes.

Setup event queue subscriber

Now, we need to create the subscriber for the SOA Queue.

To create subscriber, Run below script.

DECLARE

subscriber sys.aq$_agent;

BEGIN

subscriber := sys.aq$_agent('WF_BPEL_Q', NULL, 0);

DBMS_AQADM.ADD_SUBSCRIBER(queue_name => 'APPS.WF_BPEL_Q',

subscriber => subscriber,

rule => 'tab.user_data.event_name=''' ||

'oracle.apps.wsh.delivery.gen.shipconfirmed' || '''');

END;

  

Restart the workflow agent listener service

Once finished with above steps.

Restart the workflow agent listened service.

Go to system administrator responsibility.

Navigate to: Concurrent à Manager à Administer

Search for: Workflow Agent Listener Service

And click on Restart.

Note: Whenever you change any setup on event/ event subscriptions/ queue subscriber, restart the Workflow Agent Listener Service to reflect the change. Else Event stops working.

Imp Queries related to events:

-------------- Query to Check the subscribers of the event ---------------

select * from wf_event_subscriptions wes

where wes.event_filter_guid in (select we.guid

from wf_events we where we.name= ‘oracle.apps.wsh.delivery.gen.shipconfirmed’

union

select weg.group_guid from wf_event_groups weg

where weg.member_guid in (select we.guid

from wf_events we where we.name= ‘oracle.apps.wsh.delivery.gen.shipconfirmed’)

);

--------------- Trigger business event from backend -----------------

   declare

  l_parameters wf_parameter_list_t := wf_parameter_list_t();

begin

  wf_log_pkg.init(1, null, 1, 'wf%');

  wf_event.addparametertolist(p_name          => 'DELIVERY_ID',

                              p_value         => '9169530',

                              p_parameterlist => l_parameters);

  wf_event.raise(p_event_name => 'oracle.apps.wsh.delivery.gen.shipconfirmed',

                 p_event_key  => '9169530',

                 p_event_data => NULL,

                 p_parameters => l_parameters,

                 p_send_date  => sysdate);

  commit;

end;

----------------- Event Error Table ---------------------------

SELECT * FROM WF_ERROR   ;

---------- Query to find event parameters/ key details --------------

SELECT wd.user_data.event_name,

       sender_name,

       sender_address,

       sender_protocol,

       wd.user_data.event_key,

       n.NAME parameter_name,

       n.VALUE parameter_value,

       wd.user_data.error_message,

       wd.user_data.error_stack,

       wd.msgid,

       wd.delay

  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n

 WHERE  1=1

AND wd.user_data.event_name LIKE '%oracle.apps.wsh.delivery.gen.shipconfirmed%'

 ORDER BY wd.user_data.send_date DESC,

          wd.user_data.event_name,

          wd.user_data.event_key,

          n.NAME

--------------- Query to get triggered event details -------------------

 select * from WF_DEFERRED WHERE CORRID = 'oracle.apps.wsh.delivery.gen.shipconfirmed';

   select CONSUMER_NAME, ADDRESS,  PROTOCOL,  EXPIRATION_REASON,

tab.* from AQ$WF_BPEL_QTAB tab  where tab.user_data.event_name = 'oracle.apps.wsh.delivery.gen.shipconfirmed' order by ENQ_TIME DESC   ;

    SELECT * FROM APPs.WF_BPEL_QTAB ;

--------------- Query to get queue subscriber details ----------------

 SELECT * FROM ALL_QUEUE_SUBSCRIBERS WHERE QUEUE_NAME = 'WF_BPEL_Q' ;

When, Event activity happens and Event is triggered for the given delivery ID.

You can check the entry in event tables with below queries.

   select DEQ_TIME, DEQ_TID, a.* from WF_DEFERRED a WHERE CORRID = 'APPS:oracle.apps.wsh.delivery.gen.shipconfirmed';

If DEQ_TIME, DEQ_TID is not null then, Data is been dequeued to BPEL Queue.

If DEQ_TIME, DEQ_TID is null then, we need to raise ticket to bounce the workflow manager “Workflow agent listener”.

Retest the process after bounce.

  Now check below query.

      

select CONSUMER_NAME, MSG_STATE ,DEQ_TIME , DEQ_TIMESTAMP,

tab.* from apps.AQ$WF_BPEL_QTAB tab 

where tab.user_data.event_name = 'oracle.apps.wsh.delivery.gen.shipconfirmed' order by ENQ_TIME DESC   ;

If MSG_STATE is ‘PROCESSED’ then the Data is received at SOA end.

If MSG_STATE is other than Processed then ask SOA to restart the process.

Query to check parameters passed to event:

SELECT wd.user_data.event_name,

       wd.user_data.event_key,

       rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,

       n.NAME parameter_name,

       n.VALUE parameter_value,

       wd.user_data.error_message,

       wd.user_data.error_stack,

       wd.msgid,

       wd.delay

  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n

 WHERE  1=1

 AND wd.user_data.event_name LIKE '%shipconfirmed%'

 ORDER BY wd.user_data.send_date DESC,

          wd.user_data.event_name,

          wd.user_data.event_key,

          n.NAME;

Oracle Inbound Outbound Process flow with SOA

Outbound process flow:

Eg. Post real time transactions for sales order to third party.

1. Sales order is created.

2. Business event will be triggered to enter the SO header ID in staging table. (this staging table acts as Oracle advanced queue) this action event trigger can be custom code to feed header ID in staging table or oracle advanced queue.

3. Enqueue process is message passing from producer application to OAQ.

4. The staging table (also can be oracle advanced queue) is associate to AQ adapter. Staging table has the header ID.

5. Dequeue process passes message from OAQ to consumer application i.e. SOA.

6. AQ adapter continuously listen to the associates stg table or OAQ. As soon as the header id is enqueued in stg table, AQ adapter dequeues it and passes to DB adapter.

7. There is DB Adapter setup for every interface packages communicating to SOA. On receiving the header ID, this DB adapter calls the interface package.

8. Interface package sends the output data in OUT parameter to SOA.

9. SOA transforms the output data into some third party readable structure like XML script.

10. Third party adapter posts data to the database

Inbound Process flow

For real time inbound processing, the file will be placed by legacy system in a specific location.

1. Legacy team will inform the file location to SOA team.

2. SOA team will develop the file adapter for location which will continuously listen to file upload.

3. Received file is transformed as per the oracle readable standards.

4. DB adapter will call the interface package with input as legacy data.
5. Interface package performs the operations and post transaction to database.