To get page level access in PeopleSoft

To get page level access in PeopleSoft:

-------------------------------------------------------------------------------------------------------------------------------------------------------

select x.menuname, x.barname, x.baritemname, x.pnlgrpname, x.itemname, x.itemlabel ,

(select distinct a1.classid from psroleclass a1, PSAUTHITEM x1 where x1.menuname = x.menuname and x1.barname = x.barname and x1.baritemname = x.baritemname and x1.pnlitemname = x.pnlitemname and a1.classid = x1.classid and rolename = 'HM_GPR_SUPER_ADMIN')HM_GPR_SUPER_ADMIN,

(select distinct a2.classid from psroleclass a2, PSAUTHITEM x2 where x2.menuname = x.menuname and x2.barname = x.barname and x2.baritemname = x.baritemname and x2.pnlitemname = x.pnlitemname and a2.classid = x2.classid and rolename = 'HM_GPR_ADVANCE_ADMIN')HM_GPR_ADVANCE_ADMIN,

(select distinct a3.classid from psroleclass a3, PSAUTHITEM x3 where x3.menuname = x.menuname and x3.barname = x.barname and x3.baritemname = x.baritemname and x3.pnlitemname = x.pnlitemname and a3.classid = x3.classid and rolename = 'HM_GPR_ADMIN')HM_GPR_ADMIN,

(select distinct a4.classid from psroleclass a4, PSAUTHITEM x4 where x4.menuname = x.menuname and x4.barname = x.barname and x4.baritemname = x.baritemname and x4.pnlitemname = x.pnlitemname and a4.classid = x4.classid and rolename = 'HM_GPR_BONUS_ADMIN')HM_GPR_BONUS_ADMIN,

(select distinct a5.classid from psroleclass a5, PSAUTHITEM x5 where x5.menuname = x.menuname and x5.barname = x.barname and x5.baritemname = x.baritemname and x5.pnlitemname = x.pnlitemname and a5.classid = x5.classid and rolename = 'HM_GPR_SETUP_ADMIN')HM_GPR_SETUP_ADMIN,

(select distinct a6.classid from psroleclass a6, PSAUTHITEM x6 where x6.menuname = x.menuname and x6.barname = x.barname and x6.baritemname = x.baritemname and x6.pnlitemname = x.pnlitemname and a6.classid = x6.classid and rolename = 'HM_GPR_SETUP_READ')HM_GPR_SETUP_READ,

(select distinct a7.classid from psroleclass a7, PSAUTHITEM x7 where x7.menuname = x.menuname and x7.barname = x.barname and x7.baritemname = x.baritemname and x7.pnlitemname = x.pnlitemname and a7.classid = x7.classid and rolename = 'HM_GPR_BANK_UPDATE')HM_GPR_BANK_UPDATE

from (select distinct a.menuname, a.barname, a.baritemname, a.pnlitemname, p.pnlgrpname, p.itemname, p.itemlabel

FROM PSAUTHITEM a, pspnlgroup p

Where ((a.baritemname = p.pnlgrpname and a.pnlitemname = p.pnlname)

or (a.pnlitemname = p.pnlname and p.pnlgrpname = p.pnlname)

or (a.baritemname = p.pnlgrpname and p.pnlgrpname = p.pnlname))

-- where p.pnlgrpname = a.baritemname

-- and p.itemname = a.pnlitemname

--and pnlitemname = 'GP_DURATION'

and classid in (select distinct classid from psroleclass

where rolename like 'HM_GPR_%'and rolename not in ( 'HM_GPR_TEMP', 'HM_GPR_QUERY', 'HM_GPR_USERS'))

order by menuname, barname, baritemname, pnlgrpname, itemname) x

-------------------------------------------------------------------------------------------------------------------------------------------------------

-- security matrix read/write

select x.menuname, x.barname, x.baritemname, x.pnlgrpname, x.itemname, x.itemlabel ,

(select distinct decode(x1.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a1, PSAUTHITEM x1 where x1.menuname = x.menuname and x1.barname = x.barname and x1.baritemname = x.baritemname and x1.pnlitemname = x.pnlitemname and a1.classid = x1.classid and rolename = 'HM_GPR_SUPER_ADMIN')HM_GPR_SUPER_ADMIN,

(select distinct decode(x2.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a2, PSAUTHITEM x2 where x2.menuname = x.menuname and x2.barname = x.barname and x2.baritemname = x.baritemname and x2.pnlitemname = x.pnlitemname and a2.classid = x2.classid and rolename = 'HM_GPR_ADVANCE_ADMIN')HM_GPR_ADVANCE_ADMIN,

(select distinct decode(x3.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a3, PSAUTHITEM x3 where x3.menuname = x.menuname and x3.barname = x.barname and x3.baritemname = x.baritemname and x3.pnlitemname = x.pnlitemname and a3.classid = x3.classid and rolename = 'HM_GPR_ADMIN')HM_GPR_ADMIN,

(select distinct decode(x4.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a4, PSAUTHITEM x4 where x4.menuname = x.menuname and x4.barname = x.barname and x4.baritemname = x.baritemname and x4.pnlitemname = x.pnlitemname and a4.classid = x4.classid and rolename = 'HM_GPR_BONUS_ADMIN')HM_GPR_BONUS_ADMIN,

(select distinct decode(x5.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a5, PSAUTHITEM x5 where x5.menuname = x.menuname and x5.barname = x.barname and x5.baritemname = x.baritemname and x5.pnlitemname = x.pnlitemname and a5.classid = x5.classid and rolename = 'HM_GPR_SETUP_ADMIN')HM_GPR_SETUP_ADMIN,

(select distinct decode(x6.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a6, PSAUTHITEM x6 where x6.menuname = x.menuname and x6.barname = x.barname and x6.baritemname = x.baritemname and x6.pnlitemname = x.pnlitemname and a6.classid = x6.classid and rolename = 'HM_GPR_SETUP_READ')HM_GPR_SETUP_READ,

(select distinct decode(x7.displayonly ,0, 'Read/Write', 'Display Only') from psroleclass a7, PSAUTHITEM x7 where x7.menuname = x.menuname and x7.barname = x.barname and x7.baritemname = x.baritemname and x7.pnlitemname = x.pnlitemname and a7.classid = x7.classid and rolename = 'HM_GPR_BANK_UPDATE')HM_GPR_BANK_UPDATE

from (select distinct a.menuname, a.barname, a.baritemname, a.pnlitemname, p.pnlgrpname, p.itemname, p.itemlabel

FROM PSAUTHITEM a, pspnlgroup p

Where ((a.baritemname = p.pnlgrpname and a.pnlitemname = p.pnlname)

or (a.pnlitemname = p.pnlname and p.pnlgrpname = p.pnlname)

or (a.baritemname = p.pnlgrpname and p.pnlgrpname = p.pnlname))

-- where (a.pnlitemname = p.pnlname or a.baritemname = pnlname)

--where p.pnlgrpname = a.baritemname

--and p.itemname = a.pnlitemname

--and pnlitemname = 'GP_DURATION'

and classid in (select distinct classid from psroleclass

where rolename like 'HM_GPR_%'and rolename not in ( 'HM_GPR_TEMP', 'HM_GPR_QUERY', 'HM_GPR_USERS'))

order by menuname, barname, baritemname, pnlitemname, itemname) x

Trick to remove Blank lines in Text

1. copy the content to MS Word

2. Find: ^p^p

Replace: ^p

FYI: If we have complex blank lines the below procedure can help

There may be spaces and tabs that interfere with a normal ^p^p find/replace operation. If tabs and double spaces are not required by the other entries, I suggest this:

Find: ^t

Replace: (space)

Find: (space)(space)

Replace: (space)

(and do this until the find count is 0; remember to save the document every time)

Find: ^l

Replace: ^p

Find: ^p(space)

Replace: ^p

(do this until the find count is 0)

Find: (space)^p

Replace: ^p

(do this until the find count is 0)

Find: ^p^p

Replace: ^p

(do this until you're happy with the result)

Source: http://www.proz.com/forum/office_applications/114649-how_to_remove_empty_lines_in_ms_word_document.html

Restrictions on ROWNUM in SQL

We can't use ROWNUM in a query with greater than condition.

For Example, we have below queries.

SELECT * FROM EMPTABLE WHERE ROWNUM > 100

output: 0 rows returned.

SELECT * FROM EMPTABLE WHERE ROWNUM < 100

output: it returns all 100 rows.

It is because rownum is a dymanic number generated and assigned to the rows while fetching the data from database. which is something like counting from 1,2..... It can't read which is 100th row unless it knows previous rows starting from 1. Hence it doesn't work with greater than (>) condition.

Change the file name

Refer the below link:

http://www.labnol.org/software/tutorials/rename-multiple-files-bulk-excel-googl-docs/2481/

Please note that this can be used only for the filenames without space in it.

Dealing with Time Stamp in XML Files

If you are using Time Stamp in XML Files and XML file is being generated by Application Engine (AE), then you may probably get some issue with the time part of the timestamp. The output will show only 12 Hr insted of 24 Hr value. To nullify this issue, syntax like below.

Let us suppose the data in DB is: 01-jan-2011 14:20:23

DateTimeToLocalizedString(FIELD_TIMESTAMP,'hh:mm:ss'); will give output as : 02:20:23

DateTimeToLocalizedString(FIELD_TIMESTAMP,'HH:MM:SS'); will give output as : 14:20:23

Use upper case always to avoid surprises.

Difference between Delete, Truncate and Drop

Delete table -- will delete the rows from the table for that session/schema only.

Need to use explicit 'COMMIT' to reflect the changes across the schemas/sessions.

Truncate table -- will delete the rows from the table and an implicit 'COMMIT' command will be executed. Changes will be reflected across the schemas/sessions.

Delete table-- u can use ROLLBACK command to retrieve the deleted records

truncate table -- ROLLBACK can't be used

Delete table -- DML(Data Manipulation Language) Command 

Truncate table-- DDL (Data Definition Language) Command

Delete -- is row by row operation

Truncate -- will delete all the data from table

When ever we have to delete all the data in a table Truncate is suggested over Delete.

Drop will delete table structure also, where as Delete and Truncate will not delete Table structure.

XML File Error

If you are getting any of the following errors:

"Warning! Report access is not set."

or

"Unsupported output format", Try the following.

When you run the report, give the output Type as Web. That should resolve the issue.