Jun 23, 2015

Data Base Links(DBLinks)

DBLink:
  • Data Base Link provides you the control on the tables from other database.
  • It allows you to query against other databases using the syntax TABLENAME@DBLINK.
Syntax:
  • Use the following code to create a database link:

            create database link "database_link_name"
      connect to username
      identified by "password"
      using 'connection_name';
Example:

     create database link "HRDEV"
      connect to sysadm
      identified by "sysadm"
      using 'HR89DEV';

Usage:

  • We can select any record from HR89DEV database.
    • SELECT * FROM PS_JOB@HRDEV;

Jun 22, 2015

Job Code Full Sync Process (Integration Broker)

Activate Data Publish Rules In Source

    • Go to Enterprise Components > Integration Definitions >Full Data Publish Rule
    • Search for "JOBCODE_FULLSYNC” message.
    • If the rule is Inactive then make the status to Active and save the rule.
Activate Outbound Routing In Soruce      
    • Go to People Tools >> Integration Broker >> Integration Setup >> Service Operations
    • Search for “JOBCODE_FULLSYNC” Service Operation
    •  Navigate to the Routings tab.
    •  Select the outbound routing between "Source" to "Target"
    •  If the Routing is Inactive then click on ‘Activate Selected Routings’ button.

     Activate Inbound Routing In Destination
  •     Go to PeopleTools > Integration Broker > Integration Setup > Service Operations
  •       Search for “JOBCODE_FULLSYNC” Service Operation
  •       Navigate to the Routings tab.    
  •       Select the inbound routing between "Source" to "Target"   
  •       If the Routing is Inactive the click on 'Activate Selected Routings' button.  


    Run “Full Table Data Publish” Process In Source
       
  •       
    Go to Enterprise Components > Integration Definitions > Initiate Processes > Full Data Publish
  •      
    Provide run control id in search page.
  •      
    In run control page select process frequency as “Once”
  •       
    Select Message Name “JOBCODE_FULLSYNC”
  •       
    Click on “Run” button


  •       Select EOP_PUBLISHT (Full Table Data Publish) process
  •       Click “Ok” button.
 
    
    Verify IB Messages Were Posted In Source
  •      Go to PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Asynchronous Services
  •      In Monitor Overview tab select queue level as Pub Con, Group By Service Operation
  •      Select proper time period
  •      Verify messages for JOBCODE_FULLSYNC service operation
   
  •      If messages are in Error or Timeout state then click on message count.
  •      You will navigate to Publication Contracts tab on the same page.
  •      Click on Details link for timed out or error message
  •      Find out the root cause.
       Verify IB Messages Were Received In Destination
  •     Go to PeopleTools > Integration Broker > Service Operations Monitor > Monitoring > Asynchronous Services
  •       In Monitor Overview tab select queue level as Sub Con, Group By Service Operation  
  •       Select proper time period
  •       Verify messages for JOBCODE_FULLSYNC service operation

     Note: If messages were not received then bounce pubsub servers.

Apr 10, 2014

Phone Number Validation Using Regular Expressions in PeopleSoft

Local JavaObject &oPhoneNumExpression = CreateJavaObject("java.lang.String", "\+{0,1}[0-9\s\-\(\)]+");
Local JavaObject &oPhoneNum = CreateJavaObject("java.lang.String", &PhoneValue);
If &oPhoneNum.matches(&oPhoneNumExpression) = False Then
   MessageBox(0, "", 0, 0, "Enter valid phone number");
End-If;

Regular Expression "\+{0,1}[0-9\s\-\(\)]+"   : It allows a number + in starting and - in the middle of the number.
&PhoneValue : Contains the entered phone number value.

Dec 20, 2013

Reading excel file using jxl in peoplesoft

Reading Excel using Java:
Step1: Create a java class to read excel and compile and place the class file in /app/psoft/psadmin/appserv/classes  .

/* Java class to read excel  file */
import java.io.*;
import jxl.*;
public class ReadExcelFile {
public static void main(String[] args){
System.out.println("Hi");
}
public Workbook Readworkbook(String Filename){
try {
Workbook ReadExcel = Workbook.getWorkbook(new File(Filename));
return ReadExcel;
}
catch (Exception i)
        {
            System.out.println(i);
            return null;
        }
}
}

Step 2: Use the below code to read data

Local string &strGetEnv = GetEnv("PS_HOME");
Local string &St_path_l = &strGetEnv | "/user/" | %DbName | "/ABC.xls";
Local JavaObject &Obj_class_l = CreateJavaObject("nExcel");
Local JavaObject &Obj_getworkbook_l = &Obj_class_l.readWorkbook(&St_path_l);
&rows = &Obj_getworkbook_l.getSheet(0).getRows();
&cols = &Obj_getworkbook_l.getSheet(0).getColumns();
WinMessage("No of rows:" | &rows | " No of columns:" | &cols, 0);
Local string &St_returnvalue_l;
For &i = 1 To &rows - 1
   For &j = 0 To &cols - 1;
      &St_returnvalue_l = &Obj_getworkbook_l.getSheet(0).getCell(&j, &i).getContents();
      MessageBox(0, "", 0, 0, "" | &St_returnvalue_l);
   End-For;
End-For;

&Obj_getworkbook_l.close();

Jul 25, 2013

Changing tablespace of a record.

If you want to assign a record to the new tablespace that you created 

open your record, and go to Tools --> Data Administration --> Set Tablespace.

then you can assign new table space to record.