jsmpros jsmpros

JSMPROS is an educational service firm offering a variety of PeopleTools training courses.

jsmpros / find-ps-cn.sql

Created October 12, 2017 05:26

SQL to find PeopleSoft Conditional Navigation Components

	WITH PORTAL_REGISTRY (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PATH) AS (
	SELECT P.PORTAL_NAME
	, P.PORTAL_REFTYPE
	, P.PORTAL_OBJNAME
	, P.PORTAL_LABEL
	, PORTAL_URI_SEG1
	, PORTAL_URI_SEG2
	, PORTAL_URI_SEG3
	, P.PORTAL_LABEL AS PATH
	FROM PSPRSMDEFN P

jsmpros / ps-cn-crefs.sql

Created October 12, 2017 20:23

SQL that lists Conditional Nav CREFs

jsmpros / event-mapped-components.sql

Created December 27, 2017 00:18

Find project components that use Event Mapping

	SELECT PORTAL.PORTAL_NAME
	, PORTAL.PORTAL_OBJNAME
	, PROJ.OBJECTVALUE1 PNLGRPNAME
	, PROJ.OBJECTVALUE2 MARKET
	, EVT.PTCS_SERVICEID
	, ISCOMP_LABEL.XLATLONGNAME /EVT.PTCS_ISCOMPSERVICE/
	, PROCSEC_LABEL.XLATLONGNAME PROC_SEQ
	, ( /* nested instead of join since this field might be empty */
	SELECT XLAT.XLATLONGNAME
	FROM PSXLATITEM XLAT

jsmpros / user-component-wo-role.sql

Created June 4, 2018 00:10

Users who have access to a component, but aren't members of a specific role

	SELECT DISTINCT USR.ROLEUSER
	FROM PSROLEUSER USR
	WHERE EXISTS (
	SELECT DISTINCT ROL.ROLENAME
	FROM PSROLECLASS ROL
	WHERE EXISTS (
	SELECT DISTINCT AUTH.CLASSID
	FROM PSAUTHITEM AUTH
	INNER JOIN PSMENUITEM MENU
	ON AUTH.BARITEMNAME = MENU.ITEMNAME

jsmpros / ps-read-excel.js

Last active March 31, 2023 20:17

JavaScript that reads binary excel files using POI and inserts rows into a PeopleSoft table

	// endsWith polyfill
	if (!String.prototype.endsWith) {
	String.prototype.endsWith = function(searchString, position) {
	var subjectString = this.toString();
	if (typeof position !== 'number' \|\| !isFinite(position) \|\|
	Math.floor(position) !== position \|\|
	position > subjectString.length) {
	position = subjectString.length;
	}
	position -= searchString.length;

jsmpros / process-script.ppl

Created June 29, 2018 02:57

	Local string &script;
	Local JavaObject &manager = CreateJavaObject("javax.script.ScriptEngineManager");
	Local JavaObject &engine = &manager.getEngineByName("JavaScript");

	SQLExec("SELECT CONTDATA FROM PSCONTENT WHERE CONTNAME = :1", HTML.JSM_READ_XLS_JS, &script);

	REM ** Tell JavaScript what file to process;

	&engine.put("fileName", "c:/temp/roles.xlsx");
	&engine.eval(&script);

jsmpros / json.org-role.ppl

Created June 29, 2018 22:24

	Local JavaObject &jsonRole = CreateJavaObject("org.json.JSONObject");
	Local JavaObject &jsonPerms = CreateJavaObject("org.json.JSONArray");

	Local string &roleName = "Compensation Administrator";
	Local string &roleDescr;
	Local string &permId;
	Local string &jsonStr;
	Local SQL &perms;

	REM ** Create JSON structure...;

jsmpros / json.org-role-stringer.ppl

Created June 29, 2018 22:32

Using org.json.JSONStringer from PeopleCode

	Local JavaObject &jsonRole = CreateJavaObject("org.json.JSONStringer");

	Local string &roleName = "Compensation Administrator";
	Local string &roleDescr;
	Local string &permId;
	Local string &jsonText;
	Local SQL &perms;

	REM ** Create JSON structure...;
	SQLExec("SELECT DESCR FROM PSROLEDEFN WHERE ROLENAME = :1", &roleName, &roleDescr);

jsmpros / json.org-read-json.ppl

Created June 29, 2018 22:38

Reading JSON in PeopleCode using json.org Java classes

	Local JavaObject &jsonStr = CreateJavaObject("java.lang.String", &jsonText);

	Local JavaObject &constructorArgTypes = CreateJavaObject("java.lang.Class[]", &jsonStr.getClass());
	rem Local JavaObject &jsonClass = GetJavaClass("org.json.JSONObject");
	rem Local JavaObject &constructor = &jsonObject.getClass().getConstructor(&constructorArgTypes);
	Local JavaObject &jsonClass = CreateJavaObject("org.json.JSONObject").getClass();
	Local JavaObject &constructor = &jsonClass.getConstructor(&constructorArgTypes);
	Local JavaObject &parserObject = &constructor.newInstance(CreateJavaObject("java.lang.Object[]", &jsonStr));
	Local JavaObject &roleJson = &jsonClass.cast(&parserObject);

jsmpros / ps-create-role-json.js

Created June 29, 2018 22:49

Javascript to create JSON from PeopleCode (role permission lists)

	var result = (function() {
	var ReflectiveArray = java.lang.reflect.Array;
	var SQLExec = Packages.PeopleSoft.PeopleCode.Func.SQLExec;
	var CreateSQL = Packages.PeopleSoft.PeopleCode.Func.CreateSQL;
	var parms = ReflectiveArray.newInstance(java.lang.Class.forName("java.lang.Object"),
	2 /* number of selected columns */);
	var columns = ReflectiveArray.newInstance(java.lang.Class.forName("java.lang.Object"),
	1 /* number of selected columns */);

	parms[0] = roleName;