----------------Beginning of Preview---------------------------------------------------------------
Simple Example:Create an Application Engine with just one Step in main and one PeopleCode Action in that step. Copy the following PeopleCode. Replace the file name and the path to any valid file on your system -- on Unix or NT server where you intend to run the Application Engine.
/**********Start of PeopleCode*******************/
/* This code saves file from any server (NT/Unix) to 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;
&URL_ID = "RECORD://HRS_ATTACHMENTS";
&ATTACHUSERFILE = "/sw/app/psoft/psofthr/user/01161586201303283250187000.xml"; /* Actual File Name with path - shown here for UNIX, on NT use UNC naming convention example: \\server1\test1\test2\file.xml*/
&ATTACHSYSFILENAME = "01161586201303283250187000.xml"; /*This has to be unique file name - else will replace any previously loaded file - you may make this unique by appending something like data/time/etc*/
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;
/**********End of PeopleCode*******************/
Simple Test:
Step1: select * from PS_HRS_ATTACHMENTS;
No values
Step2: Run the Above Application Engine:
Step3: select * from PS_HRS_ATTACHMENTS;
01161586201303283250187000.xml 0 1 8214 .. BLOB NOT RETRIEVED
----------------End of Preview---------------------------------------------------------------
Note: Read the actual process that uses the delivered records FILE_ATTACH_WRK, FILE_ATTDET_SBR, etc as indicated in
http://www.pscustomizer.com/peoplesoft-examples/attachments/
The following peoplecode may just be used as a reference - I have actually tested them all and copied/pasted the code below (just modified the server names/etc) - refer this only if you have issues with the actual peoplecode after you have done what the above link says.
1) PeopleCode To Browse for File and Save attachments To FTP or Database Server
2) 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)
3) PeopleCode to Copy/Retrieve file "FROM" Database to File Server or any LAN area
4) 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]
5) Application Engine PeopleCode to copy All or Some Files in a Folder to FTP or Database Server
6) Peoplecode to Delete Multiple File Attachments
1) PeopleCode To Browse for File and Save attachments To FTP or Database Server
/* Using add_attachment if required- only using AddAttachment
Declare Function add_attachment PeopleCode FILE_ATTACH_WRK.ATTACHADD FieldChange;
add_attachment(FUNCLIB_UTIL.FTPSITE, &FILEEXTENSION, &SUBDIRECTORY, &FILESIZE, False, &recname, FUNCLIB_UTIL.ATTACHSYSFILENAME, FUNCLIB_UTIL.ATTACHUSERFILE, &MESSAGE_LVL, &RETCODE);
*/
/* All the following assignments to &URL_ID work - I have commented off all except one in each case, because only one assignment is required */
/*&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";*/
If Exact(Left(&URL_ID, 4), "URL.") Then
&RETCODE = AddAttachment(@(&URL_ID), &ATTACHSYSFILENAME, &FILEEXTENSION, &ATTACHUSERFILE, &FILESIZE);
Else
&RETCODE = AddAttachment(&URL_ID, &ATTACHSYSFILENAME, &FILEEXTENSION, &ATTACHUSERFILE, &FILESIZE);
End-If;
2) 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;
/* 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;
*/
3) 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);
4) 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 */
&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;
/* 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;
*/
5) 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;
*/
6) 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
