SQL to Retry OEOH workflow activity

declare

    cursor c_orders_to_retry is

    SELECT P.INSTANCE_LABEL,

           WAS.ITEM_KEY,

           H.ORDER_NUMBER,

           H.ORG_ID,

           was.begin_date

      FROM WF_ITEM_ACTIVITY_STATUSES WAS,

           WF_PROCESS_ACTIVITIES P,

           OE_ORDER_HEADERS_ALL H

    WHERE     TO_NUMBER (WAS.ITEM_KEY) = H.HEADER_ID

           AND WAS.PROCESS_ACTIVITY = P.INSTANCE_ID

           AND P.ACTIVITY_ITEM_TYPE = 'OEOH'

           AND P.ACTIVITY_NAME = 'CLOSE_WAIT_FOR_L'

           AND WAS.ACTIVITY_STATUS = 'NOTIFIED'

           AND WAS.ITEM_TYPE = 'OEOH'

           AND NOT EXISTS

                      (SELECT /*+ NO_UNNEST */

                             1

                         FROM OE_ORDER_LINES_ALL

                        WHERE     HEADER_ID = TO_NUMBER (WAS.ITEM_KEY)

                              AND OPEN_FLAG = 'Y')

           --and h.order_number = '4214653'

           ;

    l_update_flag   varchar2(1) := 'N';      

begin

    dbms_output.put_line('Update Flag : '||l_update_flag);

    for o in c_orders_to_retry

    loop

        if l_update_flag = 'Y' then

            MO_GLOBAL.set_policy_context ('S', o.org_id);

            WF_ENGINE.HANDLEERROR('OEOH', o.ITEM_KEY, o.INSTANCE_LABEL, 'RETRY',NULL);

        end if;

        dbms_output.put_line('Order : '||o.order_number);

    end loop;

    --commit;

exception

when others then

    dbms_output.put_line('Error : '||sqlerrm);

end;    

Find Order number for given pick release rule

Select order number for pick release rule :

  SELECT DISTINCT wdd.source_header_number,

                  otl.name,

                  SUBSTRB (party.party_name, 1, 50) customer_name,

                  wdd.source_header_id,

                  wdd.source_header_type_id,

                  cust_acct.cust_account_id customer_id

    FROM wsh_delivery_details wdd,

         hz_parties party,

         hz_cust_accounts cust_acct,

         WSH_PICKING_RULES wpr,

         oe_transaction_types_tl otl

   WHERE     wdd.customer_id = cust_acct.cust_account_id

         AND cust_acct.party_id = party.party_id

         AND wdd.released_status IN ('B', 'R', 'X')

         AND wdd.organization_id = wpr.organization_id

         AND wdd.subinventory = NVL (wpr.rel_subinventory, wdd.subinventory)

         AND otl.language = USERENV ('LANG')

         AND otl.transaction_type_id = wdd.source_header_type_id

         AND otl.name = 'Order type'

         AND wpr.name = NVL ( :RELEASE.PICKING_RULE, wpr.name)

ORDER BY 2, 1

Pick Release Rule

SELECT wpr.NAME picking_rule_name,
                    wpr.backorders_only_flag,
                    DECODE (s2.meaning,
                            'Exclude', 'Unreleased',
                            'Include', 'All',
                            'Only', 'Backordered')
                       backorders,
                    s1.meaning shipment_priority,
                    wpr.shipment_priority_code,
                    wpr.organization_id,
                    wpr.organization_id org_id,
                    wpr.organization_id warehouse_id_lov,
                    h_org_tl.NAME warehouse,
                    org.organization_code warehouse_code,
                    DECODE (wpr.existing_rsvs_only_flag, 'Y', '*', NULL)
                       reservation_star,
                    wpr.existing_rsvs_only_flag,
                    wpr.ship_method_code,
                    wpr.from_scheduled_ship_date,
                    wpr.to_scheduled_ship_date,
                    wpr.from_requested_date,
                    wpr.to_requested_date,
                    ott.NAME order_type,
                    wpr.order_type_id,
                    TO_CHAR (h.order_number) order_number,
                    wpr.order_header_id,
                    os.set_name ship_set_number,
                    wpr.inventory_item_id,
                    msi.description item_description,
                    SUBSTRB (party.party_name, 1, 50) customer_name,
                    wpr.customer_id,
                    whzl.ui_location_code ship_to_location,
                    wpr.ship_to_location_id,
                    wpr.include_planned_lines,
                    wpr.autocreate_delivery_flag,
                    pgr.NAME pick_grouping_rule_name,
                    rsqr.NAME pick_seq_rule_name,
                    wpr.task_id,
                    wpr.project_id,
                    wpr.autodetail_pr_flag,
                    wpr.auto_pick_confirm_flag,
                    wpr.ship_set_number ship_set_id,
                    whrl.ui_location_code ship_from_location,
                    wpr.ship_from_location_id,
                    wpr.document_set_id,
                    wrs.NAME doc_name,
                    wpr.pick_from_subinventory,
                    wpr.pick_from_locator_id,
                    NULL pick_from_locator,
                    wpr.default_stage_subinventory,
                    wpr.default_stage_locator_id,
                    NULL default_stage_locator,
                    sm.meaning ship_method_meaning,
                    wpr.autopack_flag,
                    wpr.autopack_level,
                    wpr.ship_confirm_rule_id,
                    wscr.NAME ship_confirm_rule_name,
                    wpr.task_planning_flag,
                    wpr.region_id,
                    wpr.zone_id,
                    wpr.ac_delivery_criteria,
                    wpr.rel_subinventory,
                    wpr.category_set_id,
                    wpr.category_id,
                    wpr.sch_start_days,
                    wpr.sch_end_days,
                    wpr.sch_start_hours,
                    wpr.sch_end_hours,
                    wpr.req_start_days,
                    wpr.req_end_days,
                    wpr.req_start_hours,
                    wpr.req_end_hours,
                    wpr.append_flag,
                    wpr.task_priority,
                    wpr.allocation_method,
                    wpr.crossdock_criteria_id,
                    wpr.dynamic_replenishment_flag,
                    wpr.client_id client_id,
                    mcpv.client_name client_name
               FROM wsh_picking_rules wpr,
                    mtl_parameters org,
                    hr_all_organization_units_tl h_org_tl,
                    hz_parties party,
                    hz_cust_accounts cust_acct,
                    fnd_lookup_values s1,
                    fnd_lookup_values s2,
                    oe_sets os,
                    wsh_pick_grouping_rules pgr,
                    wsh_pick_sequence_rules rsqr,
                    oe_transaction_types_tl ott,
                    oe_transaction_types_all otb,
                    oe_order_headers_all h,
                    wsh_locations whrl,
                    wsh_locations whzl,
                    wsh_report_sets wrs,
                    wsh_ship_confirm_rules wscr,
                    mtl_system_items_b msi,
                    fnd_lookup_values sm,
                    mtl_client_parameters_v mcpv
              WHERE     wpr.organization_id =
                           NVL (fnd_profile.VALUE ('MFG_ORGANIZATION_ID'),
                                wpr.organization_id)                -- changed
                    AND wpr.name =
                           NVL (
                              fnd_PROFILE.VALUE ('XXCNC_WMS_DEFAULT_PICK_RULE'),
                              wpr.name)
                    AND org.organization_id(+) = wpr.organization_id
                    AND h_org_tl.organization_id(+) = wpr.organization_id
                    AND cust_acct.cust_account_id(+) = wpr.customer_id
                    AND cust_acct.party_id = party.party_id(+)
                    AND h_org_tl.LANGUAGE(+) = USERENV ('LANG')
                    AND pgr.pick_grouping_rule_id(+) =
                           wpr.pick_grouping_rule_id
                    AND rsqr.pick_sequence_rule_id(+) =
                           wpr.pick_sequence_rule_id
                    AND wpr.ship_confirm_rule_id = wscr.ship_confirm_rule_id(+)
                    AND s1.lookup_code(+) = wpr.shipment_priority_code
                    AND s1.lookup_type(+) = 'SHIPMENT_PRIORITY'
                    AND s1.LANGUAGE(+) = USERENV ('LANG')
                    AND s1.view_application_id(+) = 660
                    AND s1.security_group_id(+) = 0
                    AND s2.lookup_code = wpr.backorders_only_flag
                    AND s2.lookup_type = 'PICK_RELEASE_OPTIONS'
                    AND s2.LANGUAGE(+) = USERENV ('LANG')
                    AND s2.view_application_id(+) = 660
                    AND s2.security_group_id(+) = 0
                    AND otb.transaction_type_id(+) =
                           NVL (wpr.order_type_id, -1)
                    AND otb.transaction_type_code(+) = 'ORDER'
                    AND otb.transaction_type_id = ott.transaction_type_id(+)
                    AND ott.LANGUAGE(+) = USERENV ('LANG')
                    AND whzl.wsh_location_id(+) =
                           NVL (wpr.ship_to_location_id, -1)
                    AND whrl.wsh_location_id(+) =
                           NVL (wpr.ship_from_location_id, -1)
                    AND wrs.report_set_id(+) = wpr.document_set_id
                    AND os.set_id(+) = NVL (wpr.ship_set_number, -1)
                    AND h.header_id(+) = NVL (wpr.order_header_id, -1)
                    AND msi.inventory_item_id(+) = wpr.inventory_item_id
                    --AND msi.organization_id(+) = :parameter_organization_id
                    AND TRUNC (SYSDATE) BETWEEN NVL (wpr.start_date_active,
                                                     TRUNC (SYSDATE))
                                            AND NVL (wpr.end_date_active,
                                                     TRUNC (SYSDATE) + 1)
                    AND sm.lookup_type(+) = 'SHIP_METHOD'
                    AND sm.lookup_code(+) = wpr.ship_method_code
                    AND sm.view_application_id(+) = 3
                    AND sm.LANGUAGE(+) = USERENV ('LANG')
                    AND mcpv.client_id(+) = wpr.client_id
--put yout organization_id to filter the lsst
   ORDER BY picking_rule_name

workflow access protection

During an Oracle Workflow seed data upgrade, the Workflow Definitions Loader is always run in Upgrade mode at an access level less than 100. As a result, the upgrade will not overwrite any object with a customization level of 100 or higher

Oracle E-Business Suite customers should select both the Preserve Customizations and Lock at this Access Level check boxes to protect your workflow objects during upgrades

How to auto deploy alerts from one instance to another instance using a command line script?

A: Steps which needs to be followed

1. Command to download

    FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD
    $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
   APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to download

2. Command to upload

    FNDLOAD apps_user_name/apps_password 0 Y UPLOAD
    $ALR_TOP/patch/115/import/alr.lct my_file.ldt 

How to Integrate Business Events in Concurrent Processing?

How to Integrate Business Events in Concurrent Processing? 

SOLUTION

This functionality has been introduced in Oracle Application Release 12.1.

Concurrent programs can be integrated with the Business Event System in Oracle Workflow. Business events can be raised at key points of the life cycle of a request to run a concurrent program. Users can subscribe to the business events and create their own business processes interacting with the concurrent programs. 

Navigation

System Administrator Responsibility > Concurrent > Program > Define

Here you specify the points at which business events are enabled. The possible points are:

• Request Submitted
• Request On Hold
• Request Resumed
• Request Running
• Program Completed
• Post Processing Started
• Post Processing Ended
• Request Completed

Possible parameters for each event are: 

• REQUEST_ID 
• REQUESTED_BY 
• PROGRAM_APPLICATION_ID 
• CONCURRENT_PROGRAM_ID 
• STATUS 
• COMPLETION_TEXT 
• TIME_STAMP

Change profile 

There is a new profile "Concurrent: Business Intelligence Integration Enable". This has to be set to "Yes" to enable Business Events from Concurrent Processing System

Workflow - Create Event & Subscription to link Events

You need to create Event Subscription from Workflow Administrator. An event subscription is a registration indicating that a particular event is significant to a particular system. An event subscription specifies the processing to perform when the triggering event occurs.

  

For EXAMPLE - Refer this Note

How To Use Business Events To Send Notifications when a Spawned Report Completes (Note 1506980.1 )

How to Send Email Notification from one report that is a part of a Payment Process that creates multiple spawned reports?

Usually the method of Sending Email Notifications with a report is allowed as a Delivery Option in the Concurrent Request Submission form, but it is only applicable to a single report.  In the case when the concurrent program is similar to a request that spawns multiple reports there is no option to choose an individual report from the parent report. 

Note 1161254.1 - "EBS 12.1 How to Integrate Business Events in Concurrent Processing?" describes how a Business Event can be used with a Concurrent Program.  In addition to the steps in Note 1161254.1, the ability to "Send Notification" through a Business Event is documented below. 

FIX

In Release 12.1 and up, there is a the ability to create a Business Event Subscription that is triggered when a Concurrent Program is run. The Business Event feature offers customer's the option of Notifying through email by selecting the "Action Type = Send Notification". 

NOTE: These steps should be used in conjunction with Note: 1161254.1 EBS 12.1 How to Integrate Business Events in Concurrent Processing?

1.  Login to E-Business Suite as SYSADMIN.

- Enable profile option: Concurrent: Business Intelligence Integration setting Enable=Y at the Site Level > and SAVE.

2. In step 1 of Note 1161254.1, one is telling the concurrent program that once it completes it should raise the event indicating that the request has reached any of those status in the squared area (submitted, resumed, completed, etc). 
    Select "Program Completed"

Ex:

    a) Concurrent > Program > Define

    b) Choose Concurrent Request: "Format Payment Instruction with Text Output" IBY_FD_PAYMENT_FORMAT_TEXT  > Check the "Program Completed" > SAVE 

3. Check that the proper System Name is associated with the "Business Event Local System":

   a) Login to E-Business Suite as SYSADMIN.

   b) Select "Workflow Administrator Web Applications" Responsibility.

   c) Select "Administrator Workflow"  > then "Administration".

   d) Select the proper system name under Business Event Local System > Apply.

4. Check that the Business Event oracle.apps.fnd.concurrent.program.completed is enabled:

   a) Business Event Tab > Query the Name"oracle.apps.fnd.concurrent.program.completed" > Go.

   b) Change the status from Disabled to Enabled.

   c) Click on Apply button.

5. Create the Subscription for the Business Event "oracle.apps.fnd.concurrent.program.completed":

  a)  Click on the "Subscription" Icon > Create Subscription.  

  b) Give the specifics for the Subscription:

  *** This is an Example of Required Fields when creating the Subscription

  *** This is where to specify the "Action Type = Send Notification"

Create Subscription
--------------------------
Subscriber

* System = VIS.US.ORACLE.COM


Triggering Event
------------------
* Source Type = Local

* Event Filter = oracle.apps.fnd.concurrent.program.completed

* Source Agent =


Execution Condition
-----------------------
* Phase = 100

* Status = Enabled

* Rule Data = Message


Action Type
-----------------
  Action Description
  -------- ------------
* Action Type = Send Notification Send a notification using standard or your own message templates


* On Error = Stop and Rollback


Action

* Message Type = CSM_MSGS (this an example)

* Message Name = FYI_MESSAGE

* Recipient = USER_A

* Priority = Normal

Documentation
---------------

* Owner Name = FND

* Owner Tag = FND

  > APPLY 

  
6. Check to make sure the Subscription is added to the Business Event:

   a) Business Event > Query - oracle.apps.fnd.concurrent.program.completed > Drill down on "Subscriptions" 

7. Check whether raising the business event is now working, by select "Run":

 Business Events > Query > oracle.apps.fnd.concurrent.program.completed 

  Click Test (over in the right side) 

  Notice: Business Event has been raised - message.

8. Check the WF_NOTIFICATIONS table to see if a new record is created:

  In sqlplus apps/, enter the following select:

SQL> select * from wf_notifications order by 1 desc

  NOTIFICATION_ID 
  4682893 4682893 WFERROR DEFAULT_EVENT_ERROR SYSADMIN OPEN 203626643180155389996808706742823752864 SENT
  4682892 4682892 WFSTD WFSTD_DEFAULT_VOTE USER_A OPEN 127521793540816397462516875018174582244 SENT

Note: that the subscription has generated a Notification and was Sent by the Workflow Mailer.

9. Now test running the Concurrent Program that should trigger the oracle.apps.fnd.concurrent.program.completed Business Event.
 

convert sql statement output to xml

refernce :

http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/

sql :
select XMLELEMENT("ORDERS",
       XMLELEMENT("ORDER",
       XMLFOREST('RETURN' AS "ORD_CAT_COD",
                  18184 AS "SOLD_TO_ORG",
                  TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS "ORD_DATE",
                  'R'||OH.CUST_PO_NUMBER AS "PURCHASE_ORDER",
                  'RET-'||OH.ORIG_SYS_DOCUMENT_REF AS "SOURCE_DOC_REF",
                  OH.ATTRIBUTE1 AS "H_ATTR1",
                  OH.ORIG_SYS_DOCUMENT_REF AS "ORIG_SOURCE_DOC_REF",
              (SELECT XMLAGG(XMLELEMENT("LINE",
                     XMLFOREST(OL.ATTRIBUTE4 AS "L_ATTR4",
                              OL.ATTRIBUTE2 AS "L_ATTR2",
                              OL.ATTRIBUTE1 AS "L_ATTR1",
                              OL.ATTRIBUTE3 AS "L_ATTR3",
                              OL.ATTRIBUTE8 AS "L_ATTR13",
                              OL.ORDERED_QUANTITY AS "ORD_QTY",
                              OL.ORDERED_ITEM AS "ORD_ITEM",
                              OL.UNIT_SELLING_PRICE AS "UNIT_SELL",
                              OL.TAX_VALUE AS "TOTAL_TAX",
                              OL.INVENTORY_ITEM_ID AS "ITEM_ID",
                              OL.LINE_NUMBER AS "SRC_LINE_NUM",
                              OL.ORIG_SYS_LINE_REF AS "ORIG_REFERENCE_LINE_ID",
                              'SHORT' AS RETURN_REASON )))
                FROM OE_ORDER_LINES_ALL OL
                WHERE OL.HEADER_ID = OH.HEADER_ID
              ) LINES
              )))
FROM OE_ORDER_HEADERS_ALL OH
WHERE 1=1
--AND OH.HEADER_ID = OL.HEADER_ID
AND OH.ORDER_NUMBER = '5638446'



xml:
RETURN181842016-04-14R0058RET-ECH123KCNECH1238224659615INMATE, RICKY CBL|TI|86029002653.94568111SHORT8224659615INMATE, RICKY CBL|TI|860171851.55.0934855222SHORT8224659615INMATE, RICKY CBL|TI|86019303653.95411444SHORT8224659615INMATE, RICKY CBL|TI|86019202653.95421355SHORT8224659615INMATE, RICKY CBL|TI|86019308653.95424066SHORT8224659615INMATE, RICKY CBL|TI|86018529653.95433077SHORT8224659615INMATE, RICKY CBL|TI|860167001.65.0995460988SHORT8224659615INMATE, RICKY CBL|TI|860181591.75.1055745399SHORT8224659615INMATE, RICKY CBL|TI|8601712613.75.825610711010SHORT