PeopleCode to Save File Attachment from any Server to FTP or Database Server 

(does not Browse for File on the PC – just takes it from a variable name – use this if the files are already known or created by the program where no user input is required)

/* This code saves file from any server (NT/Unix) to FTP or Database Server using PutAttachment command*/
/* Unlike AddAttachment, PutAttachment does not open the file browse window for user to select one file to load */

Local File &MYFILE;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME, &fname, &fname_provided, &Date_Time;
/*Local array of string &FNAMES;*/
/*&FNAMES = FindFiles(RUN_CNTL_HR.WHERE_CLAUSE, %FilePath_Absolute); /* Use for Multiple Files – Store paths of all files on a folder to an Array */
/*While &FNAMES.Len > 0*/

&URL_ID = “RECORD://HRS_ATTACHMENTS”;
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/”;*/
/*&URL_ID = “URL.HRS_INT_ATCH”;*/
/*&URL_ID = “ftp://user:password@mycompany.com”;*/

&fname_provided = RUN_CNTL_HR.WHERE_CLAUSE;
/*&fname_provided = “/sw/app/psoft/psofthr/OSGN.pdf”; — if the app server is on Unix – file on Unix also*/
/*&fname_provided = “\\sy-hr9upg\hrdev\ps\OSGN.pdf”; — if the app server is on NT – file on NT also – follow UNC naming convention*/
/* if the app server is on unix and the files are on NT – write an app engine program, start it from a page button, but force it to run on NT */

&MYFILE = GetFile(&fname_provided, “R”, %FilePath_Absolute); /* get the file */
&Date_Time = DateTimeToLocalizedString(%Datetime, “yyyyMMdd_HHmmss”);
&ATTACHUSERFILE = &MYFILE.Name; /* Actual File Name with path */
&fname = Substitute(&MYFILE.Name, “:\”, “_”);
&fname = Substitute(&fname, “\”, “_”);
&fname = Substitute(&fname, “/”, “_”);
&fname = Substitute(&fname, ” “, “_”);
&ATTACHSYSFILENAME = Replace(&fname, Len(&fname) – 3, 0, “_” | &Date_Time | “_” | &file_no); /* Unique name – same name may overwrite old file */
If Exact(Left(&URL_ID, 4), “URL.”) Then
   &return_code = PutAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
Else
   &return_code = PutAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
End-If;

References:
http://www.pscustomizer.com/peoplesoft-examples/attachments/
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/write-file-into-database-using-sqr-2528747
https://forums.oracle.com/forums/thread.jspa?messageID=10320192
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4541 
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcd/chapter.htm?File=tpcd/htm/tpcd11.htm
http://peoplesoftstuff.blogspot.com/2012/07/upload-file-from-local-to-server.html 
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/browse-functionality-on-run-control-page-819632

PeopleCode to Copy/Retrieve file FROM Database to File Server or any LAN area

/* The file will be copied to the /files directory on the App Server */
&file_name = &InOutAttachUserfile;
&temp_dir = “C:\Temp\”;

/* Copy file from database record to the files directory */
&Result = GetAttachment(&URL_ID, &file_name, &temp_dir | &file_name);

References:
http://www.pscustomizer.com/peoplesoft-examples/attachments/
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/write-file-into-database-using-sqr-2528747
https://forums.oracle.com/forums/thread.jspa?messageID=10320192
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4541 
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcd/chapter.htm?File=tpcd/htm/tpcd11.htm
http://peoplesoftstuff.blogspot.com/2012/07/upload-file-from-local-to-server.html 
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/browse-functionality-on-run-control-page-819632

Application Engine PeopleCode to Copy one File to FTP or Database Server

[Purpose: if the application server is on Unix and the files are on NT/LAN – write an app engine program, start it from a page button, etc. on app server (Unix), but force it to run on process scheduler on NT or Windows that has access to LAN]
Local File &MYFILE;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME, &fname, &fname_provided, &Date_Time;
/*Local array of string &FNAMES;*/
/*&FNAMES = FindFiles(RUN_CNTL_HR.WHERE_CLAUSE, %FilePath_Absolute); /* Use for Multiple Files – Store paths of all files on a folder to an Array */
/*While &FNAMES.Len > 0*/

&URL_ID = “RECORD://HRS_ATTACHMENTS”;
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/”;*/
/*&URL_ID = “URL.HRS_INT_ATCH”;*/
/*&URL_ID = “ftp://userid:password@mycompany.com”;*/

/*fname_provided = RUN_CNTL_HR.WHERE_CLAUSE;*/
/*name_provided = “/sw/app/psoft/psofthr/OSGN5.pdf”;*/
/*&fname_provided = “/sw/app/psoft/psofthr/OSGN.pdf”; — if the app server is on Unix – file on Unix also*/
&fname_provided = “\\sy-hr9upg\hrdev\ps\test1\OSGN5.docx”; /* if the app server is on NT – file on NT also – follow UNC naming convention*/
/* if the app server is on unix and the files are on NT – write an app engine program, start it from a page button, but force it to run on NT */

/* get the file */
&MYFILE = GetFile(&fname_provided, “R”, %FilePath_Absolute);

&Date_Time = DateTimeToLocalizedString(%Datetime, “yyyyMMdd_HHmmss”);
&ATTACHUSERFILE = &MYFILE.Name; /* Actual File Name with path */
&fname = Substitute(&MYFILE.Name, “:\”, “_”);
&fname = Substitute(&fname, “\”, “_”);
&fname = Substitute(&fname, “/”, “_”);
&fname = Substitute(&fname, ” “, “_”);
&ATTACHSYSFILENAME = Replace(&fname, Len(&fname) – 3, 0, “_” | &Date_Time | “_” | &file_no); /* Unique name – same name may overwrite old file */
If Exact(Left(&URL_ID, 4), “URL.”) Then
   &return_code = PutAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
Else
   &return_code = PutAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE); /* Upload file */
End-If;

References:
http://www.pscustomizer.com/peoplesoft-examples/attachments/
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/write-file-into-database-using-sqr-2528747
https://forums.oracle.com/forums/thread.jspa?messageID=10320192
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4541 
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcd/chapter.htm?File=tpcd/htm/tpcd11.htm
http://peoplesoftstuff.blogspot.com/2012/07/upload-file-from-local-to-server.html 
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/browse-functionality-on-run-control-page-819632

Application Engine PeopleCode to Copy All or Some Files in a Folder to FTP or Database Server

Local File &MYFILE, &LogFile;
Local array of string &FNAMES;
Local number &file_no;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME, &fname, &Date_Time;
Local Record &DOC_REC;
Local SQL &SQL1;

&LogFile = GetFile(“\\sy-hr9upg\hrdev\ps\test1\q_fileupload_log.log”, “w”, “a”, %FilePath_Absolute);

/* Set Log File */
&FNAMES = FindFiles(“\\sy-hr9upg\hrdev\ps\test1\*.docx”, %FilePath_Absolute);

/* Store paths of all files(pdf) of a folder to an Array */
&LogFile.WriteLine(“No. of files found: “ | &FNAMES.Len);
&file_no = 1;

&URL_ID = “RECORD://HRS_ATTACHMENTS”;
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/”;*/
/*&URL_ID = “ftp://user:password@ftpserver.peoplesoft.com:6000/PATH”;*/
/*&URL_ID = “URL.HRS_INT_ATCH”;*/
/* The following goes to the default folder (default folder assigned to the login user id) on the FTP server – example: /sw/app/psoft/psofthr*/
/*&URL_ID = “ftp://pmilind:password@mycompany.com”;*/
/* If the default folder on FTP server is /sw/app/psoft/psofthr then the following puts it to /sw/app/psoft/psofthr/log */
/*&URL_ID = “ftp://pmilind:password@mycompany.com/log”;*/
/* The following puts the file on the /tmp folder on the system root */
/*&URL_ID = “ftp://pmilind:password@mycompany.com/../../../../tmp”;*/

While &FNAMES.Len > 0
&MYFILE = GetFile(&FNAMES.Shift(), “R”, %FilePath_Absolute);

/* Catch each file */
&LogFile.WriteLine(“File No: “ | &file_no | “, File Name: “ | &MYFILE.Name);

   /* String processing to prepare parameters for putattachment() */
   &Date_Time = DateTimeToLocalizedString(%Datetime, “yyyyMMdd_HHmmss”);
   &ATTACHUSERFILE = &MYFILE.Name;

/* Actual File Name with path */
   &fname = Substitute(&MYFILE.Name, “:\”, “_”);
   &fname = Substitute(&fname, “\”, “_”);
   &fname = Substitute(&fname, “/”, “_”);
   &fname = Substitute(&fname, ” “, “_”);
   &ATTACHSYSFILENAME = Replace(&fname, Len(&fname) – 4, 0, “_” | &Date_Time | “_” | &file_no);

/* Sysfilename for DB purpose ? should be unique – use 4 if extension is 4 chars (docx), else use 3 (pdf) */
&LogFile.WriteLine(“&ATTACHUSERFILE = “ | &ATTACHUSERFILE);
&LogFile.WriteLine(“&ATTACHSYSFILENAME = “ | &ATTACHSYSFILENAME);

If Exact(Left(&URL_ID, 4), “URL.”) Then
   &return_code = PutAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &ATTACHUSERFILE);

 /* Upload each file */
Else
&return_code = PutAttachment(&URL_ID, &ATTACHSYSFILENAME, &ATTACHUSERFILE);

/* Upload each file */
End-If;

/* Note: AddAttachment() is not allowed in AE */
&LogFile.WriteLine(“&return_code = ” | &return_code);
&MYFILE.Close();
&file_no = &file_no + 1;
End-While;

/* Create Step 2 in Application Engine*/
/* Make Sure Step 1 has COMMIT:After Step */
/* Uploaded Files to be deleted from Folder for security reasons */
/* Begin – Delete files from Folder – K.S.Rama Naidu */
/*Local File &MYFILE, &LogFile;
Local array of string &FNAMES;
Local number &file_no;
Local string &ATTACHUSERFILE, &ATTACHSYSFILENAME;*/
&LogFile = GetFile(“C:\temp\q_fileupload_log.log”, “w”, “a”, %FilePath_Absolute);

/* Set Log File */
/* CHANGE ACTUAL PATH IN PRODUCTION – Create a new URL to avoid hardcode – or pass from Run Control Page*/
/*
&FNAMES = FindFiles(URL.NEW_URL, %FilePath_Absolute);
*/
&FNAMES = FindFiles(“\\sy-hr9upg\hrdev\ps\test1\*.docx”, %FilePath_Absolute);
&LogFile.WriteLine(“No. of files found: ” | &FNAMES.Len);
&LogFile.WriteLine(” “);
&file_no = 1;
While &FNAMES.Len > 0
/* Catch each file */
&MYFILE = GetFile(&FNAMES.Shift(), “R”, %FilePath_Absolute);
&LogFile.WriteLine(“File No: ” | &file_no | “, File Name: ” | &MYFILE.Name);

/* DELETE each file */
&MYFILE.Delete();
REM &return_code = DeleteAttachment(URL.NEW_URL, &ATTACHSYSFILENAME);

&LogFile.WriteLine(“&return_code = ” | &return_code);
&LogFile.WriteLine(” “);
&MYFILE.Close();

&file_no = &file_no + 1;
End-While;

/* The above deletes files from source area*/
/* The following deletes file from the FTP or Database Server.
&ATTACHSYSFILENAME = “Test123.pdf”;
If Exact(Left(&URL_ID, 4), “URL.”) Then
&return_code = DeleteAttachment(@(&URL_ID), &ATTACHSYSFILENAME);
Else
&return_code = DeleteAttachment(&URL_ID, &ATTACHSYSFILENAME);
End-If;
*/

/* Use the following code in the main PeopleCode to schedule this app engine directly
&processRqst = CreateProcessRequest();
&processRqst.RunControlID = %Datetime;
&processRqst.ProcessType = “Application Engine”;
&processRqst.ProcessName = “YourAEName”;
&processRqst.RunDateTime = %Datetime;
&processRqst.RunLocation = “PSNT”;
&processRqst.Schedule();

If &processRqst.Status = 0 Then
WinMessage(“Success”, 0);
Else
WinMessage(“Error”, 0);
End-If;

*/

References:
http://www.pscustomizer.com/peoplesoft-examples/attachments/
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/write-file-into-database-using-sqr-2528747
https://forums.oracle.com/forums/thread.jspa?messageID=10320192
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4541
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcd/chapter.htm?File=tpcd/htm/tpcd11.htm
http://peoplesoftstuff.blogspot.com/2012/07/upload-file-from-local-to-server.html
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/browse-functionality-on-run-control-page-819632

Peoplecode to Delete Multiple File Attachments

Local Rowset &rs1;

&URL_ID = “RECORD://HRS_ATTACHMENTS”;

&rs1 = CreateRowset(Record.HRS_ATTACHMENTS);
&rs1.Fill(“where ATTACHSYSFILENAME like :1”, “%OSGN%”);

For &i = 1 To &rs1.ActiveRowCount
&Value1 = &rs1(&i).HRS_ATTACHMENTS.ATTACHSYSFILENAME.Value;
&return_code = DeleteAttachment(&URL_ID, &Value1);
End-For;

References:
http://www.pscustomizer.com/peoplesoft-examples/attachments/
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/write-file-into-database-using-sqr-2528747
https://forums.oracle.com/forums/thread.jspa?messageID=10320192
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcl/book.htm?File=tpcl/htm/tpcl02.htm#H4541
http://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tpcd/chapter.htm?File=tpcd/htm/tpcd11.htm
http://peoplesoftstuff.blogspot.com/2012/07/upload-file-from-local-to-server.html
http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/browse-functionality-on-run-control-page-819632

Traversing Level 0 to Level 3 By PeopleCode

Example code about Traversing Level 0 to Level 3 By PeopleCode. You can copy and paste this code of each event action and change record name and scroll name and filed name.

Reference : http://www.dbtutor.com/2013/06/02/peoplecode-for-traversing-level-0-to-level-3

How to Clear Drop-Down List PeopleCode

How to Clear Drop-Down List PeopleCode: Dynamic DropDown Lists PeopleCode for sort or clear some dropdown list by PeopleCode for each requirement.  You can clear drop-down list of translate values in PeopleCode at rowinit event or field change event.

This peoplecode for clear all translate values of field “WO_WO_TYPE” and select some data for show in drop-down list box by use code where expression and order by  this code

“Where fieldname = ‘WO_WO_TYPE’ AND FIELDVALUE NOT IN ( ‘0’,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’10’,’12′” ORDER BY decode(XLATLONGNAME,’Income’,0,’Value Creation’,’2′ ,’Benchmarking’,’3′ ,’Risk Management’,3 ,’KPI Main’,3,1)

Peoplecode Example

For Example

This PeopleCode for replacing in Rowint in Record field

Example code of dropdown list in row init event of record

FTP a File Using PeopleCode

There is a function to FTP a file on a server using PeopleCode.
Local string &FtpUserID;
Local string &FtpPassword;
Local string &FtpURL;
Function ftp_file;
&FtpUserID = “user_id”;
&FtpPassword = “password”;
&FtpURL = “directory_name/subdirectory_name”;
&targetFile = “File_Name”;
/* Set ftp string */
&Ftp_my_file = “ftp://” | &FtpUserID | “:” | &FtpPassword | “@” | &FtpURL;
/*** It is always a good practice to check to see if local file exists before trying to transfer it ***/
If FileExists(&FILEPATHNAME, %FilePath_Absolute) Then
/*** Sending file… FILEFATHNAME is the path and the name of the file, while targetFile can be used to change the name of the file if need be ***/
&RetCode = PutAttachment(&Ftp_my_file, &targetFile, &FILEPATHNAME);
/* make sure to check the retcode by the built-in PeopleCode function PutAttachment to get the result of your ftp attempt */
Evaluate &RetCode
When %Attachment_Success
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer succeeded”);
Break;
When %Attachment_Failed
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer failed”);
Break;
When-Other
MessageBox(0, “File Attachment Status”, 0, 0, “File transfer failed, Return Code is ” | &RetCode | “. Please refer to PeopleCode Reference for Return Code description. Search for AddAttachment function.”);
End-Evaluate;
Else
MessageBox(0, “File Status”, 0, 0, “File ” | &FILEPATHNAME | ” doesn’t exist. FTP transfer Failed.”);
End-If;
End-Function;

credit : (borrowed from Lepa @ CompShack).