Wednesday, November 1, 2017

How to get Oracle Workflow Approvers Comments Code

PROCEDURE get_approver_comments_doc (
      document_id     IN       VARCHAR2,
      display_type    IN       VARCHAR2,
      document        IN OUT   VARCHAR2,
      document_type   IN OUT   VARCHAR2
   )
   IS
   BEGIN
      document := NULL;
     
      FOR var IN
         (SELECT '<tr><td class="x1l x4x">'
                   || papf.full_name||'('||wnv.responder||')'
                   || '</td><td class="x1l x4x">'
                   ||nvl((select attribute_display_value from wf_notification_attr_resp_v where group_id = wnar.group_id AND ATTRIBUTE_NAME != 'COMMENETS' ),'Withdrawn' )
                   --|| nvl(ATTRIBUTE_DISPLAY_VALUE, case when wc.USER_COMMENT = 'Withdrawn by the Requestor' then 'Withdraw' end)
                   || '</td><td class="x1l x4x">'
                   || nvl(wnar.attribute_display_value,wc.USER_COMMENT)
                   || '</td><td class="x1l x4x">'
                   || TO_CHAR (wnv.end_date, 'DD-Mon-YYYY HH:MI:SS AM')
                   || '<br></td></tr>' AS NEWLINE

FROM WF_NOTIFICATIONS WNV
,wf_notification_attr_resp_v wnar
,fnd_user fu
,per_all_people_f papf
,wf_comments wc
WHERE 1=1
AND wnv.MESSAGE_TYPE like '%XXXX%'
AND wnar.MESSAGE_TYPE = wnv.MESSAGE_TYPE
and wnv.notification_id = wc.notification_id
and wc.ACTION = 'RESPOND'
AND wnv.GROUP_ID = wnar.GROUP_ID
and wnv.responder = fu.user_name
and fu.PERSON_PARTY_ID = papf.party_id(+)
AND wnar.ATTRIBUTE_NAME = 'COMMENETS'
AND wnar.MESSAGE_TYPE LIKE '%XXXX%'
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
and substr(item_key,1,instr(item_key,'/',-1)-1) = SUBSTR (wnv.CONTEXT,INSTR (wnv.CONTEXT, ':', 1)+1,INSTR (wnv.CONTEXT, '/', 1,3)- INSTR (wnv.CONTEXT, ':', 1)-1)
AND wnv.CONTEXT LIKE  (SELECT DISTINCT SUBSTR (wn.CONTEXT,1,INSTR (wn.CONTEXT, '/', 1,3)-1)
                                     FROM wf_notifications wn
                                    WHERE wn.notification_id = document_id)|| '%'
          ORDER BY wnv.end_date)
      LOOP
         document := document || var.NEWLINE;
      END LOOP;

      IF LENGTH (document) > 0
      THEN
         document :=
               '<table cellpadding="1" cellspacing="0" border="1" width="100%" style="font-family:arial;font-size:12px;" bgcolor=white><tr><th align=CENTER colspan=4 ><span style="color:#005588; font-family=arial;font-size:12">Comments of previous approvers</span></th></tr><tr><th scope="col" class="x1r">Action Owner</th><th scope="col" class="x1r x4j">Action</th><th scope="col" class="x1r x4j">Comments</th><th scope="col" class="x1r x4j">Approval Date</th></tr>'
            || document;
         document := document || '</table>';
      END IF;
   END get_approver_comments_doc;

GET ORACLE WORKFLOW ACTION HISTORY DETAILS

CREATE OR REPLACE FUNCTION APPS.XXXX_get_hist (
   p_nid         IN   PLS_INTEGER,
   p_disp_type   IN   VARCHAR2 DEFAULT wf_notification.doc_text
)
   RETURN VARCHAR2
AS
   v_hist   VARCHAR2 (32767);
BEGIN
   wf_notification.getcomments2
                            (p_nid                   => 157531211,
                             p_display_type          => NVL
                                                           (p_disp_type,
                                                            wf_notification.doc_text
                                                           ),
                             p_hide_reassign         => 'Y',
                             p_hide_requestinfo      => 'Y',
                             p_action_history        => v_hist
                            );
   RETURN v_hist;
END XXXX_get_hist;
/