Peoplesoft Message Catalog Table

Posted on June 14, 2018November 20, 2018 by JointLantic

Peoplesoft Message Catalog Table

Navigation of Peoplesoft Message Catalog Table: PeopleTools > Utilities > Administration > Message Catalog

By default language

Step 1. input Message Set Number, description and description short

Message Set Number store in a table: PSMSGSETDEFN

Using SQL for select data by this syntax

SELECT * FROM PSMSGSETDEFN ORDER BY MESSAGE_SET_NBR

/* Related Language Record */ 
SELECT * FROM PSMSGSETLANG ORDER BY MESSAGE_SET_NBR

SELECT * FROM PSMSGSETDEFN ORDER BY MESSAGE_SET_NBR

/* Related Language Record */

SELECT * FROM PSMSGSETLANG ORDER BY MESSAGE_SET_NBR

Step 2. input Message Number and detail

Message Number store in a table: PSMSGCATDEFN

Using SQL for select Detail of message catalog and message number

SELECT * FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

/* Related Language Record */ 
SELECT * FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

SELECT * FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

/* Related Language Record */

SELECT * FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

By owner language

select message catalog and detail by PSMSGSETLANG

SELECT * FROM PSMSGSETLANG ORDER BY MESSAGE_SET_NBR

1	SELECT * FROM PSMSGSETLANG ORDER BY MESSAGE_SET_NBR

select detail in message number of message catalog by PSMSGCATLANG

SELECT * FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

1	SELECT * FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR ='29000' AND MESSAGE_NBR ='1'

You can using this message catalog in peoplesoft for store html, sql, dynamic sql, description, bind variables and use data in page or scroll area or grid of each requirements by using select sql of catalog table or use function in peoplesoft such as msggettext or msggetexplaintext in peoplecode language.

More learning at How to use MsgGet, MsgGetText and MsgGetExplainText by Peoplecode Function of Peoplesoft

convert hijri to gregorian oracle

Posted on May 10, 2018May 14, 2018 by JointLantic

convert hijri to gregorian oracle

convert hijri to gregorian oracle by use sql at below syntax

SELECT TO_CHAR(SYSDATE, ‘DD-MM-YYYY’, ‘NLS_CALENDAR=”English Hijrah”’) FROM DUAL

select TO_DATE(’24/08/1439′,’dd/mm/yyyy’,q'[nls_calendar=’English Hijrah’]’) from dual

Ref:docs.oracle.com/cd/B19306_01/server.102/b14237/initparams119.htm#REFRN10116

Oracle nls_calendar thai buddha

Posted on May 10, 2018May 14, 2018 by JointLantic

Oracle nls_calendar thai buddha

How to use oracle nls_calendar thai buddha by sql. You can use sql at below

SELECT TO_CHAR(sysdate, ‘DD MON YYYY’, ‘nls_calendar=”Thai Buddha” nls_date_language = Thai’) DATE_CHAR FROM dual

SELECT TO_CHAR(SYSDATE, ‘DD-MM-YYYY’, ‘NLS_CALENDAR=”Thai Buddha”’) FROM DUAL

SELECT TO_CHAR(SYSDATE, ‘DD/MM/YYYY’, ‘NLS_CALENDAR=”THAI BUDDHA” NLS_DATE_LANGUAGE=THAI’) FROM DUAL

Table HTML in Page Peoplesoft

Posted on February 13, 2018May 14, 2018 by JointLantic

Table HTML in Page Peoplesoft

You can all html area all page of peoplesoft by select form oracle sql

SELECT * FROM PSPNLHTMLLANG

How To Use Format Number with Commas SQL Oracle

Posted on February 13, 2018May 14, 2018 by JointLantic

How To Use Format Number with Commas SQL Oracle

You can use format number with commas or 1000 separator in oracle sql

SELECT to_char(1000,’9,999.00′, ‘NLS_NUMERIC_CHARACTERS = ”,.”’) FROM DUAL

How to Select Schema of Record or Table of Peoplesoft in SQL

Posted on January 25, 2018May 14, 2018 by JointLantic

How to Select Schema of Record or Table of Peoplesoft in SQL

You can view fields or all detail of table of peoplesoft in database by use sql statement

SELECT * FROM ALL_TAB_COLUMNS
where owner = ‘owner name’ AND TABLE_NAME =’RecordName’;

If you want to show all field of record then you can use this sql

SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
where owner = ‘owner name’ AND TABLE_NAME =’RecordName;

How to Find Permission List for a page/Component/User ID in PeopleSoft By SQL

Posted on January 16, 2018May 14, 2018 by JointLantic

How to Find Permission List for a page/Component/User ID in PeopleSoft By SQL

You can use queries of sql for fine permission list of each user ID

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLEUSER=’1111′ AND
a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID

ORDER BY ROLENAME , CLASSID , MENUNAME

You can use queries of sql for fine permission list of page of peoplesoft

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND PNLITEMNAME=’JPM_CAT_TYPES’

ORDER BY ROLENAME , CLASSID , MENUNAME

You can use queries of sql for fine permission list of component of peoplesoft

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND BARITEMNAME=’JPM_CAT_TYPES’

ORDER BY ROLENAME , CLASSID , MENUNAME

How to Using Multiple SQL Statements in an SQL Action of Application Engine

Posted on January 12, 2018May 14, 2018 by JointLantic

How to Using Multiple SQL Statements in an SQL Action of Application Engine

/*– Use ; –*/
SQL Statment 1

%Execute()
Update TABLE1 Set Status=’A’;
Update TABLE1 Set Status=’B’;
Update TABLE2 Set Status=’C’;

SQL Statment 2

%Execute()
Update TABLE1 Set Status=’A’;
Delete TABLE1 where Status=’B’;
insert into TABLE2 values(‘1′,’C’);

/*– Use / –*/
SQL Statment 3

%Execute(/)
Update TABLE1 Set Status=’A’/
Update TABLE1 Set Status=’B’/
Update TABLE2 Set Status=’C’/

SQL Statment 4

%Execute(/)
Update TABLE1 Set Status=’A’/
Delete TABLE1 where Status=’B’/
insert into TABLE2 values(‘1′,’C’)/

How to Get Column Names of a Table in Oracle Query

Posted on September 19, 2017May 14, 2018 by JointLantic

How to Get Column Names of a Table in Oracle Query

You can select column names from table oracle database

** table name is upper **

select column_name from user_tab_cols where table_name =’PS_JOB’;

SELECT COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME =’PS_JOB’;

SELECT COLUMN_NAME FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ‘PS_JOB’;

/* Sort Field by order in table*/

SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_JOB’ ORDER by COLUMN_ID

SELECT COLUMN_NAME FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_JOB’ ORDER by COLUMN_ID

/* Sort Field by order in table and concate with ,*/

SELECT RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ‘,’)).extract (‘//text()’),’,’) COLUMN_NAMES FROM DBA_TAB_COLS WHERE TABLE_NAME =’PS_DEPT_TBL’ ORDER by COLUMN_ID

/* Select text all field form owner table */

SELECT ‘SELECT ‘||RTRIM (xmlagg (xmlelement (e, COLUMN_NAME || ‘,’)).extract (‘//text()’),’,’)||’ FROM ‘||OWNER||’.’||TABLE_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME=’PS_DEPT_TBL’ GROUP BY OWNER,TABLE_NAME;

1000 Separator In Sql

Posted on May 31, 2017May 14, 2018 by JointLantic

1000 Separator In Sql

You can use sql at below for 1000 Separator In Sql

SELECT to_char(1000,’9,999.00′, ‘NLS_NUMERIC_CHARACTERS = ”,.”’) FROM DUAL