Customize Notepad++ for color coding

Follow steps given in below link

http://workingscripts.com/2009/06/notepad-peoplecode-sqr/

Problem with MsgGetExplainText function

We use MsgGetExplainText() to get the message from message catalog explain text part to display as a error or message or warning.

In PT8.49 onwards, the function is returning the text twice. for example if the text is "show me as a error message", the output will be like

show me as a error message
show me as a error message

To get rid of this bug, we need to clear the second message which is in the buffer.
use it as below.

&error= MsgGetExplainText(28000,12,"Message not found");
&novalue=MsgGetExplainText(99999,1,"");/* 99999,1 should not have any text*/
error (&error);

or this way.

&error= MsgGetExplainText(28000,12,"Message not found");
&error=MsgGetExplainText(99999,1,&error);/* 99999,1 should not have any text*/
error (&error);

Hope it will be useful for you.

PeopleSoft Auditing

Record Auditing:

select
    RECNAME,
    RECDESCR,
    AUDITRECNAME,
    case when bitand(RECUSE,1) > 0 then 'Y' else 'N' END AUDIT_ADD,
    case when bitand(RECUSE,2) > 0 then 'Y' else 'N' END AUDIT_CHANGE,
    case when bitand(RECUSE,4) > 0 then 'Y' else 'N' END AUDIT_DELETE,
    case when bitand(RECUSE,8) > 0 then 'Y' else 'N' END AUDIT_SELECTIVE
from PSRECDEFN
WHERE AUDITRECNAME != ' '
ORDER BY RECNAME;

Record Field Auditing:

select
    F.RECNAME,
    F.FIELDNUM,
    F.FIELDNAME,
    F.USEEDIT,
    case when bitand(F.USEEDIT,8) > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,
    case when bitand(F.USEEDIT,128) > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,
    case when bitand(F.USEEDIT,1024) > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE
from
    PSRECFIELD F
where
    F.FIELDNAME = (
        select
            case when (
                bitand(USEEDIT,8) > 0 or
                bitand(USEEDIT,128) > 0 or
                bitand(USEEDIT,1024) > 0
            ) then FIELDNAME else '' end as FIELD_AUDITED
        from PSRECFIELD
        where RECNAME = F.RECNAME
        and FIELDNAME = F.FIELDNAME
    )
order by F.RECNAME, F.FIELDNUM;

Refer to: http://peoplesoft.wikidot.com/what-is-audited
and to: http://xtrahot.chili-mango.net/uploaded_images/USEEDIT%20Flags-799884.jpg

Creating Database level Audit records in Peoplesoft

As we all aware, creating application level audit is just adding 3 extra fields in the audit record and placing it in main record properties.

But creating Database level audit is slightly different from it.

Please follow the below steps to do it.

1. Similar to application level audit, create a new record with addition fields AUDIT_OPRID, AUDIT_STAMP and  AUDIT_ACTN.
2. Create triggers based on your required operations like add, change and delete.
3. Run the trigger SQL script in the database.

How to create triggers:
If you are good in writing SQL in various database platforms, then you can write trigger code by your own.
if you are not familier with SQL on how to create triggers, then you can use the delivered functionality to generate the script in the following location.

Enter into the component using the main record on which you want to create the audit.
After entering into the component, Enter the audit record which was created and select the options based on your requirement.

Click on Generate code button, it will generate the script based on the type of database you use.
now save the component.

Execute Trigger Script in Database:
Now go to Perform Database level Audit component which is also in the same navigation to execute the script generated, in your database.

If you want to see the script, you can get it in process log files of that process.

How to insert null value into date field in peoplesoft using SQLEXEC

If we build a record in peoplesoft App designer, other than date fields, all other fields will be 'not null' fields in database. only date columns will be null fields. 

But if we want to insert a null value using SQLEXEC as below.

&name='Samba';

&birthdate=null;    /* or &birthdate=' '; */

SQLexec("INSERT INTO TABLE VALUES(:1,:2)",&name,&birthdate);

Result in Database will be as below:

Name        Birthdate

Samba      01/01/1900

Thought we inserted a null value or space (' '), it has taken 01/01/1900 into the date field.

To avoid the above situation, we can try this.

&name='Samba';

&birthdate=Date(0);

SQLexec("INSERT INTO TABLE VALUES(:1,:2)",&name,&birthdate);

now the output will be

Name        Birthdate

Samba        Null

So Date(0) can be used to insert null value into date fields.Hope this will be useful.

Programming in MS Word

It is interesting to know that we can do some programming in Word. You can get more information in below link:
http://word.tips.net/T001045_Using_Last-page_Headers_and_Footers.html

I ave executed below example:

Hope it helps.

Crosstab in BI Publisher

Hope this helps: https://blogs.oracle.com/xmlpublisher/entry/new_skool_crosstabbing

To Find objects in a Project

SELECT (CASE OBJECTTYPE WHEN 0 THEN 'Record'
WHEN 1 THEN 'Index'
WHEN 2 THEN 'Field'
WHEN 3 THEN 'Field Format'
WHEN 4 THEN 'Translate Value'
WHEN 5 THEN 'Pages'
WHEN 6 THEN 'Menus'
WHEN 7 THEN 'Components'
WHEN 8 THEN 'Record PeopleCode'
WHEN 9 THEN 'Menu PeopleCode'
WHEN 10 THEN 'Query'
WHEN 11 THEN 'Tree Structures'
WHEN 12 THEN 'Trees'
WHEN 13 THEN 'Access group'
WHEN 14 THEN 'Color'
WHEN 15 THEN 'Style'
WHEN 16 THEN 'N/A'
WHEN 17 THEN 'Business process'
WHEN 18 THEN 'Activity'
WHEN 19 THEN 'Role'
WHEN 20 THEN 'Process Definition'
WHEN 21 THEN 'Server Definition'
WHEN 22 THEN 'Process Type Definition'
WHEN 23 THEN 'Job Definitions'
WHEN 24 THEN 'Recurrence Definition'
WHEN 25 THEN 'Message Catalog'
WHEN 26 THEN 'Dimension'
WHEN 27 THEN 'Cube Definitions'
WHEN 28 THEN 'Cube Instance Definitions'
WHEN 29 THEN 'Business Interlink'
WHEN 30 THEN 'SQL'
WHEN 31 THEN 'File Layout Definition'
WHEN 32 THEN 'Component Interfaces'
WHEN 33 THEN 'AE program'
WHEN 34 THEN 'AE section'
WHEN 35 THEN 'Message Node'
WHEN 36 THEN 'Message Channel'
WHEN 37 THEN 'Message'
WHEN 38 THEN 'Approval rule set'
WHEN 39 THEN 'Message PeopleCode'
WHEN 40 THEN 'Subscription PeopleCode'
WHEN 41 THEN 'N/A'
WHEN 42 THEN 'Component Interface PeopleCode'
WHEN 43 THEN 'AE PeopleCode'
WHEN 44 THEN 'Page PeopleCode'
WHEN 45 THEN 'Page Field PeopleCode'
WHEN 46 THEN 'Component PeopleCode'
WHEN 47 THEN 'Component Record PeopleCode'
WHEN 48 THEN 'Component Rec Fld PeopleCode'
WHEN 49 THEN 'Image'
WHEN 50 THEN 'Style sheet'
WHEN 51 THEN 'HTML'
WHEN 52 THEN 'Not used'
WHEN 53 THEN 'Permission List'
WHEN 54 THEN 'Portal Registry Definitions'
WHEN 55 THEN 'Portal Registry Structures'
WHEN 56 THEN 'URL Definitions'
WHEN 57 THEN 'Application Packages'
WHEN 58 THEN 'Application Package Peoplecode'
WHEN 59 THEN 'Portal Registry User Homepage'
WHEN 60 THEN 'Problem Type'
WHEN 61 THEN 'Archive Templates'
WHEN 62 THEN 'XSLT'
WHEN 63 THEN 'Portal Registry User Favorite'
WHEN 64 THEN 'Mobile Page'
WHEN 65 THEN 'Relationships'
WHEN 66 THEN 'Component Interface Property Peoplecode'
WHEN 67 THEN 'Optimization Models'
WHEN 68 THEN 'File References'
WHEN 69 THEN 'File Type Codes'
WHEN 70 THEN 'Archive Object Definitions'
WHEN 71 THEN 'Archive Templates (Type 2)'
WHEN 72 THEN 'Diagnostic Plug In'
WHEN 73 THEN 'Analytic Model'
WHEN 79 THEN 'SERVICE'
WHEN 80 THEN 'SERVICE OPERATION'
WHEN 81 THEN 'SERVICE OPERATION HANDLER'
WHEN 82 THEN 'SERVICE OPERATION VERSION'
WHEN 83 THEN 'SERVICE OPERATION ROUTING'
WHEN 84 THEN 'IB QUEUE'
WHEN 85 THEN 'XMLP TEMPLATE DEFN'
WHEN 86 THEN 'XMLP REPORT DEFN'
WHEN 87 THEN 'XMLP FILE DEFN'
WHEN 88 THEN 'XMLP DATA SOURCE DEFINITION'
ELSE 'UNKNOWN OBJECT TYPE' END) AS OBJECTTYPE
, RTRIM(RTRIM(OBJECTVALUE1) || '.' || RTRIM(OBJECTVALUE2) || '.' || RTRIM(OBJECTVALUE3) || '.' || RTRIM(OBJECTVALUE4),'.') OBJECTNAAM
FROM PSPROJECTITEM
WHERE PROJECTNAME = 'ELM_TEMP' --- Replace with your project name...
ORDER BY OBJECTTYPE, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3;

Source: http://bloggingaboutoracleapplications.org/peoplesoft-quicktip-find-all-objects-projects/

Error with BI Publisher: The macro cannot be found or has been disabled because of your macro security settings

I had issues with BI Publisher when using Word 2007 - specifically the following errors: "The macro cannot be found or has been disabled because of your macro security settings" and "Object library invalid or contains references to object definitions that could not be found"

Resolution:

The problem is cached EXD files.

1. Close Word,
2. Do a search on C drive *.EXD (include hidden files)
3. Rename them to .BAK
4. Go back into word and you should be able to Load Data

Hope this helps it worked for me

Purging Web Server Cache Without Shutting Down webserver

Use the purge web server memory cache servlet command by issuing the following URL for your environment:
http://[server]/psp/[site]/?cmd=purge&pwd=[password]

So at the signon screen, replace ?cmd=login at the end of the URL with ?cmd=purge&pwd=dayoff. Note that dayoff is the default PeopleSoft web profile password.

RTF Template Limitation for Tables

As RTF Template is created in Word file, we have restriction of using Table with more than 64 columns. To handle these kind of cases, we can convert the RTF Template into XSL-FO template and exit accordingly.
1. By Using Word file

2. By Using BI Publisher Template Viewer

Hope it helps.

To make a query to be displayed as a page

1. Prepare a Query and make Owner as public
2. Create a Content Reference by using ‘Add Content Referance’ in desired path in Portal Structure and Content. In URL Information Group box, select URL Type as ‘PeopleSoft Generic URL’ and give Portal URL as ‘q/?ICAction=ICQryNameURL=PUBLIC.’
3. Assign it with proper permission list in Security Page.

Show report without Ren Server

http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/generating-a-pdf-off-data-on-a-page-and-serving-it-to-the-user-2163346

Materialized Views

I have learned about a new concept about Database Tables: Materialized Views which will be very useful in reporting purpose. This will help to decrease the processing time drastically thus improving the performance. 

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. (This is known as Snapshots previously). Materialized Views will be updated immediately the base table is updated or we can opt to deferred to indicate to be populated every refresh operation of database.

Refer to the below links for more details: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm#i2106785