Established Member.. manaschandra
Established Member..
261 views

SQL script to fetch the notification information set on workflow step

Hi All,


Anybody have script to fetch the notification information set on workflow step?


Thanks in advance.


Regards

0 Likes
2 Replies
Established Member.. manaschandra
Established Member..

Re: SQL script to fetch the notification information set on workflow step

Thanks all.

 

0 Likes
Established Member.. Utkarsh_Mishra
Established Member..

Re: SQL script to fetch the notification information set on workflow step

SQL.

 

 

select kw.workflow_name,
       kws.sort_order "Step#",
       kws.step_name,
       kn.visible_event_value "Event",
       kn.description,
       kni.interval_name "Interval",
       kn.enabled_flag "Enabled",
       knr.email_address,
       knr.distribution_type_code,
       knr.token "Recipient Token",
       knr.recipient_type_code,
       knr.notification_recipient_id,
       knr.user_id
       ku.username "Recipient Username",
       ku.full_name "Recipient Fullname",
       knr.security_group_id "Recipient SG_ID",
       ksg.security_group_name "Recipient SG Name",
       kn.subject,
       dbms_lob.substr(kn.parent_text, 4000, 1)  "Message"
from KWFL_WORKFLOWS kw,
     KWFL_WORKFLOW_STEPS kws,
     KNTA_NOTIFICATIONS kn,
     KNTA_NOTIFICATION_RECIPIENTS knr,
     KNTA_NOTIFICATION_INTERVALS kni,
     KNTA_USERS ku,
     KNTA_SECURITY_GROUPS ksg
where kws.workflow_id = kw.workflow_id
  and kws.workflow_step_id = kn.condition_value
  and kn.notification_id = knr.notification_id
  and kn.interval_id = kni.interval_id
  and kn.enabled_flag='Y'
  and knr.token is not NULL
  and knr.user_id  = ku.user_id (+)
  and knr.security_group_id = ksg.security_group_id (+)

  and kw.enabled_flag = 'Y'  -- for Enabled Workflows

  and kw.workflow_name = 'My Workflow' -- workflow name

  and knr.distribution_type_code = 'TO'  -- if want to search bu distribution type cc,bcc,to

  and knr.recipient_type_code = 'USER'  -- receipent type USER  USER_ID  SECURITY_GROUP  EMAIL_ADDRESS
    
  and ku.username = 'utkarsh'  -- if want to search by user

  and knr.recipient_type_code = 'EMAIL_ADDRESS'
  and knr.email_address like 'utkarsh.mishra@abc.com%'
  
order by kw.workflow_name, knr.distribution_type_code

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.