0 Replies Latest reply: Nov 23, 2012 12:58 PM by 954716 RSS

    Oracle Applications R12 AP Invoice Workflow Attachment

    954716
      In AP Invoice Workflow, as the customer is attaching attachments with the invoice and initiating Workflow, the approver is getting email notification and notification in Oracle Apps Workflow User. In the email notification, the Attachments are being shown as the file names as they were attached. But in Workflow Web user, as he is logging in to Apps, in the Notofication page, the Attachments are coming as links of their Display names as the Workflow Attributes have been defined in Workflow builder for those attachments.
      The Client does not like to see them by the name as HW ATTACHMENT, HW ATTACHMENT2 etc and rather they want to see their actual file name.
      How can this be achieved?

      The code used in here is:

      PROCEDURE hw_notif_attach_procedure (
      document_id IN VARCHAR2,
      display_type IN VARCHAR2,
      document IN OUT BLOB,
      document_type IN OUT VARCHAR2
      )
      IS
      lob_id NUMBER;
      bdoc BLOB;
      content_type VARCHAR2 (100);
      filename VARCHAR2 (300);
      BEGIN
      --set_debug_context('xx_notif_attach_procedure');
      lob_id := TO_NUMBER (document_id);

      -- Obtain the BLOB version of the document
      SELECT file_name, file_content_type, file_data
      INTO filename, content_type, bdoc
      FROM fnd_lobs
      WHERE file_id = lob_id;

      document_type := content_type || ';name=' || filename;
      DBMS_LOB.COPY (document, bdoc, DBMS_LOB.getlength (bdoc));
      EXCEPTION
      WHEN OTHERS
      THEN
      --debug('ERROR ^^^^0018 ' || SQLERRM);
      wf_core.CONTEXT ('AP_WFAPPROVAL_PKG',
      'HW_notif_attach_procedure',
      document_id,
      display_type
      );
      RAISE;
      END hw_notif_attach_procedure;

      And in the Main procedure:

      ...................................
      cursor cur_files(l_invoice_id IN NUMBER) IS
      SELECT fl.file_id
      --FROM fnd_lobs fl, fnd_documents_tl dt, fnd_attached_documents ad  /* commented for R12 and replaced fnd_documents_tl with fnd_documents */
      FROM fnd_lobs fl, fnd_documents dt, fnd_attached_documents ad
      WHERE ad.document_id = dt.document_id
      AND dt.media_id = fl.file_id
      AND ad.entity_name = 'AP_INVOICES'
      AND ad.pk1_value = TO_CHAR (l_invoice_id);
      begin

      for rec_files in cur_files(l_invoice_id)
      loop
      l_count:=l_count+1;
      if l_count=1 then
      v_file_attachment1:=to_char(rec_files.file_id);
      elsif l_count=2 then
      v_file_attachment2:=to_char(rec_files.file_id);
      elsif l_count=3 then
      v_file_attachment3:=to_char(rec_files.file_id);
      end if;
      exit when l_count>3;
      end loop;

      EXCEPTION
      WHEN OTHERS THEN
      v_file_attachment1:=-99;
      v_file_attachment2:=-99;
      v_file_attachment3:=-99;
      end;
      ..................................
      ..................................

      IF (v_file_attachment1 <> -99)
      THEN
      wf_engine.setitemattrdocument
      (itemtype,
      itemkey,
      aname => 'HW_ATTACHEMENT',
      documentid => 'PLSQLBLOB:HW_AP_WFAPPROVAL_CUSTOM_PKG.HW_notif_attach_procedure/'
      ||v_file_attachment1
      );
      END IF;

      IF (v_file_attachment2 <> -99)
      THEN
      wf_engine.setitemattrdocument
      (itemtype,
      itemkey,
      aname => 'HWATTACHMENT2',
      documentid => 'PLSQLBLOB:HW_AP_WFAPPROVAL_CUSTOM_PKG.HW_notif_attach_procedure/'
      ||v_file_attachment2
      );
      END IF;

      IF (v_file_attachment3 <> -99)
      THEN
      wf_engine.setitemattrdocument
      (itemtype,
      itemkey,
      aname => 'HWATTACHMENT3',
      documentid => 'PLSQLBLOB:HW_AP_WFAPPROVAL_CUSTOM_PKG.HW_notif_attach_procedure/'
      ||v_file_attachment3
      );
      END IF;
      .......................

      This is a very important requirement for the customer and the go-live is very soon

      Please help me achieving the solution.

      Thanks a lot in advance.