Web ADI - Excel 'Run-time error 91' Error

Error: Run-time error '91′
          Object variable or with block variable not set

Probable Solution: We need to alter some Microsoft Excel settings.

Please check your Excel setting with the screenshots attached below and change accordingly.

1>  Open MS Excel
       File > Options > "Trust Center" > "Trust Center Settings..."

A> "Trusted Location"
        >> "Add new location..."
               >> Add a location where you save your Web ADI Templates
                      >> EX: C:\ADI Templates

B> "Trusted Document"

C> "Add-ins"

D> "ActiveX Settings"

E> "Macro Setting"

F> "Protected View"

G> "File Block Setting"

Close all the open Excel sessions and try again, it should work.



2> If this won't resolve the problem, check your Web Browser 'Setting' Options and add your Oracle Application URL as a Trusted/Secured Site.

Few iProcurement Requisitions Are Not Visible From Application

If you create a requisition from iProcurement and exits the creation before submitting it for approval, a requisition number will be assigned and the status of the requisition will be SYSTEM_SAVED. 

 select *
   from po_requisition_headers_all
  where authorization_status = 'SYSTEM_SAVED' ;

These System Saved Requisitions are not accessible from iProcurement/Core Purchasing.

You can avoid this by saving the cart after each step of before the approver adding step else a Concurrent Program "Purge System Saved Requisition" is available under the Purchasing  Responsibility to purge these type of requisitions.
         Parameter
         Age of Requisition: Number in days, it will purge all the requisitions having last update date less than (current date - number of days)

Selection Query of this program

select distinct prh.requisition_header_id,active_shopping_cart_flag
from po_requisition_headers_all prh, po_requisition_lines_all prl
where prh.authorization_status ='SYSTEM_SAVED'
      and prh.requisition_header_id = prl.requisition_header_id (+)
      and prh.last_update_date < (sysdate- <Age of Requisition>)
      and prl.line_location_id is null
order by prh.requisition_header_id; 

You can use below script to purge specific requisition
 ------
declare
   v_requi_hdr_id number;
   v_requi_num    po_requisition_headers_all.segment1%type := <Requisition_number> ;
begin
   select requisition_header_id
     into v_requi_hdr_id
     from po_requisition_headers_all
    where authorization_status = 'SYSTEM_SAVED'
        and segment1 = v_requi_num ;
    -->'Purge System Saved Requisition' program calling por_purge_sys_saved_req.purge_req
    --  >> and internally below packaged procedure
   por_util_pkg.purge_requisition(v_requi_hdr_id);
   dbms_output.put_line('Reuisition '||v_requi_num||' Purged');
   -- Commit the changes
   commit;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;
---------

For Project Requisitions, by default Oracle Projects defines these as a 'commitments'.

You can view these requisitions list as commitments in 'Project Status Inquiry' form
Projects Responsibility > Project Status Inquiry > Project Staus Inquiry

After running 'Purge System Saved Requisition' program you need to run 'PRC: Update Project Summary Amounts' from Projects responsibility.

Resending Failed/Sent Notifications Again

1> Check if Notification mailer/Agent listener is running
 
    select (case when (select count(1)
                         from apps.fnd_concurrent_queues
                        where concurrent_queue_name = 'WFMLRSVC') > 0
                 then 'Notification Mailer Running' 
                 else 'Notification Mailer Not Running'
                end) "Status"
       from dual

2> Run the below query get the exact number based on dates

       select *--distinct trunc(begin_date)
        from wf_notifications
      where 1=1
         and trunc(begin_date) <= trunc(sysdate-4)
         and mail_status = 'FAILED'
         and status = 'OPEN'
         order by trunc(begin_date) desc

3> Run the concurrent program 'Resend Failed/Error Workflow Notifications' from 'System  Administrator' responsibility.
     > All parameters are optional, please select the parameter values accordingly  so that only required notifications get re-processed.

     Mail Status                            : Error/Failed
     Message Type                       : Workflow Item Types
     Recipient Role                       : List of workflow roles
     Notifications sent on or after    : Date
     Notifications sent on or before : Date

4> For resending the Sent notifications, please 'Rewind'

    >> Goto Workflow > Status Monitor
        >> Search with 'Type Internal Name' and 'Item Key'
             >> You can get it from wf_notifications table

        >> Click on 'Activity History' > Select the Activity with 'Notified' Status and 'Rewind'
            >> Again select the same activity and 'Apply'
             >> You will get a message for Rewind.

    Check the wf_notifications table for same 'message_type' and 'item_type', there should be two entry with previous one 'Closed'.

5> If notification is getting failed again and again, check the user preferences for that recipient.

      Select *
        from fnd_user_preferences
       where user_name = <> ;

You can also debug the notification with the notification id

Run the @$FND_TOP/sql/wfmlrdbg.sql  
 > Parameter: Enter Value for 1: <Notification_ID >

It will generate the debug file in the same directory.

Resubmitting Failed Workflows

Very often we get workflows to get failed for different reasons, there are many workflows but here I am considering the failed Requisition Approval workflow and steps to resubmit of the same.

Ex: Requisition Approval workflow(REQAPPRV) failed with error:
      Error Name  WFENG_NOTIFICATION_PERFORMER 
      Error Message  XXXX: Activity 'REQAPPRV/XXXXX' has no performer. 

>> Check, workflow status monitor to see the failed or error-ed activity and reason.
    >> To view the workflow diagram
          Go to the Purchasing Responsibility > Requisitions > Requisition Summary
           >> Search with requisition number
           >> 'Tools' > 'View Approval through Workflow'
           >> check the failed activity, and view diagram or click on the 'error' it will show you the error details.
           >> Note down the 'Item Key' of the failed workflow.
   OR check the wf_notifications table to get the item key
           select item_key
            from wf_notifications
          where subject like '%<Requisition Number>%'
 

Now Resubmission Process

Navigation
   >> Either 'Workflow Administrator' responsibility > 'Status Monitor'
        OR
             'System Administrator' responsibility > Workflow > Administrator Workflow > 'Status Monitor'

1> Search the workflow with 'Type Internal Name' and 'Item Key', you got in the above step.
2> Select the workflow and click on 'Activity History'
3> Select the failed activity and click on 'Update Attributes'
   >> Update the missing attributes, in this case update 'Preparer User name' and 'Preparer Display Name' with the output of the below query.

      select b.full_name
        from po_requisition_headers_all prha
               ,per_people_x ppx
        where prha.segment1 = <Requisition_number>
           and prha.preparer_id = ppx.person_id

   >> 'Apply' the changes and go back to 'Monitor Activities History'

4> Select the error-ed Activity and click on 'Rewind'
     > Accept the rewind confirmation
     > Check the workflow status again.
5> It should do the job else check for errors and take appropriate actions accordingly.