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:

Sunday, June 29, 2008

Bribery in india

General analysis about bribery in india.

Today, morning i came across the news that poor people in india pay money for their basic needs in the society, since i lived in india and i know that it is happening now. I have put some thinking on the matter and put forward my thinking in the topic (why bribery happens and its consequences).

  • first I like to undermine why that bribery is happening?

Even though the public services employees are being paid by the government, then why do they take bribes for the daily work they are supposed to do and provide to tax payers or people. Two things that i think that may move them to demand bribes for doing their regular task is, one peer pressure from their relatives and two they think people are getting free service in public hospitals (Public hospitals are for free serives, but they do have cost, tax money).

Lets examine why the peer pressure from relatives are pressuring them to accept bribes, lets see how an indian family works, since the indian families are closed families, means people communicate with their family members a lot, i mean family as people who also related to your mother,father,brothers,sisters and their respective relatives.
When there is some one who earns more in the family circle, then if i am in the family and work in a public service facility, I like to make money and to show off to the guy who has more money in the family(family in the sense i maent the outer cirlce).

When a guy with no education or less education works in the public facility, all he knows is the world around him in that he thinks the service offered here in the public facility is free and he can make money by asking for bribery.

  • next I like to speak about the general consequences of the bribery?
Consequence is that when somebody is taking bribe that they need to understand that they are not making money neither for them nor family nor for the nation, why, since if every body starts taking bribe then every body needs to pay bribe, thats it, it means null effect , such that you see more money in the wallet but it goes waste as un accounted again for the bribes that you need to pay for other services, so the bribe money cretaes a current of unaccounted money flow from the regular accounted money flow. so what happens when there is a whole lot of accounted money flow in the economy is that it basically wastes peoples tax money indirectly as the one who takes bribe also pays for the tax.

  • ways to avoid bribery in india
First thing is people need start thinking at macro economic level not at the level they are, for example, he needs to think how nice it will be if people stop taking bribes, that means every citizen in the nation will begetting free service in the hospitals, so that even though somebody is not earning a lot thinks that he can get good health care, so thats the way to create heaven in earth. To create heaven in india is wholly vested on the people in india.
Posted by at No comments:
Subscribe to: Posts (Atom)