1 Reply Latest reply on Jan 8, 2016 9:56 PM by Tubby

    Not able to attach pdf in FND_LOBS

    edy12

      Hi Folks,

       

      I am trying to attach a pdf file as BLOB content in FND_LOBS. Before inserting the pdf I am calling a CP(A report) from backend in a separate procedure, When clicking on View Output the File opens in that pdf format. I want to attach that file.

       

      PROCEDURE call_po_doc_report(p_from_po IN VARCHAR2,
                                                        p_to_po   IN VARCHAR2
                                                        p_errbuf  OUT VARCHAR2,
                                                        p_retcode OUT VARCHAR2) IS

       

      lv_report_conc_req_id NUMBER;
      lv_from_po        VARCHAR2(10) := NULL;
      lv_to_po          VARCHAR2(10) := NULL;
      lv_request_id     NUMBER;
      lv_request_status BOOLEAN;
      lv_complete       BOOLEAN;
      lv_phase          VARCHAR2(100);
      lv_status         VARCHAR2(100);
      lv_dev_phase      VARCHAR2(100);
      lv_dev_status     VARCHAR2(100);
      lv_message        VARCHAR2(100);
      lv_file_names     VARCHAR2(200);
      lv_attach_names   BLOB;

       

        BEGIN

       

      lv_from_po := p_from_po;
      lv_to_po   := p_to_po;

       

      lv_report_conc_req_id := apps.fnd_request.submit_request(application => 'XYZ',
                                                                                                                                                     program  => 'XYZ_APPROVED_PO',
                                                                                                                                                     description => 'Print Report',
                                                                                                                                                     start_time  => SYSDATE,
                                                                                                                                                     sub_request => FALSE,
                                                                                                                                                     argument1   => lv_from_po, -- From PO
                                                                                                                                                     argument2   => lv_to_po -- To PO
                                                               );

       

      COMMIT;

       

      -- Getting the File Attachment which can be added

          SELECT outfile_name, lv_from_po || '_out.pdf'

            INTO lv_file_names, lv_attach_names

            FROM apps.fnd_concurrent_requests

           WHERE request_id = lv_report_conc_req_id;

       

          -- Calling Procedure to add notes

          add_notes(p_pk1_value   => 1290,

                          p_pk2_value   => null,

                          p_pk3_value   => null,

                          p_note_text   => 'Test1',

                          p_note_title  =>  'Test1',

                          p_entity_name => 'AR_CUSTOMERS',

                          p_file_name   => lv_file_names,

                          p_file        => lv_attach_names,

                          p_cat_id      => 1);

       

      END call_po_doc_report;

       

      PROCEDURE add_notes(p_pk1_value   IN VARCHAR2,

                            p_pk2_value   IN VARCHAR2,

                            p_pk3_value   IN VARCHAR2,

                            p_note_text   IN VARCHAR2,

                            p_note_title  IN VARCHAR2,

                            p_entity_name IN VARCHAR2,

                            p_file_name   IN VARCHAR2,

                            p_file        IN VARCHAR2,

                            p_cat_id      IN NUMBER) IS

       

          lv_entity_name apps.fnd_attached_documents.entity_name%TYPE := p_entity_name;

          lv_category_id NUMBER := p_cat_id;

          lv_att_doc_id  NUMBER := 0;

          lv_doc_id      NUMBER := 0;

          lv_media_id    NUMBER := 0;

          lv_seq_num     NUMBER := 0;

          lv_datatype_id NUMBER := 1;

          lv_rowid       ROWID;

       

      BEGIN

       

      fnd_file.put_line(fnd_file.log, '---Inserting in fnd_lobs---');

       

          INSERT INTO fnd_lobs

            (file_id,

             file_name,

             file_content_type,

             upload_date,

             expiration_date,

             program_name,

             program_tag,

             file_data,

             language,

             oracle_charset,

             file_format)

          VALUES

            (lv_media_id,

             p_file_name,

             'application/pdf',

             SYSDATE,

             NULL,

             'FNDATTCH',

             NULL,

             p_file,

             'US',

             'UTF8',

             'binary');

       

      EXCEPTION

          WHEN OTHERS THEN

            fnd_file.put_line(fnd_file.log, 'Error: ' || SQLERRM);

       

      END add_notes;

       

      But whenever I try to run the Program I get the following Error

      Error: ORA-01465: invalid hex number


      This Error is coming from add_notes procedure.

       

      Need help ASAP

       

      Thanks

        • 1. Re: Not able to attach pdf in FND_LOBS
          Tubby

          Guessing you'll have better luck in the e-biz forum than you will here assuming you haven't posted there already.

           

          E-Business Suite

           

          As a general note, you are really doing yourself a disservice with the following piece of code

          EXCEPTION
              WHEN OTHERS THEN
                fnd_file.put_line(fnd_file.log, 'Error: ' || SQLERRM);
          
          

           

          All that is going to do is make it a heck of a lot harder to track down where the code is going kaboom. If you replace the SQLERRM with a call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (if your version supports it) you should be able to see the the entire stack pertaining to the failure which would likely help you sort out where things are going awry.

           

          Cheers,