Saturday, May 2, 2020

SQR Generate Outbound CSV File & Send Email Attachment

SQR Generate Outbound CSV File & Send Email  Attachment


Introduction:

The requirement of the document is to generate an outbound CSV file for the
business data stored in the database and need to send an email to user with attached
new CSV file.
Document Summary:
This document briefly describes how to open and write heading and the main data in
to the CSV and the email the file to the user as an attachment using “PSMAIL.EXE”.

The PeopleSoft Process Scheduler Recurrence has been used to schedule the SQR
report to email the file to the user on daily basis.

STEP 1: Open File to Write

This Procedure opens the file "TopRunSQL_Report<CurrentDate>.csv" in the folder
tmp
!************************************************************************
*******
! Begin-Procedure open-file
!************************************************************************
*******
Begin-Procedure open-file

let $sys_date=$current-date
let $OutputFile = '/tmp/TopRunSQL_Report'||$sys_date|| '.csv'
Open $OutputFile as 1 For-Writing Record=5000

!************************************************************************
*******
End-Procedure open-file
!************************************************************************
*******


STEP 2: Write Heading


This procedure writes the heading details:
!************************************************************************
******
Begin-Procedure write-heading
!************************************************************************
******

LET $Write_Job = 'SQL_TYPE'

||'|'||'LAST_LOAD_TIME'
||'|'||'ELAPSED_TIME_IN_S'
||'|'||'ELAPSED_TIME_PER_SQL_IN_S'
||'|'||'EXECUTIONS'
||'|'||'HASH_VALUE'
||'|'||'MODULE'
||'|'||'PIECE'
||'|'||'SQL_TEXT'

write 1 from $Write_Job

!************************************************************************
******
End-Procedure !write-heading
!************************************************************************
******

STEP 3: MAIN-SELECT


This procedure retrieves the data from the table PS_LN_LONGRUNSQL
!************************************************************************
*********
Begin-Procedure Process-main
!************************************************************************
*********
BEGIN-SELECT
A.LN_SQLTYPE
A.LN_LASTLOAD_TIME
A.LN_ELAPSTIMEIN_SEC
A.LN_ELAPSTIMPER_SEC
A.LN_EXECUTIONS
A.LN_HASHVALUE
A.LN_MODULE_LNGRN
A.LN_PIECE
A.SQL_TXT_LNGRNSQL

move &A.LN_SQLTYPE to $SHORT_SQL
move &A.LN_LASTLOAD_TIME to $last_load_time
move &A.LN_ELAPSTIMEIN_SEC to $elapsed_time_in_s
move &A.LN_ELAPSTIMPER_SEC to $ELAPSED_TIME_PER_SQL_IN_S
move &A.LN_EXECUTIONS to $executions
move &A.LN_HASHVALUE to $hash_value
move &A.LN_MODULE_LNGRN to $module
move &A.LN_PIECE to $piece
move &A.SQL_TXT_LNGRNSQL to $sql_text

do Write_To_File
FROM PS_LN_LONGRUNSQL A

END-SELECT
!************************************************************************
*********
End-Procedure !Process-main
!************************************************************************
*********

STEP 4: Write_To_File


This procedure writes the retrieved data to the CSV file

!************************************************************************
******
Begin-Procedure Write_To_File
!************************************************************************
******

let $Values = $SHORT_SQL
||'|'||$last_load_time
||'|'||$elapsed_time_in_s
||'|'||$ELAPSED_TIME_PER_SQL_IN_S
||'|'||$executions
||'|'||$hash_value
||'|'||$module
||'|'||$piece
||'|'||$sql_text
write 1 from $Values

!************************************************************************
******

End-procedure !Write_To_File
!************************************************************************
******

STEP 5: Send Email with Attachment


This procedure email the report to the user as an attachment
!************************************************************************
******
Begin-Procedure SendEmail
!************************************************************************
******
let $EMAIL= 'toemailid@abc.com'

let $curr_date = $current-date
let $file = '/tmp/TopRunSQL_Report'||$curr_date|| '.csv'

let $subject = 'FEA TOP RUN SQL Report'||$curr_date
let $newline = chr(13)

let $linefeed = chr(10)
LET $BODY = 'Hi,'||$newline|| ' Please find the attached FEA Long RUN SQL report. '

let $alias ='TopRunSQL_Report'||$curr_date|| '.csv'

Let $system_command ='psmail -TO"'||$EMAIL||'" -SUBJECT"'||$subject||'" -
BODY"'||$BODY||'" -FILE"'||$file||'" -ALIAS"'||$alias||'"'

show 'EMAIL INFO :' $system_command

Call system using $system_command #call_status
if #call_status <> 0
show ' '
show 'Error Sending Mail'
show '=================='
show #call_status
show $system_command
show ' '
end-if
!************************************************************************
*********
End-Procedure !SendEmail
!************************************************************************
*********

References:
  1. Project Work
  2. People Books
  3. Google

Dynamic Prompts in PeopleSoft



Dynamic Prompts in PeopleSoft


1.   Introduction

When one enters a value in a field and proceeds entering data on the page by using the prompts (magnifying glass button) of the fields, one expects the data shown by the prompts to be in some kind of relationship with the values that are already entered. In order to have such a feature, which we can conveniently call as dynamically changing prompts, we need to change the prompt table from where the data is selected or we need to change the criteria of data selection dynamically.
 Here we present two ways of doing this. Firstly using the Derived table, this is a well reported concept so it is briefly discussed in here. Secondly using the dynamic views, this is discussed to the level of implementation. Later part of the document has an example with some snaps to help the understanding.

2.   Dynamic prompt requirement


Let’s deal with the requirement part with an example here.
Let us take two fields that are present on an online PeopleSoft page, field1 and field2. Let field1 denote the party budget of a person and field2 denote the hotels that he can afford with that budget. Now imagine after entering field1 user enters a value in field2 by clicking on the prompt button of field2. Basing on the value entered in field1 he should be able to see the list of hotels in field2’s prompt.
     How to provide this functionality?

a.    Using Derived.Edittable


This is a well reported way of solving this problem. In the OurRecord.Field2 properties enter %xxx as the prompt table name (with edit or without edit). Where ‘xxx ‘ can be an already existing field name on the record ‘Derived’ or one inserted by you for this purpose into it. Place the Derived.xxx field on the page that we were dealing with, so far, at the same level where the field2 is present. Now the simplest part of the process, write some peoplecode assigning a table name to the Derived.xxx.value in RowInit or FieldChange or any other relevant event of field1. This assigned table/view is expected to have the required prompt values in it.

Continuing with our example let us assume that Table_20 holds the names of hotels that people with budget of up to 20000 can visit and Table_60 holds the names of the hotels that people with budget of up to 60000 can visit.

So a simple indicative algorithm might look like this:
If OurRecord.Field1.value <= 20000 then Derived.xxx.value = ‘TABLE_20
If OurRecord.Field1.value <= 60000 then Derived.xxx.value = ‘TABLE_60

Let’s further imagine that with time the system got introduced to the users with 30000, 40000, 50000, 70000 budgets and the users expect the system to work in a similar fashion as it did previously i.e. only hotels within their budget level need to be listed.
Continuing with our methodology, we need to create tables or views (here Table_xx) with different groupings and assign them to the Derived.xxx.value.  This is a case where there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in People Code based on what the user enters. The number of conditional statements required will increase in proportion with the number of combinations. In cases where, the given Record.Field is a delivered combination and the number of pages where this combination occurs may not be small. Then the number of objects that get modified with such a change will be high.

b.   Using Dynamic view


In this methodology a Dynamic view should be created and assigned as the prompt table to the OurRecord.Field2. Let’s create a dynamic view with required fields (Field2, Hotel_Descr) and preferably have a generic SQL, select statement. Now use the sqltext property of the field2 to assign a dynamic SQL to the dynamic view. This people code can be written in field change or/and in any other relevant event of field1.

An indicative algorithm will look like this:

OurRecord.Field2.sqltext = “Select Field2, Hotel_Descr from PS_Hotel_Tbl Where Field1 < = “|OurRecord.Field1.value|”

Effectively if budget is 30000 then the statement should look like this:
‘Select Field2, Hotel_Descr from PS_Hotel_Tbl Where Field1 < = 30000’

Here the table PS_Hotel_Tbl is assumed to have the data of all Hotels.

Using this functionality we can go ahead and incorporate a lot of conditions in the select statement. Not all functionalities that are supported for an SQL select statement are allowed here. E.g. Order By.

3.   Points that can increase our speed


a.    Check whether the tools version supports the sqltext property.
b.    Remove deferred processing for the fields where the code is written. Here Field1.
c.    Effective date and Effective status should be considered while writing the SQL of the view.
d.    Use the same fields in select statement, which are present in the dynamic view.
e.    Use a MessageBox to see the SQL statement which is getting assigned in the peoplecode.
f.     If the field is on a level other than level0, upon row insert the sqltext used for the new row will be the one specified during the dynamic view’s creation. This can be altered in RowInit or RowInsert events if felt required.

4 . Real-time Example

In a case where, on journal entry page, user enters Deptid and expects to have Products related to the Deptid entered, in the Product field’s prompt.

  1. Create a dynamic view with required fields in it.

 2. Place a SQL select statement in the view.


  3. Change the prompt of the jrnl_ln.product to this dynamic view.

4. Create a SQL statement according to the requirement and assign it to the sqltext of the product field, in some peoplecode event of jrnl.deptid field



Basic Understanding of Peoplesoft Security -


Why PeopleSoft Security
                        Why PeopleSoft Security Peoplesoft is a world-famous client-server application suite based on client-server architecture. Just like any other ER system, Peoplesoft is also based on backend RDBMS, which can either be Oracle, DB2 etc, based on client environment.
As Peoplesoft is a reputed HRMS suite, Security is mandatory , as the data mainly deals with personal information such as SSN, credit card etc, so there should be sufficient provision of security towards data accesses, having said that, they should also be able to access the data at the same time and generate appropriate reports as per user requirements on a “need to know basis”.
This holds good both in terms of data access provided to the software developers, as well as to the end-users who will be accessing it.
All users are not exposed of the same access towards the application and only authorized user have the access to application servers.
The PeopleSoft Internet Architecture falls into the category of PeopleSoft online security, also referred to as runtime security, which suggests that only authorized users can hook up with the online and application servers, and only authorized application servers can hook up with a given database.


PeopleSoft Security         

Ø  The PeopleSoft security approach is tailored specially for the internet.

Ø  Security People Tool enables the provision of security to all of the users of the PeopleSoft system.

Ø  Using the Definition Security People Tool we can restrict access to the objects developers created in PeopleSoft Application Designer. 

Terms Used while implementing Peoplesoft Security
USER ID          - Enables access to system.
                      -  Authenticates, Authorizes.

ROLE             -   A named set of work that a user can do.
                     -   Works in conjunction with Workflow.
                     -   Comprised of “1 or more” Permission Lists.
PERMISSION

LIST               - Defines the pages to which the employee is

Different types of Security

Sign on and Time-out Security:-
                  User is asked to enter User ID and a password on the PeopleSoft Sign on page, when signing into PeopleSoft. For a valid ID and password, PeopleSoft connects the user to the application and the system retrieves the appropriate user profile. When a user signs on, they are connected as long as their sign on time allows and as long as their browser doesn’t sit idle for longer than their time-out interval is achieved.
Page and Dialog Security:-
             Page and Dialog Security is used for granting access to PeopleSoft menus, Users can be controlled from accessing the data with the help of People tool Security.    

Batch Environment Security:-
      For running a batch Processes from PeopleSoft Process Scheduler, appropriate Process Profile to the User Profile should be assigned along with proper process groups.
      Each batch program has a run control that we define before we can run the batch program.
Definition Security:-
             For restricting the access of application developers, a separate People tool called ‘Definition Security’ is used , which can be used to govern access to the Record definitions, Field definitions, but Page definitions as well. 

Application Data Security:-
     Definition Security is a type of data security- which can be used to control access to object definitions in the People tool tables. We can set data permissions at the following levels:
Ø  Query/Table-Level Security:-  Query/Table- Level Security helps us to build SQL   queries to retrieve information from application tables.
Ø  Row-Level Security:-  Row Security Permission list is used to define data security to users.
Field Security:-  We can restrict the access to particular fields or columns within our application tables by using People code.

Basic Steps for assigning Permission Lists to Roles and Roles to User Profiles


Ø  Create Permission Lists
Ø  Create Roles
Ø  Assign Permission Lists to the Roles
Ø  Create User IDs
Ø  Assign Roles to User ID’s

Flow of Security



Permission Lists

Permission Lists are the building blocks of user security authorization. Permission lists allow the developer to grant user access not only to PeopleSoft Pages, but they allow us to grant access to development environments, sign on time periods, administrative tools, personalizations etc. as well.

• The fewer Permission Lists used the more modular and scalable the PS security will be.      
• Multiple Permission Lists can be assigned to a single

Navigation: People Tools>Permissions and Roles>Permission Lists


The different steps involved in working with permission lists are:-

Ø  Create a new permission list.
Ø  Copy permission lists.
Ø  Delete permission lists.

To create a new permission list:-

Ø  Select People Tools, Security, Permissions & Roles, Permission Lists.
Ø  On the search page click Add a New Value.
Ø  In the Permission List edit box, enter the name of Permission List you want to create.
Ø  From the pages in the Permission List component, select the appropriate permissions.
Ø  Save your work.

Copying Permission Lists:-

Ø  Select People Tools, Security, Permissions & Roles, Copy Permission Lists.
Ø  In the search page, search for the Permission List that you want to copy, and click it.
Ø  On the Permission List Save As page, enter a new name in the to: edit box for the Permission List that you want to copy.
Ø  Click Save.

Deleting Permission Lists:-

Ø  Select People Tools, Security, Permissions & Roles, Delete Permission Lists.
Ø  On the search page, locate the Permission List that you want to delete and click it.
Ø  The Delete Permission List page appears.
Ø  Click Delete Permission List.
Ø  Click OK to confirm the deletion, or click Cancel to end without deleting

Permission Lists – General page

PeopleSoft Navigation for managing General information of a Permission List





Permission Lists – Pages page


PeopleSoft Navigation for managing General information of a Permission List




Permission Lists –Pages–Edit Components

PeopleSoft Navigation for managing accesses of a Permission List to specific Pages, and ability of a user to edit them




Permission Lists– Pages – Page Permissions

PeopleSoft Navigation for managing additional accesses of a Permission List to specific Pages



Permission Lists – People Tools page

PeopleSoft Navigation for managing accesses of a Permission List to specific Peopletool objects



Permission Lists – Queries page

PeopleSoft Navigation for managing accesses of a Permission List to specific Peopletool objects



Sign-on Times

Peoplesoft can also inhibit users to perform sign on, on the basis of date time condition


Roles

• Roles are assigned to User Profiles and they are the intermediate objects that link User Profiles to Permission Lists
• Multiple roles can be assigned to a single User Profile. Examples: Applicant, Employee, Vendor, Accounts Payable Clerk, and Manager
• Roles allow you to mix and match access to your PeopleSoft system and can be assigned to User Profiles manually or dynamically.


Navigation: People Tools>Permissions and Roles>Roles
There are three steps for working with roles. They are:-

ØCreate a new Role
Ø  Copy a role.
Ø  Delete a role.

Creating a New Role:-

ØSelect People Tools, Security, Permissions & Roles, Roles.
Ø  On the search page click add a New Value.
Ø  In the Role Name edit box, enter the name of role you want to create, and click Add.
Ø  From the pages in the Roles component select the appropriate role options.
Ø  Save your work.


Copying Roles:-

Ø  Select People Tools, Security, Permissions & Roles, Copy Roles.
Ø  On the search page, search for the role that you want to copy (clone), and click it.
Ø  The Role Save as page appears.
Ø  On the Role Save As page, enter a new name in the as: edit box.
Ø  Click Save. 

Deleting Roles:-

Ø  Select People Tools, Security, Permissions & Roles, Delete Roles.
Ø  On the search page, locate the Permission List that you want to delete and click it.
Ø  The Delete Permission List page appears.
Ø  Click Delete Permission List.
Ø  Click OK to confirm the deletion, or click Cancel to abort.

Roles – General

Peoplesoft Navigation for managing general information of Roles



Roles – Assign Permission Lists

Peoplesoft Navigation for managing Permission List information for Roles


Roles – Members

Peoplesoft navigation which gives information of all the User IDs which are assigned to the specific Role



Roles – Workflow

Peoplesoft navigation for managing the Workflow information




Roles – Queries

Navigation gives cumulative information about the Roles.



User Profiles



Ø  Users are assigned with collection of roles which defines the individual users of your PeopleSoft system
Ø  Set of data describing a particular user of your PeopleSoft system
Ø  Information about the user such as e-mail address, language code, and password
Ø  Assign process profiles, row-level security or business unit security at the User Profile level
Ø  User Profiles are linked to Roles to grant access to specific areas within the PeopleSoft application


Navigation: People Tools>User Profiles>User Profiles

Working with User Profiles:-

Ø  Create a new User Profile.
Ø  Copy a User Profile.
Ø  Delete a User Profile.

Creating a New User Profile:-

Ø  Select People Tools, Security, User Profiles, User Profiles.
Ø  On the Find Existing Values page, click Add a New Value.
Ø  On the Add a New Value page, enter the new User ID in the User ID edit box, and click Add. 
Ø  The User ID can contain up to 30 characters. The name you use can't contain a comma (,) or       space. Also, you can't create a User ID named PPLSOFT; this ID is a reserved user ID used         within People Tools.
Ø  Specify the appropriate values from the pages in the User Profiles component, and click Save. 

Copying a User Profile:

Ø  Select People Tools, Security, User Profiles, Copy User Profiles.
Ø  On the Find an Existing Value search page, select the User ID you want to clone.
Ø  On the User Profile Save As page, enter the New User ID, description, and the password that the new user ID should use to sign on to the system.

Deleting a User Profile:-

Ø  Select People Tools, Security, User Profiles, Delete User Profiles.
Ø  On the Delete User Profile page, make sure you have selected the correct user profile.
Ø  Click Delete User Profile. 

User Profiles – General

Peoplesoft navigation where User is assigned with Permission List


User Profiles – Roles

Peoplesoft navigation where User is assigned with specific Roles


User Profiles – Audits

Navigation gives the information on Audit.



User Profiles – User ID Queries


Navigation gives cumulative information about the User ID.


Authorization IDs
             The PeopleSoft System uses various authorization ID’s and passwords to control user access to the system.

User IDs:-
             A PeopleSoft User ID is the ID you enter at the PeopleSoft sign on dialog box. Using People Tools Security, you assign each PeopleSoft user a User ID and password. The combination of these two items grants users online access to the PeopleSoft system. The system can also use a User ID stored within an LDAP directory server.
             The User ID is the key used to distinctly identify the User Profile definition.
Connect IDs:-
             People Tools offers a connectivity feature called Connect ID. Initial connection to the database is performed by Connect ID. A Connect ID is a valid user ID that, when used during login, takes the place of PeopleSoft User IDs for the logon process. We don’t have to create a new database user for every PeopleSoft user you add to the system.
Connect ID is required for a direct connection (two-tier connection) to the database. 

Access IDs:-
             The PeopleSoft Access ID is the RDBMS ID with which PeopleSoft applications are ultimately connected to the database after the PeopleSoft system gets connects.
An Access ID typically has administrator-level database access; that is, it has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application. The Access ID should have SELECT, UPDATE, and DELETE access.
It's important to understand that users do not know their corresponding Access ID. They just sign on with their User ID and password and—behind the scenes—the system logs them onto the database using the Access ID.
Access Profiles are used in the following situations only: when an application server connects to the database, when a windows workstation connects directly to the database, and when a batch job connects directly to the database.
Access Profiles are not used when end users access the applications through PIA.

Symbolic IDs:-
            The Symbolic ID acts as an intermediary entity between the User ID and the Access ID.
All the User IDs are associated with a Symbolic ID, which in turn is associated with an Access ID.
when the Access ID needs to be retrieved or referenced, the query selects the appropriate Access ID by using the Symbolic ID as a search key. 

PeopleSoft Security Tables
Different tables that are involved in PeopleSoft Security are:-
Ø  PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
Ø  PSROLEDEFN: This table stores information about PeopleSoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
Ø  PSROLEUSER: This table stores information about the Users in PeopleSoft and the roles assigned to them.
Ø  PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
Ø  PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.

    References

                 http://www.peoplesoft.com

                People Books the proprietary documentation provided by PeopleSoft Enterprise.