Override address in Oracle Workflow

Some times on a Oracle Apps Test or Development environment, you would like all the Workflow Email notifications to be sent to your email address.

As a developer, please do not try these steps on Production Environment, or else you will end up doing users work !

1

 

2

 

1

1

 

Advertisements

How to check issues in AME ?

Log into SQL plus/ SQL Developer. and run the following script as :

edit @1

> @1

We can use  the following file to check issues in AME.

set serveroutput on size 100000
declare 
  cursor getApplications is
  select application_id,application_name
    from apps.ame_calling_apps aca
   where sysdate < nvl(aca.end_date-(1/86400),sysdate+1);
 
  cursor checkConfigVars(appId in number) is
    select ‘Transaction Type’ configType,variable_name
      from apps.AME_CONFIG_VARS
     where application_id  = appId
       and sysdate < nvl(end_date,sysdate + 1)
       and variable_name in (‘allowAllApproverTypes’,’allowAllItemClassRules’,’allowFyiNotifications’,’productionFunctionality’)
       and variable_value not in (‘no’,’none’)
     union
    select ‘Default’,variable_name
      from apps.AME_CONFIG_VARS
     where nvl(application_id, 0) = 0
       and sysdate < nvl(end_date,sysdate + 1)
       and variable_name in (‘allowAllApproverTypes’,’allowAllItemClassRules’,’allowFyiNotifications’,’productionFunctionality’)
       and variable_value not in (‘no’,’none’)
       and not exists (select null from apps.AME_CONFIG_VARS B
         where application_id  = appId
                         and sysdate < nvl(B.end_date,sysdate + 1)
                         and variable_name = B.variable_name);

  cursor checkActionTypesVR(appId in number) is                        
 select name
 from apps.AME_ACTION_TYPE_CONFIG atc
     ,apps.AME_ACTION_TYPES atyp
 where application_id = appId
   and voting_regime <> ‘S’
   and sysdate between atc.start_date and nvl(atc.end_date,sysdate + 1)
   and sysdate between atyp.start_date and nvl(atyp.end_date,sysdate + 1) 
   and atc.action_type_id = atyp.action_type_id;   
  
  cursor checkActionTypesON(appId in number) is
    select name
 from apps.AME_ACTION_TYPE_CONFIG atc
     ,apps.AME_ACTION_TYPES atyp
     ,apps.AME_ACTION_TYPE_USAGES atu
 where application_id = appId
   and sysdate between atu.start_date and nvl(atu.end_date,sysdate + 1)
   and sysdate between atc.start_date and nvl(atc.end_date,sysdate + 1)
   and sysdate between atyp.start_date and nvl(atyp.end_date,sysdate + 1) 
   and atc.action_type_id = atyp.action_type_id
   and atu.action_type_id = atyp.action_type_id
   and (atu.rule_type, atc.order_number) in (select rule_type,order_number
                                               from apps.ame_action_type_config B,apps.ame_action_type_usages C
                                              where B.action_type_id = C.action_type_id
                                                and application_id = appId
                                                and sysdate between C.start_date and nvl(C.end_date,sysdate + 1)                
                                                and sysdate between B.start_date and nvl(B.end_date,sysdate + 1)
                                              group by rule_type,order_number
                                             having count(*) > 1);  
                                            
  cursor checkApprovalGroupsVR(appId in number) is                                             
 select name
   from apps.AME_APPROVAL_GROUPS apg
        ,apps.AME_APPROVAL_GROUP_CONFIG apgc
  where apg.approval_group_id = apgc.approval_group_id
    and apgc.application_id = appId
    and apgc.voting_regime not in (‘S’,’O’)
    and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
    and sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1);   
   
  cursor checkApprovalGroupsON(appId in number) is     
 select name
   from apps.AME_APPROVAL_GROUPS apg
        ,apps.AME_APPROVAL_GROUP_CONFIG apgc
  where apg.approval_group_id = apgc.approval_group_id
    and apgc.application_id = appId
    and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
    and sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1) 
    and order_number in (select order_number
                            from apps.AME_APPROVAL_GROUP_CONFIG apgc
                           where sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
                             and apgc.application_id = appId
                         group by order_number
                           having count(*) > 1);                                              

  cursor checkApprovalGroupItems(appId in number) is
   select name
   from apps.AME_APPROVAL_GROUPS apg
  where sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1) 
    and apg.approval_group_id in (  select apgi.approval_group_id
                             from apps.AME_APPROVAL_GROUP_ITEMS apgi
                                  ,apps.AME_APPROVAL_GROUP_CONFIG apgc
                            where sysdate between apgi.start_date and nvl(apgi.end_date,sysdate + 1)
                              and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
                              and apgc.application_id = appId
                              and apgi.approval_group_id = apgc.approval_group_id                            
                         group by apgi.approval_group_id,apgi.order_number
                           having count(*) > 1 );                        

  cursor checkItemClassSM(appId in number) is
    select itc.name
      from ame_item_classes itc
      where sysdate between itc.start_date and nvl(itc.end_date,sysdate+1)
        and itc.item_class_id in (select itu.item_class_id
                                    from ame_item_class_usages itu
                                   where itu.application_id = appId
                                     and sysdate between itu.start_date and nvl(itu.end_date,sysdate+1)
                                     and item_class_sublist_mode <> ‘S’
                                );

    isTitlePrinted boolean;
   
    currentAppId number;
    applicationRow getApplications%ROWTYPE;
    configVarsRow checkConfigVars%ROWTYPE;      
    actionTypesVRRow checkActionTypesVR%ROWTYPE;
    actionTypesONRow checkActionTypesON%ROWTYPE;
    checkApprovalGroupsVRRow checkApprovalGroupsVR%ROWTYPE;
    checkApprovalGroupsONRow checkApprovalGroupsON%ROWTYPE;
    checkApprovalGroupItemsRow checkApprovalGroupItems%ROWTYPE;   
  begin
    dbms_output.put_line(‘Setup causing Parallelization Errors … ‘);
    for applicationRow in getApplications loop
      isTitlePrinted := false;
      currentAppId := applicationRow.application_id;
      for configVarsRow in checkConfigVars(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Incorrect value for ‘ || configVarsRow.configType || ‘ Configuration Variable :: ‘ || configVarsRow.variable_name);
      end loop;
      dbms_output.put_line(‘ ‘);
      for actionTypesVRRow in checkActionTypesVR(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non serial Voting Regime for Action Type :: ‘ || actionTypesVRRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);
      for actionTypesONRow in checkActionTypesON(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Action Type :: ‘ || actionTypesONRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);     
      for checkApprovalGroupsVRRow in checkApprovalGroupsVR(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non serial or Order Number Voting Regime for Approval Group :: ‘ || checkApprovalGroupsVRRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);         
      for checkApprovalGroupsONRow in checkApprovalGroupsON(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Approval Group Config :: ‘ || checkApprovalGroupsONRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);       
      for checkApprovalGroupItemsRow in checkApprovalGroupItems(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Items of Approval Group :: ‘ || checkApprovalGroupItemsRow.name);
      end loop;
      dbms_output.put_line(‘ ‘); 
      for checkItemClassSMRow in checkItemClassSM(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
       end if;
        dbms_output.put_line(‘Non Serial Sublist Modes for Item Class Usages :: ‘ || checkItemClassSMRow.name);
      end loop;
    end loop;
  end;
/
rollback;

Adding CC and Bcc to Workflow Notification

For adding CC or BCC to the notification we use the following technique.

Define a new attribute CC_NAME , of type text or Role which contains the details of the CC.

Use the seeded attribute #WFM_CC and set the default for this to CC_NAME. Also keep Type as Text or “Role” and Source as Send. Drag that from the Item level to the Message level.

In the PL/SQL set the value for this CC_NAME attribute to the people for whom you want the CC to be marked to.

wf_engine.setitemattrtext (p_itemtype,
l_itemkey,
‘CC_NAME’,
l_cc_user_name
);

#WFM_BCC can be used for adding BCC.

 

For adding additional recipients, separated by semicolons (;) as value to this attribute.

Deleting Pending workflow Notification

As per metalink Note:144806.1 we Submit the Purge Obsolete Workflow Runtime Data concurrent request (FNDWFPR) for all items,

As per metalink note Note:165316.1 we run bde_wf_data.sql Workflow Purge Script and get the status of pending workflow data.

The above script create a statement as below for run to purge data.

exec WF_PURGE.ITEMS(’XDPWFSTD’,NULL,SYSDATE,FALSE);

SQL> select count(*),status from wf_notifications group by status

FROM_USER field is null in WF_NOTIFICATIONS table

There is a seeded attribute  #FROM_ROLE that needs to be set.

3

 

Attach the Attribute to the Message…

 

4

 

 

Set values for this attribute .

wf_engine.setitemattrtext (itemtype      => ‘TEST’,
                                          itemkey       => l_chr_key,
                                          Aname         => ‘#FROM_ROLE’,
                                          Avalue        => P_Requestor_Name—l_chr_requestor
                                         );