Dealing with Time Stamp in XML Files

If you are using Time Stamp in XML Files and XML file is being generated by Application Engine (AE), then you may probably get some issue with the time part of the timestamp. The output will show only 12 Hr insted of 24 Hr value. To nullify this issue, syntax like below.

Let us suppose the data in DB is: 01-jan-2011 14:20:23

DateTimeToLocalizedString(FIELD_TIMESTAMP,'hh:mm:ss'); will give output as : 02:20:23

DateTimeToLocalizedString(FIELD_TIMESTAMP,'HH:MM:SS'); will give output as : 14:20:23

Use upper case always to avoid surprises.

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.