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

how to set context sensitive dff

make Component Information from global to context sensitive


- unfreeze dff definition
- in refenrece field : $PROFILES$.ORG_ID
 - choose (either displayed or Synch or Required)
-context value fields :

org1
code : org_id say 121 , Name : HOOLAHOOP , description : HOOLAHOOP
within that you can set what attribute column would you like to be enabled for that org.

another org :
code : org_id say 144 , Name : JUMPROPE , description : JUMPROPE
within that you can set what attribute column would you like to be enabled for that org.


write a script to update the attribute category :

update BOM_COMPONENTS_B b
set attribute_category='83'
 WHERE 1 = 1
AND attribute1 IS NOT NULL
and exists ( select 1
  FROM mtl_system_items_B iasy,
       bom_bill_of_materials bom,
       mtl_system_items_B icmp,
       mtl_parameters mp
 WHERE     1 = 1
       AND iasy.organization_id = mp.organization_id
       and mp.organization_id  = bom.organization_id
       AND iasy.inventory_item_id = bom.assembly_item_id
       AND iasy.organization_id = bom.organization_id
       AND bom.bill_sequence_id = b.bill_sequence_id
       AND b.component_item_id = icmp.inventory_item_id
       and icmp.organization_id = bom.organization_id
       )

;
--check for assembly n components.
SELECT SUBSTR (iasy.segment1, 1, 20) "Assembly",
       SUBSTR (icmp.segment1, 1, 20) "Component",
       SUBSTR (comp.component_quantity, 1, 8) "Quantity",
       SUBSTR (comp.effectivity_date, 1, 8) "From",
       SUBSTR (comp.disable_date, 1, 8) "To Date",
       comp.attribute_category,
       comp.attribute1,
       --bom.organization_id,    
--       bom.ALTERNATE_BOM_DESIGNATOR,
--       comp.OPERATION_SEQ_NUM,
       mp.organization_code
--       ,comp.component_item_id
--       , comp.bill_sequence_id
  FROM mtl_system_items_B iasy,
       bom_bill_of_materials bom,
       bom_components_b comp,
       mtl_system_items_B icmp,
       mtl_parameters mp
 WHERE     1 = 1
       AND iasy.organization_id = mp.organization_id
       and mp.organization_id  = bom.organization_id
       AND iasy.inventory_item_id = bom.assembly_item_id
       AND iasy.organization_id = bom.organization_id
       AND bom.bill_sequence_id = comp.bill_sequence_id
       AND comp.component_item_id = icmp.inventory_item_id
       and icmp.organization_id = bom.organization_id
       AND comp.attribute1 IS NOT NULL
       order by 1,2
   
   

SQL-Printer/Driver Queries

SELECT *
  FROM apps.FND_PRINTER_STYLES

;
SELECT PRINTER_DRIVER_NAME,
       USER_PRINTER_DRIVER_NAME,
       PRINTER_DRIVER_METHOD_CODE,
       SPOOL_FLAG,
       SRW_DRIVER,
       COMMAND_NAME,
       ARGUMENTS,
       INITIALIZATION,
       RESET
  FROM apps.FND_PRINTER_DRIVERS
 WHERE 1 = 1 AND printer_driver_name LIKE 'XXCNC%'

;
SELECT * FROM apps.FND_PRINTER_INFORMATION;



SELECT PRINTER_DRIVER_NAME,
       USER_PRINTER_DRIVER_NAME,
       PRINTER_DRIVER_METHOD_CODE,
       SPOOL_FLAG,
       SRW_DRIVER,
       COMMAND_NAME,
       ARGUMENTS,
       INITIALIZATION
  FROM apps.FND_PRINTER_DRIVERS
 WHERE PRINTER_DRIVER_NAME =
          (SELECT printer_driver
             FROM apps.FND_PRINTER_INFORMATION
           
            WHERE PRINTER_STYLE = (SELECT PRINT_STYLE
                                     FROM apps.FND_CONCURRENT_REQUESTS
                                    WHERE REQUEST_ID = :1));

SELECT * FROM apps.FND_PRINTER_DRIVERS;

SELECT * FROM apps.FND_PRINTER_INFORMATION where 1=1
--and PRINTER_STYLE like 'XXCNC%'
;

SELECT PRINTER,
       PRINT_STYLE,
       (SELECT PRINTER_TYPE
          FROM apps.FND_PRINTER
         WHERE PRINTER_NAME = PRINTER)
          PRINTER_TYPE
  FROM apps.FND_CONCURRENT_REQUESTS
 WHERE REQUEST_ID = :1

SQL-remove new line character from tables field

update TABLE_NAME
set COLUMN= replace(replace(COLUMN, CHR(13), ''), CHR(10), '')