PeopleSoft :: INSTR IN Oracle

Definition:
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating theposition of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.
Example Syntax:
INSTR( source_string, substring [, start_position [, occurrence ] ] )

source_string is the string to be searched.

substring is the character string to be searched for inside of source_string.

start_position is an optional argument. It is an integer value that tells Oracle where to start searching in thesource_string. If the start_position is negative, then Oracle counts back that number of characters from the end of thesource_string and then searches backwards from that position. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.

If the substring is not found in source_string, the Oracle INSTR function will return 0.

Notes:

Both source_string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. The value returned is of NUMBER datatype.


Both start_position and occurrence must be an integer of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER.

The first position in the source_string is 1, not 0.
The INSTR search is case sensitive.

PeopleSoft :: RTRIM IN Oracle

The RTRIM function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle RTRIM function examples and explore how to use the RTRIM function in Oracle/PLSQL.

1)RTRIM('Peoplesoft        ')

Result:Peoplesoft

2)RTRIM('Peoplesoft        ', '    ')

Result:Peoplesoft

3)RTRIM('1230000', '0')

Result:123

4)RTRIM('Peoplesoft123123', '123')

Result:Peoplesoft

5)RTRIM('123Peoplesoft123', '123')

Result:123Peoplesoft

6)RTRIM('Techxyxzyyy', 'xyz')

Result: 'Tech'

7)RTRIM('Tech6372', '0123456789')

Result: 'Tech'

The RTRIM function may appear to remove patterns, but this is not the case as demonstrated in the following example.RTRIM('Techxyxxyzyyyxx', 'xyz') Result: 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.
The RTRIM function can also be used to remove all trailing numbers as demonstrated in the next example.RTRIM('Tech6372', '0123456789') Result: 'Tech'

PeopleSoft :: Dynamic Prompting-Edit Table method

PeopleSoft enable us to use the Dynamic Prompting in two ways.
1)DERIVED record fields or popularly known as Edit Table method
2) Dynamic Views.

DERIVED record fields or popularly known as Edit Table method

There can be business case where based on the value on one field, the prompt for other field should change. The change may include the search fields or list box values displayed on the prompt page. It can also demand an entirely different data set to be prompted for the user. In this type of scenarios the action that we require at the back end is to point the prompt of the fields to different tables or views. To tackle this kind of scenarios you need to use derived work record fields.

Consider the case where you have a check box (called effective dated) and a department field on the page. If the check box is checked you need to show only all the effective dated departments on the prompt. Otherwise the prompt should list all the department codes and user should also have a search option to search with the effective date. This is an ideal scenario where you can use derived record field method.

Please follow these steps :
1)Add the field checkbox to your main record
2) Create a view, say PPSFT_DEV0002_VW, which will fetch the effective dated departments.
3) Create another view, say PPSFT_DEV0003_VW, which will fetch all the departments. Make sure to check the alternate search key and list box item property for the EFFDT field (this is for the stated requirement. You may have to create both the views as per your real requirement).
4)On the record field properties of the field department, give the prompt table name as %EDITTABLE (it is not mandatory to give %EDITTABLE all the time. You can give % and any field name present on the table DERIVED).

Please find the code snippet that is to be added to the field change event of the field Check box in your main record

/* If it is checked then use first view else use second view */
WinMessage("Hi", 0);
If Record.PPSFT_DEV0002.CHECK_BOX.Value = "Y" Then
/*Specify the prompt record name here. Appropriate field name should be used in place of EDITTABLE
PPSFT_DEV0002_WRK.DEPTID = GetRecord(Record.PPSFT_DEV0002_VW).GetField(Field.DEPTID).Value;
Else
PPSFT_DEV0002_WRK.DEPTID = GetRecord(Record.PPSFT_DEV0003_VW).GetField(Field.DEPTID).Value;
End-If;*/
DERIVED.EDITTABLE.Value = "PPSFT_DEV0002_VW";
Else
DERIVED.EDITTABLE.Value = "PPSFT_DEV0003_VW";
End-If;
Place the field EDITTABLE (or other fields which you have mentioned in the previous step) to the page where your department field is present. This is mandatory because we will be referencing the field in PeopleCode program in next step. To reference the field in PeopleCode, it is required that the work record field is present on the component buffer. You can hide this field, because it will not make any sense to the business user.
The Edit table that we are placing as invisble on the page should have record name as DERIVED and Field Name as EDITABLE

Even we can try the same code with field default event and see what difference it makes.

PeopleSoft :: Prompt function in Peoplesoft-Dynamic Views.

Dynamic Views can prove to be powerful prompting mechanism when you come across a requirement where the values fetched by the prompt table needs to be changed based on some system variables such as %OperatorId or %EmployeeId. Take a situation where you want to filter the data present in the prompt table based on the logged in user. In this case you need to use dynamic views as a prompt table.
Take a situation where you have an address id as a field on the page. Your requirement will be to bring up the address id’s for the particular logged in user alone. But if a system administrator logs in, then you should display address id’s corresponding to all the users. In this case you have to use up dynamic views. The step should be followed will be as follows.
1. Create a record (say PROMPT_DVW) and add necessary fields that need to be prompted.
2. Set up the key structure in the way you would like the prompt page to be appeared.
3. Save the record as dynamic view type. No need to build or write sql for these kinds of records.
4. On the record field property of the address id field, set the dynamic view as the prompt table.
5. Now on the appropriate event (RowInit, FieldChange etc) write the below code.

/* Note: ADDRESS_ID is the field which requires prompt. We are not writing anything on the dynamic view people code events. */
If %OperatorId = “Admin” Then
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE”;
Else
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE where EMPLOYEE_ID = ‘”|%EmployeeId|”’”;
End-If;
The SqlText property will dynamically act as a view sql and bring up the corresponding result in the prompt page.