Peoplesoft Interview Stuff: August 2011

Peoplesoft interview Stuff - Hope ,it can helpfullll...you fellows..... Best Wishes... u all
If u feel good..send me ur stuff to gvenrey.gmail.com. Advance Thnaks...

ADDING CUSTOM RECORD TO ” PSQUERY RECORD LIST” Go to PeopleTools/ Security/ Query Security/ Query Access Manaer

• search the tree " • we can add it against any delivered tree or we can create a tree for all the custom records
• in General Tables tab I have inserted my record as child record,,, • ok now save the tree • close query tool and open again
Go to Query Security and run the process ‘QUERY ACCESS LIST cache’ with ENABLE option.

How to give access to the records that are to be used in a query?
To give access to the records that are to be used in query, we have create a new query security’’tree’’and add the records which we want to give the access and then assign a access group to the tree.

After that we have to ADD that query tree and query access group to the PL. What is query tree?

PS query uses a special type of tree, query tree, that organizes record definitions into access groups. In the Query Security panel group, U specify which access groups an operator or operator class has access rights to. Users can only create or run queries on record definitions from access groups they have rights to.

Dyanamic Roles: In PS security, Users are assigned to Roles, Roles mapped with Permission lists and Permission lists holds the list of accessible objects. There will be situation arise in PS to assign the roles dynamically to Users.
PS support this feature by Dynamic roles assignment process. We assign the roles dynamically to Users based on Role Query.

1. Create a new Public Role query in Query manager to provide the list of User Id based on business rules.

2. Create a new role in ‘Roles’ component and Navigate to the ‘Dynamic Members’ tab. Check the ‘Query Rule Enabled’ flag to ‘Y’ and select the Role query in the ‘Query Rule’ prompt and run the ‘DYNROLE’ or ‘DYNROLE_PUBL’ (if App message needs to be triggered) AE process.

After successful run of the process, the Users who are fetched through the role query will be assigned with the role dynamically. When the Application process reruns, all the Dynamic role entries will be cleared and the Users will be reloaded based on the current output of the role query.

3. Dynamic roles will also be listed under ‘User Profiles’ navigation.

However it can be distinguished from the Static roles by the ‘Dynamic’ flag in the ‘Roles’ tab.

Overriding Search record of a component/Dynamically changing the Search record of a component.
Consider the example of Dependent Information PAGE which is available in Workforce Administration folder. The DEPEND_BENEF component is registered in the portal under the ADMINISTER_WORKFORCE menu & the default search record was PERS_SRCH_GBL, this is search record will allow the administrator to search for an employee and updating the details.
If we need the same page in the ESS navigation then we need to clone the component and make all the necessary code change, this is time consuming process. Instead of this we can register the same component in the ROLE_EMPLOYEE menu and choose the OVERRIDE option in the MENU ITEM properties and specify the record HR_SS_PERS_SRCH as it is common for the ESS components.

And then add the below code in the DEPEND_BENEF postbuild event.
If %Menu = "ROLE_EMPLOYEE" Then AllowEmplIdChg( True); End-If;
Overriding the Search Rec: the search record set in the CMPT properties remains unchanged. We reuse the same CMPT multiple times with different search records. We can accomplish this by overriding

the CMPT search record at runtime when the CMPT is opened from a menu item without creating separate copies of the CMPT. CMPT override is temporary, and occurs only when the CMPT is opened from the menu item in which the override is set.

By reusing CMPT s in this way, U can limit redundancy, keeping Ur application smaller and easier to maintain.

Disable Srch_Pg: When the PS page is visited through the navigation, the PS displays the search page to collect the key information to display the page details.

However the Ps provides different functionalities to override the search key functionality in the Component:
Search record: The Cmpt search page is structured based on the search key values of the Cmpt Search record.

When the Search record does not contain any search key fields in the Record structure then the search page will not be displayed for the cmpt.

For E.g. INSTALLATION table does not contain any key structures and it has been setup as the Component Search record. When the component is visited through PIA, the search page will be bypassed and sent directly to PS pages.

PeopleCode: Consider the scenario where the record with search key structure needs to be setup as the Component search record and it need to bypass the Component Search page. In such case, PS delivers a ‘SetSearchDialogBehavior’ function to pass the key values through the PeopleCode and bypass the CMPt search page.

For E.g. HEX_TEST_VW is setup as the search record for HEX_TEST_CMP component. HEX_TEST_VW has the search key of EMPLID field. The value of EMPLID field is passed through the PeopleCode and the ‘SetSearchDialogBehavior’ function is called to bypass the component search page. Note: When the Search key passed through the PeopleCode is not matched then the control will be in Component search page to enter the new search key values

URL: The search key values can be passed through the URL also. The URL will be concatenated with the Search Key field name with its value after the menu and the component information.

In such case, when the user logins to the PS , the PIA will be automatically navigated to the specified page with the Search key values from the URL. For E.g. The following URL navigates to the HEX_TEST_CMP component in the UNISYS_TEST menu with the search key field value for Emplid as ‘PA002’.

In such case when the user logins to the system with the PS credentials the PIA transfers to the specified component with the search key value. This type will be more useful when the URL information is passed through the mail.

Note: When the Search key passed through the URL is not matched then the control will be in Component search page to enter the new search key values.
http://XXXXX/psp/hrms89/EMPLOYEE/PSFT_HR/c/UNISYS_TEST. HEX_TEST_CMP.GBL?EMPLID=PA002

How to Create a Dynamic View in PS Place in FIELDFORMULA Event of the Field, where PROMPT Table is set

&strDynamic = "SELECT FACILITY_NUM, FACILITY_NAME FROM PS_FACILITY_ADD";

* CREATE A DYNAMIC VIEW WITH ONE FIELD FACILITY_NUM (as Key).

* We can give any QUERY in RECORD VIEW, as it will be anyhow overridden by the PeopleCode Query.

Use Dynamic SQL for Prompts - SqlText

This property is valid only for fields that have a dynamic view as their prompt record. If you set SqlText to a non-null value, that text is used instead of the dynamic view's normal text used for prompting.

Suppose you wanted to have a different prompt table depending on the settings of other fields in the row. Normally you could use %EDITTABLE to dynamically specify the prompt table you want. However in this case there are too many possible combinations of values, which would require too many views. Furthermore, the values are customizable by the end-user or the application, which means even if you, the developer, wanted to, you couldn't provide all the combinations of views necessary. However you can generate the desired SQL text for the view in PeopleCode based on what the user enters.

Local string &SQLSTRING; Function set_jrnl_id_prompt();

&SQLSTRING = "SELECT DISTINCT JOURNAL_ID, BUSINESS_UNIT_IU, JOURNAL_DATE, LEDGER_GROUP, SOURCE, SYSTEM_SOURCE, PROC_PART_ID, JRNL_HDR_STATUS, DESCR FROM PS_JRNL_HEADER WHERE JRNL_HDR_STATUS IN ('N','E','V')" If All(JRNL_EDIT_REQ.BUSINESS_UNIT) Then

&SQLSTRING = &SQLSTRING | " AND BUSINESS_UNIT_IU='" | JRNL_EDIT_REQ.BUSINESS_UNIT | "'" End-If;

If All(JRNL_EDIT_REQ.LEDGER_GROUP) Then

&SQLSTRING = &SQLSTRING | " AND LEDGER_GROUP='" | JRNL_EDIT_REQ.LEDGER_GROUP | "'" End-If;

If All(JRNL_EDIT_REQ.SOURCE) Then

&SQLSTRING = &SQLSTRING | " AND SOURCE='" | JRNL_EDIT_REQ.SOURCE | "'" End-If;

If All(JRNL_EDIT_REQ.SYSTEM_SOURCE) Then

&SQLSTRING = &SQLSTRING | " AND SYSTEM_SOURCE='" | JRNL_EDIT_REQ.SYSTEM_SOURCE | "'" End-If;

If All(JRNL_EDIT_REQ.PROC_PART_ID) Then

&SQLSTRING = &SQLSTRING | " AND PROC_PART_ID='" | JRNL_EDIT_REQ.PROC_PART_ID | "'" End-If;

GetRecord().GetField(Field.JOURNAL_ID_FROM).SqlText = &SQLSTRING;

GetRecord().GetField(Field.JOURNAL_ID_TO).SqlText = &SQLSTRING; End-Function;

Dynamic drop-down list / How to change order of the Translate Values

a. Is it possible to create a dropdown list on a page and have the values in that list based of some criteria. In short, dynamic drop down list?
b. How do I change the order of translate value in drop down list for a field on a page try using peoplecode, by default it presents in ascending order?

The quickest way to do it is to use PeopleCode to force wer values in the list by using following 2 functions of Field class.

1. Field.ClearDropDownList(): It removes the current contents of the DDL.
2. Field.AddDropDownItem([Value],[Descr]): It takes two string parameters,
[Value]: It is actual value which is mapped to the dropdown item [Descr]: It is actual text which is visible as a dropdown item
Note that the DDL is automatically alpha sorted by Descr not value.
/* Define a field object for which we want to populate the drop down values */

&FLD_temp =Field.ACTION_RSN; /* Clear the current dropdown values */ &FLD_temp.ClearDropDownList();

/* Following logic would be used to dynamically populate stanalone rowset with the values */

&RS_temp = CreateRowset(Record.ACTN_RSN_TBL); &RS_temp.Fill("WHERE ACTION= .......");

/* Add the values to the dropdown list*/ For &I = 1 To &RS_temp.ActiveRowCount

&FLD_temp.AddDropDownItem (&RS_temp.GetRow(&I).GetRecord(1).ACTION_REASON.Value,

&RS_temp.GetRow(&I).GetRecord(1).DESCR.Value); End-For;

Dynamic Prompts for Record Field This is accomplished by 2 ways:

Using EDITTABLE fields for Prompt Table, Using Dynamic Views

1. Usage of EDITTABLE Fields

In this method, Prompt table property of Record Fields should be assigned with %EDITTABLE value.

Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. The Record.Field DERIVED.EDITTABLE should be assigned with value either in one of the Peoplecode events (Either in FieldChange or FieldEdit or RowInit events). This is simply done by DERIVED.EDITTABLE = “PERSON_NAME”;

Note: EDITTABLE Field should be present in the SAME Page, where Record Field (In this case, The Field is nothing but EMPLID) is also referred.

If the Record Field is not coming under Component Search Record Field, there is no need for placing the EDITTABLE field in the Page.

1. Steps to add Edittable fields for prompt table

a. Add %Edittable as prompt table to record field.
Note: If there are more than one dynamic prompt on the page then use %Edittable1, %Edittable2...
b. Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. Add DERIVED.EDITTABLE as invisible field on the page.
c. Assign value to the DERIVED.EDITTABLE field on the page with View name in one of the peoplecode events Rowinit, FieldChange, Postbuild etc.
if none(DEPT_TBL.SETID) then DERIVED.EDITTABE = "ALL_DEPT_VW";
else DERIVED.EDITTABLE = "SETID_DEPT_VW" end-if;

2. Usage of Dynamic Views: As we know, while creating Dynamic view, there is no need to specify the SQL.

This SQL should be generated dynamically and the same should be assigned to the Record Field.

Say for example, if we see the above Record Field TASK_PROFILE_ID, it is assigned with dynamic prompt table view TL_TSKGRP_DVW and this view is dynamically initialized by the following Peoplecode, which can be assigned both in FieldChange and RowInit events of the Record Field TASK_PROFILE_ID.

RECORDNAME.TASK_PROFILE_ID.SqlText = “SELECT T.TASKGROUP, T.TASK_PROFILE_ID, T.DESCR FROM PS_TL_TSKGRP_PRF_W T WHERE T.TASKGROUP = ‘” | &TSKGRP | “‘ AND

T.EFFDT = (SELECT MAX (T1.EFFDT) FROM PS_TL_TSKGRP_PRF_W T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.TASK_PROFILE_ID=T.TASK_PROFILE_ID AND T1.EFFDT<= %DATEIN (‘” | &maxdate | “‘) )”; Both &TSKGRP, &maxdate are dynamic bind parameters.

2. Steps to add Dynamic Views for prompt table a. create a record as dynamic view.
b. Assign dynamic view created in previous step as prompt table of the record field.
c. The dynamic view can be initialized in one of the peoplecode events Rowinit, FieldChange, Postbuild etc. The variables can be passed dynamically to the sql text.
if none(DEPT_TBL.SETID) then RECORD.DEPTID.SQLText = "Select Setid, deptid from ps_dept_tbl";
else RECORD.DEPTID.SQLText = "Select Setid, deptid from ps_dept_tbl where setid = " || DEPT_TBL.SETID.Value; end-if;
The first sql will get all the departments.
The 2nd sql will fetch only departments specific to setid. DEPT_TBL.SETID.Value is passed as a parameter to the sqltext.

Key - Search Key - Duplicate Order Key - Alternate Search Key - Descending Key

· Key: Select to identify the field that uniquely identify each row. We can’t have duplicate values for PK’s.

· Duplicate Order Key: Select to indicate that duplicate values can occur.

· Alternate Search Key: Select to identify the field as descending. If we clear this check box, the key is ascending. This option applies only to a field that is specified as a key, duplicate order key, or alternate search key. PS uses descending order primarily for effective date fields; most character keys are ascending.

· Search Key

· Select to make the field available on the basic and advanced search or lookup pages. A search key is valid only for keys and should be used only in search and prompt records.

If we select this check box, the system automatically selects List Box Item

· Search Edit Enabled only if Search Key is selected.

Selecting this option enforces the required property and table edits on the search page. It also enforces these edits under circumstances where the search page would normally be bypassed. With this option, the user no longer has the ability to perform partial searches on this field.

· List Box Item

Select if we want the field to appear in the list box preceding a page. If a field has values in the translate table and we designate it as a list box item, the list box automatically shows the translated value instead of the code.

NO EDIT: Select if we don’t want to edit the contents of this field against a table. This is the default, which makes the options in the Table Edit group box unavailable.

TBL EDIT: Select to edit the contents of the field against the values that are maintained in the specified table. When we select Table Edit, the Type field becomes available in the Table Edit group box.

TYPE: Select from the drop-down list box:

Prompt Table with No Edit: Provides users with a list of suggested values, but does not edit the contents of the field against the prompt table. Users can enter any value.

Prompt Table Edit: Edits the contents of the field against the values that are maintained in the specified prompt table.

Translate Table Edit: Edits the contents of the field against the translate table.

The translate table stores values for fields that must be validated but don’t need individual tables of their own.

Yes/No Table Edit: Makes the values for this field Y (yes) and N (no) only.

Prompt Table: Enter the name of the record definition that we want to use as the prompt table for this field.

If we want the prompt table to vary depending on the context of the field, indicate a field in the derived or work record (DERIVED) that contains the name of the prompt table at runtime. Then, in this field enter %FieldName.

The % is required, and indicates that we ’re referencing a derived or work record definition named DERIVED. FieldName is the name of the field in that DERIVED record definition.

Set Control Field: overrides the set control field of the record definition specified in the prompt table.

Interactive Mode

In interactive mode ,when the user exits a field that has a field-level event ( FieldChange, FieldEdit, RowInsert PCODE, prompt validation, related display),A transmission to the AS is performed to run that field-level event. The page is displayed again.

Deferred Mode: (bypassing sym edits(name format, field format, resonable dt, required,tbl edits) as well as pcode written at

f-edit,f-change events) Advantages:1) Reduces the network traffic. 2) Increases the performance.

CMPT Buffer? Area in memory, which stores data for the currently active CMPT . Whenever U open a CMPT the entire data for that CMPT is retrieved upfront and stored in the AS . CMPT buffer is a temporary buffer allocation. What data comes into CMPT Buffer? Primary record data, related display fields, translate values and dwk records.

A level 0 primary record and a search record can be different but the key field values should match.

Parent Child Relationship?

Some times while creating records, U 'll have a field within a table for which we want to allow multiple occurrences, in which case we create a subordinate or child table. To maintain parent child relationship if the parent table contains one key field the child table should contain the same key field and one additional key.

Occurs Level: Occurs level determines the level of the object that we inserted. (To specify the lvl of rec.fields in that SA)

we can set levels for example after inserting a level based control in level 2 if U want to change the level based control to level 1 U can change the occurs level.

Occur Count: Occur Count determines how many rows should be displayed default on that SA.

For ex if U want the scroll to display only 5 rows then we will give occurs count to 5.

Related Display field and Display Control field? Data entry order of DCF should be prior to RCF.

A RCF is for display purposes only - it always references a row that's not being updated from the current panel.

The display control when U select RDF, U need to relate it to the appropriate control.

A list of all the controls on the panel marked as display control fields in the Related control field box. Select the control field to which this particular related display is related.

Create a view with OPRID (key) and NAME as fields in it. The SQL definition for the view should be like: …SELECT a.oprid,b.name FROM PSOPRDEFN a, ps_personal_data b where a.oprid = b.emplid;

Make the OPRID field a DCF and hide it. Place the NAME field from the new view as a RDctlF to the OPRID field on the page.
This should display the Name of the person instead of the OPRID on the page.

Allow Expert Entry This is available for both processing modes. See To use expert entry, select:

– The Allow Expert Entry check box on the Internet tab of the Component Properties dialog box.

– The Enable Expert Entry check box in the user's profile.

– If these check boxes are selected, an Expert Entry check box appears on the bottom of the page at runtime. If the user selects this check box, the transaction runs in deferred mode, regardless of the setting on the component, page, or field property.

– When we set the component property to deferred mode, that component operates in deferred mode processing for all users, not just those who have expert entry enabled in their profiles.

Search Record: The search record controls access of rows of data in a table, and its keys and ASK’s appear on the SRCH-PG. The search record might also contain logic to filter the search to secure rows of data—row-level security is implemented in this way. search returns all data rows to a maximum of 300.

• Search record is the record used to populate the panel group's level-zero search key fields; this enables the SYM to identify a unique row of data for the level-zero primary records in the panel group's panels, build a panel buffer for the panel group, and display the panel.

ADD search record: It is specifically used for ADD actions. If we are using a search view as a search record for performance in this situation we will put the main record as a add search record so that we can enter new values in this main record. The SYM default is the standard search record if we don't specify an add search record

Diff between Search record and Add search record..? When we mention a record name in the search record option in the CMPT properties then the searching will take place from that record when the CMPT search page appears.

When we add a new value in the add mode the record name that we specify in the add search record option the new value gets stored there. By default if we don’t mention any value in the add search record option then the new value will be stored in the table mentioned in the search record option.

The ACTIONS provide the ability to the user to perform specific transactions on the table/record associated with the cmpt.

Add – If this is selected then this allows user to insert record in the table through component. If you have a component where you want to add an employee in the table. Eg: Job component you will not have add.

If you uncheck this then the user will not be able to add/insert new values in the table through the component.

Update/Display – Allows the user to update the existing records in the table through component.

and user can see only the most effective dated row less than the sysdate, …….

The design consideration is that for using update /display all and correction the component should have EFFDT’ed record primarily. For example, see Job component for the update/display all and correction.

When the update/display all and correction is selected the entire set of records will be visible.

Update/Display All – This allows the user to modify/update current and future records.

Only in the Correction Mode user will be able to modify the history, current and future rows.

Menu Types:

1. Standard menu: (Custom): menus provide an internal reference for CMPT s and pages. It appears in the menu bar of a PS application . They are grouped logically into menu groups to assign security. They are not used for navigational purposes.

2. Pop-up menus appear when a user clicks a pop-up button.

“Allows the user to navigate related information in other areas of application by right clicking on a page or CMPT. “

What are the 3- actions that can be attached to menu? CMPT Separator PCODE

Allowed actions in menu? Add, Update/Display, Update / Display All, Data Entry and Correction.

How can a CMPT have more than one search record? Give a situation.

Effective Date? Date on which a table row becomes effective, the date that an action begins.

To keep historical, current, and future information in tables. Use the information to review the past and plan for the future. There are 3- types of Effective Dates:

• 1. Future: Data rows that have effective dates that are after the SYM date, which is usually today’s date.

• 2. Current: Data row with the most recent EFFDT that is closest to today’s (SYM) date, but not a future date. Only one row is the current row. 3. History: Data rows that have effective dates before the current data row.

• For effective-dated rows, U can have multiple occurrences of future and history, but only one current row of data.

• EFFDT is almost always a key and almost never a list item. Activate the Descending Key attribute so that the row with the most recent effective date appears first on pages. U might enter %DATE (current SYM date) as the default constant for this field.

Effective Status

• In prompt tables, EFF_STATUS (Effective Date Status) usually accompanies EFFDT. When used with EFFDT, It enables the SYM to select the appropriate effective-dated rows.

• For example in Department table suppose we inactivated one dept from so and so future EFFDT, from that future date onwards this dept is going to be inactivated, after that whenever we click the link prompt button of dept id field we can see only active departments.

• U can also use EFF_STATUS by itself as a simple status field, but don’t change the translate values. They must be A (active) and I (inactive) for EFFDT to work properly. If U need a status field with different values, use or define a different field.

The EFFSEQ (Effective Sequence) If EFFSEQ isn’t paired with EFFDT, EFFSEQ has no special function and can be used as a simple sequencing field wherever U need one.

• It enables to enter more than one row with the same effective date. Assign a unique sequence number to each row that has the same effective date. Effseq number starts with’0

• For example, suppose that U want to enter both a transfer and a pay rate change for an employee, and both actions are effective on the same day.

Enter the transfer on the job data pages as usual, and leave the Effective Sequence Number field as 0 (zero). Then, insert a row to enter the change in pay rate. This time, the EFFDT is identical to the previous row, but enter 1 in the Effective Sequence Number field.

Whenever we want to enter more than one transaction on the same day for an employee like promotion and salary hike we will use effective sequence.

SELECT * FROM PS_EMPL_TBL A WHERE EMPLID = “VENKAT,G”

WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_EMPL_TBL B

WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT <= SYSTEMDATE);

AND A.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C

WHERE C.EMPLID = A.EMPLID AND C.EMP_RCD = A.EMP_RCD AND C.EFFDT = A. EFFDT); ****

SELECT * FROM PS_EMPL_TBL A WHERE EMPLID = “VENKAT, G”

AND A.EFFDT = (SELECT MAX (B.EFFDT) FROM PS_EMPL_TBL B

WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT <= SYSTEMDATE);

AND A.EFFSEQ = (SELECT MAX (C.EFFSEQ) FROM PS_JOB C

WHERE C.EMPLID = A.EMPLID AND C.EMP_RCD = A.EMP_RCD AND C.EFFDT = A. EFFDT);

Write the syntax to access third level record field using object oriented peoplecode?

&fld=Getlevel0()(1).GetRowset(Record.<level1 record>.GetRow(1),

GetRowset(Record.<level2record>).GetRow(1), GetRowset(Record.<level3 record>).GetRow(1),

GetRecord(Record.<level3 record>).GetFieild(Field.<field name>))

Here is a typical peoplecode example for reaching the level three reord using rowset peoplecode.

/* Need to insert a row when a flag = ‘Y’ after graying all other fields in the row in correction mode */
PeopleCode Event : COMPONENT_NAME(Component).GBL.PostBuild(Component PeopleCode)

Local Rowset &RS_Lvl0, &RS_Lvl1, &RS_Lvl2, &RS_Lvl3; Local number &insert_row, &lvl_1_row, &lvl_2_row, &lvl_3_row;

&RS_Lvl0 = GetLevel0(); &RS_Lvl1 = &RS_Lvl0(1).GetRowset(Scroll.LEVEL1_SCROLL_NAME);
&insert_flag = “N”; For &lvl_1_row = 1 To &RS_Lvl1.ActiveRowCount

&RS_Lvl2 = &RS_Lvl1(&lvl_1_row).GetRowset(Scroll.LEVEL2_SCROLL_NAME);
   For &lvl_2_row = 1 To &RS_Lvl2.ActiveRowCount
      &RS_Lvl3 = &RS_Lvl2(&lvl_2_row).GetRowset(Scroll.LEVEL3_SCROLL_NAME);
      For &lvl_3_row = 1 To &RS_Lvl3.ActiveRowCount

         &RS_adj_applied = &RS_Lvl3(&lvl_3_row).LEVEL3_RECORD_NAME.FIELD_NAME.Value;
         If &RS_adj_applied = “Y” Then                 &RS_Lvl3(&lvl_3_row).MPF_TINQ_LOG.EARNS_END_DT.Enabled = False;        &RS_Lvl3(&lvl_3_row).MPF_TINQ_LOG.MPF_ADD_SUBTRACT.Enabled = False;
            &RS_Lvl3(&lvl_3_row).MPF_TINQ_LOG.MPF_HOURS_TYPE.Enabled = False;
            &RS_Lvl3(&lvl_3_row).MPF_TINQ_LOG.MPF_HOURS_ADJ.Enabled = False;
            If %Mode = %Action_Correction Then
               &RS_Lvl3.InsertRow(&RS_Lvl3.ActiveRowCount);                End-If;         End-If;      End-For   End-For;    End-For;

AE is a PeopleTool used to develop, test, and run background SQL processing programs or batch programs. AE is designed for batch processing where a large (or small) amount of data will be processed without user intervention.

Standard this is a normal entry-point program. Upgrade Only which is used in PS upgrade utilities.

Import Only which is used by PS import utilities. Daemon Only a type of program used as a daemon process.

Transform Only a program type used to support Extensible Stylesheet Language Transformations (XSLT).

Application Library: An AE program without the main section.

§ It is a collection, or "library," of common routines that do not run as a standalone program.

§ When sections are defined as "Public," other programs can call the Sections, or routines, that exist in the "library" at runtime.

Step Properties: Commit: Specify the commit level for the step: (Default, before, after)

§ Default: Select to inherit whatever commit level we specified for the section in which the step resides.

§ After Step: commit a specific step within a section with no other commits.

§ Later: Select to postpone the commit until a subsequent commit occurs.

§ Frequency: numeric frequency. If non-zero, AE commits every N iterations, and then again after the last iteration.

On Error: Specify how PS AE should respond to an error at the step level. Does not apply to compile errors.

Select from: • Abort: The application terminates with an error message. • Ignore: The program continues but logs an error msg • Suppress: The program continues and presents no error message.

§ There are eight types of Actions that we can include within a Step, and a Step can contain multiple Actions.

Do When. Do While. Do Select. People Code SQL/Call Section Message Log Do Until. ( XSLT)

The maximum number of Actions a single Step can contain is seven. Mutually exclusive Actions within a single Step are Call Section and SQL Statement. Four types of Program Flow Actions: Do When. Do While. Do Select. Do Until.

Do When statement runs before any other actions in the step. Our program executes a Do When action only once

Allows subsequent actions to be executed if any rows of data are returned.

Do While, If present, runs before subsequent actions of the step. If the It does not return any rows of data, the action terminates.

Subsequent actions within the step are executed in a loop as long as at least one row is returned by the Select statement for it.

Do Until, It runs after each action when a step completes.If the Select statement returns any rows of data, the step terminates.

Use it, if we want the processing actions to execute at least once, until a Select statement returns some rows.

Do Select Is a Select statement that executes subsequent actions once for every row of data that the Do Select returns.

The subsequent actions within the step are executed in a loop based on the results of the Select statement.

Do Select Type. Select/Fetch, Re-Select, Restartable

§ Select/Fetch AE opens a cursor and performs a Fetch statement for each iteration of the loop to get each row from the Select statement. When a Fetch statement results in an end of table message, the looping is complete.

Non-restartable, because AE does not perform a checkpoint or a commit within the step

Re-Select: For each iteration of the loop, AE opens a cursor and fetches the first row from the Select statement.

The cursor is reopened for each iteration of the loop. Restartable.

§ Restartable: This is Select/Fetch that opens the cursor once, and then it performs a Fetch statement on each iteration of the loop to get each row from the Select statement. Restartable

User must make sure that the SQL we include within this action is such that, upon restart, the program recognizes where the previous run failed and where to restart processing.

On Return PC Property, we choose from the following 3- values: Abort The program issues an error and exits immediately.

Break The program exits the current step and section, and control returns to the calling step.

Skip Step The program exits the current step, and continues processing at the next step in the section. If this is the last step in the section, the calling step resumes control of the processing.

SQL: Usually a program terminates if a SQL Prepare statement or execute fails. If we select Ignore or Suppress, errors on executing programs are suppressed, but errors on compiles still cause the program to terminate

No Rows Property: If the SQL action does not return any rows, this is to specify what the AE program should do.

We choose from the following values: Abort: The program terminates. Continue: The program continues processing.

Section Break: AE exits the current section immediately, and control returns to the calling step.

Skip Step: AE exits the current step immediately and moves on to the next step.

AE ignores the commit for the current step at runtime. If the current step contains only one action, use Skip Step only to bypass the commit.

Call Section Action: Use to call another section.

§ We can call a local section defined within our current program, and we can make external calls to a section defined in another AE program. The external section we intend to call must have its access property set to Public.

§ Program ID Property Each AE program has a Program ID The default value is (current).

For accessing external program Sections, select Program ID from the dropdown list.

§ Section Name Property Defined the Section to be called part of the Program ID.

A call to MAIN section executes the entire program defined by the value in the Program ID field.

§ Dynamic Property: Enabled by selecting the Dynamic check box.

At Run time, the AE can be coded to call the different section based on conditions. In order to achieve it, we have to fallow few steps: Use the AE_APPLID and AE_SECTION fields in the state record to execute different sections depending on the conditions a program encounters during runtime. defined on the default state record for the program.

When issuing a dynamic call, both the section and the program ID must be dynamically set.

Selecting Dynamic automatically populates the AE_SECTION field with the symbolic value %SECTION, and the Program ID field with the symbolic value %AEAPPLID.

· These field values in the state record needs to be populated programmatically at run time before the dynamic call section is invoked. If the called Section lies in the same AE, the ‘AE_APPLID’ field can be omitted in the state record.

ReUse Statement Property: Enables the AE runtime process to compile the once, dedicate a cursor, and then re-execute it with new data as often as our program requires. Converts any %BIND references to real bind variables (:1, :2, and so on).

§ Bulk Insert : Used with statements like: INSERT INTO tablename (field1, field2...) VALUES (%BIND(ref1),%BIND(ref2)

– Other than Insert, the Bulk Insert option is ignored. This option turns on ReUse Statement.

– Holds all the data in a buffer and performs an insert only after a large volume of rows has gathered.

§ No : Disable ReUse Statement. The AE runtime process recompiles the SQL every time the loop executes.

§ By default, ReUse Statement is disabled. Yes : Enable basic ReUse Statement functionality.

State Record: It is a method for allocating variables for AE Programs

§ Sections, Steps and Action pass values to subsequent program steps through State Records.

§ Each AE has one or more AE State Records associated with it.

Characteristics: Name must end with the “_AET”.

§ It is a AE Record having a PROCESS_INSTANCE defined as the first field and the only key field.

§ There is only one row in the State Record for each Process Instance, or program.

§ Can be a dynamic work Record and SQL Table. Derived State Records cannot have their values saved to the database at commit time, and the values would be lost during a restart.

§ Used in a AE Restart. Only one record can be the default State Record.

§ During batch processing AE automatically performs all State Record UPDATEs. When a program starts, it INSERTS a row into the State Record that corresponds to the PI assigned to that program run.

§ AE updates the record whenever a COMMIT occurs. “When Restart is enabled and a commit occurs, all State Records that have been updated in memory will be written to the database, except for derived State Records, which are instead initialized.”

§ Then, after the program completes successfully, AE deletes the corresponding row in the State Record.

§ To set values in the State Record, we use the %SELECT construct in a SQL statement or write PeopleCode that references the State Field with the standard "record.field" notation.

§ %Bind Retrieves a field value from a state record in any Do or SQL action.

– To reference variables that exist within a default state record, use the following: %BIND(fieldname)

– To reference a state record other than the default, use the following: %BIND(recordname.fieldname)

§ With AE PeopleCode a state record data elements can be accessed using only a “Record.field” notation.

§ %SELECT Or %SELECTINIT :

If no rows selected to insert into state record, then old field values will be retained in the state record.

If no rows selected to insert into state record, then initializes state record fields.

§ EXIT (1) causes immediate termination of a PeopleCode program. Use this parameter to rollback database changes.

EXIT (0) caused immediate termination of a Peoplecode Program but don’t make rollback in the database.

%Mode: Gives the correct user action Mode, like add/update…%Join: Expands the join dynamically while executing this Meta sql

%EXECUTE(/). If you intend to include multiple SQL statements within a single AE action you should use the meta-SQL construct. The ‘/’ in the Execute parameter used to separate the multiple SQL statements one after other. Default separator is ‘;’

%Execute edit: Enables PS AE to support data dictionary edits in batch .To execute system edits against data which are "required", "reasonable date", "prompt table with no edit", "prompt table edit" "Translate table edit", "yes/no edit".

%Truncate - To delete the data from the tables. This is also applies mostly for temp tables.

Commit Considerations: By default, the commit values at the section and the step level are turned off.

§ No commits occur during the program run, except for the implicit commit that occurs after the successful completion of the program. Helps in Restartability. Commit early and often.

§ How: Divide our program into logical units of work by setting commit points within our program.

– For row-based processing, commit after every N iterations

– Steps like Do While, Do Until, or a Do Select action, we can set the frequency option.

–In Do Select type with Select/Fetch, all commits inside the loop are ignored, including the commit frequency if it’s set.

§ Overcome this by creating a SQL to keep track of records processed using a simple flag.

– At section Level use “Auto Commit” checkbox to commit after the section is complete.

– At Step Level Commit After and Later settings used.

Note. The Commit After, Later setting at the step level enables us to override the section setting if we don’t want to commit after a particular step.

Restartabilty: Using Restart:

Ø AE has the built-in ability to save a program state and restart where it stopped processing in the case of an abend.

Ø By default, AE doesn't perform a COMMIT until an entire program successfully completes.

Ø Each time that AE issues a Commit with Restart enabled, it records the current state of the program.

Ø With Restart, we can commit as often as we like without worrying about the data.

§ What: In case of abnormal termination or failure at a step in the program, we can restart request from the last successful check point.

§ When: As a general rule, restart is essential for programs that do set-based processing.

– Program Level: Make State record a SQL table.

§ Check both the PS AE Program Properties dialog box and PS Configuration Manager to make sure that Disable Restart check box is not selected.

– Section Level: Section Type ( Prepare Only & Critical Updates ), Step Level: Do Select Type – Restartable

§ How: There are two ways to restart an AE program: 1) From the command line. 2) From a process request page.

Restarting from the Command Line: Only developers and system administrators use this option.

§ Steps: Collect the command line values associated with the failed program.

Find these variables on the Process Details dialog box.

– Enter the command line syntax at the command prompt.

PSAE.EXE −CT DB_TYPE -CD DB_NAME -CO OPRID -CP PASSWORD -R RUN_CONTROL -AI PROGRAM_NAME -I PROCESS_INSTANCE

Restarting from the Process Request Page: Only for those programs that run on the server.

§ Steps: Open PS Process Scheduler by selecting PeopleTools, Process Scheduler, System Process Requests.

– Locate the run control ID number of the program to restart.

– To display the details of the failed process, click the Process Detail link.

– On the Process Request Details page, select Restart Request, and click OK.

Starting AE from Beginning when Restart Enabled

§ If we attempt to start a new process that matches the run control ID and user ID for another process, we receive a suspend error.

§ Delete the Failed AE row from the AE run control table and state record.

§ Steps: Run delete statement in our native SQL editor which would be of syntax bellow:

DELETE FROM PS_AERUNCONTROL WHERE OPRID=OPRID AND RUN_CNTL_ID=Run_Control_ID

Delete from our state record: DELETE FROM PS_MY_AET WHERE PROCESS_INSTANCE=Process_Instance

Disable Restart

§ From PS AE Program Properties dialog box. select File, Definition properties, and select the Advanced tab.

§ From Configuration Manager profile

Start Configuration Manager, select the Profile tab, and click Edit. Then select the Process Scheduler tab

§ Include the −DR Y option in the command line of PSAE.EXE.

Note: If we’ve disabled restart in any of these three places, restart is disabled.

Temporary Tables: Used for storing the transient data for the current run or iteration of program.

§ Contains only those rows which are affected by business rule.

§ By storing transient data in the temporary tables, we avoid the situation ..

where the batch program fetches the data, row-by-row , and runs the business rule, processes the data and then passes the updated data back to the database

§ Naming convention: base table name>nn Where nn is a number between 1 and 99, as in PS_TEST_TMP23.

– To reference a dedicated temp table, we need to use %Table(record)

§ Temp Tables has the ability to dedicate physical table instances to one, and only one AE program.

§ AE automatically manages the assignment of temporary table instances by controlling the

locking/unlocking the table before/after use.

Instance Count: It controls the number of physical tables to be created for each dedicated table for this program during the SQL build procedure in AD. We set this number to equal the number of parallel program runs that anticipate.

Ø As long as table is keyed to PROCESS_INSTANCE, and the application SQL includes the PI in the where clause, then the table can be shared among multiple process.

Assigning Batch Temp Tables: Instance count is the number of concurrent instances

Ø Batch Temp tables = Sum of the instance count for each program a table is used

Ø Naming convention PS_NAME_TMPnn, We can assign more than one temp table to an AE program.

Assigning Online Temp Tables:

Ø Temp Table Instances (Online) is: Number of temp tables built for all temp tables used in an AE program not designated as batch only.

Ø Temp Table Instances (Total) is: Diff between Total and Online is the number of temp tables built for custom allocation.

Parallel processing is used when considerable amounts of data must be updated or processed within a limited amount of time, or batch window. ---------Achieved using Temporary tables only.

§ How: Partition the data between multiple concurrent runs of a program, each with its own dedicated version of a temp tbl.

– Add logic select the distinct set of records from the bunch or create separate files to load.

– Define and build required Instances of Temp Table before all above steps.

To implement parallel processing,

– AD: Define and save all of the temp table records. We don't need to run the SQL Build process at this point.

– AE Designer: Assign temp tables to AE programs, and set the Instance Counts dedicated for each program. Assign each Temporary table to the AE program(s) using it, set the appropriate number of Instance Counts, and employ the %Table meta-SQL construct so that….. AE can resolve table references to the assigned temp table instance dynamically at runtime.

– PeopleTools Options: Set the global instance counts for online programs in PeopleTools Options. Set the number of temporary table instances on the PeopleTools Options page.

– Application Designer: Build all the temporary table records in Application Designer (SQL Build).

Note:

1. PS recommends that we insert the PROCESS_INSTANCE field as a key into the temporary tables we intend to use with AE.

2. Application Designer only creates a maximum of 99 temporary table instances.

Set processing is an SQL technique used to process groups, or sets of rows at one time rather than processing each row individually.

§ Set processing enables us to apply our business rule directly on the data in the database using an UPDATE OR INSERT/SELECT statement. Sothat we effectively eliminate the network round trip and database API overhead required to pull the data into the application program and then inserting it back. Improves performance.

Invoke AE Program: Following are the ways in which we can run an AE Program in the PS .

§ AE Process Request Page : which allows we to invoke the program through Process Scheduler with additional parameters.

To run an AE program in Two-tier mode: Select Edit, Run Program from the Application Designer toolbar.

§ PeopleCode SavePreChange or SavePostChange PeopleCode Synchronous process.

§ Using CallAppEngine(); CallAppEngine(applid [, statereclist ])

§ Process Scheduler: By defining the AE Process Definition, asynchronous execution

– Select PeopleTools, Process Scheduler, Processes to access the Processes Process Definition page.

§ Command Line: For Restarting, Development or testing, Debugging.

– executable (PSAE.EXE) followed by the required parameters

PSAE -CT dbtype -CS server -CD database_name -CO oprid -CP oprpswd? -R run_control_id -AI program_id -I process_instance -DEBUG (Y|N)? -DR (Y|N) -TRACE tracevalue -DBFLAGS flagsvalue -TOOLSTRACESQL value?-TOOLSTRACEPC value -OT outtype -OF outformat -FP filepath

Or, if our command line options are stored in a text file, we can enter: PSAE optfilename

Debug: “Debugging AE Programs” We can set the Debug option in the following locations:

• Launch the Configuration Manager and select the Process Scheduler tab. In the AE group, enable Debug by selecting the Debug checkbox. This is the method applies to all methods of invocation.

• If we used the command line option to invoke AE program, then we can just include the —DEBUG Y parameter in the command line we submit to PSAE.EXE.

• If we have PeopleCode in AE program, we'll want to turn the PeopleCode Debugger on, as well. When we launch program and the PeopleCode Action executes, we will enter the PeopleCode Debugger at that point.

To enable the PeopleCode debugger for AE:

– Sign on to PeopleTools using the same user ID that we are going to use to invoke the AE program.

– Open PS Application Designer. -- Select Debug, PeopleCode Debugger Mode.

Our AE program can be open on the desktop, but we do not need to open the AE program or the PeopleCode action that we want to debug.

– Select Debug, Break at Start.

– This causes the AE program to break prior to executing any PeopleCode programs within it.

– Execute the AE program that we wish to debug.

– At the AE Debugger prompt, enter the appropriate command that enables the desired debugging option.

AE Debugger - enter command or type ? for help. AETESTPROG.MAIN.STATS> ?

Debug Commands:

(Q)uit Rollback work and end program E(X)it Commit work and end program (valid between steps)

(C)ommit Commit work (valid between steps) (B)reak Set or remove a break point

(L)ook Examine state record fields (M)odify Change a state record field

(W)atch Set or remove a watch field (S)tep over Execute current step or action and stop

Step (I)nto Go inside current step or called section and stop

Step (O)ut of Execute rest of step or called section and stop (G)o Resume execution

(R)un to commit Resume execution and stop after next commit

Trace: Trace file is a document which helps us understand the internal logic and flow of the AE program. Trace is used to monitor the performance of AE

– AE step trace. - AE SQL trace. - AE statement timings trace.

– Database optimizer trace. -- By default, all AE traces are turned off

§ Enabling AE Tracing:

– Set command line options: Specify –TRACE option in command line.

§ Available from Unix and NT -- Not available from PeopleCode

– Set parameters in server configuration files.

§ To enable in the configuration files for both the AS and the PS Process

§ Only for AEs in Batch Mode

§ For programs invoked by PeopleCode and run on the application server, set the TraceAE parameter in the Trace section of the AS configuration file (PSAPPSRV.CFG). We can use PSADMIN to set this parameter.

§ In the PS Process Scheduler configuration file, set the TraceAE parameter in the Trace section to indicate a level of tracing. We can use PSADMIN to set this parameter.

– Set options in Configuration Manager.

§ Only if we are running AE programs on a Microsoft Windows workstation.

§ Start Configuration Manager, and select the Trace tab to choose the Trace.

§ Navigation: PeopleTools->Process Scheduler->Processes->Override Options.

Reviewing the Types of Queries: 6…

• User queries : User queries retrieve data from the database directly from Windows-based Query Designer or the web-based Query Manager/Query Vieour applications

• Reporting queries: Reporting queries are essentially the same as user queries, except that they are designed to be used by another reporting tool.

• Process queries : Process queries are queries that we intend to run periodically using a batch process. Create these automated batch processes using PS AE and the Query API.

• Role queries : Workflow uses role queries to determine to whom to send an email, form or worklist entry. A role query needs to return one or more role IDs based on the data that has been saved on the page that is triggering the routing.

Because a role query returns a list of role users, the record definition that we want is either PSROLEUSER (which lists role users and the roles to which they are assigned) or ROLEXLATOPR (which lists role users and their IDs). The only field that we will select in our query is ROLEUSER.

• Archive queries : These queries are generally only used by the PS Data Archive Manager.

If we have access to workflow queries that include Archive Query, Role Query, and Process Query.

• PS Nvision queries : Use PS Query to create a query in order to specify the data source for a PS/nVision report layout.

• Open the AD: Go -> Query

Steps - Creating a Query: Select the records upon which to base the new query.

– In the Database tab, right click on the record we want to include and select add record.

• Define the Fields: Add fields to the query content. Format the Query Output

• Define selection criteria including effective date options and any Unions or Expressions

• Define any advanced options Run the query Save the query

Adding Record: Right click on the first record and select ‘Add Record’. Set effective date options

Adding more records: Adding more records gives us two options.

– ‘New Join’ will join the table to the first table

– ‘New Union’ will create a condition for a Union

• Expressions are calculations that PS Query performs as part of a query.

• Use them when we must calculate a value that PS Query does not provide by default (for example, to add the values from two fields together or to multiply a field value by a constant).

• An expression can be treated just as if it oure a field in the query

Creating New Query Queries can also be created through PIA.

• Before we can create a new query, navigate to the Query Manager Search page.

• To begin defining a new query:

1. Select Reporting Tools, Query, Query Manager.

2. Click the Create New Query link to open to the Find an Existing Record search page.

Selecting Records: Access the Records page.

• If we know the name of the record we want, enter it in the Search For field on the Find Existing Record page, and click the Search button.

• Enter the name of the required record in the Search For box, and then click the Search button.

• Click Add Record.

Entering Selection Criteria: Access the Criteria page.

• Click the Add Criteria icon associated with the required field, on the Fields or Query page.

• Specify the criteria for that field, and then click OK to return to the Fields or Query page.

Defining Prompts: Access the Edit Prompt Properties page.

• Click on the Add Prompt button to add a new prompt, or click on the appropriate Edit button to edit an existing prompt. …. Click the Lookup button next to the Field to select a prompt field.

Changing Column and Sort Order for Multiple Fields

• Access the Edit Fields Ordering page by clicking the Reorder/Sort button on the Fields page.

Editing Field Properties: Access the Edit Field Properties page by clicking the Edit button on the Fields page.

Viewing Underlying SQL Code Access the View SQL page.

Saving a Query: After we make our changes in Query Manager, select Save.

• In the Query field, enter a short name for the query.

• Select a Query Type.

– Standard queries are designated as User queries. Workflow queries are either Process or Role queries.

• In the Owner field, select whether our query is Public or Private..

– Private means that only the user ID that created the query can open, run, modify, or delete the query.

– Public means that any user with access to the records used by the query can run, modify, or delete the query.

• Enter the Query Definition.

Running a Query: Select Reporting Tools, Query, Query Manager.

• If we know the name of the query we want to run, enter it in the Search For field, and click the Search button.

• Scroll to the name of the query that we want to run.

• To run a query from the list of queries, click the Run link located on the same row as the required query name.

Advance Options –Aggregate Functions:

• Sum : Adds the values from each row and displays the total.

• Count : Counts the number of rows.

• Min (Minimum) : Checks the value from each row and returns the lowest one.

• Max (Maximum) : Checks the value from each row and returns the highest one.

• Average : Adds the values from each row and divides the result by the number of rows.

Advance Options – Creating Sub-Queries:

• Click the Use As Criteria link on the Query page or click the Add Criteria button on the Criteria page.

• On the Edit Criteria Properties page, select Subquery as the comparison value.

• PS Query displays a special Query Manager view where we can select a record. See Subquery - Query page above.

• On the Subquery - Query page, select the required field.

• Use the hierarchy to navigate between the main query, subqueries, and unions

Advance Options – Creating Unions:

• Click the New Union link. (It is available on the bottom of each Query Manager page except for the Run page.)

• PS Query automatically switches to the Records tab so that we can start defining the second query. Define that query in the same way that we define other queries.

• When we ’re working on a union, each individual selection looks like an independent query, and for the most part they are independent. However, the first selection in the union—the one that we started before clicking the New Union link—has a special status. PS Query determines the ordering of the rows and columns based on what we specify for the first selection. It also uses the column headings that we defined for the first selection.

· On the page activate write the code to assign this default value. Assuming the record is at level zero:
Local record &lrec = getrecord(record.xyz); &lrec.getfield(field.lmn).value = “default value”;

Where is peoplecode stored? Database Server, PSPCMPROG

About (18)events in PeopleCode? Record fields have 15 events, Components have 2 events and Page has 1 event.

Record Level events takes precedence over the Component Record level events.

For Example: We have some code at both Record Level and Component Record level. In this case, first Record level code is executed first and then Component record level code is executed.

Data buffer contains the data other than the data in the component buffer (Data of other records)

Component buffer is the area in memory that stores data for the currently active component.

Whenever you open a component the entire data for that component is retrieved upfront and stored in the AS.

• It consists of rows of buffer fields that hold data for the records associated with page controls, including primary scroll records,related display records, derived/work records, and Translate table records.

UPDATE MODE: search init,search save,row select + ADD MODE: + prebuild,F-Default,F-formula, Row-init, Postbuild,Activate,-- f-edit-change or/and ROW-I-D – Save edit,Save prechange,workflow, SavepostChange.

Most commonly used PCODE events? FieldChange, RowInit, SaveEdit, FieldEdit, FieldDefault, and SavePreChange in that order.

Errors and Warnings?SearchSave, FieldEdit, SaveEdit and RowDelete primarily. May also code errors and warnings in RowSelect.

The error statement issues a message and the condition causing the error must be corrected before proceeding.

The warning statement issues a message and the user can proceed without changing any values. **********

Searchinit : Search init PeopleCode fires before the search record dialog box displays.

It triggers code in the search key fields of the search record. To control processing before the user enters values in the search key and alternate search key fields. To change the properties of the S.K and A.S.K fields programmatically.

(For this reason, it is often used to enhance roll level security by inserting and graying out certain values to the search dialogue page.) Purpose: To assign the default values and change the behavior of the search dialog box.

• EMPLID=%employeeId; Gray(EMPLID); AllowEmplIdChg(true); No errors or warnings in SearchInit

For example: Emplid=’100’ SetSeachDialogBehavior (0); , Emplid=’%UserId’ SetSeachDialogBehavior(0); ,

SearchSave

Firing: It enables us to control processing after search key values are entered, but before the search based on these keys are executed.

Purpose: To restrict the user to access the data from the database to Component unless knowing the S.K or A.S.K values. It forces the user to enter a value in atleast one field into the dialog box. This is placed on SK or AS keys of a component search record

Search save does not fire when Values are selected from the search list. (Validate entered value)

If none (Emplid, Name) then Error (‘we can’t enter without knowing the key values’); End-if;

IF Not Record Changed (psu_Student_tbl.Student_id) And %Mode <> ‘A’ Then Error (“we must enter at least one search value.”); End-if;

RowSelect [ Recommendation is don’t use this event for writing the code. Inefficient to filter out rows of data after they have been selected. ] For this PS gave some Built-in functions like Scroll Select family. Instead, use search record views and Effective Dated tables.

Firing: Fires at the beginning of the Component Build process in any of the Update action modes (Update, Update/Display All, Correction). This event also occurs after a Scroll Select or related function is executed.

Purpose: Performed as the component processor reads data into the component. To filter the data while reading rows from database to component buffer. It can prevent the Component Processor from loading a specific row of data into the component

For example if we enter any value in search key dialog box it(Application Processor) will goes to the database and search for the required value if found it displays the data. App Processor: App- processor searches in database for the search record, if found it displays.

Application processor brings the records from the database and place at buffer then buffer to the component.

Scroll select is the Built-in function useful to retrieve the data form the buffer to the scroll.

If %Page=Component.BEN_PROG_PARTCIPN then If COBRA_EVENT_ID>0 Then DiscardRow(); End-If; End-If;

Discardrow and Stopfetching (before warning message was used) For EFFDT table Update/Display action filters history rows Placed in Record Field or Component Record; Inefficient to filter out rows of data after they have been selected.

Instead, use search record views and effective dated tables. Also occurs after scroll select. ----Rarely used.

The RowSelect event is initiated at the beginning of the component build process in any of the update action modes (Update, Update/Display All, Correction).

Note. Instead of using row select processing, it is more efficient to filter out the rows using a search view, an effective-dated record, the Select method, or ScrollSelect or a related function, before the rows are sent to the browser.

Component Build Events

PreBuild Event Firing PreBuild fires before the rest of the component build events.

Purpose: To change the properties of the component and component fields according to the user requirements.

To define the component variables. To hide or unhide pages. Description: This event is only associated with components.

The PreBuild event is also used to validate data entered in the search dialog, after a prompt list is displayed.

Component array of array of string &CommentArray; Local array of string &Min; &Min = CreateArray(“1”,”100”,”200”,”300”);

• When error/warning the end-user returned to search page (if no search page then blank component displayed)

Validate entered value in search page Placed in Component, We cannot use Errors or Warnings as they cause runtime error.

Prebuild can be used to validate our search data, discarding rows.

FieldDefault Event: Purpose: To assign the default values to the field programmatically.

Enables to set default values to fields. Must attach it to the field to be populated. This is placed on Record field.

Sets default values when in ADD mode or a new row is inserted in the scroll area. Cannot use Errors or Warnings as they cause runtime error.

Description: We can assign default values in 2 ways. One is in AD and other is by using PeopleCode Field Default event.

If the default value is assigned in the Record field properties then the system won’t execute the PeopleCode default value.

Means The Record field properties will override the PeopleCode. (Placed in Record Field or Component Record Field)

Example: Userid (Field) Field default (Event) No Errors or warnings

Evaluate %userid When ‘ABC’ Employee Name= ‘srinivas’; Break; When ‘XYZ’ Employee name= ‘nani’; Break;

When-other Employee Name= ‘NO name’; Break; End-Evaluate;

Example2: Maritial Status (Field) / Field default(Event) <<< If Age>25 then MS=’Y’ Else MS=’N’ End-if;>>>

FieldFormula Event: Firing: Field Formula fires after Field default completes successfully, regardless of whether a field has a value.

Purpose: For writing the user defined functions in the functional libraries to store shared functions.

Used on Derived/work record definitions for PC function libraries(FUNCLIB),for storing internet scripts.

Description: PS recommendation is don’t use the event for writing the PeopleCode.

Draw back is it will execute for each and every user actions (except save/CANCEL) for each and every row in the component buffer, so it increase the network traffic and affects the performance of the Application program. No Errors or warnings Placed in Record Field

Funclibs can only be used in Record FieldFormula not component fieldformula to store shared functions.

--Not used for programming purpose.--Use RowInit and FieldChange events instead.

• DONOT place any record.field with code in FieldFormulae on a page.--

FieldFormula PeopleCode is always processed. Hence can be an overhead, SignOn PeopleCode can be used. Itperformed every time the panel is displayed on every row of data, its biggest drawback is the performance overhead it adds.

•

RowInit Event: Firing The RowInit event fires the first time the Component Processor encounters a row of data.

It also occurs when an operator inserts a row (F7),but just on the new row of data.

Purpose: RowInit event controls the intial appearance of the fields (True/False). R to set the initial state of component controls.

Description: Do not use Error or Warning statements in RowInit ,leads to the cancellation of the component.

IF %Component=Component.ABSENCE_HISTORY Then IF REG_REGION_COUNTRY=“NLD” Then

ABSENCE_HIST.RETURN_DT.Label=MsgGetText(6524,124,”Return Date &Time); End-If; End-If;

Fires ever row “fetched” and “RowInsert” Doesn’t fire when fieldchange/save/rowdelete Placed in Record Field or Component Record

PostBuild Event: Firing: The PostBuild event fires after all the other component build events have fired.

Purpose: To change the properties of the component and component fields according to the user requirements.

Used to calculate values and set display characteristics of a page or page control (sqlexec – verify permission)

To define the component variables. To hide or unhide pages. filling up scrolls.

This event is usually used to calculate values and set display characteristics of an Object.

Code that could be made available in RowInit can be out in PostBuild as it gets executed only once.

This occurs only once but RowInit occurs once for every row.

Description: This event is only associated with components. No Errors or warnings

Example: Component Rowset &Ab_Hist_Rs;

&Ab_Hist_Rs=CreateRowset(Record.ABSENCE_HIST); &Ab_Hist_Rs.Fill(“Where Emplid=:1 “,PERSONAL_DATA.EMPLID);

Activate Event: Firing: Activate PeopleCode event fires every time the page is activated.

Purpose: To change the Properties of the page and page fields. Description: This is only the one event for writing PeopleCode for Page object.

to segregate PeopleCode that is related to a specific page No Errors or warnings Fires when the page is activated

• Every page has its own Activate Event. Not supported for sub-pages. Aids in Page Display and Page Processing. (visible/enable).

• Used for enabling a field, hiding a scroll (for security validation)and also for initializing grid labels.

• Also for security validation Initialization of grid labels placed only on a page.

FieldEdit Event Firing:

Used to validate the contents of a field,and is performed after a field value has changed and the new value satisfies standard system edits. Can keep Warning & error message During error the field background show RED

Placed in Record Field or Component Record Field . It is fired only on the specific field. Without rectification, the transaction cannot be saved. Assignment statements should not be placed in this event.

Purpose: In addition to the system edits we can put some more edits to validate the field values.

For example: Suppose that salary is one field and is a numeric value, so we can’t enter character values to this field, this will take care by the system nothing but system edit. Now we want add some more edits in addition to the system edit like the salary should be >50,000, for this we have to write this code in the Field Edit event.

Salary (Field) Field Edit <<< If Salary<50000 then Error (“Minimum salary should be > 50000”); End-if;>>>>

/* once we get the error message, unless we rectify the error the cursor won’t move to the next. */

Fires after the field has changed and new value satisfies the systemedits, Validate the content of the field

FieldChange Event Fired only when the data value in the field changes. used to perform additional processing on the changed field

Firing: This event occurs after System edits and FieldEdit have been completed successfully.

Purpose: To assign the calculated values to the field by programmatically. To change the properties of the other field based on this field.

After successful passing the page get refreshed..---Used to recalculate page field values, change the appearance of page controls

Used when other fields need to be recalculated or display characteristics may need to change.

Description: Unless the Field edit executes the Field change will not execute. It is not used for validation.

Example: Maritial Status (Field) Field Change (Event) << If Maritial Staus=’N’ then Hide (Wife Name); Hide (Dependent Name); End-if;>

No Errors and Warning message Placed in Record Field or Component Record Field

RowInsert Event Firing Row Insert event fires when a new row is inserted into Scroll bar.

Purpose: To restrict some users to insert new rows into scrolls.

For example: suppose that 15 users are having the permission to access the component. But I don’t want to allow all the users to insert the rows. I want to allow some users whose permission list is All Panels; it is possible by writing code under Row insert event.

Description: It is valid only if the component is having scrolls (means child records). The program never executes unless there is scrolls, not valid for 0 level records.

Example : IF RecordNew(EMPLID) Then IF None(BUSINESS_UNIT) Then

&Bus_Unit=Fetchvalue(OPR_DEF_HR_TBL.BUSINESS_UNIT,1); BUSINESS_UNIT=&Bus_Unit; End-IF; END-IF;

No Errors and Warning message Fires when a new row added Used to override effective dated processing and auto numbering

No Row Insert (at scroll property level without pcode) Placed in Record Field or Component Record

RowInsert Event (Do not use a Warning or Error in RowInsert)

Triggered when the user adds a new row of data. within a SA. --Don't write peoplecode in RowInsert that already exists in RowInit.

• Do not use a Warning or Error in RowInsert.

• Used to override effective-dated processing or to auto number the new rows of data.

• Nothing should be in RowInsert if the code is already in RowInit as it would be performed twice

RowDelete Event: Firing Row Delete event fires when a user deletes a row of data.

Purpose: To restrict some users to delete rows from the scrolls. To recalculating the field values.

Description: It is valid only if the component is having scrolls (means child records). The program never executes unless there is scrolls, not valid for 0 level records. Used to calculate running total or to prevent a row from delete

After row delete component processor fires FieldDefault and FieldFormula

No Row Delete (at scroll property level without pcode) Placed in Record Field or Component Record

• Triggered when the user attempts to delete a row of data from a page’s scroll area.

• This event can be used to recalculate certain values or to prevent a row from being deleted

Used to delete the rows based on condition or prevent a row from being deleted

SaveEdit Event: Firing: When we click the save button the Save Edit Event will fire.

Purpose: It works same as Field Edit. Useful to validate the data which is available in the Component fields.

Description: Use the Field Edit event when we want to validate one field. But use the Save Edit event when we want to validate more than one filed. So that we can increase the performance of the program.

Example: If Not ALL(EMPLID,NAME) Then Error “Emplid & Name should not be left blank”; End-IF;

Fires when the user save Used for validation of data before it is updated to database

Assignment statement to component fields should not be performed. Placed in Record Field or Cmpt Record---Not field specific.

Applies for all rows and all pages in a cmpt FieldEdit for all field fires again During error the field background DOESN’T show RED

So provide a valid error message and use SETCURSORPOS command to focus on the error field

Warning message OK will continue with save and CANCEL will stop saving.

SavePrechange Event Firing: After SaveEdit completes successfully, the application processor applies the SavePrechange event.

Purpose: It provides one last chance to manipulate the data before the database is updated.

Description: Based on the validated values entered into the component, we may need to calculate the fields or change the display characteristics. Example: If PER_STATUS=“E” Then Assign_Employee_ID(EMPLID); End-If;

No Errors and Warning message After saveEdit successful this event get fired

Used for additional calculation and display characteristics changes

Placed in Record Field or Component Record and component

• Workflow Event is generated, and the component processor issues appropriate Insert, Update or Delete SQL statements.

• Initiated after SaveEdit completes without errors. Not Field Specific. Error / Warning message should not be issued from this event

• Provides one last chance to manipulate data before updating to database.( based on validated values entered into the component fields may need to be recalculated or display characteristics may need to be changed).

• Workflow Event is generated, and the component processor issues appropriate Insert, Update or Delete SQL statements.

SavePostChange Event: Firing: This is the last event before the database is committed.

Purpose: To update the data not in the component buffer , we use this event.SQLEXEC function is used to update the database fields.

Description: The system issues an SQL COMMIT when the SavePostChange event completes successfully. If errors or warnings are received before SavePostChange completes, all updates made to the database are rolled back.

Example:

If &Count=0 Then SQLEXEC(“Update PS_PAYROLL_DATA set company=:1 where EMPLID=:2”,COMPANY,EMPLID); End-If;

No Errors and Warning message Fires after SavePreChange successful Placed in Record Field or Component Record and component.

· Triggered after the component processor updates the database. Avoid Errors and Warnings( causes runtime errors).

· Used to update tables( not in the same component) using the SQLExec built-in Funtions.

· Never issue a SQL commit or rollback statement manually from within a SQL function, as the system itself issues a SQL commit statement.

· The SQLExec function is used primarily in SavePostChange event to update (the database, data in other tables)

Workflow Event: Firing:

Workflow PeopleCode fires immediately after SavePreChange and before the database update that precedes SavePostChange.

Purpose: To write the PeopleCode for workflow purpose (Office automation purpose).

Description: Under this event we can write the PeopleCode that is only related to workflow.

Workflow PeopleCode is not field-specific; it is an application specific means it executes for all fields and on all rows of data available in the component buffer. Example: We are going to use the below function in the Workflow event : TriggerBusinessUnit()

No Errors and Warning message People Code related workflow will be keep in this event

TriggerBusinessEvent()

• Used when E-mail/Event notification need to be sent to different parties for the transaction being saved.

• Immediately after SavePreChange event and before the database updates.

• Used when E-mail/Event notification need to be sent to different parties for the transaction being saved.

• Used to segregate PC related to workflow from the rest of the application’s PC. all workflow Events should trigger atleast one of the built-in functions like TriggerBusinessEvent() or Virtual_Router()

• This event segregates all other WorkFlow PeopleCode from all other PeopleCode

Is there any function in peoplecode which stops the processing of whole component?

Think-time functions suspend processing either until the user has taken some action (such as clicking a button in a message box), or until an external process has run to completion.

Think-time functions..Following are Think time functions:

- DoCancel, DoModal, DoModalComponent, Exec (only when Synchronous)

- File attach functions, Prompt, RemoteCall, RevalidatePassword

- WinExec(only when Synchronous), WinMessage ,WinMessageBox.

Difference Between DoSave and DoSaveNow
DoSave can be used only in FieldEdit, FieldChange, or MenuItemSelected PeopleCode.
DoSave defers processing to the end of the current PeopleCode program event, as distinct from DoSaveNow, which causes save processing (including SaveEdit, SavePreChange, SavePostChange, and Workflow PeopleCode) to be executed immediately.

What is the purpose of CreatObject function in peoplecode?

CreateObject return an instance of an OLE Automation object as a variable of type object.

CreateObject (str_class_name) Where str_class_name identifies a class of OLE Automation object, in the form: App_name.object_name Ex., local object &WORKSHEET &WORKSHEET = Create Object (“Excel. Sheet");

Use the CreateRowset function to create a standalone rowset. The parameters for this function determine if the structure of the rowset we're creating is based on an already instantiated rowset, on a record definition, or both.
For example, to create a rowset based on an existing rowset, pass in the name of the existing rowset.
&Level0 = GetLevel0(); &MyRowset = CreateRowset(&Level0);

When any page in a component is opened, the system retrieves all of the data records for the entire component and stores them in one set of record buffers called the component bufferwhich is organized by scroll level and then by page level. Standalone Rowsets? It’s a rowset that is outside of the component buffer and not related to the component presently being processed. Since it lies outside the data buffer, we will have to writePeopleCode to perform data manipulations like insert / update / delete.

Creating Standalone Rowset
We use the below PeopleCode to create a standalone rowset. With this step, the rowset is just created with similar structure to that of the record SAMPLE_RECORD. However, the rowset would be empty at this point..
Local Rowset &rsSAlone = CreateRowset(Record.SAMPLE_RECORD);

Populating a Standalone Rowset: we need to populate it with data that we need to work on.

We can use the below methods to populate data into a standalone rowset.

Fill Method: Populate the standalone rowset with all rows from the SAMPLE_RECORD where the TRAINING_ID = ’12345′.
&TRG_ID = '12345'; &rsSAlone.Fill("where TRAINING_ID = :1", &TRG_ID);

FillAppend Method: Use to populate a standalone rowset.

This method appends the database records to the rowset starting from position of (last row + 1). Ie;

it keeps the existing row intact and do not flush the rowset first like the Fill method.

&TRG_ID = '12345'; &rsSAlone.FillAppend("where TRAINING_ID = :1", &TRG_ID);

You can also use an SQL object instead of the where clause in the above statement.

CopyTo Method:

This method copies like-named fields from a source rowset to a destination rowset.

To perform the copy, it uses like-named records for matching, unless specified.

The below code copies the content of the above rowset &rsSAlone into &rsSAlone2.

Local Rowset &rsSAlone2; &rsSAlone2 = CreateRowset(Record.SAMPLE_RECORD);
&rsSAlone.CopyTo(&rsSAlone2);

In case if we had NOT created both the above rowsets from the same record, we would have mentioned the complete record names in the fill method as shown below. The below code would copy the contents of similar fields in &rsSAlone into those in &rsSAlone2.

&rsSAlone.CopyTo(rsSAlone2, RECORD.SAMPLE_RECORD, RECORD.SAMPLE_RECORD_2);

Child Rowsets
We have seen a standalone rowset being created usign a single record. However, we can also create one using another rowset. This would be handy to setup parent-child relations. This is how this can be achieved.
Local Rowset &Lvl1, &Lvl2, &Lvl3;
&Lvl3 = CreateRowset(Record.SAMPLE_LVL3_REC);
&Lvl2 = CreateRowset(Record.SAMPLE_LVL2_REC, &Lvl3);
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC, &Lvl2);

The above can also be written as shown below.

Local Rowset &Lvl1; &Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC,
CreateRowset(Record.SAMPLE_LVL2_REC, CreateRowset(Record.SAMPLE_LVL3_REC)));

How can we retrive a particular data from dropdown field values by peoplecode?do we have any functions like for it like we have AddDropDownItem ?

Just use Record.Field.Value; it will give we value, if it’s in scroll/grid then, get it by using the scroll path.

PS gives us option to validate all business rules before saving data into the database which can be written in SavePreChange.

Now, since all our data’s are saved WorkFlow event is triggered to inform about the activity in the current transaction.

There is no way we can rollback it. We have 3 options, 1) validate every scenario in SavePreChange, …………………………………….2) Don’t use Workflow instead can use AAF (Active Analytic Framework)

3) Use by CI trigger the workflow event in SavePostChange once all our data is saved.

Stop or Prevent Inserting Grid Rows Via PeopleCode

The PeoleCode below will prevent users from inserting more than 2 rows on a grid. The "+" button will disappear the moment a 2nd row is added by a user. I have place this code on a record field under rowinit event.
If GetRowset().ActiveRowCount = 1 Then
GetRowset().InsertEnabled = True; Else GetRowset().InsertEnabled = False; End-If;

GetLevel0 creates a rowset object that corresponds to level 0 of the component buffer. If used from PeopleCode that isn’t associated with a page, it returns the base rowset from the current context.

Getrowset –is used to get rowset for a record in the component buffer.
Createrowset—is used to create rowset for a record which in database, and is also called a Standalone rowset.

GetRecord creates a reference to a record object for the current context, that is, from the row containing the currently executing program. GetRecord returns a record object.

The following code: &REC = Get Record ();

Is equivalent to: &REC = GetRow().GetRecord(Record.recname); Or &REC = GetRow().recname;

CreateRecord creates a freestanding record definition and its component set of field objects. The specified record must have been defined previously, that is, it must have a record definition. However, if you are calling this function from PeopleCode associated with a page, the record does not have to be included on the current page. This function returns a record object that references a new record buffer and set of fields. EX: Local Record &REC2; &REC2 = CreateRecord(RECORD.OPC_METH);

What is CreateSQL and GetSQL and how they differ with SQL EXEC?

The GetSQL function instantiates a SQL object and associates it with the SQL definition specified by sqlname. The SQL definition must already exist, either created using PS-AD or the StoreSQL function.

Processing of the SQL definition is the same as for a SQL statement created by the CreateSQL function.

The CreateSQL function instantiates a SQL object from the SQL class and opens it on the given sqlstring and input values. sqlstring is a PeopleCode string value giving the SQL statement.

Any errors in the SQL processing cause the PeopleCode program to be terminated with an error message.

use CreateSQL with no parameters to create an empty SQL object that can be used to assign properties b4 being populated and executed.

How you can retrieve data from different ScrollAreas, if I want to retrieve level2 Scroll data which commands you have used?

Scrollselect, fetchvalue, rowselect

What is scroll select, etc? or How do you retrieve a value from scroll.. Scroll select?

ScrollSelect function selects records from a table and loads them into the scroll buffer area of a page. In parent/Child relationships, scrollSelect chooses all corresponding child rows and inserts them under the next higher level row. The function requires the specification of the target scroll area, a source record from which to select rows and an optional SQL string. The parameters passed to ScrollSelect vary based on the scroll level at which the function is targeted.

Level 1 ScrollSelect (1, RECORD.target_recname, RECORD.sel_recname);

Level 2 ScrollSelect (2, RECORD.level1_recname, RECORD.target_recname, RECORD.sel_recname);

L 3 ScrollSelect (3, RECORD.level1_recname, RECORD.level2_recname, RECORD.target_recname, RECORD.sel_recname);

What is Getlevel0()? What is the use of %subrec and %selectall functions?

Getlevel0()---used the get the rowset of the level0.

%subrec--is used only in Dynamic View SQL where it expands to the columns of a subrecord:

%selectall--%SelectAll is shorthand for selecting all fields in the specified record, wrapping date/time fields with %DateOut, %TimeOut.

Prebuild and Postbuild..? Prebuild can be used to validate your search data, discarding rows.

Postbuild can be used to play with the pages (hide, unhide), filling up scrolls.

PreBuild event is the foremost event which triggers at Component Level. There will not be any data in the component buffer structure other than the search record - it's executing prior to the component build process.

This event is often used to hide or unhide pages. It is also used to set component variables.

PostBuild is triggers after Prebuild and Search of the component. Use if we want to capture values rather than prebuild.

During the PostBuild event we will have access to the data read from the database into the component buffer structure.
Although the PostBuild event is commonly used to hide pages, we can also do things like custom security checks.

The PostBuild event is initiated after all the other component build events have been initiated.

SavePreChanges and SavePostChan... saveedit validates data and gives messages or warnings. When no error/warnings exist then saveprechange fires. it helps by doing one last check before the save process.

save post change updates/inserts data into the records that have not been brought into to component buffer. it is recomended not to give error or warning messages in prechange and postchange events.

SaveEdit Event -Triggered when the user attempts to save the component. --Used to validate the consistency of the data.

• Use SaveEdit when a validation involves more than one component field. -Not field specific.

While pressing Save Button how many times Save Pre Change and Save Post Change triggers and when does the commit occur?

It depends on which level (record level record field level or component level) you are placing you peoplecode. For example: A page has a SA or a grid which contains some fields inside. Now if you have people code in your save prechange event of the record level or record field level then it will get executed for all the active rows of your SA. Similarly save post change. The commit will happen after each save prechange. If you have got 4 active rows in your scroll and you have written same prechange peoplecode in record level it will be executed 4 times…..To avoid this you have to write this in a component level. Then it will get executed only once.

REFER A LEVEL1 RECORD:

1 LOCAL ROWSET &RS1 = GETLEVEL0().GETROW(1).GETROWSER(RECORD.EMPL_TBL);

&RS1.HIDEALLROWS(); HIDE ALL THE EMPL_TBL DETAILS: L1

2. LOCAL ROWSET &RS2; L2

&RS2 = GETLEVEL0().GETROW(!).GETROWSET(RECORD.EMPL_TBL).GETROW(3).GETROWSET(RECORD.DEPEN_TBL);

&RS2.HIDE ALLROWS();-- HIDING THE ROWS OF DEPENDENT DETAILS OF 103 EMPLOYEE.

3. LOCAL ROWSET &RS3; TO HIDE THE ROWS SSC AND DEGREE L3

&RS3 = GETLEVEL0().GETROW(1).GETROWSET(RECORD.EMPL_TBL).GETROW(1).

GETROWSET(RECORD.DEPEN_TBL).GETROW(1).GETROWSET(RECORD.EDU_TBL); &RS3.HIDE ALLROWS();

REFERING A ROW:

LOCAL ROW &RW1 = GETLEVEL0().GETROW(1).GETROWSET (RECORD.EMPL_TBL).GETROW(2);

&RW1.INVISIBLE = TRUE; HIDE 102 EMPL ROW…

1. LOCAL ROW & RW2;

&RW2 = GETLEVEL0().GETROW(1).GETROWSET(RECORD.EMPL_TBL).GETROW(1).GETROWSET(RECORD.DEPEN_TBL).GETROW(1).

&RW2.INVISIBLE = TRUE; HIDES 1001 ROW

1. LOCAL ROW &RW3;

&RW3 = GETLEVEL0().GETROW(1).GETROWSET(RECORD.EMPL_TBL).GETROW(1).GETROWSET(RECORD.DEPEN_TBL).GETROW(1). GETROWSET(RECORD.EDU_TBL).GETROW(2); &RW3.INVISIBLE = TRUE; HIDES DEGREE ROW

OR &RW3 = GETLEVEL0()(1).GETROWSET(RECORD.EMPL_TBL)(1).

GETROWSET(RECORD.DEPEN_TBL)(1). GETROWSET(RECORD.EDU_TBL)(2) REFERING A FIELD:

1. LOCAL FIELD &FLD1; HIDE 102 …… SALARY DETAILS

&FLD1= GETLEVEL0().GETROW(1).

GETROWSET(RECORD.EMPL_TBL).GETROW(2).GETRECORD (RECORD.EMPL_TBL).

GETFIELD (EMPL_TBL.SALARY); &FLD1.VISIBLE = FALSE;

1. LOCAL FIELD &FLD2; HIDES 1002……AGE DEATAILS

&FLD2 = GETLEVEL0().GETROW(1). GETROWSET (RECORD.EMPL_TBL).GETROW(1). GETROWSET(RECORD.DEPEN_TBL).GETROW(2).

GETRECORD(RECORD.DEPEN_TBL) . GETFIELD(DEPEN_TBL.AGE); &FLD2.VISIBLE = FALSE;

1. LOCAL FIELD &FLD3; ……….. HIDE SSC ROW GRADE

&FLD3 = GETLEVEL0().GETROW(1). GETROWSET(RECORD.EMPL_TBL_GETROW(1).

GETROWSET(RECORD.DEPEN_TBL).GETROW(1). GETROWSET(RECORD.EDU_TBL).GETROW(1).

GETRECORD(RECORD.EDU_TBL). GETFIELD(EDU_TBL.GRADE); &FLD3.VISIBLE = FALSE;

This is an example of how to grab the most recent/correct row from an effective dated/effective sequenced table such as PS_JOB

SELECT * (A.EMPLID) FROM PS_EMPL_TBL A

WHERE A.EFFDT = (SELECT MAX (B.EFFDT) FROM PS_EMPL_TBL B

WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT <= SYSTEMDATE/ GETDATE()));

AND A.EFFSEQ = (SELECT MAX (C.EFFSEQ) FROM PS_JOB C

WHERE C.EMPLID = A.EMPLID AND C.EMP_RCD = A.EMP_RCD AND C.EFFDT = A. EFFDT AND A.EMPLID = '12345');

Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.

SELECT A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT FROM PS_JOB A, PS_JOB B

WHERE B.EFFDT = (SELECT MAX (B.EFFDT) FROM PS_JOB B

WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT < A.EFFDT)

AND B.EFFSEQ = (SELECT MAX(C.EFFSEQ) FROM PS_JOB C

WHERE B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.EFFDT = C.EFFDT)

AND A.EMPLID = B.EMPLID AND A.EMPLID=’KA0002′

Log file in Application Engine

A typical example of Log file creation in application engine using peoplecode is given below.

local File &Log_file; &Log_file = GetFile(“c:\temp\logfilename.txt”, “W”, %FilePath_Absolute);

&Log_file.WriteLine(“Begin Process : ” | %Datetime); &Log_file.close()

But if you have to use the same log file across the sections within an application engine, then
Declare the file as global in one of the initial step as given below.

Global File &Log_file; &Log_file = GetFile(“c:\temp\logfilename.txt”, “W”, %FilePath_Absolute);
&Log_file.WriteLine(“Begin Process : ” | %Datetime);

Then in any following section’s peoplecode step, declare the global variable and start writing to the file.

Global File &Log_file; &Log_file.WriteLine(“Write anything here”); Don’t forget to close the log file at the end of AE.

Global File &Log_file; &Log_file.close();

Output Commands:

DISPLAY command - Displays the specified column, variable, or literal. Eg:- Display '123456789' xxx-xx-xxxx

SHOW command - Displays one or more variables or literals on the screen

Eg:- Show $fname ‘A***--***Z’ $mname $lname

SQC Files :

§ SQC is a library of frequently used procedures for standard report heading, report setup, and database calls.

§ The SQC files are included in SQR using #INCLUDE option. #include ‘SetEnv.sqc’

§ Frequently used SQC files

SetEnv.sqc – Environment specific to each DB

CurDtTim.sqc – Get current date & time from system Number.sqc – Various Number conversion routines

SetUp31.sqc – Portrait printer setup SetUp32.sqc – Landscape printer setup

STDHDGTR.sqc – Heading setup STDAPI.sqc – To make SQR API aware

ON-BREAK

What is On-Break and what is its purpose?

ON-BREAK causes the specified action to perform in a tabular report when the value of a field changes . The default action prints the field only when its value changes (PRINT=CHANGE).

Mainly used for Redundancy of Printing on a page. On-Break is used for removing redundancy from the reports,

sometimes we may need to skip lines when the value of the column is changed, print a value only if changed, Add white space to your reports, Perform conditional processing on variables that change, execute a special procedure before or after the break, Print subtotals, etc. We can do all these by using the SQR break handling commands.

What is the purpose of the On-break Qualifiers? What is the default qualifier? Qualifiers define specific actions to be taken when the break occurs.

Print is the default qualifier for the On-break. It can be used for both explicit and implicit printing.

Always: The break field will be printed for each detail group. Never: The break field will not be printed.

Change: The break field will be printed only when its value is changed. This is the default option.

Change/Top-Page: The break field will be printed when its value is changed plus at the top of each new page.

SAVE qualifier? It use to save the previous break value in string variable and to print the old values.

SKIP LINE command?It is used with On-Break command to denote how many lines the printer has to skip before printing the next line.

How Before And After procedures are executed?

The before qualifier will automatically calls specified procedure before the column values are changed. including the process of the 1^st row selected(before qualifiers are invoked in ascending level order)

after qualifier will automatically call the specified procedure after the column is changed. Including the last change i.e. when select is completed (after qualifiers are invoked in descending order)

On-break more than one field? use the level qualifier. When we have multiple breaks, we must arrange them in a hierarchy.

This sort of arrangement is called nesting, and the breaks can be considered nested.

Can we Use On-Break in a Process? No, We cannot use On-Break in Process. It is only used for reports.

What is the difference between Break and On-Break? Break is used for exiting from a loop

Ex: Evaluate. On-break is the special processing performed when the value of a column is changed.

ON-BREAK [PRINT= {ALWAYS|CHANGE|CHANGE/TOP-PAGE|NEVER}] [SKIPLINES= {num_lit|_var|_col}]

[PROCEDURE=procedure_name[(arg1[ ,argi]...)]] [AFTER=procedure_name[(arg1[ ,argi]...)]] [BEFORE=procedure_name[(arg1[ ,argi]...)]]

[SAVE=txt_var] [LEVEL=nn] [SET=nn] The commands available at the on-break command

Print=change/top-page: Prints the on-break field data on every page top if the entire page is having the same on-break field. This is useful to identify the records are for which group field. Print=always: This command will always the on-break field data for each record

Print=never: This command will not print the on-break field data.

Skiplines=number: This command is useful for skipping the lines between the two rows.

Before=procedure name: This command is useful in displaying the headings for the columns. Works as Report Header

After=procedure name: This command is useful in displaying the totals of the records at the report footer. Works as report footer.

Save: This command is useful to save the current on-break field data to a variable, which can be used at the group footer.

Level: The level command is useful with the on-break command to set the hierarchy of the fields of table if multiple on-breaks are used.

What is the usage of Save Parameter in on-break?

The Save parameter can be used only with the on-break command. The save parameter saves the current fields data on which the on-break command is used. Whenever the change in on-break field will happen we can use the variable to display the value at the group footer.

The syntax of Save Parameter with the on-break command is as follows: Syntax: Emplid on-break save=$emplid

How to run the program I Debug Mode?

In the Processes navigation (‘Home > PeopleTools > Process Scheduler > Processes’) for the specified process, under ‘Override Options’ tab set the Parameter list value as ‘-DEBUG[x]’ with ‘Append’ mode.

What is the difference between load- lookup and array?

Load-lookup Creates internal memory array retrieving information from multiple tables.

An Array is a collection of similar groups of fields in the program memory.

Arithmetic operations can be performed by using Arrays, which is not possible using load lookup.

An Array is a collection of similar group of fields. It is a unit of storage that consists of rows and columns and exists in memory.

An SQR array is a buffer that is useful to store the data temporarily. An array is similar to a database table, but it exists only in memory.

LET, GET and PUT command in SQR?

LET Command: Useful for assigning any value to particular variables . Useful for doing the calculation with the value of variables. syntax: LET #Basic = #basic + 1000 or LET #Basic = 1000

GET Command: while retrieving the values of array elements one by one row.

syntax: Get $name $startdate #salary from emps (#i)

PUT Command: useful for inserting the data into array. syntax: PUT $name #count $date1 into names (#j)

Placing the data into arrays using the LET command: Let Emp_Details.emp_id(10) = ‘100’

The string literal 100 is moved into field emp_id of the 11^th row of the Emp_Details array.

Let Emp_Details.emp_id(#Row_Num) = 100 (#Row_Num) stores the row number that can be changed by the program. The string literal 100 is moved into field emp_id of the (#Row_Num) row of the Emp_Details array.

Placing the data into arrays using the PUT command: Put ‘100’ into Emp_Details(10)

The string literal 100 is moved into the first field of the 11^th row of the Emp_Details array. If no field

What are the differences between Load lookup and Array

1. Arrays can be declared in any section. 1. Load-Lookup is declared in only in SETUP section only.

2. Number of rows are not automatically 2. Numbers of rows are automatically added.

gives a error if the number of added. Exceeds the specified number.

3. We should insert data in to Array by programming. 3. Rows are automatically inserted in to Load-Lookup.

4.We can retrive any number of fields from a table. 4.We can retrive only Two fields from a table.

5.We have Length & Data type. 5.We don’t have Length & Data type.

6.We cannot directly print from Array. 6.We can directly print from Load-Lookup.

Array supports all data types. It is only for text data type

Array gets populated at Execution time. We can't modify size of the Array. ARRAYS are used to store and retrieve data using the get and put commands Load-lookup will be populated at compilation time. We can adjust the size of load-lookup. Used to reduce the complexity of joins - it populates the values of a certain field depending on the key field specified from a certain table. Then the users can query

Load-lookup creates an internal memory array and populates this array with keys and column values in specified table. Allows for quick search using the LOOKUP command. It can be used in either SETUP section or the body of the report.

It will retrieve two fields key field and return value field. key field can be string or numeric but it must refer to a unique column value. Return value field refers to just on table column or a combination of several table columns.

Lookup: Used to search through the internal memory arrays created and populated by load-lookup

The LOAD-LOOKUP command defines an array containing a set of keys and values and loads it into memory. With LOAD-LOOKUP, you can reduce the number of tables that are joined in one SELECT. Use this command in conjunction with one or more LOOKUP cmnds. begin-setup load-lookup name=prods table=products key=product_code returnvalue=description end-setup

LOAD LOOKUP: Loads an internal table with columns from the database.

LOAD-LOOKUP NAME = lookup-table TABLE =database table

KEY = Key-column RETURN-VALUE = ret_column

ROWS = max-rows WHERE = where clause

SORT =sorting method used QUIET = suppresses message

Eg : LOAD-LOOKUP NAME = emplkp TABLE = JOB KEY = EMPLID

RETURN-VALUE = EMPNAME WHERE = SALARY > 30000

This searches a lookup table to find a RETURN-VALUE matching the KEY.

LOOKUP lookup_table {lookup_var}{$returned_text_str} - lookup_var -> KEY used in the lookup

- $returned_text_str -> return–value will be placed. If no match found NULL will be placed.

ARRAYS: Defined to store any number of rows with virtual memory limits. A maximum of 128 arrays with 200 fields per array are allowed in a SQR program.

CREATE-ARRAY NAME = array_name SIZE = no. of rows (starting at 0) FIELD = fieldname : type : occurs = default value

To Store values in Array PUT value1 value2………..valueN INTO array_name(row) field1 field2(0) field2(1)……….fieldN()

To Retrieve values from Array GET {any SQR variable} FROM array_name(row) field (occurs)

How do you write sub queries in SQR? The select statement can be nested with in one query.

What are the difference between AE and SQR?

AE are maintained with in PS, with in Application Designer SQRs are a 3rd party software and not maintained with in PS, therefore need a separate upgrade process.

3. AE is easy to debug as you can do line by line debugging. With SQR, it’s not easily possible.

4. AE has a restart ability, which is SQR does not have restart ability

5. By using SQR, you can create and maintain different reports, which are not possible with AE's If we use AE, below 1. AE is very systematic as it is structured as Section, step and Action. While SQR is not systematic.

2. AE flow is easy to read and understand, while SQR is not that easy.

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT B.ERNCD, B.EFFECT_ON_FLSA, B.ADD_GROSS FROM PS_EARNINGS_TBL B
WHERE B.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL WHERE B.ERNCD = ERNCD AND EFFDT <=(SYSDATE)

AND B.EFF_STATUS =”A” AND B.ERNCD = $Erncd
In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT <=SYSDATE) ‘||

‘AND EFF_STATUS =”A”’

LOAD-LOOKUP

NAME = EARN

TABLE = ‘PS_EARNINGS_TBL B’

KEY = ERNCD

RETURN_VALUE = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

WHERE = $where

QUIET

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Depending on the results of the above SQL you can trigger an email notification or any other form of action.

from the preloaded lookup table instead of joining tables.

Trace of SQL statements which runs in SQR program

If you find very hard to debug the SQR program, the order in which the SQL runs (from multiple SQC’s), the number of rows processed…In such case, PS provides a run time variable to be substituted in the command line argument of the SQR program to display the order in which the SQL statement executed in SQR program, Exact text of SQL statement with values substituted in variables, the number of rows returned… The following steps needs to be followed to attain this:

· In the Processes navigation (‘Home > PeopleTools > Process Scheduler > Processes’) for the specified process, under ‘Override Options’ tab set the Parameter list value as ‘-S’ with ‘Append’ mode.

· Run the SQR process and view the output in log file.

Note

· The log file of the process occupies more space due to the information in it. Hence, use the parameter only for testing purpose and remove it before migrating to testing or production environment

DEBUG statements in SQR

Consider a scenario where you write a code which needs to be executed only when you are trying to debug the program. For e.g. you may want to create a log file in SQR about the rows processed to check the validity of the Program. U may not want this log file to be generated in normal processing. When you want to debug the data you need to be executed.
In such cases SQR provides‘#DEBUG’ commands to perform the action.#DEBUG[x...] SQR_Command Where ‘x’ represents any letter or digit.

U can write different debug sets in a single SQR program. For E.g. you want only certain part of code to be executed in certain situation. You want some other set of codes need to be executed in other conditions.

In such case group the related set of SQR commands with the debug command

Example: In a SQR, Consider the following set of commands. If my SQR errors out and

I want to generate the list of Employee Id, Employee Records and Effective Date in log file, I will run the program again with the debug mode of ‘A’. In this case the SQR commands starting with ‘DEBUGA’ will get executed.

If I want to trap all the Employee Id, Employee Records and Effective Date in Temp Log table, I will run the program in debug mode of ‘B’. In this case all the errors will be loaded in the temp file and log file will not be generated.

If I want to run the SQR program to generate log file and load into Temp table, I can run the program in both mode at same time.

#DEBUGA show ‘Emplid :’ $Emplid #DEBUGA show ‘Empl Rcd :’ $Empl_Rcd #DEBUGA show ‘Effdt :’ $Effdt

#DEBUGB Begin-Sql #DEBUGB Insert into PS_Temp_log values (‘Error’,$Emplid,$Empl_Rcd,$Effdt); #DEBUGB End-Sql

Data Types 1. Conventional data types. 2. Object data types.

Conventional Data Types There are many data types available and the most commonly used data types are :

• Any : Undeclared local variables are Any by default.

• Boolean, Date, DateTime, Float, Integer, Number, Object, String, Time.

Object based data types are : Field, Record, Row, Rowset, Grid, Page, Array, File, SQL

PeopleCode Comments:

§ /* ……Statement ….*/

– You can surround comments with /* at the beginning and */ at the end.

Ex: /* ----- Logic for Compensation Change ----- */

/* Recalculate compensation change for next row.

Next row is based on prior value of EFFDT. */

§ Rem or Remark

– You can also use a REM (remark) statement for commenting. Put a semicolon at the end of a REM comment.

– Note: If you do not, everything up to the end of the next statement is treated as part of the comment.

Ex: REM This is an example of commenting PeopleCode;

Operators:

mathematical -> +,-,*,/,**,|

comparison -> =,!=,<>,>=,<=,<,>

logical -> NOT,AND,OR
Note:- PeopleCode applied NOT operator first ,then ANDs ,and the ORs. You can use parenthesis to explicitly define the order of precedence.

Branching Statements:

§ Branching statements control program flow based on evaluation of conditional expressions.

– If, Then, and Else

– Evaluate

– For

§ Repeat, while – are conditional loops.

• if Statement: syntax: if <condition> stmt1; else stmt2; end-if;

• break statement: syntax: break;

• Evaluate Statement

Evaluate <field or variable>

when = <value1> stmt1; break;

when = <value2> stmt2; break;

when-other stmt3;break;

End-Evaluate;

Looping Statements

loop type syntax

• while while <condition> stmt1; stmt2; end-while;

• repeat-until repeat stmt1; stmt2; until <condition>;

• for for <&variable> = <start-value> to <end-value> [Step stepvalue>]

stmt1; stmt2; end-for;

Error Statements Syntax : Error(“<message>”); Warning Statements Syntax: warning(“<message>”);

peoplecode variables: Types NUMBER,STRING,DATE,TIME,DATETIME,ANY

Scope Local Variables Global Variables Component Variables

Other Variables System Variables Derived/Work Fields

Naming Rules must begin with & and max length 18 characters including &

Declarations always on top . Can have only comments above it.

Syntax: <scope> <type> &<variable_name>

Scope of Variables

Local Variables: existence limited to current PC program runtime assignment.

Also called chameleon variables as they can be assigned values of any date type within the program.

Global Variables: exist for the complete session and are used to transfer values between panel groups.

Component Variables: exist for the duration of the current PS component.

Used to transfer values between programs on the same component.

System Variables -internally maintained and can be referred by the peoplecode any time. Ex: %Date,%Time,%Panel,%Menu etc.

Derived/Work Fields

-used when we need to pass values from one program to another without the field values being stored in the database.

-Existence limited to the current component. -They are usually display only fields on a page or have them as invisible fields on the page.

Functions: User defined functions,Built-in Functions

User defined functions

Ø Internal PC: PeopleCode functions contained within the same program where they are used.

Ø External PC: PeopleCode functions contained in a different program from where they are used.

Stored as Function Libraries( derived/work records) named with prefix “FUNCLIB_” convention and defined in the FieldFormula Event.

Ø External-Non-PC: These are routines or functions written in other language like C and loaded from a DLL (Dynamic Link Librabies). Defined by the statement Declare....... Library .

Syntax for defining or writing a function

• Function should be defined in the beginning of the code in FieldFormula event.

• We can have only comments lines preceding the function definition.

Function <function_name> (<param1,<param2>....) stmt1; stmt2; return <expression>; End-Function;

Syntax for Declaring a Function

• This should be the first statement in the event where an external function is used.

Declare Function <function_name> PeopleCode <Record_name.FieldName>.FieldFormula

• Component Buffer Functions:Used to manipulate scroll bar records rather than the entire component’s data.

Example: ScrollSelect(), ActiveRowCount() , TotalRowcount()..etc.,

· Logical Functions:Used to check if values exist for a field.Example: None(), All() , OnlyOne(), AllorNone() , OnlyoneorNone()

• Date & Time Functions:Used to calculate and manipulate dates. AddToDate(date,num_years,num_months,num_days)

· String Functions :Used to manipulate character Strings, Substring(Source str,start_pos,length)

• Math Functions:Used to perform mathematical calculations- Sine(),abs(),Max(),Min()

• Security Functions: Used for security purpose.-- Hide(),Unhide(),Gray(),Ungray()

Message Catalog and Message Display: Error MessageBox MsgGet MsgGetText MsgGetExplainText Warning WinMessage

§ Error Syntax: Error st

– Use the Error function in FieldEdit or SaveEdit PeopleCode to stop processing and display an error message. It is distinct from Warning, which displays a warning message, but does not stop processing. --Error is also used in RowDelete and RowSelect PeopleCode events.

– Errors in Other Events : Do not use the Error function in any of the remaining events, which include:

– FieldDefault - RowInit - FieldChange -Prepopup - RowInsert -SavePreChange - SavePostChange

§ Warning Syntax: Warning str

– The primary use of Warning is in Field Edit and SaveEdit PeopleCode. In FieldEdit, Warning displays a message and highlights the relevant field.

– In SaveEdit, Warning displays a message, but does not highlight any field. You can move the cursor to a specific field using the SetCursorPos function.

– Warnings in Other Events: Do not use the Warning function in any of the remaining events, which include:

– FieldDefault - RowInit -FieldChange - RowInsert --SavePreChange - SavePostChange

Warning

§ Example

If All(RETURN_DT, BEGIN_DT) and 8 * (RETURN_DT - BEGIN_DT) < (DURATION_DAYS * 8 + DURATION_HOURS) then

Warning ( "Duration of absence exceeds standard hours for number of days absent."); End-if;

§ WinMessage Syntax: WinMessage(message [, style] [, title])

– It is supported for compatibility with previous releases of PeopleTools. New applications should use MessageBox instead.

– Use the WinMessage function to display a message in a message box.

– Winmessage cannot be used in the following PeopleCode events: - SavePreChange. RowSelect. - SavePostChange.

Example &RESULT = WinMessage(MsgGetText(30000, 1, "Message not found."), 4, "Test Application");

§ MsgGet Syntax: MsgGet(message_set, message_num, default_msg_txt [, paramlist])

where paramlist is an arbitrary-length list of parameters of undetermined (Any) data type to be substituted in the resulting text string, in the form: param1 [, param2]

– Use the MsgGet function to retrieve a message from the PeopleCode Message Catalog and substitutes in the values of the parameters into the text message.

– Message sets 1 through 19,999 are reserved for use by PS applications. Message sets 20,000 through 32,767 can be used by PS users

Example &MsgText = MsgGet(30000, 2, "Message not found");

Logical All, AllOrNone, None, OnlyOne , OnlyOneOrNone

All : All(fieldlist) Where fieldlist is an arbitrary-length list of field names in the form:

[recordname.]fieldname1 [, [recordname.]fieldname2] ...

Returns: Returns a Boolean value based on the values in fieldlist. The All function returns True if all of the specified fields have a value; it returns False if any one of the fields does not contain a value.

Example: The All function is commonly used in SaveEdit PeopleCode to ensure that a group of related fields are all entered.

example: If All(RETURN_DT, BEGIN_DT) and 8 * (RETURN_DT - BEGIN_DT) (DURATION_DAYS * 8 + DURATION_HOURS) Then

Warning MsgGet(1000, 1, "Duration of absence exceeds standard hours for number of days absent."); End-if;

AllOrNone: AllOrNone(fieldlist) Where fieldlist is an arbitrary-length list of field references in the form:

[recordname.]fieldname1 [, [recordname.]fieldname2] ...

• The AllOrNone function takes a list of fields and returns True if either of these conditions is true:

All of the fields have values OR None of the fields has a value. For example, if field1 = 5, field2 = "Mary", and field3 = null,

AllOrNone returns False.

• Returns a Boolean value: True if all of the fields in fieldlist or none of the fields in fieldlist has a value, False otherwise. Example :You could use AllOrNone as follows:

If Not AllOrNone(STREET1, CITY, STATE) Then

WinMessage("Address should consist of at least Street (Line 1), City, State, and Country."); End-if;

None : None(fieldlist)

Description: None returns True if none of the specified fields contain a value. It returns False if any one of the fields contains a value.

A blank character field, or a zero (0) numeric value in a required numeric field is considered a null value.

Example: The following example uses None to check whether REFERRAL_SOURCE has a value:

If None(REFERRAL_SOURCE) or REFERRAL_SOURCE = "EE" Then Gray(EMP_REFERRAL_ID); End-if;

The following example uses None with a variable:

&ONETIME = FetchValue(POSN_INCUMB_WS.EMPLID, 1); --If None(&ONETIME) Then /* do processing */ End-if;

OnlyOne : OnlyOne(fieldlist)

Description: Use the OnlyOne function to check a list of fields and return True if one and only one of the fields has a value. If all of the fields are empty, or if more than one of the fields has a value, OnlyOne returns False. This function is used to validate that only one of a set of mutually exclusive fields has been given a value.

A blank character field, or a zero numeric value in a required numeric field is considered a Null value.

Example: You typically use OnlyOne as follows: If OnlyOne(param_one, param_two) Then value_a = "y"; End-if;

OnlyOneOrNone Syntax: OnlyOneOrNone (fieldlist)

Where fieldlist is an arbitrary-length list of field names in the form: [recordname.]fieldname1 [, [recordname.]fieldname2] ...

– Use the OnlyOneOrNone function to check a list of fields and return True if either of these conditions is true:

Only one of the fields has a value (OR) None of the fields has a value

Executable Files Functions: Exec, WinExec , Mailing Functions: Sendmail

Executable Files Functions:

Exec Syntax: Exec(command_str [, parameter]) ….. where parameter has one of the following formats:

--Boolean constant --Exec_Constant + Path_Constant

– Exec is a cross-platform function that executes an external program on either UNIX or Windows

– When Exec is used to execute a program synchronously (that is, if its synch_exec parameter is set to True) it behaves as a think-time function, which means that it can’t be used in any of the following PeopleCode events: SavePreChange. SavePostChange. Workflow. RowSelect.

SQL Functions: SQLExec, CreateSQL, GetSQL , DeleteSQL

SQLExec: SQLExec({sqlcmd | SQL.sqlname}, bindexprs, outputvars)

where bindexprs is a list of expressions, one for each :n reference within sqlcmd or the text found in the SQL definition sqlname, in the form: inexpr_1 [, inexpr_2]. . . and outputvars is a list of variables, record fields, or record object references, one for each column selected by the SQL command, in the form: out_1 [, out_2]. . .

• Use the SQLExec function to execute a SQL command from within a PeopleCode program by passing a SQL command string.

• The PS record name specified in the SQL SELECT statement must be in uppercase

• SQLExec commands go directly to the database server, not to the Component Processor

Bind Variables in SQLExec:

– Bind variables are references within the sqlcmd string to record fields listed in bindvars. Within the string, the bind variables are integers preceded by colons like :1, :2,. . . ---The integers need not be in numerical order.

– Bind variables can be used to refer to long character (longchar) fields. Long character fields are represented in PeopleCode as strings

– You should use %TextIn() Meta-SQL to ensure these fields are represented correctly on all database platforms.

– Bind variables can be passed as parameters to meta-SQL functions

Example: SQLExec(". . .%datein(:1). . .", START_DT, &RESULT)

SQLExec("Select sum(posted_total_amt) from PS_LEDGER where deptid between :1 and :2", DEPTID_FROM, DEPTID_TO, &SUM);

Limitations of SQLExec

– SQLExec can only Select a single row of data. If your SQL statement (or your SQL.sqlname statement) retrieves more than one row of data, SQLExec sends only the first row to its output variables.Any subsequent rows are discarded.

– If you need to SELECT multiple rows of data, use the CreateSQL or GetSQL functions and the Fetch SQL class method.

– SQLExec statements that result in a database update (specifically, UPDATE, INSERT, and DELETE) can only be issued in the following events: SavePreChange,WorkFlow ,SavePostChange ,FieldChange

PeopleCode Classes & Objects: Declarations, Scope of the Variable , Built-In functions, Methods , Properties

OO Data Types: There are many Object based data types but the most used are the following:

• Field, Record, Row , RowSet , File, Chart , Object , Grid , GridColumn , SQL

Variable Declaration: Declaration : Syn : Local / Global / Component datatype &Instance

Ex : Local record &rec;

DoModalComponent(MENUNAME.MAINTAIN_ITEMS_FOR_INVENTORY, BARNAME.USE_A, ITEMNAME.ITEM_DEFINITION, COMPONENT.ESTABLISH_AN_ITEM, “C”, &WRK_REC1);

DoModalComponent is used to navigate to a new component modally.

When we navigate to a new component using this method, u cannot change values in the parent component.

If we want to pass data from source component to a target component, we should create a record and put this record on the level zero of both components.

This way the values assigned to the shared record on the source component are available to we on the target component once it is opened.

We need to keep a Hyperlink in the custom page where we are going to call the Do Modal. In the hyperlink field change event write the domodalcomponent with the menu name, component name and the search record object. The search record name of the shared work record (preceded by the reserved word Record). This record must include:

§ Fields that are search keys in the modal component search record; if search key fields are not provided, or if they are invalid, the user accesses the modal component using the search dialog box.

§ Other fields to pass to the modal component. § Fields to get back from the modal component after it has finished processing.

Do Modal

1. But as far as your question goes, the secondary page is part of the main component. So if your secondary page is at Level 1 and there is a Scroll Area/Scroll Bar/Grid in your Secondary page, it would be Level 2. In this case, your secondary page cannot have more than two levels, as it will go against the "3 Levels only" rule of the component buffer.

2. If you see the secondary page's Ok/Cancel (in page properties), then you need not write any code. But you can also disable the Ok/Cancel option in page properties and put your own custom push buttons, say example, "Confirm Save", "Cancel & Return". In these cases, you have to write code to go back.

EndModal() is the function we use. It takes number as the parameter. The control comes back to the statement next to DoModal. So you would do something like this &myReturnFlag = DoModal(&MyPage);

If &myReturnFlag = 0 Then <Processing> Else <Some other processing> End-If

3. There are some other intricacies when it comes to Secondary pages, especially if you would like to access the Component buffer on Secondary Page's Page Activate Event.

For a Secondary page to be called from an PeopleCode Event or Push Button, the secondary page need not be physically available in the component.

But if the secondary page is not available in the component, then you wont be able to access the component buffer.

Classes:Array Class, Field Class, Record Class, Row Class, RowSet Class, Page Class, Grid Class, Chart Class, SQL Class, File Class

Field Class: Declaration : Local field &Myfield; Built-In function : Getfield

Syntax : GetField ([recname.fieldname]) Example: Local Field &Myfield; &Myfield = GetField(dept_tbl.deptid);

Methods

1. GetLongLabel Syntax : GetLongLabel(LabelID) 2. GetRelated Syntax : GetRelated(recname.fieldname)

3. GetShortLabel Syntax : GetShortLabel(LabelID) 4. SetCursorPos Syntax : SetCursorPos(PAGE.pagename | %Page)

5. SetDefault Syntax : SetDefault( )

Properties :

6. DisplayFormat : Ex : If %Component = PAGE.INTERNATIONAL

&CHAR.DisplayFormat = "STANDARD"; Else &CHAR.DisplayFormat = "UNFORMATTED"; End-If;

7. DisplayOnly

8. Enabled : This property is read-write. Example :&CHARACTER.Enabled = True; &CHARACTER.Enabled = False;

9. FieldLength : This property returns the length of the field as a number.

10. IsChanged : This property returns True if the value for the field has been changed. This property is read-only.

Example : If &CHARACTER.IsChanged Then Warning ("The character field has been changed"); End-If;

11. IsInBuf : Ex : For &I = 1 to &REC.FieldCount

&FIELD = &REC.GetField(&I); If &FIELD.IsInBuf Then &VALUE = &FIELD.Value; End-If; End-For;

12. Value : This property contains the current value of the field, converted to an appropriate PeopleCode data type.

Record Class Declaration : Local Record &rec; Built-In Functions: 1. Getrecord ( ) 2. CreateRecord( )

CreateRecord ( ) Syntax : CreateRecord(RECORD.recname)

Creates a free standing record definition and its component set of field objects. The specified record must have been defined previously, that is, it must have a record definition.

However, if you are calling this function from PeopleCode associated with a page, the record does not have to be included on the current page.

GetRecord Syntax : GetRecord([RECORD.recname])

Creates a reference to a record object for the current context, that is, from the row containing the currently executing program. The following code: &REC = GetRecord(); is equivalent to:

&REC = GetRow().GetRecord(Record.recname); or &REC = GetRow().recname;

Ex : &FLAGS = CreateRecord(RECORD.DR_LINE_FLG_SBR); &REC.CopyFieldsTo(&FLAGS); &INFO = GetRecord(@("RECORD." | &NAME));

If All (&INFO) Then &FLAGS.CopyFieldsTo(&INFO); End-If;

Methods :

1. CompareFields Syntax : CompareFields(recordobject) Returns A Boolean value; True if all like-named fields have the same value.

Example: &REC = GetRecord(RECORD.OP_METH_VW); &REC2 = GetRecord(RECORD.OPC_METH);

If &REC2.CompareFields(&REC) Then WinMessage("All liked named fields have the same value"); End-If;

2. Delete( ) : Ex : Local record &REC; &REC = CreateRecord(RECORD.MYRECORD);

&REC.KEYF1 = "A"; &REC.KEYF2 = "X"; &REC.Delete();

3. Update ( ) : Local record &REC; &REC = CreateRecord(RECORD.MYRECORD);

&REC.KEYF1.Value = "A";&REC.KEYF2.Value = "B";&REC.MYRF3.Value = "X";&REC.MYRF4.Value = ”Y";

&REC.Update();

4. Insert( ): Local record &REC; &REC = CreateRecord(RECORD.MYRECORD);

&REC.KEYF1.Value = "A";&REC.KEYF2.Value = "B";&REC.MYRF3.Value = "X";&REC.MYRF4.Value = "Y";

&REC.Insert();

5. SelectByKey( ): Local record &REC; &REC = CreateRecord(RECORD.MYRECORD);

&REC.KEYF1.Value = "A";&REC.KEYF2.Value = "X";&REC.SelectByKey();

6. SetDefault() Description : SetDefault sets the value of every field in the record to a null value, Ex : &Rec.SetDefault();

Record Properties :

1. FieldCount : This property returns the total number of fields contained in the record. This value is a number.

2. IsChanged : This property returns True if any field value on the primary database record of the row has been changed.

Ex : If &REC.IsChanged Then Warning("This Record has been changed"); End-if;

Rowset Class:A rowset object, instantiated from a Rowset class, is a collection of rows associated with buffer data. A component scroll is a rowset. You can also have a level zero rowset. Declaration : Local Rowset &rs;

Built-In Functions : 1. GetRowset ( ) 2. CreateRowset( ) 3. Getlevel0( )

CreateRowset ( ): Syntax : CreateRowset(RECORD.recname) Description : CreateRowset creates a non-populated standalone rowset .

A standalone rowset is a rowset that has the specified structure, but is not tied to any data (that is, to the component buffer or to a message.) In addition, a standalone rowset isn’t tied to the Component Processor.

If a rowset object is instantiated using the CreateRowset function, the rowset object that’s instantiated is a standalone rowset.

Any records and field references created by this function are initialized to null values, that is, they do not contain any data. You can populate this rowset object using the CopyTo, Fill, or FillAppend methods.

GetRowset() Syntax : GetRowset([RECORD.recname])

Description : GetRowset creates a reference to a rowset object for the current context, that is, from the rowset containing the currently executing program. The following code: In the following example, RS1 is a level 1 rowset, and RS2 is a child rowset of RS1.

Local Rowset &RS1, &RS2; &RS1 = GetRowset(); &RS2 = GetRowset(SCROLL.EMPL_CHKLST_ITM);

If a rowset object is instantiated using GetRowset (either the function or one of the methods) the rowset object that is instantiated is populated with data according to the context in which it was instantiated

Row Class Declaration : local Row &Myrow; Built-In Function : GetRow ( )

To obtain a row object for the current context, that is the row containing the currently executing program. GetRow() function is equivalent to: &ROW = GetRowset().GetRow(CurrentRowNumber());

/*Object oriented code */ Local Rowset &l0, &L1, &L2; Local Field &f; Local number &i, &K, &J;

&l0 = GetLevel0(); /* Zero Level */ For &J = 1 To &l0.ActiveRowCount /* For all Zero Level rows */

&L1 = &l0.GetRow(&J).GetRowset(Scroll.K_PROJ_TASK_TBL);

/*create rowset for first level */ /* Indicates first level record field */

&f = &L1.GetRow(&J).GetRecord(Record.K_PROJ_TASK_TBL).GetField (Field.K_H_TASK_STATUS);

For &K = 1 To &L1.ActiveRowCount /* For First Level Rowset Rows */

/* Create the Row set for second level */ &L2 = &L1.GetRow(&K).GetRowset(Scroll.PROJTASK_RESTBL);

If &f.Value = "C" Then &L2.HideAllRows(); /* Hide the second level scroll */

Else &L2.ShowAllRows(); /* Unhide the Second Level Scroll */ End-If; End-For; End-For;

/*Procedure oriented code */ &J = CurrentRowNumber(1); /* Current Row number at level 1 */

If K_PROJ_TASK_TBL.K_H_TASK_STATUS = "C" Then HideScroll(Record.K_PROJ_TASK_TBL, &J, Record.PROJTASK_RESTBL);

/* Hide the Second Level Scroll */

Else UnhideScroll(Scroll.K_PROJ_TASK_TBL, &J, Record.PROJTASK_RESTBL); /* Unhide the Second Level Scroll */ End-If; */

Function DISPLAY_RECONCILE_DATA(&emplid As string, &penddt As date, &bugl As string, &flg As string)

/* Create the Row Set for Level 1 (Grid) */ &rsSummary = GetLevel0()(1).GetRowset(Scroll.K_SUBCN_RECON);

&rsSummary.Flush(); /* Flush The Grid */ /* To select the rows and populates these rows in Grid */

&rows = &rsSummary.Select(Record.K_SUBCN_RECON, "where emplid like :1 and business_unit_gl like :2 and k_recon_status like :3 and period_end_dt = :4", &emplid, &bugl, &flg, &penddt); End-function;

Array Class: This chapter provides an overview of arrays and discusses how to:

Create arrays., Populate an array. Remove items from an array. Create empty arrays.

Create and populate multi-dimensional arrays. Declare array objects. Understand the scope of an array object.

Use array class built-in functions. Use array class methods. Use array class properties

Declaration: Local Array of Number &MYARRAY; Local Array &ARRAYANY;

Two Dimensional Array Local Array of Array of Number &MYARRAY;

Built-In Functions :

1. Createarray( &temp) 2. Createarrayrept(&temp , size) 3. Split(string , seperator) 4. CreateArrayAny ; from 8.4 onwards

Methods :

1. Push ( ) : Use the Push method to add items to the end of the array.

Ex : Local Array of Number &MYARRAY; Local Number &MYNUM; &MYARRAY = CreateArrayRept(&MYNUM,0);

/* this creates an empty array of number */

&MYARRAY.Push(100); &MYARRAY.Push(200); &MYARRAY.Push(300);

2. Unshift ( ) : Use the Unshift method to add items to the beginning of the array:

Local Array of Number &MYARRAY; Local Number &MYNUM; &MYARRAY = CreateArrayRept(&MYNUM, 0);

/* this creates an empty array of number */ &MYARRAY.Unshift(300); &MYARRAY.Unshift(200); MYARRAY.Unshift(100);

3. POP ( ) : Use the POP method to select and remove an element from the end of an array

Local Array of Number &MYARRAY; &MYARRAY = CreateArray(); &MYARRAY[1] = 100; &MYARRAY[2] = 200; &MYARRAY[3] = 300;

&ANSWER = &MYARRAY.Pop(); &ANSWER will equal 300.

4. SHIFT method to select and remove an element from the beginning of an array

Local Array of Number &MYARRAY; &MYARRAY = CreateArray(); &MYARRAY[1] = 100; &MYARRAY[2] = 200;

&MYARRAY[3] = 300; &ANSWER = &MYARRAY.Shift(); &ANSWER equals 100.

File Class File Class : topics:

· File object declaration , Scope of a file object

· File layout , File security considerations

File access interruption recovery,Plain text files,Automatic PeopleCode generation,File layout error processing,

File class built-in functions,File class methods,File class properties,File layout examples,Multiple file layouts

Data Type of a File Object : Local File &MYFILE; Scope :Local File &F1, &F2; &F1 = GetFile("somefile.txt", "R"); &F2 = &F1;

&F2.Close(); /* Now &F1 is also closed. */

Built-in Function : Syntax : GetFile(filename, mode [, charset] [, pathtype]) --

pathtype : %FilePath_Relative (default) , %FilePath_Absoulte ;

If you don't specify pathtype the default is %FilePath_Relative.

PeopleTools supports reading and writing to plain text files, and to files that have a format based on a File Layout that has been created in PS Application Designer.

· If the file is a plain text file, data is read or written using text strings.

· If the file is based on a File Layout, you can use text strings, rowset, or record objects.

This simplifies reading, writing, and manipulating hierarchical transaction data with PeopleCode.

Plain Text Files: To read and write from plain text files involves reading and writing strings of data.

These text strings can be manipulated with built-in string functions, like RTrim, Find, Replace, and so on.

Note. If your data is hierarchical in nature, or based on existing PS records or pages, use a File Layout definition for reading and writing your data, rather than doing it line by line (or field by field.)

The following example creates an array of array of string, then reads in two files, one into each "column" of the array.

The Names file contains names; the Numbers file contains employee numbers.

The ReadLine method reads each successive line in a file, until it reaches the end of the file. Notice that the first file is opened using GetFile. The second file is not opened using GetFile, but rather with Open. After the data is read into the array, you can do processing on the data.

The end of the program writes the changes back to the files, using the WriteLine method, which includes a system end of line character at the end of every line.

Local array of array of string &BOTH; Local File &MYFILE; Local string &HOLDER;

/* Create empty &BOTH array */ &BOTH = CreateArrayRept(CreateArrayRept("", 0), 0);

/* Read first file into first column */ &MYFILE = GetFile("names.txt", "R");

While &MYFILE.ReadLine(&HOLDER); &BOTH.Push(&HOLDER); End-While;

/* read second file into second column */ &MYFILE.Open("numbers.txt", "R"); &LINENO = 1;

While &MYFILE.ReadLine(&HOLDER); If &LINENO > &BOTH.Len Then /* more number lines than names, use a null name */

&BOTH.Push(CreateArray("", &HOLDER)); Else &BOTH[&LINENO].Push(&HOLDER); End-If;

&LINENO = &LINENO + 1; End-While; /* if more names than numbers, add null numbers */

For &LINENO = &LINENO to &BOTH.Len &BOTH[&LINENO].Push(""); End-For; &MYFILE.Close();

/* do processing with array */

/* write data back to files */ &MYFILE1 = GetFile("names.txt", "A"); &MYFILE2 = GetFile("numbers.txt", "A");

/* loop through array and write to files */

For &I = 1 To &BOTH.Len &STRING1 = &BOTH[&I][1]; &MYFILE1.writeline(&STRING1); &STRING2 = &BOTH[&I][2];

&MYFILE2.writeline(&STRING2); End-For; &MYFILE1.Close(); &MYFILE2.Close();

Built-in Functions: FileExists GetFile FindFiles

FileExists Syntax: FileExists(filename [, pathtype])

Ex: The following example opens a file for appending if it exists on the system:

If FileExists("c:\work\item.txt", %FilePath_Absolute) Then &MYFILE = GetFile("c:\work\item.txt", "A");

/* Process the file */ &MYFILE.Close(); End-If;

FindFiles Syntax: FindFiles(filespec_pattern [, pathtype])

Ex : Local array of string &FNAMES; Local file &MYFILE; &FNAMES = FindFiles("\*.txt");

while &FNAMES.Len > 0 &MYFILE = GetFile(&FNAMES.Shift(), "R"); /* Open each file */

/* Process the file contents */ &MYFILE.Close(); end-while;

GetFile: Syntax: GetFile(filename, mode [, charset] [, pathtype])

File Class Methods

1. Close Syntax : Close() Ex :

&MYFILE.Open("somefile.txt", "W", %FilePath_Relative); &MYFILE.WriteLine("Some text."); &MYFILE.Close();

2. Open Syntax Open(filespec, mode [, charset] [, pathtype])

Description : The Open method associates the file object with an external file for input or output.

EX : MYFILE.Open("C:\temp\item*.txt", "N", %FilePath_Absolute);

If &MYFILE.IsOpen Then &MYFILE.WriteLine("Some text."); &MYFILE.Close(); End-If;

3. Readline : Syntax : ReadLine(string)

Description : The ReadLine method reads one line of text from the external file. Example : The following example reads a file called &MYFILE and puts each line as a separate element in an array.

Local File &MYFILE; Local array of string &MYARRAY; Local string &TEXT;

&MYFILE = GetFile("names.txt", "R"); &MYARRAY = CreateArrayRept("", 0); While &MYFILE.ReadLine(&TEXT);

&MYARRAY.Push(&TEXT); End-While; &MYFILE.Close();

4. WriteLine : Syntax : WriteLine(string);Description : The WriteLine method writes one string of text, string, to the output file

Ex : &MYFILE.Open("somefile.txt", "A"); &MYFILE.WriteLine("This is the last line in the file."); &MYFILE.Close();

5. WriteRecord Syntax : WriteRecord(record)

Description: The WriteRecord method is a file layout method. It writes the contents of the record object record, to the output file. Example: The following example appends all the data in a record to an existing file:

Local File &MYFILE; &MYFILE = GetFile("record.txt", "A");

If &MYFILE.IsOpen Then

If &MYFILE.SetFileLayout(FILELAYOUT.VOL_TEST) Then

&LN = CreateRecord(RECORD.VOLNTER_ORG_TBL); &SQL2 = CreateSQL("%Selectall(:1)", &LN);

While &SQL2.Fetch(&LN) &MYFILE.WriteRecord(&LN); End-While;

Else /* do error processing — filelayout not correct */ End-If;

Else /* do error processing — file not open */ End-If; &MYFILE.Close();

7. ReadRecord : Ex :

"8001","VAC","09/12/1981","09/26/1981","14","0","","P","Y",""

"8001","VAC","03/02/1983","03/07/1983","5","0","","P","Y",""

"8001","VAC","08/26/1983","09/10/1983","13","0","","P","Y",""

"8105","CNF","02/02/1995","??/??/","0","0","","U","N",""

To read in the previous CSV file we use the following PeopleCode. It reads the file into a temporary record. First each line of the file is read into a string. The string is split into an array, with the value of each field in the array becoming an element in the array. The value of each field in the record is assigned a value from the array. After additional processing (for example, converting strings into dates or numbers, verifying data, and so on) the record can be inserted into the database. To insert the final data into the database, this code must be associated with a PeopleCode event that allows database updates, that is, SavePreChange, WorkFlow, SavePostChange, and so on. This code could also be used as part of an Application Engine program.

Local File &MYFILE; Local Record &REC; Local array of string &ARRAY;

&MYFILE = GetFile("c:\temp\vendor.txt", "R", %FilePath_Absolute);

&REC = CreateRecord(RECORD.ABS_HIST_TEST); &ARRAY = CreateArrayRept("", 0);

If &MYFILE.IsOpen Then

If &MYFILE.SetFileLayout(FILELAYOUT.ABS_HIST) Then

While &MYFILE.ReadLine(&STRING); &ARRAY = Split(&STRING, ",");

For &I = 1 To &REC.FieldCount &REC.GetField(&I).Value = &ARRAY[&I]; End-For;

/* do additional processing here for converting values */ &REC.Insert(); End-While;

Else /* do error processing — filelayout not correct */ End-If;

Else /* do error processing — file not open */ End-If; &MYFILE.Close();

SQL Class: Declaring a SQL object Scope of a SQL object SQL class built-in functions SQL class methods SQL class properties

· Declaration : SQL objects are declared as type SQL. For example: Local SQL &MYSQL;

· Scope : An SQL object can be instantiated only from PeopleCode.

· SQL Class Built-In Functions : 1. CreateSql 2. DeleteSql 3. GetSql 4. FetchSql 5. StoreSql

1. CreateSql : CreateSQL([sqlstring [, paramlist]]) Where paramlist is an arbitrary-length list of values in the form: inval1 [, inval2] …

It instantiates a SQL object from the SQL class and opens it on the given sqlstring and input values.

sqlstring is a PeopleCode string value giving the SQL statement.

Ex : This SQL object should be used in a series of Fetch method calls: Local SQL &SQL;

&SQL = CreateSQL("%SelectAll(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);

This SQL object has been opened, bound, and is already closed again:

&SQL = CreateSQL("Delete from %Table(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);

This SQL object should be used in a series of Execute method calls:&SQL = CreateSQL("Delete from %Table(:1) where EMPLID =:2");

2. DeleteSQL : Syntax DeleteSQL([SQL.]sqlname[, dbtype[, effdt]])

Description : The DeleteSQL function enables you to programmatically delete a SQL definition.

Ex : The following code deletes the ABCD_XY SQL definition for the current DBType and as of date:

&RSLT = DeleteSQL(SQL.ABC_XY);

If NOT(&RSLT) Then /* SQL not found — do error processing */ End-if;

The following code deletes the ABCD_XY SQL Definition for the current DBType and November 3, 1998:

&RSLT = DeleteSQL(SQL.ABCD_XY, "",Date(19981103));

3. FetchSQL Syntax : FetchSQL([SQL.]sqlname[, dbtype[, effdt]] )

It returns the SQL definition with the given sqlname as SQL.sqlname or a string value, matching the dbtype and effdt.

If sqlname is a literal name, it must be in quotes. Returns The SQL statement associated with sqlname as a string.

Eg: The following code gets the text associated with the ABCD_XY SQL Definition for the current DBType and as of date:

&SQLSTR = FetchSQL(SQL.ABC_XY);

The following code gets the text associated with the ABCD_XY SQL Definition for the current DBType and November 3, 1998:

&SQLSTR = FetchSQL(SQL.ABCD_XY, "", Date(19981103));

4. GetSQL : Syntax : GetSQL(SQL.sqlname [, paramlist])

Where paramlist is an arbitrary-length list of values in the form: inval1 [, inval2] ...

Description : The GetSQL function instantiates a SQL object and associates it with the SQL definition specified by sqlname. The SQL definition must already exist, either created using PS Application Designer or the StoreSQL function.

Processing of the SQL definition is the same as for a SQL statement created by the CreateSQL function.

For example, suppose you had a SQL definition INSERT_TEST, that had PeopleCode that dynamically (that is, at runtime) generated the following SQL statement:

"INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . .TESTN) VALUES (:1, :2, %DateTimeIn(:3), %TextIn(:4). . .N)";

Suppose you have placed the values to be inserted into an Array of Any, say &AAny:

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . .N); U can execute the insert by: GetSQL(SQL.INSERT_TEST, &AAny);

5. StoreSQL : StoreSQL(sqlstring, [SQL.]sqlname[, dbtype[, effdt]])

It writes the given sqlstring value to a SQL definition, storing it under the name sqlname, with the database type dbtype and the effective date effdt. If sqlname is a literal name, it must be in the form SQL.sqlname or in quotes ("sqlname"). Returns: None.

Example : The following code stores the select statement as a SQL definition under the name SELECT_BY_EMPLID, for the current database type and effective as of the current as of date: StoreSQL("%Select(:1) where EMPLID = :2", SQL.SELECT_BY_EMPLID);

SQL Class Methods: 1. Close : Syntax : Close() It closes the SQL object.

Example : &SQL = CreateSQL("%Delete(:1)"); While /* Still something to do */ /* Set key field values of &ABS_HIST */

&ABSSQL.Execute(&ABS_HIST); End-While; &SQL.Close();

2. Execute : Execute(paramlist), Where paramlist is an arbitrary-length list of values in the form: inval1 [, inval2] ...

It executes the SQL statement of the SQL object.

Ex : &SQL1 = CreateSQL("%Select(:1) where EMPLID = :2", &ABS_HIST, &EMPLID);&SQL_UP = CreateSQL("%Update(:1)");

While &SQL1.Fetch(&ABS_HIST); /* Set some field values of &ABS_HIST. */ &SQL_UP.Execute(&ABS_HIST); End-While; &SQL_UP.Close();

3. Fetch : Fetch(paramlist) , Where paramlist is an arbitrary-length list of values in the form: outvar1 [, outvar2] ...

It retrieves the next row of data from the SELECT that is open on the SQL object.

Any errors result in termination of the PeopleCode program with an error message.

Note. If you want to fetch only a single row, the SQLExec function can perform better, as it fetches only a single row from the server.

Local SQL &SQL; Local Record &REC; Local Array of Record &RECS; /* Get the SQL object open and ready for fetches. */

&SQL = CreateSQL("%SelectAll(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID); /* Create the first record. */

&REC = CreateRecord(RECORD.ABSENCE_HIST); /* Create an empty array of records. */ &RECS = CreateArrayRept(&REC, 0);

While &SQL.Fetch(&REC) /* We got a record, add it to the array and create another.*/

&RECS.Push(&REC); &REC = CreateRecord(RECORD.ABSENCE_HIST); End-While;

4. Open : Open(sql [, paramlist]) Use only after you've already gotten a reference to another SQL object.

SELECT and SEL_ABS_HIST are the names of the SQL definitions created in PS Application Designer.

Local SQL &SQL; &SQL = GetSQL(SQL.SELECT); /* do other processing */ /* get next SQL statement for additional processing */

/* The open automatically closes the previous SQL statement */

&SQL.Open(SQL.SEL_ABS_HIST, &NAME1, "Smith"); While &SQL.Fetch(&ABS_HIST) /* Process ABS_HIST record. */ End-While;

PS Security Types

• Sign-on and Time-out Security

• Page and Dialog Security: Menus or specific actions (Enabled/Disabled)

• Batch Environment Security

a) Process Security (1) Run Control ID (2) Process Groups

b) Reporting Security (1) Report Repository at WS (2) Server should be locked from outside access (3) Can distribute reports and view them based on Roles

• Object Security: GroupID(Field, Record and Page level Security) + AD to primary PL.

• Application Data Security

a) Query/Table Level Security: -Works only for queries (SQL),, -Query Access Groups in Tree Manager,,

-Doesn’t control run-time page access table data

b) Row Level Security: -SQL views - security views,, -saving only rows of data,, -Tailored to specific applications

c) Field Security: -Securing fields or columns by using PeopleCode

Security Definition: It refers to Security attributes created by using Maintain Security. Also known as Access Profiles but at the database level.

The main PS security object types are: User Profiles,, Roles,, Permission Lists

Set of data describing a particular user of PS system.

Data includes Language Code, SETIDs etc. Different from application data tables e.g. PERSON_DATA

User Profile Types: Security related: Passwords, Descriptive: Email Address, Preference: Multilingual

When User Profiles are relevant: Home > People Tools > Security > User Profiles

When user interacts with the system by “” logging in, viewing his/her worklist entry, receiving an email’’ etc.

Roles: Intermediate objects that link User Profile with permission lists.

Examples: Employee, Manager, Customer, Vendor, Student etc.

Roles can be assign in two ways: Home > People Tools > Security > Permissions & Roles > Roles

• Manually, Dynamically by using PeopleCode, LDAP and Query Tools

Permission Lists: Home > People Tools > Security > Permissions & Roles > Permission Lists

• List or group of authorizations that are assigned to a Role.

• They store: Sign-on times, Page access, PeopleTools access etc.

• Some Permission Lists, such as Process Profile or row-level security, we apply directly to a User Profile.

• Data permissions, or row-level security, appear either through a Primary Permissions List or a Row Security PL.

• Online COMPONENT Registration: Home > People Tools > Portal > Structure and Content

PS Authorization IDs: User ID: ID required entering the PS application. Also used to distinctly identify the User Profile.

Connect ID: ID required to connect to the PS database. ID required for direct/2-tier connection.

Access ID: Has administrator level database access (SELECT, UPDATE, DELETE). ID used when connecting PS database through AS.

Symbolic ID: ID used to retrieve Access ID which is stored in PSACCESSPRFL.\\\\

Row Level Security:

· By default, when we give Query users access to a record definition, they have access to all the rows of data in the table built using the associated record definition.

· With row-level security, users can have access to a table without having access to all rows on that table.

· This type of security is typically applied to tables that hold sensitive data.

· For example, we might want users to be able to review personal data for employees in their own department, but not for people in other departments. We would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they could only see rows where the DEPTID matches their own.

· PS applications implement row-level security by using a SQL view that joins the data table with an authorization table.

· When a user searches for data in the data table, the system performs a related record join between the view and the base table rather than searching the table directly.

· To open an existing Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.

· Create custom Query Access Group suitable to our organization. Create functional group names and add records under the group.

· To Add the Query Access Groups to user:

· Open the primary Permission List for the user,,, Go to ‘Query’ Tab -- Click on Access Group Permissions.

· Add the tree name, select the proper Access Group, Select ‘Accessible’ button. Repeat to add more Access groups. --------------Save the permission List.

To apply Row Level Security:

1. Open the record on which we want to apply row-level security.

2. With the record definition open in the AD, click the Properties button, and select the Use tab from the Record Properties dialog box.

3. Select the security record definition (usually a view) in the Query Security Record list box.

4. Once we’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition. If we’ve already used SQL Create to build a table from this record definition, we don’t need to rebuild it.

Note. To secure data through the search record, simply put one of the three RLS fields on our record as a Key, not a List Box Item. They are OPRID (User ID), OPRCLASS (Primary Permission List) and ROWSECCLASS (Row Security Permission List). If one of these fields is on the search record as a Key, not a List Box Item, PeopleTools does the following.

PeopleTools adds a WHERE clause when it performing a SELECT through the record forcing the value to be equal to the current user’s value.

Elements of CI: There are three fundamental elements to the overall CI architecture: Components, CIs, and the CI API.

- Components: One or more pages performing a business transaction that a CI is associated with.

- CI : Exposed aspect of a component. However, unlike components, CIs are readily accessible by internal and external applications and multiple CIs can reference the same component.

- CI API: Application programming interface for a Microsoft COM (Visual Basic) application and PeopleCode

Attributes of CI: Every CI has the following 4- main attributes: CI Name, Keys , Properties and Collections, Methods

Keys for CI: CI keys are created automatically based on the associated component’s search record. Keys define the values that uniquely identify an instance of a CI. Three Types of keys:

Get Keys : Required Keys for the component. Generated from search key;

Create Keys: If the component has an Add action then Create keys are generated for the CI automatically

Find keys: Generated from search key and alternate search key,

These map to fields marked as both Alt and Srch in the component search record.

You may remove Find Keys that you do not wish to make available for searching.

CI: Properties & Collections:

- Properties are the individual data items (fields) that describe a CI. Each property maps to a single field in the CI’s underlying component. They Map to Record Fields

- A collection —which points to a scroll, instead of mapping to an individual field.

Collections: Property that corresponds to a scroll. It contains fields and subordinate scrolls as defined in its underlying cmpt. By default, each collection uses the name of the primary record for the underlying scroll.

- User-defined properties come from a CI’s associated component, and must be added manually.

They are the specific record fields that you choose to expose to an external system with the CI. Standard properties are common across all CIs and are assigned automatically when a CI is created. Standard properties also exist for each collection within a CI. CreateKeyInfoCollection : Returns a set of items that describes the Create keys. GetKeyInfoCollection : FindKeyInfoCollection : PropertyInfoCollection :

Collection & DataRow: ‘’Collection’’

- Count: Returns the number of items in a collection, DataRow

- ItemNum : Returns the position of the row within the collection of a DataRow.

Methods: A method is an object that performs a very specific function on a CI at runtime.

For each CI, numerous methods are available.

- For example, if you are working with a purchase order CI, you may use a method to approve a specific purchase order. Likewise, you can use methods to save or create a new purchase order.

- There are two main types of methods: user-defined methods and standard methods.

User Defined Methods: User-defined methods are those that you can create to meet the requirements of an individual CI.

- A method is simply a PeopleCode function that you wish to make accessible through the CI.

- Each method maps to a single PeopleCode function.

- Application developer can write their own methods. These methods are written as Functions using CI PeopleCode. For example, suppose you wanted to be able to copy an instance of CI data.

Standard Methods: Standard methods are those that are available on all CIs created as default with CI.

- Create : Creates a new instance of a CI ( add data in the component). Equivalent to opening a new record in Add mode online. Returns “True” on success, and “False” on failure.

- Find : Performs a partial key search for a particular instance of a CI. Returns “True” on success, and “False” on failure.

- Get

§ Retrieves a particular instance of a CI. Equivalent to opening a record in Update/Display or Correction mode when online with a PS application. Returns “True” on success, and “False” on failure.

- Save: Saves an instance of a CI. Equivalent to clicking the Save button in the online system.

- Cancel Backs out of the current CI, canceling any changes made since the last save.Equivalent to clicking the Return to Search button online.

§ CI : standard methods: (from PeopleCode only)

- CopyRowset : Enables you to copy rowsets created from the message data in your CI.

- CopyRowsetDelta : Enables you to copy only the changes created from the message data in your CI.

§ Data Collection Methods:

Ø InsertItem(Index)

• Inserts a new item. Equivalent to pressing F7 to insert a new row when online. It takes the item number as a parameter, and follows the same conventions for executing business rules (PeopleCode) as the online system.

Item(Index): Takes an item number as a parameter, and returns the specified row in the collection.

ItemByKeys(keys): Identifies and finds a specific item based on keys. The keys will vary according to the design of the collection.

CI: Add individual objects, or groups of objects, to the CI by dragging objects from the component view into the CI view.

All objects in the component view are part of the underlying CI, and they are accessible through user-defined methods or through PeopleCode events on the component.

However, only the objects in the CI view will be exposed to the calling program at runtime.

Accessing CI Properties: Properties that affect how the CI is executed

Ø These properties must be set before the Component Interface is populated with data (that is, before you use the Get or Create method.)

Ø GetHistoryItems lets you access the data in the Component Interface in a similar manner as if you were accessing a component in Update/Display All mode. If you don’t set this property as True, it’s as if you were accessing a component in update/display mode.

Ø EditHistoryItems lets you access the data in CI in a similar manner as if you were accessing a component in Correction mode.

§ InteractiveMode causes the CI to emulate an online component. For example, if you set a value for a field in a CI and you have set InteractiveMode to True, then any FieldChange PeopleCode programs associated with that field will fire as soon as you set that value.

Accessing CI: User Defined Props: Accessing User Defined CI Properties

– Every user defined property in a CI definition can be used like a property on the object instantiated from that CI at runtime.

– At runtime, you can use PeopleCode to assign a value to that property (field), or to access the value of that field. &MYCI.RETURN_DT = "05/05/2000"; /* OR */ &DATE = &MYCI.RETURN_DT;

Differences b/w Component & CI:

Search Init, Search Save & Rowinit Peoplecode events are not fired for CI

WinMessage cannot be used with CI

Peoplecode functions that relate to GUI and online processing cannot be used

- Menu peoplecode & pop-up menu’s are not supported

- Cursor position(set control value) cannot be used

– Save in the middle of a transaction (Do Save) cannot be used. DoSave,- DoSaveNow .

These include: Search dialog processing. Menu PeopleCode and pop-up menus.

• Transfers between components, including modal transfers.

• DoSave() and DoSaveNow(). Dynamic tree controls. ActiveX controls.

• Functions that are ignored in a CI call: WinMessage,

– CheckMenuItem, DisableMenuItem, EnableMenuItem, HideMenuItem , UncheckMenuItem , SetCursorPos, TransferPanel, TransferPage, DoModalComponent, IsModalComponent, DoModalPanelGroup , IsModalPanelGroup

– GetSelectedTreeNode , GetTreeNodeParent, RefreshTree, TreeDetailInNode, GetControl

Creating a CI: Select File, New from the Application Designer menu.

- Select the CI object type from the New dialog. Select the component on which this CI will be based.

- ..Save the CI. == Add properties, collections, or methods to the CI. .. Set the security…Test the CI.

Implementing CI: The following are the usual actions that we perform with a CI:

- Create a new instance of data - Get an existing instance of data - Retrieve a list of instances of data

PeopleCode for CI implementation:

Create A New Instance Of Data ( assume this one supports ADD mode):

Ø In this example, we are creating a new instance of data for the EXPRESS CI, which is based on the EXPRESS_ISSUE_INV cmpt. The following is the complete code sample: the steps explain each line. This is Add mode. 3- keys are in this CI

Local ApiObject &MYSESSION; Local ApiObject &MYCI;

&MYSESSION = GetSession(); &MYSESSION.Connect(1, "EXISTING", "", "", 0");

&MYCI = &MYSESSION.GetCompIntfc(COMPINTFC.EXPRESS);

&MYCI.BUSINESS_UNIT = "H01B";/* Key 1 */ &MYCI.INTERNAL_FLG = "Y"; /* Key 2 */ &MYCI.ORDER_NO = "NEXT’; /* Key 3 */

&MYCI.Create();

&MYCI.CUSTOMER = "John’s Chicken Shack";/* Other Properties*/ &MYCI.LOCATION = "H10B6987"; /* other Properties */

If NOT(&MYCI.Save()) Then /* save didn’t complete */ &COLL = &MYSESSION.PSMessages; For &I = 1 to &COLL.Count &ERROR = &COLL.Item(&I); &TEXT = &ERROR.Text;

/* do error processing */ End-For; &COLL.DeleteAll(); End-if;

Ø If there are multiple errors, all errors will be logged to the PSMessages collection, not just the first occurrence of an error. As we correct each error, we will want to delete it from the PSMessages collection.

Ø For example, if we specified the incorrect format for a date field of the Component Interface named ABS_HIST, the Text property would contain the following string:

• Invalid Date {ABS_HIST.BEGIN_DT} (90), (1)

To Get an existing instance of data: (update /display mode)

Ø In this example, we are getting an existing instance of data for the EMPL_CHKLST_BC Component Interface, which is based on the EMPLOYEE_CHECKLIST component.

Local ApiObject &MYSESSION; Local ApiObject &MYCI;

&MYSESSION = GetSession(); &MYSESSION.Connect(1, "EXISTING", "", "", 0);

&MYCI = &MYSESSION.GetCompIntfc(COMPINTFC.EMPL_CHKLST_BC); &MYCI.EMPLID= "8001";

&MYCI.Get(); /* Get checklist Code */ &CHECKLIST_CD = &MYCI.CHECKLIST_CD;

/* Set Effective date */ &MYCI.EFFDT = "05-01-1990";

If NOT(&MYCI.Save()) Then /* save didn’t complete */ &COLL = &MYSESSION.PSMessages;

For &I = 1 to &COLL.Count &ERROR = &COLL.Item(&I); &TEXT = &ERROR.Text;

/* do error processing */ End-For; &COLL.DeleteAll(); End-if;

To retrieve partial list check: To retrieve a list of instances of data:

In this example, we are getting a list of existing instances of data for the EMPL_CHKLST_CI-- (CI), which is based on the EMPLOYEE_CHECKLIST component.

Local ApiObject &MYSESSION; Local ApiObject &MYCI; Local ApiObject &MYNEWCI;

&MYSESSION = GetSession(); &MYSESSION.Connect(1, "EXISTING", "", "", 0);

&MYCI = &MYSESSION.GetCompIntfc(COMPINTFC.EMPL_CHKLST_CI);

&MYCI.EMPLID= "8"; &MYCI.LAST_NAME_SRCH = "S";

&MYLIST = &MYCI.Find(); For &I = 1 to &MYLIST.Count; &MYNEWCI = &MYLIST.Item(&I); /* CI from list still must be instantiated to use it */ &MYNEWCI.Get(); /* do some processing */ End-For;

What the STEPS that we need to do in people code to invoke CI?

· Establish a user section Get the CI definition

· Populate the create keys Create an instance of the CI

· Populate the required fields Save theCI.

&Session = GetSession(); &CI = &Session.GetcompIntfc(CompIntfc.INTERFACE_NAME)

&CI.KEY_FILED_NAME = ‘NEW’ If not &CI.Create () Then Else Populate other fields End-if;

Populate the other fields If not &CI.Save () Then Else End-if;

Catching error message in the CI? Or Use of PSMessages in the CI ?

This function needs to be called when ever methods like Find, Save, Create methods return false.

Error text and Error type can be printed in the log message for any other action in to the log message.

Function CheckErrorCodes() &PSMessages = &Session.PSMessages; &ErrorCount = &PSMessages.Count;

For &i = 1 To &ErrorCount &ErrorText = &PSMessages.Item(&i).Text; &ErrorType = &PSMessages.Item(&i).Type; End-For;End-Function;

Can u explain how to read multiple data’s from a flat file(csv) to database using CI

I have to write an inbound program to read multiple files and processing them in PS . After processing I have to move them to different folder.

1)How to handle multiple file in peoplecode (first I have to search for files In particular place then I have to process one by one ) 2)How to move processed files to dump folder. We are using PS Finance 9.0 and Tools 8.48 , NT server, SQL server 2005 data base. Below are the steps to be done by using AE:-

1. Execute FTP/OS commands to search for files by using EXEC or WINEXEC built-in functions

2. Using GetFile function to read the remote file

a. Used the Filename directly only when we read only one File

b. In case of multiple files, Filenames should be stored in a table and then use GetFile function(to read the files) by looping through data on the table

3. Either we can directly read the data on the file or we can read the data by using FileLayout for the file (Use SetFileLayout method to assign the File data to FileLayout object and then use ReadRowset method to read the data)

a. Based on the input file format, we need to decide the reading of data on the file

b. Say for example, if the input file is of CSV format, we can read the data by both ways. But the simple way is to read directly by using GetFile andSplit (Used to split the data based on delimiter) functions

4. Execute FTP/OS commands to move the processed files to the specified locations by using EXEC or WINEXEC built-in functions

1. Data Processing can be done either by using Peoplecode or using CI

2. If it is (CI), use below mentioned steps in AE

a. Get the current session using %session

b. Call the CI by using one of the Session methods

GetCompIntfc ( SessionApiObject.GetCompIntfc(CompIntfc.CI Name) )

Before Calling CI, CI should be tested by using Test CI Popup menu option in AD. This menu option will be applicable only when we open the CI in App Designer.

a. Setting the True/False Boolean value to CI modes ( Interactive / GetHistoryItems / EditHistoryItems)

b. Assigning the values to Key Fields of the Component Search Record

c. Calling CI methods – Get/Create (Based on requirement, call either of these methods.)

Create method will be accessible only when the Component is having ADD action mode

a. Assign values to all the Collection Fields of the CI (Collection Fields are nothing but Record Fields present in each Levels of the Component)

b. Call CI method –“ Save” to save the data into the database

c. Finally call CI method – Cancel to close the CI sessions

In data upload using CI, How to enable it to run next row of data when error occur. For example, 10 transactions

This can be achieved by adding the try catch block to catch the exception in the people code. You can catch the exception and do desired processing or log the error.

eg: While <reading lines of data>

try

<all your ci collection related code goes here> catch Exception &ex <Error handling> end-try; End-while;

Please check if your USERID has access to this CI. Verify the SECURITY. Please check any of the PS provided CI implementation. There is an error display code available. If you use this error handling function in your peoplecode, you can get the exact error. Based on this error you can trouble shoot the problem. Below is the sample code for tracing the error:

Function IScript_DisplayErrors Returns string

&errMsgs = &oSession.PSMessages; If &errMsgs.Count > 0 Then

For &i = 1 To &errMsgs.Count &ERROR = &errMsgs.Item(&i);

If None(&ERROR.Source) Then &Error_txt = &ERROR.Text | “ ” | &ERROR.ExplainText;

MessageBox(0, “”, 0, 0, &Error_txt); End-For; rem &errMsgs.DeleteAll(); End-If; Return &Error_txt; End-Function;

First, create the Staging tables and then use create a File Layout, map the Staging table as a Segment to the File Layout and use Browse button in the file Layout to map the source file into the File Layout. That it saving will set you all done. Following is the Typical File Layout Code.

Function EditRecord(&REC As Record) Returns boolean;             Local integer &E;
REM &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_TranslateTable + %Edit_PromptTable + %Edit_OneZero);
&REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_OneZero);
If &REC.IsEditError Then
For &E = 1 To &REC.FieldCount        &MYFIELD = &REC.GetField(&E);
If &MYFIELD.EditError Then
&MSGNUM = &MYFIELD.MessageNumber;               &MSGSET = &MYFIELD.MessageSetNumber;
&LOGFILE.WriteLine("****Record:" | &REC.Name | ", Field:" | &MYFIELD.Name);
&LOGFILE.WriteLine("****" | MsgGet(&MSGSET, &MSGNUM, ""));
End-If;      End-For;    Return False;      Else    Return True;     End-If;      End-Function;
Function ImportSegment(&RS2 As Rowset, &RSParent As Rowset)
Local Rowset &RS1, &RSP;    Local string &RecordName;   Local Record &REC2, &RECP;
Local SQL &SQL1;                  Local integer &I, &L;
&SQL1 = CreateSQL("%Insert(:1)");        &RecordName = "RECORD." | &RS2.DBRecordName;
&REC2 = CreateRecord(@(&RecordName));     &RECP = &RSParent(1).GetRecord(@(&RecordName));
For &I = 1 To &RS2.ActiveRowCount        &RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
&CovrgCd = &RS2.GetRow(&I).GetRecord(1).GetField(Field.KZL_COVERAGE_ELECT).Value;
Rem          This is the place where we usually modify, add all the field level validations to the file layout before loading it to the staging table         If (EditRecord(&REC2)) Then      &SQL1.Execute(&REC2);
                                                                    &RS2(&I).GetRecord(1).CopyFieldsTo(&RECP);
For &L = 1 To &RS2.GetRow(&I).ChildCount    &RS1 = &RS2.GetRow(&I).GetRowset(&L);
If (&RS1 <> Null) Then                                       &RSP = &RSParent.GetRow(1).GetRowset(&L);
                         ImportSegment(&RS1, &RSP);                  End-If;    End-For;
If &RSParent.ActiveRowCount > 0 Then         &RSParent.DeleteRow(1);      End-If;
Else       &LOGFILE.WriteRowset(&RS);
&LOGFILE.WriteLine("****Correct error in this record and delete all error messages");
&LOGFILE.WriteRecord(&REC2);                      For &L = 1 To &RS2.GetRow(&I).ChildCount
&RS1 = &RS2.GetRow(&I).GetRowset(&L);         If (&RS1 <> Null) Then
&LOGFILE.WriteRowset(&RS1);    End-If; End-For; End-If; End-For; End-Function;
rem *****************************************************************;
                                                              rem * PeopleCode to Import Data *;
rem *****************************************************************;
Local File &FILE1;     Local Record &REC1;   Local SQL &SQL1;   Local Rowset &RS1, &RS2;   Local integer &M;
&FILE1 = GetFile("C:\Documents and Settings\jvelchamy\Desktop\Csv_files\Benefits\FILE2.CSV", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("C:\Documents and Settings\jvelchamy\Desktop\Csv_files\Benefits\FILE2.CSV.err", "W", %FilePath_Absolute);            &FILE1.SetFileLayout(FileLayout.KZL_BEN_ENROLL_FL);     &LOGFILE.SetFileLayout(FileLayout.KZL_BEN_ENROLL_FL);
&RS1 = &FILE1.CreateRowset();            &RS = CreateRowset(Record.KZL_HLT_BEN_STG);
&SQL1 = CreateSQL("%Insert(:1)");        &RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;        ImportSegment(&RS1, &RS);   &RS1 = &FILE1.ReadRowset();    End-While;
&FILE1.Close();       &LOGFILE.Close();

What is SQLExec function ? And what are the limitations of this command? What is the diffrence between SQLExec and CreateSQL?

The SQLExec function executes a SQL command from within a PCODE program by passing an SQL command string. Sqlexec is used to select, insert, update or delete a single record using a sql statement.

Sqlexec executes the sql comand from within pcode bypassing the component processor and interacts dorectly with db server.

If we want to select, insert, update or delete a series of record then use Getsql or Createsql and then execute sql class method. Limitation

If U write a SQL statement that retrieves more than one row of data, SQLExec outputs only the first row to its output variables.

If U want to select multiple rows of data, U can use ScrollSelect to read rows into a work scroll.

Only be issued in SavePreChange, WF and SavePostChange events.

Since the SQL statement is contained within quotes, it is a black box to PS. Means the programmer is responsible for the syntax, efficiency and maintenance of the SQL.

Also, if a SQLSelect is being performed within the function, only one row of data can be returned.

Person model is all about capturing the information about a Person and his/her relationship in an organization. This information can be captured using Core HR tables.

Person Information can be captured in tables like

· PERSON · PERS_DATA_EFFDT · NAMES · ADDRESSES · PERSONAL_PHONE · EMAIL_ADDRESSES · PERS_NID

Person’s organizational relationship can be captured in tables like:· PER_ORG_ASGN · PER_POI_TYPE

Table loading Sequence (installation)?

Company table , Installation, Location, Department, Salary Plan, Salary step, Job code,Pay group, Benefit Programs

Sequence of table setup in HR? Installation, Company, SetId (SetId table) ,

Business Unit, Tableset Control, Organization defaults by Permission lists, Business unit HR defaults table, Establishment, Location,Department, Compensation Rate Code, Salary plan, Grade and Step, Job Code , Pay group.

IMP TABLES IN HRMS? PS_PERSONALDATA, PS_PERS_NID, PS_EMPLOYMENT, PS_JOB, PS_JOB_TBL, PS_DEPT_TBL, PS_LOCATION_TBL, PS_FED_TAX_DATA, PS_STATE_TAX_DATA, PS_LOCAL_TAX_DATA,

PS_BEN_PROG_PRATIC, PS_HEALTH_BEN, PS_LEAVE_PLANS, PS_LIFE_BEN, PS_RTRMNT_PLN, PS_PAY_CHECK,

PS_PAY_LINE, PS_PAY_BONUS, PS_PAY_DEDUCTIONS, PS_POSITION_DATA

2-TIER: USER ID -------CONFIG MR-------CONNECT ID------- DB

3-TIER: USER ID –PS APPLICATION—SYM ID----AS---ACCESS ID ----- DB

Peoplesoft Interview Stuff

Friday, 26 August 2011

PeoplesoftInterviewStuff

Log file in Application Engine