Oracle Apps R12 SSHR 'Request Information' (QUESTION/ANSWER) from plsql

1. Procedure ..

    CREATE OR replace PROCEDURE apps.equest_information(pAction IN VARCHAR2, --QUESTION , for Answer keep null
                                pComments IN VARCHAR2, -- Manager request for more info details
                                userName IN VARCHAR2,  --to whom to be intended
                                pNotification_id IN NUMBER, -- Notification ID
                                pStatus OUT VARCHAR2,
                                pMessage OUT VARCHAR2
                                )
            IS
            BEGIN
                    BEGIN

IF(pAction = 'QUESTION') THEN
wf_notification.UpdateInfo(pNotification_id, userName,pComments,'MANAGER.API','WA','');
ELSE
wf_notification.UpdateInfo(pNotification_id, '',pComments,'','','');
END IF;

                    pStatus := 'S';
                    pMessage := 'Successfully '||initcap(pAction);
                    COMMIT;
            EXCEPTION
            WHEN OTHERS THEN
            pStatus := 'E';
            pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
            END equest_information;
END;           


2..----------------------------to check the transaction history then ----------
SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am')  l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
 (nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
 decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
 decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
 na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
 ,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
 AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
 UNION
 SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
 ,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
 ,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
 ,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
 ( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
 ,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
 ,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
 ,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
 FROM pqh_ss_approval_history pah ,wf_notifications wn
 WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
 AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
 AND rownum = 1 )
 UNION
 select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
 -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
 and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER') UNION
 select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
 and C.ACTION in('QUESTION', 'ANSWER')
 UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
 ,appr.text_value note ,begin_date l_date
 FROM wf_items i ,wf_item_attribute_values appr
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
 AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a