Workflow Notifications Status 'FAILED' checking status

1. Check workflow mailer service current status

  sqlplus apps/<apps password>
  select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

 
  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM
 
  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a 
count of data is at best only a snapshot of a particular moment in time.

1. Here is a more robust script for monitoring the wf_notifications table:
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS
messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'

2. Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.

select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;
Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql

Orders created from the Spares Management Queries for alerts

Orders created from the Spares Management Queries

SELECT B.requirement_header_id,a.ORDER_LINE_ID,  a.ORDER_BY_DATE, a.* FROM CSP_REQUIREMENT_LINES A , CSP_REQUIREMENT_HEADERS B  WHERE
A.REQUIREMENT_HEADER_ID = B.REQUIREMENT_HEADER_ID
AND B.ORDER_TYPE_ID = <XXX>
order by   a.ORDER_BY_DATE desc;

select OH.ORIG_SYS_DOCUMENT_REF

       ,OH.ORDER_NUMBER

       ,OH.CREATION_DATE

       ,PPF.FULL_NAME

       ,FU.EMPLOYEE_ID

from OE_ORDER_HEADERS_ALL OH

     ,PO_REQUISITION_HEADERS_ALL POH

     ,FND_USER FU

     ,PER_ALL_PEOPLE_F PPF

     where 1                    =1

and OH.ORDER_TYPE_ID       =xxx0

and OH.SOURCE_DOCUMENT_ID  = POH.REQUISITION_HEADER_ID

and POH.TYPE_LOOKUP_CODE   = 'INTERNAL'

and POH.INTERFACE_SOURCE_CODE   = 'CSP'

and FU.USER_ID                  =OH.CREATED_BY

and FU.EMPLOYEE_ID              =PPF.PERSON_ID

and TRUNC(sysdate) between TRUNC(PPF.EFFECTIVE_START_DATE) and TRUNC(PPF.EFFECTIVE_END_DATE)

and TRUNC(OH.CREATION_DATE)     = TRUNC(sysdate)

inv_item_sub_default_pkg.insert_upd_item_sub_defaults Update Default ubinventory in Order Management tab : Item Update conversion

begin
 ip_trx_from_id   IN       NUMBER,
      ip_trx_to_id     IN       NUMBER

   IS
      --update assignment variables
      CURSOR cur_eur_upd
      IS
          SELECT   *
             FROM XX_EUR_ITEM_SHIP_SUBINV_STG
            where 1 = 1
              and STATUS_STG = 'V'
              and TRANSACTION_ID_STG between ip_trx_from_id and ip_trx_to_id
         ORDER BY transaction_id_stg;
   x_return_status   VARCHAR2 (100);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (2000);
   p_commit          VARCHAR2 (100)  DEFAULT fnd_api.g_false;
   l_msg_dummy       VARCHAR2 (2000);
   x_output          VARCHAR2 (2000);
   l_resp_id NUMBER;
   l_resp_appl_id NUMBER;
    l_user_id          NUMBER                         := xxx;
   BEGIN
      BEGIN
           select RESPONSIBILITY_ID, APPLICATION_ID
           INTO l_resp_id, l_resp_appl_id
           from FND_RESPONSIBILITY_TL
          WHERE responsibility_name  =  'Inventory';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
                 XX_asa.g_error_message :=
               'Entered into Exception when getting the responsibiliy id and applicationid';
                  END;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
/*====================================================================
           updating  assignments DFF
 ==============================================================*/
      FOR item_rec IN cur_eur_upd
      LOOP
         BEGIN
         
            inv_item_sub_default_pkg.insert_upd_item_sub_defaults
                          (x_return_status          => x_return_status,
                           x_msg_count              => x_msg_count,
                           x_msg_data               => x_msg_data,
                           p_organization_id        => item_rec.ORGANIZATION_ID,
                           p_inventory_item_id      => item_rec.INVENTORY_ITEM_ID,
                           p_subinventory_code      => 'FG_xxbujh',
                           --shipping_subinv2,
                           p_default_type           => 1,
                           p_creation_date          => SYSDATE,
                           p_created_by             => xxx,
                           p_last_update_date       => SYSDATE,
                           p_last_updated_by        => xxxx,
                           p_process_code           => 'SYNC',
                           p_commit                 => p_commit
                          );
      IF x_msg_count > 0
      THEN
         FOR j IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_msg_data, l_msg_dummy);
            x_output := ('Msg' || TO_CHAR (j) || ': ' || x_msg_data);
            FND_FILE.put_line(FND_FILE.output,SUBSTR (x_output, 1, 255));
         END LOOP;
      END IF;
           
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               PRINT ('When Others Error - Update shipping subinventory in EUR' || SQLERRM);
                        END;

CUSTOM.pll

1. Take the back up of the CUSTOM.pll present in path "/djak/uat/app/apps/apps_st/appl/au/12.0.0/resource" to CUSTOM.pll08Oct2012

2. Port the attached CUSTOM.pll to above path


3. Compile the CUSTOM.pll from above path using below command(please provide apps database username/password)

frmcmp_batch module=CUSTOM.pll userid=apps/<pwd> output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Query ORG_ID vs ORGANIZATION_ID

select FSP.INVENTORY_ORGANIZATION_ID,
       FSP.ORG_ID ,
       HOU.name   ,
       mp.organization_code
      
from FINANCIALS_SYSTEM_PARAMS_ALL  FSP,
     HR_OPERATING_UNITS HOU      ,
     mtl_parameters mp
where 1=1
and   FSP.ORG_ID                    =  HOU.ORGANIZATION_ID
and   FSP.INVENTORY_ORGANIZATION_ID =  MP.ORGANIZATION_ID(+);
and   mp.organization_code = 'XXX';

Query to retrieve active Alert information

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code

Oracle Alert History

SELECT
    alv . *
    FROM ALR_ALERTS al ,
    ALR_ACTION_HISTORY aah ,
    ALR_OUTPUT_HISTORY aoh ,
    ALR_ACTIONS_V alv ,
    alr_alert_historY_view aahv
    WHERE al . alert_name = <name of alert > --'XPEP_PENDING_APPROVALS'
    AND al . alert_id = aah . alert_id
    AND aah . check_id = aoh . check_id
    AND alv . ALERT_ID = aah . alert_id
    AND aahv . alert_name = al . alert_name;