First and last day of previous Month using Oracle SQL

To get the First and last day of previous month use following code snippet::

a) SYSDATE
select trunc(trunc(SYSDATE,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

 b)For any Input Date::
select trunc(trunc(TO_DATE('01-JAN-15','DD-MON-YYYY'),'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

Where '01-JAN-2015' is the input Date

PeopleSoft DB Link

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.
In a scenario where you are trying to access the finance data from HCM environment,at that particular point you can create a DB link to do the same.

CREATE public database link DBlink Name CONNECT TO Username IDENTIFIED BY password using 'databasename';

PS:In order to execute this query use a data mover.

Process Scheduler::Location of the Log and SQL Trace Files

In case if we are unable to view the log file from Peoplesoft process monitor there is another method to view the out file and the log file.

Navigate to the following location::

Operating System	Log Directory
Windows	PS_CFG_HOME\appserv\prcs\<Database Name>\LOGS
UNIX	PS_CFG_HOME/appserv/prcs/<Database Name>/LOGS
OS390 UNIX System Services	PeopleSoft Process Scheduler Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSPRCSRVLOG Distribution Agent Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSDSTSRVLOG

In the UNIX Box,Navigate to the location(HOME\appserv\prcs\<HCMDEV>\LOGS) ,let the Database Name be HCMDEV. At that particular location you will find many folders named after dates.Go to the recent date folder ,then you will come across folders named after the Report ID (which you can find in process monitor view trace page).Check for the apt report id and get your log file and  Out file.

Oracle SQL:: Convert a field from NOT NULL to NULL

In order to convert a field from NOT NULL to NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NULL;

In order to convert a field from  NULL to NOT NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NOT NULL;

Detail Page,Disable saving Page in Component Properties

Detail Page
Select a Component,navigate to the use tab in properties,you will find the Detail page tab.I was wondering what does this function do. I  did a lot of R & D which i am sharing with you folks.

The detail page relates back to the search record. It's used to give the user more information about the item being selected before it's selected. For example, assume that your search page returns two employees with the same name but different EmplIDs. The detail page could be used to show more information about the employee (department, job title, manager, etc.) so the user makes sure they choose the right one. 

The Detail Page option exists on the component properties, it has been dropped by Oracle. Please go through  8.9 PeopleBooks:: 'No longer used in PeopleSoft applications. This option appears for upgrade purposes only.'

Disable saving Page

Navigate to component Properties Use tab, we have the Disable saving Page option.This function is used to disable the saving option from your page.

Peoplebooks Explanation:Select when you want to hide the Save button in the toolbar and disable the Alt+1 (Save) hot key. Selecting this option prevents users from being prompted to save when exiting a page. However, it does not prevent using PeopleCode to save a page with the DoSave() or DoSaveNow() functions. This functionality can be helpful for applications in which the user is not making database changes and does not need to be prompted to save.

After enabling the Disable Saving Page Option

So if we want back the save option , go back to the component properties and uncheck the Disable Saving Option.Then navigate to the internet tab in component properties,then check the save option.once you are done save the properties.check the page you will have the save option.

ORA-01791: not a SELECTed expression

ORA-01791:	not a SELECTed expression
Cause:	There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action:	Remove the inappropriate ORDER BY item from the SELECT list and retry the statement. Once we remove the ORDER By clause the sql should work without any error.

Prompt not working in Peoplesoft

I tried to create a new page,added Emplid field and made it as the key and search key.
Then for this field Emplid,prompted the job table,but the value was not getting populated.
I searched many areas to figure it out but for my bad i was unable to resolve it.
then i found out that even though i have prompted the job table to Emplid field,it was fetching value from some other table.I checked the Job table ,Emplid field then i realized that Emplid field in job table was prompted to Person table.
The other option to solve this issue is to create a view of Job table,but i wanted to resolve it without creating a view,then i changed the Emplid field prompt from Job table to the Person table and hence i was able to populate the values correctly.

PeopleSoft Fluid User Interface

Hi Readers,you might have heard about PeopleSoft Fluid User Interface,Well this has been one good revolution that took place in PeopleSoft.Let's go ahead and see what is PeopleSoft Fluid Interface.
                                                              PeopleSoft UI is much more than a mobile Platform.The design consist of device independent design for mobiles,Tablets and desktops,offering the advanced users experience utilizing the latest rendering technologies including HTML 5 and CSS3.
PeopleSoft Fluid User Interface offers all users a rich and easily understood means with PeopleSoft.
Power and casual Users with the traditional Classic Desktop View and the new Users from the executive range with the fluid Interface.

PeopleSoft Fluid User Interface enables mobility in any device and it is truly responsive.It recognize the device form factor that you  are using to access PS and renders the layout that's optimized for it.

For example,the PeopleSoft Interface behaves responsively to a landscape and portrait modes in a tablet

Landscape Mode

Portrait Mode

Hidden Menu

Designed as a significant improvement over the PeopleSoft “classic” user interface, the PeopleSoft Fluid User Interface moves away from pixel-perfect page layout and provides greater flexibility with the enhanced use of cascading style sheets (CSS3), HTML5, and JavaScript. PeopleSoft application fluid pages scale gracefully from large screen devices, such as laptops and desktops, to the reduced viewing space of tablets and smartphones. Many commercial websites use a similar design model whereby the presentation and layout of information is adjusted dynamically to conform to the dimensions of the user’s device. The Fluid User Interface design approach gives developers just this type of control over the user experience. When a larger screen size (a screen with more “real estate”) is detected, the application content will adjust and conform accordingly to fill the space effectively. Similarly, if a smaller screen size is detected, non-essential information can be removed and the presentation of content will adjust to flow in a usable way. Like PeopleSoft “classic” pages, fluid page definitions are maintained within PeopleSoft Application Designer, and the application developer will have the ability to define and apply conditional formatting appropriate to smartphone, tablet, or large-screen devices. At runtime, PeopleTools will apply the corresponding formatting according to the device in use. An end user can interact with fluid pages using a conventional mouse and keyboard or using a touch interface, which is common on tablets and smartphones. These new pages are device-independent and will operate on any certified device browser

PeopleSoft :: Insert into Table using Select statement(illegal use of long datatype Error)

This note explains the inability to perform SQL: INSERT INTO...SELECT FROM when columns inserted/selected are of type LONG.
In this case we have multiple option to solve the issue,here we are refering to the PL/SQl using the cursor method to overcome this issue.
There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:
TO_LOB;
PL/SQL;
DBMS_XMLGEN;
Dictionary Long Application (oracle-developer.net utility).


Error::ORA-00997: illegal use of LONG datatype error

INSERT INTO TABLE_B

 (

ROW_ID,

LONG_COLUMN

)

SELECT ROW_ID,

LONG_COLUMN

FROM TABLE_A;

 COMMIT;

Where::

TABLE_A has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN (LONG)

TABLE_B has following fields

- ROW_ID (VARCHAR2(15)

- LONG_COLUMN2 (LONG)

Solution

DECLARE

 CURSOR c IS

 SELECT ROW_ID, LONG_COLUMN

 FROM TABLE_A;

 rc c%ROWTYPE;

 BEGIN

 OPEN c;

 LOOP

 FETCH c INTO rc;

 EXIT WHEN c%NOTFOUND;

 INSERT INTO TABLE_B

 (

 ROW_ID,

 LONG_COLUMN2

 )

 VALUES

 (

 rc.ROW_ID,

 rc.LONG_COLUMN

 );

 END LOOP;

 COMMIT;

 END;

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.