Project and Portfolio Management Practitioners Forum
cancel

SQL script to fetch the notification information set on workflow step

manaschandra
Trusted Contributor.

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

2 REPLIES
Highlighted
manaschandra
Trusted Contributor.

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

Thanks all.

 

Utkarsh_Mishra
Acclaimed Contributor.

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)