Quick PS Util - Display Table data in order of Column Name

When using Toad or other tools that displays results in a grid that can be scrolled from left to right, and there are many columns, I usually have to to go back and forth a few times to find a column. Some of the solutions to this --

1. creataing a function returns a sql that displays fields in the order of filed name:

CREATE OR REPLACE function  fo(table_in IN varchar2)RETURN varchar2
IS
  sql1      varchar2(6000);
  cnt       integer;
  rec_name  varchar2(30);

  cursor c1 is
     select column_name as fieldname from all_tab_columns where TABLE_NAME=rec_name order by column_name;

BEGIN

 cnt      :=0; 
 sql1     :='select ';
 rec_name :='PS_'||upper(table_in);

 FOR zz IN c1 LOOP
    sql1:=sql1 || zz.fieldname||',';
    cnt:=cnt+1;
 END LOOP;    

 if cnt = 0 then

    rec_name := upper(table_in);

    FOR zz IN c1 LOOP
        sql1:=sql1 || zz.fieldname||',';
        cnt:=cnt+1;
    END LOOP;    
 end if;

 if cnt = 0  then
    return table_in ||' columns not found';
 end if;

 sql1 := sql1 || '''---'' from ' || rec_name ;

 RETURN sql1 ;

EXCEPTION
 WHEN OTHERS THEN
    raise_application_error(-20001,'Errro - '||SQLCODE||' -ERROR- '||SQLERRM);

END;
/

show error function fo

Ex:

• select fo('installation') from dual -- default to 'PS_'
• select fo('psrecdefn') from dual
  
• select fo('all_objects') from dual

Change Peoplesoft FTP to SFTP - Part 4

A sample script that ties up things using the information discussed.
1. minimize code changes to delivered script & call custom code in separate script

:::::

cd ${PS_HOME}/bin

# source my sftp script
. my_sftp_script

ftp_cmd_file=$1
::::::
::::::
echo "about to submit ftp -vin $1"
#$FTP_CMD -vin < $1    
my_sftp_function

break
;;

esac

::::::::

2. my_sftp_script:

#####################################################
#
# My SFTP function
#
#####################################################
function my_sftp_function
{

#singal handler
trap "cleanup" 0
trap "echo Cancelled; cleanup;" 1 2 3 15

err=0
feedback=1

SFTP_CMD="/usr/bin/sftp"
sftp_cmd="h_sftp_cmd."$$
sftp_tmp="h_sftp_tmp."$$
sftp_log="h_sftp_log."$$
dev=me@some.place.com
user=/somehost/somedir/user_list

##### sftp messages           --change text as needed
SFTP_LOGIN_ERR="Permission denied (publickey,password,keyboard-interactive)"
SFTP_NO_FILE="No such file or directory"

##### ftp messages & codes    --change text as needed
FTP_NO_FILE="A file or directory in the path name does not exist."
FTP_LOGIN_ERR="Login incorrect."
FTP_MAX_SIZE="Exceeded storage allocation"

FTP_DEL_NOFILE_CD=532
FTP_LOGIN_ERR_CD=530
FTP_MAX_SIZE_CD=552
FTP_GET_NOFILE_CD=550

FTP_PUT_OK="226 Transfer complete."
FTP_GET_OK="226 Transfer complete."
FTP_DEL_OK="250 DELE command successful."

###############
# get ftp xfer command, remote dir, remote file name

cmd=''
r_file=`grep ^put $ftp_cmd_file | awk '{print $3}' | tr '\"' '\0'`

if [ ${r_file:-none} == 'none' ]; then

r_file=`grep ^get $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`

if [ ${r_file:-none} == 'none' ]; then

r_file=`grep ^delete $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`

if [ ${r_file:-none} != 'none' ]; then
  cmd=delete
fi
else
cmd=get
fi
else
cmd=put
fi

r_dir=`grep ^cd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`

#echo "!sleep 500" > $sftp_cmd

# convert ftp to sftp commands
grep -e ^lcd -e ^cd -e ^put -e ^delete -e ^get -e ^quit $ftp_cmd_file | sed s/^delete/rm/ | awk '!/^rm/ {print $0} /^rm/ {print $0"\""}'  > $sftp_cmd


echo 'sftp commands---------------'                  > $sftp_log
cat $sftp_cmd                                       >> $sftp_log

echo '\nsftp log--------------------'               >> $sftp_log

# run sftp command batch file
$SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>>$sftp_log 2>&1

err=$?

if [ $err -ne 0 ]; then

grep -qi $SFTP_LOGIN_ERR $sftp_log

if [ $? -eq 0 ]; then
echo $FTP_LOGIN_ERR_CD" "$FTP_LOGIN_ERR
else
grep -qi  $SFTP_NO_FILE $sftp_log

if [ $? -eq 0 ]; then

   if [ $cmd = 'delete' ]; then
       echo $FTP_DEL_NOFILE_CD" "$FTP_NO_FILE
   else
       echo $FTP_GET_NOFILE_CD" "$FTP_NO_FILE
   fi
else

   grep -qi  $FTP_MAX_SIZE $sftp_log

   if [ $? -eq 0 ]; then

       echo $FTP_MAX_SIZE_CD" "$FTP_MAX_SIZE

   fi
fi
fi

else
# check sftp results

echo '\nverifying results------------'            >>$sftp_log

chk_results

err=$?

if [ $err -eq 0 ]; then

if [ $cmd = 'get' ]; then
   echo $FTP_GET_OK
else
   if [ $cmd = 'put' ]; then
       echo $FTP_PUT_OK
   else
       echo $FTP_DEL_OK
   fi
fi
fi
fi

# send feedback
if [ $err -ne 0 ] && [ $feedback -eq 1 ]; then

echo $cmd' failed.'                                >> $sftp_log                     
                                      
echo '\nftpunx log------------------'              >> $sftp_log
echo ${ftp_cmd_file%.*}".log:"                     >> $sftp_log
cat `echo ${ftp_cmd_file%.*}".log"`                >> $sftp_log
                                      
echo '\nEnd: '`date`                               >> $sftp_log

if [ -f  $user ]; then
mailx -s"My SFTP failed" `cat $user`             < $sftp_log     else                                                          mailx -s"My SFTP failed" $dev                    < $sftp_log    fi  fi  return $err  }  ################################################ # # check SFTP results # ################################################ function chk_results {  case $cmd in        put | get)         # compare file sizes                  echo "         `grep ^cd $ftp_cmd_file`         ls -l "$r_file"          quit         " > $sftp_cmd

$SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_tmp 2>&1

if [ $? -ne 0 ]; then
   return 1
fi

# get temp local file name

if [ $cmd = 'put' ]; then
  l_file=`grep ^lcd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`/`grep ^put $ftp_cmd_file | awk '{print $2}' | tr '\"' '\0'`
else
  l_file=`grep ^lcd $ftp_cmd_file | awk 'BEGIN{var=""} {if ($2!="/") var=var"/"$2; else var=var$2; } END{print var}' | sed 's/\/\//\//g'`/`grep ^get $ftp_cmd_file | awk '{print $3}' | tr '\"' '\0'`
fi

l_file_size=`ls -l $l_file | awk '{print $5}'`
r_file_size=`grep -v ^sftp $sftp_tmp | grep $r_file | awk '{print $5}'`

if [ $l_file_size -ne $r_file_size ]; then

   echo 'file size different:'                                 >> $sftp_log
   echo 'local  file size='$l_file_size' ('$l_file')'          >> $sftp_log  
   echo 'remote file size='$r_file_size' ('$r_dir'/'$r_file')' >> $sftp_log

   return 1
fi

break
;;

delete)

# DELETE - verify file does not exist; use subshell so sftp does not error off

(
echo "
`grep ^cd $ftp_cmd_file`
ls -l "$r_file"
quit
"
) | $SFTP_CMD `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_tmp 2>&1

if [ $? -ne 0 ]; then
   return 1
fi


grep -q "$2 not found" $sftp_tmp

if [ $? -ne 0 ]; then
   cat $sftp_tmp                                               >> $sftp_log

   return 1
fi

break
;;
esac

return 0
}


function cleanup
{
rm $sftp_tmp 2>/dev/null
rm $sftp_cmd 2>/dev/null
rm $sftp_log 2>/dev/null

}

Navigation to related pages:

Page Used to Define an FTP Server for File Attachments

Page Name	Object Name	Navigation	Usage
URL Maintenance	URL_TABLE	PeopleTools, Utilities, Administration, URLs	Specify an FTP server for storing file

Page used to Set Up File Attachment Servers

Page Name	Object Name	Navigation	Usage
Maintain File Att Servers	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers	Used to set up the FTP server location for PDF and XML file attachments which are included with notifications.

Pages Used to Set Up Attachments for Transactions

Page Name	Object Name	Navigation	Usage
Maintain File Att Servers	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers	Identify servers on which to store attachments.
Maintain Component Subdirs	PV_ATT_SRV_ADMIN	Set Up Financials/Supply Chain, Utilities, Administer File Attachments, Administer Attachment Servers, Maintain Component Subdirs	Define component names and paths where the attachments are stored.
Vendor Address	VNDR_ADDRESS	eProcurement, Procurement Application Admin, Maintain Vendors, Vendor Profile, Address	Define vendor email addresses for sending attachments.
URL Maintenance	URL_TABLE	eProcurement, Procurement Application Admin, Maintain Overall System Options, Maintain FTP Server Location	Requires end users to enter the file location manually for attachments (not the recommended method). Set up a location for storing attachments. This is usually an FTP server. Add a subfolder named PV to the root directory. Give read and write privileges to anonymous users or any users that are specified in the FTP string. Create a URL identifier that is named PV for the FTP location for PeopleSoft eProcurement attachments, and specify the FTP server address. The FTP root location must contain a subdirectory that is named PV.

Change Peoplesoft FTP to SFTP - Part3

Change Peoplesoft FTP to SFTP - Part 3

Because FTP is like any 3rd party application to PS, and it generally passes a return code of zero in the non-interactive session, it is not useful in determining a Success or Fail status. So PS is using the session message that is captured in the log file and filtering the FTP status code. A list of status code I can find that is used by PS for this :

226 - Transfer complete.
250 - DELE command successful.
532 - A file or directory in the path name does not exist
550 - A file or directory in the path name does not exist
552 - Exceeded storage allocation

The following considerations should be taken in regards to the wide range of systems and implementation of SFTP executables:

• The only way I found that can consistently returns a error status code is the "-b" batch option. If you use HERE document or subshell-echo it returns zero even if there are errors.
• The FTP status code should be standard, but the message text can vary per system. SFTP does not have a standard status code (on our system), and message text can also vary by systems.

So analyze and thoroughly test the code, look for variations in message text and make necessary adjustments.

The Peoplecode statement for adding/loading a file is like:


The function (AddAttachment) calls ExecutePutAttachment API which examines the log file (ftp????_?????.log) for FTP status code then sets RETCODE as follows:

System Variable for &RETCODE	Value	FTP Code	Comments
%Attachment_Success	0
%Attachment_Failed	1
%Attachment_Cancelled	2		user hit “CANCEL” instead of “Upload”
%Attachment_FileTransferFailed	3
%Attachment_NoDiskSpaceAppServ	4		N/A for FTP
%Attachment_NoDiskSpaceWebServ	5		N/A for FTP
%Attachment_FileExceedsMaxSize	6	552
%Attachment_DestSystNotFound	7
%Attachment_DestSysFailedLogin	8	530	sftp cannot authenticate
%Attachment_FileNotFound	9	550	user “Upload” a 0-byte file; “View” file does not exist
%Attachment_DeleteFailed	10	552	user hit “delete” but file does not exist
%Attachment_NoFileName	11		user hit “Upload” without selecting a file
%Attachment_FileNameTooLong	12

You can see not all status codes are related to FTP. This is due to the the fact only a portion of the transfer process utilizes ftp. Between Browser and Web Server, Web Server & App Server, App Server & DB Server FTP is not involved. I tried to test as many FTP codes as I can, these are the ones I see PS reports off the log file. For ex. if you set &MESSAGE_LVL (in FILE_ATTACH_WRK peoplecode) to a value of 1 or 2, you will see a detailed message box after each operation. This is the delivered code:

&RETCODE = AddAttachment(&URL_ID, &ATTACHSYSFILENAME, &FILEEXTENSION, &ATTACHUSERFILE, &FILESIZE);
:::::::::::::::::::::::::::::::::::::
If &MESSAGE_LVL = 1 Or
&MESSAGE_LVL = 2 Then
If (&RETCODE = %Attachment_Failed) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 2, "AddAttachment failed");
End-If;
:::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::
If (&RETCODE = %Attachment_DestSysFailedLogin) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 9, "AddAttachment failed: Unable to login into destination system for ftp");
End-If;
If (&RETCODE = %Attachment_FileNotFound) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 29, "The file was not found so the operation could not be completed.");
End-If;
/* Error Message for Invalid file or No file name */
If (&RETCODE = %Attachment_NoFileName) Then
MessageBox(0, MsgGetText(137, 1, "File Attachment Status"), 137, 38, "AddAttachment failed: No File Name Specified.");
End-If;
::::::::::::::::::::::::::::::

The API AddAttachment will return zero if it sees this FTP session message in the log file:

226 Transfer complete.

so the SFTP will simply run:

echo "226 Transfer complete"

after it verifies a PUT operation is successful. Similarly, DeleteAttachment API is looking for:

250 DELE command successful

Next is a script that ties everything together.

Change Peoplesoft FTP to SFTP - Part 2 | Change Peoplesoft FTP to SFTP - Part4

Change Peoplesoft FTP to SFTP - Part 2

In the PS generated script, ftp?????_????.txt, it stores the host, login, cd, lcd, get/put/delete & quit commands. A single file is processed each time. Prior to doing sftp, the correct public/private keys need to be generated, known_host and authorized_keys must be set up for the ftp use account. Converting the script to run sftp is straightforward, in 2 steps:

1. grep -e ^lcd -e ^cd -e ^put -e ^delete -e ^get -e ^quit $ftp_cmd_file | sed s/^delete/rm/ | awk '!/^rm/ {print $0} /^rm/ {print $0"\""}' > $sftp_cmd

This extracts all ftp commands, excluding host and login info, and stores them into a temp sftp script. The ftp "delete" command is replaced by sftp "rm" command. In addition, for DELETE operation, PS leaves off a closing ", so it looks like this

delete "XXXXXX.YYY

FTP does not complain it, but SFTP is more strict in syntax and will cause an error. The command changes it to

rm "XXXXXX.YYY"

For PUT operation, PS inserted a "mkdir" command. This will cause SFTP to error in batch mode. So this command is not picked up by the script, just verify the directory exists beforehand.

2. $SFTP_CMD -b $sftp_cmd `grep ^user $ftp_cmd_file|awk '{print $2}'`@`grep ^open $ftp_cmd_file|awk '{print $2}'` 1>$sftp_log 2>&1
($SFTP_CMD = sftp executable)

This executes SFTP in batch mode, with host name and login info from the original ftp script. Password is not needed, as it will be authenticated by Public Key.

I embedded a sleep command in SFTP to get a view at the process tree for this

• 24781020 - An App Server instance handles the PS connection and peforms

• 15798308 - App Server instances spwans a shell & executes ftpunx script

• 19480650 - ftpunx script runs sftp command

• 14291166 - sftp run ssh to handle file transfer

• 2396354 - ssh exeutes a OS command

This is all it takes to transfer a file using secured FTP with sftp. But this is just the beginning, to fully integrate with PS, the results must be "relayed" back to PS . This is done through examination of the SFTP log file. The technique I used is detailed in the next part.

Change Peoplesoft FTP to SFTP - Part 1 | Change Peoplesoft FTP to SFTP - Part 3

Peoplesoft Notify Address

Application Package Peoplecode:
PT_WF_NOTIFICATION.Notification.OnExecute

method Send

/* SendMail(0, &strEmailCC, "", &strEmailBCC, &Subject, &Message, &strFileNames, &strFileTitles, &tempEmailReplyTo, ";", &ContentType);*/
SendMail(0, &strEmailTo, &strEmailCC, &strEmailBCC, &Subject, &Message, "/path/path/filename.ext", "xxx.txt", "From_Notify@rver.xx.yy.zz", ";", &ContentType, &tempEmailReplyTo, "sender@srvr1.xx.yy.zz");

Further reading:

SMTP
Internet Messaging protocols (SMTP,POP3 and IMAP4)
SPAM Filtering