精华内容
下载资源
问答
  • WF_NOTIFICATIONS表中的MAIL_STATUS

    千次阅读 2014-09-28 14:02:44
     Troubleshooting Workflow Notification Mailer IssuesFind Workflow Notification Mailer is up and Running?...SELECT component_name, component_statusFROM fnd_svc_componentsWHERE component_type = 'WF_MA
    

    Troubleshooting Workflow Notification Mailer IssuesFind Workflow Notification Mailer is up and Running?
    SELECT component_name, component_statusFROM fnd_svc_componentsWHERE component_type = 'WF_MAILER';
    Workflow logs: FNDCPGSC*.txt under $APPLCSF/$APPLOG directoryFind the Failed Ones?
    Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';
    Check pending e-mail notification that was pending for process.
    Sql> SELECT COUNT(*), message_name FROM wf_notificationsWHERE STATUS='OPEN'AND mail_status = 'MAIL'GROUP BY message_name;
    Sql> SELECT * FROM wf_notificationsWHERE STATUS='OPEN'AND mail_status = 'SENT'ORDER BY begin_date DESC
    Check the Workflow notification has been sent or not?
    select mail_status, status from wf_notifications where notification_id=<notification_id>
    --If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
    --If mail_status is SENT, its means mailer has sent email
    --If mail_status is Null & status is OPEN, its means that no need to send emailas notification preference of user is "Don't send email"
    --Notification preference of user can be set by user by logging in application +click on preference + the notification preference
    1. Verify whether the message is processed in WF_DEFERRED queue
    select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<nid>'<nid> - notification id
    2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue
    select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msgfrom wf_error wf where wf.user_data.event_key = '<nid'>To check what all mails have went and which all failed ?
    Select from_user,to_user,notification_id, status, mail_status, begin_datefrom WF_NOTIFICATIONS where status = 'OPEN';
    Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_ KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_datefrom WF_NOTIFICATIONS where status = 'OPEN';


    Users complain that notifications are stuck ?
    Use the following query to check to see whatever the users are saying is correct
    SQL> select message_type, count(1) from wf_notificationswhere status='OPEN' and mail_status='MAIL' group by message_type;
    E.g o/p of query -
    MESSAGE_Type COUNT(1)
    -------- ----------
    POAPPRV 11 --- 11 mails of Po Approval not sent --
    -
    INVTROAP 12
    REQAPPRV 9
    WFERROR 45 --- 45 mails have error
    If Mail not received by User ?
    select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUSfrom wf_users where DISPLAY_NAME=xxx,yyy;
    StatusActive
    Notification_preference-> Mailtext
    Email Address should not be null
    Notification not sent waiting to be mailed ?
    SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONSwhere status =OPENand mail_status =MAIL;
    To debug the notification id ?
    $FND_TOP/sqlrun wfmlrdbg.sql
    Change Mail Preferences for all Application Users (FND USERS)How to mass change Mail Preferences for FND USERS [ID 451929.1]
    Solution :Using sqlplus as the apps user:
    1st Method:
    1.update wf_local_rolesset notification_preference = 'MAILHTML'
    where user_flag='Y'
    2.update fnd_user_preferencesset preference_value = 'MAILHTML'where module_name = 'WF'and preference_name = 'MAILTYPE'
    commit;
    2nd method:
    Navigation: Connect to Applications as Sysadmin user and select responsibility
    Workflow Administrator Web Applications --> Administration --> Global Preferences

    Tablespace Usage/Free
    TEMP Usage per session ?
    SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_opsFROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process PWHERE T.session_addr = S.saddrAND S.paddr = P.addrAND T.tablespace = TBS.tablespace_nameGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.program, TBS.block_size, T.tablespaceORDER BY sid_serial;
    Temp Space used/free?
    select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;
    Users Using Temp tablespace?
    select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v _$session a, sys.v_$sort_usage b, sys.v_$parameter pwhere p.name=b.session_addr;
    To get which user is using temp tablespace ?
    select username,tablespace,extents from v$sort_usage;
    Free and Used Space in Temp Tablespace ?
    SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

    展开全文
  • EBS Workfow常用表

    2020-09-19 19:32:36
    1.Workflow Definition Tables WF_ITEM_TYPES WF_ACTIVITIES WF_MESSAGES WF_ITEM_ATTRIBUTES WF_ACTIVITY_ATTRIBUTES WF_MESSAGE_ATTRIBUTES WF_PROCESS_ACTIVITIES WF_ACTIVITY_TRANSITIONS ...WF_NOTIFICATIONS

    1.Workflow Definition Tables

    WF_ITEM_TYPES

    WF_ACTIVITIES

    WF_MESSAGES

    WF_ITEM_ATTRIBUTES

    WF_ACTIVITY_ATTRIBUTES

    WF_MESSAGE_ATTRIBUTES

    WF_PROCESS_ACTIVITIES

    WF_ACTIVITY_TRANSITIONS

    1. Workflow Runtime Tables

    WF_ITEMS

    WF_ITEM_ACTIVITY_STATUSES

    WF_NOTIFICATIONS

    WF_MESSAGES

    WF_NOTIFICATION_ATTRIBUTES

    1. Business Event Tables

    WF_SYSTEMS

    WF_AGENTS

    WF_EVENTS

    WF_EVENT_SUBSCRIPTIONS

    1. 各表解析

    wf_item_types_vl,保存工作流的定义,即类

    wf_items,保存实际的工作流,或者说工作流的对象实例

    wf_item_attribute_values,保存工作流实例的attribute最新值

    wf_item_activity_statuses,保存工作流实例的各个activity的状态,比如完成否,返回值

    wf_notifications,保存工作流实例的notifications消息,基本是按顺序的,可以看发给谁了

    wf_roles,角色视图,工作流引用角色的依据,有mail地址等信息

    wf_user_roles,用户和角色关系视图,工作流可以根据它进行“群发”

    展开全文
  • 适用于:11.5到12 在workflow mailer没有启动之前,邮件队列中有很多等待发送的mail,一旦workflow ... FROM WF_NOTIFICATIONS WHERE STATUS = 'OPEN' AND MAIL_STATUS = 'MAIL'; 更改这些邮件状态为“已发送”

    适用于:11.5到12

    在workflow mailer没有启动之前,邮件队列中有很多等待发送的mail,一旦workflow mailer启动,这些邮件就会发送。如何阻止这些邮件发送?

    1. 查询哪些通知等待发送邮件
    SELECT NOTIFICATION_ID, STATUS, MAIL_STATUS, BEGIN_DATE
     FROM WF_NOTIFICATIONS
     WHERE STATUS = 'OPEN'
     AND MAIL_STATUS = 'MAIL';
     --外发Mail通常在如下Queue中:
    SELECT *
      FROM wf_notification_out wno
     WHERE wno.state = 0
       AND wno.corrid LIKE 'APPS%'
    
    
    1. 更改这些邮件状态为“已发送”
    UPDATE WF_NOTIFICATIONS
    SET MAIL_STATUS = 'SENT'
    WHERE MAIL_STATUS = 'MAIL';
    
    1. 清空队列中的数据(在应用节点运行)
      语法格式:
    sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr
    

    例如

    sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys
    
    1. 启动workflow mailer

    转自https://www.cnblogs.com/helloolduncle/archive/2011/11/14/2247757.html

    展开全文
  • 本文来源:... 问题描述 在做Oracle EBS工作流列表开发的时候,可以使用如下SQL获取系统工作流列表: --sql已做精简 SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS

    本文来源:http://blog.csdn.net/sunansheng/article/details/49620789

    问题描述

    在做Oracle EBS工作流列表开发的时候,可以使用如下SQL获取系统工作流列表:

    --sql已做精简
    SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
     wn.notification_id,
     wn.from_user,
     decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
     decode(wn.more_info_role,
            NULL,
            wn.subject,
            fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
     wn.language,
     wn.begin_date,
     wn.due_date,
     wn.status,
     wn.priority,
     'P' AS priority_f,
     wn.recipient_role,
     wn.end_date,
     wit.display_name AS TYPE,
     wn.more_info_role,
     wn.from_role,
     wn.message_type,
     wn.message_name,
     wn.mail_status,
     wn.original_recipient
      FROM wf_notifications wn,
           wf_item_types_tl wit,
           wf_lookups_tl    wl
     WHERE wn.status = 'OPEN'
       AND wn.message_type = wit.name
       AND wit.language = userenv('LANG')
       AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
       AND wn.status = wl.lookup_code
       AND wl.language = userenv('LANG')
    但是会发现有些行的From_user、TO_USER、SUBJECT等字段为空,如下图


    当我们在EBS界面上面打开了工作流列表,发现这些字段又有值了。


    解决方法:

    我们可以通过表wf_notification_attributes取出各个字段的值。

    修改后了SQL:

    SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
     wn.notification_id,
     nvl(wn.from_user, cux_wx_workflow_utl.get_from_user(wn.notification_id)) from_user,
     nvl(wn.to_user, cux_wx_workflow_utl.get_role_displayname(wn.recipient_role)) to_user,
     nvl(wn.subject, cux_wx_workflow_utl.get_subject(wn.notification_id, wn.message_type)) subject,
     wn.begin_date,
     wn.recipient_role,
     wit.display_name AS TYPE,
     nvl(wn.from_role, cux_wx_workflow_utl.get_from_role(wn.notification_id)) from_role,
     cux_wx_workflow_utl.get_document_id(wn.notification_id) document_id
      FROM apps.wf_notifications wn,
           apps.wf_item_types_tl wit,
           apps.wf_lookups_tl    wl
     WHERE wn.status = 'OPEN'
       AND wn.message_type = wit.name
       AND wit.language = 'ZHS'
       AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
       AND wn.status = wl.lookup_code
       AND wl.language = userenv('LANG')
       AND wn.message_type IN ('CUXFINPR', 'CUXXPMWF')
       AND wn.message_name IN ('CUX_PRJ_REQ_APPROVE', 'CUXMSG_REQ_APPROVAL');
    cux_wx_workflow_utl包关键代码:

    FUNCTION get_from_user(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
        l_username VARCHAR2(250);
      BEGIN
         SELECT wf_directory.getroledisplayname(wna.text_value)
           INTO l_username
           FROM wf_notification_attributes wna
          WHERE wna.notification_id = p_notification_id
            AND wna.name = '#FROM_ROLE';
        RETURN l_username;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN NULL;
      END get_from_user;
      
      FUNCTION get_from_role(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
        l_username VARCHAR2(250);
      BEGIN
         SELECT (wna.text_value)
           INTO l_username
           FROM wf_notification_attributes wna
          WHERE wna.notification_id = p_notification_id
            AND wna.name = '#FROM_ROLE';
        RETURN l_username;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN NULL;
      END get_from_role;
      
      FUNCTION get_role_displayname(p_rolename IN VARCHAR2) RETURN VARCHAR2 IS
        l_username VARCHAR2(550);
      BEGIN
        l_username :=  wf_directory.getroledisplayname(p_rolename);
        RETURN l_username;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN NULL;
      END get_role_displayname;
      
      FUNCTION get_subject(p_notification_id IN NUMBER,p_message_type IN VARCHAR2) RETURN VARCHAR2 IS
        l_subject VARCHAR2(2050);
      BEGIN
        SELECT wna.text_value
           INTO l_subject
           FROM wf_notification_attributes wna
          WHERE wna.notification_id = p_notification_id
            AND wna.name = 'DOCUMENT_NUMBER';
            
        IF p_message_type = 'CUXFINPR' THEN
          RETURN '付款申请 '|| l_subject ||' 请求审批';
        ELSIF p_message_type = 'CUXXPMWF' THEN
          RETURN '项目 '|| l_subject ||' 请求审批';
        ELSE
          RETURN l_subject ||' 请求审批';
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN NULL;
      END get_subject;
      
      FUNCTION get_document_id(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
        l_subject VARCHAR2(2050);
      BEGIN
        SELECT SUBSTR(wna.text_value,INSTR(wna.text_value,'=',1)+1)
           INTO l_subject
           FROM wf_notification_attributes wna
          WHERE wna.notification_id = p_notification_id
            AND wna.name = 'OPEN_FORM_COMMAND';
            
        RETURN l_subject;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN NULL;
      END get_document_id;


    其它

    完整的worklist的SQL语句如下,当然如果不考虑more_info_role,可以使用上面简化的SQL
    SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
     wn.notification_id,
     wn.from_user,
     decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
     decode(wn.more_info_role,
            NULL,
            wn.subject,
            fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
     wn.language,
     wn.begin_date,
     wn.due_date,
     wn.status,
     wn.priority,
     'P' AS priority_f,
     wn.recipient_role,
     wn.end_date,
     wit.display_name AS TYPE,
     wn.more_info_role,
     wn.from_role,
     wn.message_type,
     wn.message_name,
     wn.mail_status,
     wn.original_recipient
      FROM wf_notifications wn,
           wf_item_types_tl wit,
           wf_lookups_tl    wl
     WHERE wn.status = 'OPEN'
       AND wn.message_type = wit.name
       AND wit.language = userenv('LANG')
       AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
       AND wn.status = wl.lookup_code
       AND wl.language = userenv('LANG')
       AND wn.recipient_role IN (SELECT wur.role_name
                                   FROM wf_user_roles wur
                                  WHERE wur.user_name = :1
                                    AND wur.user_orig_system = :2
                                    AND wur.user_orig_system_id = :3)
       AND more_info_role IS NULL
    UNION ALL
    SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N6)*/
     wn.notification_id,
     wn.from_user,
     decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
     decode(wn.more_info_role,
            NULL,
            wn.subject,
            fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
     wn.language,
     wn.begin_date,
     wn.due_date,
     wn.status,
     wn.priority,
     'P' AS priority_f,
     wn.recipient_role,
     wn.end_date,
     wit.display_name AS TYPE,
     wn.more_info_role,
     wn.from_role,
     wn.message_type,
     wn.message_name,
     wn.mail_status,
     wn.original_recipient
      FROM wf_notifications wn,
           wf_item_types_tl wit,
           wf_lookups_tl    wl
     WHERE wn.status = 'OPEN'
       AND wn.message_type = wit.name
       AND wit.language = userenv('LANG')
       AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
       AND wn.status = wl.lookup_code
       AND wl.language = userenv('LANG')
       AND wn.more_info_role IN (SELECT wur.role_name
                                   FROM wf_user_roles wur
                                  WHERE wur.user_name = :4
                                    AND wur.user_orig_system = :5
                                    AND wur.user_orig_system_id = :6)
    

    展开全文
  • Notification Mailer停用一段时间后,总会积累一些待发送邮件,因此如果有需要重启mailer,需要清理过期的邮件。步骤如下:更新WF_NOTIFICATIONS 中的STATUS为CLOSED,MAIL_STAT...
  • 2. Purge the wf_notification_out queue and recreate it out of the wf_notifications table by executing: cd $FND_TOP/patch/115/sql sqlplus APPS/ @wfntfqup.sql APPS APPLSYS 3. Start the ...
  • 这个EBS发送邮件的问题,断断续续弄了两个月,一直未能得到解决。 说一下环境。 EBS r12.1.3 + Microsoft exchange2013的smtp...select * from apps.wf_notifications order by begin_date desc; --这里我们留
  • 摘自:...Some times Due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,T
  • 适用于:11.5到12在workflow mailer没有启动之前,邮件队列中有很多等待发送的mail,一旦workflow mailer启动,...1 SELECT NOTIFICATION_ID, STATUS, MAIL_STATUS, BEGIN_DATE2 FROM WF_NOTIFICATIONS3 WHERE ST...
  • Oracle Workflow Queries

    千次阅读 2012-11-09 17:43:13
    Workflow Definition--Workflow Item Type的定义信息select * from wf_item_types where name = 'TPLEAVE'select * from wf_item_types_tl where name = 'TPLEAVE'--工作流里各个节点信息select * from wf_...
  • Oracle Workflow Tables

    千次阅读 2012-11-09 17:41:07
    Workflow Definition TablesWF_ITEM_TYPESThe WF_ITEM_TYPES table defines an item that is transitioning through a workflow process.WF_ACTIVITIESWF_ACTIVITIES table stores the definition of an activity.Ac
  • 作业start end,状态改变都会创建一个通知,同伙URL 可...oozie.wf.workflow.notification.proxy可用来配置为通知为http,或者socket proxy  格式proxyHostname:port or proxyType@proxyHostname:port  默认使
  • 工作流表结构

    2011-04-25 15:12:06
    --工作流的定义表--ITEMTYPEselect * from wf_item_types wit where wit.name = 'CUXTEST'; --wf_item_types_vl--ATTRIBUTESsel...
  • PO核准通知界面修改

    2014-08-09 23:14:00
    想在notification頁面把供應商的稅捐代碼帶出來,添在如下紅框中 PO_WF_PO_NOTIFICATION head information:get_po_approve_msg line information:get_po_lines_...CREATE OR REPLACE PACKAGE BODY PO_WF_PO_N...
  • Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED'; Check pending e-mail notification that was ...
  • SELECT * FROM wf_item_types t WHEREt.name= 'CUX_TEST';   /*查询指定ITEM_TYPE中新建的activitie定义(没有activity的id)。*/ SELECT *  FROM wf_activities_tlt  WHERE t.item_type= 'CUX_0005'  AN
  • FROM apps.wf_notifications WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN' GROUP BY message_type; ==================================================================== ...
  • WF-mails not being sent from workflow mailer

    千次阅读 2014-09-16 15:01:50
    test mailer I sent has status OPEN in the wf_notifications table. Does it mean that the mail is not delivered ? Please see (Tracking Workflow Notification Event Messages [ID 433359.1]). ...
  • WF--Notification查看和回应审批通知

    千次阅读 2014-09-12 12:04:16
    查看和回应审批通知 您可以使用“通知明细”Web 页(可通过 Oracle Purchasing 中的“通知汇总”菜单访问)来管理待批队列和执行审批活动。如果尚未通过“审批单据”窗口将单据提交审批,您也可以在打开通知之后,...
  • PO Tables

    2015-05-28 09:24:46
    PO Tables PO -Purchasing EDW_PO_VENDOR_HIERARCHIES FND_FUNCTION_PARAMETER_MAP FND_NOTIFICATIONS POA_BIS_SAVINGS POA_BIS_SAVINGS_EFC POA_BIS_SAVINGS_RPT POA_BIS_SAVINGS_RPT_EFC POA_BIS_SUPPLIE
  • EBS中经常会出现PO提交审批后状态为“处理中”的情况,此时PO创建人无法打开,审批人也无法打开,工作流等查看也无异常,可以使用一下SQL处理再进行审批... CURSOR POTORESET IS SELECT WF_ITEM_TYPE, WF_ITEM_KEY, ...

空空如也

空空如也

1 2 3 4 5 ... 15
收藏数 296
精华内容 118
关键字:

wf_notifications