Attachment migration script (PO)

--Create temporary table and store the po attachment files to be loaded
CREATE TABLE XX_UPLOAD_ATTACHMENTS_TBL
(
  PO_NUMBER          VARCHAR2(20 BYTE),
  FILE_TYPE          VARCHAR2(20 BYTE),
  FILE_NAME          VARCHAR2(100 BYTE),
  FILE_CONTENT       BLOB,
  PROCESS_FLAG       VARCHAR2(1 BYTE)           DEFAULT 'N',
  ERROR_DESCRIPTION  VARCHAR2(4000 BYTE),
  DOCUMENT_TYPE      VARCHAR2(50 BYTE)
);


--script

CREATE OR REPLACE PROCEDURE      xx_load_attachment_prc (
   errbuf    OUT   VARCHAR2,
   retcode   OUT   VARCHAR2
)
IS
   x_access_id   NUMBER;
   x_file_id     NUMBER;
   v_po_hdr_id   po_headers_all.po_header_id%TYPE   := NULL;

   PROCEDURE upload_file (
      v_filename    IN       VARCHAR2,
      x_access_id   OUT      NUMBER,
      x_file_id     OUT      NUMBER
   )
   AS
      v_access_id   NUMBER;
      v_file_id     NUMBER;
      x_errbuf      VARCHAR2 (200);
      fid           NUMBER         := -1;
      fn            VARCHAR2 (256);
      mt            VARCHAR2 (240);
      bloblength    NUMBER;     
      ufslim        NUMBER;
   BEGIN
      v_access_id := fnd_gfm.authorize (NULL);
      x_access_id := v_access_id;
      fnd_file.put_line (fnd_file.LOG, 'Access id :' || v_access_id);

      -- The function fnd_gfm.confirm_upload return the file id
      IF (fnd_gfm.authenticate (v_access_id))
      THEN
         SELECT fnd_lobs_s.NEXTVAL
           INTO fid
           FROM DUAL;

         fn := SUBSTR (v_filename, INSTR (v_filename, '/') + 1);
      
         SELECT DBMS_LOB.getlength (blob_content), mime_type
           INTO bloblength, mt
           FROM fnd_lobs_document
          WHERE NAME = v_filename AND ROWNUM = 1;
         
         IF fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT') IS NULL
         THEN
            ufslim := bloblength;
         ELSE
            /* The profile is not limited to being a numeric value.  Stripping off any
               reference to kilobytes. */
            IF (INSTR (UPPER (fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')),
                       'K'
                      ) > 0
               )
            THEN
               ufslim :=
                  SUBSTR
                     (fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT'),
                      1,
                        INSTR
                           (UPPER (fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')
                                  ),
                            'K'
                           )
                      - 1
                     );
            ELSE
               ufslim := fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT');
            END IF;
            
            ufslim := ufslim * 1000;
         END IF;

         IF bloblength BETWEEN 1 AND ufslim
         THEN
            INSERT INTO fnd_lobs
                        (file_id, file_name, file_content_type, file_data,
                         upload_date, expiration_date, program_name,
                         program_tag, LANGUAGE, file_format)
               (SELECT upload_file.fid, fn, ld.mime_type, ld.blob_content,
                       SYSDATE, NULL, 'PO', 'PO', 'en',
                       fnd_gfm.set_file_format (mt)
                  FROM fnd_lobs_document ld
                 WHERE ld.NAME = v_filename AND ROWNUM = 1);

            IF (SQL%ROWCOUNT <> 1)
            THEN
               RAISE NO_DATA_FOUND;
            END IF;

            UPDATE fnd_lob_access
               SET file_id = fid
             WHERE access_id = v_access_id;
         -- bug 3045375, added else to return fid = -2.
         ELSE
            fid := -2;
         END IF;
      END IF;

      DELETE FROM fnd_lobs_document;

      DELETE FROM fnd_lobs_documentpart;

      x_file_id := fid;
      fnd_file.put_line (fnd_file.LOG, 'File id :' || x_file_id);
   EXCEPTION
      WHEN OTHERS
      THEN
         x_errbuf :=
               'Procedure upload_file errored out with the following error : '
            || SQLERRM;
         fnd_file.put_line (fnd_file.LOG, x_errbuf);
   END upload_file;
BEGIN
   FOR rec_1 IN (SELECT a.ROWID r_id, a.po_number, a.file_name,
                        fmt.mime_type
                   FROM xx_upload_attachments_tbl a,
                        fnd_mime_types_tl fmt
                  WHERE NVL (a.process_flag, 'N') = 'N'
                    AND a.file_type = fmt.file_format_code)
   LOOP
      x_access_id := NULL;
      x_file_id := NULL;
      v_po_hdr_id := NULL;

      BEGIN
         --inserting recorsds into fnd lob doc
         INSERT INTO fnd_lobs_document
                     (NAME, mime_type, doc_size, content_type, blob_content)
            SELECT file_name, rec_1.mime_type,
                   DBMS_LOB.getlength (file_content), 'BINARY', file_content
              FROM xx_upload_attachments_tbl
             WHERE po_number = rec_1.po_number;

         fnd_file.put_line (fnd_file.LOG,
                               'UPLOAD_FILE_SIZE_LIMIT '
                            || fnd_profile.VALUE ('UPLOAD_FILE_SIZE_LIMIT')
                           );
         upload_file (rec_1.file_name, x_access_id, x_file_id);
         COMMIT;

         SELECT po_header_id
           INTO v_po_hdr_id
           FROM po_headers_all
          WHERE type_lookup_code NOT IN ('RFQ', 'QUOTATION')
            AND segment1 = rec_1.po_number;

         fnd_webattch.add_attachment
                                    (seq_num                   => 10,
                                     category_id               => 1000504,
                                     --for documents
                                     document_description      => 'PO Attachments',
                                     datatype_id               => 6,
                                     text                      => NULL,
                                     file_name                 => rec_1.file_name,
                                     url                       => NULL,
                                     function_name             => 'PO_POXPOEPO',
                                     entity_name               => 'PO_HEADERS',
                                     pk1_value                 => v_po_hdr_id,
                                     pk2_value                 => 0,
                                     pk3_value                 => NULL,
                                     pk4_value                 => NULL,
                                     pk5_value                 => NULL,
                                     media_id                  => x_file_id,
                                     user_id                   => 1213,---userid 
                                     usage_type                => 'O'
                                    );

         UPDATE xx_upload_attachments_tbl
            SET process_flag = 'Y'
          WHERE ROWID = rec_1.r_id;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, SQLERRM);
      END;
   END LOOP;
END xx_load_attachment_prc;
/