Difference between Delete, Truncate and Drop

Delete table -- will delete the rows from the table for that session/schema only.

Need to use explicit 'COMMIT' to reflect the changes across the schemas/sessions.

Truncate table -- will delete the rows from the table and an implicit 'COMMIT' command will be executed. Changes will be reflected across the schemas/sessions.

Delete table-- u can use ROLLBACK command to retrieve the deleted records

truncate table -- ROLLBACK can't be used

Delete table -- DML(Data Manipulation Language) Command 

Truncate table-- DDL (Data Definition Language) Command

Delete -- is row by row operation

Truncate -- will delete all the data from table

When ever we have to delete all the data in a table Truncate is suggested over Delete.

Drop will delete table structure also, where as Delete and Truncate will not delete Table structure.

XML File Error

If you are getting any of the following errors:

"Warning! Report access is not set."

or

"Unsupported output format", Try the following.

When you run the report, give the output Type as Web. That should resolve the issue.

XML Error when '&' is encountered

If we have the following Characters in Data part of XML File, We will get the following errors: ‘whitespace is not allowed at this location’ or ‘a name was started with an invalid character’. To overcome this error, replace the character with the following strings.

Audit Record on View

Case: A View is built on a Record and View is being used in a page. Suppose we add a Record level audit on the base record Changes from the page will not be captured in the audit record.

To capture the changes from PIA, we have to create an Audit on View level.

Publish/Generate an XML Report from a link in a PIA

We can generate XML report from a Link in PIA. Click a given link in PIA and your desired report will pop-up. Follow the below given steps:

1. Prepare a Query

2. Define XML Data Source and Report Definition.

3. Write the following code in relevant Event of peoplecode.

import JPM_PROFILES_MANAGER:CMP_PROFILE:BUS:ProfileIdentityController;

import HCR_JPM_PKG:Utilities:TextCatalog;

import PSXP_RPTDEFNMANAGER:*;

import HCR_JPM_PKG:Profiles:BUS:Profile;

Local Record &promptRec;

/********************************************************************************

* Function DeleteLocalFile

* deletes appserver local file

********************************************************************************/

Function DeleteLocalFile(&sFilename As string, &nPathType As number)

Local File &oFile;

try

If &sFilename <> "" And

FileExists(&sFilename, &nPathType) Then

&oFile = GetFile(&sFilename, "R", &nPathType);

If &oFile.IsOpen Then

&oFile.Delete();

End-If;

End-If;

catch Exception &Dummy

end-try;

End-Function;

Function Personal_Details_Change_Report(&TemplateID);

/*REPORT ON DEMAND*/

Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;

Local PSXP_RPTDEFNMANAGER:Utility &oUtil;

Local HCR_JPM_PKG:Profiles:BUS:Profile &oProfile;

Local string &sRptDefn, &sTemplateId, &sProfile_id, &sReportname;

Local string &sOutFileName, &sOutputFile, &sOprid;

Local string &RUNREPORT;

Local date &dAsOfDate;

Local string &sOutputFormat;

Local number &OutDestFormat;

/* high level variables*/

&RUNREPORT = "Y";

&dAsOfDate = &inAsOfDate;

&OutDestFormat = 0; /*0=Default 2=PDF 5=HTML 8=XLS 12=RTF*/

&sOprid = %OperatorId;

&sReportname = "HM_AWF_WAB02";

&sTemplateId = &TemplateID;

try

/* create report defn object */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sReportname);

&oRptDefn.Get();

/* Get the PSQuery prompt record to setup the keys */

&promptRec = &oRptDefn.GetPSQueryPromptRecord();

&promptRec.GetField(Field.EMPLID).Value = %EmployeeId;

&oRptDefn.SetPSQueryPromptRecord(&promptRec);

/* output format */

If &OutDestFormat = 0 Then

&sOutputFormat = " "

Else

&sOutputFormat = &oRptDefn.GetOutDestFormatString(&OutDestFormat);

End-If;

/*generate the report*/

&oRptDefn.ProcessReport(&sTemplateId, %Language_Data, &dAsOfDate, &sOutputFormat);

CommitWork();

/* display the output */

&oRptDefn.DisplayOutput();

/* cleanup */

DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

REM WriteToLog(%ApplicationLogFence_Level1, "*** XML Publisher View Report Job End: " | String(%Datetime) | "***");

end-try;

End-Function;

Note: We can give dynamic input parameters to the Query also. The following peace of code is used in the above example to realize the dynamic assignment of input parameters.

&promptRec = &oRptDefn.GetPSQueryPromptRecord();

&promptRec.GetField(Field.EMPLID).Value = %EmployeeId;

SQL Developer - F8 SQL History

F8 is the keyboard shortcut to get History of SQL Statements executed.

SQL Developer - SQL Editor File

If you are using SQL Developer and by mistake you have closed window without saving your SQL Editor File?

Don't worry you can get your SQL Statements in unsaved SQL Editor File. The following screen shots will help you to get the unsaved files.

1. Go to the similar path in your machine:

2. To get the recent file, sort the folders by recently changed date time (as shown in the screen shoot) and open the first folder.

FYI: Number of folders in ‘.history’ folder represents number of SQL Editor Files you have opened in your machine.

3. To get the latest file, sort the file by recently changed date time (as shown in the screen shoot) and open the first file. That is the SQL Editor File that you have recently changed and not saved.

Some Tricky SQLs - Leave Days Count

I have following Requirement:

I have to calculate number of 'No Pay Leaves' consumed by an employee in a given period(suppose in the month of Aug 2010). Can i get the desired count in one SQL?

Solution:

In HCM System there is a Dates Attributes Table with name: 'PS_TL_DATES_TBL' (Time and Labor Module). This record will simplify our requirement as shown in the following SQL.

SELECT COUNT(A.THE_DATE)

FROM PS_TL_DATES_TBL A,PS_GP_ABS_EVENT B

WHERE B.PIN_TAKE_NUM = (SELECT PIN_NUM FROM PS_GP_PIN

WHERE PIN_NM LIKE 'TP NPL LVE')

AND B.EMPLID = '000052'

AND B.EMPL_RCD = 0

AND A.THE_DATE BETWEEN B.BGN_DT AND B.END_DT

AND A.THE_DATE BETWEEN '26-AUG-2010' AND '28-AUG-2010' ;

Some Tricky SQLs FTE

I have a requirement where in I have to get starting date and ending date of an FTE Value. Sample Employee data is as follows. Can we create a View which will give my desired rages as shown in the following format.

Employee Data:

EMPLID EMPL_RCD EFFDT FTE

000051 0 01-jan-2008 1

000051 0 10-feb-2008 1

000051 0 10-mar-208 0.5

000051 0 10-apr-2008 0.5

000051 0 10-may-2008 1

000051 0 10-jun-2008 1

000051 0 10-jul-2008 0.5

Required output format:

EMPLID EMPL_RCD FTE FTE_BGN_DT FTE_END_DT

000051 0 1 01-jan-2008 09-mar-2008

000051 0 0.5 10-mar-2008 09-may-2008

000051 0 1 10-may-2008 09-Jul-2008

000051 0 0.5 10-jul-2008 null

If the above tables are not clear enough to understand, Below image will give clear picture of the table structure.

Some Tricky SQLs

Let’s suppose, In a Table, we have the following kind of data. I want to change the SEQUENCE_NUMBER field value to a running number, numbering starting with 1000. Can we write a single SQL statement to get desired result?

Table Name: TABLE1

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1

After executing SQL the data should be like this:

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1001
000071 Same Name 1002
000071 Same Name 1003
000071 Same Name 1004

The tricky part is that, we can not select single row with any Where condition. Can you suggest any solution?

Answer: Is simple

UPDATE TABLE1 SET SEQUENCE_NUMBER = ROWNUM + 1000;

Subquery

SELECT (SELECT DEPENDENT_BENEF FROM PS_DEP_BENEF_VW WHERE EMPLID = A.EMPLID AND DEPENDENT_BENEF = '01') DEPENDENT_BENEF FROM (SELECT EMPLID,EMPL_RCD FROM PS_JOB WHERE EMPLID = '000072') A;

This should work in HCM Instance. Query before From should select only one field and one row.
If we select more than one field, we will get the following error: ‘ORA-00913: too many values’
If subquery selects more than one row, we will get the following error: ‘ORA-01427: single-row subquery returns more than one row’

Dynamic Dropdown Values

&FLD = GetRecord(Record.HM_EMP_BEN_PLAN).GetField(Field.HM_BEN_PLAN_TYPE);
&FLD.ClearDropDownList();
&Xlat = CreateRowset(Record.PSXLATITEM);
&Xlat.Fill("WHERE FILL.FIELDNAME = 'HM_BEN_PLAN_TYPE' AND FILL.FIELDVALUE IN ('10','01') AND FILL.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM B WHERE B.FIELDNAME = 'HM_BEN_PLAN_TYPE' AND B.FIELDVALUE = FILL.FIELDVALUE AND EFFDT <= SYSDATE)");
&Xlat_cnt = &Xlat.ActiveRowCount;

For &I = 1 To &Xlat_cnt
&CodeIn = &Xlat.GetRow(&I).GetRecord(1).FIELDVALUE.Value;
&DescIn = &Xlat.GetRow(&I).GetRecord(1).XLATLONGNAME.Value;
&FLD.AddDropDownItem(&CodeIn, &DescIn);
End-For;

Syntax for Some Object Oriented Programming Language in PeopleSoft

&record.field.Enabled = False is equivalent to UnGray(RecordName.FieldName)
&record.field.Enabled = True is equivalent to Gray(RecordName.FieldName)

in Gray() and UnGray() functions we can not use Variable in place of record name.
Example:
case 1: This will not work
&RECORD = &LEVEL2_ROW.HM_EMP_BEN_D_VW;
Gray(&RECORD.HM_INCURRED_BY);

Case 2: This will work
&RECORD = &LEVEL2_ROW.HM_EMP_BEN_D_VW;
&RECORD.HM_INCURRED_BY.enabled = False;
This is very useful in Row Traversing where we can not use RecordName.FieldName.

Some more OOPS Syntax in PS:
&record.field.IsChanged;

Reports: Protecting PDF Output files

Please follow the link: http://blogs.oracle.com/peopletools/2010/05/protecting_pdf_files_and_xdocf.html

Can we use same record on level zero and level 1 as primary record?

Question: Can we use same record on level zero and level 1 and on both levels it is being used as primary record?
Answer is Yes. Hold on.... We have a condition here.

If we have effective date as one of the keys and all the fields after Effective date are being used in Level 1 then we can use same record on level zero and level 1 and on both levels it is can be used as primary record.

Otherwise, with other keys, it will not serve any purpose, though it is technically feasible.

Google Account Tracker

Google, gmail, orkut ....... these are the websites I open more than once in a day. There are many google hosted online products like Blog, Google Docs, picasa, youtube uses same login details as gmail. What can you do if you doubt someone else is also using your details? Some of my friends google account is being hacked and their orkut account is filled with junk photos and comments. Spam mails being triggered from their mail IDs for which they will apologize afterwards.

I came to see an option in gmail with which you we can know our past 10 visits to our account with IP address and location information. We will have an option to signout of all the other sessions and change password. Below pictures will show how we can use the option. Click on the image to view it clearly.

The following link helps to find location of a given IP address: http://www.ip2location.com/free.asp

Have a safe browsing.

Create XSD from XML file

In case you need the XSD file for bursting option within XML Publisher, you may like to use the below converter:

http://www.flame-ware.com/products/xml-2-xsd/default.aspx

Note: XML Publisher created using PS Query, we have build in option to create XML and XSD files. But if you are using Application engine to generate XML file you have to use the above given link.

Thanks to Murali for providing the above info.

PeopleTools Compare/Copy Projects

I would like to share some findings/issues with Compare Projects between instances.

1. Issue: XML Objects will not be migrated properly when you copy.
Resolution: If you have XML Objects in your project, make sure that you add XML Objects to your project again, after you compare and before migrate. or in other words after you add XML objects you should not compare.
2. Issue: Roles will not be Compared.
Resolution: Don't Panic, Compare Manually. Open PIA of Source and Destination and compare manually.
3. Issue: Processes Definitions and Job Definition will be shown in Compare report even after migration was successful.
Resolution: Compare manually from PIA.

Intro to XML/BI Publisher demo

For those who are not familiar with the XML/BI Publisher, you may find a simple demo that walks you through creating a simple XML Publisher report:

http://oukc.oracle.com/static09/opn/apps09/fusion/84690/020410_84690_DEMO.html

Issue: PeopleSoft Webserver(WebSphere) not getting Stopped

Issue: Not Able to stop PeopleSoft Webserver(WebSphere).
Symptoms: When trying to stop service with command 'stopServer.sh server1', it has shown the following message
0000000a SSLConfigMana I CWPKI0027I: Disabling default hostname verification for HTTPS URL connections.
0000000a AdminTool A ADMU3201I: Server stop request issued. Waiting for stop status.

and after that i neither get the following confirmation message nor error message:

0000000a AdminTool A ADMU4000I: Server server1 stop completed.

Resolution: Resolution is to kill the WebService process. Difficult part is to find process ID to be killed. there is a way to findout the process ID of the WebService. in the following path

PSHOME:/psoft/[instance name]/webserv/[Instance Name]/logs/server1/

'*.pid' file will contain Process ID of the WebService. Before you kill make sure that you kill child processes if any.

Fix for VMWare error: Error while opening virtual machine. This virtual machine appears to be in use

I received the following error when trying start a VMware machine :

'Error while opening virtual machine. This virtual machine appears to be in use.'

To resolve the issue, delete all of the '.lck' files in the directory given in the error message. This allowed to start VM.

Row Traversing

&LEVEL0 = GetLevel0();                                               --level zero rowset

&LEVEL0_ROW = &LEVEL0(1);                                            --Level Zero Row

&LEVEL1 = &LEVEL0_ROW.GetRowset(SCROLL.EMPL_CHECKLIST);              --level1 Rowset

For &I = 1 to &LEVEL1.ActiveRowCount                                 --for loop to get all the values till current row

   &LEVEL1_ROW = &LEVEL1(&I);

   &LEVEL2 = &LEVEL1_ROW.GetRowset(SCROLL.EMPL_CHKLST_ITM);

   For &J = 1 to &LEVEL2.ActiveRowCount

        &LEVEL2_ROW = &LEVEL2(&J);

        &LEVEL3 = &LEVEL2_ROW.GetRowset(SCROLL.EMPL_CHKLST_ITM_1);

        For &k = 1 to &LEVEL3.ActiveRowCount

                &LEVEL3_ROW = &LEVEL3(&k);

               &RECORD = &LEVEL3_ROW.EMPL_CHKLST_ITM_1;              --get record

               &Emplid = &RECORD.emplid.value;                       --get value

               Winmessage(“Employee ID: “|&Emplid);                  --to display all retrieved employee IDs online

        End-For;

   End-For;

End-For;

In this case for suppose we have 1 row in EMPL_CHECKLIST, 5 rows in EMPL_CHKLST_ITM, 10 rows in EMPL_CHKLST_ITM_1 for the same employee ID, then we will get 10 messages when the system executes given code.

Some more Row Traversing functions:

CurrentRowNumber(1);

Issue: Not Able to create an employee with Employee Record '0'

Issue: Some times in Peoplesoft system we can not create a new employee with Employee Record zero. In 'Organizational Relationships' page 'Empl Rcd' will be defaulted to '1'. Even if we try to overwrite it with '0' it will through the error: 'This Empl_rcd is already in use for this person in PER_ORG_ASGN. Choose a diffrent EMPL_RCD.' as in the following screen shot.
This happens because there is a row in database with Employee ID :'NEW'. Peoplsoft system shoould not allow to create an Employee with Empl ID 'NEW', because of some descripancies in the system Empl ID 'NEW' will be created.
Even in the same instance this issue can not be replicated. We can not use 'ID Delete' component to delete this Empl ID, because we can not get emplid in prompt table.

We can not find Empl ID 'NEW' in ID Dlelete Component. It is because Empl ID 'NEW' is not there in 'PERS_SRCH_ALL' View and Undelying Tables.

Resolution: Insert dummy row with Empl ID with 'NEW' into all the underlying records of the View 'PERS_SRCH_ALL' which are 'PS_PERSON', 'PS_PERS_DATA_EFFDT' and 'PS_NAMES'. Then proceed to run ID Delete process with emplid 'NEW'.

PeopleSoft Viewlets

This could be helpful.

Download from: http://download.oracle.com/peopletools/viewlets.html

I will update this blog with my understandings on Viewlets.

Applying Country Extension and Bundles

This Blog is regarding the steps to be followed while applying Country extensions and GP Bundles #8 and #9 in UAT and PROD instances considering the china Bolton issue encountered in DEV and DMO instances.
UAT and PROD are now at MP6 ML stage, Country extensions followed by GP Bundle #8 and #9 has to be applied. Following are the steps to be followed to apply Country extensions and GP Bundles in UAT and PROD instances:

1. Run SYSAUDIT and DDDAUDIT reports and make sure that they are clean.

2. Refer: 1-7,1-10 and 1-11 in “Installing_HRMS_Campus_Solutions_9[1].0_Applications.pdf”.

Note: This step may include bouncing application server regarding which CHRIS team has to be informed 2 Hrs prior to the action.

3. For country extensions, follow “HC9_MP6_GP_ML.pdf”. Please maintain a copy of all the log files.

4. China Bolt-on is prerequisite for China Country Extension. Follow “GP_China_bolton.pdf” for China Bolton. It is suggested not to use the record structure in China Bolton, as suggested in SR 3-970069241. Because China Bolt-on, which was released before GP Bundle 4, is having older version of Record Structures compared to MP6 ML. Instead skip the Project download step in China Bolton and proceed with Rule and Non Rule packages steps Page 22 “Installing Global Payroll for China Extensions” and page 26 “Installing the Global Payroll for China Extensions Multilingual Portion”.
HC9_MP6_GP_ML.pdf will be present in MP6 zip file. Unzip MP and follow the path: “MP6\HC9_MP6_ML\Cumulative\upd774233ml\upd774233_install_ml”.
China Bolt-on and China Bolt-on ML Zip folder and Installation guide can be found in Shared folders “\HRIS_HRMS\hcm9phase2\2000-Implement\070-Patches\China Bolt-on”

a. While Applying Non-Rule Package "CNSYS" as part of China Bolt-on, CNSYS_RECORDS_IMP.DMS throws Error: "SQL duplicate rows in SET_CNTRL_REC 1". Resolution would be to execute “DELETE FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = 'CHN' AND SETID <> 'CHN' “ followed by commit and rerun CNSYS_RECORDS_IMP.dms.

b. We will encounter error in “cnsyszhs_records_imp.dms” as part of Non-Rule Package “CNSYSZHS”. The error will be because of the mismatch in the record structure of “GPCN_PSLP_LANG”. The Script “cnsyszhs_records_imp.dms” will be looking for only 5 fields in the record whereas system will have 11 Fields.
Resolution would be:

i. In App designer record GPCN_PSLP_LANG will have 11 fields. Delete all the 6 fields GPCN_LBL_ACUM1 to GPCN_LBL_ACUM6.

ii. Alter the Table (with Build and Execute Option) and rerun “cnsyszhs_records_imp.dms”. Complete the China Bolt-on Rule and Non-Rule packages.

iii. in App designed add all 6 deleted fields in the record GPCN_PSLP_LANG, Alter the Table (with Build and Execute Option). Continue with the steps specified for China Country Extension.

5. Run DDDAUDIT and SYSAUDIT reports and make sure that both are clean.

6. Apply Bundle #8 and #9 after country extensions. Zip folders can be found in shared folders “\HRIS_HRMS\hcm9phase2\2000-Implement\070-Patches\Bundles\Bundle Zip folders”
GP Bundle #8: upd734686.zip
GP Bundle #9: upd765610.zip

a. We will encounter one more issue while applying GP Bundle #9. “Createviews.sql” throws error while attempting to create view “PS_GPFR_DA_SIT_VW”. It is because the view “PS_GPFR_DA_SIT_VW“ will be built on one of the non existing Records “PS_GPFR_AF_EE_SIT” in the system. We can ignore this View as it is related to Country France Global Payroll. Resolution would be to delete the steps related to the view “PS_GPFR_DA_SIT_VW” in “Createviews.sql” and run the script for the remaining views.

7. Run DDDAUDIT and SYSAUDIT reports and make sure that both are clean.

8. In DDDAUDIT report records with name PS_GPDE_SI_ACCDT will appear in database and Application Designer. This record is supposed to be deleted in GP Bundle 9 both from Database and application designer. But in application designer it is deleted but not from Database (we got the same record in all the instances in which Bundle 9 is applied). Drop the table from database. After deleting the table, make sure that DDDAUDIT is clean.

Error: The database is at release 8.48. The PeopleTools being run require databases at release 8.49

Error While loading application designer: The database is at release 8.48. The PeopleTools being run require databases at release 8.49.

Scenario: even though the PTDDL.SQL being run on Database is from 8.49 CD, we get this error.

Root cause: All Apps (Version 9) till date have been made on 8.48 tools...So as part of the installation process one needs to run the release script for 849 based on the kind of DB they have ... This is as per design ..
for further information refer: http://forums.oracle.com/forums/thread.jspa?threadID=863896&tstart=45

PeopleSoft Processes and Job Servers

The server in which this job runs will be independent of servers selected for individual processes, and will run on the server selected for job (if it is blank, default server for job will be selected).

China Bolt-on

In our project HRMS 9.0 was being implemented and as client was multinational company, we had to implement country extension to many countries, including China. In Oracle PeopleSoft document for China country Extension, it was suggested to install China Bolt-on as prerequisite to China country extension. China Bolt-On being released after GP Bundle #3, all the definitions included in China Bolt-on was as in GP Bundle #4. As our system was already on MP 6 which was including till GP Bundle #7, while installing China Bolt-on we got error in one of the DMS scripts which lead us to raise an SR in Metalink and we got to know all these information. Resolution for the issue was to implement MP6 on the system again. SR ID being 3-970069241 and China Bolton information is in 3-947771311.