Archive for June, 2013


PeopleSoft PeopleTools Meta-Tables

PeopleSoft Projects

PSPROJECTDEFN table stores information about projects created in Application Designer.
PSPROJECTITEM table stores objects inserted into your Application Designer project.
Portal Structure
PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.
XLAT Tables
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Record & Field Tables

PSRECDEFN:
CASE RECTYPE
WHEN 0 THEN ‘Table’
WHEN 1 THEN ‘View’
WHEN 2 THEN ‘Derived’
WHEN 3 THEN ‘Sub Record’
WHEN 5 THEN ‘Dynamic View’
WHEN 6 THEN ‘Query View’
WHEN 7 THEN ‘Temporary Table’
ELSE TO_CHAR(RECTYPE)
END CASE
PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Contains 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.
CASE FIELDTYPE
WHEN 0 THEN ‘Character’
WHEN 1 THEN ‘Long Character’
WHEN 2 THEN ‘Number’
WHEN 3 THEN ‘Signed Number’
WHEN 4 THEN ‘Date’
WHEN 5 THEN ‘Time’
WHEN 6 THEN ‘DateTime’
WHEN 8 THEN ‘Image’
WHEN 9 THEN ‘Image Reference’
ELSE TO_CHAR(FIELDTYPE)
END CASE
PSDBFLDLABL: Stores field label information.
Continue reading

Advertisements

Sankashti Chaturthi 2013

Sankashti Chaturthi is celebrated every Hindu month,on the fourth day of Waning Lunar Phase i.e. Krishna Paksha. If Sankashti Chaturthi falls on a Tuesday it is called Angarika Chaturthi. Sankashti 2013 dates are listed below. You may want to refer to Panchang for exact timings. Many devotees of Lord Ganesha observe a fast on Sankashti Chaturthi / Angarika Chaturthi for the entire day which is broken after taking darshan of Moon followed by worshiping of Lord Ganesha

Observing Sankashti Chaturthi fast is believed to reduce problems (Vignas) as Lord Ganesha symbolizes the remover of Vignas and supreme lord of intelligence.

Date Day Chaturthi Name
01 January Tuesday Angarki Chaturthi
30 January Wednesday Sankashti Chaturthi
01 March Friday Sankashti Chaturthi
30 March Saturday Sankashti Chaturthi
28 April Sunday Sankashti Chaturthi
28 May Tuesday Angarki Chaturthi
26 June Wednesday Sankashti Chaturthi
25 July Thursday Sankashti Chaturthi
24 August Saturday Sankashti Chaturthi
22 September Sunday Sankashti Chaturthi
22 October Tuesday Angarki Chaturthi
21 November Thursday Sankashti Chaturthi
21 December Saturday Sankashti Chaturthi

Hindu Calendar

Hindu Calendar

Using %CurrentDateIn in PeopleSoft

A very useful PeopleCode Meta-SQL function.Below is an example on how %CurrentDateIn function will be resolved at run time.

The %CurrentDateIn meta-SQL variable expands to a platform-specific SQL
substring representing the current date in the Where clause of a SQL
Select or Update statement, or when the current date is passed in an
Insert statement.

And here is an example:

SELECT ‘X’
FROM dual
WHERE sysdate > %CurrentDateIn;

At run time, the SQL above will be resolved to look like this:

SELECT ‘X’
FROM dual
WHERE sysdate > To_Date(To_Char(SYSDATE, ‘YYYY-MM-DD’), ‘YYYY-MM-DD’);

This will also improve performance of a object at run time.

Using %Join in PeopleSoft

/*Syntax*/
%Join({COMMON_KEYS | COMMON_FIELDS}, join_recname  
[ correlation_id1], to_recname [ correlation_id2]  
[, override_field_list])

/*Use the %Join meta-SQL construct to dynamically build a Where clause joining one table to another. At runtime, the entire construct is replaced with a character string. */

Example:
 
SELECT J.JPM_DATE_5
 , J.JPM_PROFILE_ID
  FROM PS_JPM_JP_ITEMS J
 WHERE J.JPM_CAT_TYPE = ‘DEG’
   AND J.EFFDT = (
 SELECT MAX (J1.EFFDT)
  FROM PS_JPM_JP_ITEMS J1
 WHERE %Join(COMMON_KEYS,JPM_JP_ITEMS J1,JPM_JP_ITEMS J,EFFDT)

Expands to:

SELECT J.JPM_DATE_5 
 , J.JPM_PROFILE_ID 
  FROM PS_JPM_JP_ITEMS J 
 WHERE J.JPM_CAT_TYPE = ‘DEG’ 
   AND J.EFFDT = ( 
 SELECT MAX (J1.EFFDT) 
  FROM PS_JPM_JP_ITEMS J1 
 WHERE J1.JPM_PROFILE_ID = J.JPM_PROFILE_ID 
   AND J1.JPM_CAT_TYPE = J.JPM_CAT_TYPE 
   AND J1.JPM_CAT_ITEM_ID = J.JPM_CAT_ITEM_ID 
   AND J1.JPM_CAT_ITEM_QUAL = J.JPM_CAT_ITEM_QUAL 
   AND J1.JPM_CAT_ITEM_QUAL2 = J.JPM_CAT_ITEM_QUAL2

 You can aslo join on COMMON_FIELDS the same way you do on COMMON_KEYS.