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);