Download Files From DB and Upload to FTP in IFS

DECLARE
   error_msg_         VARCHAR2(32000);
   file_name_         edm_file_tab.file_name%TYPE;
   local_file_name_   VARCHAR2(4000);
   directory_path_    VARCHAR2(2000);
   doc_type_          edm_file_tab.doc_type%TYPE;
   
   value_delim_       VARCHAR2(100) := '[^^]+';
   value_count_delim_ VARCHAR2(100) := '\^';
   value_count_       NUMBER;
   
   CURSOR get_doc_info IS
      SELECT doc_class, 
             doc_no, 
             doc_sheet, 
             doc_rev, 
             Edm_File_API.Get_Doc_Types_For_Document(doc_class, doc_no, doc_sheet, doc_rev) doc_type,
             key_ref, 
             lu_name
      FROM   doc_reference_object_tab
      WHERE  lu_name = 'ManSuppInvoice'
      AND    key_ref LIKE 'COMPANY=' || '02' || '^INVOICE_ID=' || '129' || '^';
   

   CURSOR get_directory_path IS
      SELECT TO_CHAR(directory_path)   directory_path
      FROM   all_directories
      WHERE  directory_name = 'OUT_DIR';
BEGIN
   OPEN  get_directory_path;
   FETCH get_directory_path INTO directory_path_;
   IF (get_directory_path%NOTFOUND) THEN
      directory_path_ := NULL;
   END IF;
   CLOSE get_directory_path;
   
   IF (directory_path_ IS NOT NULL) THEN
      FOR rec_ IN get_doc_info LOOP
         value_count_ := REGEXP_COUNT(rec_.doc_type, value_count_delim_);
         
         FOR i_ IN 1..value_count_
         LOOP
            doc_type_ := REGEXP_SUBSTR(rec_.doc_type, value_delim_, 1, i_);
         END LOOP;
         
         file_name_       := Edm_File_API.Get_File_Name(rec_.doc_class, rec_.doc_no, rec_.doc_sheet, rec_.doc_rev, doc_type_);
         local_file_name_ := '\\server01\vol1\output' || '\' || TO_CHAR(SYSDATE, 'yyyymmddhhmmss') || '-' || file_name_;
         
         -- download from db
         Batch_Transfer_Handler_API.Download_From_Db(error_msg_       => error_msg_,
                                                     doc_class_       => rec_.doc_class,
                                                     doc_no_          => rec_.doc_no,
                                                     doc_sheet_       => rec_.doc_sheet,
                                                     doc_rev_         => rec_.doc_rev,
                                                     doc_type_        => doc_type_,
                                                     local_file_name_ => local_file_name_);
        
         
         -- upload to ftp
         error_msg_ := NULL;
         Batch_Transfer_Handler_API.Upload_To_Ftp(error_msg_         => error_msg_,
                                                  local_file_name_   => local_file_name_,
                                                  file_name_         => file_name_,
                                                  edm_location_name_ => 'DELIVERY_NOTES_AT');
      END LOOP;
   END IF;
END;

Create a new FTP access repo and use this location in the above procedure