Sample DMS Script to import/export PeopleSoft data

 

Following Export and Import DMS script can be used for the beginner who is new to use DMS to export data from PeopleSoft records.

DMS to Export Data and create DAT file:

 

SET LOG C:\Temp\TEST_TABLE_EXPORT.log;

SET OUTPUT C:\Temp\TEST_TABLE_DATA.DAT;

EXPORT PS_TEST_TABLE WHERE LASTUPDDTTM <= SYSDATE();

DMS to Import data from DAT file:

SET LOG C:\Temp\TEST_TABLE_IMPORT.log;

SET INPUT C:\Temp\TEST_TABLE_DATA.DAT;

IMPORT PS_TEST_TABLE ;

PeopleCode to Schedule Process and wait until that executed successfully

Following sample PeopleCode can be used to schedule any process using PeopleCode (Application Engine PeopleCode or in any other event). This sample PeopleCode is used to schedule Crystal report and email the same to the specific user. First it override the parameter set in the Crystal report process definition than it schedules the Crystal Report process and wait until that process resulted into success.

Local ProcessRequest &Rqst;
Local string &Subject, &Text, &EmailId, &Running, &Finish;
Local integer &PrcsInst;

/*use sql exe to update parameter of crystal report process definition*/
TEST_STATE_AET.PARMLIST.Value = "-ORIENTP " | Char(34) | TEST_STATE_AET.OPRID.Value | Char(34) | " " | Char(34) | TEST_STATE_AET.PROCESS_INSTANCE.Value | Char(34);

&Rqst = CreateProcessRequest();
&Rqst.RunControlID = TEST_STATE_AET.RUN_CNTL_ID.Value;
&Rqst.ProcessType = "Crystal";
&Rqst.ProcessName = "TESTCRST";
&Rqst.RunLocation = "PSNT";
&Rqst.OutDestType = "Email";
&Rqst.OutDestFormat = "PDF";
&Rqst.OutDest = TEST_STATE_AET.EMAILID.Value;
&Rqst.EmailSubject = "TEST MAIL";
&Rqst.EmailText = "TEST MAIL";
&Rqst.EmailAttachLog = False;
&Rqst.Schedule();
&PrcsInst = &Rqst.ProcessInstance;

/* wait until PROCESS finish */
/*check process running status check process completed or not*/
&Running = "S";
While &Running = "S"
   SQLExec("select 'x' from psprcsrqst where PRCSINSTANCE = :1 and runstatus  in ('5','6','7','14')", &PrcsInst, &Finish);
   If None(&Finish) Then
      &Running = "N"
   End-If;
End-While;

Generate File using PeoleCode and email generated file

When there is requirement that they need CSV file and email the generated file to specific user, following sample PeopleCode could help you create such functionality:

Local File &File;
Local SQL &SQL1, &SQL2, &SQL3;

Local string &reportPath;
Local string &FilPath;

/*Check server where process is being executed*/
&PrcsInstace = TEST_STATE_AET.PROCESS_INSTANCE;
SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist  WHERE  prcsinstance = :1", &PrcsInstace, &Server);

&find = 0;
&find = Find("UNX", &Server);

/* DEFINE FOLDER PATH AS PER SERVER WHERE PROCESS IS BEING EXECUTED*/
If &find = 0 Then
   &reportPath = GetEnv("PS_SERVDIR") | "\files\";
Else
   &reportPath = GetEnv("PS_SERVDIR") | "/files/";
End-If;
&FilPath = &reportPath | "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";


&File = GetFile(&FilPath, "W", %FilePath_Absolute);
If &File.IsOpen Then
   &SQL1 = CreateSQL("SELECT RPad(BUSINESS_UNIT,15,' ') %Concat ',  '%Concat RTrim(DATE_FIELD) %Concat '   ,' %Concat LPad(' ',(13-Length(RTrim(TIME_FIELD)))/2,' ')%Concat RTrim(TIME_FIELD) %Concat RPad(' ',Round((13-Length(RTrim(TIME_FIELD)))/2),' ')%Concat ',' %Concat LPad(' ',(18-Length(To_Char(COUNT_LINES)))/2,' ')%Concat To_Char(COUNT_LINES) %Concat RPad(' ',Round((18-Length(To_Char(COUNT_LINES)))/2),' ')    %Concat ',' %Concat LPad(' ',(17-Length(To_Char(GROSS_AMT)))/2,' ') %Concat To_Char(GROSS_AMT) %Concat RPad(' ',Round((17-Length(To_Char(GROSS_AMT)))/2),' ')  %Concat ',' %Concat DESCR15 FROM %table(TEST_RECORD_NAME)              WHERE PROCESS_INSTANCE = :1 ORDER BY BUSINESS_UNIT", &PrcsInstace);

   /*Write Header Line*/
   &Header = "SAMPLE PROGRAM TO PRINT CSV FILE DATE:" | String(%Date);
  
   &File.WriteLine(&Header);
   &File.WriteLine("");
   &File.WriteLine("BUSINESS UNIT  ,DATE          ,TIME         ,TOTAL AMOUNT    ,AMOUNT          ,STATUS");
   &File.WriteLine("---------------,---------------,-------------,------------------,-----------------,-------------");
   While &SQL1.Fetch(&String)
      If RTrim(&String, " ") <> "" Then
         &File.WriteLine(&String);
      End-If;
   End-While;
  
  
Else
   Error ("File not open");
End-If;
&File.Close();

/*Send email to generated file*/
/* Added PeopleCode to send generated Text File*/

Local SQL &SQLOutDest2;
Local string &reportPath;
Local string &FilPath;

&PrcsInstace = TEST_STATE_AET.PROCESS_INSTANCE;
SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist  WHERE  prcsinstance = :1", &PrcsInstace, &Server);

&find = 0;
&find = Find("UNX", &Server);

If &find = 0 Then
   &reportPath = GetEnv("PS_SERVDIR") | "\files\";
Else
   &reportPath = GetEnv("PS_SERVDIR") | "/files/";
End-If;
&FilPath = &reportPath | "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";


/*Get EmailIDs from TEST_ROLE role to sent mail*/
&SQLOutDest2 = CreateSQL("SELECT DISTINCT EMAILID FROM PSROLEUSER R, PSOPRDEFN O   WHERE R.ROLENAME = 'TEST_ROLE' AND R.ROLEUSER = O.OPRID");

/*CHECK FOR DUPLICATE EMAIL IDS*/
While &SQLOutDest2.Fetch(&Email)
   If Find(&Email, &OutDest) = 0 Then
      &OutDest = &OutDest | ";" | &Email;
   End-If;
End-While;

/*Initialize Parameters to sent mail*/
&MAIL_FLAGS = 0;
&MAIL_TO = &OutDest;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "TEST CSV FILE";
&MAIL_TEXT = "";
&MAIL_FILES = &FilPath;
&MAIL_TITLES = "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";

/*Send Mail*/
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER);

/*Log Message about status of sending emails*/
If &RET <> 0 Then
   MessageBox(0, "", 0, 0, "Sending Email Failed, Email IDs are  " | &OutDest);
Else
   MessageBox(0, "", 0, 0, "Succesfuly sent Emails to following Email IDs " | &OutDest);
   /*Delete Generated File*/
   &File = GetFile(&FilPath, "W", %FilePath_Absolute);
   &File.delete();
End-If;

SQL to Get Navigation for any PeopleSoft Component

Sometime during testing or development we would like to know navigation through which we can access the component in PIA. Following SQL could help you to find out Path/Navigation using PeopleSoft component name.

SELECT
RTRIM(E.PORTAL_LABEL)
|| ' >> ' || RTRIM(D.PORTAL_LABEL)
|| ' >> ' || RTRIM(C.PORTAL_LABEL)
|| ' >> ' || RTRIM(B.PORTAL_LABEL)
|| ' >> ' || RTRIM(A.PORTAL_LABEL)
FROM PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C
ON C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D
ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E
ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE
A.PORTAL_URI_SEG2 = 'COMPONENT_NAME'

Get Online Objects Associated with PeopleSoft Project

Below SQL could help you to get all the online objects present in any PeopleSoft Project. This SQL could be used to get list of object during creation of Technical design document even it can be used for technical keep-drop during PeopleSoft Upgrade.

SELECT
PROJECTNAME,
(CASE
WHEN OBJECTTYPE = 0 THEN 'Record'
WHEN OBJECTTYPE = 1 THEN 'Indexes'
WHEN OBJECTTYPE = 2 THEN 'Fields'
WHEN OBJECTTYPE = 3 THEN 'Field Formats'
WHEN OBJECTTYPE = 4 THEN 'Translates Values'
WHEN OBJECTTYPE = 5 THEN 'Pages'
WHEN OBJECTTYPE = 6 THEN 'Menus'
WHEN OBJECTTYPE = 7 THEN 'Components'
WHEN OBJECTTYPE = 8 THEN 'Record PeopleCode'
WHEN OBJECTTYPE = 9 THEN 'Menu PeopleCode'
WHEN OBJECTTYPE = 10 THEN 'Queries'
WHEN OBJECTTYPE = 11 THEN 'Tree Structures'
WHEN OBJECTTYPE = 12 THEN 'Trees'
WHEN OBJECTTYPE = 13 THEN 'Access Groups'
WHEN OBJECTTYPE = 14 THEN 'Colours'
WHEN OBJECTTYPE = 15 THEN 'Styles'
WHEN OBJECTTYPE = 17 THEN 'Business Processes'
WHEN OBJECTTYPE = 18 THEN 'Activities'
WHEN OBJECTTYPE = 19 THEN 'Roles'
WHEN OBJECTTYPE = 20 THEN 'Process Definitions'
WHEN OBJECTTYPE = 21 THEN 'Server Definitions'
WHEN OBJECTTYPE = 22 THEN 'Process Type Definitions'
WHEN OBJECTTYPE = 23 THEN 'Job Definitions'
WHEN OBJECTTYPE = 24 THEN 'Recurrence Definitions'
WHEN OBJECTTYPE = 25 THEN 'Message Catalog Entries'
WHEN OBJECTTYPE = 26 THEN 'Dimension Definition'
WHEN OBJECTTYPE = 27 THEN 'Cube Definition'
WHEN OBJECTTYPE = 28 THEN 'Cube Instance Definition'
WHEN OBJECTTYPE = 29 THEN 'Business Interlink'
WHEN OBJECTTYPE = 30 THEN 'SQL'
WHEN OBJECTTYPE = 31 THEN 'File Layout Definitions'
WHEN OBJECTTYPE = 32 THEN 'Component Interfaces'
WHEN OBJECTTYPE = 33 THEN 'Application Engine Programs'
WHEN OBJECTTYPE = 34 THEN 'Application Engine Sections'
WHEN OBJECTTYPE = 35 THEN 'Message Nodes'
WHEN OBJECTTYPE = 36 THEN 'Message Channels'
WHEN OBJECTTYPE = 37 THEN 'Message Definitions'
WHEN OBJECTTYPE = 38 THEN 'Approval Rule Set'
WHEN OBJECTTYPE = 39 THEN 'Message PeopleCode'
WHEN OBJECTTYPE = 40 THEN 'Subscription PeopleCode'
WHEN OBJECTTYPE = 42 THEN 'Comp. Interface PeopleCode'
WHEN OBJECTTYPE = 43 THEN 'Application Engine PeopleCode'
WHEN OBJECTTYPE = 44 THEN 'Page PeopleCode'
WHEN OBJECTTYPE = 45 THEN 'Page Field PeopleCode'
WHEN OBJECTTYPE = 46 THEN 'Component PeopleCode'
WHEN OBJECTTYPE = 47 THEN 'Component Record PeopleCode'
WHEN OBJECTTYPE = 48 THEN 'Component Rec Fld PeopleCode'
WHEN OBJECTTYPE = 49 THEN 'Images'
WHEN OBJECTTYPE = 50 THEN 'Style Sheets'
WHEN OBJECTTYPE = 51 THEN 'HTML Object(s)'
WHEN OBJECTTYPE = 52 THEN 'File References'
WHEN OBJECTTYPE = 53 THEN 'Permission Lists'
WHEN OBJECTTYPE = 54 THEN 'Portal Registry Definitions'
WHEN OBJECTTYPE = 55 THEN 'Portal Registry Structures'
WHEN OBJECTTYPE = 56 THEN 'URL Definitions'
WHEN OBJECTTYPE = 57 THEN 'Application Packages'
WHEN OBJECTTYPE = 58 THEN 'Application Package PeopleCode'
WHEN OBJECTTYPE = 59 THEN 'Portal Registry User Home Page'
WHEN OBJECTTYPE = 60 THEN 'Analytic Types'
WHEN OBJECTTYPE = 62 THEN 'XSLT'
WHEN OBJECTTYPE = 63 THEN 'Portal Registry Related'
WHEN OBJECTTYPE = 64 THEN 'Mobile Pages'
WHEN OBJECTTYPE = 68 THEN 'File References'
WHEN OBJECTTYPE = 69 THEN 'File Type Codes'
WHEN OBJECTTYPE = 72 THEN 'Dignostic Plug Ins'
WHEN OBJECTTYPE = 73 THEN 'Analytic Models'
WHEN OBJECTTYPE = 79 THEN 'Service'
WHEN OBJECTTYPE = 80 THEN 'Service Operation'
WHEN OBJECTTYPE = 81 THEN 'Service Operation Handler'
WHEN OBJECTTYPE = 82 THEN 'Service Operation Version'
WHEN OBJECTTYPE = 83 THEN 'Service Operation Routing'
WHEN OBJECTTYPE = 84 THEN 'IB Queues'
WHEN OBJECTTYPE = 85 THEN 'XLMP Template Definition'
WHEN OBJECTTYPE = 86 THEN 'XLMP Report Definition'
WHEN OBJECTTYPE = 87 THEN 'XMLP File Definition'
WHEN OBJECTTYPE = 88 THEN 'XMPL Data Source Definition'

ELSE CAST(OBJECTTYPE AS CHAR)
END) OBJECTTYPE,

OBJECTVALUE1,
(CASE
WHEN OBJECTID2= 0 THEN 'BLANK'
WHEN OBJECTID2= 2 THEN 'FIELDNAME'
WHEN OBJECTID2= 4 THEN 'BARNAME'
WHEN OBJECTID2= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID2= 16 THEN 'LANGUAGE CODE'
WHEN OBJECTID2= 21 THEN 'EFFDT'
WHEN OBJECTID2= 24 THEN 'INDEXID'
WHEN OBJECTID2= 25 THEN 'OPRID'
WHEN OBJECTID2= 28 THEN 'PROCESSNAME'
WHEN OBJECTID2= 39 THEN 'MARKETNAME'
WHEN OBJECTID2= 67 THEN 'FIELDNAME'
WHEN OBJECTID2= 77 THEN 'SECTION'
WHEN OBJECTID2= 81 THEN 'SQLTYPE'
WHEN OBJECTID2= 87 THEN 'SUBSCRIPTIONS'
WHEN OBJECTID2= 100 THEN 'TYPE'
WHEN OBJECTID2= 102 THEN 'LABELNAME'
END) OBJECTID2,

OBJECTVALUE2,
(CASE
WHEN OBJECTID3= 5 THEN 'ITEMNAME'
WHEN OBJECTID3= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID3= 22 THEN 'FIELDVALUE'
WHEN OBJECTID3= 101 THEN 'OBJECTNAME'
END) OBJECTID3,

OBJECTVALUE3,
(CASE
WHEN OBJECTID4= 2 THEN 'FIELDNAME'
WHEN OBJECTID4= 12 THEN 'PROGRAMTYPE'
WHEN OBJECTID4= 21 THEN 'EFFDT'
END) OBJECTID4,

OBJECTVALUE4,
(CASE
WHEN SOURCESTATUS= 1 THEN 'ABSENT'
WHEN SOURCESTATUS= 2 THEN 'CHANGED'
WHEN SOURCESTATUS= 3 THEN 'UNCHANGED'
WHEN SOURCESTATUS= 4 THEN 'CHANGED*'
WHEN SOURCESTATUS= 5 THEN 'UNCHANGED*'
WHEN SOURCESTATUS = 6 THEN 'SAME'
END) SOURCESTATUS,

(CASE
WHEN TARGETSTATUS= 1 THEN 'ABSENT'
WHEN TARGETSTATUS= 2 THEN 'CHANGED'
WHEN TARGETSTATUS= 3 THEN 'UNCHANGED'
WHEN TARGETSTATUS= 4 THEN 'CHANGED*'
WHEN TARGETSTATUS= 5 THEN 'UNCHANGED*'
WHEN TARGETSTATUS = 6 THEN 'SAME'
END) TARGETSTATUS,

(CASE
WHEN UPGRADEACTION= 0 THEN 'COPY'
WHEN UPGRADEACTION= 1 THEN 'DELETE'
WHEN UPGRADEACTION= 3 THEN 'COPYPROPERTIES'
END) UPGRADEACTION,


(CASE
WHEN TAKEACTION= 0 THEN 'NO'
WHEN TAKEACTION= 1 THEN 'YES'
END) TAKEACTION
FROM PSPROJECTITEM WHERE PROJECTNAME = 'M_LIB'

SQL to get Record Field attributes of PeopleSoft Record

To get list of PS records with all fields attributes like keyfield information prompt table name, default value etc

----For Oracle DataBase

SELECT A.RECNAME,
       A.FIELDNAME,
    CASE
    WHEN bitand(A.USEEDIT,1) = 0 THEN 'N'
    WHEN bitand(A.USEEDIT,1) = 1 THEN 'Y'
ELSE ' ' END ,
       A.FIELDNUM,
    CASE B.FIELDTYPE
    WHEN 0 THEN 'CHAR'
    WHEN 1 THEN 'LONG CHAR'
    WHEN 2 THEN 'NBR'
    WHEN 3 THEN 'SIGN'
   WHEN 4 THEN 'DATE'
    WHEN 5 THEN 'TIME'
    WHEN 6 THEN 'DTTM'
    WHEN 7 THEN 'IMG'
    WHEN 8 THEN 'VERS'
ELSE ' ' END ,
       B.LENGTH,
       CASE
    WHEN bitand(A.USEEDIT,256) = 0 THEN 'N'
    WHEN bitand(A.USEEDIT,256) = 256 THEN 'Y'
    ELSE ' ' END ,
    A.EDITTABLE ,
    A.DEFRECNAME ,
    A.DEFFIELDNAME ,
        ( SELECT DISTINCT 'Y' FROM PSXLATITEM C WHERE C.FIELDNAME=A.FIELDNAME) AS TRANSLATE
FROM PSRECFIELDDB A ,PSDBFIELD B
WHERE A.RECNAME IN ('SF_ACCTG_LN'
)
AND A.FIELDNAME=B.FIELDNAME
ORDER BY A.RECNAME,A.FIELDNUM
 -_sql sERVER
SELECT A.RECNAME,
       A.FIELDNAME,
    CASE A.USEEDIT&1
    WHEN 0 THEN 'N'
    WHEN 1 THEN 'Y'
ELSE ' ' END AS KEYFIELD,
       A.FIELDNUM,
    CASE B.FIELDTYPE
    WHEN 0 THEN 'CHAR'
    WHEN 1 THEN 'LONG CHAR'
    WHEN 2 THEN 'NBR'
    WHEN 3 THEN 'SIGN'
    WHEN 4 THEN 'DATE'
    WHEN 5 THEN 'TIME'
    WHEN 6 THEN 'DTTM'
    WHEN 7 THEN 'IMG'
    WHEN 8 THEN 'VERS'
ELSE ' ' END "TYPE",
       B.LENGTH,
       CASE A.USEEDIT&256
    WHEN 0 THEN 'N'
    WHEN 256 THEN 'Y'
    ELSE ' ' END AS REQFIELD,
    A.EDITTABLE AS PROMPTTBL,
    A.DEFRECNAME AS  DEFAULT_REC,
    A.DEFFIELDNAME AS 'DEF_REC_FLD/VALUE',
        ( SELECT DISTINCT 'Y' FROM PSXLATITEM C WHERE C.FIELDNAME=A.FIELDNAME) AS TRANSLATE
FROM PSRECFIELDDB A ,PSDBFIELD B
WHERE A.RECNAME IN ('ARB_BILL_TP_LST',
)
AND A.FIELDNAME=B.FIELDNAME
ORDER BY A.RECNAME,A.FIELDNUM

---- For SQL Server DataBase

SELECT A.RECNAME,
A.FIELDNAME,
CASE A.USEEDIT&1
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
ELSE ' ' END AS KEYFIELD,
A.FIELDNUM,
CASE B.FIELDTYPE
WHEN 0 THEN 'CHAR'
WHEN 1 THEN 'LONG CHAR'
WHEN 2 THEN 'NBR'
WHEN 3 THEN 'SIGN'
WHEN 4 THEN 'DATE'
WHEN 5 THEN 'TIME'
WHEN 6 THEN 'DTTM'
WHEN 7 THEN 'IMG'
WHEN 8 THEN 'VERS'
ELSE ' ' END "TYPE",
B.LENGTH,
CASE A.USEEDIT&256
WHEN 0 THEN 'N'
WHEN 256 THEN 'Y'
ELSE ' ' END AS REQFIELD,
A.EDITTABLE AS PROMPTTBL,
A.DEFRECNAME AS DEFAULT_REC,
A.DEFFIELDNAME AS 'DEF_REC_FLD/VALUE',
( SELECT DISTINCT 'Y' FROM PSXLATITEM C WHERE C.FIELDNAME=A.FIELDNAME) AS TRANSLATE

FROM PSRECFIELDDB A ,PSDBFIELD B

WHERE A.FIELDNAME=B.FIELDNAME

AND A.RECNAME IN ( )

ORDER BY A.RECNAME,A.FIELDNUM

-------------------------------------------------------------------------------------------------------------