!***********************************************************************
! Name_of_sqr_file : sqr04.sqr   Effective dated table
!***********************************************************************
! Description: CS SQR template -- PLUS-- additional program 
! logic needed for a typical SQR program and generated report.
! Owner: Dept_that_this_sqr_belongs_to.
! Owner: Dept_that_this_sqr_belongs_to.
! Frequency: How_often_would_this_sqr_be_run. 
!***********************************************************************
! Specifications By: Name_of_person_requesting_this program
! Date: Date_when_request_was issued. 
!***********************************************************************
! Created By: Arlan Martin
! Date: 03/27/2008
!***********************************************************************
! MODIFICATION LOG
!***********************************************************************
! Mod #    Programmer                  Date
!   ** Effecrtive Date processing **  
!***********************************************************************

#include 'setenv.sqc'   !Set environment

!***********************************************************************
! Setup Section
!***********************************************************************
! Subsitution variable used:
!         UM_ORIENTATION       values: PORTRAIT, LANDSCAPE (default PORTRAIT)
!         UM_PRINTER_TYPE      values: HPLASER, LINEPRINTER, INDEPENDENT, HTML, CSV, 
!                                      POSTSCRIPT (default INDEPENDENT)
!         UM_REPORT_OPTION     values: SINGLE, MULTIPLE
!                            					 SINGLE - Almost always used
!                            					 MULTIPLE - Rarely used, set when creating a report for each campus from one program
Begin-Setup

#define UM_ORIENTATION    LANDSCAPE
#define UM_PRINTER_TYPE   INDEPENDENT
#define UM_REPORT_OPTION  SINGLE
#include 'umseta.sqc'

!Please add additional setup commands below this line...

  #define col_sep 		1
  #define col_sep2 		2
  #define col_sep3 		3
  #define col_sep4 		4
  #define col_sep5 		5
  #define col_sep10 	10
  #define col_sep15		15

	#define col_Name    			30 
	#define col_emplid   			10
	#define col_institution		8
	#define col_acad_career   11
	#define col_admit_term		10
	#define col_admit_type	  10
	#define col_region   			10
	#define col_create_dt		  12
	#define col_acad_lvl		  8
	#define col_campus				8 
	#define col_type					5
 
end-setup

! Do Not Change
!***********************************************************************
! Initialize SQR
!***********************************************************************
begin-Program	
	do Stdapi-Init
	do Init-DateTime
	do Get-Current-DateTime
	show ''
	show 'Starting Issue Reporting: ' $AsOfNow
!***********************************************************************

! Please modify the variable values below to your values
  Let $ReportTitle = 'Effective Dated Table'
  Let $ReportId = 'SQR04' 
  LET $BU = ' '

  do Get_Dates 
    
  do Get-Database-Name ($DBName)
   show 'Database ID: ' $DBName
   show '$ReportID: ' $ReportId
   
! Establish selection criteria for report - [Input Parameters] 
  do Get_Input_Parms 
  Let $report_busunit = $BU
    
	do Process-Main 
	
	do Wrap_Up    

!***********************************************************************
	do Get-Current-DateTime
	show ''
	show 'Ending Issue Reporting: ' $AsOfNow
	do Stdapi-Term
end-Program
!***********************************************************************
! End of Initialize SQR
!***********************************************************************

! 
!***********************************************************************
! Report Heading Setup
!***********************************************************************
 Begin-Heading 6	
	  ALTER-PRINTER 
	     Point-Size = 8 
	     
	  #include 'umhdra.sqc'           !* Set Standard Report Headings   **
   
! **** 1 LINE REPORT HEADING SETUP  **** 
!	  print 'EMPL_ID'						(,+{col_sep},{col_emplid}) underline BOLD
!	  print 'ACAD_CAREER'				(,+{col_sep},{col_acad_career}) underline BOLD
!	  print 'BUS_UNIT'					(,+{col_sep},{col_institution}) underline BOLD   
!	  print 'ADMIT_TERM'				(,+{col_sep},{col_admit_term}) underline BOLD
!	  print 'ADMIT_TYPE'				(,+{col_sep},{col_admit_type}) underline BOLD
!	  print 'REGION'						(,+{col_sep},{col_region}) underline BOLD
!	  print 'CREATE-DTE'				(,+{col_sep},{col_create_dt}) underline BOLD
!	  print 'ACAD-LVL'					(,+{col_sep},{col_acad_lvl}) underline BOLD 
!	  print 'CAMPUS'						(,+{col_sep},{col_campus}) underline BOLD

! **********  setup Report Heading graphics  *******************  
	 print ' ' (+2,1)
	  graphic (+1,1,5) box 1 0 21	
		graphic (,31,6)  box 2 0 18	  
	  graphic (,42,6)  box 2 0 18 
	  graphic (,54,5)  box 2 0 18
	  graphic (,65,6)  box 2 0 18
	  graphic (,76,6)  box 2 0 18
	  graphic (,89,6)  box 2 0 18

				
! **** 2 LINE REPORT HEADING SETUP  ****   
    print 'NAME'              (,1,{col_Name}) underline BOLD  
		print 'EMPL'							(,+{col_sep},{col_emplid}) BOLD
 	  print ' EFF'							(,+{col_sep},{col_acad_career}) BOLD 		
	  print 'CNTRY'							(,+{col_sep},{col_admit_term}) BOLD
	  print 'NAME'							(,+{col_sep},{col_admit_type}) BOLD  			  
	  print 'LAST'				  		(,+{col_sep},{col_create_dt}) BOLD
	  print 'NAME'							(,+{col_sep},{col_type}) BOLD
 
!*  * 2nd row of column Headings.....   ** 	  
	  print ' '									(+1,1,{col_Name}) 
	  print ' ID'								(,+{col_sep},{col_emplid}) underline BOLD
	  print ' DATE'						  (,+{col_sep},{col_acad_career}) underline BOLD
	  print 'FORM'							(,+{col_sep},{col_admit_term}) underline BOLD
	  print 'DSPLY'							(,+{col_sep},{col_admit_type}) underline BOLD
	  print 'UPDTE'							(,+{col_sep},{col_create_dt}) underline BOLD
	  print 'TYPE'							(,+{col_sep},{col_type}) underline BOLD
	            
 End-Heading
!***********************************************************************
! End Report Heading Setup 
!***********************************************************************


! ToDo - Add your program within Process-Main
!***********************************************************************
! Program entry point
!***********************************************************************
begin-procedure Process-Main
!* Add your logic here 
 
   let $first_time = 'Y'                  !*  Alpha-numeric literal   **
   let #recs = 0                  				!*  Numeric field           **
   let $match-found = 'N'									!*  Setup matching trigger  **
   let $DO_Procedure = 'Process-Main'      
   
BEGIN-SELECT on-error=sql-recover
A.EMPLID,
A.NAME,
A.NAME_TYPE,
A.EFFDT,
A.COUNTRY_NM_FORMAT,
A.NAME_DISPLAY,
A.LASTUPDDTTM

    move &a.emplid								to 		$emplid
    move &a.name									to    $NAME
    move &a.name_type       			to		$name_type
    move &a.EFFDT									to		$EFFDT
    move &a.COUNTRY_NM_FORMAT			to		$COUNTRY_NM_FORMAT
    move &a.NAME_DISPLAY					to		$NAME_DISPLAY
    move &a.LASTUPDDTTM 					to		$LASTUPDDTTM

!* Conditional logic termination 		
	 If #recs >= 150 
	   show  $ReportID '.SQR - stopped report after printing: ' #recs edit 9,999   
!*     STOP                   				!*  ABORT This Program!       **
     EXIT-SELECT             					!*  EXIT SELECT IMMEDIATELY   **
   End-If
   
   Do Print_It               					!*  Do Cmd to  print procedure  **

FROM SYSADM.PS_NAMES A 
 where a.emplid = '0012706'
   AND A.NAME_TYPE = 'PRI'
   AND A.EFF_STATUS = 'A'
   AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NAMES A_ED
         WHERE A.EMPLID = A_ED.EMPLID
          AND A.NAME_TYPE = A_ED.NAME_TYPE
          AND A_ED.EFFDT <= $ASOFDATE ))   
	   
End-Select

end-procedure
!***********************************************************************
! End of Program entry point
!***********************************************************************


! ToDo - Add your functional DO procedures here....
!************************   $$$$$$$$$$    ******************************
!  *********************  Misc. Do Procedures   ********************
!***********************************************************************
begin-procedure Get_Input_Parms 
! Retrieve "Input parameters for SQR"

  do Get-Value('BusUnit',$BU) 
   show 'Business Unit is: ' $BU
   
  do Get-Value('EmplID',$EmplID)
!*   show 'EmplID is: ' $EmplID

end-procedure

!************************   $$$$$$$$$$    ******************************
begin-procedure Get_Dates

  Let $ASOFDATE = datenow() 						!*  Current Date  DD-MMM-CCYY **
  show '$ASOFDATE: ' $ASOFDATE
  
! Following examples of date manipulation    
  let #wk_yr = substr($ASOFDATE,8,4)
  let #wk_yr = (#wk_yr - 1)   								!* Prior Year value     **
 
! ***  Establish PRIOR Year date to work with  ***   
  do CONVERT-TO-DTU-DATE($ASOFDATE, $InDt) 
  do DTU-ADD-YEARS($InDt, -1,$OutDt)				!get prior year date  :$OutDt
  do CONVERT-FROM-DTU-DATE($OutDt, $EndDt)
  let $YR = $EndDt 
  let $PREV_REQYEAR_DT = '31-DEC-'||substr($YR,8,4)
  let $PREV_JUNE_DT = '30-JUN-'||substr($YR,8,4)
   show '$EndDt: ' $EndDt
   show '$PREV_REQYEAR_DT: ' $PREV_REQYEAR_DT
   show '$PREV_JUNE_DT: ' $PREV_JUNE_DT

	let $ASOF_Month = substr($ASOFDATE,4,3)
	let $CURR_MO_DT = '15-'||$ASOF_Month||'-'||substr($ASOFDATE,8,4)
	let $CURR_JAN_DT = '31-JAN-'||substr($ASOFDATE,8,4)
	let $BEG_REQYEAR_DT = '01-JAN-'||substr($ASOFDATE,8,4)
	  show '$CURR_MO_DT: ' $CURR_MO_DT
	  show '$CURR_JAN_DT: ' $CURR_JAN_DT
	  show '$BEG_REQYEAR_DT: ' $BEG_REQYEAR_DT

end-procedure 

!************************   $$$$$$$$$$    ******************************
begin-procedure Print_It
   ALTER-PRINTER 
	     Point-Size = 8 
	
	print $Name 									(+1,1,{col_Name})    
  print $emplid									(,+{col_sep},{col_emplid})
  print $EFFDT									(,+{col_sep},{col_acad_career})
  print $COUNTRY_NM_FORMAT			(,+{col_sep},{col_admit_term})
  print $NAME_DISPLAY						(,+{col_sep},{col_admit_type})
  print $LASTUPDDTTM						(,+{col_sep},{col_region}) 
  print $name_type							(,+{col_sep3},{col_type})	
      		
  
  add 1   	to  	#recs            ! ** Increment record counter ** 
  						
end-procedure 

!************************   $$$$$$$$$$    ******************************
begin-procedure Wrap_Up

	move '  ** Total Records Processed: ' 		to  	  $Name
  print $Name					(+2,1,{col_Name})
  print #recs							(,+{col_sep},{col_emplid}) edit 999,999
  
  show 'Total Recs: ' #recs edit 999,999

end-procedure 


!************************   $$$$$$$$$$    ******************************
Begin-Procedure sql-Recover

   show 'Do_Procedure > ' $Do_Procedure
   display $SQL-Error
   let $err_msg = $SQL-Error

 STOP QUIET

End-Procedure sql-Recover

!************************   $$$$$$$$$$    ******************************
Begin-Procedure show-errors

  show '+-------------------------------------------------------------+'
  show $err_msg
  show '+-------------------------------------------------------------+'
  STOP
 
End-Procedure show-errors



!***********************************************************************
!  ****************  End of User Do Procedures   *******************
!***********************************************************************


!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'reset.sqc'      !Reset printer procedure
#Include 'stdapi.sqc'     !Update Process API
#Include 'datemath.sqc'   !Date Manipulation functions
#Include 'umfunclib.sqc'  !UMaine specific functions
!***********************************************************************
! End of Standard Include Files
!***********************************************************************

!***********************************************************************
! Special Include Files
!***********************************************************************
!Add any other include files below