Tuesday, July 26, 2011

PeopleSoft Tech Spec SQLs

----------RECNAME AND RECFIELD LEVEL SPECS GENERATION
SELECT RECNAME,FIELDNAME,
(CASE
WHEN USEEDIT=8388609 THEN 'K'
WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'K/S'
WHEN USEEDIT=8388673 THEN 'K/D' ELSE ' '
END) AS KSA
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 THEN 'L' ELSE ' ' END) AS LISTBOX
,(CASE WHEN USEEDIT=8407329 OR USEEDIT=8390945 OR USEEDIT=8405248 THEN 'R' ELSE ' ' END) AS REQUIRED
,(CASE WHEN DEFRECNAME=' ' THEN DEFFIELDNAME ELSE DEFRECNAME||'.'||DEFFIELDNAME END) AS DEFAULTV,EDITTABLE FROM PSRECFIELDDB WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME,FIELDNUM

----------RECNAME LEVEL SPECS
SELECT DISTINCT RECNAME,DECODE(RECTYPE,0,'Table',7,'Temp Table','DEF') AS RECTYPE,'A','New Record' FROM PSRECDEFN WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 1 AND OBJECTTYPE=0
)
ORDER BY RECNAME;
COMMIT;
-----------------------FIELD DETAILS - PROIDED THE PROJECT NAME

SELECT
F.FIELDNAME
,( CASE WHEN F.FIELDTYPE = 3 THEN 'Signed Number'
WHEN F.FIELDTYPE = 2 THEN 'Number'
WHEN F.FIELDTYPE = 0 THEN 'Character' END) AS FIELDTYPE
,( CASE WHEN F.FIELDTYPE = 3 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS-1)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 2 THEN TO_CHAR(F.LENGTH-F.DECIMALPOS)||','||TO_CHAR(F.DECIMALPOS)
WHEN F.FIELDTYPE = 0 THEN TO_CHAR(F.LENGTH) END) AS LENGTH
,L.LONGNAME
,L.SHORTNAME
,'A'
,'NEW FIELD'
FROM PSDBFIELD F,PSDBFLDLABL L WHERE F.FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2
)
AND F.FIELDNAME = L.FIELDNAME
AND L.DEFAULT_LABEL = 1
AND F.FIELDNAME LIKE 'SCB%'

------------------------------------------------XLAT VALUES BASED ON THE PRJ NAME-------------------
SELECT FIELDNAME
,FIELDVALUE AS XLATVALUE
,XLATLONGNAME AS LONGNAME
,XLATSHORTNAME AS SHORTNAME
,'A'
,'NEW XLAT'
FROM PSXLATITEM WHERE FIELDNAME IN (
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTID1 = 6 AND OBJECTTYPE=2 )
--------------------------------------------------PAGE NAMES----------------------------------------------------

SELECT * FROM PSPROJECTITEM P WHERE PROJECTNAME = 'SCB_GF_DEPTID' AND OBJECTTYPE=8 AND OBJECTID1 = 1
SELECT MESSAGE_SET_NBR,MESSAGE_NBR,'A',MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 22300
------------------------------------------------------------------------------------
Posted by at 1 comment:

PeopleSoft Reference SQLs

UPDATE PSRECTBLSPC SET DDLSPACENAME = 'GLAPP' WHERE RECNAME IN
(
SELECT DISTINCT RECNAME FROM PSRECDEFN WHERE RECNAME IN
(
SELECT DISTINCT OBJECTVALUE1 FROM PSPROJECTITEM
WHERE PROJECTNAME = 'SCBCSRTE186' AND OBJECTID1 = 1 AND OBJECTTYPE=0
) AND RECTYPE = 0
)
AND
DDLSPACENAME <> 'GLAPP'
;COMMIT;



SELECT DISTINCT R.ROLENAME,P.CLASSID
FROM PSROLECLASS R,PSAUTHITEM P,PSROLEUSER U
WHERE R.CLASSID = P.CLASSID
AND U.ROLENAME = R.ROLENAME
AND U.ROLEUSER = '1354466'
AND P.MENUNAME='PROCESSMONITOR'

SELECT * FROM PSAUTHITEM WHERE MENUNAME='PROCESSMONITOR'

SELECT * FROM PSROLECLASS WHERE CLASSID='STPPTPT1200'


SELECT DISTINCT C.ROLEUSER,CMP.AUTHORIZEDACTIONS,CMP.DISPLAYONLY
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C ,PSAUTHITEM CMP
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND CMP.CLASSID = A.CLASSID
AND CMP.PNLITEMNAME = 'RUN_BIBSCMON'
AND A.CLASSID = 'XXX_BILLING_ALL'


SELECT * FROM PSAUTHITEM WHERE PNLITEMNAME = 'RUN_BIBSCMON'

----VENDOR RELATED TABLES

UPDATE PS_VENDOR_ADDR SET EMAILID = 'a@a.a';
COMMIT;

UPDATE PS_VENDOR_ADDR SET NAME1 =
TRANSLATE(LOWER(REVERSE(NAME1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_VENDOR_ADDR SET NAME2 =
TRANSLATE(LOWER(REVERSE(NAME2)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS1 =
TRANSLATE(LOWER(REVERSE(ADDRESS1)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS2 =
TRANSLATE(LOWER(REVERSE(ADDRESS2)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS3 =
TRANSLATE(LOWER(REVERSE(ADDRESS3)),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET ADDRESS4 =
TRANSLATE(REVERSE(ADDRESS4),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET COUNTRY =
TRANSLATE(REVERSE(COUNTRY),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR SET POSTAL =
TRANSLATE(REVERSE(POSTAL),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR_ADDR_PHN SET PHONE =
TRANSLATE(REVERSE(PHONE),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET VENDOR_NAME_SHORT =
TRANSLATE(REVERSE(VENDOR_NAME_SHORT),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET VNDR_NAME_SHRT_USR =
TRANSLATE(REVERSE(VNDR_NAME_SHRT_USR),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_VENDOR SET NAME1 =
TRANSLATE(REVERSE(NAME1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');


-----------PS_PERSONAL_DATA

UPDATE PS_PERSONAL_DATA SET NAME =
TRANSLATE(REVERSE(NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_PERSONAL_DATA SET
LAST_NAME_SRCH=TRANSLATE(REVERSE(LAST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME_SRCH=TRANSLATE(REVERSE(FIRST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

UPDATE PS_PERSONAL_DATA SET LAST_NAME=TRANSLATE(REVERSE(LAST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME=TRANSLATE(REVERSE(FIRST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,ADDRESS1=TRANSLATE(REVERSE(ADDRESS1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2=TRANSLATE(REVERSE(ADDRESS2),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3=TRANSLATE(REVERSE(ADDRESS3),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1_OTHER=TRANSLATE(REVERSE(ADDRESS1_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2_OTHER=TRANSLATE(REVERSE(ADDRESS2_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3_OTHER=TRANSLATE(REVERSE(ADDRESS3_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS4_OTHER=TRANSLATE(REVERSE(ADDRESS4_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
;
COMMIT;

----------------PS_PERS_DATA_EFFDT

UPDATE PS_PERS_DATA_EFFDT SET NAME=TRANSLATE(REVERSE(NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,LAST_NAME_SRCH=TRANSLATE(REVERSE(LAST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
,FIRST_NAME_SRCH=TRANSLATE(REVERSE(FIRST_NAME_SRCH),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, LAST_NAME=TRANSLATE(REVERSE(LAST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, FIRST_NAME=TRANSLATE(REVERSE(FIRST_NAME),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1=TRANSLATE(REVERSE(ADDRESS1),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2=TRANSLATE(REVERSE(ADDRESS2),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3=TRANSLATE(REVERSE(ADDRESS3),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS1_OTHER=TRANSLATE(REVERSE(ADDRESS1_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS2_OTHER=TRANSLATE(REVERSE(ADDRESS2_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS3_OTHER=TRANSLATE(REVERSE(ADDRESS3_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
, ADDRESS4_OTHER=TRANSLATE(REVERSE(ADDRESS4_OTHER),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ')
;
COMMIT;

--------------BANK ACCOUNT DETAILS

UPDATE PS_BANK_ACCT_DEFN SET BANK_ACCOUNT_NUM =
TRANSLATE(REVERSE(BANK_ACCOUNT_NUM),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_BANK_ACCT_DEFN SET BANK_ACCT_KEY =
TRANSLATE(REVERSE(BANK_ACCT_KEY),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_EX_EE_PYMNT_DTL SET BANK_ACCOUNT_NUM =
TRANSLATE(REVERSE(BANK_ACCOUNT_NUM),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

------------SECURITY

UPDATE PSOPRDEFN SET OPRDEFNDESC =
TRANSLATE(REVERSE(OPRDEFNDESC),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');

UPDATE PS_ROLEXLATOPR SET DESCR =
TRANSLATE(REVERSE(DESCR),'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_-, ','9876543210-abcde_ ,zyxwvutsfghijklmnopqrABCDEFGHIJKLMNOPQRSTUVWXYZ');
COMMIT;

------------------------END---------
Posted by at No comments:
Subscribe to: Posts (Atom)