Diffrence between search record and add search record

Search record : using this we can get search record search  key in search page.
Add search record : in this we get this record search key when the component is in add mode(add new value)

Global and Local Variables in SQR

A local procedure is any procedure that has one of the following in its BEGIN-PROCEDURE: Parameters (coded within parentheses), or the keyword LOCAL.

Within a local procedure, a variable name containing an initial underscore (#_amount) indicates a global variable.

Within a local procedure, a variable name without an initial underscore (#amount) indicates a local variable.

A local variable cannot be accessed from outside of the LOCAL procedure in which it is used, unless the procedure passes the variable to another local procedure through a parameter list.

Within a global procedure, all variables are global, and a variable name cannot contain the initial underscore.

A local procedure can return a value to a global variable by specifying an initial colon (:#amount) within its parameter list.

These rules apply to numeric (#), string ($), and database column (&) variables (but not arrays).

Maybe a few examples will help.

  begin-procedure Main
    let #n = 1
    do Other-Procedure
    show #n
  end-procedure

  begin-procedure Other-Procedure local
    let #n = 2
  end-procedure

What is the value of #n at the SHOW statement? It is 1. The assignment within Other-Procedure (a local procedure) creates another variable called #n. There is no connection between this local #n and the global #n.

If, however, Other-Procedure contained this line:

    let #_n = 2

then the value of the global variable #n would be changed to 2, and the SHOW would display 2.

Now consider this:

  begin-procedure Main
    let #n = 1
    do Other-Procedure(#n)
    show #n
  end-procedure

  begin-procedure Other-Procedure(:#number)
    let #number = 2
  end-procedure

This time, the value of #n at the SHOW statement is 2. #n was passed as a parameter to Other-Procedure, which knows it as #number. In addition, Other-Procedure's parameter list includes the leading colon (:#number), indicating that it will pass a value back to the caller. Since the caller provides the variable (#n), it will receive the modified value of this variable, which is 2.

Be careful to code the leading colon if you need it. Suppose the BEGIN-PROCEDURE was coded like this:

  begin-procedure Other-Procedure(#number)

In this case, #number would contain the value of #n passed by the caller (1) but it would not be passed back to the caller. Even though Other-Procedure changes the value of #number, this will not affect the value of #n, since the leading colon is not coded.

Here's a trick question. What's the value of #n at the SHOW statement in the example below?

  begin-procedure Main
    let #n = 1
    do Other-Procedure(#n)
    show #n
  end-procedure

  begin-procedure Other-Procedure(#n)
    let #n = 2
  end-procedure

It's 1. The leading colon was not coded, so the #n in the local procedure is not passed back to the caller.

How about in this convoluted example?

  begin-procedure Main
    let #n = 1
    do Other-Procedure(#n)
    show #n
  end-procedure

  begin-procedure Other-Procedure(#n)
    let #_n = 2
  end-procedure

This time it's 2. The value of global #n was passed to the procedure as #n, but was never used. Instead, the procedure specified the global variable itself directly, as #_n. Remember that #n in a global context (in procedure Main in this example) is the same as #_n in a local context (in Other-Procedure). As I said, this example is convoluted, but it's the sort of thing we end up with if we're not careful.

Now try this example:

  begin-procedure Main
    let #n = 1
    do Other-Procedure
    show #n
  end-procedure

  begin-procedure Other-Procedure
    let #_n = 2
  end-procedure

This example is invalid and it won't even compile. The reason is that #_n is specified within a global procedure, and that's illegal. Other-Procedure is global in this example because it does not receive any parameters and does not contain the LOCAL keyword.

So, with these complications, why use local procedures and variables at all? They give you much better control over variables. If you break your program up into small local procedures, and you can prevent one procedure from affecting all of the others. This makes it easier to code large programs (there's less to remember as you code) and easier to track down bugs. The larger the program, the more useful this becomes. In addition, if you code reusable procedures in SQCs, it's best to keep them local as much as possible, so that the programmers who use those procedures don't need to check to see if they used the same variable names.

So, there are good reasons for using local procedures and variables. Unfortunately, those little underscores and colons can give rise to their own bugs. On balance, I think that using local procedures and variables is a very good idea, but it requires a little more awareness while you code.

SQR arrays

Requirement : produce an employee listing by department and
print the following departmental statistical information: the number of active employees,
the number of women, the number of men, and an average salary for the department.

!TEST12A.SQR
!USING ARRAYS IN SQR
#DEFINE STAT_ARRAY_SIZE 100
!***********************
BEGIN-SETUP
!***********************
CREATE-ARRAY NAME=STATISTICS SIZE ={STAT_ARRAY_SIZE}
FIELD=DEPTID:CHAR='999999'
FIELD=ACTIVE_EE:NUMBER
FIELD=WOMEN:NUMBER
FIELD=MEN:NUMBER
FIELD=TOTAL_SALARY:NUMBER
END-SETUP
!********************
BEGIN-HEADING 3
!********************
PRINT 'EMPLOYEE STATISTICS BY THE DEPARTMENT' (1,10)
PRINT 'DEPARTMENT ID' (2,1,)
PRINT 'EMPLOYEE ID' (,+2)
PRINT 'STATUS' (,+2)
PRINT 'SEX' (,+2)
PRINT 'ANNUAL RATE' (,+2)
PRINT ' ' (3,1)
END-HEADING
!********************
BEGIN-PROGRAM
!********************
LET #I=0
DO SELECT_EMPLOYEES
DO PRINT_SUMMARY
END-PROGRAM
!***********************************
BEGIN-PROCEDURE SELECT_EMPLOYEES
!***********************************
BEGIN-SELECT
A.DEPTID (+1,1,13) ON-BREAK
A.EMPLID (,+2,11)
A.EMPL_STATUS (,+2,6)
B.SEX (,+2,3)
A.ANNUAL_RT (,+2,12) EDIT $,$$$,$$$.00
     DO UPDATE_ARRAY
FROM PS_JOB A, PS_PERSONAL_DATA B
WHERE
A.EMPLID=B.EMPLID
AND A.EMPL_STATUS='A'
!AND A.DEPTID IN ('10200','21700')
AND A.EFFDT=(SELECT MAX(EFFDT) FROM PS_JOB
    WHERE EMPLID=A.EMPLID
    AND EMPL_RCD=A.EMPL_RCD
    AND EFFDT<=SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.EFFSEQ=(SELECT MAX(EFFSEQ) FROM PS_JOB
    WHERE EMPLID=A.EMPLID
    AND EMPL_RCD=A.EMPL_RCD
    AND EFFDT = A.EFFDT)
ORDER BY A.DEPTID,A.EMPLID
END-SELECT
END-PROCEDURE
!*******************************
BEGIN-PROCEDURE PRINT_SUMMARY
!*******************************
PRINT 'SUMMARY BY DEPARTMENT' (+2,10)
LET #I=0
WHILE #I <= {STAT_ARRAY_SIZE}
GET $DEPTID #ACTIVE #WOMEN #MEN #TOTAL_SALARY
FROM STATISTICS(#I)
IF $DEPTID='999999'
BREAK
ELSE
IF #ACTIVE <> 0
LET #AVERAGE_SALARY = #TOTAL_SALARY / #ACTIVE
END-IF
PRINT 'DEPARTMENT ' (+1,1)
PRINT $DEPTID (,+1)
PRINT 'NUMBER OF ACTIVE EMPLOYEES = ' (+1,1,33)
PRINT #ACTIVE (,+1) EDIT 9,999,999
PRINT 'NUMBER OF WOMEN = ' (+1,1,33)
PRINT #WOMEN (,+1) EDIT 9,999,999
PRINT 'NUMBER OF MEN = ' (+1,1,33)
PRINT #MEN (,+1) EDIT 9,999,999
PRINT 'AVERAGE SALARY = ' (+1,1,33)
PRINT #AVERAGE_SALARY (,+1) EDIT $$$,$$$,$$$.99
NEXT-LISTING NEED=4 SKIPLINES=1
END-IF
ADD 1 TO #I
END-WHILE
END-PROCEDURE
!*****************************

!*******************************
Begin-Procedure UPDATE_ARRAY
!*******************************
!Find an element in the array with Deptid = Selected Deptid
!If it's not there, create a new element in the array
Let $Found='N'
Let #j=0
While #j < {Stat_Array_Size}
Get $Deptid From Statistics(#j) Deptid
If $Deptid='999999'
Let #i=#j
Put &A.Deptid Into Statistics(#i) Deptid
Let $Found='Y'
Break
Else
If &A.Deptid = $Deptid
Let #i=#j
Let $Found = 'Y'
Break
End-If
End-If
Let #j=#j+1
End-While
If $Found='N'
Display 'ERROR: There are more than {Stat_Array_Size} departments in the array'
Stop
End-If
Array-Add 1 To Statistics(#i) Active_EE
Array-Add &A.Annual_Rt To Statistics(#i) Total_Salary
If &B.Sex='M'
Array-Add 1 to Statistics(#i) Men
Else
Array-Add 1 to Statistics(#i) Women
End-If
End-Procedure
!****************************************************
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                                      OUTPUT
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 EMPLOYEE STATISTICS BY THE DEPARTMENT
DEPARTMENT ID  EMPLOYEE ID  STATUS  SEX  ANNUAL RATE


               SFTRN067     A       U      $42,000.00
               SFTRN077     A       U      $42,000.00
               SFTRN087     A       U      $42,000.00
               SFTRN097     A       U      $42,000.00
               SFTRN107     A       U      $42,000.00
               SFTRN117     A       U      $42,000.00
               SFTRN127     A       U      $42,000.00
               SFTRN137     A       U      $42,000.00
               SFTRN147     A       U      $42,000.00
               SFTRN157     A       U      $42,000.00
T006           KUI059       A       M      $30,368.00

         SUMMARY BY DEPARTMENT
DEPARTMENT  10000
NUMBER OF ACTIVE EMPLOYEES =            304
NUMBER OF WOMEN =                       117
NUMBER OF MEN =                         187
AVERAGE SALARY =                   $1,757,008.79

Multiple reports in sqr

Requirement :

1.       Develop a SQR report to Generate Hire/Terminate report.  Single SQR should generate 2 reports. Process has to pickup From Date and To date as runcontrol parameters and generate report.

Report Heading – Should be

                Employee Hire Information (Center)

                From Date                                                                           Report Rundatetime

                To Date

                Logo of Company at Right Corner(Envytee).

Report Footing – Should be

                Page Number 1ofn

this is the SQR

#include 'setenv.sqc'      !Set Enviroment Values
#Include 'setup32.sqc'     !Printer and page-size initialization (Landscape)

!**********************    B E G I N   P R O G R A M    **********************!
!********************************************************

Begin-Setup
!***********************
Declare-Layout employee_active
    Left-Margin=1
End-Declare
Declare-Layout emp_Terminated
    Orientation=Landscape
    Left-Margin=0.3
End-Declare
Declare-Report employee_active
    Layout=employee_active
End-Declare
Declare-Report emp_Terminated
    Layout=emp_Terminated
End-Declare
Declare-Image companylogo
Type=BMP-FILE
Source='C:\Documents and Settings\Administrator\Desktop\envyteelogo.BMP'
End-Declare
End-Setup
!**********************************************************

BEGIN-PROGRAM

 do Init-DateTime
 do Init-Number
 do Get-Current-DateTime
 do Stdapi-Init
    if $prcs_process_instance = ''
      input $from_date 'Enter From Date' type = date format = 'YYYYMMDD'
      input $to_date 'Enter To Date' type = date format = 'YYYYMMDD'
   else
 do Select-Parameters
end-if

 do Process-Main
 do process-term
 do Stdapi-Term
end-program



!*****************************************************************************!

!*****************************************************************************!
BEGIN-HEADING 9  For-Reports=(employee_active)
  let $ReportID         = 'Rama'
  let $ReportTitle      = 'HI - Starters Report'
 #include 'stdhdg01.sqc'
SET-COLOR PRINT-TEXT-BACKGROUND = (255,0,255)
   print 'employees who are working' (3) center
Let #Image_Length = 7
Let #Image_Height = 3
!Print 'company logo' (3,20)
Print-Image companylogo (3,1)
Image-Size=(#Image_Length,#Image_Height)
Let #Curr_Line_Adj = #Image_Height +3
   print 'From Date:'        (5,1)
   print $from_date            (5,13) edit 'DD_MM-YYYY'
   print 'To Date:  '        (6,1)
   print $to_date            (6,13)  edit 'DD-MM-YYYY'
   print 'EMPID' (8,1)
   print 'NAME'   (,10)
   print 'SALARY' (,28)
   print 'HIRE DATE'  (,33)
   print 'Action' (,48)
   print 'Sl.No' (,55)
SET-COLOR PRINT-TEXT-BACKGROUND = (255,255,255)
   !print '--------' (7,1) FILL
   !GRAPHIC () FONT  4  8.5
!GRAPHIC (1,30,11) HORZ-LINE 8

END-HEADING
!*****************************************************************
BEGIN-HEADING 9  For-Reports=(emp_Terminated)
let $ReportID         = 'Rama'
  let $ReportTitle      = 'HI - Starters Report'
 #include 'stdhdg01.sqc'
SET-COLOR PRINT-TEXT-BACKGROUND = (255,0,255)
   print 'employees who are terminated' (3) center
Let #Image_Length = 7
Let #Image_Height = 3
!Print 'company logo' (3,20)
Print-Image companylogo (3,1)
Image-Size=(#Image_Length,#Image_Height)
Let #Curr_Line_Adj = #Image_Height +3
   print 'From Date:'        (5,1)
   print $from_date            (5,13) edit 'DD_MM-YYYY'
   print 'To Date:  '        (6,1)
   print $to_date            (6,13)  edit 'DD-MM-YYYY'
   print 'EMPID' (8,1)
   print 'NAME'   (,10)
   print 'SALARY' (,28)
   print 'TERMINATION_DT'  (,34)
print 'Action' (,48)
  print 'Sl.No' (,55)
SET-COLOR PRINT-TEXT-BACKGROUND = (255,255,255)
   !print '--------' (7,1) FILL
!GRAPHIC () FONT  4  8.5
!GRAPHIC (1,30,11) HORZ-LINE 8


END-HEADING
!******************************************************************
begin-footing 4 For-Reports = (employee_active)
! print "Page n of m" in the footing
page-number (1,50) 'Page '
last-page () ' of '
end-footing
!******************************************************************************!
begin-footing 4 For-Reports = (emp_Terminated)
! print "Page n of m" in the footing
page-number (1,50) 'Page '
last-page () ' of '
end-footing
!*****************************************************************************!
BEGIN-PROCEDURE Select-Parameters

Begin-Select
RC.FROMDATE
RC.THRUDATE
   
    let $from_date = &RC.FROMDATE
    let $to_date = &RC.THRUDATE

FROM PS_SP_RUNCNTL_REC RC
WHERE RC.OPRID           = $prcs_oprid
  AND RC.RUN_CNTL_ID     = $prcs_run_cntl_id
End-Select

END-PROCEDURE
!*****************************************************************************!
!to control the in put parameters if the report is run by sqrw or by process-scheduler
!*****************************************************************************!
BEGIN-PROCEDURE Process-Main

let #data_row_count = 0
USE-REPORT employee_active
begin-select DISTINCT
P.EMPLID  (+1,1)
P.NAME_FORMAL       (,10,17)
J.MONTHLY_RT        (,28,4)
J.HIRE_DT           (,34,12)
J.ACTION         (,48)

     let #data_row_count = #data_row_count + 1 ! increment when a row is found
     print #data_row_count (,55,4)
       FROM PS_PERSONAL_DATA P,PS_JOB J
       WHERE P.EMPLID = J.EMPLID
and J.ACTION='HIR'
      AND   J.HIRE_DT between $from_date and $to_date
end-select
if #data_row_count = 0
    print ' **************  No Data Found  ************** '        (+2, 60)
   end-if
end-procedure ! list_customers
!*********************************************************************************
begin-procedure process-term
let #data_row_count = 0
Use-Report emp_Terminated
begin-select DISTINCT
P1.EMPLID  (+1,1)
P1.NAME_FORMAL       (,10,17)
J1.MONTHLY_RT        (,28,4)
J1.TERMINATION_DT           (,34,12)
J1.ACTION         (,48)


     let #data_row_count = #data_row_count + 1 ! increment when a row is found
     print #data_row_count (,55,4)
       FROM PS_PERSONAL_DATA P1,PS_JOB J1
       WHERE P1.EMPLID = J1.EMPLID
and J1.ACTION='TER'
      AND   J1.TERMINATION_DT between $from_date and $to_date
end-select
if #data_row_count = 0
    print ' **************  No Data Found  ************** '        (+2, 60)
   end-if
end-procedure
 
!********************************************************************************!
#include 'reset.sqc'            ! Reset printer
#include 'stdapi.sqc'    ! UPDATE PROCESS API
#include 'curdttim.sqc'         ! Current Date and Time
#Include 'datetime.sqc'  !Routines for date and time formatting
#Include 'number.sqc'    !Routines to format numbers
#include 'prcsapi.sqc'   !Update Process Request API
#include 'prcsdef.sqc'   !Update Process Request variable declaration
 

multiple reports in SQR

Begin-Setup
!***********************

Declare-Report EE_Data
!Layout=EE_Data
End-Declare
Declare-Report Term_Dependents
!Layout=Term_Dependents
End-Declare
End-Setup
begin-program
use-report EE_Data        !!!using USEREPORT command we can print on specific report!!!
print 'hi surya' (1,1)
use-report Term_Dependents   
print 'bye surya' (1,1)
end-program
Begin-Heading 4 For-Reports=(EE_Data)
print 'this is report 1' (+1,1)
end-heading
Begin-Heading 4 For-Reports=(Term_Dependents)
print 'this is report 2' (+1,1)
end-heading

Usage of GETGRID function

This code should be helpful if you are trying to hide a grid column using PeopleCode
To
hide a column in a grid (using the Grid and GridColumn objects). You no
longer must loop through every row in the grid and hide that field. Now
you can use the GridColumn property Visible. The Visible property will
also hide grid columns that are displayed as tabs in the PeopleSoft
Internet Architecture.
Local Grid &GRID;
Local GridColumn &COLUMN;
If COMPLETE_FLAG = "Y" Then
&GRID = GetGrid(PAGE.RESOURCE, "GRID1");
&COLUMN = &GRID.GetColumn("COL5");
&COLUMN.Visible = False;
End-If;

Difference between setsearchdefault and setsearchdilogbehavior

SetSearchDefault
Syntax:

SetSearchDefault([recordname.]fieldname)
Description
Use the SetSearchDefault function to set system defaults (default values set in record field definitions) for the
specified field on search dialog boxes. It does not cause the FieldDefault event to fire.

EMP1.DEPTNO.Value = "20";
EMP1.EMPID.Value = "000000000003443";
SetSearchDefault(EMP1.DEPTNO);
output is :
 

 SetSearchDialogBehavior

SyntaxSetSearchDialogBehavior(force_or_skip)

DescriptionUse the SetSearchDialogBehavior function in SearchInit PeopleCode to set the behavior of search and add
dialog boxes before a page is displayed, overriding the default behavior. There are two dialog behavior
settings: skip if possible (0) and force display (1).

0: sets the dialog behavior to skip if possible.
1: sets the dialog behavior to force display.




Note: using this function search page is skiped but you should be carefull that all  search keys should be initallized to a value.if u not initialize one key (in above ex:empid then we can't skip page and it acts like
SetSearchDefault function .
 like this 

How to Use Temp Tables to Easily Insert Effective-dated Rows

Your business hands out a list of currencies that are used at your company and asks you to inactivate all the remaining ones in PeopleSoft. This is being done so that, users are not overwhelmed with the large set of currencies to choose from.
How do you do this quickly?
Well, here’s what we did.
As this is a fairly large set of data, we didn’t want to do it manually from the front end. Inactivating all non-used currencies in the PS_CURRENCY_CD_TBL table from the backend looked more promising. Since this is an effective dated table, we had to insert a new effective dated row for all currencies that were to be made inactive.
We used the below query to check what currencies were going to be inactivated. It will list down all currencies that are Active as of today and are not present in the list.

SELECT *
FROM PS_CURRENCY_CD_TBL A
WHERE
A.EFF_STATUS = 'A'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) 
               FROM PS_CURRENCY_CD_TBL A_ED
               WHERE A.CURRENCY_CD = A_ED.CURRENCY_CD
               AND A_ED.EFFDT <= GETDATE())
AND CURRENCY_CD NOT IN
        ('USD',
        'GBP',
        'INR',
        'JPY',
        'INR',
        'EUR',
        'AUD')

This SQL statement would insert the rows that we are interested in into a
 temporary table (#TMP1) where we can do all what we need. 

SELECT * INTO #TMP1
FROM PS_CURRENCY_CD_TBL A
WHERE
A.EFF_STATUS = 'A'
AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) 
               FROM PS_CURRENCY_CD_TBL A_ED
               WHERE A.CURRENCY_CD = A_ED.CURRENCY_CD
               AND A_ED.EFFDT <= GETDATE())
AND CURRENCY_CD NOT IN
        ('USD',
        'GBP',
        'INR',
        'JPY',
        'INR',
        'EUR',
        'AUD')

Now that we have all our rows in the Temp table, we will update the effective date, effective status and the last updated date time so that these currencies become inactive.

UPDATE
#TMP1
SET EFFDT = '01-OCT-2012',
EFF_STATUS = 'I',
LASTUPDDTTM = GETDATE()

There’s time now for a quick review to make sure everything is as expected.

SELECT * FROM
#TMP1

Now we insert the rows from the temporary table back into the original table. Since #TMP1 is a temporary table, it will not exist once you disconnect the current session.

INSERT INTO PS_CURRENCY_CD_TBL
SELECT * FROM #TMP1

How to display colour and image using HTML area

following approach i made using my colleges  ramana and santosh , i am very thankful to them by sharing this.
step 1 : define field as char 1 veg_name it as vegetables use translate value ex: carrot , ladyfinger ,orange second as descr  field and third as image field datatype as imagereference.
 step 2: insert html area into page set field as descr 

write code in fieldchage event of veg_name field. using html code we can display text color.

Evaluate SP_VEG_REC.SP_VEG.Value
When = "C"
   REM SP_VEG_REC.DESCR = "Red";
   SP_VEG_REC.DESCR = "<font = 4 color=red>" | "CARROT" | "</font>";
   rem SP_VEG_REC.DESCR = "<body bgcolor='red'>" | "</body>";
   SP_VEG_REC.SP_VEG_IMG.Value = Image.CARROT;
   rem this is html text;
   Break;
When = "L"
   REM SP_VEG_REC.DESCR = "GREEN";
   SP_VEG_REC.DESCR = "<font = 4 color=GREEN>" | "LADYSFINGER" | "</font>";
   SP_VEG_REC.SP_VEG_IMG.Value = Image.LADIESFINGER;
   Break;
When = "D"
   REM SP_VEG_REC.DESCR = "BLUE";
   SP_VEG_REC.DESCR = "<font = 4 color=BLUE>" | "DRUMSTICK" | "</font>";
   SP_VEG_REC.SP_VEG_IMG.Value = Image.DRUMSTICKS;
   Break;
When-Other
   SP_VEG_REC.DESCR = "Unknown";
End-Evaluate



step 3: insert images by clicking new image .insert image into same page using this we can display image.

out put as follows :