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.