How to Hide Reassign and Request for information button in workflow notification in oracle apps

HIDE REASSIGN BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.

HIDE REQUEST FOR INFORMATION BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.

How to send attachment in oracle workflow notification / blob documents as attachment in oracle workflow notification

Create normal workflow process with simple notification as below.

Create a new document type attribute with below details.

drag and drop this attribute under "Attachment_notification"(notification message). Set the Attributes property as shown below.

Your workflow is complete. Now write the following PL/SQL code and run the workflow to get the notification. 

Sequence for item key :::

create sequence xx_example_s

start with 1

increment by 1;

CREATE OR REPLACE PACKAGE xx_example

IS

   PROCEDURE xx_training_wf;

   PROCEDURE xx_attach_p (

      document_id     IN       VARCHAR2,

      display_type    IN       VARCHAR2,

      document        IN OUT   BLOB,

      document_type   IN OUT   VARCHAR2

   );

END;

CREATE OR REPLACE PACKAGE BODY xx_example

IS

   gv_document_id   VARCHAR2 (100) := '2955797';

   PROCEDURE xx_training_wf

   IS

      l_itemtype   VARCHAR2 (30)  := 'XXTEST';

      l_itemkey    VARCHAR2 (300);

   ---l_file_name   VARCHAR2 (100) := 'holidaylist.xls';

   --l_unique_id   VARCHAR2 (50)  := unique_id;

   BEGIN

      SELECT TO_CHAR (xx_example_s.NEXTVAL)

        INTO l_itemkey

        FROM DUAL;

      wf_engine.createprocess (l_itemtype, l_itemkey, 'XX_TEST_ATTACHMENT');

      wf_engine.setitemattrdocument

                         (itemtype        => l_itemtype,

                          itemkey         => l_itemkey,

                          aname           => 'XX_ATT1',

                          documentid      =>    'PLSQLBLOB:XX_EXAMPLE.XX_ATTACH_P/'

                                             || gv_document_id

                         );

      wf_engine.setitemattrtext (itemtype      => l_itemtype,

                                 itemkey       => l_itemkey,

                                 aname         => '#FROM_ROLE',

                                 avalue        => 'WF_EMP1'

                                );

      wf_engine.setitemattrtext (itemtype      => l_itemtype,

                                 itemkey       => l_itemkey,

                                 aname         => 'XXTIMEOUT',

                                 avalue        => 2880

                                );

      wf_engine.startprocess (l_itemtype, l_itemkey);

      COMMIT;

   END xx_training_wf;

   PROCEDURE xx_attach_p (

      document_id     IN       VARCHAR2,

      display_type    IN       VARCHAR2,

      document        IN OUT   BLOB,

      document_type   IN OUT   VARCHAR2

   )

   IS

      lv_file_name           VARCHAR2 (100) := NULL;

      lv_file_content_type   VARCHAR2 (100) := NULL;

      ld_document            BLOB;

   BEGIN

      SELECT file_name, file_content_type, file_data

        INTO lv_file_name, lv_file_content_type, ld_document

        FROM fnd_lobs

       WHERE file_id = 2955797;

      document_type := lv_file_content_type || ';name=' || lv_file_name;

      DBMS_LOB.COPY (document, ld_document, DBMS_LOB.getlength (ld_document));

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.CONTEXT ('xx_example',

                          'xx_attach_p',

                          'document_id',

                          'display_type'

                         );

         RAISE;

   END xx_attach_p;

END xx_example;

====================================================

Now execute procedure  to initiate the workflow.

execute xx_example.xx_training_wf ;

====================================================

Attachment will look like this

TImeout functionality in workflow notification / Due date of notification in workflow

- Log in to workflow builder , open the workflow process

select the notification , right click and select properties then click on node tab.

it will be as below.

In the timeout section you can select timeout properties.

There are 3 options.


1- No timeout : It means no due date of the notification.


2 -  Relative Time : It is nothing but the constant time details .
you can set the value in days, hours and minutes combination.

3 - Item Attribute : You can create number type attribute and assign it here.

you can set the attribute value as below.


wf_engine.setitemattrtext (itemtype      => l_itemtype,
                                 itemkey       => l_itemkey,
                                 aname         => 'XXTIMEOUT',
                                 avalue        => 2880
                                );

2880 - it is  minutes.

if you want to set due date to 3 days then value should be =(24*60*3)= 4320.

How to send notification through pl/sql without creating a new workflow

You can use any existing workflow and send notification by using wf_notification.send

Just you need to set the attributes of workflow message that you want to send.


Below is the example:


Here we will use seeded CS_MSGS (Service Messages)  item type nothing but the message type.

You can any of the predefined seeded item type or any custom workflow item type.

you just need the internal name of item type that you can get as shown below.

log in to workflow builder , load the item type , right click on item type and get the internal name.

or you can use the below query to get item type and it's internal name.


select * from WF_ITEM_TYPES_vl
where name ='CS_MSGS';

Now there are 4 messages in this item type and you want to use Expanded FYI Message

right click and select properties to get internal name. EXPANDED_FYI_MSG.

Now you just need to set the attributes of the message.
But you don't know the message attributes, so right click on message and select body tab.

Now you have everything so it's time to write the code .




DECLARE

   v_notification_id        NUMBER                 := NULL;
   v_from_user_name    VARCHAR2 (500)    := 'XX_FROM_USER';
   l_to_user_name         VARCHAR2 (500)    := 'XX_TO_USER';
   v_subject_line           VARCHAR2 (500)    := 'Subject Line';
   v_message_line         VARCHAR2 (500)    := 'Message Line';

BEGIN

   v_notification_id :=
      wf_notification.send (UPPER (l_to_user_name),
                            'CS_MSGS',
                            'EXPANDED_FYI_MSG'
                           );

   wf_notification.setattrtext (v_notification_id,
                                '#FROM_ROLE',
                                v_from_user_name
                               );

   wf_notification.setattrtext (v_notification_id,
                                'OBJECT_TYPE',
                                v_subject_line
                               );

   wf_notification.setattrtext (v_notification_id, 'SENDER', v_from_user_name);

   wf_notification.setattrtext (v_notification_id,
                                'MESSAGE_TEXT',
                                v_message_line
                               );

   wf_notification.denormalize_notification (v_notification_id);

   COMMIT;

END;

===================================================================

wf_notification.denormalize_notification and COMMIT is mandatory at last.


==================================================================

This way you can send normal text as well as HTML notifications.

Just you need to identify proper item type and message name.

How to Purge old completed workflows

How to Purge old completed workflows 

After years few years of go-live ,

workflow transaction tables will become heavy and will lot of data.
It may cause performance issue for new workflow transactions.
So you can purge the old existing workflow transaction through below procedure.

You can loop through below procedure for all old workflow transactions.

Just you need to pass the item type and item key as parameters.


execute wf_purge.Total(itemtype =>'Test',itemkey => 23);

How to approve/reject or respond a notification through script

How to approve/reject or respond a notification through script

BEGIN
   wf_notification.setattrtext (nid         => '4754620',
                                aname       => 'RESULT',
                                avalue      => 'REJECTED'
                               );
   wf_notification.respond (nid         => '4754620',
                                     respond_comment =>  'Reject From backend',
                                     responder =>  'XXTEST');
   COMMIT;
END;


nid =  will be notification id

There are multiple ways to get notification id

-  Open the notification in the application through SYSADMIN or any other user
    in the notification below sent and to date, you can see the ID

or

-   you can search the notification in wf_notification table

aname = will be always RESULT

avalue = will be APPROVE,REJECTED or any other action .

Generally for avalue , perform the appropriate action on the notification through front end
and see the value of RESULT attribute in wf_notification_attributes table.

responder coment = any comment

responder => will be recipient role/user name