!****************************************************************************
! Report Name: EMS Report
! ReportID: ZINEMS
! Creation Date: 28/03/2009
! Orientation: A4 Landscape
!****************************************************************************
! Maintenance History
! --------------------------------------------------------------------------
! NAME |DATE |SR#/CR# |Description
! --------------------------------------------------------------------------
!
!
!****************************************************************************
!****************************************************************************
! This report will produce the EMS report
!****************************************************************************
!****************************************************************************
! Standard Include Files
!****************************************************************************
#include 'setenv.sqc' !Set environment
!****************************************************************************
!Procedure to Initialize Report
!****************************************************************************
BEGIN-setup
Declare-Variable
Date $resignation_dt_p
end-declare
End-setup
BEGIN-PROCEDURE Init-Report
Do Define-Prcs-Vars
Do Get-Run-Control-Parms
Do Select-Parameters
Do Get-RowSecClass
END-PROCEDURE
!***********************************************************************
!Procedure to Get Row Security Class
!***********************************************************************
BEGIN-PROCEDURE Get-RowSecClass
Let $OPRID = $prcs_oprid
Begin-select
ROWSECCLASS &Rowsecclas
let $Rowsecclass=&Rowsecclas
from PSOPRDEFN A
where A.OPRID = $OPRID
End-select
END-PROCEDURE
!****************************************************************************
!Procedure to Get Run Control Parameters
!****************************************************************************
BEGIN-PROCEDURE Select-Parameters
Let $OprID = $prcs_oprid
BEGIN-SELECT
TR.SETID
TR.EMPLID
TR.FROMDATE
TR.THRUDATE
TR.ZIN_ZONE
TR.ZIN_SUB_ZONE
TR.ESTABID
TR.LOCATION
!TR.ZIN_BRANCH
TR.DEPTID
TR.GRADE
TR.ZIN_FUNC_CODE
TR.SUPERVISOR_ID
TR.ZIN_CHECKLIST_ST
TR.ZIN_APPROVAL_ST
Let $Set_I = rtrim(&TR.SETID,' ')
Let $Emp_I = rtrim(&TR.EMPLID,' ')
Let $Frm_Dt_I = rtrim(&TR.FROMDATE,' ')
Let $To_Dt_I = rtrim(&TR.THRUDATE,' ')
Let $Zone_I = rtrim(&TR.ZIN_ZONE,' ')
Let $Sz_I = rtrim(&TR.ZIN_SUB_ZONE,' ')
Let $Loc_I = rtrim(&TR.ESTABID,' ')
Let $Br_I = rtrim(&TR.LOCATION,' ')
!Let $Brn_I = rtrim(&TR.ZIN_BRANCH,' ')
Let $Sb_Fn_I = rtrim(&TR.DEPTID,' ')
Let $Grade_I = rtrim(&TR.GRADE,' ')
Let #Fn_I = edit(&TR.ZIN_FUNC_CODE,'99')
!Let $Fn_I = lpad(ltrim($Fn_I,' '),2,'0')
Let $chk_st_I = rtrim(&TR.ZIN_CHECKLIST_ST,' ')
Let $App_st_I= rtrim(&TR.ZIN_APPROVAL_ST,' ')
Let $Supervisor_I = rtrim(&TR.SUPERVISOR_ID,' ')
FROM PS_ZIN_EMS_RCR TR
WHERE TR.OPRID= $prcs_oprid
AND TR.RUN_CNTL_ID = $prcs_run_cntl_id
END-SELECT
END-PROCEDURE
!****************************************************************************
!Procedure to Reset Run Status & Printer
!****************************************************************************
BEGIN-PROCEDURE Terminate
If #prcs_process_instance > 0
Do Update-Prcs-Run-Status
End-If
Do Reset
END-PROCEDURE
!****************************************************************************
!Procedure to Split data in multiple file if no:of rows exceeds 50000
!****************************************************************************
!BEGIN-PROCEDURE Split-File
!Let #Appl_Cnt=1
!Do Close-Output-File
!Do Open-Output-File
!END-PROCEDURE
!****************************************************************************
!Open-Output-File
!****************************************************************************
BEGIN-PROCEDURE Open-Output-File
Let $Curr_Dt = datetostr(datenow(),'DD-MM-YYYY_HH-MI-SS-NNNNN')
Let $FileName = 'EMS Report_'||$Curr_Dt ||'.xls'
Begin-select
MAX(PRCSINSTANCE) &EE_name
from PS_PMN_PRCSLIST
where PRCSNAME= 'ZINEMS'
End-select
Begin-Select
PRCSOUTPUTDIR &Out_Dir
from PSPRCSPARMS
WHERE PRCSINSTANCE=&EE_name
End-Select
If $Set_I = 'TALIC'
Let $Company='601'
Else
Let $Company='602'
End-If
Show 'HOST NAME ' $sqr-hostname
Let $HOSTNAME=' '
Let $HOSTNAME=$sqr-hostname
If $Company='601'
Let $outfile_name1='\\'||$HOSTNAME||'\TALIC\'||$FileName
End-If
If $Company='602'
Let $outfile_name1='\\'||$HOSTNAME||'\TAGIC\'||$FileName
End-If
Let $outfile_name1 = rtrim((&Out_Dir),' ')||'/'|| $FileName
OPEN $outfile_name1 AS 1 FOR-WRITING RECORD=3000:VARY STATUS = #file_status
Show 'FilePath' $outfile_name1
Show 'Outdir' &Out_Dir
IF #file_status = 0
SHOW $outfile_name1 ' opened successfully for writing'
ELSE
SHOW 'Error while opening output file: ' $outfile_name1
STOP QUIET
END-IF
END-PROCEDURE
!****************************************************************************
!Procedure to Close-Output-File
!****************************************************************************
BEGIN-PROCEDURE Close-Output-File
CLOSE 1
END-PROCEDURE
!****************************************************************************
!This Procedure writes header in excel file.
!****************************************************************************
BEGIN-PROCEDURE Write-Header
ENCODE '<009>' INTO $tab
Let $Heading = $tab || $tab || $tab || $tab || $tab || $tab || $tab ||'EMS Report'
Let $Header='Sl. No.' || $tab || 'Employee ID' || $tab || 'Employee Name' || $tab || 'Designation'
|| $tab || 'Zone' || $tab || 'Location' || $tab || 'Branch' || $tab || 'Grade'
|| $tab || 'Job Function' || $tab || 'Sub Function' || $tab || 'DOJ'|| $tab || 'Date of Resignation Submission'
|| $tab || 'Last Working Date' || $tab || 'Duration (in days)' || $tab || 'Supervisor Id'
|| $tab || 'Supervisor name'
|| $tab || 'Notice period served'|| $tab ||'Reason for Exit'|| $tab || 'Regrettable/Non Regrettbale'
|| $tab || 'Name of Originator actioning'|| $tab || 'Date of Originator action'|| $tab || 'Name of Supervisor actioning'|| $tab || 'Date of Supervisor action' || $tab || 'Name of ZHR actioning' || $tab ||'Date of ZHR action'
|| $tab || 'Name of CHR actioning' || $tab ||'Date of CHR action'
|| $tab || 'Leave details (Closing VL balance)' || $tab ||'Comments of Employee' || $tab || 'Comments of Supervisor'
|| $tab || 'Comments of ZHR' || $tab || 'Comments of CHR' || $tab || 'Approval status'
|| $tab || 'Home Phone No'|| $tab || 'Mobile No' || $tab || 'Address' || $tab || 'Personal EMail id'
|| $tab || 'Finance Checklist'|| $tab || 'Max Action dt for Finance' || $tab || 'Status of Finance ' || $tab || 'Comments -Finance' || $tab||'IT checklist' || $tab ||'Max Action dt for IT'
|| $tab || 'Status of IT' || $tab ||'Comments - IT' || $tab ||'Facilities'
|| $tab || 'Max Action dt for Facilities'|| $tab ||'Status of Facilities'
|| $tab || 'Comments-Facilities' || $tab || 'T&D Checklist' || $tab || 'Max Action dt for T&D'
|| $tab || 'Status of T&D' || $tab ||'Comments -T&D' || $tab || 'HR checklist'
|| $tab || 'Max Action dt for HR' || $tab || 'Status of HR' || $tab || 'Comments -HR'
|| $tab || 'Checklist status (excluding HR)'
!WRITE 1 From ' '
WRITE 1 From $Heading
WRITE 1 From $Header
END-PROCEDURE
!****************************************************************************
!Procedure to Print-Exit-Data
!****************************************************************************
BEGIN-PROCEDURE Print-Exit-Data
Let $final_status =''
let $resignation_dt = datetostr(strtodate($resignation_dt1 ),'DD Month YYYY')
let $hiredt = datetostr(strtodate($hiredt),'DD Month YYYY')
let $lwd= datetostr(strtodate($lwd1),'DD Month YYYY')
let $emp_transdt= datetostr(strtodate($emp_transdt),'DD Month YYYY')
let $sup_transdt= datetostr(strtodate($sup_transdt),'DD Month YYYY')
let $zhr_transdt= datetostr(strtodate($zhr_transdt),'DD Month YYYY')
let $chr_transdt= datetostr(strtodate($chr_transdt),'DD Month YYYY')
let $action_date= datetostr(strtodate($action_date),'DD Month YYYY')
let $action_date1= datetostr(strtodate($action_date1),'DD Month YYYY')
let $action_date2= datetostr(strtodate($action_date2),'DD Month YYYY')
let $action_date3= datetostr(strtodate($action_date3),'DD Month YYYY')
let $action_date4= datetostr(strtodate($action_date4),'DD Month YYYY')
let $status_date= datetostr(strtodate($status_date),'DD Month YYYY')
let $status_date1= datetostr(strtodate($status_date1),'DD Month YYYY')
let $status_date2= datetostr(strtodate($status_date2),'DD Month YYYY')
let $status_date3= datetostr(strtodate($status_date3),'DD Month YYYY')
let $status_date4= datetostr(strtodate($status_date4),'DD Month YYYY')
let $upd= datetostr(strtodate($upd),'DD Month YYYY')
let $ZHR-transdt1= datetostr(strtodate($ZHR-transdt2),'DD Month YYYY')
show 'zhr transaction dt:' $ZHR-transdt1
SHOW 'status Date: ' $status_date
If $chk_status_fin = 'Pending' or $chk_status1_IT = 'Pending' or $chk_status2_Fac = 'Pending' or $chk_status3_TR='Pending'
Let $final_status = 'Pending'
Else
Let $final_status = 'Completed'
End-If
! checking For CHR Approval if the transaction is initiated by ZHR ****START****
! the emp_transdt is changed from $resignation_dt to $ZHR-transdt1 to capture the zhr action date
If ltrim(rtrim($Trans,' '), ' ') ='ZHR'
Let $enter = chr(13)||chr(10)
Let $emp_comments ='-'
Let $Supervisor_Name1='-'
Let $ZHR_Name=$Originator_Name
Let $zhr_transdt=$ZHR-transdt1
Let $sup_transdt='-'
Let $sup_comments='-'
!Let $Originator_Name='Initiated by ZHR'
Let $zhr_comments =replace($comments1, $enter, ' ')
Let $zhr_comments=replace($zhr_comments, $tab, ' ')
Let $chr_comments=replace($comments2, $enter, ' ')
Let $chr_comments=replace($chr_comments, $tab, ' ')
Let $emp_transdt =$ZHR-transdt1
End-If
! checking For CHR Approval if the transaction is initiated by ZHR ****END****
! To check whether there is any supervisor for the employee ****START****
If $supervisor='' or $supervisor=' '
Let $Supervisor_Name='No supervisor for this employee'
End-If
! To check whether there is any supervisor for the employee ****END****
!If there is no action date then the status will be Not initiated ****START****
If $action_date ='' or $action_date1 ='' or $action_date2 ='' or $action_date3 ='' or $action_date4 =''
Let $status_date =''
Let $status_date1 =''
Let $status_date2 =''
Let $status_date3 =''
Let $status_date4 =''
Let $final_status ='Not initiated'
Let $chk_status1_IT = 'Not initiated'
Let $chk_status2_Fac = 'Not initiated'
Let $chk_status3_TR ='Not initiated'
Let $chk_status4_HR ='Not initiated'
Let $chk_status_fin = 'Not initiated'
End-If
!This validation is make the max action date blank if the briefing status is Pending /* Joseph 15 Sep 09*/ ****START****
If ltrim(rtrim($chk_status1_IT,' '), ' ')='Pending'
Let $status_date1=''
End-If
If ltrim(rtrim($chk_status2_Fac,' '), ' ')='Pending'
Let $status_date2=''
End-If
If ltrim(rtrim($chk_status3_TR,' '), ' ')='Pending'
Let $status_date3 =''
End-if
If ltrim(rtrim($chk_status4_HR,' '), ' ')='Pending'
Let $status_date4=''
End-if
If ltrim(rtrim($chk_status_fin,' '), ' ')='Pending'
Let $status_date = ''
End-if
!This validation is make the max action date blank if the briefing status is Pending ****END****
!If there is no action date then the status will be Not initiated ****END****
! checking For Approval i.e If the ZHR or CHR has not approved then the status will be 'yet to approve' ****START****
If ltrim(rtrim($Trans,' '), ' ') <>'ESS'
!show 'enters'
If $zhr_emplid=''
Let $zhr_transdt=''
Let $ZHR_Name='Yet to Approve'
End-If
If $chr_emplid=''
Let $chr_transdt=''
Let $CHR_Name='Yet to Approve'
End-If
If $sup_emplid=''
Let $Supervisor_Name1 ='Yet to approve'
Let $sup_transdt =''
End-If
End-if
!checking for ESS
If ltrim(rtrim($Trans,' '), ' ') ='ESS'
!SHOW 'IN THE LOOP'
!show 'zhr emplid :' $zhr_emplid
If $zhr_emplid=''
Let $zhr_transdt=''
Let $ZHR_Name='Yet to Approve'
End-If
!show 'chr emplid :' $chr_emplid
If $chr_emplid=''
Let $chr_transdt=''
Let $CHR_Name='Yet to Approve'
End-If
!show 'sup id:' $sup_emplid
If $sup_emplid=''
Let $Supervisor_Name1 ='Yet to approve'
Let $sup_transdt =''
End-If
End-If
If ltrim(rtrim($Trans,' '), ' ') ='MSS'
Let $emp_comments ='-'
Let $Supervisor_Name1=$Originator_Name
Let $sup_transdt=$emp_transdt
End-If
If ltrim(rtrim($Trans,' '), ' ') ='MSS' AND $App_st='A'
Let $chr_status = 'Approved'
End-If
If ltrim(rtrim($Trans,' '), ' ') ='MSS' AND $App_st='D'
Let $chr_status = 'Denied'
End-If
If ltrim(rtrim($Trans,' '), ' ') ='MSS' AND ($App_st='U' or $App_st='P')
Let $chr_status = 'Pending'
End-If
If ltrim(rtrim($Trans,' '), ' ') ='ZHR' AND $App_st='A'
Let $chr_status ='Approved'
Let $chr_transdt = $upd
Let $CHR_Name = $CHR_Name
Let $Supervisor_Name1='-'
Let $sup_transdt='-'
Let $zhr_transdt = $ZHR-transdt1
Let $ZHR_Name= $Originator_Name
End-If
If ltrim(rtrim($Trans,' '), ' ') ='ZHR' AND $App_st='D'
Let $chr_status ='Denied'
Let $chr_transdt = $upd
Let $CHR_Name = $CHR_Name
Let $Supervisor_Name1='-'
Let $sup_transdt='-'
Let $zhr_transdt =$ZHR-transdt1
Let $ZHR_Name= $Originator_Name
End-If
If ltrim(rtrim($Trans,' '), ' ') ='ZHR' AND ($App_st='U' or $App_st='P')
Let $chr_status ='Pending'
Let $CHR_Name='Yet to approve'
Let $chr_transdt= ''
Let $Supervisor_Name1='-'
Let $sup_transdt='-'
Let $zhr_transdt =$ZHR-transdt1
Let $ZHR_Name= $Originator_Name
End-If
!If $App_st='A'
!Let $chr_status ='Approved'
!End-If
!If $App_st='D'
!Let $chr_status ='Denied'
!End-If
!If $App_st='U' or $App_st='P'
!Let $chr_status ='Pending'
!End-If
! checking For Approval i.e If the ZHR or CHR has not approved then the status will be 'yet to approve' ****END****
! To print the Approval Status ****START****
If ($sup_status='' or $zhr_status='' ) and ltrim(rtrim($Trans,' '), ' ') <>'ZHR' and ltrim(rtrim($Trans,' '), ' ') <>'MSS'
Let $chr_status='Pending'
End-If
If ($sup_status='' or $zhr_status='' ) and ltrim(rtrim($Trans,' '), ' ') ='MSS'
Let $chr_status='Pending'
End-If
If $App_st='A'
Let $chr_status ='Approved'
End-If
If $App_st='D'
Let $chr_status ='Denied'
End-If
If $App_st='U' or $App_st='P'
Let $chr_status ='Pending'
End-If
If $sup_status='DNY'
Let $zhr_transdt='-'
Let $ZHR_Name='Supervisor Denied'
Let $zhr_comments='-'
Let $chr_transdt='-'
Let $CHR_Name='Supervisor Denied'
Let $chr_comments='-'
End-If
If $zhr_status='DNY'
Let $chr_transdt='-'
Let $CHR_Name='ZHR Denied'
Let $chr_comments='-'
End-If
If $sup_status='DNY' or $zhr_status='DNY' or $chr_status='DNY'
Let $chr_status='Denied'
End-If
!If $chr_status='DNY'
!Let $chr_status='Denied'
!End-If
If $chr_status='APV'
Let $chr_status='Approved'
End-If
!To print the Approval Status ****END****
!To print action reason i.e regrettable or non-regrettable ****START****
Evaluate $action_reason
When ='ABS'
When ='CON'
When ='IAR'
When ='IER'
When ='IOR'
When ='IPR'
When ='IRE'
When ='ISU'
When ='ITE'
When ='JAL'
Let $regret = 'Non-Regrettable'
Break
When ='CHA'
When ='JFB'
When ='VBE'
When ='VBP'
When ='VBS'
When ='VCC'
When ='VDP'
When ='VEM'
When ='VFE'
When ='VHR'
When ='VIP'
When ='VJM'
When ='VJJ'
When ='VJS'
When ='VNB'
When ='VOR'
When ='VPC'
When ='VPR'
Let $regret= 'Regrettable'
Break
When ='OCE'
When ='OPD'
When ='ODE'
When ='ORE'
When ='OTR'
When ='REL'
When ='TAF'
Let $regret = 'Others'
End-Evaluate
!To print action reason i.e regrettable or non-regrettable ****END****
!let $resignation_dt_p = strtodate($resignation_dt,'DD/MM/YYYY')
!let $resignation_dt_p = edit($resignation_dt,'DD/MM/YYYY')
! let $resignation_dt_p = edit($resignation_dt_p,'MON')
Let $Appl_Cnt = edit(#Appl_Cnt,'9999')
Let $Str = $Appl_Cnt || $tab || $Emp_ID || $tab || $Name || $tab || $designation || $tab || $Zone_descr
|| $tab || $Loc_descr || $tab || $branch_descr || $tab || $grade || $tab ||$function_descr
|| $tab || $sub_function_descr|| $tab || $hiredt || $tab || $resignation_dt ||$tab || $lwd || $tab || $duration || $tab ||$supervisor
|| $tab || $Supervisor_Name || $tab || $notice_descr || $tab || $action
|| $tab || $regret || $tab || $Originator_Name || $tab || $emp_transdt || $tab || $Supervisor_Name1 || $tab || $sup_transdt || $tab || $ZHR_Name || $tab || $zhr_transdt || $tab || $CHR_Name || $tab || $chr_transdt
|| $tab ||''|| $tab ||$emp_comments|| $tab || $sup_comments || $tab || $zhr_comments
|| $tab || $chr_comments || $tab || $chr_status
|| $tab || $home || $tab || $mobile || $tab || $ADDRESS || $tab || $mail || $tab || $checklist_cd
|| $tab || $status_date || $tab || $chk_status_fin || $tab || $comments_fin || $tab || $checklist_cd1
|| $tab || $status_date1 || $tab || $chk_status1_IT || $tab || $comments_IT || $tab || $checklist_cd2
|| $tab || $status_date2 || $tab || $chk_status2_Fac || $tab || $comments_Fac || $tab || $checklist_cd3
|| $tab || $status_date3 || $tab || $chk_status3_TR || $tab || $comments_TR || $tab || $checklist_cd4
|| $tab || $status_date4 || $tab || $chk_status4_HR || $tab || $comments_HR || $tab || $final_status
WRITE 1 From $Str
let #Appl_Cnt = #Appl_Cnt + 1
END-PROCEDURE
!****************************************************************************
!Procedure to Get-Exit-Employee-Data
!****************************************************************************
BEGIN-PROCEDURE Get-Exit-Employee-Data
BEGIN-SELECT
JB.BUSINESS_UNIT
let $opr_bunit = &JB.BUSINESS_UNIT
FROM PS_JOB JB ,
PSOPRDEFN OP
WHERE OP.OPRID = $PRCS_OPRID
AND JB.EMPLID = OP.EMPLID
AND JB.EFFDT = (SELECT MAX(JB_ED.EFFDT) FROM PS_JOB JB_ED
WHERE JB.EMPLID = JB_ED.EMPLID
AND JB.EMPL_RCD = JB_ED.EMPL_RCD
AND JB_ED.EFFDT <= GETDATE())
AND JB.EFFSEQ = (SELECT MAX(JB_ES.EFFSEQ) FROM PS_JOB JB_ES
WHERE JB.EMPLID = JB_ES.EMPLID
AND JB.EMPL_RCD = JB_ES.EMPL_RCD
AND JB.EFFDT = JB_ES.EFFDT)
END-SELECT
BEGIN-SELECT
A.EMPLID
A.APPROVAL_STATUS
A.ZIN_TRANS
A.ORIGINATORID
A.ZIN_EMS_COMMENTS1
A.ZIN_EMS_COMMENTS2
A.UPD_DTTM
A.UPD_BY_USERID
B.ZRG_POSITION_TITLE
E.ZIN_ZONE
E.ZIN_SUB_ZONE
E.ESTABID
E.ZIN_BRANCH
C.GRADE
C.DEPTID
C.BUSINESS_UNIT
C.SUPERVISOR_ID
E.ZIN_FUNC_CODE
E.ZIN_SUB_FUNC
D.HIRE_DT
A.ACTION_DT_SS
A.ZIN_SUBMN_DT
A.ACTION
A.ACTION_REASON_SS
A.ZIN_NOTICE_PERIOD
DATEDIFF(DAY,D.HIRE_DT,A.ACTION_DT_SS )+1 &duration
let $Emp_Id = &A.EMPLID
let $App_st = &A.APPROVAL_STATUS
let $Trans = &A.ZIN_TRANS
let $Originate = &A.ORIGINATORID
let $comments1= ltrim(rtrim(&A.ZIN_EMS_COMMENTS1, ' '), ' ')
let $comments2=ltrim(rtrim(&A.ZIN_EMS_COMMENTS2, ' '), ' ')
let $upd=&A.UPD_DTTM
let $upd_user=&A.UPD_BY_USERID
let $setid = &C.BUSINESS_UNIT
let $designation = &B.ZRG_POSITION_TITLE
let $zone = &E.ZIN_ZONE
let $Sub_zone= &E.ZIN_SUB_ZONE
let $location = &E.ESTABID
let $branch= &E.ZIN_BRANCH
let $grade= &C.GRADE
let $deptid = &C.DEPTID
let $function =edit(&E.ZIN_FUNC_CODE,'99')
let $function=lpad(ltrim($function,' '),2,'0')
let $sub_function=&E.ZIN_SUB_FUNC
let $hiredt=&D.HIRE_DT
let $lwd=datetostr(&A.ACTION_DT_SS,'DD/MM/YYYY')
let $lwd1=&A.ACTION_DT_SS
let $duration= &duration
let $supervisor=&C.SUPERVISOR_ID
let $resignation_dt=datetostr(&A.ZIN_SUBMN_DT,'DD/MM/YYYY')
let $resignation_dt1=&A.ZIN_SUBMN_DT
let $notice_period=&A.ZIN_NOTICE_PERIOD
let $action_reason=&A.ACTION_REASON_SS
Do Get-Names
!Do Get-Designation
Do Get-Zone-Descr
Do Get-Sub-Zone-Descr
Do Get-Location-Descr ! To get Location details
Do Get-Branch-Descr
Do Get-Grade !To get Grade details
Do Get-Dept-Descr !To get department details
Do Get-Job-Function !To get jod function details
Do Get-Job-Sub-Function
Do Get-supervisor-id
Do Get-supervisor-names
Do Get-Notice-period !To get notice period details
Do Get-action-reason !To get action reason details
Do Get-ZHR-EMPLID
Do Get-ZHR-name
Do Get-CHR-EMPLID
Do Get-CHR-name
Do Get-supervisor-details
Do Get-supervisor-action-names
DO Get-employee-details
DO Get-Originator-action-names
Do Get-employee-home-ph
Do Get-employee-mob
Do Get-employee-address
Do Get-employee-mail
if $chk_st_I <> '' AND $chk_st_I <> ' '
LET $Chk_fg = 'N'
DO Get-Req-Chklst !To get the Chklist details for the Given input
SHOW '$Chk_fg :' $Chk_fg '$chk_st_I :' $chk_st_I
if $Chk_fg = 'Y'
Do Get-finance-checklist
Do Get-IT-checklist
Do Get-Facilities
Do Get-TR-D !To get Training & development chcklist details
DO Get-HR-OP ! To get HR Operation
end-if
else
Do Get-finance-checklist
Do Get-IT-checklist
Do Get-Facilities
Do Get-TR-D !To get Training & development chcklist details
DO Get-HR-OP ! To get HR Operation
end-if
if $chk_st_I <> '' AND $chk_st_I <> ' '
if $Chk_fg = 'Y'
Do Print-Exit-Data
end-if
else
Do Print-Exit-Data
end-if
Do Initialize-Appl-Variables
FROM PS_HR_TERM_DAT A,
PS_ZRG_JOB B,
PS_JOB C,
PS_EMPLMT_SRCH_QRY C1,
PS_EMPLOYMENT D,
PS_ZIN_JOB E
WHERE C.EMPLID = C1.EMPLID
AND C.EMPL_RCD = C1.EMPL_RCD
AND C1.ROWSECCLASS = $Rowsecclass
AND D.EMPLID = C1.EMPLID
AND D.EMPL_RCD = C1.EMPL_RCD
AND ( A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
AND A.UPD_DTTM = (SELECT MAX(A_ES.UPD_DTTM) FROM PS_HR_TERM_DAT A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD )
AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ZRG_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ZRG_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND A.EMPLID = C.EMPLID
AND A.EMPL_RCD = C.EMPL_RCD
AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_JOB C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND C.EFFSEQ = (SELECT MAX(C_ES.EFFSEQ) FROM PS_JOB C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.EMPL_RCD = C_ES.EMPL_RCD
AND C.EFFDT = C_ES.EFFDT)
AND A.EMPLID = D.EMPLID
AND A.EMPL_RCD = D.EMPL_RCD
AND A.EMPLID = E.EMPLID
AND A.EMPL_RCD = E.EMPL_RCD
AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ZIN_JOB E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_JOB E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.EFFDT = E_ES.EFFDT))
AND C.BUSINESS_UNIT = $opr_bunit
[$where]
END-SELECT
END-PROCEDURE
!***********************************************************************
!BEGIN-PROCEDURE Generate_Where_Condition
!***********************************************************************
BEGIN-Procedure Generate_Where_Condition
Let $Where =''
If not isnull($Zone_I)
Let $Where = $Where || ' AND E.ZIN_ZONE = ' || '''' || $Zone_I || ''''
SHOW '$Where ZONE: '$Where
End-If
If not isnull($Frm_Dt_I)
Let $Where = $Where || ' AND A.ZIN_SUBMN_DT >= ' || '''' || $Frm_Dt_I || ''''
End-If
If not isnull($To_Dt_I)
Let $Where = $Where || ' AND A.ZIN_SUBMN_DT <= ' || '''' || $To_Dt_I || ''''
End-If
If not isnull($Sz_I)
Let $Where = $Where || ' AND E.ZIN_SUB_ZONE = ' || '''' || $Sz_I || ''''
SHOW '$Where SZ :'$Where
End-If
If not isnull($Grade_I)
Let $Where = $Where || ' AND C.GRADE = ' || '''' || $Grade_I || ''''
SHOW '$Where GRADE'$Where
End-If
If not isnull($Supervisor_I)
Let $Where = $Where || ' AND C.SUPERVISOR_ID = ' || '''' || $Supervisor_I || ''''
SHOW '$Where SUPERVISOR'$Where
End-If
If not isnull($Emp_I)
Let $Where = $Where || ' AND A.EMPLID = ' || '''' || $Emp_I || ''''
SHOW '$Where EMPLID'$Where
End-If
If not isnull($App_st_I)
If $App_st_I='U'
Let $Where = $Where || ' AND A.APPROVAL_STATUS IN (' || '''' || 'U'||''''||','||''''||'P'||''''||')'
Else
Let $Where = $Where || ' AND A.APPROVAL_STATUS = ' || '''' || $App_st_I || ''''
End-If
SHOW '$Where Approval_Staus'$Where
End-If
If #Fn_I <> 0
LET $Fn_I = TO_CHAR(#Fn_I)
Let $Fn_I = lpad(ltrim($Fn_I,' '),2,'0')
Let $Where = $Where || ' AND E.ZIN_FUNC_CODE = ' || '''' || $Fn_I || ''''
SHOW '$Where FUNCTION'$Where
End-If
END-PROCEDURE
!**********************************************************************************
!Begin-Procedure Get-Names
!Get the names of employee
!**********************************************************************************
Begin-Procedure Get-Names
Let $Name=''
Begin-Select
N.FIRST_NAME
N.LAST_NAME
Let $Name = ltrim(rtrim(&N.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&N.LAST_NAME, ' '), ' ')
FROM PS_NAMES N
WHERE N.EMPLID = $Emp_Id
AND N.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = N.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-supervisor-names
!**********************************************************************************
Begin-Procedure Get-supervisor-names
Let $Supervisor_Name =''
Begin-Select
N1.FIRST_NAME
N1.LAST_NAME
Let $Supervisor_Name = ltrim(rtrim(&N1.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&N1.LAST_NAME, ' '), ' ')
FROM PS_NAMES N1
WHERE N1.EMPLID =$supervisor
AND N1.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = N1.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Originator-action-names
!To get name of the originator who is actioning
!**********************************************************************************
Begin-Procedure Get-Originator-action-names
Let $Originator_Name=''
Begin-Select
ORIG.FIRST_NAME
ORIG.LAST_NAME
Let $Originator_Name = ltrim(rtrim(&ORIG.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&ORIG.LAST_NAME, ' '), ' ')
FROM PS_NAMES ORIG
WHERE ORIG.EMPLID =$orig_emplid
AND ORIG.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = ORIG.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-supervisor-action-names
!To get name of the supervisor who is actioning
!**********************************************************************************
Begin-Procedure Get-supervisor-action-names
Let $Supervisor_Name1=''
Begin-Select
N11.FIRST_NAME
N11.LAST_NAME
Let $Supervisor_Name1 = ltrim(rtrim(&N11.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&N11.LAST_NAME, ' '), ' ')
FROM PS_NAMES N11
WHERE N11.EMPLID =$sup_emplid
AND N11.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = N11.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-ZHR-name
!To get the name of the Zhr actioning
!**********************************************************************************
Begin-Procedure Get-ZHR-name
Let $ZHR_Name=''
Begin-Select
N2.FIRST_NAME
N2.LAST_NAME
Let $ZHR_Name = ltrim(rtrim(&N2.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&N2.LAST_NAME, ' '), ' ')
FROM PS_NAMES N2
WHERE N2.EMPLID =$zhr_emplid
AND N2.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = N2.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-CHR-name
!To get the name of the CHR actioning
!**********************************************************************************
Begin-Procedure Get-CHR-name
Let $CHR_Name=''
Begin-Select
N3.FIRST_NAME
N3.LAST_NAME
Let $CHR_Name = ltrim(rtrim(&N3.FIRST_NAME, ' '), ' ') || ' ' || ltrim(rtrim(&N3.LAST_NAME, ' '), ' ')
FROM PS_NAMES N3
WHERE N3.EMPLID =$chr_emplid
AND N3.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_NAMES E_ED
WHERE E_ED.EMPLID = N3.EMPLID
AND E_ED.NAME_TYPE ='PRI'
AND E_ED.EFFDT <= GETDATE())
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Designation
!To get the designation details of the employee
!**********************************************************************************
Begin-Procedure Get-Designation
Let $designation=''
Begin-Select
DESIGNATION.ZRG_POSITION_TITLE
Let $designation = ltrim(rtrim(&DESIGNATION.ZRG_POSITION_TITLE, ' '), ' ')
FROM PS_ZRG_JOB DESIGNATION
WHERE DESIGNATION.EMPLID = $Emp_Id
AND DESIGNATION.EFFDT = (SELECT MAX(ZRG_ED.EFFDT) FROM PS_ZRG_JOB ZRG_ED
WHERE DESIGNATION.EMPLID = ZRG_ED.EMPLID
AND DESIGNATION.EMPL_RCD = ZRG_ED.EMPL_RCD
AND DESIGNATIONEFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND DESIGNATION.EFFSEQ = (SELECT MAX(ZRG_ES.EFFSEQ) FROM PS_ZIN_JOB ZRG_ES
WHERE DESIGNATION.EMPLID = ZRG_ES.EMPLID
AND DESIGNATION.EMPL_RCD = ZRG_ES.EMPL_RCD
AND DESIGNATION.EFFDT = ZRG_ES.EFFDT)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Zone-Descr
!To get the zone description
!**********************************************************************************
Begin-Procedure Get-Zone-Descr
Let $Zone_descr = ''
Begin-Select
ZONE.DESCR
Let $Zone_descr = ltrim(rtrim(&ZONE.DESCR, ' '), ' ')
FROM PS_ZIN_ZONE_TBL ZONE
WHERE ZONE.SETID =$setid
AND ZONE.ZIN_ZONE = $zone
AND ZONE.EFF_STATUS = 'A'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Sub-Zone-Descr
!To get the sub zone description
!**********************************************************************************
Begin-Procedure Get-Sub-Zone-Descr
Let $SubZone_descr=''
Begin-Select
SUBZONE.DESCR
Let $SubZone_descr = ltrim(rtrim(&SUBZONE.DESCR, ' '), ' ')
FROM PS_ZIN_ZN_SZ_VW SUBZONE
WHERE SUBZONE.SETID =$setid
AND SUBZONE.ZIN_ZONE = $zone
AND SUBZONE.ZIN_SUB_ZONE =$Sub_zone
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Location-Descr
!To Get the subzone description
!**********************************************************************************
Begin-Procedure Get-Location-Descr
Let $Loc_descr=''
Begin-Select
LOC.DESCR
Let $Loc_descr = ltrim(rtrim(&LOC.DESCR, ' '), ' ')
FROM PS_ZIN_SZ_LOC_VW LOC
WHERE LOC.SETID =$setid
AND LOC.ZIN_SUB_ZONE = $Sub_zone
AND LOC.ESTABID = $location
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Branch-Descr
!To get the branch description
!**********************************************************************************
Begin-Procedure Get-Branch-Descr
Let $branch_descr=''
Begin-Select
BR.DESCR
Let $branch_descr = ltrim(rtrim(&BR.DESCR, ' '), ' ')
FROM PS_ZIN_SZ_BR_VW BR
WHERE BR.SETID =$setid
AND BR.ZIN_BRANCH =$branch
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Grade
!To get the grade of the employee
!**********************************************************************************
Begin-Procedure Get-Grade
Let $grade1=''
Begin-Select
GR.GRADE
Let $grade1 = ltrim(rtrim(&GR.GRADE, ' '), ' ')
FROM PS_JOB GR
WHERE GR.EMPLID = $Emp_Id
AND GR.EFFDT = (SELECT MAX(GRD_ED.EFFDT) FROM PS_JOB GRD_ED
WHERE GR.EMPLID = GRD_ED.EMPLID
AND GR.EMPL_RCD = GRD_ED.EMPL_RCD
AND GR.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND GR.EFFSEQ = (SELECT MAX(GRD_ES.EFFSEQ) FROM PS_JOB GRD_ES
WHERE GR.EMPLID = GRD_ES.EMPLID
AND GR.EMPL_RCD = GRD_ES.EMPL_RCD
AND GR.EFFDT = GRD_ES.EFFDT)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Dept-Descr
!To get the department description
!**********************************************************************************
Begin-Procedure Get-Dept-Descr
Let $Dept_Descr=''
Begin-Select
DEPT.DESCR
Let $Dept_Descr = ltrim(rtrim(&DEPT.DESCR, ' '), ' ')
FROM PS_DEPT_TBL DEPT
WHERE DEPT.DEPTID = $deptid
AND DEPT.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED
WHERE E_ED.DEPTID = DEPT.DEPTID
AND E_ED.SETID = DEPT.SETID
AND E_ED.EFFDT <= GETDATE())
AND DEPT.EFF_STATUS = 'A'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Job-Function
!TO get the Job Function Description
!**********************************************************************************
Begin-Procedure Get-Job-Function
Let $function_descr=''
Begin-Select
FN.DESCR
Let $function_descr= ltrim(rtrim(&FN.DESCR, ' '), ' ')
FROM PS_ZIN_FN_QRY_VW FN
WHERE FN.SETID =$setid
AND FN.ZIN_FUNC_CODE =$function
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Job-Sub-Function
!TO get the job sub function description
!**********************************************************************************
Begin-Procedure Get-Job-Sub-Function
Let $sub_function_descr=''
Begin-Select
SUB_FN.DESCR
Let $sub_function_descr= ltrim(rtrim(&SUB_FN.DESCR, ' '), ' ')
FROM PS_ZIN_FN_SF_VW SUB_FN
WHERE SUB_FN.SETID =$setid
AND SUB_FN.ZIN_FUNC_CODE =$function
AND SUB_FN.DEPTID=$deptid
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-supervisor-id
!**********************************************************************************
Begin-Procedure Get-supervisor-id
Let $supervisor_id=''
Begin-Select
J.SUPERVISOR_ID
Let $supervisor_id= ltrim(rtrim(&J.SUPERVISOR_ID, ' '), ' ')
FROM PS_JOB J
WHERE J.EMPLID =$Emp_Id
AND J.EFFDT = (SELECT MAX(SUP_ED.EFFDT) FROM PS_JOB SUP_ED
WHERE J.EMPLID = SUP_ED.EMPLID
AND J.EMPL_RCD =SUP_ED.EMPL_RCD
AND J.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND J.EFFSEQ = (SELECT MAX(SUP_ES.EFFSEQ) FROM PS_JOB SUP_ES
WHERE J.EMPLID = SUP_ES.EMPLID
AND J.EMPL_RCD = SUP_ES.EMPL_RCD
AND J.EFFDT = SUP_ES.EFFDT)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-action-reason
!To Get the action reason for termination of the employee
!**********************************************************************************
Begin-Procedure Get-action-reason
Let $action=''
Begin-Select
R.DESCR
Let $action= ltrim(rtrim(&R.DESCR, ' '), ' ')
FROM PS_ACTN_REASON_TBL R
WHERE R.ACTION_REASON =$action_reason
AND R.EFFDT = (SELECT MAX(R_ED.EFFDT) FROM PS_ACTN_REASON_TBL R_ED
WHERE R.ACTION = R_ED.ACTION
AND R.ACTION_REASON =R_ED.ACTION_REASON
AND R.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Notice-period
!To get the notice period details
!**********************************************************************************
Begin-Procedure Get-Notice-period
Let $notice_descr=''
Begin-Select
N.XLATLONGNAME
Let $notice_descr= ltrim(rtrim(&N.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM N
WHERE N.FIELDNAME = 'ZIN_NOTICE_PERIOD'
AND N.FIELDVALUE = $notice_period
AND N.EFF_STATUS = 'A'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-employee-details
!To get the comments of the employee
!**********************************************************************************
Begin-Procedure Get-employee-details
Let $orig_emplid= ''
Let $emp_comments= ''
Let $emp_transdt= ''
Let $enter = ''
Begin-Select
E.EMPLID_TO
E.ZIN_EMS_COMMENTS2
E.TRANS_DT
Let $orig_emplid= ltrim(rtrim(&E.EMPLID_TO, ' '), ' ')
Let $emp_comments= ltrim(rtrim(&E.ZIN_EMS_COMMENTS2, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $emp_comments = replace($emp_comments, $enter, ' ')
Let $emp_comments = replace($emp_comments, $tab, ' ')
Let $emp_transdt= &E.TRANS_DT
FROM PS_ZIN_TERM_STA E
WHERE E.EMPLID=$Emp_Id
AND E.ZIN_SUBMN_DT=$resignation_dt1
AND E.ACTION_DT_SS=$lwd1
AND E.ROLENAME LIKE 'Originator%'
AND E.HR_WF_ACTION='SUB'
AND E.EFFSEQ=(SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_TERM_STA E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.ZIN_SUBMN_DT= E_ES.ZIN_SUBMN_DT
AND E.EFFSEQ = E_ES.EFFSEQ)
End-Select
If $orig_emplid= ''
Let $orig_emplid=$Originate
Let $chr_emplid=$upd_user
Do Get-CHR-name
Do Get-ZHR-transdt
End-If
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-supervisor-details
!To get the supervisor emplid,transaction date,approval action and comments
!**********************************************************************************
Begin-Procedure Get-supervisor-details
Let $sup_emplid=''
Let $sup_transdt=''
Let $sup_status=''
Let $sup_comments=''
Let $enter=''
!show '$Emp_Id ' $Emp_Id
!show '$resignation_dt1 ' $resignation_dt1
!show '$lwd1 ' $lwd1
Begin-Select
S.EMPLID_TO
S.TRANS_DT
S.HR_WF_ACTION
S.ZIN_EMS_COMMENTS2
Let $sup_emplid= ltrim(rtrim(&S.EMPLID_TO, ' '), ' ')
Let $sup_transdt= &S.TRANS_DT
Let $sup_status= ltrim(rtrim(&S.HR_WF_ACTION, ' '), ' ')
Let $sup_comments= ltrim(rtrim(&S.ZIN_EMS_COMMENTS2, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $sup_comments = replace($sup_comments, $enter, ' ')
Let $sup_comments = replace($sup_comments, $tab, ' ')
!show 'sup in proc:' $sup_emplid
FROM PS_ZIN_TERM_STA S
WHERE S.EMPLID=$Emp_Id
AND S.ZIN_SUBMN_DT=$resignation_dt1
AND S. ACTION_DT_SS=$lwd1
AND S.ROLENAME LIKE 'TALMSS-TEST%'
AND (S.HR_WF_ACTION='APV'OR S.HR_WF_ACTION ='DNY' OR S.HR_WF_ACTION='-')
AND S.EFFSEQ=(SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_TERM_STA E_ES
WHERE S.EMPLID = E_ES.EMPLID
AND S.EMPL_RCD = E_ES.EMPL_RCD
AND S.ZIN_SUBMN_DT= E_ES.ZIN_SUBMN_DT
AND S.EFFSEQ = E_ES.EFFSEQ)
End-Select
If ltrim(rtrim($Trans,' '), ' ') ='MSS'
Do Get-Sup-initiation
End-if
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Sup-initiation
!To get the supervisor comments when th supervisor initiates
!**********************************************************************************
Begin-Procedure Get-Sup-initiation
Let $sup_comments=''
Let $emp_comments='-'
Let $enter=''
Begin-Select
S1.ZIN_EMS_COMMENTS2
Let $sup_comments= ltrim(rtrim(&S1.ZIN_EMS_COMMENTS2, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $sup_comments = replace($sup_comments, $enter, ' ')
Let $sup_comments = replace($sup_comments, $tab, ' ')
FROM PS_ZIN_TERM_STA S1
WHERE S1.EMPLID=$Emp_Id
AND S1.ZIN_SUBMN_DT=$resignation_dt1
AND S1. ACTION_DT_SS=$lwd1
AND S1.ROLENAME LIKE 'Originator%'
AND (S1.HR_WF_ACTION='SUB')
AND S1.EFFSEQ=(SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_TERM_STA E_ES
WHERE S1.EMPLID = E_ES.EMPLID
AND S1.EMPL_RCD = E_ES.EMPL_RCD
AND S1.ZIN_SUBMN_DT= E_ES.ZIN_SUBMN_DT
AND S1.EFFSEQ = E_ES.EFFSEQ)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-ZHR-EMPLID
!TO get ZHR emplid,transaction date,approval action and comments
!**********************************************************************************
Begin-Procedure Get-ZHR-EMPLID
Let $zhr_emplid= ''
Let $zhr_transdt= ''
Let $zhr_status= ''
Let $zhr_comments= ''
Let $enter=''
Begin-Select
Z.EMPLID_TO
Z.TRANS_DT
Z.HR_WF_ACTION
Z.ZIN_EMS_COMMENTS2
Let $zhr_emplid= ltrim(rtrim(&Z.EMPLID_TO, ' '), ' ')
Let $zhr_transdt= &Z.TRANS_DT
Let $zhr_status= ltrim(rtrim(&Z.HR_WF_ACTION, ' '), ' ')
Let $zhr_comments= ltrim(rtrim(&Z.ZIN_EMS_COMMENTS2, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $zhr_comments = replace($zhr_comments, $enter, ' ')
Let $zhr_comments = replace($zhr_comments, $tab, ' ')
FROM PS_ZIN_TERM_STA Z
WHERE Z.EMPLID=$Emp_Id
AND Z.ZIN_SUBMN_DT=$resignation_dt1
AND Z. ACTION_DT_SS=$lwd1
AND Z.ROLENAME LIKE 'ZIN_ZONAL_MGR_HR%'
AND (Z.HR_WF_ACTION='APV'OR Z.HR_WF_ACTION ='DNY')
AND Z.EFFSEQ=(SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_TERM_STA E_ES
WHERE Z.EMPLID = E_ES.EMPLID
AND Z.EMPL_RCD = E_ES.EMPL_RCD
AND Z.ZIN_SUBMN_DT= E_ES.ZIN_SUBMN_DT
AND Z.EFFSEQ = E_ES.EFFSEQ)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-ZHR-transdt
!TO get ZHR transaction date
!included for getting the transaction date if ZHR initiates 9/9/2009 --> Joseph
!to get the originator action date on ZHR initiation 10/9/2009--> Joseph
!**********************************************************************************
Begin-Procedure Get-ZHR-transdt
Let $instance= ''
Let $ZHR-transdt2= ''
Let #instance='0'
Begin-Select
Z1.INSTANCEID
Let #instance= &Z1.INSTANCEID
Let $instance = edit(#instance,'9999')
FROM PS_ZIN_WF_TERM_WL Z1
WHERE Z1.EMPLID =$Emp_Id
AND Z1.BUSPROCNAME = 'HR_SS_WORK_EVENTS'
AND Z1.ACTIVITYNAME = 'HR_EE_TERMINATE'
AND Z1.EVENTNAME = 'CorporateHR Approval'
AND Z1.WORKLISTNAME = 'ZHR EMS CHR Approval'
End-Select
show 'instance:' $instance
show 'emplid:' $Emp_Id
Begin-Select
Z2.INSTAVAILABLEDTTM
Let $ZHR-transdt2= &Z2.INSTAVAILABLEDTTM
FROM PSWORKLIST Z2
WHERE Z2.BUSPROCNAME = 'HR_SS_WORK_EVENTS'
AND Z2.ACTIVITYNAME = 'HR_EE_TERMINATE'
AND Z2.EVENTNAME = 'CorporateHR Approval'
AND Z2.WORKLISTNAME = 'ZHR EMS CHR Approval'
AND Z2.INSTANCEID = $instance
AND Z2.ORIGINATORID = $orig_emplid
End-Select
!show 'zhr transdt:'$ZHR-transdt2
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-CHR-EMPLID
!To get CHR emplid,transaction date,approval action and comments
!**********************************************************************************
Begin-Procedure Get-CHR-EMPLID
Let $chr_emplid=''
Let $chr_transdt= ''
Let $chr_status= ''
Let $chr_comments= ''
Let $enter=''
Begin-Select
CH.EMPLID_TO
CH.TRANS_DT
CH.HR_WF_ACTION
CH.ZIN_EMS_COMMENTS2
Let $chr_emplid= ltrim(rtrim(&CH.EMPLID_TO, ' '), ' ')
Let $chr_transdt= &CH.TRANS_DT
Let $chr_status= ltrim(rtrim(&CH.HR_WF_ACTION, ' '), ' ')
Let $chr_comments= ltrim(rtrim(&CH.ZIN_EMS_COMMENTS2, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $chr_comments = replace($chr_comments, $enter, ' ')
Let $chr_comments = replace($chr_comments, $tab, ' ')
FROM PS_ZIN_TERM_STA CH
WHERE CH.EMPLID=$Emp_Id
AND CH.ZIN_SUBMN_DT=$resignation_dt1
AND CH. ACTION_DT_SS=$lwd1
AND CH.ROLENAME LIKE 'Corporate HR Exec%'
AND (CH.HR_WF_ACTION='APV'OR CH.HR_WF_ACTION ='DNY')
AND CH.EFFSEQ=(SELECT MAX(E_ES.EFFSEQ) FROM PS_ZIN_TERM_STA E_ES
WHERE CH.EMPLID = E_ES.EMPLID
AND CH.EMPL_RCD = E_ES.EMPL_RCD
AND CH.ZIN_SUBMN_DT= E_ES.ZIN_SUBMN_DT
AND CH.EFFSEQ = E_ES.EFFSEQ)
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-employee-home-ph
!To Employee phone number
!**********************************************************************************
Begin-Procedure Get-employee-home-ph
Let $home =''
Begin-Select
P.PHONE
Let $home = ltrim(rtrim(&P.PHONE, ' '), ' ')
FROM PS_PERSONAL_PHONE P
WHERE P.EMPLID=$Emp_Id
AND P.PHONE_TYPE='HOME'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-employee-mob
!To get emloyee mobile nunber
!**********************************************************************************
Begin-Procedure Get-employee-mob
Let $mobile = ''
Begin-Select
P1.PHONE
Let $mobile = ltrim(rtrim(&P1.PHONE, ' '), ' ')
FROM PS_PERSONAL_PHONE P1
WHERE P1.EMPLID=$Emp_Id
AND P1.PHONE_TYPE='MOBI'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-employee-address
!To get employee address
!**********************************************************************************
Begin-Procedure Get-employee-address
Let $ADDRESS =''
Begin-Select
A1.ADDRESS1
A1.ADDRESS2
A1.ADDRESS3
A1.ADDRESS4
A1.CITY
A1.STATE
A1.POSTAL
Let $ADDRESS = ltrim(rtrim(&A1.ADDRESS1, ' '), ' ') || ' ' || ltrim(rtrim(&A1.ADDRESS2, ' '), ' ') || ltrim(rtrim(&A1.ADDRESS3, ' '), ' ') || ' ' || ltrim(rtrim(&A1.ADDRESS4, ' '), ' ')|| ltrim(rtrim(&A1.CITY, ' '), ' ') || ' ' || ltrim(rtrim(&A1.STATE, ' '), ' ') || ' ' ||ltrim(rtrim(&A1.POSTAL, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $ADDRESS= replace($ADDRESS, $enter, ' ')
Let $ADDRESS = replace($ADDRESS, $tab, ' ')
FROM PS_ADDRESSES A1
WHERE
A1.EMPLID=$Emp_Id
AND A1.EFFDT = (SELECT MAX(A1_ED.EFFDT) FROM PS_ADDRESSES A1_ED
WHERE A1.EMPLID = A1_ED.EMPLID
AND A1.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-employee-mail
!To get employee personal email id
!**********************************************************************************
Begin-Procedure Get-employee-mail
Let $mail =''
Begin-Select
E1.EMAIL_ADDR
Let $mail = ltrim(rtrim(&E1.EMAIL_ADDR, ' '), ' ')
FROM PS_EMAIL_ADDRESSES E1
WHERE E1.EMPLID=$Emp_Id
AND E1.E_ADDR_TYPE='HOME'
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Req-Chklst
!Input-->Emplid
!Output-->Check list Flag
!**********************************************************************************
Begin-Procedure Get-Req-Chklst
If $chk_st_I = 'C'
Begin-Select DISTINCT
AC.EMPLID
SHOW 'In Completed'
LET $Chk_fg = 'Y'
FROM PS_EMPL_CHECKLIST AC, PS_EMPL_CHKLST_ITM C, PS_CHKLST_ITEM_TBL D, PS_CHECKLIST_TBL F
WHERE AC.EMPLID = C.EMPLID
AND AC.EMPL_RCD = C.EMPL_RCD
AND AC.CHECKLIST_DT = C.CHECKLIST_DT
AND D.CHKLST_ITEM_CD = C.CHKLST_ITEM_CD
AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_CHKLST_ITEM_TBL D_ED
WHERE D.CHKLST_ITEM_CD = D_ED.CHKLST_ITEM_CD
AND D_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND AC.CHECKLIST_CD = F.CHECKLIST_CD
AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_CHECKLIST_TBL F_ED
WHERE F.CHECKLIST_CD = F_ED.CHECKLIST_CD
AND F_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND F.CHECKLIST_TYPE = 'TER'
AND C.BRIEFING_STATUS IN ('C','W','X')
AND NOT EXISTS (SELECT 'X' FROM PS_EMPL_CHKLST_ITM H, PS_EMPLMT_SRCH_QRY H1
WHERE H.EMPLID = H1.EMPLID
AND H.EMPL_RCD = H1.EMPL_RCD
AND H1.ROWSECCLASS = $Rowsecclass
AND ( AC.EMPLID = H.EMPLID
AND AC.EMPL_RCD = H.EMPL_RCD
AND H.BRIEFING_STATUS = 'I'
AND H.CHECKLIST_CD IN ('EXIT01','EXIT02','EXIT03','EXIT04') ))
AND AC.EMPLID = $Emp_Id
End-Select
ELSE
Begin-Select distinct
ACC.EMPL_RCD
LET $Chk_fg = 'Y'
FROM PS_EMPL_CHECKLIST ACC, PS_EMPL_CHKLST_ITM C, PS_CHKLST_ITEM_TBL D, PS_CHECKLIST_TBL F
WHERE ACC.EMPLID = C.EMPLID
AND ACC.EMPL_RCD = C.EMPL_RCD
AND ACC.CHECKLIST_DT = C.CHECKLIST_DT
AND D.CHKLST_ITEM_CD = C.CHKLST_ITEM_CD
AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_CHKLST_ITEM_TBL D_ED
WHERE D.CHKLST_ITEM_CD = D_ED.CHKLST_ITEM_CD
AND D_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND ACC.CHECKLIST_CD = F.CHECKLIST_CD
AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_CHECKLIST_TBL F_ED
WHERE F.CHECKLIST_CD = F_ED.CHECKLIST_CD
AND F_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND F.CHECKLIST_TYPE = 'TER'
AND C.BRIEFING_STATUS = 'I'
AND ACC.EMPLID = $Emp_Id
AND C.CHECKLIST_CD IN ('EXIT01','EXIT02','EXIT03','EXIT04')
End-Select
END-IF
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-finance-checklist
!Input-->Emplid,Checklist code
!Output-->Maximum action date,Briefing status
!**********************************************************************************
Begin-Procedure Get-finance-checklist
Let $checklist_cd=''
Let $briefing_st=''
Let $action_dt=''
Let $chk_status_fin=''
Let $comments_fin=''
Let $status_dt=''
Let $status_date=''
Let $action_date=''
Begin-Select
CHK.CHECKLIST_CD
Let $checklist_cd = ltrim(rtrim(&CHK.CHECKLIST_CD, ' '), ' ')
FROM PS_CHECKLIST_TBL CHK
WHERE CHK.DESCR LIKE 'Exit Finance & Accounts%'
AND CHK.CHECKLIST_TYPE= 'TER'
End-Select
Begin-Select
MAX(AC.ZIN_FWD_ACC_DT) &AC.ZIN_FWD_ACC_DT
Let $action_dt = ltrim(rtrim(&AC.ZIN_FWD_ACC_DT, ' '), ' ')
Let $action_date= &AC.ZIN_FWD_ACC_DT
FROM PS_EMPL_CHKLST_ITM AC
WHERE AC.CHECKLIST_CD = $checklist_cd
AND AC.EMPLID =$Emp_Id
End-Select
Begin-Select
MAX(ST.STATUS_DT) &ST.STATUS_DT
Let $status_dt = ltrim(rtrim(&ST.STATUS_DT, ' '), ' ')
Let $status_date= &ST.STATUS_DT
FROM PS_EMPL_CHKLST_ITM ST
WHERE ST.CHECKLIST_CD = $checklist_cd
AND ST.EMPLID =$Emp_Id
End-Select
let $brief_st = 'C'
Begin-Select
BS.BRIEFING_STATUS
Let $briefing_st = ltrim(rtrim(&BS.BRIEFING_STATUS , ' '), ' ')
IF $briefing_st = 'I'
let $brief_st = 'I'
END-IF
FROM PS_EMPL_CHKLST_ITM BS
WHERE BS.CHECKLIST_CD = $checklist_cd
AND BS.CHECKLIST_DT = (SELECT MAX(C_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM C_ED
WHERE BS.EMPLID=C_ED.EMPLID
AND BS.EMPL_RCD=C_ED.EMPL_RCD
AND BS.CHECKLIST_CD = C_ED.CHECKLIST_CD
AND BS.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND BS.EMPLID =$Emp_Id
End-Select
Begin-Select
X.XLATLONGNAME
Let $chk_status_fin = ltrim(rtrim(&X.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM X
WHERE X.FIELDNAME='BRIEFING_STATUS'
AND X.FIELDVALUE=$brief_st
End-Select
Begin-Select
ECHK.COMMENTS
Let $comments_fin = ltrim(rtrim(&ECHK.COMMENTS, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $comments_fin = replace($comments_fin, $enter, ' ')
Let $comments_fin = replace($comments_fin, $tab, ' ')
FROM PS_EMPL_CHECKLIST ECHK
WHERE ECHK.EMPLID=$Emp_Id
AND ECHK.CHECKLIST_CD=$checklist_cd
AND ECHK.CHECKLIST_DT = (SELECT MAX(ECHK_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM ECHK_ED
WHERE ECHK.EMPLID=ECHK_ED.EMPLID
AND ECHK.EMPL_RCD=ECHK_ED.EMPL_RCD
AND ECHK.CHECKLIST_CD = ECHK_ED.CHECKLIST_CD
AND ECHK.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-IT-checklist
!Input-->Emplid,Checklist code
!Output-->Maximum action date,Briefing status
!**********************************************************************************
Begin-Procedure Get-IT-checklist
Let $checklist_cd1=''
Let $briefing_st=''
Let $action_dt1=''
Let $chk_status1_IT=''
Let $comments_IT=''
Let $status_dt1=''
Let $status_date1=''
Let $action_date1=''
Begin-Select
CHK1.CHECKLIST_CD
Let $checklist_cd1 = ltrim(rtrim(&CHK1.CHECKLIST_CD, ' '), ' ')
FROM PS_CHECKLIST_TBL CHK1
WHERE CHK1.DESCR LIKE 'Exit Data Centre%'
AND CHK1.CHECKLIST_TYPE= 'TER'
End-Select
Begin-Select
MAX(AC1.ZIN_FWD_ACC_DT) &AC1.ZIN_FWD_ACC_DT
Let $action_dt1 = ltrim(rtrim(&AC1.ZIN_FWD_ACC_DT, ' '), ' ')
Let $action_date1= &AC1.ZIN_FWD_ACC_DT
FROM PS_EMPL_CHKLST_ITM AC1
WHERE AC1.CHECKLIST_CD = $checklist_cd1
AND AC1.EMPLID =$Emp_Id
End-Select
Begin-Select
MAX(ST1.STATUS_DT) &ST1.STATUS_DT
Let $status_dt1 = ltrim(rtrim(&ST1.STATUS_DT, ' '), ' ')
Let $status_date1= &ST1.STATUS_DT
FROM PS_EMPL_CHKLST_ITM ST1
WHERE ST1.CHECKLIST_CD = $checklist_cd1
AND ST1.EMPLID =$Emp_Id
End-Select
let $brief_st = 'C'
Begin-Select
BS1.BRIEFING_STATUS
Let $briefing_st = ltrim(rtrim(&BS1.BRIEFING_STATUS , ' '), ' ')
IF $briefing_st = 'I'
let $brief_st = 'I'
END-IF
FROM PS_EMPL_CHKLST_ITM BS1
WHERE BS1.CHECKLIST_CD = $checklist_cd1
AND BS1.CHECKLIST_DT = (SELECT MAX(C_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM C_ED
WHERE BS1.EMPLID=C_ED.EMPLID
AND BS1.EMPL_RCD=C_ED.EMPL_RCD
AND BS1.CHECKLIST_CD = C_ED.CHECKLIST_CD
AND BS1.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND BS1.EMPLID =$Emp_Id
End-Select
Begin-Select
X1.XLATLONGNAME
Let $chk_status1_IT = ltrim(rtrim(&X1.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM X1
WHERE X1.FIELDNAME='BRIEFING_STATUS'
AND X1.FIELDVALUE=$brief_st
End-Select
Begin-Select
ECHK1.COMMENTS
Let $comments_IT = ltrim(rtrim(&ECHK1.COMMENTS, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $comments_IT = replace($comments_IT, $enter, ' ')
Let $comments_IT = replace($comments_IT, $tab, ' ')
FROM PS_EMPL_CHECKLIST ECHK1
WHERE ECHK1.EMPLID=$Emp_Id
AND ECHK1.CHECKLIST_CD= $checklist_cd1
AND ECHK1.CHECKLIST_DT = (SELECT MAX(ECHK_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM ECHK_ED
WHERE ECHK1.EMPLID=ECHK_ED.EMPLID
AND ECHK1.EMPL_RCD=ECHK_ED.EMPL_RCD
AND ECHK1.CHECKLIST_CD = ECHK_ED.CHECKLIST_CD
AND ECHK1.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-Facilities
!Input-->Emplid,Checklist code
!Output-->Maximum action date,Briefing status
!**********************************************************************************
Begin-Procedure Get-Facilities
Let $checklist_cd2=''
Let $briefing_st=''
Let $action_dt2=''
Let $chk_status2_Fac=''
Let $comments_Fac=''
Let $status_dt2=''
Let $status_date2=''
Let $action_date2=''
Begin-Select
CHK2.CHECKLIST_CD
Let $checklist_cd2 = ltrim(rtrim(&CHK2.CHECKLIST_CD, ' '), ' ')
FROM PS_CHECKLIST_TBL CHK2
WHERE CHK2.DESCR LIKE 'Exit Facilities%'
AND CHK2.CHECKLIST_TYPE= 'TER'
End-Select
Begin-Select
MAX(AC2.ZIN_FWD_ACC_DT) &AC2.ZIN_FWD_ACC_DT
Let $action_dt2 = ltrim(rtrim(&AC2.ZIN_FWD_ACC_DT, ' '), ' ')
Let $action_date2= &AC2.ZIN_FWD_ACC_DT
FROM PS_EMPL_CHKLST_ITM AC2
WHERE AC2.CHECKLIST_CD = $checklist_cd2
AND AC2.EMPLID =$Emp_Id
End-Select
Begin-Select
MAX(ST2.STATUS_DT) &ST2.STATUS_DT
Let $status_dt2 = ltrim(rtrim(&ST2.STATUS_DT, ' '), ' ')
Let $status_date2= &ST2.STATUS_DT
FROM PS_EMPL_CHKLST_ITM ST2
WHERE ST2.CHECKLIST_CD = $checklist_cd2
AND ST2.EMPLID =$Emp_Id
End-Select
let $brief_st = 'C'
Begin-Select
BS2.BRIEFING_STATUS
Let $briefing_st = ltrim(rtrim(&BS2.BRIEFING_STATUS , ' '), ' ')
IF $briefing_st = 'I'
let $brief_st = 'I'
END-IF
FROM PS_EMPL_CHKLST_ITM BS2
WHERE BS2.CHECKLIST_CD = $checklist_cd2
AND BS2.CHECKLIST_DT = (SELECT MAX(C_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM C_ED
WHERE BS2.EMPLID=C_ED.EMPLID
AND BS2.EMPL_RCD=C_ED.EMPL_RCD
AND BS2.CHECKLIST_CD = C_ED.CHECKLIST_CD
AND BS2.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND BS2.EMPLID =$Emp_Id
End-Select
Begin-Select
X2.XLATLONGNAME
Let $chk_status2_Fac = ltrim(rtrim(&X2.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM X2
WHERE X2.FIELDNAME='BRIEFING_STATUS'
AND X2.FIELDVALUE=$brief_st
End-Select
Begin-Select
ECHK2.COMMENTS
Let $comments_Fac = ltrim(rtrim(&ECHK2.COMMENTS, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $comments_Fac = replace($comments_Fac, $enter, ' ')
Let $comments_Fac = replace($comments_Fac, $tab, ' ')
FROM PS_EMPL_CHECKLIST ECHK2
WHERE ECHK2.EMPLID=$Emp_Id
AND ECHK2.CHECKLIST_CD= $checklist_cd2
AND ECHK2.CHECKLIST_DT = (SELECT MAX(ECHK_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM ECHK_ED
WHERE ECHK2.EMPLID=ECHK_ED.EMPLID
AND ECHK2.EMPL_RCD=ECHK_ED.EMPL_RCD
AND ECHK2.CHECKLIST_CD = ECHK_ED.CHECKLIST_CD
AND ECHK2.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-TR-D
!Input-->Emplid,Checklist code
!Output-->Maximum action date,Briefing status
!**********************************************************************************
Begin-Procedure Get-TR-D
Let $checklist_cd3=''
Let $briefing_st=''
Let $action_dt3=''
Let $chk_status3_TR=''
Let $comments_TR=''
Let $status_dt3=''
Let $status_date3=''
Let $action_date3=''
Begin-Select
CHK3.CHECKLIST_CD
Let $checklist_cd3 = ltrim(rtrim(&CHK3.CHECKLIST_CD, ' '), ' ')
FROM PS_CHECKLIST_TBL CHK3
WHERE CHK3.DESCR LIKE 'Exit T & D%'
AND CHK3.CHECKLIST_TYPE= 'TER'
End-Select
Begin-Select
MAX(AC3.ZIN_FWD_ACC_DT) &AC3.ZIN_FWD_ACC_DT
Let $action_dt3 = ltrim(rtrim(&AC3.ZIN_FWD_ACC_DT, ' '), ' ')
Let $action_date3= &AC3.ZIN_FWD_ACC_DT
FROM PS_EMPL_CHKLST_ITM AC3
WHERE AC3.CHECKLIST_CD = $checklist_cd3
AND AC3.EMPLID =$Emp_Id
End-Select
Begin-Select
MAX(ST3.STATUS_DT) &ST3.STATUS_DT
Let $status_dt3 = ltrim(rtrim(&ST3.STATUS_DT, ' '), ' ')
Let $status_date3= &ST3.STATUS_DT
FROM PS_EMPL_CHKLST_ITM ST3
WHERE ST3.CHECKLIST_CD = $checklist_cd3
AND ST3.EMPLID =$Emp_Id
End-Select
let $brief_st = 'C'
Begin-Select
BS3.BRIEFING_STATUS
Let $briefing_st = ltrim(rtrim(&BS3.BRIEFING_STATUS , ' '), ' ')
IF $briefing_st = 'I'
let $brief_st = 'I'
END-IF
FROM PS_EMPL_CHKLST_ITM BS3
WHERE BS3.CHECKLIST_CD = $checklist_cd3
AND BS3.CHECKLIST_DT = (SELECT MAX(C_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM C_ED
WHERE BS3.EMPLID=C_ED.EMPLID
AND BS3.EMPL_RCD=C_ED.EMPL_RCD
AND BS3.CHECKLIST_CD = C_ED.CHECKLIST_CD
AND BS3.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND BS3.EMPLID =$Emp_Id
End-Select
Begin-Select
X3.XLATLONGNAME
Let $chk_status3_TR = ltrim(rtrim(&X3.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM X3
WHERE X3.FIELDNAME='BRIEFING_STATUS'
AND X3.FIELDVALUE=$brief_st
End-Select
Begin-Select
ECHK3.COMMENTS
Let $comments_TR = ltrim(rtrim(&ECHK3.COMMENTS, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $comments_TR = replace($comments_TR, $enter, ' ')
Let $comments_TR = replace($comments_TR, $tab, ' ')
FROM PS_EMPL_CHECKLIST ECHK3
WHERE ECHK3.EMPLID=$Emp_Id
AND ECHK3.CHECKLIST_CD= $checklist_cd3
AND ECHK3.CHECKLIST_DT = (SELECT MAX(ECHK_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM ECHK_ED
WHERE ECHK3.EMPLID=ECHK_ED.EMPLID
AND ECHK3.EMPL_RCD=ECHK_ED.EMPL_RCD
AND ECHK3.CHECKLIST_CD = ECHK_ED.CHECKLIST_CD
AND ECHK3.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!**********************************************************************************
!Begin-Procedure Get-HR-OP
!Input-->Emplid,Checklist code
!Output-->Maximum action date,Briefing status
!**********************************************************************************
Begin-Procedure Get-HR-OP
Let $checklist_cd4=''
Let $briefing_st=''
Let $action_dt4=''
Let $chk_status4_HR=''
Let $comments_HR=''
Let $status_dt4=''
Let $status_date4=''
Let $action_date4=''
Begin-Select
CHK4.CHECKLIST_CD
Let $checklist_cd4 = ltrim(rtrim(&CHK4.CHECKLIST_CD, ' '), ' ')
FROM PS_CHECKLIST_TBL CHK4
WHERE CHK4.DESCR LIKE 'Exit - ZHR / HR Ops%'
AND CHK4.CHECKLIST_TYPE= 'TER'
End-Select
Begin-Select
MAX(AC4.ZIN_FWD_ACC_DT) &AC4.ZIN_FWD_ACC_DT
Let $action_dt4 = ltrim(rtrim(&AC4.ZIN_FWD_ACC_DT, ' '), ' ')
Let $action_date4= &AC4.ZIN_FWD_ACC_DT
FROM PS_EMPL_CHKLST_ITM AC4
WHERE AC4.CHECKLIST_CD = $checklist_cd4
AND AC4.EMPLID =$Emp_Id
End-Select
Begin-Select
MAX(ST4.STATUS_DT) &ST4.STATUS_DT
Let $status_dt4 = ltrim(rtrim(&ST4.STATUS_DT, ' '), ' ')
Let $status_date4= &ST4.STATUS_DT
FROM PS_EMPL_CHKLST_ITM ST4
WHERE ST4.CHECKLIST_CD = $checklist_cd4
AND ST4.EMPLID =$Emp_Id
End-Select
let $brief_st = 'C'
Begin-Select
BS4.BRIEFING_STATUS
Let $briefing_st = ltrim(rtrim(&BS4.BRIEFING_STATUS , ' '), ' ')
IF $briefing_st = 'I'
let $brief_st = 'I'
END-IF
FROM PS_EMPL_CHKLST_ITM BS4
WHERE BS4.CHECKLIST_CD = $checklist_cd4
AND BS4.CHECKLIST_DT = (SELECT MAX(C_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM C_ED
WHERE BS4.EMPLID=C_ED.EMPLID
AND BS4.EMPL_RCD=C_ED.EMPL_RCD
AND BS4.CHECKLIST_CD = C_ED.CHECKLIST_CD
AND BS4.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND BS4.EMPLID =$Emp_Id
End-Select
Begin-Select
X4.XLATLONGNAME
Let $chk_status4_HR = ltrim(rtrim(&X4.XLATLONGNAME, ' '), ' ')
FROM PSXLATITEM X4
WHERE X4.FIELDNAME='BRIEFING_STATUS'
AND X4.FIELDVALUE=$brief_st
End-Select
Begin-Select
ECHK4.COMMENTS
Let $comments_HR = ltrim(rtrim(&ECHK4.COMMENTS, ' '), ' ')
Let $enter = chr(13)||chr(10)
Let $comments_HR = replace($comments_HR, $enter, ' ')
Let $comments_HR = replace($comments_HR, $tab, ' ')
FROM PS_EMPL_CHECKLIST ECHK4
WHERE ECHK4.EMPLID=$Emp_Id
AND ECHK4.CHECKLIST_CD= $checklist_cd4
AND ECHK4.CHECKLIST_DT = (SELECT MAX(ECHK_ED.CHECKLIST_DT) FROM PS_EMPL_CHKLST_ITM ECHK_ED
WHERE ECHK4.EMPLID=ECHK_ED.EMPLID
AND ECHK4.EMPL_RCD=ECHK_ED.EMPL_RCD
AND ECHK4.CHECKLIST_CD = ECHK_ED.CHECKLIST_CD
AND ECHK4.CHECKLIST_DT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
End-Select
End-Procedure
!****************************************************************************
!Procedure to Initialize-Appl-Variables
!****************************************************************************
BEGIN-PROCEDURE Initialize-Appl-Variables
Let $Emp_ID = ' '
let $Name = ' '
let $designation = ' '
let $zone = ' '
let $Sub_zone = ' '
let $location = ' '
let $branch = ' '
let $grade = ' '
let $deptid = ' '
let $function = ' '
let $setid = ' '
let $sub_function = ' '
let $hiredt = ' '
let $lwd = ' '
let $duration = ' '
let $supervisor = ' '
let $resignation_dt = ' '
let $notice_period = ' '
let $action_reason = ' '
let $Trans = ' '
let $Originate = ' '
let $comments1 = ' '
let $comments2 = ' '
let $upd = ' '
let $upd_user = ' '
let $regret = ' '
END-PROCEDURE
!****************************************************************************
!Main Procedure for the SQR
!****************************************************************************
BEGIN-PROCEDURE Process-Main
Let #Appl_Cnt = 1
Do Open-Output-File
Do Write-Header
Do Generate_Where_Condition
Do Get-Exit-Employee-Data
Do Close-Output-File
END-PROCEDURE
!****************************************************************************
!SQR Program
!****************************************************************************
BEGIN-PROGRAM
Do Get-Current-DateTime
Do Init-DateTime
Do Init-Number
Do Init-Report
Do Process-Main
Do Terminate
Do Commit-Transaction
END-PROGRAM
!*****************************************************************************
! Include Files for this report
!*****************************************************************************
#include 'number.sqc' !Routines to format numbers
#include 'datetime.sqc' !Routines for date and time formatting
#include 'prcsapi.sqc' !Update process request API
#include 'datemath.sqc' !Date conversion procedures
#include 'reset1.sqc' !Reset printer procedure & print "End of Report"
