Extracting file details from unix into peoplesoft

There are some cases where we would like to get the File details like timestamp etc from unix server into peoplesoft.

There are different approaches that we can follow to get file details from unix file server.

1. Use Shell script and write file attributes like owner,filename,lastupddttm etc to another file and then
    call this script from peoplecode Exec function.
2. User shell script and write file attributes to database record and then call this record from peoplecode.
3. Use Java language file class concept. Like instantiate the Java file object and then call the javamethod from   peoplecode to get the file attributes.

Let us see the first approach.

1. Use Shell script and write file attributes like owner,filename,lastupddttm etc to another file and then
    call this script from peoplecode Exec function.
 a) Create a shell script and execute from server to check the correctness of the script.
     pseudocode:
     Get the file details using ls -ltr command and store the output to variable
     Write these variables to a file.
b)  since we need file attributes of a particular file from a particular env, pass the filename and env as input.
c)  Use exec command from peoplecode and execute the script.
One challenge is in getting the file datetime values. In unix, ls -ltr will give file datetime till hours and mins but not secs.
Example: Abc.txt  Jan 14 5:29 but there are chances file might get overriden during the same time like 5:29 14 secs might get overriden at 5:29 42 secs. We cannnot find this using the ls -ltr command.  
To overcome this , use the perl script which will give the time till secs.

Thus we can get the file attributes from the above steps. Hope the info is useful.

Preserve case parameter in addattachment function

Below post is helpful to those who have encountered an issue with the file extension getting converted to lower case  instead of retain the same.

We use add_attachment(list_of_param....) to upload files to the server.
Inside the Add_attachment(list_of_param....) function there is a builtin library function addattachment(list_of_param....) which actually uploads the file in the server.

There is one parameter called preserve case which helps to retain the extension which user had uploaded.
By default, it is set to false.
That means if user uploaded "Test_File.TXT" , the file name on the server will be "Test_File.txt".

There could some cases where you feel the extension of the file should not change.
In that case , change the value to 'true'.

AddAttachment(URLDestination, DirAndFilename, FileType, UserFile[, MaxSize]                                                                     [, PreserveCase, UploadPageTitle])

Above syntax is from tools version 8.49

Using Dynamic View

Dynamic View:
'View' is nothing but a static sql that pulls data from atleast one record.
'Dynamic View' enables to pass values dynamically ( at runtime ) to the where clause.
Example:
Lets think of a page which has country and state fields.
RECA.COUNTRY -->RECORD.FIELD
RECA.STATE -->RECORD.FIELD

The state field prompt should show the list of states for the country selected in the Country field prompt.
That means the value of the state field depends on the value of the country field.
In this case , we can use dynamic view for the state field ( STATE ).
PS_STATE_DVW -- This should contain STATE as a key field and listbox item and leave the SQL definition as blank and Save.

Setting Record field properties in App designer.

Now add this Dynamic view as prompt to the record field STATE .

Until now , we have only defined that the STATE record will use the PS_STATE_DVW as Dynamic view. But we have not injected the SQL to this dynamic view to pull the required data.

Below is the code, which builds the SQL and attaches to the SQL Editor of the Dynamic view.

Now that the code is ready , we need to know where exactly to place this code? Since the user selects the 'Country value' from the country field , in the COUNTRY field , field change event we can place the below code.

/** Peoplecode Begin **/
/** This is the SQL string which is used to pull the states for a given country **/
&str = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = (:1) ";
/** ExpandSQLBinds will expand the &str and inserts the values into bind param **/
/** &sql_text_defn = "select STATE from PS_CUST_CNTRY_TBL where COUNTRY = 'IND'" assuming 'IND' is selected on the page. **/
&sql_text_defn = ExpandSQLBinds(&str, RECA.COUNTRY.value);
At this stage the SQL string is ready with its dynamic value (country).
Now, we need to pass this SQL string has to be injected to the Dynamic view SQL Text definition.
/** Passing the SQL string to the Dynamic view **/
RECA.STATE.sqltext = &sql_text_defn;

/*** Peoplecode End ***/
In the PIA, now once we select 'IND' for country field and select on STATE prompt, all states for given country will be listed.
This completes the usage of Dynamic View.

Add Attachment

Uploading files through Add button at level0 and showing the uploaded files onto a grid.
Main records:
Transaction Record at Level 0 : PS_REC_TRANS.
Transaction Record at Level 1: PS_REC_UPLD
Derived Work Record: FILE_ATTACH_WRK or custom record which is clone of the FILE_ATTACH_WRK

Details:
-->Suppose that we have a transaction record PS_REC_TRANS and TRANS_KEY is a Key.
-->Since we are showing uploaded files in the grid, the level1 record (PS_REC_UPLD) should have 2 keys (TRANS_KEY and ATTACHSYSUSERFILE).
-->Now we need to have a derived work record that holds the buttons 'Add Attachment/Delete Attachment/View Attachment'.
There is a delivered record (FILE_ATTACH_WRK) but PeopleSoft recommends to use own custom record which is a copy of delivered record. This recommendation is made so that in future if any changes are done it should not impact the current functionality.

Designing the page:
Add PS_REC_TRANS at level0.
Drag and drop the AddAttachment button onto the page. (level 0).
Optional: Mostly we would like to see the uploaded file next to AddAttachmet button. To accomplish this create a derived work record that holds the ATTACHUSERFILE field.
Insert grid.
Drag and drop the record PS_REC_UPLD onto the grid.
Also include the buttons Delete Attachment and View Attachment onto the grid.
Save the Page.
We need to add peopleocode to the buttons to implement the attachment functionality.
Better if we use Component record field peoplecode, as it will not impact any other comoponent.
Now consider the 'Add Attachment ' Button. Once the user clicks on this button file should be uploaded to the server and then after successful upload filename should appear in the level1 grid.
To make this happen, Declare the function Add_attachment and then call the function. There will be a status field present in the function which will let us know the attachment if failure / success.
Call the level1 rowset and insert the file data. Make sure that DoSave() method to be called.
I will be providing the code in the next post.

Grids

Some pointers using grid.
-> Derived work record cannot be used as main record in the grid.
-> Usually we use views to populate data in the grid.
you can nest a grid in a scroll area, you cannot nest a scroll area in a grid or a
grid in another grid.

Creating Records in PeopleSoft: SQL views

A SQL view is not a Physical SQL table in the database. It represents the data that is present in different physical SQL tables. Thus no DML operations can be performed on a view.
If you try to create a View with DML operation say INSERT statement and try to build the SQL View, it will generate an Error message "ORA-00928: missing SELECT keyword".

To create a View, click on File --> New and select the Record from the definition list.
Insert the fields that you want to see into the record definition.
Click on record Type Tab and select Record Type as 'SQL View'.
Now when you try to click to open SQL Editor, it will ask you to first save the record definition.
Now its important to make sure whether the view you are creating is a Non-Standard SQL table name or not. Generally, all the records created in the App Designer will be prefixed by default with "PS_". So If you create a record with name 'TEST_VW', it will be created as PS_TEST_VW. If you want the record to deviate from the normal peoplesoft record naming convention , you should specify that name in the 'Non-Standard SQL Table Name:". In simple terms, your own convention.
Also you can observe that the build sequence number is '1' by default. This will show the number of instances that are available for the view.

After saving the record , open SQL editor and type the Select SQL statement.
Note:
1. See that the select columns match the same order and type of the fields mentioned in the record definition.
2.Donot put Semicolon[;] at the end of the SQL statement. It will be treated as an invalid character .

Now build the SQL View with appropriate settings. Below the build editor, shows the status of the build action.
If there are 0 errors and 0 warnings, the view is successfully build.
Hope the info is helpful.
Will share you about 'Dynamic View' in next post. In short, Dynamic view is a SQL that is executed in runtime.

Effective Dates usage

Action Type : Update/Display
View : Current, Future
Change : Future only
Insert New Rows : Effective Date Greater Than the Current Row

Action Type : Update/Display All
View : History, Current, Future
Change : Future only
Insert New Rows : Effective Date Greater Than the Current Row

Action Type : Correction
View : History, Current, Future
Change : All Existing Rows
Insert New Rows : Add New Rows with No EffectiveDate Restrictions.

Note. For records that do not contain EFFDT, all actions (Update/Display, Update/Display All,and Correction) operate the same way—they retrieve all existing rows for the specified keys.