Tuesday, December 22, 2009
To Find the Navigation in PIA using Pagename,ProcessName..etc
Using Process Name:
SELECT PRCSPNL.PRCSNAME, MENUGROUP || ' > ' || MENULABEL || ' > ' || BARLABEL || ' > ' || ITEMLABEL "Path"
FROM PSMENUITEM MENU, PS_PRCSDEFNPNL PRCSPNL, PSMENUDEFN MNUDFN
WHERE MENU.PNLGRPNAME = PRCSPNL.PNLGRPNAME
AND MNUDFN.MENUNAME = MENU.MENUNAME
AND PRCSPNL.PRCSNAME LIKE '%Process_name%'
Using Page name:
SELECT MENU.PNLGRPNAME, PNLGRP.MARKET, PNLGRP.ITEMNAME, PNLGRP.PNLNAME, MENUGROUP || ' > ' || MENULABEL || ' > ' || MENU.BARLABEL || ' > ' || MENU.ITEMLABEL "Path"
FROM PSMENUITEM MENU, PSMENUDEFN MNUDFN, PSPNLGROUP PNLGRP
WHERE MNUDFN.MENUNAME = MENU.MENUNAME
AND MENU.PNLGRPNAME = PNLGRP.PNLGRPNAME
AND PNLGRP.PNLNAME LIKE '%Page_Name%'
Using Component Name-1:
SELECT MENU.PNLGRPNAME, MENUGROUP || ' > ' || MENULABEL || ' > ' || BARLABEL || ' > ' || ITEMLABEL "Path"
FROM PSMENUITEM MENU, PSMENUDEFN MNUDFN
WHERE MNUDFN.MENUNAME = MENU.MENUNAME
AND MENU.PNLGRPNAME LIKE '%Component_name%'
Using Component Name-2:
SELECT DISTINCT REVERSE(LTRIM(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL),' >- '),' >- '))
FROM PSPRSMDEFN P
WHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT'
START WITH PORTAL_URI_SEG2 = '<Component Name>'
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME
AND PORTAL_NAME = PRIOR PORTAL_NAME
AND PORTAL_REFTYPE = 'F'
Following information is added by Rajinesh:
If you want to check the navigation of a component follow this navigation
" Main Menu --> People Tools --> Portal --> View Menu Item Detail". Here mention the component which you want to know the navigation....
ex: if you want to know the navigation of "ITEM_BILLING_GBL" component then in the search mention as "%ITEM_BILLING_GBL" and hit the search button.
As soon as we hit it, it will redirect us to the navigation. The Advantage using this is we can know all the permission lists, roles and the users who can access this component
Language-Sensitive fields and Related Language Records
I have several questions related to the language-sensitive fields and related language records:
What columns are considered to be language-sensitive.
How does PeopleSoft decide whether a particular column of a table is or is not language-sensitive?
Would any non-numeric and non-date fields be considered to be language-sensitive? Typically short-description, long description, longname, shortname are considered to be language-sensitive. Why are Translate value and Prompt table editable codes considered not to be language sensitive?
Is it a matter of choice whether any given table should have a related language table? In other words, is it technically possible for a table having only numeric columns, for example, to be defined to have a related language table? If so, what would be fields of such a related language table?
Can any one of the Peopletools tables (the ones beginning PS) have related language tables? Is this a technical feasibility?
Can any one of the application tables (the ones beginning PS_) have related language tables? For example, suppose there is only a key field and only non-language-sensitive fields in a certain base table, is it possible for such a table to have related language table defined? Even if this is technically possible, would it make sense?
Any inputs would be very helpful.
What columns are considered to be language-sensitive.
How does PeopleSoft decide whether a particular column of a table is or is not language-sensitive?
Would any non-numeric and non-date fields be considered to be language-sensitive? Typically short-description, long description, longname, shortname are considered to be language-sensitive. Why are Translate value and Prompt table editable codes considered not to be language sensitive?
Is it a matter of choice whether any given table should have a related language table? In other words, is it technically possible for a table having only numeric columns, for example, to be defined to have a related language table? If so, what would be fields of such a related language table?
Can any one of the Peopletools tables (the ones beginning PS) have related language tables? Is this a technical feasibility?
Can any one of the application tables (the ones beginning PS_) have related language tables? For example, suppose there is only a key field and only non-language-sensitive fields in a certain base table, is it possible for such a table to have related language table defined? Even if this is technically possible, would it make sense?
Any inputs would be very helpful.
Importance of 'Related Language Record'
I will update this blog once I understand the importance of 'Related Language Record'.
As of now, I would like to share come points I understand : there is a one to one relation between Related Language Record to Base Record. That means one 'Related Language Record' can be used for only one base record. SYSLANG-13 exception in SYSAUDIT will be raised if this rule is violated.
As of now, I would like to share come points I understand : there is a one to one relation between Related Language Record to Base Record. That means one 'Related Language Record' can be used for only one base record. SYSLANG-13 exception in SYSAUDIT will be raised if this rule is violated.
Thursday, December 3, 2009
Audit Record actions
Most us know that audit actions in peoplesoft are only A,C,D. But there 2 more audit actions also availble.
Those are K,N.
K is for Key Change Old Key
N is for Key Change New Key
Means If you change any non-key field value in the record. Audit record capture it as a change(C).
But if you change any key field value in the record, it records 2 rows in the audit record.
one row is with K as audit action ( old values before change)
one row is with N as audit action (new values after change)
Those are K,N.
K is for Key Change Old Key
N is for Key Change New Key
Means If you change any non-key field value in the record. Audit record capture it as a change(C).
But if you change any key field value in the record, it records 2 rows in the audit record.
one row is with K as audit action ( old values before change)
one row is with N as audit action (new values after change)
Performance Tuning for Reports
This is a one page list of simple guidelines to tune SQL without much DBA knowledge.
This is all you need to know to tune some reports from 2 hours (or more) to 20 seconds (or less). I have compiled this list during a report tuning assignment
where I achieved that kind of runtime improvements repeatedly.
There are many more tips but they tend to be more difficult to understand and there are many good books already written about this.
* Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
* If a view joins 3 extra tables to retrieve data that you do not need, don't use the view!
* When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
* Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you lose control and understanding of exactly how much task loading your query will generate for the system.
* Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
* WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
* WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
* In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
* Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
* Avoid writing where project_category is not null. nulls can prevent the optimizer from using an index.
* Consider using IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.
* Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt*3 > 26000.
* Avoid this: SUBSTR(haou.attribute1,1,LENGTH(':p_otc')) = :p_otc). Consider this: WHERE haou.attribute1 like :p_otc||'%'
* Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don't assume that an index was defined. Check and talk to your DBA if you don't find any.
* Consider replacing outer joins on indexed columns with UNIONs. A nested loop outer takes more time than a nested loop unioned with another table access by index.
* Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
* Consider NOT EXISTS instead of NOT IN.
* If a query is going to read most of the records in a table (more than 60%), use a full table scan.
* Try to group multiple sub queries into one.
If you remember nothing else ...
* Don't apply these guidelines blindly, EXPERIMENT: compare one method to another. Do NOT expect that one trick will work all the time.
* Educate yourself: read, read, read. It SAVES time!
Source: http://iherve.com/oracle/tune100.htm
This is all you need to know to tune some reports from 2 hours (or more) to 20 seconds (or less). I have compiled this list during a report tuning assignment
where I achieved that kind of runtime improvements repeatedly.
There are many more tips but they tend to be more difficult to understand and there are many good books already written about this.
* Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
* If a view joins 3 extra tables to retrieve data that you do not need, don't use the view!
* When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
* Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you lose control and understanding of exactly how much task loading your query will generate for the system.
* Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
* WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
* WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
* In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
* Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
* Avoid writing where project_category is not null. nulls can prevent the optimizer from using an index.
* Consider using IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.
* Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt*3 > 26000.
* Avoid this: SUBSTR(haou.attribute1,1,LENGTH(':p_otc')) = :p_otc). Consider this: WHERE haou.attribute1 like :p_otc||'%'
* Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don't assume that an index was defined. Check and talk to your DBA if you don't find any.
* Consider replacing outer joins on indexed columns with UNIONs. A nested loop outer takes more time than a nested loop unioned with another table access by index.
* Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
* Consider NOT EXISTS instead of NOT IN.
* If a query is going to read most of the records in a table (more than 60%), use a full table scan.
* Try to group multiple sub queries into one.
If you remember nothing else ...
* Don't apply these guidelines blindly, EXPERIMENT: compare one method to another. Do NOT expect that one trick will work all the time.
* Educate yourself: read, read, read. It SAVES time!
Source: http://iherve.com/oracle/tune100.htm
Wednesday, December 2, 2009
XML Some Points (Draft)
This post is related to PeopleSoft XML publisher:
1. If the input data is zero peoplesoft will not give any data in XML
1. If the input data is zero peoplesoft will not give any data in XML
2. If a variable is taken and sum of 10 values are done in RTF, if one of them is zero (means we dont have any data in XML) output will be 'NaN'. To avoid this we have to take individual value into a variable and assign it to zero if we dont have any data in XML.
Ex: Declare and Assign value to Variable:
Sum:
Monday, November 30, 2009
SQR Related Issues
Today i got a need to find out the line-number of the cursor in the SQR. After verifying the google and many other ways, i found how to do it.
SQR has some internal variables, using those we can find the line or column numbers of the cursor.
$current-column is to find out the current column where the cursor is.
#current-line is to find out the Current row where the cursor is.
-------------------------------------------------------------------------------------------------
we all use wrap to divide text into 2 or 3..n lines. when it happens usually the cursor won't come back to the top line where the actual string started printing. To make it happen, Use keep-top.
print $long_descr () wrap 20 5 keep-top
-------------------------------------------------------------------------------------------------
Introduction
I would like to write all the issues related to peoplesoft and it's reporting tools at one place. So that i or my friends can refer it in future whenever it is needed.
Subscribe to:
Posts (Atom)