Ask Question
Help Others
Home

CompShack Blogs

Leading Zero Issue in CSV/Excel Formatterd Report

Wed, 11/19/2008 - 9:22am — shiv4uall

If you use APP ENGINE to report the data from the temp table /staging table on to CSV file using excel format, there is a known issue with fields that have leading zeros. For ex:You have a field of length 6 characters. If the field value is 002123, the excel file removes the leading zeros. But if the requirement is to preserve those leading zeros, we can do something like this.
Update the temp table right before you write out into a file.

Update %Table(Temp_Stg) A
Set A.Field = (Select '"='||A1.Field||'"' from  %Table(Temp_Stg) A1 where A1.Keyfield = A.Keyfield)

Function to Increment a Date Field

Sun, 11/02/2008 - 9:40pm — Lepa

The function increments a date field using the AddToDate function and sets a weekday field to the corresponding weekday. You can increment date value by year, month or day.

Function increment_date(&DATE, &WEEKDAY, &YEAR_INCREMENT, &MONTH_INCREMENT, &DAY_INCREMENT);
   If All(&DATE) Then
      &DATE = AddToDate(&DATE, &YEAR_INC, &MONTH_INC, &DAY_INC);
      /*get day value for the incremented date*/
      &WEEKDAY = String(Weekday(&DATE));
   End-If;
End-Function;

Function to Add Leading Characters to a Value or Field

Mon, 10/27/2008 - 7:11pm — Lepa

A function to facilitate the addition of a leading character to a field value. The function takes three parameters:
- &LEADCHAR: The character you want to append to your value.
- &FIELDZISE: The over all size of your value after appending your desired character.
- &FIELDVALUE: The value you want to append the character to.

Function add_leading_char(&LEADCHAR, &FIELDSIZE, &FIELDVALUE, &RESULTFIELD);

Content References Accessed by a Permission List

Wed, 10/01/2008 - 2:21pm — Lepa

Another permission list query to list Content References accessed by a specific Permission List.

SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
    FROM psprsmdefn a, psprsmperm b, psclassdefn c
   WHERE a.portal_reftype = 'C'
     AND a.portal_cref_usgt = 'TARG'
     AND a.portal_name = b.portal_name
     AND a.portal_reftype = b.portal_reftype
     AND a.portal_objname = b.portal_objname

PeopleTools Objects Accessed by a Permission List

Sun, 09/28/2008 - 6:13pm — Lepa

A query that will list all peopletools objects (Query, Application Designer, Data Mover) that a specific permission list could access.

SELECT DISTINCT b.menuname
           FROM psclassdefn a, psauthitem b
          WHERE a.classid = b.classid
            AND (   b.menuname = 'CLIENTPROCESS'
                 OR b.menuname = 'DATA_MOVER'
                 OR b.menuname = 'IMPORT_MANAGER'
                 OR b.menuname = 'APPLICATION_DESIGNER'
                 OR b.menuname = 'OBJECT_SECURITY'
                 OR b.menuname = 'QUERY'
                )

Pages Accessed by a Permission List

Sun, 09/28/2008 - 5:59pm — Lepa

A query to identify pages that could be accessed by a specific permission list.

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
 WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

Rate Posts Contributed by CompShack IT Members!

Tue, 09/23/2008 - 10:03pm — CompShack

In an attempt to recognize quality posts made by our members, users (registered and site visitors) can now vote on posts available on the site. The voting option will be located at the very begining of each post (example picutre below).

vote on members posts

Registered users will have a link under their "My account" to easily track posts they voted on and we will be soon adding a page listing top rated nodes!

As always, feedback is always welcome.

Roles Assigned to a Permission List

Thu, 09/18/2008 - 10:00pm — Lepa

A query to help you identify Roles that are assigned to a specific permission list.

SELECT b.rolename, b.classid AS permission_list
  FROM psclassdefn a, psroleclass b
 WHERE a.classid = b.classid AND a.classid = :permissionlist;

Identify Records with a Specific Subrecord

Wed, 09/17/2008 - 9:08am — Lepa

A subrecord in PeopleSoft is a way to group fields together and place them on a record, view, or a temp table. Here is a query you can use to identify records that have a specific subrecord.

SELECT a.recname, a.fieldname, b.rectype, b.objectownerid
  FROM psrecfield a, psrecdefn b
 WHERE a.recname = b.recname
   AND a.fieldname = 'SUB-RECORD-NAME';

User IDs assigned to a Permission List

Tue, 09/16/2008 - 1:45pm — Lepa

A query to list all user IDs that are assigned to a specific permission list.

SELECT   c.roleuser AS USER_IDs
    FROM psclassdefn a, psroleclass b, psroleuser c
   WHERE a.classid = b.classid
     AND b.rolename = c.rolename
     AND a.classid = :permissionlist
GROUP BY c.roleuser;