Thursday, 26 July 2012

Return for correction in Oracle Notifications R12



Return for correction is the link in the notification page to send/return the control to the submitter for correction. This link navigates to the some other OAF page. It is not the button in the notification page (which comes from workflow lookup code of result type/lookup type).


If we click on the return for correction link the below page appears:






The return for correction link can be found in HR approval notifications like Leave approval, Competency approval, adieu/termination approval, PMS (Performance Management System) reviewer notification etc…

Issue:
Generally we may face issue whenever we click on the return for correction link; it navigates to the open notifications instead of opening the new page to submit for correction.


1. Causes of the issue:
  • Submitter and the Approver are same (say if a person submits his/her leave to  himself/herself)
  • Delegates/reassign the ownership of the notification (It depends on the original recipient of the notification)
  • Request for more info (may work, it depends on customizations on original recipient)

NOTE:
It is perfect perception from Oracle side because there is no point doing return for correction when the submitter and approver are same. But this issue may occur whenever delegations/reassigning the ownerships/request for more info and customizations are done.


2. Issue when the standard Oracle product is customized:
Example: Performance Management System (PMS)
  As per Oracle set KRA/appraisals goes to the supervisor of the employee. If the product is customized in such a way that all set KRAs/appraisals should go for approval on the basis of employee selection from the adhoc approver.
 
     In order to achieve the above requirement, we need to write custom logic and should customize the workflow (HR-Appraisal process).

   Process:  The employee sends his/her appraisal to Main Appraiser and if he/she is not the right person, Main appraiser sends it back to the employee to select the new main appraiser. If the employee selects the correct approver (new main appraiser), approver sends it to the reviewer to review the appraisal. Issue may occur in the reviewer notification* when reviewer tries to click on return for correction.

*In R12, Self Service Generic Notification Process is common for most of the HR approvals.
  As this is common for all, issue may occur in various processes like Leave, Termination, Competencies, PMS reviewer etc…


Solution for the Issue1:

1. Get the notification ID of the oracle notification in which you are facing problem return for correction.
2. Updating original recipient column in wf_notifications table to any other user name for the noted notification id.

Explanation:
 Whenever you click on return for correction link it internally calls the queries (a and d below) in the ReturnForCorrectionCO. Query a should return ‘N’. Query d editallowed field should return ‘Y’. If the query returns null or ‘N’ the page navigates to the work list i.e notifications.


Solution for the Issue2 when customization is done:

Check the values of the below workflow attributes of HR workflow.

Workflow Attributes:

Approval Creator Username   
Approval Creator ID                
Approval Creator Display Name   

Forward From Display Name 
Forward From ID                   
Forward From Username       

Creator Person ID                  
Creator Person Username 
Creator Person Display Name 

Main Appraiser Person Id   
Main Appraiser User Name   
Main Appraiser Display Name  

All attributes should have the new main appraiser person ID, User Name and Full Name values.
If not, Update attribute values in the workflow.
Steps to update attribute values:
1.       Get the item key using notification id from wf_notifications table
2.       Open status monitor using item type HR(HRSSA) and item key
3.       Select workflow details à Update attributes at the bottom of the page
4.       Change the values and apply


OAF:
oracle.apps.pqh.selfservice.workflow.webui.ReturnForCorrectionCO
oracle.apps.pqh.selfservice.workflow.server.ReturnForCorrectionAM


Queries:

a)
select
pqh_ss_workflow.complete_custom_rfc(14747838) from dual

b)
select transaction_id,item_key from hr_api_transactions where creator_person_id=35406
and transaction_ref_table='PER_APPRAISALS'

c)
select * from wf_notifications where notification_id =20386415 -- Get Item Key and compare with the above item key

Parameters for the query:
1,4 – Trasnaction ID
2,3 –  Notification ID
5    – Orig System (Ex: PER)
         select hr_approval_ss.getuserorigsystemid(null,:Notification_id) from dual
6    –  Orig system ID
         Person ID of the notification owner
         select hr_approval_ss.getuserorigsystemid(null,:Notification_id) from dual


d)
SELECT   ROWNUM - 1 seq, rfc.*
    FROM (SELECT   pah.action test_action, hat.transaction_id, hat.item_type,
                   hat.item_key, pah.approval_history_id, pah.user_name,
                   NVL
                      (pah.orig_system,
                       hr_approval_ss.getuserorigsystem (pah.user_name)
                      ) orig_system,
                   NVL
                      (pah.orig_system_id,
                       hr_approval_ss.getuserorigsystemid (pah.user_name)
                      ) orig_system_id,
                   hr_approval_ss.getroledisplayname
                                              (pah.user_name,
                                               pah.orig_system,
                                               pah.orig_system_id
                                              ) person_name,
                   hl.meaning action,
                   hr_approval_ss.isapprovereditallowed
                                             (hat.transaction_id,
                                              pah.user_name,
                                              pah.orig_system,
                                              pah.orig_system_id
                                             ) editallowed
              FROM hr_api_transactions hat,
                   pqh_ss_approval_history pah,
                   hr_lookups hl
             WHERE hat.transaction_id = :1
               AND pah.transaction_history_id = hat.transaction_id
               AND pah.action IN
                          ('APPROVED', 'SUBMIT', 'APPROVED_EDIT', 'RESUBMIT')
               AND pah.approval_history_id <> 0
               AND pah.approval_history_id <=
                      NVL
                         ((SELECT MAX (approval_history_id) + 1
                             FROM pqh_ss_approval_history ph
                            WHERE ph.transaction_history_id =
                                                    pah.transaction_history_id
                              AND ph.orig_system =
                                     hr_approval_ss.getuserorigsystem (NULL,
                                                                       :2
                                                                      )
                              AND ph.orig_system_id =
                                     hr_approval_ss.getuserorigsystemid (NULL,
                                                                         :3
                                                                        )
                              AND ph.approval_history_id <> 0),
                          999999999
                         )
               AND hl.lookup_type = 'PQH_SS_APPROVAL_STATUS'
               AND hl.lookup_code = pah.action
               AND pah.approval_history_id IN (
                      SELECT MAX (p1.approval_history_id)
                        FROM pqh_ss_approval_history p1
                       WHERE p1.transaction_history_id = :4
                         AND p1.action <> 'RFC')
               AND pah.orig_system_id <>
                                  hr_approval_ss.getuserorigsystemid (NULL,:5) – This condition may not satisfy whenever the submitter and approver are same
          ORDER BY approval_history_id) rfc
   WHERE rfc.orig_system = NVL (:6, rfc.orig_system)
     AND rfc.orig_system_id = NVL (:7, rfc.orig_system_id)
     AND rfc.editallowed = 'Y'
ORDER BY approval_history_id DESC;






Thanks,
Rajesh