Generating SQL IN EXCEL

There are situatuion where we have data in excel sheet and we need to generate SQL corresponing to each row of excel sheet .

Example: Consider this data    which we have in excel sheet.

We have four column of a table say table1 and we want to generate sql to insert these data in table1.

We will use excel feature to generate Sql.

We are Going to use concatenate command which conactenate two strings. COMMA (,) is operator which concatenate two string .For example: =concatenate("ram is "," a good boy");operator , will concatenate these two string ram is & a good boy .So if want to concatenate cell A2 & B2 then we will write =concatenate(A2,B2) & it will result in concatenating the values of cell A2 AND B2.

For our case we will try to write like this :

=CONCATENATE("Insert into table1 values('",A2,"','",B2,"',",C2,",'",D2,"');")

("Insert into table1 values('" --->this is one string and it should be concatenated by value of A2 cells followed by single quote which we will  get concatenate in next string.

"','" -->This is nothing but we are ending charachter field with quote, inserting a comma (not the operator one)starting a new quote for the next char col and ending the string with double quote.After we generate the command we populate the same thing for each column

Result will look like this.It is useful in case we have a lot of data in excel.

PSQuery Security

Query is a PeopleTool that helps you build SQL queries to retrieve information from your application tables.

For each Query user, you can specify the records they are allowed to access when building and running queries by creating Query Access Groups in the Query Access Group

Query Access Group Trees:

Trees are a graphical way of presenting hierarchical information.

PeopleSoft Query uses query access group trees to control the access of the tables in your PeopleSoft database.

Define a hierarchy of PeopleSoft record definitions, based on logical or functional groupings, and then give users access to one or more nodes of the tree.

Users can retrieve information only from those tables whose record definitions to which they have access.

 Nodes: Query access group trees contain two types of Nodes: groups and records.

• Groups are a logical representation of a set of child groups or records. It is similar to folder in Windows.It will be another Accessgroup.
• Records represent a PeopleSoft record definition.

Navigation : Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.

To Add the Query Access Groups to user: Open the primary Permission List for the user--> ‘Query’ Tab-->Click on Access Group Permissions.
Add the tree name, select the proper Access Group, Select ‘Accessible’ button. Repeat to add more Access groups.-->Save

Example:Suppose in query access tree we have two groups each is having two records :Grp1 (Rec1,Rec2) & Grp2 (rec3,rec4).

And in permission list ptptptest1 we have included grp1 of access group tree & in ptpttest2 we have added grp2 of access grp tree .Then user having permission list ptpttest1 can access only rec1 & rec2 while user having pemission list ptpttest2 can access rec3 & rec4.If we will add both grp1 & grp2 then user can access all the 4records

Using Query Profile page, user level security can be set. Query profile specify which type of access a user can have to work on PS Query
Navigation Path: Main Menu -> People Tools -> Security -> Permissions and Roles -> Permission Lists -> Query Tab -> Query Profile Link

Row Level Security:

• By default, when you give Query users access to a record definition, they have access to all the rows of data in the table built using the associated record definition.
• With row-level security, users can have access to a table without having access to all rows on that table.
• This type of security is typically applied to tables that hold sensitive data.
• For example, you might want users to be able to review personal data for employees in their own department, but not for people in other departments. You would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they could only see rows where the DEPTID matches their own.
• PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.
• When a user searches for data in the data table, the system performs a related record join between the view and the base table rather than searching the table directly.

Query Security Record Definitions:

• You implement row-level security by having Query search for data using a query security record definition. The query security record definition adds a security check to the search.
• Query security record definitions serve the same purpose as search record definitions do for panels. Just as a panel’s search record definition determines what data the user can display in the panel, the query security record definition determines what data the user can display with Query.
• To get Query to retrieve data by joining a security record definition to the base table, you specify the appropriate Query Security Record when you create the base table’s record definition.

To apply row level security:

1. Select PeopleTools, Application Designer to open the Application Designer, and open the record on which you want to apply row-level security.
2. With the record definition open in the Application Designer, click the Properties button, and select the Use tab from the Record Properties dialog box.
3. Select the security record definition (usually a view) in the Query Security Record list box.

4. Once you’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition. If you’ve already used SQL Create to build a table from this record definition, you don’t need to rebuild it.

Note. PeopleSoft row-level security views restrict users from seeing certain rows of data. To secure data through the search record, simply put one of the three Row Level Security fields on your record as a Key, not a List Box Item. The three Row Level Security fields are OPRID (User ID), OPRCLASS (Primary Permission List), and ROWSECCLASS (Row Security Permission List). If one of these fields is on the search record as a Key, not a List Box Item, PeopleTools does the following. PeopleTools adds a WHERE clause when it performing a SELECT through the record forcing the value to be equal to the current user’s value.

Understanding SetID ,Business Unit & Record Groups

I was just going through some blogs which clearly explains concepts of Setid, Business Unit, Record Groups and Tableset Control .

I am just posting the same content here .

Scenario Walkthrough:

Assume that you want to record the transfer of an employee from New York to Dallas in PeopleSoft HCM. To carry this out, you will navigate to the Job Data component, enter the effective date of transfer, select the appropriate action and action reason and select the location code corresponding to Dallas. When you look up the prompt on the location field in Job data, you will see that the field called 'Setid' is read-only and pre-filled with a certain value, let's say 'USA'. This is the first observation I want you to understand and question. How is the setid field of the Location prompt in Job data pre-filled with a certain value?

You select the appropriate location from the prompt and save the componen.

After you completed this transaction, you realise that you also had to change the department of the employee along with the change in location. So you navigate back to Job data and try to change the department in the effective dated row that you created for the change in location transaction. When you look up the department prompt in Job data, you again observe that the setid field in the prompt is read only and has a pre-filled value of 'SHARE'. This is the second observation that I need you to question. Why is the pre-filled value of setid different for department (SHARE) and location (USA)?

Let us try to answer these questions first.

How is the setid value of certain fields like location, department, jobcode etc. defaulted in Job data?

A number of setup components in PeopleSoft HCM like Department, Location, Jobcode, Salary Administration Plan, Employee Class etc. are keyed by the field called Setid. The significance of Setid is that it helps to drive the security behind the display of key setup values in the application. Let us assume that you are implementing PeopleSoft Workforce Administration for Australia and New Zealand. The customer requirement is that when they are hiring an employee in Australia, they should be able to look up all departments defined in the organisation, but at the same time, they should be able to select only locations that are specific to Australia. As Department and Location setups are keyed by Setid, this key can be effectively used to drive this requirement. So taking this simplistic example forward, you would have to create three setids in this case. One setid that is 'SHARED' between Australia and New Zealand, another setid that is specific to Australia and the third setid specific to New Zealand. For this customer, the setid assigned to all departments should be the shared setid, while the setid assigned to Australian locations should be the Australian setid and the New Zealand locations should have the New Zealand setid. With the setid allocation while setting up key tables clarified, let us understand how the defaulting of setids happen in Job data.

The value of Business Unit selected in the Job data component, controls the setid that will be defaulted in the Department, Location, Jobcode and Salary Administration plan fields in the Job data component. Coming back to our example of the company with operations in Australia and New Zealand, let us assume that there are two business units that have been defined - one for Australia and another for New Zealand. Prior to selecting the values for Department or Location, you will have to select the value for Business Unit in Job data (a good corollary experiment is to try to look up the department and location prompts in job data without entering any value for Business Unit. You will see that no values will be returned for Departments or Locations in this case). Once a business unit is selected, the defaulted setid values for Deparment, Location, Jobcode and Salary Administration plan is controlled by the value of the business unit field. This goes on to say that if you selected business unit of Australia in Job data, the setid defaulted for department prompt will be the shared setid while the setid defaulted for location prompt will be the Australian setid. Similarly. if you selected a New Zealand business unit in Job data, the setid defaulted for department prompt will be the shared setid, while the setid defaulted for locations will be the New Zealand setid. 

This brings us to the obvious conclusion that there should be a link or mapping between the value of business unit and setids for the various setup values. This means that there should be a mechanism where we can define that for Australia business unit, departments should have the shared setid and locations should have the Australian setid, while for New Zealand business unit, the setid for locations should be New Zealand setid. 

This mapping is achieved by the concepts of 'Table Set Control' and 'Record Groups'.

A Record Group is a group of functionally similar records. From an application point of view, there are different record groups for Departments, Job Codes, Locations etc. 

Tableset Control is the mechanism through which you can map the actual values of a business unit to the default setid for each record group. Thus, using the tableset control setup page under 

Peopletools > Utilities > Administration > Tableset Control you can define the setid that should default for each setup value for a selected business unit. Note that each setup value like Department, Location etc. will be a separate record group. So taking our previous example, to define the setid defaults for the Australian business unit, you would have to navigate to the Tableset Control page and enter the value of the Australian business unit in the search field called Set Control value. This will take you to the transaction page, where all available record groups and the corresponding setid that will be defaulted will be listed. In this page, you will have to change the setid corresponding to the location record group to the Australian setid and set the shared setid as the setid for the Department record group.  So tableset control is the link that maps the business unit values to the appropriate setid for each record group. In conclusion, this entire setup (and terms!) is only to enable organisations define how setup data should be shared/controlled between different entities in the company. 

To retrace the entire concept, let us try to run through how a setid is defaulted for a field like Department in Job data. When a user looks up the prompt of the department table in Job data, the system looks at the Business Unit entered in the component. Then the system checks the table set control setup to retrieve the setid corresponding to the department record group for the set control value of the business unit entered in the component. This setid is used as the default setid for the department prompt in Job data. 

I will leave you with one final clarification that the set control value in a tableset control definition is not necessarily always business unit. We took the example of business unit as that was relevant for our exercise. By definition, a set control value is a higher key on which the setid of a certain record group is dependent. To take an example, the set control value corresponding to the Employee Class field in Job data component (in the Job Information page) is Regulatory Region. This means that the setid defaulted in the prompt of Employee Class will be controlled by the setid attached to the employee class record group in the tableset control page for the Regulatory Region value entered in Job data component.

XML Publisher reports from PeopleCode

Steps to create an XML Publisher report using People Code

Prepare XML File

• Instantiate a new File object and associate it with a local xml file.
• Create a local Rowset and fill it with the data needed for report.
• Open the xml file and set the File Layout (XML) to the file.
• Write the data from Rowset to the xml file and close it.

Generate Report using the XML File

• Import Report definition manager Application classes.
• Create a new report definition object using the XML Publisher Report ID.
• Call the Process Request method using the Template ID.
• Display the generated output report.

XML File Generation Sample:

&file_name = "xmlReport.xml";
Local File &File = GetFile(&file_name, "W", %FilePath_Relative);
WriteToLog(%ApplicationLogFence_Error, "View XML Publisher Report");

&RS = CreateRowset(Record.MYPS_XML_TEST);
&Rows = &RS.Fill();

&File.Open(&file_name, "A", "UTF8", %FilePath_Absolute);
If &File.IsOpen Then
   &File.WriteLine("<MYPS_XML_REPORT>");
   If &File.SetFileLayout(FileLayout.MYPS_XML_TEST) Then
      &File.WriteRowset(&RS, True);
   End-If;
End-If;
&File.WriteLine("</MYPS_XML_REPORT>");
&File.Close();

Report File Generation Sample: 

import PSXP_RPTDEFNMANAGER:ReportDefn;
import PSXP_RPTDEFNMANAGER:Utility; 

Local PSXP_RPTDEFNMANAGER:ReportDefn &oPrtDefn;
Local string &ReportId = "MYPS_RPTST";
Local string &TemplateId = "MYPS_RPTST";

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&ReportId);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&file_name);
&sOutDestFormat = &oRptDefn.GetDefaultOutputFormat();
 
&oRptDefn.ProcessReport(&TemplateId, "", %Date, &sOutDestFormat);
CommitWork();

&oRptDefn.DisplayOutput();

For More info :
http://psoftdiary.blogspot.com/2012/02/xml-publisher-reports-from-peoplecode.html

Calling Application Engine from Page

In this method, a run control table is created. It is populated prior to scheduling the process.

Example:
   Local Record &RunControl = CreateRecord(Record.RUNCNTL_tbl);
   &RunControl.OPRID.Value = %UserId;
   &RunControl.RUN_CNTL_ID.Value = "Run_Control_ID_1";
   &RunControl.TEXT1.Value = &RunPara1;/**Run Control Parameter*/
   &RunControl.TEXT2.Value = &RunPara2;/**Run Control Parameter*/

   &RunControl.Delete();
   &RunControl.Insert();

   /* Create the ProcessRequest Object */
   Local ProcessRequest &RQST = CreateProcessRequest();

   /* Set all the Required Properties */
   &RQST.RunControlID = "RuncntID_1";
   &RQST.ProcessType = "Application Engine";
   &RQST.ProcessName = "PROGID";

   /* Schedule the Process */
   &RQST.Schedule();

   If &RQST.Status = 0 Then
      WinMessage("Process is scheduled", 0);
   End-If;

Opening Report which is generated by calling an application engine

Opening Report By clicking a btn
(Report should have generated in the same session by calling an application engine )
Steps
1)Create filePath from CDM_FILELIST_VW
2) use ViewUrl /ViewContentUrl function.

Example:
Component ProcessRequest &RQST;

/***Generating the file Path of Report Repository***/
&PRCSINSTANCE = &RQST.ProcessInstance;
Local Rowset &RSFileList, &RSCDM_LIST_VW;
&RSFileList = CreateRowset(Record.CDM_FILELIST_VW);
&RSFileList.Fill("where prcsinstance=:1 and cdm_file_type=:2", &PRCSINSTANCE, "XLS");
&RSCDM_LIST_VW = CreateRowset(Record.CDM_LIST_VW);
&RSCDM_LIST_VW.Fill("where prcsinstance=:1 and prcsname=:2", &PRCSINSTANCE, "XMLP");

/**For ViewUrl Function**/
&fname = &RSCDM_LIST_VW(1).CDM_LIST_VW.URL.Value | "/" | &RSCDM_LIST_VW(1).CDM_LIST_VW.CONTENTID.Value | "/" | &RSFileList(1).CDM_FILELIST_VW.FILENAME.Value;

/**For ViewContentURL Function**/
&outputdir = &RSCDM_LIST_VW(1).CDM_LIST_VW.OUTPUTDIR.Value;
&fname1 = "ftp://usrname:pass@hostname/dpappweb3/dev/hcmdev/psreports/" | &outputdir | "/" | &RSFileList(1).CDM_FILELIST_VW.FILENAME.Value;
ViewContentURL(&fname1);

/**********If we are using Viewurl then we need to make pathname like below*************/
/*&fname = &RSCDM_LIST_VW(1).CDM_LIST_VW.URL.Value | "/" | &RSCDM_LIST_VW(1).CDM_LIST_VW.CONTENTID.Value | "/" | &RSFileList(1).CDM_FILELIST_VW.FILENAME.Value;*/
/*Format : url/contentid/filename*/
 ViewURL(&fname);
/************************************************************************************/

Peoplesoft :Use of Important Functions/tables

Dataareacollapsed :It is used to collapsed /Expand groupbox
Syntax:&rec.fld.Dataareacollapsed =True;/*it will collapsed the Grp Box*/

Showrequirefieldque :It will Put * mark on the label of the field.
Syntax:&rec.fld.Showrequirefieldque =true;

HOVERTEXT   :It will change hover text of the Push Buttoon & Hyperlink
Syntax: &rec.fld.HOVERTEXT ='TEST';  
GenerateScriptContentURL:To generate the url of the  a particular navigation.

ViewURL & ViewContentURL :To Open a file which is stored at a particular Location.

PSUSEROBJTYPE:
We can customise grid on Page .We can hide/unhide some columns .We can even sort according to some fields of the grid   .These customisation is specific to a particular user .All these Information is stored in Meta Table PSUSEROBJTYPE.
Understanding PTCUSTOMFORMAT field .It will Look like   0|0|0|0|0#2|0|0|1|2#1|0|0|2|1#
Each # represents the end of a field definition. 
The 5 columns for each field mean:
1. Fieldnum.  The number represents the order you would see in App Designer.  However, the entries are sorted according to the personalized display order. 0 is the first value
2. Hidden: 1-Yes 0-No
3. Frozen: 1-Yes 0-No
4. Sort Type: 1=asc 2=desc 
5. Sort Order: 0-No sort otherwise the order in which the the user specified the sorts

Data Mover Basic

Here I will brief How we can use Data Mover to Move Data from one database to another :
 Data Mover can be used

Transfer application data between PeopleSoft databases.

Move PeopleSoft databases across operating systems and database platforms.

Execute Structured Query Language (SQL) statements against any PeopleSoft database.

There are two mode in which we can run DMS: 

Regular mode. Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.

PeopleSoft Data Mover supports the following standard SQL commands:

• ALTER
• COMMIT
• CREATE
• DELETE
• DROP
• INSERT (cannot be used with SET NO COMMIT or SET NO TRACE.)
• ROLLBACK

For example you can run sql "delete from ps_table_name where field=value;"

 Select sql is not allowed.

Bootstrap mode. In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. 

To Move data  first we will move the data to a Dat file.(Export operation)

After that we read data from that file to record (import operations)

Basic DM Commands .

Export: Select record information and data from records and store the result set in a file. You can use the generated export file as input for migrating to another platform. (file should be .dat type)

Import: Insert data into tables using the information in an export file. If a tablespace or table does not exist, this command creates tablespace, table, and indexes for the record, using the information in the export file, and inserts the data.

REM, REMARK:Indicate comment statements.

Rename:Rename a PeopleSoft record, a field in one record, or a field in all records.

REPLACE_ALL:This is a variation of the IMPORT command. If a table already exists, use this command to drop the table and its indexes from the database. It then does the following:

1. creates the table.
2. creates any triggers.
3. inserts the data.(from export file)
4. creates indexes.

REPLACE_DATA:

 This is a variation of the IMPORT command. Delete data in existing tables and insert the corresponding data from the export file.No need to run Import statment.

SET NO RECORD   :Prevents records from being created during an import.

Example:

On Source Data Mover:

set output  c:\path\file.dat;

set log c:\path\file.log;

export ps_test_tbl  where field=value;

It will create a .dat & .log file .Dat file willbe used as Input on target DataMover.

on target DataMover

set input c:\path\file.dat;

set log c:\path\file.log;

import ps_test_tbl ;
--or you can use
Replace_data  ps_test_tbl ;

This will Read data from input (.dat ) and load it into the table.

Creating Time Lag (Delay )

&current_time = %Datetime;
&delay_time = AddToDateTime(&current_time, 0, 0, 0, 0, 0, 120);
While True
   If %Datetime > &delay_time Then
      Break;
   End-If;
End-While;

Excel to CI Utility

Excel to CI Utility
I am Gng to write about how to use Excel to load data in the Component .I am gng to give a just basic steps to use this utility.


How to enter Connection Data .
1)Web Server Machine Name :Without any protocol Name
(E.g  hrmsonlinedev.somecompany.com)


2)Protocol:Http


3)HTTP Port :Give the port name of your Instance(LOOK into Address bar of your Instance)


4)Portal:psp/Employee


5)PeopleSoft Site Name: This will be your database name. This is the second field after the port number in the URL. e.g (HCMDEV)

6)Node: The PeopleSoft default local node name. The default is PT_LOCAL. To determine the default local node, log into PIA and navigate to PeopleTools > Integration Broker > Node Definitions. Then press the search button without providing any values. The default local node will have a 1 in the Local Node column and a “Y” in the Default Local Node column.
 7)Actions:

Create: if the component interface has create keys. Use this mode when new keys are being added at level 0.


Update: if the component interface does not have create keys. Use this mode if you are adding new children to an existing parent (e.g. inserting new rows into existing collections).


UpdateData: requires you to select this option from the drop-down list. This mode is used to update specific non-key values that already exist but need to be updated. The system uses the keys to locate the row, and when a match is found, the row is updated with new data. If a key match is not found by the system, it displays an error message indicating which collection was missing a key match.

 

• After filling connection information

• Select the field  and Click on Select Input Cell to Select the field for which   you want to enter data.Similary Select All the fields.Then click on New Input Cell

• In Data Input sheet , fill the sheet with the data which you want to upload and the  click on Stage Data For Submission & Finally Click  on Submit on the Last Sheet.That will upload the DATA .If any error comes then check the data which you are uploading.To see the error just keep cursor on Error.

Note that:

Level 0 is indicated 000

Level 1--100

Level 2-110

Limitation of Excel to CI.

1) We can upload max 65000  rows .

2)Can’t load Components that have no keys at level 0, but rely on logic at level 0 to load the level 1 collection

Tip: For safer side, please create new CI. Don't use existing CI for which code has written, as existing CI may corrupt your excel to CI.

Go to Next Sheet "Template".log in with ur PIA credential and give CI name.