SQL: List of objects in a project

SELECT OBJECTTYPE,
CASE OBJECTTYPE
  WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)
                           WHEN 0 THEN 'SQL Table in DB'
                           WHEN 1 THEN 'SQL View in DB'
                           WHEN 2 THEN 'Work Record'
                           WHEN 3 THEN 'Sub Record'
                           WHEN 5 THEN 'Dynamic View'
                           WHEN 6 THEN 'Query View'
                           WHEN 7 THEN 'Temporary Table'
                           ELSE 'Other Record or Deleted' END
  WHEN 1     THEN 'Index'
  WHEN 2     THEN 'Field'
  WHEN 3     THEN 'Field Format'
  WHEN 4     THEN 'Translate Value'
  WHEN 5     THEN 'Page'
  WHEN 6     THEN 'Menu'
  WHEN 7     THEN 'Component'
  WHEN 8     THEN 'Record PeopleCode'
  WHEN 9     THEN 'Menu PeopleCode'
  WHEN 10    THEN 'Query'
  WHEN 11    THEN 'Tree Structure'
  WHEN 12    THEN 'Tree'
  WHEN 13    THEN 'Access Group'
  WHEN 14    THEN 'Color'
  WHEN 15    THEN 'Style'
  WHEN 16    THEN 'Business Process Map'
  WHEN 17    THEN 'Business Process'
  WHEN 18    THEN 'Activity'
  WHEN 19    THEN 'Role'
  WHEN 20    THEN 'Process Definition'
  WHEN 21    THEN 'Process Server Definition'
  WHEN 22    THEN 'Process Type Definition'
  WHEN 23    THEN 'Process Job Definition'
  WHEN 24    THEN 'Process Recurrence Definition'
  WHEN 25    THEN 'Message Catalog'
  WHEN 26    THEN 'Dimension'
  WHEN 27    THEN 'Cube Definition'
  WHEN 28    THEN 'Cube Instance Definition'
  WHEN 29    THEN 'Business Interlink'
  WHEN 30    THEN CASE OBJECTVALUE2
                               WHEN '0' THEN 'SQL Object'
                               WHEN '1' THEN 'App Engine SQL'
                               WHEN '2' THEN 'Record View SQL'
                               WHEN '5' THEN 'Query for DDAUDIT or SYSAUDIT'
                               WHEN '6' THEN 'App Engine XML SQL'
                               ELSE 'SQL' END
  WHEN 31    THEN 'File Layout'
  WHEN 32    THEN 'Component Interface'
  WHEN 33    THEN 'Application Engine Program'
  WHEN 34    THEN 'Application Engine Section'
  WHEN 35    THEN 'Message Node'
  WHEN 36    THEN 'Message Channel'
  WHEN 37    THEN 'Message'
  WHEN 38    THEN 'Approval rule set'
  WHEN 39    THEN 'Message PeopleCode'
  WHEN 40    THEN 'Subscription PeopleCode'
  WHEN 41    THEN 'N/A'
  WHEN 42    THEN 'Component Interface PeopleCode'
  WHEN 43    THEN 'Application Engine PeopleCode'
  WHEN 44    THEN 'Page PeopleCode'
  WHEN 45    THEN 'Page Field PeopleCode'
  WHEN 46    THEN 'Component PeopleCode'
  WHEN 47    THEN 'Component Record PeopleCode'
  WHEN 48    THEN 'Component Record Field PeopleCode'
  WHEN 49    THEN 'Image'
  WHEN 50    THEN 'Style sheet'
  WHEN 51    THEN 'HTML'
  WHEN 52    THEN 'Not used'
  WHEN 53    THEN 'Permission List'
  WHEN 54    THEN 'Portal Registry Definitions'
  WHEN 55    THEN 'Portal Registry Structure'
  WHEN 56    THEN 'URL Definitions'
  WHEN 57    THEN 'Application Packages'
  WHEN 58    THEN 'Application Package PeopleCode'
  WHEN 59    THEN 'Portal Registry User Homepage'
  WHEN 60    THEN 'Problem Type'
  WHEN 61    THEN 'Archive Templates'
  WHEN 62    THEN 'XSLT'
  WHEN 63    THEN 'Portal Registry User Favorite'
  WHEN 64    THEN 'Mobile Page'
  WHEN 65    THEN 'Relationships'
  WHEN 66    THEN 'Component Interface Property PeopleCode'
  WHEN 67    THEN 'Optimization Models'
  WHEN 68    THEN 'File References'
  WHEN 69    THEN 'File Type Codes'
  WHEN 70    THEN 'Archive Object Definitions'
  WHEN 71    THEN 'Archive Templates (Type 2)'
  WHEN 72    THEN 'Diagnostic Plug In'
  WHEN 73    THEN 'Analytic Model'
  WHEN 79    THEN 'Service'
  WHEN 80    THEN 'Service Operation'
  WHEN 81    THEN 'Service Operation Handler'
  WHEN 82    THEN 'Service Operation Version'
  WHEN 83    THEN 'Service Operation Routing'
  WHEN 84    THEN 'Info Broker Queues'
  WHEN 85    THEN 'XLMP Template Definition'
  WHEN 86    THEN 'XLMP Report Definition'
  WHEN 87    THEN 'XMLP File Definition'
  WHEN 88    THEN 'XMPL Data Source Definition'
  ELSE 'Unknown ' || OBJECTTYPE END AS Object_Type,

CASE OBJECTTYPE
  WHEN 12    THEN OBJECTVALUE3
  WHEN 30    THEN CASE WHEN OBJECTVALUE2 = 0 THEN OBJECTVALUE1 /* SQL Object */
                       WHEN OBJECTVALUE2 = 1 THEN SUBSTR(OBJECTVALUE1, 1, 12)
                       WHEN OBJECTVALUE2 = 2 THEN OBJECTVALUE1 /* Record View SQL */
                       ELSE ' ' END
  WHEN 34    THEN TRIM(OBJECTVALUE1) || '.' || TRIM(OBJECTVALUE2)
  WHEN 62    THEN TRIM(SUBSTR(OBJECTVALUE1, 1, 12))
  ELSE OBJECTVALUE1 END AS NAME,
CASE
  WHEN OBJECTTYPE = 1    THEN 'Index: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 4    THEN 'XLAT: ' || OBJECTVALUE2 || '; Date: ' || OBJECTVALUE3 || '; ' ||
                             NVL((SELECT 'ShortName: ' || XLATSHORTNAME || '; LongName: ' ||
                                          XLATLONGNAME || '; Status: ' || EFF_STATUS
                                  FROM PSXLATITEM
                                  WHERE FIELDNAME = I.OBJECTVALUE1 AND FIELDVALUE = I.OBJECTVALUE2
                                     AND EFFDT = TO_DATE(I.OBJECTVALUE3, 'YYYY-MM-DD')
                                  ), 'XLAT Deleted')
  WHEN OBJECTTYPE = 7    THEN 'Market: ' || OBJECTVALUE2
  WHEN OBJECTTYPE = 8    THEN OBJECTVALUE1 || '.' || OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 9    THEN OBJECTVALUE2 || '.' || OBJECTVALUE3 || '.' || OBJECTVALUE4
  WHEN OBJECTTYPE = 12   THEN 'EFFDT: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 20   THEN 'Process Name: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(22, 40)   THEN OBJECTVALUE2 || '.' || OBJECTVALUE3
  WHEN OBJECTTYPE = 25   THEN 'Message: ' || OBJECTVALUE2 ||
                              ' (Message Set Descr: ' || OBJECTVALUE3 || ')'
  WHEN OBJECTTYPE = 30   THEN
                         CASE WHEN OBJECTVALUE2 = 0 THEN ' ' /* SQL Object */
                              WHEN OBJECTVALUE2 = 1 THEN 'AE Progam: ' ||
                                           SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                                           SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                                           SUBSTR(OBJECTVALUE1, 21, 8) || ' Type: ' ||
                                           DECODE(SUBSTR(OBJECTVALUE1, 29, 1), 'S', 'SQL',
                                                  'D', 'Do Select', 'W', 'Do While',
                                                  'H', 'Do When', 'N', 'Do Until',
                                           SUBSTR(OBJECTVALUE1, 29, 1))
                               WHEN OBJECTVALUE2 = 2 THEN ' ' /* Record View SQL */
                               ELSE ' ' END
  WHEN OBJECTTYPE = 38   THEN 'EFFDT: ' || OBJECTVALUE2
  WHEN OBJECTTYPE IN(39, 42, 44)   THEN OBJECTVALUE2
  WHEN OBJECTTYPE = 43   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) = 'OnExecute' THEN
                                     'Section: ' || SUBSTR(I.OBJECTVALUE2, 1, 8) || '; Step: ' ||
                                     OBJECTVALUE3 || '; Market: ' || SUBSTR(I.OBJECTVALUE2, 9, 3) ||
                                     '; Database: ' || TRIM(SUBSTR(OBJECTVALUE2, 12, 8)) ||
                                     '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE2, 21, 10))
                              ELSE 'Section: ' || OBJECTVALUE2 || '; Market: ' || OBJECTVALUE3
                                       || '; Database: ' || TRIM(SUBSTR(OBJECTVALUE4, 12, 8)) ||
                                       '; EFFDT: ' || TRIM(SUBSTR(OBJECTVALUE4, 21, 10)) END
  WHEN OBJECTTYPE = 46   THEN 'Market: ' || OBJECTVALUE2 || '; Event: ' || OBJECTVALUE3
  WHEN OBJECTTYPE = 47   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Event: ' || OBJECTVALUE4
  WHEN OBJECTTYPE = 48   THEN 'Market: ' || OBJECTVALUE2 || '; Record: ' || OBJECTVALUE3
                                         || '; Field: ' || TRIM(SUBSTR(OBJECTVALUE4, 1, 18))
                                         || '; Event: ' || TRIM(SUBSTR(OBJECTVALUE4, 19, 16))
  WHEN OBJECTTYPE = 55   THEN DECODE(OBJECTVALUE2, 'C', 'Content: ', 'F', 'Folder: ') || OBJECTVALUE3
  WHEN OBJECTTYPE = 57   THEN
                         CASE WHEN TRIM(OBJECTVALUE4) NOT IN(' ', ':', '.') THEN
                                  'Subclass: ' || TRIM(OBJECTVALUE2) || ':' || TRIM(OBJECTVALUE3)
                                   || ':' || TRIM(OBJECTVALUE4)
                              ELSE
                                 CASE WHEN TRIM(OBJECTVALUE3) NOT IN(' ', ':', '.') THEN
                                           'Subclass: ' || TRIM(OBJECTVALUE2) || ':' ||
                                           TRIM(OBJECTVALUE3)
                                     ELSE
                                         CASE WHEN TRIM(OBJECTVALUE2) NOT IN(' ', ':', '.') THEN
                                                  'Subclass: ' ||  TRIM(OBJECTVALUE2)
                                              ELSE ' '
                                         END
                                 END
                         END
  WHEN OBJECTTYPE IN(58, 63, 68, 81, 82, 83, 87, 88) THEN
                         CASE WHEN TRIM(OBJECTVALUE4) IS NOT NULL THEN
                                   TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)  || '.'
                                   || TRIM(OBJECTVALUE4)
                              ELSE
                                  CASE WHEN TRIM(OBJECTVALUE3) IS NOT NULL THEN
                                            TRIM(OBJECTVALUE2) || '.' || TRIM(OBJECTVALUE3)
                                  ELSE
                                      CASE WHEN TRIM(OBJECTVALUE2) IS NOT NULL THEN
                                                TRIM(OBJECTVALUE2)
                                           ELSE ' '
                                      END
                                  END
                              END
  WHEN OBJECTTYPE = 59   THEN TRIM(OBJECTVALUE2)
  WHEN OBJECTTYPE = 62   THEN 'AE Progam: ' || SUBSTR(OBJECTVALUE1, 1, 12) || '  Section: ' ||
                              SUBSTR(I.OBJECTVALUE1, 13, 8) || '  Step: ' ||
                              SUBSTR(OBJECTVALUE1, 21, 8)
  ELSE ' ' END AS EXTENDED_OBJ_NAME,
CASE OBJECTTYPE
  WHEN 0    THEN NVL((SELECT RECDESCR FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), ' ')
  WHEN 1    THEN NVL((SELECT IDXCOMMENTS FROM PSINDEXDEFN WHERE RECNAME = I.OBJECTVALUE1
                      AND INDEXID = I.OBJECTVALUE2), ' ')
  WHEN 3    THEN NVL((SELECT DESCR FROM PSFMTDEFN WHERE FORMATFAMILY = I.OBJECTVALUE1), ' ')
  WHEN 5    THEN NVL((SELECT DESCR FROM PSPNLDEFN WHERE PNLNAME = I.OBJECTVALUE1), ' ')
  WHEN 6    THEN NVL((SELECT DESCR FROM PSMENUDEFN WHERE MENUNAME = I.OBJECTVALUE1), ' ')
  WHEN 7    THEN NVL((SELECT DESCR FROM PSPNLGRPDEFN WHERE PNLGRPNAME = I.OBJECTVALUE1
                      AND MARKET = I.OBJECTVALUE2), ' ')
  WHEN 20   THEN NVL((SELECT DESCR FROM PS_PRCSDEFN WHERE PRCSTYPE = I.OBJECTVALUE1
                      AND PRCSNAME = I.OBJECTVALUE2), ' ')
  WHEN 32   THEN NVL((SELECT DESCR FROM PSBCDEFN WHERE BCNAME = I.OBJECTVALUE1), ' ')
  WHEN 33   THEN NVL((SELECT DESCR FROM PSAEAPPLDEFN WHERE AE_APPLID = I.OBJECTVALUE1), ' ')
  ELSE ' ' END AS DESCR
FROM PSPROJECTITEM I
WHERE PROJECTNAME = 'MY_PEOPLESOFT_PROJECT'
ORDER BY OBJECTTYPE, 2, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4

PS Query and magic of OPRID

OPRID is one of the "magic fields" that Query (and PeopleSoft) knows and treats specially. 

If you join in a record that has OPRID as a key but it is NEITHER a Search nor List Box then Query will automatically add the criteria of "(<record>.OPRID = <current user) AND " 

To see this in action, look at any of the operator preference tables (e.g. OPR_DEF_TBL_FS) and then look at the SQL it generated. Compare it to PSOPRDEFN where this criteria is NOT added. 

Enable the Advanced search option in Prompt by default

Go the component and change the setting to default search behavior to Advance then Advance Search will come by default in prompt as well.

Update matching rows in one table with data from another table

Update matching rows in t1 with data from t2

update t1
set (c1, c2, c3) =
(select c1, c2, c3 from t2
 where t2.user_id = t1.user_id)
where exists
(select * from t2
 where t2.user_id = t1.user_id)

The "where exists" part it to prevent updating the t1 columns to null where no match exists.

Getting rid of invalid Tablespace references

Suppose you import a big project to a DB, and notice that a number of tables in the Project refer to invalid (non- existing) tablespaces.

One way of updating this would be from AppDesigner.

Open Table. Select Tools-> Data Administration-> Set Tablespace, and set the correct Tablespace

But in case you have a large number of records having this problem, and don't want to go through the pain of updating it one by one, here's a backend update that will help.

UPDATE PSRECTBLSPC set DDLSPACENAME='PTAPP', DBNAME='PSPTDMO' where DDLSPACENAME in (<INVALID TABLESPACE>)

HyperLink of a file in PeopleSoft page

Usually files are stored on the Application Server or in the database as attachments, since it is the application server that does the processing and not the webserver. It is common to place the file on the appserver or in the database.This is better manageable and movable between environments.

After placing the file on the appserver or in the database you can create a link on a page and use the ViewAttachment function to link to the file. ViewAttachment() retrieve the file from the server and show it to the user and it may require ftp to be configured for its paramater URL.

If you really want to place it on the webserver, you can add the file to the root of PIA, something like
[PS_CFG_HOME]\webserv\peoplesoft\applications\peoplesoft\PORTAL.war\myfile.htm
or
[PS_HOME]\webserv\peoplesoft\applications\peoplesoft\PORTAL.war\myfile.htm

Now you can access the file directly via the webserver by defining a url like
http:/<server>:<port>/myfile.htm

Rowset Flush() and Select()

&SELECT_STRING = "WHERE 1 = 1";

if [some condition] then
&SELECT_STRING = &SELECT_STRING | " AND YOUR_FIELD = '" | &Variable | "'";
end-if;
[more conditions/fields as required]

&RS1 = GetLevel0()(1).GetRowset(Scroll.YOUR_RECORD_VW);
&RS1.Flush();
&RS1.Select(Record.YOUR_RECORD_VW, &SELECT_STRING);

Remove / Delete Directory and Its Contents in Linux/UNIX

rm -rf <DirectoryName>

Messagebox Yes No button

REM - confirmation from user;
&n_Check = WinMessage(MsgGetText(0, 0, "Content can not be changed once saved. Do you want to Continue?"), 4);

If &n_Check <> 6 Then
   /* Cancel save */
   MessageBox(0, " ", 20000, 6, "Your content has not been saved");
End-If;

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

&re = WinMessage(&job_SWHA, 1);

0 gives you 'OK'
1 gives you 'OK' and 'Cancel'
2 gives you 'Abort', 'Retry', and 'Ignore'
3 gives you 'Yes', 'No' and 'Cancel'
4 gives you 'Yes' and 'No'
5 gives you 'Retry' and 'Cancel'

The value of &re gives you which button user has selected. 

Some Strengths And Weaknesses Of PeopleTools

Strengths of PeopleTools:
Extensive Support for Effective Dated Logic
Built in Security Model/Framework
Navigation Built In
Rapid Development
Platform independence

Weaknesses of PeopleTools:

No Usage Tools
Long Learning Curve
Proprietary JavaScript Libraries
No Version Control

References:
strengths-and-weaknesses-of-peopletools

Rules for Buffer Allocation and enhancing performance

When a user chooses a search key from the search page, the component processor will build a set of SQL statements to retrieve data from the database and store it into the buffer.

The component processor uses a specific set of rules to do this. If a field from a record is referenced on a page, then all fields/columns from that record definition and all rows that are subordinate to the search key are retrieved into the component buffer.

There are, however, a few exceptions to this rule. The first exception has to do with the data at level 0. If the only fields at level 0 are the search key and alternate search key,then the component processor will only retrieve those fields and not the entire row. However, if there is at least one field that is not a search field on the page at level 0, the component processor will retrieve the entire row and load it into the buffer.The second exception deals with related fields. If you have a related field on the page, the component processor will only retrieve that field from the database and place it in the buffer. It will not bring in any other columns or rows from the record definition associated with the related display.Other fields may also be retrieved. These fields consist of the following types of fields:

• Derived/Work Field

• Translate Table Field

If an entire row of data was brought into the buffer, any PeopleCode program on any field event within that record definition may be accessed and executed. Here are a few other rules for PeopleCode and the buffer allocation.
• If derived/work fields are brought into the buffers, any PeopleCode on that specific field maybe performed.
• PeopleCode on the related/display record definition is NOT performed.

If the component processor retrieves all of the data that is subordinate to an occurs-level (say all the records at a level) or when it can’t find any more child record definitions, it will retrieve the next row of data at the current occurs-level. It will continue to work its way down through the occurs levels.

If it is at all possible, I highly advise you to use views as the primary record of scroll areas.Using views eliminates the need for related displays and can enhance PeopleCode efficiency.

PeopleCode Insert/update to a record

If All(&str_var1, &nbr_Num1, &nbr_Num2, &str_Var2, &d_Date1, &b_Exists) Then
   &REC = CreateRecord(Record.MY_RECORD);
   &REC.OPRID.Value = %OperatorId;
   &REC.FIELD1.Value = MY_RECORD.FIELD1.Value;
   &b_Update_Flag = &REC.SelectByKey();

   &REC.OPRID.Value = %OperatorId;
   &REC.FIELD1.Value = BITW_PERSON_DTL.FIELD1.Value;
   &REC.FIELD2.Value = MY_RECORD.FIELD2.Value;
   &REC.DATETIME_STAMP.value = %Datetime;

   If &b_Update_Flag Then
      &REC.Update();
   Else
      &REC.Insert();
   End-If;
End-If;

Installation Table

When you make a change within the Installation Table, you must sign off all clients, stop and restart your application server, and then sign back on again to have the change take effect

PeopleCode events: Did you know?

1. The Activate event is valid only for pages that are defined as standard or secondary. This event is not supported for subpages. 

2. SearchInit and SearchSave events will only execute if the Allow Search Events for Prompt Dialogs checkbox is selected for the search key’s record field properties in Application Designer. 

3. FieldChange PeopleCode is often paired with RowInit PeopleCode. In these RowInit/FieldChange pairs, the RowInit PeopleCode checks values in the component and initializes the state or value of page controls accordingly. FieldChange PeopleCode then rechecks the values in the component during page execution and resets the state or value of page controls.

To take a simple example, suppose you have a derived/work field called PRODUCT, the value of which is always the product of page field A and page field B. When the component is initialized, you would use RowInit PeopleCode to initialize PRODUCT equal to A × B when the component starts up or when a new row is inserted. You could then attach FieldChange PeopleCode programs to both A and B which also set PRODUCT equal to A × B. Whenever a user changes the value of either A or B, PRODUCT is recalculated.

4. The FieldFormula event is not currently used. Because FieldFormula PeopleCode initiates in many different contexts and triggers PeopleCode on every field on every row in the component buffer, it can seriously degrade application performance. Use RowInit and FieldChange events rather than FieldFormula.

5. Deleting All Rows from a Scroll Area

When the last row of a scroll area is deleted, a new, dummy row is automatically added. As part of the RowInsert event, RowInit PeopleCode is run on this dummy row. If a field is changed by RowInit (even if it’s left blank), the row is no longer new, and therefore is not reused by any of the ScrollSelect functions or the Select method. In this case, you may want to move your initialization code from the RowInit event to FieldDefault.

6. Do not use Error or Warning statements in RowInit PeopleCode. They cause a runtime error.

Do not put PeopleCode in RowInsert that already exists in RowInit, because a RowInit event always initiates after the RowInsert event, which will cause your code to be run twice.

Oracle SQL - Rows to Coulmn conversion

Pivoting techniques (Rows to Column conversion) is very possible in Oracle SQL. There are two methods used popularly for Pivoting in Oracle SQLO( From Oracle 8i) . They are:

1. Using Decode ( or CASE )

2. Using Sys_connect_by_path

1. Using Decode 

This method uses Decode and group by together. 

2. Using Sys_connect_by_path 

As Sys_connect_by_path is used along with hierarchical queries, we need to make any query in that format for doing the pivot . It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

Though Decode gives more flexibility, Sys_connect_by_path is recommended in case row values are unknown or not fixed.

PeopleCode Built-in Encryption Functions

The following functions are provided as built-in functions in PeopleCode for encryption/decryption:

    Encrypt/Decrypt

The syntax for these functions (as provided in PeopleBooks) is:

    Encrypt (KeyString, ClearText) returns CipherText

    Decrypt (KeyString, CipherText) returns ClearText

The Encrypt and Decrypt functions rely on a key string which is used as part of the encryption. Note that the key string can be blank so you can simply issue the commands Encrypt(ClearText) and Decrypt(CipherText).

&strCipherText = encrypt("", rtrim(ltrim(&strClearText)));

Use transfer function to skip search page while going from one component to another

We will need to write the peoplecode on FieldChange of Hyperlink/Pushbutton. 

PeopleCode fetches the values from source based on which we want to search in the destination component. e.g. var1, var2 and var3 are the values picked from source page. Now create the search record of destination component and pass the appropriate values from source to its key fields. 

Now write the peoplecode at the FieldChange of PushButton/Hyperlink of Source component in following format: 

Local Record &rc = CreateRecord(Record.{destination component search record name or level0 key record of page on which we are going});

&rc.{key/search field1.Value} = &var1;

&rc.{key/search field2.Value} = &var2;

Transfer(False, MenuName.{destination menu name}, BarName.{Bar name component is associated to}, ItemName.{destination component name}, Page.{destination page name}, "{action mode A/U/...}", &rc);

Change the sort order of a prompt

Append the char(9), the string with most char(9) will come first in order.

here is the example of same : 

Field.AddDropDownItem("L", Rept(Char(9), 5) | "Low"); 

Field.AddDropDownItem("M", Rept(Char(9), 4) | "Medium"); 

Field.AddDropDownItem("H", Rept(Char(9), 3) | "High");                                              

Disable the Save warning

PeopleTools function SetSaveWarningFilter. SetSaveWarningFilter(True) disables the Save Warning while SetSaveWarningFilter(False) enables the Save Warning.

This can also be achieved by setting to False the SetComponentChanged property of the rowset that was causing the issue.

As a general rule, if you know the rowset that is causing the issue then you should set the SetComponentChanged property to False since it makes for easier maintenance. The SetSaveWarningFilter option on the other hand, is more of a generic solution for cases where it’s difficult to find out exactly what is causing the issue.

SQL: Insert into a table from another table

insert into table1(COL1,COL2,COL3,COL4)
select col1,col2,col3,col4 from table2

insert into table FINAL (select * from STAGING)

updated on 26-Jul-2015:
If you get error like "ORA-12899: value too large for column emp_name (actual: 16, maximum: 15)" while inserting or updating data from one table to another, and if you have also tried LENGTH function, then there is something more to look into.

The usual reason for problems like this are non-ASCII characters that can be represented with one byte in the original database but require two (or more) bytes in the target database (due to different NLS settings).

Try below query to find out the reason:
SELECT * FROM staging WHERE lengthb(mycol) > 15

SQL: CREATE a table from another table

Syntax #1 - Copying all columns from another table

The syntax for CREATING a table by copying all columns from another table is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

For Example:

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The syntax for CREATING a table by copying selected columns from another table is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n FROM old_table);

For Example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
      FROM companies
      WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The syntax for CREATING a table by copying selected columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);

For Example:

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Frequently Asked Questions

---

Question: How can I create an SQL table from another table without copying any values from the old table?
Answer: To do this, the syntax is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table WHERE 1=2);

For Example:

CREATE TABLE suppliers
  AS (SELECT * FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

Tip About AE Actions

We know that CALL SECTION & SQL are mutually exclusive. Because dead lock will occur if both the actions point to the same table.

Ever you thought why not the dead lock is not happening with SQL and Peoplecode action, if both the actions points to a single table.

Lets take a AE example:

SECTION : main

STEP 1:

Action : Peoplecode

 we are updating the phone number of a particular employee.

ACTION : SQL

 We are fetching the phone number of the employee which we updated in the previous action.

the value what we update will be reflecting in the next action.

END STEP

END SECTION

We know the commit will occur only at the end of the step or section, then how come the changes what we done in tables using peoplecode actions are reflecting in the next action itself.

The reason behind this is implicit commit in "SQLExec".Whenever we write any code in sqlexec, it will be commited automatically. so the changes are reflecting in the next action itself in the previous example. Hence deadlock wont appear here with Peoplecode and SQL.

Such implicit commit is missing in case of SQL and call SECTION. thats the main reason for its mutually exclusiveness.

Index on View (Oracle)

A view is really just a stored SQL statement so if you want to create an index on a view, create the index on the base table instead. 

You can't even add constraints on view. Constraints such as Primary key and unique key do occupy spaces because they create a unique index in turn.  You can create a view with check option.

Note: Using DISTINCT, GROUP BY or UNION clause in a View implies that no join using an index can be done on this View. These keywords should be avoided.

Debug SQR

Command line Flag helpful in performance tuning is –S, requests the status of all cursors, which includes text of each SQL statement, number of times compiled and run and total number of rows selected

Direct URL of a page in PeopleSoft

To directly navigate to a page with portal navigation

Link: http://server/servlet_name/SiteName/PortalName/NodeName/content_type/content_id?content_parm

For components content parameters are : Page=pagename&action=action_value&key_id=key_value

If you do not specify the Page then it goes to the first page in component.

Action = A - Add, U - Update/Display, L - Update/Display All, C - Correction

KeyIDs are actual Field Name of the Search record that has search key attribute set.

To display a page and navigate to that page for e.g URL Maintenance Page the URL will be

https://servername/psp/ps/EMPLOYEE/EMPL/c/UTILITIES.URL_TABLE.GBL?Page=URL_TABLE&Action=U&URL_ID=CMDOCDB

Example:

https://servername:port/psc/instance name/EMPLOYEE/Node/c/ABC_MENU.ABC_COMP_CMP.GBL?page=ABC_PAGE_NM

ORA-00022: invalid session ID; access denied

Here is a quick fix.

Set the following value in psappsrv.cfg (appserver) and psprcs.cfg (batch server) for all the configured appservers and batch servers. You may have to reconfigure the appserver and batch servers and restart them.

DbFlags=8

Default value is DbFlags=0 which means use Persistent Secondary DB Connection.

Setting DbFlags=4 is not recommended by PeopleSoft, which completely disables the secondary database connection.

Setting it to 8 disables Persistent Secondary DB Connection, but it still uses on demand Secondary DB Connection for each request. This is required for using GetNextNumberWithGapsCommit (GNNWGC) function, which is internally used by PeopleSoft for workflow transactions to generate APPR_INSTANCE

If you do not do this, you may get row inserted in PS_APPR_INST_LOG with

APPR_INSTANCE = 0

which may cause, some undesired workflow routings.

The easiest solution I have found is

delete from ps_appr_inst_log where APPR_INSTANCE = 0

Please make sure that you backup the data and test it.

Here is some more information on GetNextNumberWithGapsCommit (GNNWGC) function from peoplebooks.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

Note. A secondary database connection is used to increment and retrieve record.field. The default behavior is to keep the secondary database connection persistent in order to improve performance for the next GetNextNumberWithGapsCommit usage. If the database administrator finds the persistent connection too high an overhead for the production environment (which should not be the case since PeopleSoft uses application server to mulitplex the database connection), the database administrator can change the default behavior to use an on-demand connection method. The persistent second connection is disabled using DbFlags bit eight in the application server and process scheduler configuration files. The second connection can be completely disabled using DbFlags bit four in the application server and process scheduler configuration files

In this resolution, PeopleSoft generally recommends to Set DbFlags=8. I hope they deliver this value by default in future peopletools releases.

Retrieve milliseconds from Oracle database and display it on a page.

You can use the following SQL to get milliseconds from Oracle 9i and higher and assign it to a Field of Type Time in PeopleSoft page. May Place this code in FieldDefault event.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_TIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties.

For Date Time Field, use the following SQL.

SQLExec("select TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD-HH24:MI:SS.FF') from dual", XX_TEST_DERIVED.XX_DATETIME);

Set the Time Formatting to HH:MI:SS:999999 in Field properties. Select Display Century and Display Time Zone in Page Field Properties to display complete date and time.

Page data is inconsistent with database

If you are getting this message, while saving the page, please refer to following resolution on MOS (My Oracle Support).

E-AS: How to Trace "Page data is inconsistent with database" error? [ID 655907.1]

Step 1: Set the following in psappsrv.cfg

TracePPR=1 

TracePPRMask=32767

Step 2: Go to the PIA and before logging on, add &trace=Y (uppercase y) at the end of the web address and press the keyboard Enter key. Check the the first 5 SQL options and for PeopleCode the 'List Program' and 'Each statement' options.

Step 3: Log in to PIA and reproduce the error.

Step 4: Check the trace, especially the portion which contains this information: 

PSAPPSRV.28630    1-167605 11.00.03    0.001 Record RO_LINE_ATTR.ROW_LASTMANT_DTTM database value "2007-06-11-10.58.44.000000" page value "2007-06-11-10.05.30.000000" 

The trace should clearly show the field which is producing the error as well as the values populated both on database side and page side.

One of the most common reason for this happening is:

You are using a view to select data into a Scroll based on a table and No Auto Select option is turned on. The number of columns in view and scroll area base table differs and additional column in base table contain not null values. You use Auto update for Scroll and change the value of the field in a scroll and save.

To resolve this issue, add the same number of  columns to view as the base table or if possible, make the same view as a base record for the scroll that you use for selection.

Other reason is using SQLExec in SavePostChange to update the Base Table Record and therefore changing the value.

How to Use CTRL+J

1. Microsoft Internet Explorer
- CTRL + J

2. Mozilla Firefox
- Hold down the CTRL key and quickly hit "J" TWICE

3. Google Chrome
Use either of below options:
- First press and hold J then press CTRL and release J.

- Press CTRL+SHIFT+J Twice

4. MS Edge or IE 11
- Press the "J" key and hold
- Press the "CTRL" key and hold
- Press 'SHIFT" key and hold
- Release the "J" key

PeopleSoft project migration - tips

1. Always set your PeopleSoft Application Designer Tools Options to:

• Insert object into project when modified and saved.

• Prompt for related objects

The benefit of setting these Tools parameters is that the work the developer just did stands a much better chance of actually being stored in the project.

2. Use the Save All feature early and often in order to reap the benefit of the above recommendation.

3. Insert role, permission lists and portal structure into the project if PeopleSoft online has been used to register

4. Always run Compare Reports between source and target data bases before migrating (copying) projects.

Running compare report set/reset the project upgrade checkbox automatically

5. Make sure the menu definition, component definition and page definition(s) are in the same project as the Permission List.

Export and Import DMS database scripts

Export:
Set output C:\temp\RawData.dat;

Set log C:\temp\RawData.log;

export ABC_RAW_TBL;
export ABC_DATA_TBL;

Import:

set input C:\temp\RawData.dat;
Import *

Check Box Select/Deselect All on Grid

The below function is to be used on a grid with multiple check boxes. Place the code behind a FieldChange event and users will have the option to Select or Deselect grid rows all at once.

Function selectAllRows(&rs As Rowset)

   Local number &i;

   Local Row &row;

   

   For &i = 1 To &rs.ActiveRowCount

      &row = &rs.GetRow(&i);

      /* Make sure we only select visible rows. */

      If &row.Visible = True Then

         &row.Selected = True;

      End-If;

   End-For;

end-function;

/*main line*/

Local Rowset &rs;

&rs = GetLevel0()(1).GetRowset(Scroll.scroll_table);

/*Call Function*/

selectAllRows(&rs);

The same code would work for multiple check boxes "Deselect All", just change the name of the function and line &row.Selected = True; to &row.Selected = False;

Make sure the Multiple Row (Check Box) is checked on the grid properties.

Launch a PSQuery from PeopleSoft webpage

Below delivered function could be used to launch a PSQuery in PS webpage. Place the peoplecode in record rowinit event.

Declare Function CreateQueryURL PeopleCode WEBLIB_QUERY.QRYGENFUNCS FieldFormula;

Your record name.your field name.Value = CreateQueryURL("yuor query name", True);

Example:

Declare Function CreateQueryURL PeopleCode WEBLIB_QUERY.QRYGENFUNCS FieldFormula;

If %Component = Component.ABC_COMPNM_CMP Then
   ABC_COMMON_WRK.URL_1.Value = CreateQueryURL("ABC_PSQRYNM_QRY", True);
End-If;

Custom sort in Grid

Custom sort of the grid can be achieved by using peoplecode. 

Create a Rowset for the grid and use the below code. 

&RowsetName.Sort(Record.Field1, "D", Record.Field2, "A"); 

The grid is sorted by Field1 in Descending and Field2 in Ascending order. 

Search Record and Row Level Security

Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data. You can restrict data by:

• User, by using the OPRID field.
• Primary permission list, by using the OPRCLASS field.
• Row security permission list, by using the ROWSECCLASS field.

To implement row-level security through a security view:

1. In Application Designer, insert one of the three row-level security fields (OPRID, OPRCLASS, ROWSECCLASS) into the record definition.
2. Configure the field as a Key, but not a List Box Item.
3. Save the record and build the view.
4. Use the record as the search record or query security record.

Now, when the user searches, the system dynamically adds a WHERE clause — that incorporates the security field — to the search SELECT statement. The value of the security field is based on the current user.

Oracle SQL Developer problem – enter / backspace / delete button not working – quick fix

To resolve the problem you have to go to:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

PeopleSoft Trace PeopleCode

One can generate a trace while the subscription PeopleCode get executed by using the following two functions in the subscription PeopleCode:

SetTracePC(0);

SetTraceSQL(0);

Example:

SetTracePC(384);
/* REM PeopleCode Statements to be traced */
Local string &St_temp_l;
/* More PeopleCode statements */
SetTracePC(0);

AE/SQR output files to show up in the Process Monitor/Report Repository

Application Engine Example

In tools 8.4x and beyond, any files opened using the GetFile will actually be automatically transfered to the Report Repository.

Local File &f;
&f = GetFile("summary_log.txt", "w", %FilePath_Relative);
&f.WriteLine("Hello World");
&f.close()

The key thing about this code is the %FilePath_Relative parameter. 

SQR Example

Here is a procedure that you can put in an SQC. A variable named $weboutputdir will be populated with the director where you want to open the file.

begin-procedure get-web-outdir

 if $sqr-platform = 'WINDOWS-NT'
    let $dirSep = '\'
 else
    let $dirSep = '/'
 end-if

begin-select
CDM.PRCSOUTPUTDIR
  let $weboutputdir = rtrim(&CDM.PRCSOUTPUTDIR, ' ')  ||  $dirSep

  FROM PS_CDM_LIST CDM WHERE CDM.PRCSINSTANCE = #prcs_process_instance

end-select
end-procedure

Integration broker - third party integration

In case of any issues, check the below steps:

1. Check if web server/s are up and running. 

2. See if communication between app server/s and web server/s are fine for specific port

3. Ensure firewall is allowing packets to pass through and there is no block. Do transaction from PIA and let network team monitor.

4. Telnet service is always running in App server/s and Web server/s

5. Password is encrypted in advanced gateway properties file

6. Ensure third party certificates are installed using pskeymanager and web servers has been rebooted

7. Check the host name has valid entry in host file (Start -> Run -> Drivers | etc\hosts file). Need to restart the computer after any change.

8. Third party has done the required configuration at their end.

PeopleSoft Coding Standard

1. Declaring Local Variables
- When writing PeopleCode, declare ALL local variables that will be used. Declaring the specific type of variable needed improves performance.
- When variables are declared, the PeopleCode editor can check the compatibility of variables used in an operation, or passed as function arguments during compile time.
- For programming languages that allow dynamic allocation of variables, it is a common programming error to misspell variable names. However, on the PeopleCode editor, when all variables are consciously declared, it is easy to check for misspelled variable names.

2. Usage of the %Table() meta-SQL When Writing SQL Statements
- When writing SQLExec statements, the common practice is to hard-code table/view names. For example:
SQLExec("Select FIELD1, FIELD2 From PS_RECORD_TBL Where CRITER1 = :1 AND CRITER2 > :2", &IN1, &IN2, &OUT1, &OUT2);
A problem with the above code is that information in the PeopleCode string literals cannot be classified by the compiler and saved into the PeopleTools reference tables.

Use the meta-SQL %Table() in SQL statements to refer to SQL tables or views. With a little effort, the above code can be transformed into the following:
SQLExec("Select FIELD1, FIELD2 From %Table(:1) Where CRITER1 = :2 AND CRITER2 > :3", Record.RECORD_TBL, &IN1, &IN2, &OUT1, &OUT2);

Standalone Rowset’s Fill Method

A standalone rowset can be populated with rows directly from the database by using the Fill method. It’s quite common to see where strings in the Fill method to reference the FILL correlation ID inside subqueries to limit the resultset.

Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

 However, if you observe it closely (ignoring related-language processing), the Fill method would basically just generate a SQL statement in the following manner:

SELECT {list of fields from primary record} FROM %TABLE({primary record}) FILL {wherestring}

Interestingly, it also means you could fill a rowset with data not coming from the primary record by using a UNION. For example (notice the fields doesn’t even have to match the primary record):

Local Rowset &rs = CreateRowset(Record.PSOPRCLS); /* Fields: OPRID, OPRCLASS */

&rs.Fill("WHERE 1=0 UNION SELECT OPRID, ROWSECCLASS FROM PS_ABS_DEPT2_VW WHERE EMPLID = :1", &emplid);

However, it might be handy for quick prototypes and filling message rowsets with data directly without the need for new SQL Views and CopyTo operation.

Run JavaScript on your PeopleSoft pages conditionally

Here, PeopleCode sets the logic that determines when the JavaScript code will run.

This is not as simple as dropping a HTML Area on your page and setting the script in PeopleCode. This is because the value in the HTML Area field remains and the JavaScript code will keep executing at subsequent page refreshes.

Steps:

Lets have a derived/work record TEST_WRK And field HTMLAREA (TEST_WRK – HTMLAREA).

1. Create a HTML definition as your javascript template. Include all the necessary user-defined javascript functions that you need. Eg Html Definition is TESTJS

<input type="hidden" name="USERJSINJECTION" value=""/>
<script type="text/javascript">
function addLoadEvent(func) {
  var oldonload = window.onload;
  if (typeof window.onload != 'function') {
   window.onload = func;
  } else {
   window.onload = function() {
     oldonload();
     func();
   }
  }
}
 
function user_function1() {
  window.open("","toolbar = no");
}
function user_function2() {
  alert('Hello from user javascript');
}
 
addLoadEvent(function() {
  %bind(:1)
});
</script>

2. At the scroll level 0 of your PS page, insert a HTML Area control. Assign this to the TEST_WRK.HTMLAREA field.

3. Again at scroll level 0 of your page, insert an editbox and assign this again to TEST_WRK.HTMLAREA. And Set the Following Field Property

a. On the Use tab, check Invisible and Modifiable by JavaScript.

b. On the General tab, set Page Field Name to USERJSINJECTION.

4. Now in PeopleCode, to execute your javascript function.

GetLevel0()(1).TEST_WRK.HTMLAREA.Value = GetHTMLText(HTML.TESTJS, "user_function1()");

Traversing component buffer - one-line "shortcut" example

F = GetLevel0()(1).EMPL_CHECKLIST(1).EMPL_CHKLST_ITM(1).EMPL_CHKLST_ITM.BRIEFING_STATUS;
      Rowset   Row      Rowset   Row     Rowset     Row     Record           Field

COBOL Trace

PART I – IDENTIFY THE TRACE SETTING

The trace setting that you choose to use will be determined by a single number that is the sum of the values corresponding to each option that you wish to include.   These are the values for each of the options available to you:

1 = SQL Statements

2 = SQL statement variables

4 = SQL connect, disconnect, commit and rollback

8 = Row Fetch (indicates that it occurred, not data)

16 = All other API calls except SSBs

32 = Set Select Buffers (holds values of columns to be selected.

64 = Database API specific calls

128 = COBOL statement timings

256 = Sybase Bind information

512 = Sybase Fetch information

So if you only wanted to trace the SQL Statements, SQL Statement variables and Row Fetch, you would use 11 (1 + 2 + 8 = 11).

If your analyst provides you with a setting to use, use that setting.

If you do not know which setting to use, use 255.

PART II – PROCESS SCHEDULER TRACING

1. Go to process type COBOL SQL and copy the parameter list

2. Search your COBOL process and go to Override parameters tab. Select override and paste the content from clipboard

3. Now locate the pair of forward slashes between INSTANCE and DBFLAG in the parameter list and insert your trace setting in between these two slashes (in my case I am using 255)

PART III – TRACING ON THE APP SERVER

Step 1 – Set the trace on the App Server Configuration File

TraceSQLMask is the variable that you will set.  Use the value that you determined in Part I.  If you do not know what setting to use, use 255.

Once the trace has been set on the App Server, ALL remote call cobol jobs will be traced, so use this judiciously in your production environment.  Once you have retreived the trace, be certain to turn it off.

Step 2 – Retrieve the trace

Once the job has been completed, you can turn off the trace at the App Server and retrieve the trace.  The trace will be located in:

<PSHOME>/Appserv/<domain>/Logs/

The name of the trace will start with ‘cobsql_xxxxxx.trc’ where xxxxxx is a date time stamp indicating when the trace was made.

Dynamically hide a column in grid

Use the below code:

GetGrid(Page.PAGE_NAME, "GRID_NAME").GetColumn("COLUMN_FIELDNAME").Visible = False

Ensure providing the COLUMN_FIELDNAME general tab of in page field property.

Finding navigation based on component name - II

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2, 

                                              l_portal_reftype VARCHAR2, 

                                              l_portal_objname VARCHAR2, 

                                              l_level          NUMBER, 

                                              l_type           VARCHAR2, 

l_count_max      INTEGER DEFAULT 10) 

RETURN VARCHAR2 

IS 

  pl_count              INTEGER := 0; 

  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname; 

  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0; 

  pl_portal_label       psprsmdefn.portal_label%TYPE := ' '; 

  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' '; 

  CURSOR cur_1 IS 

    SELECT portal_prntobjname, 

           portal_label, 

           portal_seq_num 

    FROM   psprsmdefn 

    WHERE  portal_name = l_portal_name 

           AND portal_reftype = l_portal_reftype 

           AND portal_objname = pl_portal_objname; 

BEGIN 

  WHILE pl_count <> l_level LOOP 

      pl_count := pl_count + 1; 

      EXIT WHEN pl_count > l_count_max; 

      OPEN cur_1; 

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num 

      ; 

      IF cur_1%found THEN 

        pl_portal_objname := pl_portal_prntobjname; 

      ELSE 

        pl_portal_label := ' '; 

        pl_portal_seq_num := 0; 

        EXIT WHEN cur_1%notfound; 

      END IF; 

      CLOSE cur_1; 

  END LOOP; 

  IF l_type = 'S' THEN 

    RETURN pl_portal_seq_num; 

  ELSE 

    RETURN pl_portal_label; 

  END IF; 

END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname, 

       a.portal_linkobjname, 

       a.portal_seq_num                              seq, 

       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L') 

             || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L') 

       || 

       Decode( 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '', 

                                                                  ' > ') 

       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L') 

       || Decode(a.portal_label, ' ', '', 

                                 ' > ' 

                                 || a.portal_label)) navigation, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6, 

       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7, 

       a.portal_uri_seg1                             menuname, 

       a.portal_uri_seg2                             component, 

       a.portal_uri_seg3                             market, 

       a.portal_urltext 

FROM   psprsmdefn a 

WHERE  a.portal_name = 'EMPLOYEE' 

       AND a.portal_reftype = 'C' 

       AND a.portal_prntobjname <> ' ' 

--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME' 

ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S') 

          || 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), 

Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S') 

|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')