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.