Instantiating Microsoft Excel COM Objects

Following is a sample code ,which can be used in application engine or Peoplecode to instantiate COM Objects and can read and write data from excel file.


/*Variable Declaration*/

Local object &oWorkApp, &oWorkBook, &oWorkApp1, &oWorkBook1, &oWorkApp2, &oWorkBook2, &oWorkSheet, &oWorkSheet1, &oWorkSheet2;
Local integer &Idx1, &IdxSuccess, &IdxError, &TotalRow, &TotalRowSuccess, &TotalRowError;
Local File &fileLog;
Local string  &InputFile;
Local string &RetPath;


 
try

   &InputFile=&FilePath;/**ABHISHEK**/
   &oWorkApp = CreateObject("COM", "Excel.Application");
   ObjectSetProperty(&oWorkApp, "Visible", True);
   &oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
   &oWorkBook.Open(&InputFile);
   &oWorkSheet = &oWorkApp.Worksheets("Sheet1");
  
 
      /***Output File***/
    &OutputFile= &strRptOutputDir | &strDirSep | "JAPAN_LEAVING_REPORT.xls";
    &oWorkApp1 = CreateObject("COM", "Excel.Application");
   ObjectSetProperty(&oWorkApp1, "Visible", True);
   &oWorkBook1 = ObjectGetProperty(&oWorkApp1, "Workbooks");
   &oWorkApp1.DisplayAlerts = "False";
   &oWorkBook1 = &oWorkApp1.Workbooks.Add();
   &oWorkSheet1 = &oWorkApp1.Worksheets("Sheet1");

  
  
   &Idx1 = 1;
   &IdxSuccess = 0;
   &ThisRec = "Y";
  
For &i=  1 to 3
For &j= 1 TO 71
  &oWorkSheet1.cells( &i,&j).Value = &oWorkSheet.cells( &i,&j).Value;

end-for;
end-for; 
 
   &status = &oWorkApp.ActiveWorkBook.Close( False);
   &oWorkApp.DisplayAlerts = "True";
   &oWorkApp.Quit();
  
   &status1 = &oWorkApp1.ActiveWorkBook.SaveAs(&OutputFile);
   &status1 = &oWorkApp1.ActiveWorkBook.Close( False);
   &oWorkApp1.DisplayAlerts = "True";
   &oWorkApp1.Quit();
 


catch Exception &ex
  
   &status = &oWorkApp.ActiveWorkBook.Close( False);
   &oWorkApp.DisplayAlerts = "True";
   &oWorkApp.Quit();
  
   &status1 = &oWorkApp1.ActiveWorkBook.SaveAs(&OutputFile);
   &status1 = &oWorkApp1.ActiveWorkBook.Close( False);
   &oWorkApp1.DisplayAlerts = "True";
   &oWorkApp1.Quit();
  

end-try;
 

Migrating AWE Setups HCM Version 9.1

We will first prepare Export Script and then will import the data.We have to write following export script in Source data Mover:
-- Export Script:
 SET INPUT h:\temp\QC680\AWESetup.dat;
SET LOG h:\temp\QC680\AWEimportSetup.log;

--"Register Transaction

EXPORT PS_EOAW_TXN WHERE EOAWPRCS_ID = 'ProcessID';
EXPORT PS_EOAW_TXN_LNG WHERE EOAWPRCS_ID = 'ProcessID';
EXPORT PS_EOAW_TXN_LBL WHERE EOAWPRCS_ID = 'ProcessID';
EXPORT PS_EOAW_TXN_LVL WHERE EOAWPRCS_ID = 'ProcessID';

-- "Configure Transactions"
EXPORT PS_EOAW_NOTIFY WHERE EOAWPRCS_ID = 'ProcessID';
EXPORT PS_EOAW_NOT_USER WHERE EOAWPRCS_ID = 'ProcessID';
EXPORT PS_EOAW_TXN_CFG WHERE EOAWPRCS_ID = 'ProcessID';

-- "Setup Process Definitions"
 EXPORT PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_PRCS_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_TIMEOUTDEF WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_NOTIFYDEF WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_NOT_USRDEF WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_STAGE WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_STG_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_PATH WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_PATH_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_TIMEOUT WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_STEP WHERE EOAWPRCS_ID = 'ProcessID';
 EXPORT PS_EOAW_STEP_LNG WHERE EOAWPRCS_ID = 'ProcessID';


-- "Criteria Definitions"
EXPORT PS_EOAWCRTA WHERE EOAWCRTA_ID LIKE '%CrtID%';
 EXPORT PS_EOAWCRTA_LNG WHERE EOAWCRTA_ID LIKE '%CrtID%';
 EXPORT PS_EOAWCRTA_REC WHERE EOAWCRTA_ID LIKE '%CrtID%';
 EXPORT PS_EOAWCRTA_RECLNG WHERE EOAWCRTA_ID LIKE '%CrtID%';
 EXPORT PS_EOAWCRTA_VAL WHERE EOAWCRTA_ID LIKE '%CrtID%';


-- "Maintain User Lists"
EXPORT PS_EOAWUSER_LIST WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');
 EXPORT PS_EOAWUSER_LNG WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');
 EXPORT PS_EOAW_UL_ATTRIB WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');


-- "Generic Templates"
EXPORT PS_WL_TEMPLATE_GEN WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';
 EXPORT PS_WL_TEMPL_GEN_TK WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';
 EXPORT PS_WL_TEMPL_GEN_RS WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';


Import Script(in Target Data Mover)

 SET INPUT h:\temp\QC680\AWESetup.dat;
SET LOG h:\temp\QC680\AWEimportSetup.log;

--"Register Transaction

DELETE FROM PS_EOAW_TXN WHERE EOAWPRCS_ID = 'ProcessID';
DELETE FROM PS_EOAW_TXN_LNG WHERE EOAWPRCS_ID = 'ProcessID';
DELETE FROM PS_EOAW_TXN_LBL WHERE EOAWPRCS_ID = 'ProcessID';
DELETE FROM PS_EOAW_TXN_LVL WHERE EOAWPRCS_ID = 'ProcessID';

-- "Configure Transactions"
DELETE FROM PS_EOAW_NOTIFY WHERE EOAWPRCS_ID = 'ProcessID';
DELETE FROM PS_EOAW_NOT_USER WHERE EOAWPRCS_ID = 'ProcessID';
DELETE FROM PS_EOAW_TXN_CFG WHERE EOAWPRCS_ID = 'ProcessID';

-- "Setup Process Definitions"
 DELETE FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_PRCS_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_TIMEOUTDEF WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_NOTIFYDEF WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_NOT_USRDEF WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_STAGE WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_STG_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_PATH WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_PATH_LNG WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_TIMEOUT WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_STEP WHERE EOAWPRCS_ID = 'ProcessID';
 DELETE FROM PS_EOAW_STEP_LNG WHERE EOAWPRCS_ID = 'ProcessID';


-- "Criteria Definitions"
DELETE FROM PS_EOAWCRTA WHERE EOAWCRTA_ID LIKE '%CrtID%';
 DELETE FROM PS_EOAWCRTA_LNG WHERE EOAWCRTA_ID LIKE '%CrtID%';
 DELETE FROM PS_EOAWCRTA_REC WHERE EOAWCRTA_ID LIKE '%CrtID%';
 DELETE FROM PS_EOAWCRTA_RECLNG WHERE EOAWCRTA_ID LIKE '%CrtID%';
 DELETE FROM PS_EOAWCRTA_VAL WHERE EOAWCRTA_ID LIKE '%CrtID%';


-- "Maintain User Lists"
DELETE FROM PS_EOAWUSER_LIST WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');
 DELETE FROM PS_EOAWUSER_LNG WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');
 DELETE FROM PS_EOAW_UL_ATTRIB WHERE EOAWUSER_LIST_ID IN ('CHN_USR_LIST','CHN_MEX_USR_LIST');


-- "Generic Templates"
DELETE FROM PS_WL_TEMPLATE_GEN WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';
 DELETE FROM PS_WL_TEMPL_GEN_TK WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';
 DELETE FROM PS_WL_TEMPL_GEN_RS WHERE WL_TEMPLATE_ID LIKE '%Temp_id%';

IMPORT *;

Executing Unix Command in Peoplsoft

Exec Method is used to execute unix commands:

Following is a sample syntax for copying a file from one directory to another .
&strCmd = "cp " | file path1 |"/abc.txt" | " " | file path 2|"/abc.txt";
&exitcode = Exec(&strCmd, %Exec_Asynchronous + %FilePath_Absolute);

 Similarly Syntax for changing Permission for a file
/**Changing Permision*/
&strCmd = "chmod 777 " | file path |"/abc.txt";
&exitcode = Exec(&strCmd, %Exec_Asynchronous + %FilePath_Absolute);

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