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