PeopleSoft : SQL to Stop PSQuery Recurrence

-- Update run status to delete in request table

UPDATE PSPRCSRQST R
SET R.RUNSTATUS=2
WHERE EXISTS
  (SELECT 'x'
  FROM sysadm.PSPRCSQUE B ,
    sysadm.PS_QUERY_RUN_CNTRL A
  WHERE a.oprid      = 'SUPERUSER'
  AND a.run_cntl_id  = B.runcntlid
  AND a.oprid        = b.oprid
  AND B.PRCSNAME     = 'PSQUERY'
  AND B.RECURNAME   <> ' '
  AND B.PRCSINSTANCE =
    (SELECT MAX(B1.PRCSINSTANCE)
    FROM PSPRCSQUE B1
    WHERE B.PRCSNAME = B1.PRCSNAME
    AND B.OPRID      = B1.OPRID
    AND B.RUNCNTLID  = B1.RUNCNTLID
    AND B1.RECURNAME = B.RECURNAME
    )
  AND R.PRCSINSTANCE=B.PRCSINSTANCE
  )
  AND R.RUNSTATUS=5
  AND R.RUNCNTLID='TEST8' ;

-- Update run status to delete in queue table

UPDATE PSPRCSQUE R
SET R.RUNSTATUS=2
WHERE EXISTS
  (SELECT 'x'
  FROM sysadm.PSPRCSQUE B ,
    sysadm.PS_QUERY_RUN_CNTRL A
  WHERE a.oprid      = 'SUPERUSER'
  AND a.run_cntl_id  = B.runcntlid
  AND a.oprid        = b.oprid
  AND B.PRCSNAME     = 'PSQUERY'
  AND B.RECURNAME   <> ' '
  AND B.PRCSINSTANCE =
    (SELECT MAX(B1.PRCSINSTANCE)
    FROM PSPRCSQUE B1
    WHERE B.PRCSNAME = B1.PRCSNAME
    AND B.OPRID      = B1.OPRID
    AND B.RUNCNTLID  = B1.RUNCNTLID
    AND B1.RECURNAME = B.RECURNAME
    )
  AND R.PRCSINSTANCE=B.PRCSINSTANCE
  )
  AND R.RUNSTATUS=5
  AND R.RUNCNTLID='TEST8';

PeopleSoft : SQL to remove carriage return

UPDATE PS_PERSON_ADDRESS SET ADDRESS1=REPLACE(ADDRESS1,chr(13),'') WHERE EMPLID ='999999';

PeopleSoft : SQL to find special character in Names

SELECT *
FROM SYSADM.PS_NAMES A
WHERE (A.NAME                        !=   CONVERT(A.NAME,'JA16SJIS')
OR A.LAST_NAME                      !=   CONVERT(A.LAST_NAME,'JA16SJIS')
OR A.LAST_NAME_SRCH         !=   CONVERT(A.LAST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME_SRCH        !=   CONVERT(A.FIRST_NAME_SRCH,'JA16SJIS')
OR A.FIRST_NAME                     !=   CONVERT(A.FIRST_NAME,'JA16SJIS')
OR A.MIDDLE_NAME                !=   CONVERT(A.MIDDLE_NAME,'JA16SJIS')
OR A.NAME_DISPLAY               !=   CONVERT(A.NAME_DISPLAY,'JA16SJIS')
OR A.NAME_FORMAL               !=   CONVERT(A.NAME_FORMAL,'JA16SJIS')
OR A.SECOND_LAST_NAME    !=   CONVERT(A.SECOND_LAST_NAME,'JA16SJIS')
OR A.PREF_FIRST_NAME         !=    CONVERT(A.PREF_FIRST_NAME,'JA16SJIS')
OR A.NAME_AC                          !=    CONVERT(A.NAME_AC ,'JA16SJIS')
OR A.SECOND_LAST_SRCH     !=    CONVERT(A.SECOND_LAST_SRCH,'JA16SJIS')
OR A.NAME_DISPLAY_SRCH   !=    CONVERT(A.NAME_DISPLAY_SRCH,'JA16SJIS'))
AND EFFDT=(SELECT MAX(B.EFFDT) FROM SYSADM.PS_NAMES B WHERE B.EMPLID=A.EMPLID AND B.NAME_TYPE=A.NAME_TYPE);

PeopleSoft : Handling Special Character Issue in BI(XMLP) Publisher Reports

Some time BI Publisher report got error due to special character in xml file.To handle this we have to modify xml file.

We have to use <?xml version="1.0" encoding="iso-8859-1"?> instead of  <?xml version="1.0"?> it will allow report to run if data has some special characters.


If you are using rowset based xml generation use substitute function to replace the tag before writeline.

/* Create Sample XML File */
      &strXMLFile = &ObjRowsetXSD.getXMLData(&rs1, &StrXMLfilepath | &StrXMLfileNm | ".XSD");
      &strXMLFile = Substitute(&strXMLFile, "<?xml version=""1.0""?>", "<?xml version=""1.0"" encoding=""iso-8859-1""?>");
      &SampleXMLFile.WriteLine(&strXMLFile);



If you are generating XML directly in code then use

&XMLdoc = CreateXmlDoc("<?xml version='1.0' encoding=""iso-8859-1""?><ROOT/>");

PeopleSoft : Excel to CI Issue : User-defined type not defined

This issues comes mostly when using higher versions of windows operating system(Windows 8 and above) or using higher versions of Office (Above office 2007)

Follow the below steps to resolve the issue:

Solution 1:Need to enable macros

                 Step1: In excel to CI template Press Alt+F11

                 Step2: Go to Tools-> References

                 Step3: Select “Microsoft AcitveX Data Objects 6.1 Library”  and “Microsoft DAO 3.6   Object Library” and press OK button and Save the references.

Solution 2: Need to modify VB Script

                 Step1: In excel to CI template Press Alt+F11

                 Step2: In Menu bar go to Debug->Compile Excel to CI template, it will open the code with error

                 Step3: Replace  "DOMDocument" with "DOMDocument60"

                                  Replace "Set xHTTP = New XMLHTTP" with "Set xHTTP = CreateObject("MSXML2.SERVERXMLHTTP.6.0")"

                    Step4: Save the Document and compile again

Note:Some times Solution1 is not enough to resolve the issue Try both the solutions.