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.