New People Tools Feature - PeopleSoft Test framework in People Tools - 8.51

People Tools 8.51 has new interesting feature – Test framework as part of people tools objects.

It will be an interesting feature for Testing/Functional and Development teams.

Find out how it works - demo videos.

Visual page compare and merge feature in Application Designer.

Visual way of comparing pages between source and target instances.

Visual Page compare and merge feature in Application Designer. – One of not more widely known feature in Application designer. It is available since People Tools - 8.46.

1) Easier/Visual way to find the differences between the pages in different instance.

2) Compare page without need to include them in any project.

3) Move or Merge the changes in the pages easily.

4) Easy to find the field properties differences.

In Application Designer select Tools -> Diff/Merge Page. This menu item only appears when you have a page definition open (which is probably part of the reason that this feature is not more widely known). You'd then select your target; either another database or an Application Designer project export file.

Pages from both the source and target instance is shown side-by-side and differences are marked in red. Very easily to analyze.

The PeopleSoft Application Designer displays a Property Value panel to show details about every difference or each changed page control. To the right, each page is displayed side-by-side enabling the visual comparison.

Ganesh Muralidhar

Pace-soft - Peoplesoft Trace file analyzer

PeopleSoft SQL Trace file analyzer. New version of Pace-soft trace both *.trc files and *.AET files.

Easily filter the SQL statements in the trace file.

Extracting the useful information from this file can be a significant hurdle. Loading the file in Pace-Trace, this information is made available to you in a clear format. Dramatically speeds the process toward your eventual solution.

e-learnings - a functional point

PAY_ONE_TIME - You would have heard about this table for making adjustments after pay run first calc. Point is, if you are adjusting by (an app engine for example) process, the adjustment entries may be not of PAY_CHECK's. Technically what I am trying to say is, "When you are making entries into PAY_ONE_TIME, dont join PAY_CHECK, just join PAY_EARNINGS with SINGLE_CHECK_USE in P or N. The Pay Calc will take care of pulling it off in Paycheck".

Peoplesoft Navigation of the setup components. - In simple query.

PS_PTLT_COMP_NAV – Define Components Navigation Setup record. - The record stores the PTLT_TASK_CODE which is the component name followed by the market name “.GBL”/Global extension

There is a catch –It doesn’t work for the transaction components like JOB_DATA.
This record is used in setup manager and hence doesn’t have details for the transactional components.

Ganesh Muralidhar

Below query gives for all tables (including hidden).. based the level of navigation (for example Main Menu --> Careers, for this two tables should be joined.. so if you are not getting the results, you have to keep reducing the no of joins :)) it is configured the self join table's count should be increased/decreased...

Oracle------
SELECT D.PORTAL_LABEL ' -->' C.PORTAL_LABEL' -->' B.PORTAL_LABEL ' -->' A.PORTAL_LABEL
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C,PSPRSMDEFN DWHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAMEAND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAMEAND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAMEAND A.PORTAL_URI_SEG2 ='JOB_DATA_HIRE'

MSSQL-----
SELECT D.PORTAL_LABEL+ ' -->'+C.PORTAL_LABEL+' -->'+ B.PORTAL_LABEL +' -->' + A.PORTAL_LABEL
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C,PSPRSMDEFN DWHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAMEAND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAMEAND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAMEAND A.PORTAL_URI_SEG2 ='JOB_DATA_HIRE'

Manivannan Ramar

Enable page Anchor

Just a quick thought of sharing this info. you would have heard about 'Enable page Anchor'. how to achive this.

1. The Anchor fields should be a Push Button or Hyperlink
2. On the General tab, Check the box, Enable as Page Anchor and give the Page Filed Name as 'Anchor1' (upto us)
3. Keep this button at the top of the page
4. Create one more field with the same properties (Give the page field name as Anchor2)

5. On both the fields, change the destination (on the Type tab) to Page Anchor
6. Once done, Action tab will be enabled on the same Type tab
7. Give the Action Type as Page Anchor
8. Give 'Related Control' as Anchor 2 for Anchor1 field and Anchor1 for Anchor2 field

Once done, if you click on Anchor1, the control goes to Anchor2 and vice versa.

Mani

Quick tips

• Feed dashboard in your PIA
  
  Auto Saving the PeopleSoft page using JavaScript
  
  Customizing the Help Link
  
  TREE generation using PeopleCode
  
  Forgot my password Implementation
  
  HEUG Online: Shared HCM-CS Tables
  
  Check for Table Lock
  
  Process status check
  
  Code for Attachment Launch
  
  Import/Export WebService

Customizing the Help Link

I have seen one requirement from the client, who needs to have their own custom help documents to be displayed along with the PeopleBooks hyperlink, when clicked on the help link. The only way to do it was to track down, from where the PeopleBooks data is being pulled from and add those client customized docs into the folder.

How to do the same? There is a post in the PeopleSoft pros website, where Derek Tomei explains how to accomplish this task.

His blog post is given below:

Using Custom Documentation as PeopleBooks:

Prior to creating custom PeopleBooks, you need to divide documentation and/or training manuals into logical units. For example, if you have a custom component with three custom pages, and the functionality of all three pages is contained within one training document, three distinct documents would have to be created. The information for each page would be used to create an .htm document (.pdf and .doc documents can also be used but .htm is better for searching). Copying the documentation for a page into a Word Web Page template does this.

If, for example, the three custom pages are Query_1, Query_2 and Query_3, the three .htm documents can be saved as qry01.htm, qry02.htm and qry03.htm. It is important, especially if there are numerous custom pages, to use a naming convention that is easy to keep in the order of the pages you are going to attach them to.

Here are 6 Easy Steps to Create Custom PeopleBooks
The following steps detail how to create custom PeopleBooks:

Step 1: Create a new folder, for example Query817, under the weblogic folder on your Web Server. The navigation for PeopleTools 8.1x should be weblogic\ myserver\public_html \htmldoc\eng. This is the same location as the psbooks folder that holds the delivered PeopleBooks.

Step 2: Create a folder under Query817 called Query. In this folder, create a .htm folder and copy the .htm custom query files here. The PeopleSoft delivered Query PeopleBooks are .htm documents and are located under psbooks\tpsq\htm.

Step 3: Copy the contextids folder from a delivered PeopleBooks folder, with all of it’s content, to the custom Query folder that was just created.

Step 4: Modify the content of Query\contextids\q (javascript file). Use q because all of the pages that will have custom documentation begin with Q. The name of this javascript file must match the first letter of the name of the page that will be accessing the documentation. Open the file in Notepad and use the following format to add the custom PeopleBooks. Delete or comment out the delivered code:

o The item in bold is the page name of the page that you are linking a particular .htm file to. This is easy to find by navigating to your custom PeopleSoft page and clicking the HELP link. Since there is currently no PeopleBooks for this custom page, a message will appear stating the nothing could be found for :

bookmarks[bookmarks.length] = "QRY_CRITERIA_FIELD||Query817/Query/htm/qry03.pdf#F1ID_QRY_CRITERIA_FIELD";

o After the page name, the path to the file that contains the documentation for the page:

bookmarks[bookmarks.length] = "QRY_CRITERIA||Query817/Query/htm/qry03.htm#F1ID_QRY_CRITERIA";

o After the file path, the name of the file containing the documentation for the page:

bookmarks[bookmarks.length] = "QRY_FIELDS_SEC||Query817/Query/htm/qry02.htm#F1ID_QRY_FIELDS_SEC";

o After the file name, #F1ID_ and the name of the page:

bookmarks[bookmarks.length] = "QRY_FIELDS||Query817/Query/htm/ qry 02. htm #F1ID_QRY_FIELDS";

bookmarks[bookmarks.length] = "QRY_PROMPT||Query817/Query/htm/qry 02. htm #F1ID_QRY_PROMPT";
bookmarks[bookmarks.length] = "QRY_PROMPT_SELECT||Query817/Query/htm/qry 02. htm #F1ID_QRY_PROMPT_SELECT";
bookmarks[bookmarks.length] = "QRY_PROPERTIES||Query817/Query/htm/qry 05.htm#F1ID_QRY_PROPERTIES";
bookmarks[bookmarks.length] = "QRY_QUERY_HIERJOIN||Query817/Query/htm/qry 05.htm#F1ID_QRY_QUERY_HIERJOIN";
bookmarks[bookmarks.length] = "QRY_QUERY||Query817/Query/htm/qry 01.htm#F1ID_QRY_QUERY";

arraycount[arraycount.length] = bookmarks.length - 1;

These entries link the online Query pages to the corresponding custom PeopleBooks .htm file in the new folder (as described above).

Step 5: Update the following javascript files under weblogic\myserver\public html\htmldoc\eng\js:

o colltypes
o doctypes
o helptypes

In each of these files add the following (in bold):

helptypes = new Array(
"psbooks||PeopleBooks",
"dftie||Transactional Help",
"Query817||Query"
);


Step 6: Update the following javascript files under weblogic\myserver\public html\htmldoc\eng\psbooks\js:

o booklist
o colllist
o helplist

For example, in colllist, insert the following code:

o colllist[colllist.length]="Query817/Query||PeopleTools 8.17:Query||eng/Query817/Query/coll||";

In booklist, insert the following code:

o booknames[booknames.length]="Query817/Query||PeopleTools 8.17: Query";

In helplist, insert the following code:

o helpnames[helpnames.length]="Query817/Query||PeopleTools 8.17: Query";

Conclusion:
Now, the custom PeopleBooks will be accessible through the HELP link when the user is in the custom Query page. Follow these procedures for any custom PeopleBooks that you want to create for your custom pages.

Auto Saving the PeopleSoft page using JavaScript

It was one year back, while I was working with my previous company, one of my colleague implemented an auto save functionality for Performance appraisal data entry page. The requirement was to provide an auto save in the peoplesoft page after a certain interval, as there happened some instances, which the employees enter big novels about their performance and by mistake they close without saving the page.

Just now I thought of searching for that code around and I got it from will bailey it toolbox thread. Download the code below. This is an example for jQuery implementation also.

iScripts

This is an area which I never worked before. This blog post by Jim gives you the best on iScripts. Go ahead and read the same.

Below screenshot gives you the difference between a regular URL and an iScript URL.

How do I create one?

--Create the PeopleCode in a Weblib

--Add Security for your IScript

--Create the link for your IScript

Create the PeoplCode in a Weblib

--Weblib’s are like Funclibs. They should be Derived Work Records. Usually a single field (Funclib) is enough.

--Place your code in the FieldFormula Event

--Each Iscript Function must begin with the string Iscript_ (i.e. Iscript_HellowWorld)

Add The Security

--Access to Weblibs is granted via Permission Lists

--Special Weblib tab on the Permission List tab

--Access is granular to the Iscript (Function) level

Create the Link

--Where am I accessing the weblib from?

--If from PeopleCode/Page use Button HyperLink type field of at least 254 length!

--Set as External Link, Dynamic URL

--Use deleivered functions to set field value. (i.e. GenerateScriptContentURL)

--Or you can use a Content Reference.

Few points worth to be noted:

--you can generate an IScript URL using the PeopleCode built-in function GenerateScriptContentURL.

--if you just want to view/print the component without the header and menu, then you can use the GenerateComponentContentURL function to create a URL to the component that does not include the portal markup. The GenerateComponentContentURL function creates a URL that matches the CREF's URL but uses the psc servlet rather than the psp servlet.

--"is this a psp or psc URL? I ask because this makes a difference in regards to the URL you see in the browser. If it is psp, then what you are seeing is the URL to your iScript plus the portal information. In this case, the actual URL to the content is in an iframe. If you use the psc URL, then you bypass the portal frameset and go direct to the content."

Anoop Savio

ANALYZE & compute statistics

%Execute(/) ANALYZE TABLE PS_CLASS_TBL compute statistics/ ANALYZE TABLE PS_CLASS_ATTRIBUTE compute statistics/ ANALYZE TABLE PS_CLASS_MTG_PAT compute statistics/ ANALYZE TABLE PS_CLASS_CHRSTC compute statistics/ ANALYZE TABLE PS_CLASS_COMPONENT compute statistics/ ANALYZE TABLE PS_CLASS_ASSOC compute statistics/ ANALYZE TABLE PS_CRSE_OFFER compute statistics/ ANALYZE TABLE PS_CU_CLAS_NBR_STG compute statistics/

I didnt get it first when I saw the above SQL. It had a clue that, its something realted to the performance tuning. Given a search, got the below info from the Stanford site.

ANALYZE
Purpose
--Use the ANALYZE statement to collect statistics, for example, to:
--Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
--Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
--Identify migrated and chained rows of a table or cluster.

More indepth info on the same.

Lokesh Madnavat

Disable Browser Caching in PeopleSoft

Going through the alliance presentation, just came across the point: Disabling Browser Caching in PeopleSoft. Got the below information from learnpsdba blog.

A browser will cache various pages and states in memory to increase performance. It may be necessary to disable these performance features on the browser for security reasons. Note that once caching is disabled, the Back button on the browser stops working in PIA.
To disable caching:
1. In PIA, navigate to PeopleTools, Web Profile, Web Profile Configuration.
2. Select the web profile that you want to configure; for example, PROD.
3. Select the Caching page.
4. Make sure that the "Cache Generated HTML" and "Cache Homepage" check boxes are both cleared.
5. Save your changes

About DBMS_STATS Usuage

To be frank, This is for the first time I coming across this code below:

EXECDBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSADM',tabname=>'PS_TEST_LOAD'

,estimate_percent=>10,CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

Gave a search in google and found the info from the Burleson Consulting site.

Note: You should never use the default dbms_stats statistics collection mechanism with Oracle. Just like the initialization parameters, the statistics collection should be customized to your specific needs. For details, see my latest book "Oracle Tuning: The Definitive Reference" and these notes on 11g extended optimizer statistics.

When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row, what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins, hash joins, star joins, and sort merge join methods). These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.

The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics. To always choose the best execution plan for a SQL query, Oracle relies on information about the tables and indexes in the query.Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don't always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements.The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans. Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

“The payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude”

Below is a sample execution of dbms_stats with the options clause.

exec dbms_stats.gather_schema_stats( -ownname => 'SCOTT', -options => 'GATHER AUTO', -estimate_percent => dbms_stats.auto_sample_size, -method_opt => 'for all columns size repeat', -cascade => true, -degree => 15 -)

Here is a representative example of invoking dbms_stats in 10g:

DBMS_STATS.gather_schema_stats(ownname=>’’,estimate_percent=>dbms_stats.
auto_sample_sizecascade=>TRUE,method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

To fully appreciate dbms_stats, you need to examine each of the major directives. Let’s take a close look at each directive and see how it is used to gather top-quality statistics for the cost-based SQL optimizer.The options parameterUsing one of the four provided methods, this option governs the way Oracle statistics are refreshed:

gather—Reanalyzes the whole schema
gather empty—Only analyzes tables that have no existing statistics
gather stale—Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto—Reanalyzes objects which currently have no statistics and objects with stale statistics (Using gather auto is like combining gather stale and gather empty.)

Note that both gather stale and gather auto require monitoring. If you issue the alter table xxx monitoring command, Oracle tracks changed tables with the dba_tab_modifications view, which allows you to see the exact number of inserts, updates, and deletes tracked since the last analysis of statistics.

The estimate percent option
The following estimate_percent argument is a new way to allow Oracle’s dbms_stats to automatically estimate the best percentage of a segment to sample when gathering statistics:

estimate_percent => dbms_stats.auto_sample_size

You can verify the accuracy of the automatic statistics sampling by looking at the dba_tables sample_size column. It is interesting to note that Oracle chooses between 5 and 20 percent for a sample size when using automatic sampling. Remember, the better the quality of your statistics, the better the decision of the CBO.

The method_opt option
The method_opt parameter for dbms_stats is very useful for refreshing statistics when the table and index data change. The method_opt parameter is also very useful for determining which columns require histograms.

In some cases, the distribution of values within an index will effect the CBOs decision to use an index versus perform a full-table scan. This happens when a where clause has a disproportional amount of values, making a full-table scan cheaper than index access.

Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows. In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics. As a general rule, histograms are used when a column's values warrant a change to the execution plan.

To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats. There are also important new options within the method_opt clause, namely skewonly, repeat and auto:

method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'

The skewonly option is very time-intensive because it examines the distribution of values for every column within every index.

If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index versus full-table scan access. For example, if an index has one column that is in 50 percent of the rows, a full-table scan is faster than an index scan to retrieve these rows.

--*************************************************************

-- SKEWONLY option

—Detailed analysis

---- Use this method for a first-time analysis for skewed indexes

-- This runs a long time because all indexes are examined--*************************************************************

begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', cascade => true, degree => 7 );end;

If you need to reanalyze your statistics, the reanalyze task will be less resource intensive with the repeat option. Using the repeat option will only reanalyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will reanalyze you statistics on a regular basis.

--**************************************************************

-- REPEAT OPTION - Only reanalyze histograms for indexes

-- that have histograms---- Following the initial analysis, the weekly analysis

-- job will use the “repeat” option. The repeat option

-- tells dbms_stats that no indexes have changed, and

-- it will only reanalyze histograms for-- indexes that have histograms.--**************************************************************

begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', cascade => true, degree => 7 );end;

The auto option within dbms_stats is used when Oracle table monitoring is implemented using the alter table xxx monitoring; command. The auto option, shown in Listing D, creates histograms based upon data distribution and the manner in which the column is accessed by the application (e.g., the workload on the column as determined by monitoring). Using method_opt=>’auto’ is similar to using the gather auto in the option parameter of dbms_stats.

begin dbms_stats.gather_schema_stats( ownname => 'SCOTT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade => true, degree => 7 );end;

Parallel collection

Oracle allows for parallelism when collecting CBO statistics, which can greatly speed up the time required to collect statistics. A parallel statistics collection requires an SMP server with multiple CPUs.

Better execution speed

The dbms_stats utility is a great way to improve SQL execution speed. By using dbms_stats to collect top-quality statistics, the CBO will usually make an intelligent decision about the fastest way to execute any SQL query. The dbms_stats utility continues to improve and the exciting new features of automatic sample size and automatic histogram generation greatly simplify the job of the Oracle professional.

Anoop Savio

Feed dashboard in your PIA

This might be the simplest code you can ever create.

This is about creating a feed page in the PeopleSoft instance, which will pull up your favorites as rss feeds.

I created a small sample one while working with my previous company. The intention was to pull up the news feeds from my favorite news sites, which I can take up a glance without navigating away from the PIA.

It was done in two ways. One using Pagelet wizard and using the HTML area.
Then you can create a page which exactly serves the purpose of Google news, right on your PeopleSoft homepage.

How to make it is a very simple task. Pick up any feed link from your favorite site and use the following link to create the RSS feed HTML script to embed in Pagelet wizard or in the html area.

I used: http://www.rss-to-javascript.com/p/RSSRDF-Converter___138.html to create the embedding script.

So how to create the embed script for MyTechSpeak PeopleSoft site?

You can see a feed icon on the navigation bar of the site.



Right click on it and click on the copy shortcut option. You will get the following link: http://mytechspeak.blogspot.com/feeds/posts/default

Now go to the link: http://www.rss-to-javascript.com/p/RSSRDF-Converter___138.html and follow the screenshots below:



Click on the generate javascript button to get the embed code.



Now after embedding the above code in Pagelet or html area, it will look like this. This will be automatically updated with the sites status.stay connected.



Try it. It’s fun, simple and useful. Even you can create a kind of google news inside your pia :)

%Bind [NOQUOTES]

You might have used %Bind ([record name.]Fieldname [, NOQUOTES]) while you code. It might be a confusing part for some people, on why NOQUOTES is used with %bind.

If you are selecting the character field from the database, it will be enclosed in quotes. Use NOQUOTES to remove that quotes while selecting.

For example say you are selecting the record PS_TEST from a table to use it in the AE SQL action. While selecting it the record name will be enclosed in quotes, which will error out the SQL action. There you can use NOQUOTES for removing the quotes. This is a typical example for information.

Anoop Savio

PS Meta tables

You might have came across this info previously. Its good to refresh it at times.

PeopleSoft Projects
PSPROJECTDEFN table stores information about projects created in Application Designer.

Try it out:
SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';
PSPROJECTITEM table stores objects inserted into your Application Designer project.

Try it out:
SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';

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

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).

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: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.

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.

Process Definition Table(s)

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.

PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

Message Catalog Tables

PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.

-- Example
SELECT * FROM PSMSGCATDEFN WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY') AND LAST_UPDATE_DTTM <> TO_DATE('03-DEC-07', 'DD-MON-YY') AND LAST_UPDATE_DTTM <>> PeopleTools >> Utilities >> Administration >> URLs

Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.

PeopleSoft Query Tables

PSQRYDEFN: Stores query related info.

PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).

PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.

PSQRYEXPR: Stores query expressions.

PSQRYBIND: Stores query bind variables.

PSQRYRECORD: Stores all records used in all aspects of query creation

PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.

PSQRYLINK: Stores the relationships to child queries.

PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)

PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SQL Objects

PSSQLDEFN: Stores SQL object definitions.

PSSQLDESCR: Stores SQL objects descriptions, and description long.

PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.

-- When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs

Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.

PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.

PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.

PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.

PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.

PSAESTEPDEFN: Steps in application engines are stored in this table.

PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.

PSAESTEPMSGDEFN: Application engine message action definition table.

AEREQUESTTBL: Application Engine request table behind the AE run control page.

AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.

PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).
Process Request Tables

PSPRCSQUE: This record contains the process request information to run a process request.

PSPRCSRQST: This record contains the process request information to run a process request.

PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.

PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.

PSMAPFIELD: Stores Field mapping of Activity

PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

Tino Simon

Custom Navigation Collection and Pagelet

When we are working on PIA we often feel that we are visiting the same areas within the system regularly.

So to make our life easier we can create a Pagelet based on a custom Navigation Collection containing our often used components. This greatly reduces the number of clicks required for day-to-day work and could be really useful for end users.

Creating the Navigation Collection

First, let’s create the Navigation Collection. You can have whatever menus/components you want in your collection.

Go to PeopleTools > Portal > Portal Utilities > Navigation Collections

Add a Collection with an appropriate Name and Description Click Add Folder Select Menu Folder, from the Employee Portal. Use the prompt to locate the folder to add, click the plus folder next to a high level menu to expand it and choose a child menu. Highlight the top level, and click Add Folder again. Repeat the process until you have added all your folders.

We can also add links directly to the Components we used most often, like Process Monitor, and moved them to the top of the list. Click Save (and remember the name of your new Collection). Publishing the Navigation Collection as a Pagelet

Go to PeopleTools > Portal > Pagelet Wizard > Pagelet Wizard

Add a New Pagelet and fill out the Title etc Choose a Data Type of Navigation Collection Choose a Portal of Employee and prompt to find your Navigation Collection. We normally reduce the Max Child Links to 5. I choose the Menu layout. I set the ‘Parent Images’ = Small, Turn off ‘Parent Descriptions’ and set the ‘Child Display Type’ = Simple List.

On the final page, check HomePage Pagelet, choose a folder, and set the security required.That’s your Pagelet done. Now add it to your Homepage.

Adding a Pagelet to your Homepage From the PIA Homepage, select the ‘Personalize Content’ link. Place a Check in the Checkbox next to the Pagelet you want, then click Save. Click the Personalize Layout link. Select whether you want a 2 or 3 column layout, and move the new Pagelet to the column you wish it to appear in, click Save.

That’s how you can create your own Custom Navigation Collection and Pagelet.

Original link: PeopleSoft Tipster

TREE generation using PeopleCode

Creating the tree for security related purpose; you might have came across the same, in the course of development.

We create it under: Menu > Tree manager > Tree manager

Note: For getting an overview of tree manager: PeopleBooks > PeopleTools 8.51 PeopleBooks: PeopleSoft Tree Manager > Introduction to PeopleSoft Tree Manager

Note: Download page and record design document to see the corresponding structures

Note: Download peoplecode and SQL for the tree generation

Opening any tree you can see the nodes. Parent node, child nodes, sibling nodes and whole other tree properties.

Every time there happens a department structure change, when we take the example of department tree as an example, when the numbers of employees are more in the organization, it is pretty much tough affair to structure all the employees under the new management structure. What if we have a code to fetch the employees reporting to a manager/supervisor of a department and assign them through peoplecode?

Below is the code to create a tree using peoplecode. This one is tested and verified code. If you have any doubts related to the same, you can drop a e-mail.

Note: Keep the Tree API open before you go through the code below: PeopleBooks > PeopleTools 8.51 PeopleBook: PeopleCode API Reference > Tree Classes

Note: Keep this tip in the mind. The parent node is similar to the level0 fields, the child rows are similar to level1, level2 fields. We can have only upto 3 levels for a component, but can have n number of levels in case of tree. I have used upto 12 levels in this code.

Consider the tree creation using peoplecode as one part. The tree will be created in the database. How to display this tree in a custom page, where when a person enters, consider a supervisor comes to this page, he should be able to view the details of his subordinates only. This cannot be achieved using row level security. Can you tell the reason? (Just think about it.)

The code given below meets the above two requirements.

The application engine peoplecode is to create the tree structure using the tree api objects.
The component level peoplecode is to render the tree structure, based on the employee who logs in to the custom page. When a supervisor logs in, the tree structure data starting from his position is fetched from the database and displayed. Hence the supervisor becomes the parent node and all the subordinates are the child rows.

You can see one HTML area (refer to the record AS_DERIVED_HR in the design doc). That while embedded in the custom page, renders the tree structure as we can see it in the tree manager page. TREECLTEVENT (refer to the record AS_DERIVED_HR in the design doc), is like the push button which get the control from the HTML area while expanding/collapsing the area.

Go through the code. Feel free to comment to this post, in case of any doubts. It will be answered.

See the peoplecode and SQL below:

Appendix 1: PeopleCode
Field: OPRID
Event: ROWINIT
AS_SUPER_RUN.OPRID = %OperatorId

Field: RUN_CNTL_ID
Event: SAVEEDIT
&POS = Find(" ", AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(65, 33, "Run Control Ids may not contain embedded spaces");
End-If;
&POS = Find("%",AS_SUPER_RUN.RUN_CNTL_ID);
If &POS > 0 Then
Error MsgGet(99, 99, "Run Control Ids may not contain %");
End-If;

Component: AS_SUPVTREE
Event: PostBuild
Component Rowset &rsTree;
Local Rowset &rsNode;
Local ApiObject &apiRootNode;
Local ApiObject &apiSession;
Local ApiObject &apiTree;
Local Record &recTree;
Local boolean &boolRes;
Local string &boolFlag;
Local date &dtEffDt;
&rsNode = CreateRowset(Record.AS_SUPVTREE_DTL);
&rsTree = CreateRowset(Record.AS_SUPVTREE_HDR, &rsNode);
&rsTree.InsertRow(1);
&recTree = &rsTree.GetRow(2).GetRecord(1);
&recTree.GetField(Field.PAGE_NAME).Value = "AS_SUPVTREE";
&recTree.GetField(Field.PAGE_FIELD_NAME).Value = "TREECTLEVENT";
&recTree.GetField(Field.PAGE_SIZE).Value = 500;
&recTree.GetField(Field.DISPLAY_LEVELS).Value = 12;
&recTree.GetField(Field.COLLAPSED_IMAGE).Value = "PT_TREE_COLLAPSED";
&recTree.GetField(Field.EXPANDED_IMAGE).Value = "PT_TREE_EXPANDED";
&recTree.GetField(Field.END_NODE_IMAGE).Value = "PT_TREE_END_NODE";
&recTree.GetField(Field.LEAF_IMAGE).Value = "PT_TREE_LEAF";
&recTree.GetField(Field.IMAGE_WIDTH).Value = 15;
&recTree.GetField(Field.IMAGE_HEIGHT).Value = 12;
&recTree.GetField(Field.INDENT_PIXELS).Value = 20;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
SQLExec("SELECT TREE_NODE FROM PS_AS_NODE_EMPL WHERE EMPLID=:1",
%EmployeeId, &strTreeNode);
rem &apiRootNode = &apiTree.FindnODE(&strTreeNode, "");
&apiRootNode = &apiTree.FindnODE(%EmployeeId, "");
If All(&apiRootNode) Then
If &apiRootNode.HasChildren Then
&strFlag = "X";
Else
&strFlag = "N";
End-If;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&rsNode.InsertRow(1);
&recTree = &rsNode.GetRow(2).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_JOB_CURR_VW WHERE EMPLID=:1",
&apiRootNode.NAME, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiRootNode.NAME;
&recTree.GetField(Field.DESCR).Value = &apiRootNode.DESCRIPTION;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&apiTree.Close();
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree);
Else
MessageBox(0, "", 20006, 30, "Message not Found");
End-If;

Record: AS_DERIVED_HR
Field: TREECTLEVENT
Event: FIELDCHANGE
Component Rowset &rsTree;
Local Rowset &rsNode;
Local Record &recTreeParent, &recTree;
Local integer &intRow, &intParentLevel, &intLevelOffset;
Local ApiObject &apiSession, &apiTree, &apiParentNode, &apiChildLeaf, &apiChildNode;
Local date &dtEffDt;
Local boolean &boolRes, &boolFirst;
Local string &strRangeFrom, &strRangeTo, &strDynamicRange, &strParentFlag, &strTemp;
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "X" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTreeParent = &rsNode.GetRow(&intRow).GetRecord(1);
&intParentLevel = &recTreeParent.GetField(Field.TREE_LEVEL_NUM).Value;
&intRow = &intRow + 1;
&apiSession = %Session;
&apiTree = &apiSession.GetTree();
SQLExec("SELECT %DateOut(A.EFFDT) FROM PSTREEDEFN_VW A WHERE
A.TREE_NAME=:1", "AS_SUPERVISOR1", &dtEffDt);
&boolRes = &apiTree.Open("", "", "AS_SUPERVISOR1", &dtEffDt, "", False);
&apiParentNode =
&apiTree.FindNode(&recTreeParent.GetField(Field.TREE_NODE_NEW).Value, "");
If &apiParentNode.HasChildren Then
&apiParentNode.Expand(2);
If &apiParentNode.HasChildLeaves Then
&boolFirst = True;
&apiChildLeaf = &apiParentNode.FirstChildLeaf;
While &boolFirst Or
&apiChildLeaf.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildLeaf = &apiChildLeaf.NextSib;
End-If;
If &apiChildLeaf.Dynamic = True Then
&strRangeFrom = "";
&strRangeTo = "";
&strDynamicRange = "Y";
Else
&strRangeFrom = &apiChildLeaf.RangeFrom;
&strRangeTo = &apiChildLeaf.RangeTo;
&strDynamicRange = "N";
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "Y";
&recTree.GetField(Field.TREE_NODE).Value = "";
&recTree.GetField(Field.DESCR).Value = "";
SQLExec("SELECT DEPTNAME,JOBCODE_DESCR FROM PS_AS_SUPER_VW WHERE
SUPERVISOR_ID=:1", &strRangeFrom, &strDescrFrom, &strDescrTo);
&recTree.GetField(Field.RANGE_FROM).Value = &strDescrFrom;
&recTree.GetField(Field.RANGE_TO).Value = &strDescrTo;
&recTree.GetField(Field.DYNAMIC_FLAG).Value = &strDynamicRange;
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = "N";
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = 0;
&intRow = &intRow + 1;
End-While;
End-If;
If &apiParentNode.HasChildNodes Then
&boolFirst = True;
&apiChildNode = &apiParentNode.FirstChildNode;
While &boolFirst Or
&apiChildNode.HasNextSib
If &boolFirst Then
&boolFirst = False;
Else
&apiChildNode = &apiChildNode.NextSib;
End-If;
If &apiChildNode.HasChildren Then
&strParentFlag = "X";
Else
&strParentFlag = "N";
End-If;
If &apiTree.LevelUse = "S" Then
&intLevelOffset = &apiChildNode.LevelNumber - &apiParentNode.LevelNumber - 1;
Else
&intLevelOffset = 0;
End-If;
&rsNode.InsertRow(&intRow - 1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
&recTree.GetField(Field.LEAF_FLAG).Value = "N";
SQLExec("SELECT DEPTID FROM PS_AS_SUPER_VW WHERE SUPERVISOR_ID=:1",
&apiChildNode.Name, &strTreeNode);
&recTree.GetField(Field.TREE_NODE).Value = &strTreeNode;
&recTree.GetField(Field.TREE_NODE_NEW).Value = &apiChildNode.Name;
&recTree.GetField(Field.DESCR).Value = &apiChildNode.Description;
&recTree.GetField(Field.RANGE_FROM).Value = "";
&recTree.GetField(Field.RANGE_TO).Value = "";
&recTree.GetField(Field.DYNAMIC_FLAG).Value = "N";
&recTree.GetField(Field.ACTIVE_FLAG).Value = "Y";
&recTree.GetField(Field.DISPLAY_OPTION).Value = "B";
&recTree.GetField(Field.STYLECLASSNAME).Value = "PSHYPERLINK";
&recTree.GetField(Field.PARENT_FLAG).Value = &strParentFlag;
&recTree.GetField(Field.TREE_LEVEL_NUM).Value = &intParentLevel + 1;
&recTree.GetField(Field.LEVEL_OFFSET).Value = &intLevelOffset;
&intRow = &intRow + 1;
End-While;
End-If;
&recTreeParent.GetField(Field.PARENT_FLAG).Value = "Y";
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
&apiTree.Close();
Else
If Left(AS_DERIVED_HR.TREECTLEVENT, 1) = "S" Then
&intRow = Value(Right(AS_DERIVED_HR.TREECTLEVENT,
Len(AS_DERIVED_HR.TREECTLEVENT) - 1)) + 1;
&rsNode = &rsTree.GetRow(2).GetRowset(1);
&recTree = &rsNode.GetRow(&intRow).GetRecord(1);
If &recTree.GetField(Field.LEAF_FLAG).Value = "N" Then
SQLExec("SELECT NAME_DISPLAY'-'DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE_NEW).Value),
&strDescr100);
MessageBox(0, "", 0, 0, "The selected node is %1.", &strDescr100);
Else
If &recTree.GetField(Field.DYNAMIC_FLAG).Value = "N" Then
SQLExec("SELECT DESCR100 FROM PS_AS_SUPER_VW A WHERE
A.SUPERVISOR_ID=:1", String(&recTree.GetField(Field.TREE_NODE).Value),
&strDescr100);
REM &recTree.GetField(Field.RANGE_FROM).Value = &strDescr100;
REM &recTree.GetField(Field.RANGE_TO).Value = &strDescr100;
If &recTree.GetField(Field.RANGE_FROM).Value =
&recTree.GetField(Field.RANGE_TO).Value Then
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value "]";
Else
&strTemp = "[" &recTree.GetField(Field.RANGE_FROM).Value " - "
&recTree.GetField(Field.RANGE_TO).Value "]";
End-If;
REM &strTemp = &strDescr100;
Else
&strTemp = "[ ]";
End-If;
MessageBox(0, "", 0, 0, "The selected leaf is %1.", &strTemp);
End-If;
Else
AS_DERIVED_HR.HTMLAREA = GenerateTree(&rsTree,
AS_DERIVED_HR.TREECTLEVENT);
End-If;
End-If;
AS_DERIVED_HR.TREECTLEVENT = "";

Appendix 2: SQL
SQL ID: AS_EMPRPTEMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID =B.EMPLID
AND A.SUPERVISOR_ID=A.EMPLID
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
PeopleCode and SQL’s mytechspeak.com
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
AND A.SUPERVISOR_ID=A.EMPLID
AND A.HR_STATUS='A'

SQL ID: AS_SUPER_EMP_SQL
SELECT A.EMPLID
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND SUPERVISOR_ID=:1
AND EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=%CurrentDateIn) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.EMPLID<>A.SUPERVISOR_ID
AND SUPERVISOR_ID<>' '
AND A.HR_STATUS='A'
ORDER BY A.EMPLID

View: AS_SUPER_VW
SELECT DISTINCT A.EMPLID
,B.FIRST_NAME
FROM PS_JOB A
, PS_PERSONAL_DATA B
WHERE A.EMPLID=B.EMPLID
AND A.EFFDT= (
SELECT MAX (EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND A.EFFSEQ= ( SELECT MAX (EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND SUPERVISOR_ID<>' '
UNION
SELECT 'SUPER'
,'super'
FROM DUAL

SQL ID: AS_EMP_SUP_SQL
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=:1 CONNECT BY NOCYCLE
PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

SQL ID: AS_INSERT_SUP
INSERT INTO PS_AS_EMP_SUP_TBL
SELECT EMPLID
,SUPERVISOR_ID
FROM PS_JOB A
WHERE EFFDT=(
SELECT MAX(EFFDT)
FROM PS_JOB
WHERE EMPLID=A.EMPLID
AND EMPL_RCD=A.EMPL_RCD
AND EFFDT<=SYSDATE) AND EFFSEQ=( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID=A.EMPLID AND EMPL_RCD=A.EMPL_RCD AND EFFDT=A.EFFDT) AND A.HR_STATUS='A' AND A.PER_ORG='EMP'

Appendix D: AppEngine
Action: PeopleCode
Local ApiObject &apiSession, &apiLevel;
Component ApiObject &treeSupervisor;
Local SQL &sqlSuperEmployees, &sqlSwSupEmpSql;
Local number &numReturnDelete, &counter, &numLevel;
Local string &strEmplid, &strSupervisor;
SQLExec(SQL.AS_EMPRPTEMP_SQL, &strEmployeeSupervisor);
&apiSession = %Session;
&treeSupervisor = &apiSession.GetTree("", "", "", "");
&treeSupervisor.Delete("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
If Not &treeSupervisor.Create("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE") = 0 Then
&numReturnDelete = &treeSupervisor.Open("", "", "AS_SUPERVISOR1", Date("1901-01-01"),
"SUPERVISOR_TREE");
Else
&treeSupervisor.Description = "Supervisor Tree";
&treeSupervisor.Category = "DEFAULT";
&apiLevelCollection = &treeSupervisor.levels;
For &numI = 1 To 12
&apiLevel = &apiLevelCollection.add("SUPLVL:" &numI);
&apiLevel.description = "Level" &numI;
End-For;
End-If;
SQLExec("DELETE FROM PS_AS_SUPER_TBL");
SQLExec("INSERT INTO PS_AS_SUPER_TBL SELECT
SUPERVISOR_ID,substr(NAME_DISPLAY,1,30) FROM PS_AS_SUPER_VW");
SQLExec("DELETE FROM PS_AS_EMP_SUP_TBL");
SQLExec(SQL.AS_INSERT_SUP);
&nodeRootNode1 = &treeSupervisor.InsertRoot(&strEmployeeSupervisor);
&nodeRootNode1.InsertChildLeaf(&strEmployeeSupervisor, &strEmployeeSupervisor);
&treeSupervisor.Save();
AS_SUPER_AET.SUPERVISOR_ID = &strEmployeeSupervisor;

Step: SupTree2
Action: DoSelect
%Select(EMPLID,SUPERVISOR_ID ,LEVELS)
SELECT DISTINCT EMPLID
, SUPERVISOR_ID
,LEVEL
FROM PS_AS_EMP_SUP_TBL START WITH SUPERVISOR_ID=%Bind(SUPERVISOR_ID)
CONNECT BY NOCYCLE PRIOR EMPLID=SUPERVISOR_ID
ORDER BY LEVEL

Step: SupTree2
Action: Peoplecode

Component ApiObject &treeSupervisor;
&nodeRootNode1 = &treeSupervisor.FindNode(AS_SUPER_AET.SUPERVISOR_ID, "");
&counter = &counter + 1;
If All(&nodeRootNode1) And
&treeSupervisor.NodeExists(AS_SUPER_AET.EMPLID) Then
&nodeRootNode1 = &nodeRootNode1.InsertChildNode(AS_SUPER_AET.EMPLID);
&nodeRootNode1.InsertChildLeaf(AS_SUPER_AET.EMPLID, AS_SUPER_AET.EMPLID);
&treeSupervisor.Save();
SQLExec("commit");End-If;


Manivannan Ramar

%SQLrows

While executing INSERT/DELETE/UPDATE statements you can see the number of rows effected displayed at the bottom of the sqldeveloper, as shown in the screenshot below.


To capture the number of effected rows, during conversion/interface run, to properly audit the actual number of rows effected, %SQLrows can be used.

PeopleBooks Definition:

%SQLRows returns the number of rows affected by the most recent UPDATE, DELETE, or INSERT executed through the SQLExec function.

%SQLRows can also be used after SELECT. It returns 0 if no rows are returned, a non-zero value if one or more rows are returned. In this case, the non-zero value does not indicate the total number of rows returned.

This will be useful and handy at times.

Manivannan Ramar

Implementing Search/Match

To use the full functionality of your system, you must maintain the integrity of your database. With users from many departments entering data, you want to minimize the entry of duplicate or multiple records. Search/Match enables you to define criteria to check for duplicate or multiple ID records. The searchable ID types (called Search Types) are:

• Person (emplID).
• Organization (organization IDs within PeopleSoft Enterprise Campus Solutions).
• Applicant (HRS_PERSON_ID within PeopleSoft Enterprise HRMS Talent Acquisition Manager).

People books have reference to the search match codes already delivered in the system. They are found in the following application engine sections.

• Search/Match/Post Test Scores (SAD_TEST_PST).
- See the SrchMtch section in the program
• CBAP Post (SAD_CPST_AE).
- See the SrchMtch section in the program, Step 01

Prior to implementing the code, make sure that you have created the Search/Match rules and Search/Match Parameters, to meet your business requirement.

Navigations:
Set Up SACR, System Administration, Utilities, Search/Match, Search/Match Rules
Set Up SACR, System Administration, Utilities, Search/Match, Search/Match Parameters, Search Parameters

Being setup the above two, you can also define permissions and exceptions.

Refer the Search/Match feature in PeopleBooks, for more information on the same.
So you have seen the delivered code for the searchmatch

The code has the object &PersonSrchType, which is used to compare the columns that are being passed for the search/match.

SEARCH_ADD_PARM & SEARCH_ORDR_PRM: The first record has the Search/Match parameter used for a particular job and the second search/match, has the parameter order number.

HCR_SM_CRIT_VW table has the parameter and the rule mapping, which is done in the Search/Match Parameters

Now how to implement that code in a better way.

You can create a function using the field formulae and put this code, by modifying it according to your search parameter and the rules.

Then call that function using the Declare function:

Declare Function USTSearchMatch PeopleCode UST_DERIVED_112.FIELDNAME FieldFormula; /* search match function to find whether Person exists in the PeopleSoft system*/

Then pass the parameters to this function by creating a derived record and assigning the values to it one by one.

/* Search Match */

&fileLog.WriteLine("Selected student For Search Match: " UST_I112_AET.UST_EMPLID.Value);
&SearchMatchTblRec = CreateRecord(Record.UST_DERIVED_SM);
&SearchMatchTblRec.FIRST_NAME_SRCH.VALUE = Upper(&First_Name_srch);
&SearchMatchTblRec.LAST_NAME_SRCH.VALUE = Upper(&Last_Name_srch);
&SearchMatchTblRec.BIRTHDATE.VALUE = &BirthDate;
If All(&National_ID) And
&Flag = "R" Then
&SearchMatchTblRec.NATIONAL_ID.VALUE = &National_ID;
End-If;
&SearchMatchTblRec.EXTERNAL_SYSTEM_ID.VALUE = &National_ID;
&SearchMatchTblRec.EXTERNAL_SYSTEM.VALUE = "LID";
&rs_PersonSrchRules = CreateRowset(Record.HCR_SM_CRIT_VW); /* delivered method/row set */
&PARM_CD = UST_I112_AET.SM_PARM_CD.Value;
SQLExec("SELECT DISTINCT SM_TYPE FROM PS_HCR_SM_ORDR_TBL WHERE SM_PARM_CD=:1", &PARM_CD, &SMTYPE);
&rs_PersonSrchRules.Fill("where SM_TYPE = :1 and SM_PARM_CD = :2", &SMTYPE, &PARM_CD);
&rs_PersonSrchRules.Sort(HCR_SM_CRIT_VW.SEARCH_RULE_NBR, "A", HCR_SM_CRIT_VW.SEQNO, "A");
&Processing_Code = USTSearchMatch(&rs_PersonSrchRules, &SearchMatchTblRec); /* search match function */

The &Processing_Code returns the value from the function call, which tells the result, whether the person exist in the system or not.

Write an evaluate statement to assess the &Processing_Code variable as given below.

Evaluate &Processing_Code
When = "A" /* ADD */
/* The person doesn’t exist in the system. Write the code to add the person */
Break;

When = "U" /* UPDATE */
/* The person exist in the system. Write the code to update the person information */
Break;

When = "S" /* SUSPEND */
/* The person with the details already exist in the system. Write the code to update the flag for that row to suspend the processing */
Break;

When-Other /* IGNORE */
/* Invalid person ID. Write the code to write the respective details into a log file */
Break;

End-Evaluate;

Hurray! So you have done implementing the Search/Match feature. If you suggestions to make the code even more better do mail us or post comment for this post.

Anoop Savio

Messages with Style

I have often wanted to display an informational message at the top of the page. Rather than just slap a Long Edit Box at the top of the screen, I decided to look for a nice looking way to do it. One of the great things about PeopleSoft, it always has an example somewhere in the huge product for what you are trying to do.

Here are some of the messages that I found:

Information Message:

Example Page: SSS_STDNTCTR_SR_SP

Here are the different pieces:

• Stylesheet of the page: SSS_STYLESHEET
• Group Box:
  • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
  • Body Stylesheet: SSSMSGINFOFRAME
• Static Image:
  • Image: PS_CS_MESSAGE_INFO_ICN
  • Size: 23×23
• Static Text:
  • Style: SSSMSGINFOTEXT

Alert Message:

Example Page: SSF_SS_ERRORMSG

Here are the different pieces:

• Page Stylesheet: SSS_STYLESHEET
• Group Box
  • Label Stylesheet: PAGROUPBOXLABELINVISIBLE
  • Body Stylesheet: SSSMSGALERTFRAME
• Push Button (for the image, you could use a static icon instead)
  • Image: PS_CS_MESSAGE_ALERT_ICN
  • Size: Large Icon
• Long Edit Box (works the same as the static textbox, but lets you change it from code)
  • Stylesheet: SSSMSGALERTTEXT
  • No Label

Ganesh M

Useful SQL's

General:

Alter session - so you do not have to preface everything with user.table
alter session set current schema=;

Example: If I needed to access a table(booklist) under the js schema and I was logged in at mj I could do the following.

* select count(*) from js.booklist;

or

* alter session set current schema=js;
* select count(*) from booklist;

Alter session works very well when you are working constantly in another schema like peoplesoft.

Load Specific:

I use some oracle specific sql some times.

Process Scheduler:

Rundates with the number of processes that were queued when an item was scheduled to run

select to_char(a.rundttm,'yyyy-mm-dd hh24:mi:ss') rundate, count(*) from ( select distinct rundttm from sysadm.ps_pmn_prcslist) a join sysadm.ps_pmn_prcslist b on a.rundttm between b.rundttm and b.enddttm group by a.rundttm;

How long processes are talking to run

select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || (a.ENDDTTM-a.BEGINDTTM) * 1440 as ProcessIDProcessNameMinutesTaken from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;

select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || floor(((((a.ENDDTTM-a.BEGINDTTM)*24*60*60)/3600)*3600)/60) as IdNameMinutes from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;

CSV export of the process scheduler table

select OPRID || ',' || PRCSINSTANCE || ',' || PRCSTYPE || ',' || PRCSNAME || ',' || to_char(rundttm,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(BEGINDTTM,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(ENDDTTM,'yyyy-mm-dd hh24:mi:ss') from sysadm.ps_pmn_prcslist;

Tino Simon

Peopletools 8.51

About one year after Peopletools 8.50, the next generation, Peopletools 8.51 is now available.

As usual, download it from EDelivery and don’t forget the first patch 8.51.01 to be applied, to be download from FTP website.

The documentation is also available online on the OTN hosted Peoplebooks.

Original Link: http://gasparotto.blogspot.com/

Peoplecode Trace in a File.

Local File &fle;
&fle = GetFile(GetCwd() | "/files/Test.xml", "W", %FilePath_Absolute);
&fle.WriteLine("Hi");
&fle.Close();

Test.xml can Found in...

Web Server: psoft > appserv > DATABASENAME > files > Test.xml

Lokesh Madnavat

Campus Solutions: Financial Aid overview

While the knowledge transfer section was going for the Financial Aid module, I realized the fact to know the functional side of Financial aid provision would add to the knowledge. And, my manager was insisting on the same, which made me to have a seek on the FA provisioning.

Student financial aid refers to funding intended to help students pay educational expenses including tuition and fees, room and board, books and supplies, etc. for education at a college, university, or private school. General governmental funding for public education is not called financial aid, which refers to awards to specific individual students. Certain governments, e.g. Nordic countries, provide student benefit. A scholarship is sometimes used as a synonym for a financial aid award, although grants and student loans are also components of financial aid packages from students' intended colleges.

Financial aid may be classified into two types based on the criteria through which the financial aid is awarded: merit-based or need-based.

Student aid is awarded as grants and scholarships, low-interest, government-subsidized loans, and education tax benefits, and nearly everyone is eligible for some of it.

In the U.S. to apply for most student aid, a student must first complete the Free Application for Federal Student Aid (FAFSA) by submitting the application electronically to the U. S. Department of Education's using the Department of Education's Web site, or as the law also authorizes, by getting professional assistance from a fee-based preparer. A student's aid application (FAFSA) may be submitted to the Department of Education as early as January 1 before the summer or fall when the student enrolls and must be re-submitted with updated income, asset, and dependency information each year.

The Department of Education processes each request and tells a student how much the federal government expects your family to contribute towards paying for college - the Expected Family Contribution (EFC). However, an EFC is not necessarily how much a student will pay for college - aid can reduce an individual's cost. Then, the post-secondary institutions to which a student applies determine how much federal, state, and college-specific aid a student will receive. An individual's student aid award is likely to vary from institution to institution.

Most student aid is federal aid – people's tax dollars working for students. Most federal student aid is awarded as grants and low-interest loans. Grant programs include the Pell Grant, the Academic Competitiveness Grant, the TEACH Grant, and the SMART Grant. Grants are best because they are "free money" – they don't have to be repaid as long as a student meets any obligations they may have.

The federal loan programs include the Federal Direct Subsidized and Federal Direct Unsubsidized Loans, the Perkins Loan, and the Parent PLUS (Parental Loan for Undergraduate Students) Loan and Graduate PLUS (a loan for Graduate students). Unlike with federal grants, a borrower must repay the loan amount and any interest. Federal loans offer lower interest rates and better repayment terms than private student loans from banks and other financial institutions.

Students (or their parents/guardians) can take advantage of education tax benefits to ease the financial burden of attending college. Tax-based education programs include the American Opportunity Tax Credit and the Lifetime Learning Tax Credit. These programs reduce a student's (or his or her parents'/guardians') taxable income while the student attends college.
In addition to federal student aid, students may be eligible for state-based aid. States provide students more than $10.2 billion of aid every year. Each state aid program is different. Usually, a student must reside and attend college in the state providing his/her aid. In some cases, a student can spend state aid on colleges in neighboring states.

Merit-based:

Merit-based grants or scholarships include both scholarships awarded by the individual college or university and those awarded by outside organizations. Merit-based scholarships are typically awarded for outstanding academic achievements and minimum SAT or ACT scores, although some merit scholarships can be awarded for special talents, leadership potential and other personal characteristics. Scholarships may also be given because of group affiliation (such as YMCA, Boys Club, etc.). Merit scholarships are sometimes awarded without regard for the financial need of the applicant. At many colleges, every admitted student is automatically considered for merit scholarships. At other institutions, however, a separate application process is required. Scholarships do not need to be repaid as long as all scholarship requirements are met.

Athletic scholarships are a form of merit aid that takes athletic talent into account.

Need-based:

Need-based financial aid is awarded on the basis of the financial need of the student. The Free Application for Federal Student Aid application (FAFSA) is generally used for determining federal, state, and institutional need-based aid eligibility. At private institutions, a supplemental application may be necessary for institutional need-based aid.

Determining Your Student Aid:

Post-secondary institutions post a Cost of Attendance or Price of Attendance, also known as a "sticker price."

However that price is not how much an institution will cost an individual student. To make higher education costs more transparent before a student actually applies to college, federal law requires all post-secondary institutions receiving Title IV funds (federal funds for student aid) to post net price calculators on their Web sites by October 29, 2011.

As defined in The Higher Education Opportunity Act of 2008, the net price calculator’s purpose is:

“…to help current and prospective students, families, and other consumers estimate the individual net price of an institution of higher education for a student. The [net price] calculator shall be developed in a manner that enables current and prospective students, families, and consumers to determine an estimate of a current or prospective student’s individual net price at a particular institution.”

The law defines estimated net price as the difference between an institution’s average total Price of Attendance (the sum of tuition and fees, room and board, books and supplies, and other expenses including personal expenses and transportation for a first-time, full-time undergraduate students who receive aid) and the institution’s median need- and merit-based grant aid awarded.

In the United States:

The United States government and all U.S. state governments provide merit and need-based student aid including grants, work-study, and loans. As of 2010 there are nine federal and 605 state student aid programs and many of the nearly 7,000 post-secondary institutions provide merit aid.

Major federal grants include the Pell Grants, Federal SEOG Grants, SMART Grants, Academic Competitiveness Grants (ACG Grant), Federal Work-Study Program, Federal Stafford Loans (in subsidized and unsubsidized forms), State Student Incentive Grants and Federal PLUS Loans. Federal Perkins Loans are made by participating schools per annual appropriations from the U.S. Department of Education. Federal Stafford Loans and Federal PLUS Loans are made by the U.S. Department of Education. As of April 2010, Congress voted to eliminate the Federal Family Education Loan Program (FFELP) which had allowed private lenders to make student loans guaranteed by the federal government.

To qualify for federal, state, and institutional aid, a student must prepare a Free Application for Federal Student Aid (FAFSA) every year. The earliest filing date is January 1 for the upcoming academic year. Federal law authorizes that students have two choices when preparing their federal student aid application, either prepare the application themselves on the Department of Education's Web site, or use the services of a fee-based, professional aid advisory firm.

Most aid is provided on a first-come, first-served basis so it is essential that students prepare and submit their aid applications in as close to January 1 as possible. The aid "window" stays open 18 months in case student's financial circumstances change and require adjustment to their aid application.

The application - approximately 130 questions each year - considers household size, income, assets, the number in college and other financial factors to determine a student's aid eligibility and an expected family contribution (EFC). Institutions use EFC to guide their decision about how much need-based financial aid to award a student.

The EFC also takes into consideration any participation in college savings or pre-paid tuition plans. In the past, financial aid officers weighed pre-paid tuition plans more heavily than other 529 college savings plans when determining a student’s eligibility. In February 2006, Congress passed legislation to treat both types of plans evenly.

State governments also typically provide some types of need- and non-need-based aid, consisting of grants, loans, work-study programs, tuition waivers, and scholarships. Individual colleges and universities may provide grants and need- and merit-based scholarships. Students requiring financial aid beyond what is offered by their institution may consider a private (alternative) education loan, available from most large lending institutions. Typically, education loans obtained through the federal government have lower interest rates than private education loans.

Institutions may also offer their own student financial assistance, in the form of need- or merit-based aid, as well as endowed scholarships (with varying need and/or merit-based criteria). Some institutions may only require the FAFSA; some may also require an additional need-based analysis document, such as the CSS/Profile, to apply for such funds to apply a more stringent need analysis for the rationalization of institutional funds.

Anoop Savio

Photo Upload interface code

Use the code below in an application engine, peoplecode step, as per your requirement.

Function PutPhotoInit:

This function initializes employee photo processing. It initializes PSOPTIONS.MAXCHUNKSIZE. The point of the initialization is to select a chunk size that is larger than the largest photo that will be processed. The processing chunksize is currently set to 1MB. The program would need to be updated if a larger photo is to be loaded. The original chunksize should be noted as it is possible for the program to abend without restoring the original chunksize (28K as of this writing).

Function PutPhotoInit()
SQLExec("SELECT MAXCHUNKSIZE FROM PSOPTIONS", &iOriginalChunkSize);
&iPhotoChunkSize = 1000000;
If &iOriginalChunkSize < &iPhotoChunkSize Then SQLExec("UPDATE PSOPTIONS set MAXCHUNKSIZE = :1", &iPhotoChunkSize); End-If; &recEMPL_PHOTO = CreateRecord(Record.EMPL_PHOTO); End-Function;

Function PutPhotoTerm:

This function terminates employee photo processing. It restores PSOPTIONS.MAXCHUNKSIZE to the value that it had when processing began. The original chunksize should be noted as it is possible for the program to abend without restoring the original chunksize (28K as of this writing).

Function PutPhotoTerm()
If &iOriginalChunkSize < &iPhotoChunkSize Then SQLExec("UPDATE PSOPTIONS set MAXCHUNKSIZE = :1", &iOriginalChunkSize); End-If; MessageBox(0, "", 0, 0, &iPhotoCnt " employee photos committed."); CommitWork(); End-Function;

Function PutPhoto:

This function actually loads the photo.
1)The photo file is first loaded to PSFILE_ATTDET using the PutAttachment function.
2)The blob data of the photo is then selected into a variable using SQLExec();
3)The photo file row is then deleted from PSFILE_ATTDET.
4)PS_EMPL_PHOTO.PSIMAGEVER is set to the number of seconds since 01-JAN-2000. This method was found in sample code obtained by Brady Dunn from an Oracle mailing list.
5)The row is is updated or inserted on PS_EMPL_PHOTO depending on whether the employee ID already exists.
6)A commit is performed every 50 rows.

Function PutPhoto()
Local integer &iVersion, &iResult;
Local string &strAttachFileName;
Local string &strMsg;
Local any &anyPhotoBlob;
Local boolean &bFound;

&recEMPL_PHOTO.SetDefault();
&strAttachFileName = &strImageEmplid ".txt";
&iResult = PutAttachment("record://PSFILE_ATTDET", &strAttachFileName, &strImageFileFullName);
If &iResult <> 0 Then
MessageBox(0, "", 0, 0, "*** Start Message ***");
MessageBox(0, "", 0, 0, "PutAttachement failed");
MessageBox(0, "", 0, 0, "Return Code=" &iResult);
MessageBox(0, "", 0, 0, "Image File=" &strImageFileFullName);
MessageBox(0, "", 0, 0, "Blob File=" &strAttachFileName);
MessageBox(0, "", 0, 0, "*** End Message ***");
CommitWork();
End-If;
SQLExec("SELECT FILE_DATA FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &strAttachFileName, &anyPhotoBlob);
SQLExec("DELETE FROM PSFILE_ATTDET WHERE ATTACHSYSFILENAME = :1 AND VERSION = 1", &strAttachFileName);
REM MessageBox(0, "", 0, 0, "&strImageEmplid=" &strImageEmplid);
&recEMPL_PHOTO.EMPLID.Value = &strImageEmplid;
&bFound = &recEMPL_PHOTO.SelectByKey();
&recEMPL_PHOTO.EMPLID.Value = &strImageEmplid;
&recEMPL_PHOTO.EMPLOYEE_PHOTO.Value = &anyPhotoBlob;
/* Set the version to seconds from year 2000 */
&recEMPL_PHOTO.PSIMAGEVER.Value = (Days365(Date3(1999, 12, 31), %Date) * 86400) + (%Time - Time3(0, 0, 0));

If &bFound Then
&recEMPL_PHOTO.Update();
Else
&recEMPL_PHOTO.Insert();
End-If;
If Mod(&iPhotoCnt, 50) = 0 Then
MessageBox(0, "", 0, 0, &iPhotoCnt " employee photos committed.");
CommitWork();
End-If;
End-Function;

Function GetEmplidFromFileName:

This function is passed a filename. As mentioned earlier, that filename contains the first and last name of the employee
1) The extension (.jpg) is removed from the file name.
2) The remainder is separated into first and last name;
3) For Japan, we are unsure whether the name was in firstname-lastname or lastname-firstname format. So the PS_NAMES table is searched for a match with either format.
4) If one and only one match is found on PS_NAMES, the corresponding EMPLID is returned.

Function GetEmplidFromFileName(&fileName As string) Returns string
Local integer &iSepPos, &iRowCnt;
Local string &strFirstNameFirst, &strLastNameFirst, &strLeftPart, &strRightPart, &strPersonName, &strFirstNameSrch, &strLastNameSrch;
Local string &strSQL, &strEmplID, &strDebug;
&strPersonName = LTrim(RTrim(&fileName));
REM MessageBox(0, "", 0, 0, "&strPersonName=" &strPersonName);
If None(&strPersonName) Then
MessageBox(0, "", 0, 0, "NO EMPLID:&strPersonName (from &fileName) is blank");
Return "";
End-If;
<* chop off file extension *>
&strPersonName = Left(&strPersonName, (Len(&strPersonName) - 4));
If None(&strPersonName) Then
MessageBox(0, "", 0, 0, "NO EMPLID:&strPersonName (chop extension) is blank, &fileName=" &fileName);
Return "";
End-If;
<* find the position of the separator, underscore, blank, or dot *>
&iSepPos = Find(" ", &strPersonName);
If &iSepPos <= 0 Then &iSepPos = Find(".", &strPersonName); End-If; If &iSepPos <= 0 Then &iSepPos = Find("_", &strPersonName); End-If; If &iSepPos <= 0 Then &iSepPos = Find("-", &strPersonName); End-If; If &iSepPos <= 0 Then MessageBox(0, "", 0, 0, "NO EMPLID:&iSepPos=" &iSepPos ", separator not found, &strPersonName=" &strPersonName); Return ""; End-If; <* separate the name into left and right hand parts *>
&strLeftPart = Left(&strPersonName, (&iSepPos - 1));
&strRightPart = Right(&strPersonName, (Len(&strPersonName) - &iSepPos));
<* find emplid by first and last name, assume incoming file name was in last name first format *>
/*
&strLastNameFirst = Upper(&strLeftPart &strRightPart);
&strFirstNameFirst = Upper(&strRightPart &strLeftPart);
&strSQL = "select count(1), EMPLID from PS_NAMES where LAST_NAME_SRCH in (:1, :2) and FIRST_NAME_SRCH in (:1, :2) group by EMPLID";
SQLExec(&strSQL, &strLastNameFirst, &strFirstNameFirst, &iRowCnt, &strEmplID);
*/
&strLastNameSrch = Upper(&strRightPart);
&strFirstNameSrch = Upper(&strLeftPart);
&strSQL = "select count(1), EMPLID from PS_NAMES where ((LAST_NAME_SRCH = :1 and FIRST_NAME_SRCH = :2) or (LAST_NAME_SRCH = :3 and FIRST_NAME_SRCH = :4)) group by EMPLID";
SQLExec(&strSQL, &strLastNameSrch, &strFirstNameSrch, &strFirstNameSrch, &strLastNameSrch, &iRowCnt, &strEmplID);
&strDebug = "DEBUG: ";
&strDebug = &strDebug "&iRowCnt=" &iRowCnt ", ";
&strDebug = &strDebug "&strEmplID=" &strEmplID ", ";
&strDebug = &strDebug "&strPersonName=" &strPersonName ", ";
&strDebug = &strDebug "&strLastNameSrch=" &strLastNameSrch ", ";
&strDebug = &strDebug "&strFirstNameSrch=" &strFirstNameSrch;
If &iRowCnt <> 1 Then
MessageBox(0, "", 0, 0, "NO EMPLID:&iRowCnt=" &iRowCnt ", should equal 1");
MessageBox(0, "", 0, 0, &strDebug);
Return "";
End-If;
Return &strEmplID;End-Function;

Main processing (not within any function):

This code drives the program. It
1) Retrieves an array of the filenames in C:\EMPL_PHOTO that have a (.jpg) extension.
2) If no files are found the processing is complete.
3) On the first loop iteration, PutPhotoInit() is called to prepare for images to be inserted.
4) For each file name in the array
a) The array entries contain the full path, e.g., C:\EMPL_PHOTO\John_Doe.jpg. The directory is removed leaving only the filename John_Doe.jpg.
b) The filename is passed to the function GetEmplidFromFileName().
c) If an employee ID is returned the image file is saved to the database by calling PutPhoto();
5) After the loop is complete PutPhotoTerm() is called to reset back to pre-run state.


<* Load list of image file names into array *>
&arrImageFileList = FindFiles("C:\EMPL_PHOTO\*.JPG", %FilePath_Absolute);
If &arrImageFileList = Null Then
MessageBox(0, "", 0, 0, "No files found");
Else
MessageBox(0, "", 0, 0, "found " &arrImageFileList.Len " photo files.");
For &iIdx = 1 To &arrImageFileList.Len
&strImageFileFullName = &arrImageFileList [&iIdx];
&strImageFileName = &strImageFileFullName;
If &iPhotoCnt <= 0 Then PutPhotoInit(); &iPhotoCnt = 1; Else &iPhotoCnt = &iPhotoCnt + 1; End-If; MessageBox(0, "", 0, 0, "Processing file " &iPhotoCnt ":" &strImageFileFullName); &strImageEmplid = ""; <* remove the path to extract the simple file name *>
&iStartPos = Find("\", &strImageFileName);
If &iStartPos > 0 Then
&iLastSlashFoundPos = &iStartPos;
&iStartPos = &iStartPos + 1;
Else
&iLastSlashFoundPos = 0;
End-If;
While &iStartPos > 0
&iStartPos = Find("\", &strImageFileName, &iStartPos);
If &iStartPos > 0 Then
&iLastSlashFoundPos = &iStartPos;
&iStartPos = &iStartPos + 1;
Else
Break;
End-If;
End-While;
If &iLastSlashFoundPos > 0 Then
&strImageFileName = Substring(&strImageFileFullName, (&iLastSlashFoundPos + 1), Len(&strImageFileFullName));
&strImageDir = Left(&strImageFileFullName, (&iLastSlashFoundPos - 1));
Else
&strImageFileName = &strImageFileFullName;
&strImageDir = "";
End-If;
&strImageEmplid = GetEmplidFromFileName(&strImageFileName);
If All(&strImageEmplid) Then
PutPhoto();
End-If;
<* not sure that we are going to want to delete the files &fImage = GetFile(&strImageFileFullName, "E", %FilePath_Absolute); &fImage.Delete(); &fImage.Close(); *>
End-For;
PutPhotoTerm();
End-If;

Lokesh Madnavat