Oracle Approval Graph

   PROCEDURE getapprovalgraph (preqno IN VARCHAR2, o_appr_graph OUT VARCHAR2)
   IS
      approvalgraph             VARCHAR2 (10000);
      image_path                VARCHAR2 (100)
                                   := 'http://companyURL/OA_MEDIA';
      person_name               VARCHAR2 (300);
      vl_final_status           VARCHAR2 (300) := 'In Process';
      vl_arrow_img              VARCHAR2 (1000);
      vl_line_img               VARCHAR2 (1000);
      vl_header                 VARCHAR2 (2000);
      vl_fotter                 VARCHAR2 (2000);
      vl_start_approved_table   VARCHAR2 (2000);
      vl_approved_table         VARCHAR2 (2000);
      vl_inprocess_table        VARCHAR2 (2000);
      vl_max_squence            NUMBER;

      CURSOR c1
      IS
           SELECT (SELECT NVL (full_name, user_name)
                     FROM per_all_people_f papf, fnd_user fu
                    WHERE     fu.user_id = bcr.user_id
                          AND fu.employee_id = papf.person_id
                          AND ROWNUM = 1)
                     employee_name,
                  bcr.sequence_no,
                  bcr.action_meaning
             FROM xxbg_asset_disp_appr_history_t bcr
            WHERE request_number = preqno
         ORDER BY 2;
   BEGIN
      vl_line_img :=
         '<td><img src=' || image_path || '/ag_line_horizontal.gif ></td>';
      vl_arrow_img :=
         '<td><img src=' || image_path || '/ag_arrow_horizontal.gif ></td>';

      /*  Header */
      vl_header :=
         '<table border=''0'' cellpadding=''0'' cellspacing=''0''><tr><td align=center><img src='
         || image_path
         || '/ag_transparentpixel.gif width=''0'' height=''56'' align=''middle'' alt=''Blank image''><img src='
         || image_path
         || '/tree_document.gif align=''middle'' alt=''Image for document''></td>';


      /*  Fotter */
      vl_fotter :=
            vl_line_img
         || vl_arrow_img
         || '<td><span class=''OraLinkText''>'
         || vl_final_status
         || '</span></td></tr></table>';

      FOR i IN c1
      LOOP
         IF i.sequence_no = 0
         THEN
            vl_header := NULL;

            vl_start_approved_table := NULL;
            vl_inprocess_table := NULL;
            vl_approved_table := NULL;

            SELECT MAX (sequence_no)
              INTO vl_max_squence
              FROM xxbg_asset_disp_appr_history_t bcr
             WHERE request_number = preqno AND sequence_no <> 99;

            vl_header :=
               '<table border=''0'' cellpadding=''0'' cellspacing=''0''><tr><td align=center><img src='
               || image_path
               || '/ag_transparentpixel.gif width=''0'' height=''56'' align=''middle'' alt=''Blank image''><img src='
               || image_path
               || '/tree_document.gif align=''middle'' alt=''Image for document''></td>';


            vl_start_approved_table :=
               '<td><table width=''100%'' border=''0'' cellspacing=''0'' cellpadding=''0'' bgcolor=''#003399'' height=''44''><tr><td><span><table width=''100%'' border=''0'' cellspacing=''1'' cellpadding=''0''><tr><td width=''10%'' bgcolor=''#E9F5FF''> <div align=''center''><img src='
               || image_path
               || '/okind_status.gif width=''16'' height=''16'' alt=''Approved''></div></td><td height=''22'' bgcolor=''#C3D9EE''><div align=''center''><img src='
               || image_path
               || '/tree_person.gif width=''16'' height=''16'' alt=''Image for approver type''></div></td></tr><tr><td colspan=2 height=''22'' bgcolor=''#FFFFFF'' nowrap><p align=''center''><span class=''OraInstructionText''>'
               || i.employee_name
               || '</span></p></td></tr></table></span></td></tr></table></td>';
         END IF;

         IF i.sequence_no NOT IN (0, 99)
         THEN
            IF vl_max_squence = i.sequence_no
            THEN
               vl_inprocess_table :=
                  vl_inprocess_table || vl_line_img || vl_arrow_img
                  || '<td><table width=''100%'' border=''0'' cellspacing=''0'' cellpadding=''0'' bgcolor=''#003399'' height=''44''><tr><td><span><table width=''100%'' border=''0'' cellspacing=''1'' cellpadding=''0''><tr><td width=''10%'' bgcolor=''#E9F5FF''> <div align=''center''><img src='
                  || image_path
                  || '/inprogressind_status.gif width=''16'' height=''16'' alt=''Pending''></div></td><td height=''22'' bgcolor=''#C3D9EE''><div align=''center''><img src='
                  || image_path
                  || '/tree_person.gif width=''16'' height=''16''></div></td></tr><tr><td colspan=2 height=''22'' bgcolor=''#FFFFFF'' nowrap><p align=''center''><span class=''OraInstructionText''>'
                  || i.employee_name
                  || '</span></p></td></tr></table></span></td></tr></table></td>';
            ELSE
               vl_approved_table :=
                  vl_approved_table || vl_line_img || vl_arrow_img
                  || '<td><table width=''100%'' border=''0'' cellspacing=''0'' cellpadding=''0'' bgcolor=''#003399'' height=''44''><tr><td><span><table width=''100%'' border=''0'' cellspacing=''1'' cellpadding=''0''><tr><td width=''10%'' bgcolor=''#E9F5FF''> <div align=''center''><img src='
                  || image_path
                  || '/okind_status.gif width=''16'' height=''16'' alt=''Approved''></div></td><td height=''22'' bgcolor=''#C3D9EE''><div align=''center''><img src='
                  || image_path
                  || '/tree_person.gif width=''16'' height=''16'' alt=''Image for approver type''></div></td></tr><tr><td colspan=2 height=''22'' bgcolor=''#FFFFFF'' nowrap><p align=''center''><span class=''OraInstructionText''>'
                  || i.employee_name
                  || '</span></p></td></tr></table></span></td></tr></table></td>';
            END IF;
         END IF;

         IF i.sequence_no = 99
         THEN
            vl_fotter := NULL;
            vl_fotter :=
                  vl_line_img
               || vl_arrow_img
               || '<td><span class=''OraLinkText''>'
               || i.action_meaning
               || '</span></td></tr></table>';
         END IF;
      END LOOP;



      approvalgraph :=
            vl_header
         || vl_arrow_img
         || vl_start_approved_table
         || vl_approved_table
         || vl_inprocess_table
         || vl_fotter;


      o_appr_graph := approvalgraph;
   END;