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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s