HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END

HOW TO SEND A MAIL FROM UTL_SMTP OR FROM BACK END :



create or replace FUNCTION xxx_popmd_upd_email_notif (p_po_number         VARCHAR2,
                                                      p_po_line           NUMBER,
                                                      p_po_shipment_num   NUMBER,
                                                      p_po_release_num    NUMBER,
                                                      p_po_promissed_date VARCHAR2,
                                                      p_justification     VARCHAR2)
   RETURN VARCHAR2
AS

   v_mail_conn       utl_smtp.connection;
   v_mail_host       VARCHAR2 (30);
   v_port_no         NUMBER;
   v_to_recipients   apps.alr_distribution_lists.to_recipients%TYPE;
   v_cc_recipients   apps.alr_distribution_lists.cc_recipients%TYPE;
   v_mail_subject    VARCHAR2 (3000);
   v_from            VARCHAR2 (80) := 'OracleMailerTest@xxx.com';
   crlf              VARCHAR2 (2) := CHR (13) ||CHR (10);
   v_employee_name   apps.fnd_user.user_name%TYPE;
   v_cp_date         VARCHAR2 (30);
   v_user_id         NUMBER := fnd_profile.value ('USER_ID');
   v_req_id          NUMBER := fnd_global.conc_request_id;
   l_cc              VARCHAR2 (3000);
   v_cc              VARCHAR2 (3000);
   l_to              VARCHAR2 (2000);
   l_tmp             VARCHAR2 (3000);
   n                 NUMBER;
BEGIN
   SELECT to_recipients,
          cc_recipients
     INTO v_to_recipients,
          v_cc_recipients
     FROM apps.alr_distribution_lists
    WHERE name = 'ORIGINAL_PROMISE_DATE_UPDATE';

   dbms_output.put_line ('Email Distribution List: ' || v_to_recipients);

   SELECT DISTINCT NVL (pf.full_name, fu.user_name)
     INTO v_employee_name
     FROM per_all_people_f pf,
          fnd_user fu
    WHERE fu.employee_id = pf.person_id(+)
      AND user_id = v_user_id;

   dbms_output.put_line ('Update by User Name: ' || v_employee_name);

    SELECT NVL (TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS'), sysdate)
      INTO v_cp_date
      FROM fnd_concurrent_requests fcr
     WHERE request_id = v_req_id;

   dbms_output.put_line ('Update by date: ' || v_cp_date);

   SELECT SUBSTR (v.profile_option_value, 1, instr (v.profile_option_value, ':') - 1),
          to_number (SUBSTR (v.profile_option_value, 20, 2))
     INTO v_mail_host,
          v_port_no
     FROM fnd_profile_option_values v,
          fnd_profile_options o
    WHERE v.profile_option_id = o.profile_option_id
      AND o.profile_option_name = 'XX_CUS_TOP_EMAIL_SMTP';

   l_cc := v_to_recipients;

   LOOP
      EXIT WHEN l_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (l_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (l_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := l_cc;
         l_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || l_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || 'PO Number: '                       || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      l_cc := SUBSTR (l_cc, n + 1);

      dbms_output.put_line ('Output3: ' || l_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   v_cc:=v_cc_recipients;
    LOOP
      EXIT WHEN v_cc IS NULL;

      v_mail_subject := 'Original Promise Date Update - ' || p_po_number || ' by ' || v_employee_name;
      v_mail_conn := utl_smtp.open_connection (v_mail_host, v_port_no);

      utl_smtp.helo (v_mail_conn, v_mail_host);
      utl_smtp.mail (v_mail_conn, v_from);

      n := instr (v_cc, ',');

      --IF n =0 THEN exit; end if;
      l_tmp := SUBSTR (v_cc, 1, n - 1);

      IF n = 0 THEN
         l_tmp := v_cc;
         v_cc := NULL;
      END IF;

      dbms_output.put_line ('Output1: ' || l_tmp);
      dbms_output.put_line ('Output2: ' || v_cc);

      --utl_smtp.rcpt( l_tmp );
      utl_smtp.rcpt (v_mail_conn, l_tmp);
      utl_smtp.data (v_mail_conn,  'Date: '                            || TO_CHAR (sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                        || crlf || 'From: '                            || v_from
                        || crlf || 'Subject: '                         || v_mail_subject
                        || crlf || 'To: '                              || v_to_recipients
                        || crlf || 'Cc: '                              || v_cc_recipients
                        || crlf || crlf || 'PO Number:  '              || p_po_number
                        || crlf || 'Line Number: '                     || p_po_line
                        || crlf || 'Shipment Number: '                 || p_po_shipment_num
                        || crlf || 'Release Number: '                  || p_po_release_num
                        || crlf || 'New Original Promised Date: '      || p_po_promissed_date
                        || crlf || 'Justification: '                   || p_justification|| crlf
                        || crlf || 'Updated By    :' ||v_employee_name
                        || crlf || 'Date Updated  :' ||v_cp_date|| crlf);

      v_cc := SUBSTR (v_cc, n + 1);

      dbms_output.put_line ('Output3: ' || v_cc);

      utl_smtp.quit (v_mail_conn);
   END LOOP;

   RETURN ('Notification Sent');
EXCEPTION
   WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
      raise_application_error ( - 20000, 'Unable to Send Mail', true);
   WHEN OTHERS THEN
      raise_application_error ( - 20001, 'Other Errors: '||sqlerrm||';'||SQLCODE);
END xxx_popmd_upd_email_notif;

passing multiple values for a single parameter in oracle apps Reports

passing multiple values for a single parameter in oracle apps Reports:

Here Comma delimited Multiple values parameter considering as a single sting .

Example : If passing parameter value as 'ABC,ABCD' This entire sting consider as a single string but i want to use this as a comma delimited like in ('ABC','ABCD')

By using below Example it could be Rectified.


 SELECT * FROM XX_TABLE WHERE XX_COLUMN in (SELECT REGEXP_SUBSTR (value,'[^, ]+',1,LEVEL) value
        FROM (SELECT 'ABC,ABCD' value FROM dual)
                  connect BY REGEXP_SUBSTR (value,'[^, ]+',1,LEVEL) IS NOT NULL) --ABC,ABCD

In XML Report Date format zero's are missing (How to get zero values in XML Report)

By using this Below tag in RTF Template we will disply the zero starting and ending dates and number valued tags.


Here Tag is Creation_date.

Normally if the value for this tag is 03-JAN-2010,when you run the XML report by using Excel output the value will come like 3-jan-10.

by using below one overriding tag we will get expected output like 03-JAN-2010.

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CREATION_DATE?></fo:bidi-override>

How to attach RTF Layout to the Concurrent Program From Back End:

Using submit_request will only submits the program and will not attach any layout or print option.
Code below will help to set XML publisher template/layout along with print option.

NOTE: Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

Layout is submitted to a concurrent request using below procedure

fnd_request.add_layout (
                    template_appl_name   => 'Template Application',
                    template_code        => 'Template Code',
                    template_language    => 'en', --Use language from template definition
                    template_territory   => 'US', --Use territory from template definition
                    output_format        => 'PDF' --Use output format from template definition
                     );


Setting printer while submitting concurrent program

fnd_submit.set_print_options (printer      => lc_printer_name
                                   ,style        => 'PDF Publisher'
                                   ,copies       => 1
                                   );

fnd_request.add_printer (
                    printer => printer_name,
                    copies  => 1);



DECLARE
   lc_boolean        BOOLEAN;
   ln_request_id     NUMBER;
   lc_printer_name   VARCHAR2 (100);
   lc_boolean1       BOOLEAN;
   lc_boolean2       BOOLEAN;
BEGIN

      -- Initialize Apps
      fnd_global.apps_initialize (>USER_ID<
                                 ,>RESP_ID<
                                 ,>RESP_APPL_ID<
                                 );
   -- Set printer options
   lc_boolean :=
      fnd_submit.set_print_options (printer      => lc_printer_name
                                   ,style        => 'PDF Publisher'
                                   ,copies       => 1
                                   );
   --Add printer
   lc_boolean1 :=
                fnd_request.add_printer (printer      => lc_printer_name
                                         ,copies       => 1);
  --Set Layout
  lc_boolean2 :=
               fnd_request.add_layout (
                            template_appl_name   => 'Template Application',
                            template_code        => 'Template Code',
                            template_language    => 'en', --Use language from template definition
                            template_territory   => 'US', --Use territory from template definition
                            output_format        => 'PDF' --Use output format from template definition
                                    );
   ln_request_id :=
      fnd_request.submit_request ('FND',                -- application
                                  'COCN_PGM_SHORT_NAME',-- program short name
                                  '',                   -- description
                                  '',                   -- start time
                                  FALSE,                -- sub request
                                  'Argument1',          -- argument1
                                  'Argument2',          -- argument2
                                  'N',                  -- argument3
                                  NULL,                 -- argument4
                                  NULL,                 -- argument5
                                  'Argument6',          -- argument6
                                  CHR (0)               -- represents end of arguments
                                 );
   COMMIT;

   IF ln_request_id = 0
   THEN
      dbms.output.put_line ('Concurrent request failed to submit');
   END IF;
END;

Coma Delimiter sub string Program example for Reports

DECLARE
L_STRING VARCHAR2(400):='1,12,123,1234,12345,0,12';
begin
IF L_STRING IS NOT NULL THEN

SELECT LENGTH(L_STRING) into l_length FROM DUAL;

select substr(L_STRING,2,l_length-2) into L_STRING from dual;
srw.message(4,'P_SOURCE_LIN :'||L_STRING);

select (length(L_STRING) - length(replace(L_STRING, ',')))  INTO L_COUNT from dual;
srw.message(5,'l_count :'||l_count);
L_COUNT:=L_COUNT+1;
srw.message(6,'l_count :'||l_count);
FOR i IN 1..L_COUNT
loop

srw.message(8,'i  :'||i);
l_source:=null;
l_f:=0;
l_last:=0;
l_fir:=0;
l_sec:=0;
l_ccc:=0;
l_order_number:=0;
l_line_number:=0;

SELECT INSTR(L_STRING,',', 1,i) into l_f FROM DUAL;
srw.message(9,'l_f  :'||l_f);
if i=1 and l_f =0 then
select L_STRING into l_source FROM DUAL;
elsif i=1 then
SELECT substr(L_STRING, 1,l_f-1) into l_source FROM DUAL;
srw.message(10,'l_source  :'||l_source);

elsif i=L_COUNT and i<>1 then
SELECT INSTR(L_STRING, ',', 1,i-1) into l_last FROM DUAL;
select substr(L_STRING,l_last+1) into l_source FROM DUAL;
else
SELECT INSTR(L_STRING, ',', 1,i-1) into l_fir FROM DUAL;
SELECT INSTR(L_STRING, ',',1, i) into l_sec FROM DUAL;

SELECT substr(L_STRING, l_fir+1,l_sec-l_fir-1) into l_source FROM DUAL;
end if;
srw.message(7,'l_chr :'||l_source);


srw.message(12,'l_ccc :'||l_ccc);
end if;

end loop;

END IF;
END;

Concurrent Request Status Codes and Meaning Of Status Code's

Concurrent Request Status Codes and Meaning Of Status Code's:

Use below query to cancel all scheduled concurrent programs.

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

UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code IN ('Q','I')

AND requested_start_date > SYSDATE

AND hold_flag = 'N';

COMMIT;

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

TO cancel all running concurrent programs.

UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'X'

WHERE status_code IN ('R','I');

Commit;

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

Concurrent Request Phase Codes:-

SELECT LOOKUP_CODE, MEANING

  FROM FND_LOOKUP_VALUES

 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND LANGUAGE = 'US'

       AND ENABLED_FLAG = 'Y';

To Cancel Specific request:

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

update fnd_concurrent_requests

set status_code='D', phase_code='C'

where request_id=:request_id;

LOOKUP_CODE MEANING

C             Completed

I               Inactive

P             Pending

R             Running

Concurrent Request Status Codes:-

SELECT LOOKUP_CODE, MEANING

  FROM FND_LOOKUP_VALUES

 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND LANGUAGE = 'US'

       AND ENABLED_FLAG = 'Y';

LOOKUP_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

Normally a concurrent request proceeds through three, possibly four, life cycle stages or phases,

Phase Code	Meaning with Description
Pending	Request is waiting to be run
Running	Request is running
Completed	Request has finished
Inactive	Request cannot be run

                                               

                               

Within each phase, a request's condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

The status and the description of each meaning given below:

PENDING	Normal	Request is waiting for the next available manager.
	Standby	Program to run request is incompatible with other program(s) currently running.
	Scheduled	Request is scheduled to start at a future time or date.
	Waiting	A child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.
RUNNING	Normal	Request is running normally.
	Paused	Parent request pauses for all its child requests to complete. For example, a report set pauses for all reports in the set to complete.
	Resuming	All requests submitted by the same parent request have completed running. The Parent request is waiting to be restarted.
	Terminating	Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.
COMPLETED	Normal	Request completes normally.
	Error	Request failed to complete successfully.
	Warning	Request completes with warnings. For example, a report is generated successfully but fails to print.
	Cancelled	Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
	Terminated	Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.
INACTIVE	Disabled	Program to run request is not enabled. Contact your system administrator.
	On Hold	Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
	No Manager	No manager is defined to run the request. Check with your system administrator.

Steps to Create Oracle workflow:

Access level:

The level of access can be set in Workflow Builder and developers need to set access levels as defined in the following list:

·         0-9 is reserved for Oracle Workflow
·         10-19 is reserved for Oracle Application Object Library
·         20-99 is reserved for Oracle E-Business Suite
·         100-999 is reserved for customer specific extensions
·         1000 is reserved for public

An object will be locked to users that have a higher protection level than the object. You can see this as there is a padlock against objects that you do not have the access level to modify. The access level defaults to 100 and as an e-Business Suite developer, we always operate with an access level of 100. The access level can be modified by navigating to Help | About Oracle Workflow Builder as shown in the following screenshot:

 

1.       Open WFSTD and save as new workflow

Navigation: File >> Open

Click Browse then navigate to Workflow installation directory

Navigation: Workflow Installation Directory\ WF\DATA\US\WFSTD

               

Now Click File >Save as, Enter “XX First Workflow” and click OK

Right click on WFSTD and select New Item type

Enter the fields as below for Creating Item Type

Internal Name: XXFSTWF

Display name= XX First Workflow

 

Creating Process

Internal Name:  XXFSTPCS

Display name= XX First Process

 

Expand the node to see attributes, processes, notifications, functions, Events, Messages and lookup types as shown below.

Right click on Process and click on New Process

 

Enter the fields:

Internal Name: XXFSTPCS

Display Name: XX First Process

We need to create a new Runnable Process so that we can call it from PLSQL/Form/OAF/Java. Under a Process there can be any number of sub process depending upon the complexity of the workflow logic that needs to be implemented.

 

 

To Design any Process in Workflow it should start with One start Function and we get it from WFSTD. Just we need to drag and drop the start and end functions from the Standard Functions

Drag and drop the Start and END Functions from the standard Workflow.

 

 

After Drap and Drop Right click Start Function and click on Node Tab and change the value Start/End to Start.

Similarly change the properties of the end function

Note: Any Process can have only one Start but can have any number of ends depending upon the Business logic’s complexity.

Now we need to create a Custom Function that has the business logic. You can have the Function call your plsql function or Java Class or any external application. We can mention the PLSQL Function Name in the Function Name field in the properties of the Function.

 

Once the function is created just drag and drop the function between START and END Functions. Once the Function is placed just by using right click of the mouse draw the Flow how the process to be. Always The Process would be starting from Start and No Functions can be in the Process without coupling to another in the flow. Always the flow should end using and END function

 

Now Verify the workflow and save in the data base or the (desktop and upload using wfload.)

Once the workflow is saved in the data base which usually takes time we can test it using below queries:

SELECT * FROM wf_item_types WHERE name = 'XXFSTWF';

SELECT * FROM WF_PROCESS_ACTIVITIES WHERE process_item_type = 'XXFSTWF';

The data in WF_PROCESS_ACTIVITIES table explains the complete details of the workflow item type. Like how many Process, Functions, etc. with its versions.

Table Script

Package Script

Sample Script to Trigger the workflow:

We can see the Activity status of the workflow by using below query by passing item_key

select * from wf_item_activity_statuses where item_key like 'XXFST-321';

 

If any error in the workflow we can rewind the workflow from Applications or from Database (By using API)

Sample Script to Revision the workflow:

 

 Steps to Create Workflow with Approval Notification:

Open the existing workflow which is created above from the database or by using WFLOAD

Save the file on the desktop

Once the workflow file is saved on the desktop, Now we will be modifying that wft.

The Agenda is to send an email notification to one person using Oracle workflow.

For this we are going to create an Item Attribute which is similar to a Global Variable in a PLSQL Package. There are different types of item attributes like Text, number, date, lookup, form, url, document, role, attribute and event. Here we are selecting a text. We can even go for a Role type but then we need to create a role and assign it then to the attribute.

 

Attributes: Attributes acts like a global variable that can be referenced or updated by any activity with in a process.

 

Set the item attribute properties

 

Once the attribute is created then create a new message which needs to be send in the notification.

Set the properties of the new message:

 

 

In the Tab Body the actual email notification Body and in the subject the email notification subject goes in.

As we are using standard Approve/Reject notification we are selecting an existing lookup. Else if we want to create a new, we can always create a new look up with its values and use it here.

Add the display name and description which are mandatory for the Message

 

Create a new notification by using right click on the notifications menu.

Set the Notification properties

Select the Message which we have created.

Drag the item attribute to the message as we are using the item attribute in the message

 

Now edit the Main Process  to add the new Notification:

Form a link between Function and notification:

Drag the Notification which we created into the process and link the process to notification.

 

Edit the properties of the Notification to add the performer.

In the node change the Performer type to Item attribute as we intent to set the attribute value from function and link the process to end.

Validate and save the workflow in the database or on the desktop and upload it using WFLOAD.

 

If the Notification Mailer is up we will receive the notification via email to the role which we assigned to the attribute or we can check the notification in the application.

 

 

Role in Oracle Workflow
In Oracle workflow we need a role for notification activity. Workflow engine calls the notification system to deliver a notification message to an appropriate role. Once user of that role completes the notification response, the workflow Engine continues to drive through the remaining activities in process

We can create adhoc roles manually and can add any number of users to the roles.

Sample Scripts

                                 

Package script with setting the Attribute value

Viewing Workflow status diagram in Applications

We can view the process flow of our workflow in application level by following below steps

Login To Applications

Go to the Navigation SystemAdministrator - > WorkFlow -> Administrator WorkFlow -> Status Monitor

 

Below Form will open

 

Give Workflow Internal Name and Item Key which processed and click on GO

 

 

Click on Status Diagram Tab to view the process flow of the Workflow.

 

Firing Notifications Based on the Action

Create 2 messages XXFST_APPROVE AND XXFST_REJECT and 2 notifications XXFST_APP_NOTIF(Attach  XXFST_APPROVE  Message for this )and XXFST_REJ_NOTIF(Attach  XXFST_REJECT Message for this ) .

 

 

Edit the process and delete the selection from Notification to END in the process.

 

Edit the Properties of the notification and change the result type to Approval.

 

 

Now try to create the link between Notification and END it will prompt you to proceed with some action.

 

Drag the Notification XXFST_APP_NOTIF  in the process and select the action Approve and direct  the process to the notification XXFST_APP_NOTIF and from approval notification to END .

Drag the Notification XXFST_REJ_NOTIF in the process and select the action Reject and direct the process to the notification XXFST_REJ_NOTIF and from approval notification to END.

Change the performer in the node tab for the notifications XXFST_REJ_NOTIF and XXFST_APP_NOTIF which we created.

 

 

Save the Workflow in the Database and trigger it by using triggering script.

After triggering the workflow we can see the Notification in applications home page

 

Open the Message and click on Approve button.

 

Once approving the Notification we will receive the Approve Message

 

 

 

Process flow diagram for Approval Process

 

Similarly we can test the Reject functionality.

Trigger the Workflow open the message and click on reject Button.

 

 

Once Rejecting the Notification we will receive the Reject Message

 

Process flow diagram for Rejection Process

 

Loop Counters

Loop Counter activity used to limit the number of times the Workflow Engine transitions through a particular path in a process. The Loop Counter activity can have a result of Loop or Exit.

Using Loop Counters in Notifications

Scenario : If the Notification is rejected 3 times then it should come out of the loop and trigger the new notification.

Create new message XXFST_MAX_REJC .

 

Create new Notification XXFST_MAX_REJC_NOF and attach the message which we created

 

Open the process and create new function Loop counter by right clicking.

 

Set the Loop counter value in the tab node Attributes Tab

 

 

Drag the Notification XXFST_MAX_REJC_NOF into the process and give the performer in the node tab.

Implement the Loop counter in Reject process as shown in the screen shot below

The above process states that if the Rejection happens then it will loop for 3 times and fire the same notification 3 times. Once it reaches the Maximum Limit it will exit and send the new Notification which we created.

After triggering the workflow we can see the Notification in applications home page

Open the notification and Reject it will fire the Notification Again.

 

Open the notification and Reject it twice, after reaching the maximum point of rejections it will fire the new Notification. In the below screen shot we can see that we rejected the Notification 3 times

 

 

 

Process Flow Diagram

 

TIME OUT IN NOTIFICATIONS

In this section we will see setting the TIME OUT option in Notification. The Main purpose of using this functionality is to deviate the process if no action is taken with in the specified time limit.

Create a Message XXFST_TIMEOUT and Notification XXFST_TIMEOUT_NOTIF and attach the new message.

 

Open the process and edit the properties of the Notification XX First Notification and click on Node Tab and set the time to 10 minutes as mentioned in the below screen shot

 

Drag the Notification XXFST_TIMEOUT_NOTIF into the process and Delete selection from the Notification XX First Notification.

 

Try to connect the selection from the Notification XX First Notification we will get Timeout Action along with Approve and Reject.

 

Now create the selection for APPROVE, REJECT and TIME OUT as mentioned in the below screen shot.

 

Save the Workflow in the Database and trigger it by using triggering script.

As we set the time to 10 mins, if no action is taken on this Notification the Timeout Notification should trigger.

As we are running this in Development Instance we have to submit Workflow Background Process Manually to trigger the Timeout Notification, in PRODUCTION Environment this program will be scheduled.

 

 

After completing this program we will receive the Timeout Notification.

LOOKUP TYPES:

Lookup Types are used to define our own Values in the lookup to use.

Create new Lookup Type XXFST_LOOKUP_TYPE.

Create Lookup Codes YES and NO  for the Lookup Type XXFST_LOOKUP_TYPE.

 

 

Creating selections based on the values in Lookup

Open the Process and edit the properties of the function XX First Function and attach the Lookup Type which we created in the Result Type of the Function.

 

 

Delete Selection from the function and create New Selection.

 

 

We have to return the values same as the lookup values from the Function INSERT_PROC in the package XXFST_PKG.

Modified Package Script

Process Flow Diagram