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;

No comments:

Post a Comment