Search This Blog

Sunday, January 29, 2017

Recommendation for using Excel to CI

Excel to CI - is not designed to load data for complex transactions that involves huge data set and also many peoplecode events.
- Especially, when you try to load under these circumstances you may end up seeing certain xml message as No response from server. Login cleared etc.
- Try to load with small chunks of data when the load is data intensive.
- You may need to be aware that server handles these as multiple transactions and chances are that you end up errors sometimes.
- If the need is still to load huge volume of data at once, you may need to go for using File Layout and AE to load the data.

Saturday, October 8, 2016

Oralce: SQL to find the BLANK or TAB character in a string.

Below is the SQL that can be used to identify the BLANK characters or TAB characters present at the end of the string.

SELECT UPPER(ADDRESS1),EMPLID FROM PS_ADDRESSES
WHERE regexp_like(ltrim(UPPER(ADDRESS1)), '[[:space:]]$');

Sunday, May 1, 2016

Oracle SQL: Error while Inserting text containing single apostrophe or single quote

When we try to insert data containing single apostrophe we end with error saying ' missing comma' as shown below.




To overcome this error, we need to use Q[string].
This will indicate Oracle that we have a string that contains single quote which needs to be inserted.

Now, let's do a select to view the result.



Thursday, March 31, 2016

SQL to find the Bundle applied on the current version.

SELECT A.UPDATE_ID, A.DESCR, A.DTTM_IMPORTED, A.FIXOPRID, A.APPLYOPRID, A.PRODUCT_LINE, A.RELEASEDTTM, A.RELEASELABEL, A.MAINTLOGTYPE, A.DESCRLONG FROM PS_MAINTENANCE_LOG A WHERE upper(A.DESCRLONG) like '%BUNDLE%' ORDER BY 2 DESC;

Saturday, March 19, 2016

ePerformance: Ability to Control the Display List of Rating Model Description.


In ePerformance rating values are shown in alphabetical order in the dropdown list. This is peopletools default functionality to display the values.

Not always the default rating order matches with the user defined order. Like suppose, users can come up with a rating which is not in alphabetical order.

In such case, we can use below code to display the custom sort order which would match the user defined order.

Below is the code which will help in sorting the rating as per user defined order:

/* Declarations */
Local SQL &SQL;
Local string &type, &descr;
Local string &rating_model;
Local Rowset &Xlat;

/* Field which shows the dropdown list on the page */
&FLD = GetRecord(Record.DERIVED_EP_ADVW).GetField(Field.REVIEW_RATING);
&rating_model = DERIVED_EP_ADVW.RATING_MODEL.Value;

/* Clear the drop down list */
&FLD.ClearDropDownList();

/* Create Rowset that should be filled with Rating values */
&Xlat = CreateRowset(Record.REVW_RATING_TBL);
&Xlat.Flush();

/* Code to fill the rowset */
&Xlat.Fill("WHERE RATING_MODEL = :1 and effdt = (select max(R1.EFFDT) from PS_REVW_RATING_TBL R1 WHERE R1.RATING_MODEL = RATING_MODEL AND R1.EFFDT <= SYSDATE ) order by EP_RATING DESC ", &rating_model);

/* code to fill the dropdown list */
&j = &Xlat.ActiveRowCount + 1;
For &i = 1 To &Xlat.ActiveRowCount
   &Value = &Xlat.GetRow(&i).REVW_RATING_TBL.REVIEW_RATING.Value;
   &descr = &Xlat.GetRow(&i).REVW_RATING_TBL.DESCR.Value;
   DERIVED_EP_ADVW.REVIEW_RATING.AddDropDownItem(&Value, Rept(Char(9), &j - &i) | &descr);
End-For;

Wednesday, March 9, 2016

File Layout : How to edit the field starting position for csv file

During development, we may come across requirements where we need to add fields to the file layout.
For file layouts of type CSV, we might have faced issues adding the length attribute for each field in the file layout.

Say , File Layout, accepts maximum 1000 characters in length and as of now there are 10 fields which take upto 950 characters.
If you want to add two new fields 10 char each , then the last field should start at position (950 + length of prior field).
At this stage, the starting position is shown as 1 and is Read Only. Thus we end up unsuccessful in editing the field.

To overcome this, we can change the type of file to TXT and then enter the start position. After that go back to File Type and revert back as CSV and save.

This should now show the new fields with new starting position.

Friday, February 12, 2016

Benefits Tables

Benefit Tables:

PS_DEPENDENT_VW -- Contains information related to Dependent Beneficiaries.
PS_BAS_ACTIVITY    -- Lists all events triggered for an Employee.