Following script I have use for SFTP with Oracle R12, this needs to be modified for General Use.
I have used this script for following 2 purposes.
a) AR: Getting Lockbox file from Chase Bank for Lockbox File Processing.
b) AP: Getting invoice file from FedEx to import the invoices into payables.
#!/usr/bin/perl use warnings; use strict; use Switch; use Net::SFTP::Foreign; use IO::Pty; use Fcntl ':mode'; use Cwd; use File::Copy "cp"; ################################################################################### # # Program Name: kfisftppw.plx # # Generic sftp program using username and password authentication # #################################################################################### print "\nStarting execution of program kfisftppw.plx...\n\n"; # Ten parameters should be passed to this script. The first four are passed by the # Oracle Applications concurrent manager, the following six are passed as # parameters to the concurrent request. my $parmcnt = $#ARGV + 1; if ($parmcnt != 17) {die "\nUSAGE ERROR: Expecting 7 parameters but received $parmcnt\n" . " Parameter1: sftp program uses user identifier\n" . " Parameter2: sftp remote user identifier\n" . " Parameter3: sftp remote host ip address\n" . " Parameter4: sftp remote host port number\n" . " Parameter5: sftp mode, either put or get\n" . " Parameter6: local directory\n" . " Parameter7: remote directory\n" . " Parameter8: local archive remote directory\n" . " Parameter9: file name\n" . " Parameter10: sftp remote user password\n" . " Parameter11: Re Process Without SFTP Yes or No\n\n" }; my $appsident = $ARGV[0]; my $fnduserid = $ARGV[1]; my $fndusrname = $ARGV[2]; my $reqid = $ARGV[3]; my $proguses = $ARGV[4]; my $destuser = $ARGV[5]; my $hostip = $ARGV[6]; my $portno = $ARGV[7]; my $sftpmode = $ARGV[8]; my $localdir = $ARGV[9]; my $remotedir = $ARGV[10]; my $locArchdir = $ARGV[11]; my $filename = $ARGV[12]; my $destpasswd = $ARGV[13]; my $toUserEmail = $ARGV[14]; my $toITEmail = $ARGV[15]; my $reprocessflag = $ARGV[16]; print "Remote User Identifier = $proguses\n"; print "Remote User Identifier = $destuser\n"; print "Remote Server Address = $hostip\n"; print "Remote Port Number = $portno\n"; print "Processing Mode = $sftpmode\n"; print "Local Directory = $localdir\n"; print "Remote Directory = $remotedir\n"; print "Local Archive Dir = $locArchdir\n"; print "File Name = $filename\n"; print "User Email Address = $toUserEmail\n"; print "IT Email Address = $toITEmail\n"; print "SFTP Reprocess Flag = $reprocessflag\n"; #print "Yogesh Test Apps password appsident = $appsident\n"; #print "Yogesh Test Apps password fnduserid = $fnduserid\n"; #print "Yogesh Test Apps password fndusrname = $fndusrname\n"; # Check for files containing userid and password needed to # connect at the sftp destination #my $userfile = $ENV{"HOME"} . "/.ssh/." . $destuser . "user"; #my $pwfile = $ENV{"HOME"} . "/.ssh/." . $destuser . "pw"; # For testing only, we do not want this information in the concurrent request log file # print "User Name File = $userfile\n"; # print "User Password File = $pwfile\n"; my $user = $destuser ; my $passwd = $destpasswd; my $successStatus = "Y"; #print "successStatus Flag 1 = $successStatus\n"; #if ( -e $userfile ) { chomp($user = `cat $userfile`) } #else { die "\nERROR - Remote Username File $userfile does not exist\n" }; #if ( -e $pwfile ) { chomp($passwd = `cat $pwfile`) } #else { die "\nERROR - Remote Username File $pwfile does not exist\n" }; print "Remote sftp User = $user\n"; # For testing only, we do not want to print the password in the concurrent request log file # print "Password = $passwd\n"; # Make sure the local directory is valid if (! -d $localdir) {die "\nERROR - Local Directory $localdir does not exist\n"}; chdir $localdir or die "\nERROR - Could not change to Local Directory $localdir\n"; if ( $reprocessflag eq "NO" ) { # Initiate the sftp session my $sftp = Net::SFTP::Foreign->new(host=>"$hostip",port=>$portno, user=>"$user",password=>"$passwd",timeout=>60, asks_for_username_at_login=>0,stderr_discard => 1); $sftp->die_on_error("\nUnable to establish SFTP connection: " . $sftp->error); # Change to the remote directory if one is passed as a parameter if ( $remotedir ne "" ) { $sftp->setcwd("$remotedir") or die "\nUnable to change to remote dir $remotedir: " . $sftp->error; }; # Execute the sftp get or put command $sftpmode = 'm' . $sftpmode; #$sftp->$sftpmode($filename) or warn "\nsftp $sftpmode failed or no files to process: " . $sftp->error; if($sftp->$sftpmode($filename)){ $successStatus = "Y"; } else{ $successStatus = "N"; warn "\nsftp $sftpmode failed or no files to process: " . $sftp->error; } #print "successStatus Flag 2 = $successStatus\n"; undef $sftp; }; # Copy the files to Archive Directory if ( $locArchdir ne "" and $successStatus eq "Y" ) { if (! -d $locArchdir){warn "\nERROR - Local Directory $locArchdir does not exist\n"}; # Make sure the Archive directory is valid chdir $locArchdir or warn "\nERROR - Could not change to Local Archive Directory $locArchdir\n"; my $dir = $localdir."/".$filename; print "\nLocal Directory with Filename $dir \n"; my $renfilename; my $fedfilename = $localdir."/".'fedex_ap.csv'; my @files = glob( $dir ); foreach (@files ){ #print $_ . "\n"; # Change the file permission of copied files into local directory chmod 0666, $_ or warn "\nERROR - Could not change file permissions to 666 of copied files at directory $locArchdir\n"; cp($_,$locArchdir) or warn "File Copy failed to Archive Directory: $!";; # Change the file name to remove time stamp so user can enter right file name while submitting lockbox job if ( $proguses eq "AR_LOCKBOX_IN" ) { $renfilename = $_; substr $renfilename,-8,4,""; system("mv $_ $renfilename"); # print "\nYogesh Testing $renfilename and $_"; }; # Change the file name to remove time stamp so user can enter right file name while submitting lockbox job if ( $proguses eq "AP_FEDEX_EDI_IN" ) { # print "\nYogesh Testing I am Here 2 $fedfilename and $_ \n"; system("mv $_ $fedfilename"); system("CONCSUB $appsident SQLAP 'Payables Manager - gmail Inc' $fndusrname WAIT=15 CONCURRENT XKFI KFI_FEDEX_LOAD_CSV PROGRAM_NAME=''"); # print "\nYogesh Testing $fedfilename and $_"; }; } }; #my $cwd = getcwd(); #print " Current working directory $cwd "; # Code to send email upon success or even failure my $subFileName = ""; my $subject =""; my $message=""; my $to = $toUserEmail; my $from = 'OracleEBS@gmail.com'; if ( $proguses eq "AP_FEDEX_EDI_IN" ) { $subFileName = "FedEx Invoices"; }elsif( $proguses eq "AR_LOCKBOX_IN" ){ $subFileName = "Lockbox"; } if($successStatus eq "Y" ){ $subject = "$subFileName New File - Extraction Notification"; $message = "Dear Team, $subFileName file have been copied from FTP location to local directory for further processing. Sincerely, Oracle Support Team"; # To Print File Name in the log file if ( $remotedir eq "" ) { $remotedir = "/" }; print "\nFile $filename was"; switch ($sftpmode) { case "mput" { print " sent to " } case "mget" { print " retrieved from " } }; print "directory $remotedir on server $hostip.\n"; print "File $filename can be found in directory $localdir on the local server.\n\n"; } elsif($successStatus eq "N"){ $subject = "Error Notification - $subFileName New File"; $message = "Dear Team, Some exception has occured while moving $subFileName file to local directory. Please contact Oracle Support Team if you feel this is an error. Sincerely, Oracle Support Team"; }else{ $to = $toITEmail; $subject = "Error Notification - $subFileName New File"; $message = "Dear IT Team, Some exception has occured while moving $subFileName file to local directory. This could be connectivity issue. Please check the log file for further investigation. Sincerely, Oracle Support Team"; } open(MAIL, "|/usr/sbin/sendmail -t"); # Email Header print MAIL "To: $to\n"; print MAIL "From: $from\n"; print MAIL "Subject: $subject\n\n"; # Email Body print MAIL $message; close(MAIL); print "\nNotification Email Sent Successfully\n"; #my $to = 'xyx@gmail.com,abc@gmail.com'; #my $from = 'OracleEBS@gmail.com'; #my $subject = 'Test Email'; #my $message = 'This is test email sent by Perl Script'; #open(MAIL, "|/usr/sbin/sendmail -t"); # Email Header #print MAIL "To: $to\n"; #print MAIL "From: $from\n"; #print MAIL "Subject: $subject\n\n"; # Email Body #print MAIL $message; #close(MAIL); #print "Email Sent Successfully\n";
Thanks & Regards
Yogesh Pachpute