Dynamic SQR for PS_JOB,PS_EMPLOYEE Details

!*****************************************************************************!
! Name:      istemp.SQR a copy of template.sqr with few modifications         !
! Descr:     Client name - Starters Report                  !
!*****************************************************************************!
! Orig Date: 29/08/2012                                                       !
! Purpose:   Extract the list of all employees that started in HI during the  !
!                                                                           !
! Author:     ismail                                     !
! Reference:  rajendra                                               !
!*****************************************************************************!
!                                                                             !
!               Confidentiality Information:                                  !
!                                                                             !
! This module contains confidential and proprietary information               !
! of Hilton; it is not to be copied, reproduced, or transmitted               !
! in any form, by any means, in whole or in part, nor is it to                !
! be used for any purpose other than that for which it is                     !
! expressly provided under the applicable license agreement.                  !
!                                                                             !
! Copyright (C) 2007 Hilton Hotels. All Rights Reserved.                      !
!                                                                             !
!*****************************************************************************!
! CHANGE HISTORY                                                              !
! ----------------------------------------------------------------------------!
! Date          Author       Change                                           !
! ----------------------------------------------------------------------------!
! 05/09/2012    Ismail   envytee solutions (Rajendar singh)                   !
!                                          !
!*****************************************************************************!
#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-PROGRAM

 do Init-DateTime
 do Init-Number
 do Get-Current-DateTime
 do Stdapi-Init
! do Select-Parameters
 do Process-Main
 do Stdapi-Term
! do reset
! do Terminate
end-program
!*****************************************************************************!
!*****************************************************************************!
BEGIN-HEADING 6
  let $ReportID         = 'Ismail'
  let $ReportTitle      = 'HI - Starters Report'
 #include 'stdhdg01.sqc'
   print 'Customer Listing' (3) center
   print 'From Date:'        (4,1)
   print $from_date            (4,13) 
   print 'To Date:  '        (5,1)
   print $to_date            (5,13) 
   print 'SL.No'  (6,2)  bold underline
   print 'EMPID' (,8) bold underline
   print 'NAME'   (,16) bold underline
   print 'CITY' (,40) bold underline
   print 'STATE'    (,58) bold underline
   print 'PAN NO' (,66) bold underline
   print 'SEX' (,80) bold underline
   print 'SALARY' (,85) bold underline
   print 'HIRE DATE'  (,97) bold underline
   print 'DESIGNATION' (,110) bold underline
   print 'DEPARTMENT' (,132) bold underline
   print 'EXPERIENCE' (,152) bold underline
  
   END-HEADING
!*****************************************************************************!
BEGIN-PROCEDURE Select-Parameters
Begin-Select
RC.IS_FROM_DATE
RC.IS_TO_DATE
RC.IS_DEPTID   
    let $from_date = &RC.IS_FROM_DATE
    let $to_date = &RC.IS_TO_DATE
        let $dept_id = &RC.IS_DEPTID
FROM PS_IS_SQRCMP_TBL RC
WHERE RC.OPRID           = $prcs_oprid
  AND RC.RUN_CNTL_ID     = $prcs_run_cntl_id
End-Select
      if $dept_id = ' '
         let $wdpid = ' '
      else
         let $wdpid = 'AND  P.DEPTID = ''' || $dept_id || ''''        
      END-IF
!**** following code of line is to what deptno is passed and its where claues****!
display 'departno entered is :  ' noline
display $dept_id
display 'clause passed is :  ' noline
display $wdpid
END-PROCEDURE
!*****************************************************************************!
!to control the in put parameters if the report is run by sqrw or by process-scheduler
!*****************************************************************************!
BEGIN-PROCEDURE Process-Main
let $wdpid = ' '
let $dept_id = ' ' 
if #prcs_process_instance = 0   
  input $from_date 'Enter from date(YYYY-MM-DD)'  type=date  format='yyyy-mm-dd'
  input $to_date 'Enter to date (YYYY-MM-DD)'  type=date  format='yyyy-mm-dd'
  input $dept_id 'Enter department no (11000-99000)'  type = char 
     if $dept_id = ''
         let $wdpid = ' '
      else
         let $wdpid = 'AND  P.DEPTID = ''' || $dept_id || ''''        
     END-IF
 show $dept_id
 show $wdpid
else
  do Select-Parameters
END-IF
let #data_row_count = 0
begin-select
P.EMPLID  (+1,8)
P.NAME       (,16,20)
P.CITY              (,40)
P.STATE             (,60)
P.NATIONAL_ID             (,65)
P.SEX               (,81)
J.MONTHLY_RT        (,85)
J.HIRE_DT           (,97,11)
P.JOBTITLE            (,110,18)
P.DEPTNAME             (,132,18)
      let #data_row_count = #data_row_count + 1
      let $dt = datediff(datenow(),&J.HIRE_DT,'YEAR')
       print #data_row_count (,2,3)
       print $dt (,155,2)
       FROM PS_EMPLOYEES P,PS_JOB J
       WHERE P.EMPLID = J.EMPLID
       AND   P.EFFDT=J.EFFDT
       AND   P.JOBCODE = J.JOBCODE
       AND   P.DEPTID = J.DEPTID
       [$wdpid]
       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 Process-Main
!********************************************************************************!
#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