Using Oracle SRS Delivery Options to SFTP output

Dear Reader

I am sure many of us have faced the requirement where we need to SFTP the output generated by a program to a specific server and location

Maybe we have the requirement to generate a CSV file in specific location with specific naming conventions and then SFTP this to a remote server

Most of the times, we would go with the approach of using a custom Host program to perform the SFTP operation which will pick the CSV file we wrote in specific directory and send this to the remote server

However, when we use the custom program approach especially for SFTP, we need to create the private-public key combination on the 2 servers which may not be accepted by the remote server

Now, in R12, Oracle have given a way to do this using seeded functionality via Delivery options window on the SRS submission itself as shown below

We just need to enter the destination details here along with the password in this screen

Now, though this is fine if you want to SFTP the output file from $APPLCSF/$APPLOUT directory with the name as o<request_id>.out ... what if you want to SFTP a file from another directory with specific name such as SFTPTestingDelOpt20140917.csv ???

Then, this seeded approach alone will not work. So for this we need to add below update statement at the end of the program to update the outfile name in FCR table so that the delivery options will know the file to be transferred

lv_request_id is fnd_global.conc_request_id
lv_file_dir is the directory in which you created the file
lv_file_name is the file with name as SFTPTestingDelOpt20140917.csv

UPDATE   fnd_concurrent_requests

SET      outfile_name = lv_file_dir || '/' || lv_file_name

   WHERE    request_id = lv_request_id;

Now, many user may also complain that they do not want to enter the delivery options details each time when they run the program and rather they would have the same done automatically.

This can also be done by creating a row in FND_CONC_PP_ACTIONS as shown below. Here, the assumption is we pass the server name, user name, password and destination directory as hidden parameters to our program and it will create the delivery options internally and user need not to enter it ... simple isn't it :-)

   SELECT   processor_id
   INTO     lv_processor_id
   FROM     fnd_conc_pp_actions
   WHERE    concurrent_request_id = lv_request_id
   AND      ROWNUM < 2;

   INSERT INTO fnd_conc_pp_actions

   (

      concurrent_request_id,

      action_type,

      status_s_flag,

      status_w_flag,

      status_f_flag,

      last_update_date,

      last_updated_by,

      creation_date,

      created_by,

      last_update_login,

      completed,

      SEQUENCE,

      argument1,

      argument2,

      argument3,

      argument4,

      argument5,

      argument6,

      ops_instance,

      processor_id

   )

   SELECT   lv_request_id,

            7,

            'Y',

            'Y',

            'N',

            SYSDATE,

            lv_user_id,--fnd_global.user_id

            SYSDATE,

            lv_user_id,--fnd_global.user_id

            lv_login_id,--fnd_global.login_id

            'N',

            1,

            'S',--secure flag

            p_server,    --hidden parameter for SFTP server

            p_username,  --hidden parameter for SFTP username

            p_password,  --hidden parameter for SFTP password

            p_dest_dir,  --hidden parameter for SFTP destination dir

            '22',--constant

            -1,

            lv_processor_id --derived above

   FROM     DUAL;

   COMMIT;

Hope this helps you all

Cheers
A